> ## Documentation Index
> Fetch the complete documentation index at: https://docs.adhdsimple.co.uk/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Schema

> Database architecture with entity relationships

## Architecture

```mermaid theme={null}
erDiagram
    users {
        uuid id PK
        string email
        string full_name
        string role
        boolean mfa_enabled
        timestamp created_at
    }
    
    patients {
        uuid id PK
        string nhs_number
        string full_name
        date date_of_birth
        string clinical_pathway
        uuid assigned_clinician FK
        timestamp created_at
    }
    
    prescriptions {
        uuid id PK
        uuid patient_id FK
        uuid prescriber_id FK
        string medication_name
        string dosage
        date prescribed_date
        date valid_until
        string status
        timestamp created_at
    }
    
    health_metrics {
        uuid id PK
        uuid patient_id FK
        decimal weight_kg
        integer systolic_bp
        integer diastolic_bp
        integer pulse_bpm
        text symptoms
        date recorded_date
        timestamp created_at
    }
    
    shipments {
        uuid id PK
        uuid prescription_id FK
        string dhl_tracking_number
        string delivery_address
        string status
        date shipping_date
        date delivered_date
        timestamp created_at
    }
    
    audit_logs {
        uuid id PK
        uuid user_id FK
        string action
        string table_name
        uuid record_id
        jsonb old_values
        jsonb new_values
        timestamp created_at
    }
    
    users ||--o{ patients : "assigned_clinician"
    patients ||--o{ prescriptions : "patient_id"
    users ||--o{ prescriptions : "prescriber_id"
    patients ||--o{ health_metrics : "patient_id"
    prescriptions ||--o{ shipments : "prescription_id"
    users ||--o{ audit_logs : "user_id"
```

## Core Tables

### users

* **Purpose**: Clinician accounts and authentication
* **Technology**: Supabase Auth integration
* **Key Functions**:
  * Role-based access control
  * 2FA enforcement
  * Session management

### patients

* **Purpose**: ADHD patient records
* **Technology**: PostgreSQL with RLS policies
* **Key Functions**:
  * NHS number uniqueness
  * Clinical pathway tracking
  * Clinician assignment

### prescriptions

* **Purpose**: Medication prescriptions and orders
* **Technology**: PostgreSQL with audit triggers
* **Key Functions**:
  * 28-day prescription cycles
  * Status tracking
  * Controlled drug compliance

## Clinical Data

### health\_metrics

* **Purpose**: Patient monitoring data
* **Technology**: PostgreSQL with time-series indexing
* **Key Functions**:
  * Weight, BP, pulse tracking
  * Symptom recording
  * Clinical decision support

### shipments

* **Purpose**: DHL delivery tracking
* **Technology**: PostgreSQL with DHL webhook updates
* **Key Functions**:
  * Tracking number storage
  * Delivery status updates
  * Address validation

## Audit System

### audit\_logs

* **Purpose**: Complete audit trail for compliance
* **Technology**: PostgreSQL triggers on all tables
* **Key Functions**:
  * Before/after value capture
  * User action tracking
  * NHS compliance reporting

<Note>
  **All tables use Row Level Security (RLS) policies for data protection**
</Note>