Database Schema

CREATE TABLE patients (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL REFERENCES clinics(id),
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  date_of_birth DATE NOT NULL,
  phi_data JSONB DEFAULT '{}'::jsonb,  -- encrypted contact info
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE form_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID REFERENCES clinics(id),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  definition JSONB NOT NULL,  -- form structure
  is_active BOOLEAN DEFAULT TRUE,
  version VARCHAR(50),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE form_assignments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL,
  patient_id UUID NOT NULL REFERENCES patients(id),
  form_template_id UUID NOT NULL REFERENCES form_templates(id),
  assigned_by_user_id UUID REFERENCES users(id),
  assigned_to_user_id UUID REFERENCES users(id),
  status VARCHAR(50) DEFAULT 'pending',
  portal_token VARCHAR(255) UNIQUE,  -- for patient link
  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);

CREATE TABLE form_submissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL,
  assignment_id UUID NOT NULL UNIQUE REFERENCES form_assignments(id),
  patient_id UUID NOT NULL REFERENCES patients(id),
  submitted_by_user_id UUID REFERENCES users(id),

  form_type VARCHAR(100) NOT NULL,
  form_version VARCHAR(50) NOT NULL,
  form_data JSONB NOT NULL,  -- patient answers

  processing_results JSONB DEFAULT '{}'::jsonb,
  -- Stores AI summaries:
  -- {
  --   "summaries": {
  --     "clinician": "...",
  --     "patient": "...",
  --     "pa": "..."
  --   }
  -- }

  submission_status VARCHAR(50) DEFAULT 'submitted',
  processing_status VARCHAR(50) DEFAULT 'pending',

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL REFERENCES clinics(id),
  email VARCHAR(255) UNIQUE NOT NULL,
  full_name VARCHAR(255),
  role VARCHAR(50) NOT NULL,  -- admin, staff, referrer
  is_active BOOLEAN DEFAULT TRUE,
  last_login_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_type VARCHAR(100) NOT NULL,
  user_id UUID REFERENCES users(id),
  clinic_id UUID,
  resource_type VARCHAR(100),
  resource_id UUID,
  event_data JSONB DEFAULT '{}'::jsonb,
  -- Stores:
  -- {
  --   "action": "READ_PATIENT",
  --   "ip_address": "192.168.1.1",
  --   "user_agent": "Mozilla/5.0...",
  --   "metadata": {...}
  -- }
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE clinics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  settings JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

SELECT set_config('app.user_id', 'user-uuid', true);
SELECT set_config('app.clinic_id', 'clinic-uuid', true);
SELECT set_config('app.role', 'staff', true);

CREATE POLICY patients_clinic_isolation ON patients
  FOR ALL
  USING (clinic_id = current_setting('app.clinic_id')::uuid);

CREATE INDEX idx_patients_clinic ON patients(clinic_id);
CREATE INDEX idx_patients_name ON patients(first_name, last_name);
CREATE INDEX idx_patients_dob ON patients(date_of_birth);

CREATE INDEX idx_submissions_patient ON form_submissions(patient_id);
CREATE INDEX idx_submissions_clinic ON form_submissions(clinic_id);
CREATE INDEX idx_submissions_status ON form_submissions(submission_status);

CREATE INDEX idx_audit_logs_user ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_created ON audit_logs(created_at);
CREATE INDEX idx_audit_logs_event ON audit_logs(event_type);

-- Extract field
SELECT phi_data->>'email' FROM patients;

-- Filter by nested field
WHERE form_data->>'age' = '30';

-- Check if field exists
WHERE processing_results ? 'summaries';