Database Schema

​

Architecture

​

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