Database Schema Documentation

This document provides an overview of the LyfeAI Provider database schema as currently implemented.

⚠️ Current State: The database schema is in early development. Many tables shown in aspirational documentation are not yet implemented. This document reflects the actual current state of the database.

Overview

The database uses PostgreSQL with:

• UUID primary keys for all tables
• JSONB columns for flexible data storage (addresses, insurance info)
• Basic foreign key relationships
• Timestamps for audit trails

Current Implementation Status

✅ Implemented Tables (5 tables)

• users - System users and healthcare providers
• patients - Patient demographics
• patient_problems - Medical conditions/diagnoses
• patient_medications - Current and past medications
• patient_allergies - Allergies and reactions

❌ Not Yet Implemented (Planned)

• encounters - Clinical visits
• patient_notes - Clinical documentation
• ai_insights - AI-generated insights
• timeline_events - Patient timeline
• appointments - Scheduling
• communications - Messages
• orders - Lab/imaging orders
• results - Test results

Database Diagram (Current State)

erDiagram
    users ||--o{ patients : "primary_provider"
    users ||--o{ patient_problems : "created_by"
    users ||--o{ patient_medications : "prescribed_by"
    users ||--o{ patient_allergies : "created_by"
    
    patients ||--o{ patient_problems : "has"
    patients ||--o{ patient_medications : "takes"
    patients ||--o{ patient_allergies : "has"

Implemented Tables

users

Stores system users and healthcare providers.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY, DEFAULT uuid_generate_v4()	Unique identifier
email	TEXT	NOT NULL, UNIQUE	User email
encrypted_password	TEXT	NOT NULL	Password (currently plain text in mock system)
first_name	TEXT	NOT NULL	User's first name
last_name	TEXT	NOT NULL	User's last name
role	TEXT	NOT NULL	User role (admin, doctor, nurse, staff)
specialty	TEXT		Medical specialty
status	TEXT	DEFAULT 'active'	Account status
profile_image_url	TEXT		Profile picture URL
created_at	TIMESTAMPTZ	DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	DEFAULT NOW()	Last update timestamp
last_login_at	TIMESTAMPTZ		Last login time

Notes:

• The encrypted_password field is misleadingly named - passwords are stored in plain text in the current mock system
• No indexes are currently defined beyond the primary key

---

patients

Central patient demographic and contact information.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY, DEFAULT uuid_generate_v4()	Unique identifier
mrn	TEXT	NOT NULL, UNIQUE	Medical Record Number
first_name	TEXT	NOT NULL	Patient's first name
last_name	TEXT	NOT NULL	Patient's last name
date_of_birth	DATE	NOT NULL	Date of birth
gender	TEXT	NOT NULL	Patient gender
address	JSONB		Structured address data
phone	TEXT		Primary phone
email	TEXT		Email address
status	TEXT	DEFAULT 'active'	Patient status (no enum type)
primary_provider_id	UUID	REFERENCES users(id)	Primary care provider
insurance_info	JSONB		Insurance details
emergency_contact	JSONB		Emergency contact info
created_at	TIMESTAMPTZ	DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	DEFAULT NOW()	Last update timestamp
last_visit_date	TIMESTAMPTZ		Most recent visit

Address JSONB Structure Example:

{
  "street": "123 Main St",
  "city": "Boston",
  "state": "MA",
  "zipCode": "02101"
}

Notes:

• The hidden column mentioned in some documentation is added by script 07, not in base schema
• No custom enum type for status - uses plain TEXT

---

patient_problems

Medical problems, diagnoses, and conditions.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY, DEFAULT uuid_generate_v4()	Unique identifier
patient_id	UUID	NOT NULL, REFERENCES patients(id)	Patient reference
name	TEXT	NOT NULL	Problem/diagnosis name
icd10_code	TEXT		ICD-10 diagnosis code
onset_date	DATE		Date of onset
status	TEXT	DEFAULT 'active'	Problem status
severity	TEXT		Severity level
notes	TEXT		Clinical notes
created_at	TIMESTAMPTZ	DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	DEFAULT NOW()	Last update timestamp
created_by	UUID	REFERENCES users(id)	Creating provider

Notes:

• No CASCADE DELETE defined on foreign keys
• No indexes beyond primary key

---

patient_medications

Current and historical medications.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY, DEFAULT uuid_generate_v4()	Unique identifier
patient_id	UUID	NOT NULL, REFERENCES patients(id)	Patient reference
medication_id	UUID		Reference to medication database
medication_name	TEXT	NOT NULL	Medication name
dosage	TEXT	NOT NULL	Dosage amount
frequency	TEXT	NOT NULL	Dosing frequency
route	TEXT		Administration route
start_date	DATE		Start date
end_date	DATE		End/discontinuation date
status	TEXT	DEFAULT 'active'	Medication status
instructions	TEXT		Special instructions
reason	TEXT		Indication
prescribed_by	UUID	REFERENCES users(id)	Prescribing provider
created_at	TIMESTAMPTZ	DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	DEFAULT NOW()	Last update timestamp

---

patient_allergies

Allergies and adverse reactions.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY, DEFAULT uuid_generate_v4()	Unique identifier
patient_id	UUID	NOT NULL, REFERENCES patients(id)	Patient reference
allergen	TEXT	NOT NULL	Allergen name
reaction	TEXT		Type of reaction
severity	TEXT		Severity level
onset_date	DATE		When identified
status	TEXT	DEFAULT 'active'	Allergy status
created_at	TIMESTAMPTZ	DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	DEFAULT NOW()	Last update timestamp
created_by	UUID	REFERENCES users(id)	Documenting provider

---

Views

According to script 02_create_views.sql, the following views should exist:

❓ patient_summary (May not be created)

A view that aggregates patient information, but references tables that don't exist (encounters).

❓ recent_patient_activity (May not be created)

A union view across multiple tables, but references non-existent tables.

Note: These views likely fail to create due to missing dependent tables.

---

Security

Row-Level Security (RLS)

According to script 03_create_policies.sql, RLS should be enabled, but:

• ⚠️ Policies may not be properly enforced
• Current implementation allows broad access for development
• No production-ready security policies

Current Security State

• Authentication: Mock system with localStorage
• Authorization: Basic role checking in application code
• Database Security: Minimal - relies on Supabase service role key
• Encryption: None implemented
• Audit Trails: Basic timestamps only

---

Missing Features

Tables Not Yet Implemented

1. Clinical Documentation

   • encounters (visits)
   • patient_notes
   • orders
   • results

2. Communication & Workflow

   • appointments
   • communications
   • tasks
   • notifications

3. AI & Analytics

   • ai_insights
   • timeline_events
   • analytics_metrics

Features Not Implemented

• Custom enum types
• Comprehensive indexes
• Triggers for updated_at
• Audit tables
• Soft delete patterns
• Data archival

---

Development vs Production Gaps

Current State (Development)

• 5 basic tables
• Mock data from SQL scripts
• No real security
• Missing most clinical features
• No performance optimization

Required for Production

• Complete clinical data model
• HIPAA-compliant security
• Comprehensive audit trails
• Performance indexes
• Data encryption
• Backup and recovery
• High availability

---

Migration Scripts

The following SQL scripts exist in the /scripts directory:

1. 01_create_tables.sql - Creates the 5 base tables
2. 02_create_views.sql - Attempts to create views (may fail)
3. 03_create_policies.sql - Creates RLS policies
4. 04_insert_sample_data.sql - Inserts demo data
5. Additional scripts for fixes and updates

Note: Run scripts in order, but be aware that views may fail due to missing tables.

---

Next Steps for Schema Development

1. Immediate Needs

   • Create encounters table for visits
   • Create appointments table for scheduling
   • Add proper indexes

2. Short Term

   • Implement patient_notes for documentation
   • Add orders and results tables
   • Create working views

3. Long Term

   • Design AI insights storage
   • Implement audit trail system
   • Add FHIR compatibility layer

---

Important Notes

⚠️ This schema is NOT production-ready:

• Missing critical healthcare tables
• No HIPAA compliance features
• Security is minimal
• No performance optimization
• Many planned features not implemented

The current implementation is suitable for:

• Development and prototyping
• UI demonstrations
• Basic CRUD operations
• Concept validation

It is NOT suitable for:

• Production healthcare use
• Storing real patient data
• HIPAA-compliant environments
• High-performance requirements