BytecodeApps/docverse-api

1-- Database Schema for AI Medical Document Scanner & Health Insight App2-- Target RDBMS: PostgreSQL3 4CREATE EXTENSION IF NOT EXISTS "uuid-ossp";5CREATE EXTENSION IF NOT EXISTS pgcrypto; -- For AES encryption functions if needed natively6 7-- 1. Users Table8CREATE TABLE users (9    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),10    email VARCHAR(255) UNIQUE NOT NULL,11    password_hash VARCHAR(255) NOT NULL,12    first_name VARCHAR(100),13    last_name VARCHAR(100),14    role VARCHAR(50) DEFAULT 'user', -- 'user', 'admin'15    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,16    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP17);18 19-- 2. Subscriptions Table20CREATE TABLE subscriptions (21    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),22    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,23    plan_tier VARCHAR(50) DEFAULT 'free', -- 'free', 'premium'24    status VARCHAR(50) DEFAULT 'active',25    start_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,26    end_date TIMESTAMP WITH TIME ZONE,27    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,28    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP29);30 31-- 3. Reports Table32CREATE TABLE reports (33    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),34    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,35    file_name VARCHAR(255) NOT NULL,36    file_path TEXT NOT NULL, -- S3/Bucket path (Encrypted at rest)37    report_type VARCHAR(50), -- 'lab_result', 'prescription'38    status VARCHAR(50) DEFAULT 'processing', -- 'processing', 'completed', 'failed'39    extracted_text_encrypted BYTEA, -- Raw text from EasyOCR encrypted40    summary_encrypted BYTEA, -- Insight generator summary encrypted41    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,42    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP43);44 45-- 4. LabResults Table46CREATE TABLE lab_results (47    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),48    report_id UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,49    test_name VARCHAR(100) NOT NULL,50    test_value NUMERIC(10, 2),51    unit VARCHAR(50),52    reference_range VARCHAR(100),53    flag VARCHAR(50), -- 'high', 'low', 'normal', 'critical'54    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP55);56 57-- 5. Medications Table58CREATE TABLE medications (59    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),60    report_id UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,61    drug_name VARCHAR(200) NOT NULL,62    dosage VARCHAR(100),63    frequency VARCHAR(100),64    instructions TEXT,65    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP66);67 68-- 6. RiskFlags Table69CREATE TABLE risk_flags (70    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),71    report_id UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,72    flag_type VARCHAR(100), -- 'interaction', 'vital_anomaly', 'emergency'73    severity VARCHAR(50), -- 'low', 'medium', 'high', 'critical'74    description TEXT NOT NULL,75    resolved BOOLEAN DEFAULT FALSE,76    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP77);78 79-- 7. Reminders Table80CREATE TABLE reminders (81    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),82    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,83    medication_id UUID REFERENCES medications(id) ON DELETE SET NULL,84    title VARCHAR(100) NOT NULL,85    scheduled_time TIME NOT NULL,86    days_of_week VARCHAR(50), -- e.g., '1,2,3,4,5,6,7' for daily87    is_active BOOLEAN DEFAULT TRUE,88    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,89    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP90);91 92-- Indexes for performance93CREATE INDEX idx_users_email ON users(email);94CREATE INDEX idx_reports_user_id ON reports(user_id);95CREATE INDEX idx_lab_results_report_id ON lab_results(report_id);96CREATE INDEX idx_medications_report_id ON medications(report_id);97CREATE INDEX idx_risk_flags_report_id ON risk_flags(report_id);98CREATE INDEX idx_reminders_user_id ON reminders(user_id);99