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_at and updated_at.

2. HCG Entries (`hcg_entries`)

Tracks β-hCG blood test results and relevant menstrual cycle data.

userId: varchar (Foreign Key to users.id).
hcgValue: integer (The measured HCG level).
date: text (Stored in YYYY-MM-DD format).
units: text (Defaults to mIU/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.

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_result providing 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.

Database Schema & Models