Database Documentation

This section contains comprehensive documentation for the LyfeAI Provider database architecture, schema, and operations.

Overview

LyfeAI Provider uses PostgreSQL as its primary database, hosted on Supabase. The database is designed specifically for healthcare applications with a focus on:

• HIPAA Compliance - Row-level security and audit trails
• FHIR Compatibility - Structure supports FHIR resource mapping
• AI Integration - Dedicated tables for AI insights and analytics
• Scalability - UUID keys and optimized indexes
• Flexibility - JSONB columns for complex medical data

Documentation Structure

Core Documentation

• Database Schema - Complete table definitions and relationships
• Data Models - TypeScript interfaces and types
• Migrations - Database migration guide and scripts
• Security - RLS policies and security considerations
• Performance - Optimization and indexing strategies

Operational Guides

• Setup Guide - Initial database setup instructions
• Backup & Recovery - Backup strategies and procedures
• Monitoring - Database monitoring and alerts
• Troubleshooting - Common issues and solutions

Quick Reference

Connection

// Environment variables
NEXT_PUBLIC_SUPABASE_URL=your_supabase_url
NEXT_PUBLIC_SUPABASE_ANON_KEY=your_supabase_anon_key
SUPABASE_SERVICE_ROLE_KEY=your_service_role_key

Key Tables

Table	Purpose	Key Fields
users	System users and providers	email, role, specialty
patients	Patient demographics	mrn, name, dob, status
patient_problems	Medical conditions	name, icd10_code, severity
patient_medications	Current/past medications	medication_name, dosage, status
patient_allergies	Allergies and reactions	allergen, reaction, severity
encounters	Clinical visits	encounter_date, type, provider
patient_notes	Clinical documentation	note_type, content, author
ai_insights	AI-generated insights	title, description, confidence
timeline_events	Patient history timeline	event_type, event_date, details

SQL Scripts

The /scripts directory contains numbered SQL scripts for database setup:

1. 01_create_tables.sql - Base schema creation
2. 02_create_views.sql - Helper views
3. 03_create_policies.sql - RLS policies
4. 04_insert_sample_data.sql - Demo data
5. Additional migration scripts...

Common Queries

-- Get patient summary
SELECT * FROM patient_summary WHERE patient_id = ?;

-- Recent activities
SELECT * FROM recent_patient_activity 
WHERE patient_id = ? 
ORDER BY activity_time DESC 
LIMIT 10;

-- Provider dashboard
SELECT * FROM provider_dashboard WHERE provider_id = ?;

Development Workflow

Local Development

1. In-Memory Store (Default)

   • No database setup required
   • Data stored in lib/data.ts
   • Resets on server restart

2. Local PostgreSQL

   • Run SQL scripts in order
   • Update environment variables
   • Test with real database

Making Schema Changes

1. Create new migration script
2. Test on development database
3. Update TypeScript types
4. Document changes
5. Deploy to staging
6. Apply to production

Best Practices

Schema Design

• Use UUIDs for all primary keys
• Include audit fields (created_at, updated_at)
• Implement soft deletes with status fields
• Use JSONB for flexible data structures
• Create appropriate indexes

Security

• Enable RLS on all tables
• Use parameterized queries
• Implement field-level encryption for PHI
• Regular security audits
• Follow HIPAA guidelines

Performance

• Monitor slow queries
• Use EXPLAIN ANALYZE
• Create covering indexes
• Partition large tables
• Regular VACUUM and ANALYZE

Resources

• PostgreSQL Documentation
• Supabase Documentation
• FHIR Specification
• HIPAA Compliance Guide