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_resultthat 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.