Ghasi e-Prescribing Gateway Service — Data Model

Status: populated Owner: TBD Last updated: 2026-04-18 Companion: Service Template · 03 platform-services · 02 DDD

ID Prefix Registry (this service)

Prefix	Entity
`mr_`	MedicationRequest (interop copy)
`md_`	MedicationDispense (interop copy)
`sub_`	SubscriptionChannel
`wtk_`	WorkflowTask
`prx_`	PrescriptionBusinessId (correlation)
`idm_`	IdempotencyRecord

All IDs are ULIDs. All tables are prefixed gepgw_ per ADR-0044.

TypeScript Interfaces

export interface PrescriptionRecord {
  id: string;                      // mr_ULID
  tenantId: string;
  prescriptionBusinessId: string;  // prx_ULID — correlation ID
  patientId: string;
  prescriberId: string;            // practitioner reference
  targetOrgId: string;             // resolved pharmacy org
  targetEndpointId: string;        // resolved endpoint
  fhirResource: unknown;           // JSONB — full MedicationRequest R4
  fhirResourceVersion: string;     // ETag token
  status: string;                  // FHIR MR status
  idempotencyKey?: string;
  payloadFingerprint?: string;
  authoredOn: Date;
  createdAt: Date;
  updatedAt: Date;
}

export interface DispenseRecord {
  id: string;                      // md_ULID
  tenantId: string;
  medicationRequestId: string;     // mr_ULID
  prescriptionBusinessId: string;  // prx_ULID
  patientId: string;
  pharmacistId: string;
  fhirResource: unknown;           // JSONB — full MedicationDispense R4
  fhirResourceVersion: string;     // ETag
  status: string;
  idempotencyKey?: string;
  whenHandedOver?: Date;
  createdAt: Date;
  updatedAt: Date;
}

export interface SubscriptionChannel {
  id: string;                      // sub_ULID
  tenantId: string;
  subscriberClientId: string;      // B2B client ID
  criteria: string;                // FHIR search criteria string
  endpointUrl: string;
  signingKeyId: string;
  status: string;                  // active, error, off
  lastDeliveredAt?: Date;
  cursor?: string;                 // last event sequence
  createdAt: Date;
  updatedAt: Date;
}

export interface IdempotencyRecord {
  id: string;                      // idm_ULID
  tenantId: string;
  idempotencyKey: string;
  payloadFingerprint: string;
  resourceId: string;              // created resource ID
  resourceType: string;
  outcome: string;                 // success, error
  responseStatus: number;
  expiresAt: Date;
  createdAt: Date;
}

Postgres Schema

-- All tables prefixed gepgw_ per ADR-0044

