Medication Service — Data Model

Status: populated Owner: TBD Last updated: 2026-04-17 Companion: Service Template · Naming

1. ID Prefixes

Prefix	Entity
`rx_`	Prescription
`dsp_`	DispensingEvent
`stk_`	StockItem
`mar_`	MedicationAdministration
`rec_`	ReconciliationSession
`mli_`	MedicationListItem
`aor_`	AlertOverrideRecord
`phm_`	PharmacyLocation
`med_`	Medication reference entry

IDs are ULIDs with 3-letter prefix (rx_01HX...).

2. TypeScript Domain Interfaces

type PrescriptionStatus =
  | 'draft' | 'proposed' | 'active' | 'on-hold'
  | 'discontinued' | 'completed' | 'partially-dispensed' | 'entered-in-error';

interface Prescription {
  id: PrescriptionId;
  tenantId: TenantId;
  patientId: PatientId;
  encounterId?: EncounterId;
  gatewayMedicationRequestFhirId?: string;
  fhirResourceId?: string;
  prescriberId: UserId;
  medication: MedicationRef;
  sig: Sig;
  quantity: Quantity;
  refillsAuthorized: number;
  refillsDispensed: number;
  isControlled: boolean;
  controlledSchedule?: 'II' | 'III' | 'IV' | 'V';
  dispenseAsWritten: boolean;
  preferredPharmacyId?: PharmacyLocationId;
  status: PrescriptionStatus;
  signedAt?: Date;
  discontinuedAt?: Date;
  discontinueReason?: string;
  version: number;
  createdAt: Date;
  updatedAt: Date;
}

interface DispensingEvent {
  id: DispenseId;
  tenantId: TenantId;
  prescriptionId: PrescriptionId;
  fhirResourceId: string;
  gatewayMedicationDispenseFhirId?: string;
  pharmacistId: UserId;
  counterSignId?: UserId;
  dispensedQuantity: number;
  quantityUnit: string;
  stockItemId: StockItemId;
  lotNumber: string;
  expiryDate: Date;
  dispensedAt: Date;
  isPartial: boolean;
  isReturn: boolean;
  returnReason?: string;
  idempotencyKey: string;
}

interface StockItem {
  id: StockItemId;
  tenantId: TenantId;
  nodeId: NodeId;
  drugCode: string;
  drugName: string;
  lotNumber: string;
  expiryDate: Date;
  quantityOnHand: number;
  reorderPoint: number;
  unitCost?: number;
  currency?: string;
  supplierId?: string;
  isRecalled: boolean;
  recallReason?: string;
  status: 'ACTIVE' | 'QUARANTINED' | 'RECALLED' | 'EXPIRED';
  version: number;
}

interface MedicationAdministration {
  id: MarId;
  tenantId: TenantId;
  prescriptionId: PrescriptionId;
  patientId: PatientId;
  administeredBy: UserId;
  administeredAt: Date;
  action: 'given' | 'held' | 'refused' | 'not-available';
  routeActual?: string;
  reason?: string;
  correctedById?: MarId;
}

interface ReconciliationSession {
  id: ReconciliationId;
  tenantId: TenantId;
  patientId: PatientId;
  encounterId?: EncounterId;
  type: 'admission' | 'transfer' | 'discharge' | 'outpatient';
  performedBy: UserId;
  performedAt?: Date;
  status: 'open' | 'completed' | 'canceled';
  resultSummary?: string;
}

interface AlertOverrideRecord {
  id: AlertOverrideId;
  tenantId: TenantId;
  prescriptionId: PrescriptionId;
  alertType: 'ALLERGY' | 'INTERACTION' | 'FORMULARY' | 'CONTROLLED' | 'DOSE_RANGE' | 'DUPLICATE';
  alertSeverity: 'HIGH' | 'MEDIUM' | 'LOW';
  alertDetail: string;
  overriddenBy: UserId;
  overrideReason: string;
  overriddenAt: Date;
  drugKbVersion: string;
}

3. Postgres Schema

Schema: medication. All tables have tenant_id UUID NOT NULL and RLS.

