# Database Schema

## ER Diagram

```mermaid
erDiagram
  tenants ||--o{ pharmacies : owns
  tenants ||--o{ users : contains
  roles ||--o{ user_roles : grants
  users ||--o{ user_roles : has
  pharmacies ||--o{ inventory_stock_lots : stores
  products ||--o{ inventory_stock_lots : stocked_as
  suppliers ||--o{ purchase_orders : receives
  purchase_orders ||--o{ purchase_order_items : contains
  products ||--o{ purchase_order_items : ordered
  patients ||--o{ prescriptions : receives
  doctors ||--o{ prescriptions : writes
  prescriptions ||--o{ prescription_items : contains
  products ||--o{ prescription_items : dispenses
  patients ||--o{ patient_allergies : has
  patients ||--o{ patient_medications : takes
  prescriptions ||--o{ clinical_alerts : triggers
  pharmacies ||--o{ pos_orders : processes
  pos_orders ||--o{ pos_order_items : contains
  products ||--o{ pos_order_items : sold
  tenants ||--o{ audit_logs : records
```

## Core SQL

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TYPE user_status AS ENUM ('active', 'invited', 'suspended');
CREATE TYPE prescription_status AS ENUM ('received', 'under_review', 'approved', 'dispensed', 'rejected');
CREATE TYPE alert_severity AS ENUM ('info', 'warning', 'critical');

CREATE TABLE tenants (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  name varchar(180) NOT NULL,
  country_code char(2) NOT NULL,
  default_locale varchar(10) NOT NULL DEFAULT 'ar-SA',
  data_residency_region varchar(80) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE pharmacies (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  name varchar(180) NOT NULL,
  license_number varchar(80) NOT NULL,
  city varchar(120) NOT NULL,
  timezone varchar(80) NOT NULL DEFAULT 'Asia/Riyadh',
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, license_number)
);

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  pharmacy_id uuid REFERENCES pharmacies(id),
  email citext NOT NULL,
  phone varchar(40),
  full_name varchar(180) NOT NULL,
  password_hash text NOT NULL,
  status user_status NOT NULL DEFAULT 'active',
  two_factor_enabled boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, email)
);

CREATE TABLE roles (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  code varchar(80) UNIQUE NOT NULL,
  name_en varchar(120) NOT NULL,
  name_ar varchar(120) NOT NULL
);

CREATE TABLE user_roles (
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id uuid NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, role_id)
);

CREATE TABLE products (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  sku varchar(80) NOT NULL,
  barcode varchar(80),
  qr_code varchar(160),
  name_en varchar(220) NOT NULL,
  name_ar varchar(220) NOT NULL,
  active_ingredient varchar(220),
  dosage_form varchar(120),
  strength varchar(80),
  storage_type varchar(40) NOT NULL DEFAULT 'ambient',
  reorder_point integer NOT NULL DEFAULT 0,
  controlled_drug boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, sku),
  UNIQUE (tenant_id, barcode)
);

CREATE TABLE suppliers (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  name varchar(180) NOT NULL,
  api_endpoint text,
  contact_email citext,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE inventory_stock_lots (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  pharmacy_id uuid NOT NULL REFERENCES pharmacies(id),
  product_id uuid NOT NULL REFERENCES products(id),
  supplier_id uuid REFERENCES suppliers(id),
  lot_number varchar(100) NOT NULL,
  rfid_tag varchar(160),
  expiry_date date NOT NULL,
  quantity_on_hand integer NOT NULL CHECK (quantity_on_hand >= 0),
  reserved_quantity integer NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0),
  unit_cost numeric(12, 4),
  received_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, pharmacy_id, product_id, lot_number)
);

CREATE INDEX idx_stock_lots_expiry ON inventory_stock_lots (tenant_id, pharmacy_id, expiry_date);
CREATE INDEX idx_stock_lots_product ON inventory_stock_lots (tenant_id, product_id);

CREATE TABLE patients (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  national_id_hash text,
  full_name varchar(180) NOT NULL,
  date_of_birth date,
  phone varchar(40),
  preferred_locale varchar(10) NOT NULL DEFAULT 'ar-SA',
  consent_version varchar(40),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE doctors (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  full_name varchar(180) NOT NULL,
  license_number varchar(80) NOT NULL,
  organization_name varchar(180),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, license_number)
);

CREATE TABLE prescriptions (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  patient_id uuid NOT NULL REFERENCES patients(id),
  doctor_id uuid NOT NULL REFERENCES doctors(id),
  pharmacy_id uuid REFERENCES pharmacies(id),
  external_reference varchar(120),
  status prescription_status NOT NULL DEFAULT 'received',
  electronic_signature text,
  received_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE prescription_items (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  prescription_id uuid NOT NULL REFERENCES prescriptions(id) ON DELETE CASCADE,
  product_id uuid REFERENCES products(id),
  drug_name varchar(220) NOT NULL,
  dose varchar(120) NOT NULL,
  route varchar(80),
  frequency varchar(120),
  duration_days integer,
  substitution_allowed boolean NOT NULL DEFAULT true
);

CREATE TABLE clinical_alerts (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  prescription_id uuid NOT NULL REFERENCES prescriptions(id),
  severity alert_severity NOT NULL,
  alert_type varchar(80) NOT NULL,
  message text NOT NULL,
  acknowledged_by uuid REFERENCES users(id),
  acknowledged_at timestamptz
);

CREATE TABLE audit_logs (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  actor_user_id uuid REFERENCES users(id),
  action varchar(120) NOT NULL,
  entity_type varchar(120) NOT NULL,
  entity_id uuid,
  ip_address inet,
  user_agent text,
  metadata jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now()
);
```

## Row-Level Security Pattern

```sql
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_products
ON products
USING (tenant_id = current_setting('app.tenant_id')::uuid);
```