CREATE TABLE gepgw_medication_requests (
  id                      TEXT        PRIMARY KEY,        -- mr_ULID
  tenant_id               TEXT        NOT NULL,
  prescription_business_id TEXT       NOT NULL UNIQUE,    -- prx_ULID
  patient_id              TEXT        NOT NULL,
  prescriber_id           TEXT        NOT NULL,
  target_org_id           TEXT        NOT NULL,
  target_endpoint_id      TEXT        NOT NULL,
  fhir_resource           JSONB       NOT NULL,
  fhir_resource_version   TEXT        NOT NULL,           -- ETag token
  status                  TEXT        NOT NULL,
  idempotency_key         TEXT,
  payload_fingerprint     TEXT,
  authored_on             DATE,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ix_gepgw_mr_tenant_patient ON gepgw_medication_requests (tenant_id, patient_id);
CREATE INDEX ix_gepgw_mr_tenant_status  ON gepgw_medication_requests (tenant_id, status);
CREATE INDEX ix_gepgw_mr_business_id    ON gepgw_medication_requests (prescription_business_id);
CREATE UNIQUE INDEX ix_gepgw_mr_idempotency ON gepgw_medication_requests (tenant_id, idempotency_key)
  WHERE idempotency_key IS NOT NULL;

ALTER TABLE gepgw_medication_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY gepgw_mr_tenant_isolation ON gepgw_medication_requests
  USING (tenant_id = current_setting('app.current_tenant_id'));

-- ============================================================

CREATE TABLE gepgw_medication_dispenses (
  id                      TEXT        PRIMARY KEY,        -- md_ULID
  tenant_id               TEXT        NOT NULL,
  medication_request_id   TEXT        NOT NULL REFERENCES gepgw_medication_requests(id),
  prescription_business_id TEXT       NOT NULL,
  patient_id              TEXT        NOT NULL,
  pharmacist_id           TEXT,
  fhir_resource           JSONB       NOT NULL,
  fhir_resource_version   TEXT        NOT NULL,
  status                  TEXT        NOT NULL,
  idempotency_key         TEXT,
  when_handed_over        TIMESTAMPTZ,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ix_gepgw_md_tenant_mr ON gepgw_medication_dispenses (tenant_id, medication_request_id);
CREATE INDEX ix_gepgw_md_business_id ON gepgw_medication_dispenses (prescription_business_id);

ALTER TABLE gepgw_medication_dispenses ENABLE ROW LEVEL SECURITY;
CREATE POLICY gepgw_md_tenant_isolation ON gepgw_medication_dispenses
  USING (tenant_id = current_setting('app.current_tenant_id'));

-- ============================================================

CREATE TABLE gepgw_subscriptions (
  id                  TEXT        PRIMARY KEY,
  tenant_id           TEXT        NOT NULL,
  subscriber_client_id TEXT       NOT NULL,
  criteria            TEXT        NOT NULL,
  endpoint_url        TEXT        NOT NULL,
  signing_key_id      TEXT        NOT NULL,
  status              TEXT        NOT NULL DEFAULT 'active',
  last_delivered_at   TIMESTAMPTZ,
  cursor              TEXT,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ix_gepgw_sub_tenant_client ON gepgw_subscriptions (tenant_id, subscriber_client_id);

ALTER TABLE gepgw_subscriptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY gepgw_sub_tenant_isolation ON gepgw_subscriptions
  USING (tenant_id = current_setting('app.current_tenant_id'));

-- ============================================================

CREATE TABLE gepgw_idempotency_records (
  id                  TEXT        PRIMARY KEY,
  tenant_id           TEXT        NOT NULL,
  idempotency_key     TEXT        NOT NULL,
  payload_fingerprint TEXT        NOT NULL,
  resource_id         TEXT        NOT NULL,
  resource_type       TEXT        NOT NULL,
  outcome             TEXT        NOT NULL DEFAULT 'success',
  response_status     INTEGER     NOT NULL,
  expires_at          TIMESTAMPTZ NOT NULL,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ix_gepgw_idm_key ON gepgw_idempotency_records (tenant_id, idempotency_key);
CREATE INDEX ix_gepgw_idm_expiry ON gepgw_idempotency_records (expires_at);

-- ============================================================

CREATE TABLE gepgw_outbox (
  id              TEXT        PRIMARY KEY,
  tenant_id       TEXT        NOT NULL,
  event_type      TEXT        NOT NULL,
  payload         JSONB       NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at    TIMESTAMPTZ,
  status          TEXT        NOT NULL DEFAULT 'pending'
);

CREATE INDEX ix_gepgw_outbox_pending ON gepgw_outbox (status, created_at) WHERE status = 'pending';

Migrations

Migration file	Description
`20260418000001_gepgw_core_tables.sql`	gepgw_medication_requests, gepgw_medication_dispenses, gepgw_subscriptions
`20260418000002_gepgw_idempotency.sql`	gepgw_idempotency_records
`20260418000003_gepgw_outbox.sql`	gepgw_outbox
`20260418000004_gepgw_rls_policies.sql`	RLS policies on all tables
`20260418000005_gepgw_workflow_tasks.sql`	gepgw_workflow_tasks (Phase 1)

ID Prefix Registry (this service)​

TypeScript Interfaces​

Postgres Schema​

Migrations​

ID Prefix Registry (this service)

TypeScript Interfaces

Postgres Schema

Migrations