CREATE SCHEMA IF NOT EXISTS medication;

CREATE TABLE medication.prescriptions (
  id                                TEXT PRIMARY KEY,
  tenant_id                         UUID NOT NULL,
  patient_id                        UUID NOT NULL,
  encounter_id                      UUID,
  gateway_medication_request_fhir_id TEXT,
  fhir_resource_id                  TEXT,
  prescriber_id                     UUID NOT NULL,
  medication_code                   TEXT NOT NULL,
  medication_display                TEXT NOT NULL,
  sig                               JSONB NOT NULL,
  quantity                          NUMERIC(12,3) NOT NULL,
  quantity_unit                     TEXT NOT NULL,
  refills_authorized                INT NOT NULL DEFAULT 0,
  refills_dispensed                 INT NOT NULL DEFAULT 0,
  is_controlled                     BOOLEAN NOT NULL DEFAULT false,
  controlled_schedule               TEXT,
  dispense_as_written               BOOLEAN NOT NULL DEFAULT false,
  preferred_pharmacy_id             TEXT,
  status                            TEXT NOT NULL,
  signed_at                         TIMESTAMPTZ,
  discontinued_at                   TIMESTAMPTZ,
  discontinue_reason                TEXT,
  version                           INT NOT NULL DEFAULT 1,
  created_at                        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_prescriptions_tenant_patient ON medication.prescriptions (tenant_id, patient_id);
CREATE INDEX ix_prescriptions_tenant_status ON medication.prescriptions (tenant_id, status);
CREATE INDEX ix_prescriptions_gateway_mr ON medication.prescriptions (gateway_medication_request_fhir_id);
ALTER TABLE medication.prescriptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY prescriptions_tenant_isolation ON medication.prescriptions
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE TABLE medication.dispensing_events (
  id                                     TEXT PRIMARY KEY,
  tenant_id                              UUID NOT NULL,
  prescription_id                        TEXT NOT NULL REFERENCES medication.prescriptions(id),
  fhir_resource_id                       TEXT,
  gateway_medication_dispense_fhir_id    TEXT,
  pharmacist_id                          UUID NOT NULL,
  counter_sign_id                        UUID,
  dispensed_quantity                     NUMERIC(12,3) NOT NULL,
  quantity_unit                          TEXT NOT NULL,
  stock_item_id                          TEXT NOT NULL,
  lot_number                             TEXT NOT NULL,
  expiry_date                            DATE NOT NULL,
  dispensed_at                           TIMESTAMPTZ NOT NULL DEFAULT now(),
  is_partial                             BOOLEAN NOT NULL DEFAULT false,
  is_return                              BOOLEAN NOT NULL DEFAULT false,
  return_reason                          TEXT,
  idempotency_key                        TEXT NOT NULL
);
CREATE UNIQUE INDEX ix_dispense_idempotency ON medication.dispensing_events (tenant_id, idempotency_key);
CREATE INDEX ix_dispense_prescription ON medication.dispensing_events (tenant_id, prescription_id);
ALTER TABLE medication.dispensing_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY dispensing_events_tenant_isolation ON medication.dispensing_events
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE TABLE medication.stock_items (
  id                 TEXT PRIMARY KEY,
  tenant_id          UUID NOT NULL,
  node_id            UUID NOT NULL,
  drug_code          TEXT NOT NULL,
  drug_name          TEXT NOT NULL,
  lot_number         TEXT NOT NULL,
  expiry_date        DATE NOT NULL,
  quantity_on_hand   NUMERIC(14,3) NOT NULL,
  reorder_point      NUMERIC(14,3) NOT NULL DEFAULT 0,
  unit_cost          NUMERIC(12,2),
  currency           TEXT,
  supplier_id        TEXT,
  is_recalled        BOOLEAN NOT NULL DEFAULT false,
  recall_reason      TEXT,
  status             TEXT NOT NULL DEFAULT 'ACTIVE',
  version            INT NOT NULL DEFAULT 1,
  CHECK (quantity_on_hand >= 0)
);
CREATE UNIQUE INDEX ix_stock_lot ON medication.stock_items (tenant_id, node_id, drug_code, lot_number);
CREATE INDEX ix_stock_reorder ON medication.stock_items (tenant_id, node_id) WHERE quantity_on_hand < reorder_point;
CREATE INDEX ix_stock_expiry ON medication.stock_items (tenant_id, expiry_date);
ALTER TABLE medication.stock_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY stock_items_tenant_isolation ON medication.stock_items
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE TABLE medication.medication_administrations (
  id                 TEXT PRIMARY KEY,
  tenant_id          UUID NOT NULL,
  prescription_id    TEXT NOT NULL,
  patient_id         UUID NOT NULL,
  administered_by    UUID NOT NULL,
  administered_at    TIMESTAMPTZ NOT NULL,
  action             TEXT NOT NULL,
  route_actual       TEXT,
  reason             TEXT,
  corrected_by_id    TEXT
);
CREATE INDEX ix_mar_patient ON medication.medication_administrations (tenant_id, patient_id, administered_at DESC);
ALTER TABLE medication.medication_administrations ENABLE ROW LEVEL SECURITY;
CREATE POLICY mar_tenant_isolation ON medication.medication_administrations
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE TABLE medication.reconciliation_sessions (
  id                 TEXT PRIMARY KEY,
  tenant_id          UUID NOT NULL,
  patient_id         UUID NOT NULL,
  encounter_id       UUID,
  type               TEXT NOT NULL,
  performed_by       UUID NOT NULL,
  performed_at       TIMESTAMPTZ,
  status             TEXT NOT NULL DEFAULT 'open',
  result_summary     JSONB
);
ALTER TABLE medication.reconciliation_sessions ENABLE ROW LEVEL SECURITY;
CREATE POLICY reconciliation_tenant_isolation ON medication.reconciliation_sessions
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE TABLE medication.alert_overrides (
  id                 TEXT PRIMARY KEY,
  tenant_id          UUID NOT NULL,
  prescription_id    TEXT NOT NULL,
  alert_type         TEXT NOT NULL,
  alert_severity     TEXT NOT NULL,
  alert_detail       TEXT NOT NULL,
  overridden_by      UUID NOT NULL,
  override_reason    TEXT NOT NULL CHECK (char_length(override_reason) >= 10),
  overridden_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  drug_kb_version    TEXT NOT NULL
);
CREATE INDEX ix_alert_override_rx ON medication.alert_overrides (tenant_id, prescription_id);
ALTER TABLE medication.alert_overrides ENABLE ROW LEVEL SECURITY;
CREATE POLICY alert_override_tenant_isolation ON medication.alert_overrides
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE TABLE medication.pharmacy_locations (
  id            TEXT PRIMARY KEY,
  tenant_id     UUID NOT NULL,
  node_id       UUID NOT NULL,
  name          TEXT NOT NULL,
  type          TEXT NOT NULL,
  ncpdp_id      TEXT,
  is_active     BOOLEAN NOT NULL DEFAULT true
);

-- Standard outbox/inbox
CREATE TABLE medication.outbox (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  subject TEXT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  delivered_at TIMESTAMPTZ
);
CREATE TABLE medication.inbox (
  event_id TEXT PRIMARY KEY,
  tenant_id UUID NOT NULL,
  received_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

4. Retention

Table	Retention
prescriptions (active)	Indefinite
prescriptions (entered-in-error)	7y + soft delete
dispensing_events	10y (controlled substances); 7y otherwise
stock_items (status=RECALLED/EXPIRED)	7y
alert_overrides	7y
medication_administrations	7y
reconciliation_sessions	7y

5. Partitioning

dispensing_events partitioned by month on dispensed_at once volume exceeds ~20M rows per tenant.
medication_administrations partitioned by month on administered_at.

1. ID Prefixes​

2. TypeScript Domain Interfaces​

3. Postgres Schema​

4. Retention​

5. Partitioning​

1. ID Prefixes

2. TypeScript Domain Interfaces

3. Postgres Schema

4. Retention

5. Partitioning