Database Design

The HCG AI application utilizes PostgreSQL as its primary database, with Drizzle ORM for schema definition, migrations, and type-safe database queries. The schema is designed to handle user authentication, longitudinal medical tracking, and AI-driven analysis results.

Schema Overview

The database consists of five core tables: users, sessions, hcg_entries, ultrasound_entries, and analysis_results. All clinical data is linked to a specific user via a Foreign Key relationship.

1. User Management (users)

Stores authenticated user profiles. The system uses UUIDs for primary keys to ensure unique identification across distributed environments.

| Field | Type | Description | | :--- | :--- | :--- | | id | varchar (UUID) | Primary key, generated via gen_random_uuid() | | email | varchar | Unique email address used for login | | password | varchar | Argon2/Bcrypt hashed password string | | first_name | varchar | Optional user's first name | | last_name | varchar | Optional user's last name |

2. HCG Blood Test Tracking (hcg_entries)

Records β-hCG levels over time. It includes advanced fields for tracking the Last Menstrual Period (LMP) and ovulation to improve analysis accuracy.

| Field | Type | Description | | :--- | :--- | :--- | | hcg_value | integer | The measured HCG level | | date | text | Date of test (Stored in YYYY-MM-DD format) | | units | text | Default: mIU/mL | | lmp_date | text | Optional Last Menstrual Period date | | cycle_length| integer | User's menstrual cycle length in days |

3. Ultrasound Analysis (ultrasound_entries)

Captures diagnostic measurements from ultrasound scans. This table supports multiple pregnancy sacs through a flexible JSONB column.

| Field | Type | Description | | :--- | :--- | :--- | | gestational_age| text | Formatted age (e.g., "6w 3d") | | gs_size | integer | Gestational Sac size in mm | | ys_size | integer | Yolk Sac size in mm | | crl_size | integer | Crown-Rump Length in mm | | heart_rate | integer | Fetal heart rate in BPM | | sac_data | jsonb | Nested JSON for individual sac measurements |

4. Analysis & Risk Assessment (analysis_results)

Stores the output of medical-grade algorithms and AI assessments.

| Field | Type | Description | | :--- | :--- | :--- | | entry_type | text | Discriminator: 'hcg' or 'ultrasound' | | risk_level | text | Assessment value: 'LOW', 'MEDIUM', or 'HIGH' | | diagnosis | text | Summary of findings | | z_scores | jsonb | Calculated standard deviations for measurements | | recommendations| jsonb | Array of suggested medical follow-ups |

Data Relationships

The schema implements a one-to-many relationship hierarchy:

• User → Entries: A single user owns multiple HCG and Ultrasound records.
• Entries → Analysis: Every HCG or Ultrasound entry can have an associated analysis_result that provides clinical context.

// Example: Accessing related data via Drizzle
export const userRelations = relations(users, ({ many }) => ({
  hcgEntries: many(hcgEntries),
  ultrasoundEntries: many(ultrasoundEntries),
}));

Type Definitions & Validation

The project uses drizzle-zod to automatically generate validation schemas from the database definitions. These are used for API request validation and TypeScript intellisense.

Usage Example: TypeScript Types

import { SelectUser, InsertHcgEntry } from '@shared/schema';

// Type-safe HCG entry creation
const newEntry: InsertHcgEntry = {
  userId: "uuid-string",
  hcgValue: 1500,
  date: "2023-10-25",
  units: "mIU/mL"
};

Usage Example: Zod Validation

import { insertHcgEntrySchema } from '@shared/schema';

// Validates request body in Express routes
const result = insertHcgEntrySchema.safeParse(req.body);
if (!result.success) {
  res.status(400).json({ error: "Invalid data format" });
}

Schema Migrations

The database schema is managed through Drizzle Kit. To sync the physical database with the definitions in shared/schema.ts, use the following commands:

• Development Push: Syncs the schema immediately (useful for rapid prototyping).

  npm run db:push
  

• Force Push: Resets the schema (Caution: This will delete existing data).

  npm run db:push --force
  

Session Storage

The application uses the connect-pg-simple library to store session data in a sessions table within PostgreSQL. This ensures that user logins persist across server restarts and provides better scalability than memory-based sessions.