Database Schema & Models
Database Schema & Models
HCG AI uses PostgreSQL as its primary data store, managed via Drizzle ORM. The schema is designed to handle user authentication, longitudinal medical tracking (HCG and Ultrasound), and AI-generated risk assessments.
Core Tables
1. Users (users)
Stores account information and authentication credentials.
- id:
UUID(Primary Key, auto-generated). - email:
varchar(Unique, required). - password:
varchar(Hashed). - firstName / lastName:
varchar(Optional). - timestamps:
created_atandupdated_at.
2. HCG Entries (hcg_entries)
Tracks β-hCG blood test results and relevant menstrual cycle data.
- userId:
varchar(Foreign Key tousers.id). - hcgValue:
integer(The measured HCG level). - date:
text(Stored inYYYY-MM-DDformat). - units:
text(Defaults tomIU/mL). - Advanced Fields:
lmpDate: Last Menstrual Period date.cycleLength: Length of cycle in days.ovulationDate: Estimated date of ovulation.
3. Ultrasound Entries (ultrasound_entries)
Stores biometric measurements from ultrasound scans.
- gestationalAge:
text(Estimated age, e.g., "6w2d"). - Measurements (mm):
gs_size(Gestational Sac),ys_size(Yolk Sac),crl_size(Crown-Rump Length). - heartRate:
integer(Measured in BPM). - Multiple Sac Support:
numberOfSacs:integer.sac_data:jsonb(Stores specific measurements for individual sacs).
4. Analysis Results (analysis_results)
Stores AI-processed insights and medical risk assessments.
- entryType:
text('hcg' or 'ultrasound'). - riskLevel:
text(Enum:LOW,MEDIUM,HIGH). - diagnosis:
text(Brief summary of findings). - recommendations:
jsonb(Actionable steps generated by the AI). - zScores:
jsonb(Calculated statistical deviations for measurements).
Data Validation & Types
The project uses drizzle-zod to provide runtime validation. These schemas ensure that data coming through the API matches the database requirements.
| Exported Schema | Usage |
| :--- | :--- |
| insertUserSchema | Validates registration and profile updates. |
| insertHcgEntrySchema | Validates new HCG blood test logs. |
| insertUltrasoundEntrySchema | Validates ultrasound measurement submissions. |
Entity Relationships
The schema defines a one-to-many relationship between users and their medical data:
- User ↔ HCG Entries: A user can have multiple HCG logs over time.
- User ↔ Ultrasound Entries: A user can log multiple scans.
- Entries ↔ Analysis: Every HCG or Ultrasound entry can link to an
analysis_resultproviding automated insights.
Usage Example: Querying Data
To interact with the models within the application, use the exported storage object or Drizzle's db instance:
// Example: Creating a new HCG entry
const newEntry = await storage.createHcgEntry({
userId: "user-uuid-123",
hcgValue: 1500,
date: "2023-10-27",
units: "mIU/mL",
notes: "First blood test"
});
// Example: Fetching ultrasound data with relations
const scans = await db.query.ultrasoundEntries.findMany({
where: eq(ultrasoundEntries.userId, currentUserId),
with: {
analysisResults: true
}
});
Session Management
The application uses the sessions table via connect-pg-simple to manage user sessions. This table is handled automatically by the middleware and stores the session ID (sid), the session data (sess), and the expiration timestamp.