Laboratory Service — Data Model

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

---

1. ID Prefix Convention

Entity	Prefix	Example
Accession	ACC_	ACC_01J...
Specimen	SPM_	SPM_01J...
LabResult	RES_	RES_01J...
TestCatalogItem	CAT_	CAT_01J...
ResultAcknowledgment	ACK_	ACK_01J...
CriticalValuePolicy	CVP_	CVP_01J...

All IDs are ULIDs (sortable by time, collision-resistant).

---

2. TypeScript Interfaces

export interface Accession {
  id: string;                        // ACC_ prefixed ULID
  tenantId: string;
  accessionNumber: string;           // {tenantShortCode}-{YYYYMMDD}-{seq6}
  patientId: string;
  encounterId?: string;
  orderId?: string;
  priority: 'routine' | 'urgent' | 'stat';
  status: AccessionStatus;
  canceledReason?: string;
  createdAt: Date;
  updatedAt: Date;
  version: number;
}

export type AccessionStatus =
  | 'ordered' | 'collected' | 'received'
  | 'in-process' | 'resulted' | 'verified'
  | 'released' | 'canceled';

export interface Specimen {
  id: string;                        // SPM_ prefixed ULID
  tenantId: string;
  accessionId: string;
  type: string;                      // SNOMED specimen type code
  status: SpecimenStatus;
  collectedAt?: Date;
  collectorId?: string;
  receivedAt?: Date;
  condition?: string;
  volume?: string;
  payloadJson?: object;              // FHIR Specimen resource
  createdAt: Date;
  updatedAt: Date;
}

export type SpecimenStatus =
  | 'ordered' | 'collected' | 'received'
  | 'in-process' | 'canceled';

export interface LabResult {
  id: string;                        // RES_ prefixed ULID
  tenantId: string;
  accessionId: string;
  testCode: string;                  // LOINC or local catalog code
  testDisplay?: string;
  valueQuantity?: number;
  valueUnit?: string;
  valueText?: string;
  referenceRangeLow?: number;
  referenceRangeHigh?: number;
  referenceRangeText?: string;
  abnormalFlag?: string;
  criticalFlag: boolean;
  comments?: string;
  status: LabResultStatus;
  enteredBy: string;
  verifiedBy?: string;
  releasedAt?: Date;
  priorResultId?: string;            // correction chain
  correctionReason?: string;
  fhirObservationId?: string;
  terminologyValidated: boolean;
  payloadJson?: object;              // FHIR Observation resource
  version: number;
  createdAt: Date;
  updatedAt: Date;
}

export type LabResultStatus =
  | 'draft' | 'verified' | 'released'
  | 'corrected' | 'amended' | 'canceled';

export interface ResultAcknowledgment {
  id: string;                        // ACK_ prefixed ULID
  tenantId: string;
  resultId: string;
  ackType: 'reviewed' | 'forwarded' | 'plan-documented' | 'patient-notified';
  ackBy: string;
  ackAt: Date;
  comments?: string;
}

export interface TestCatalogItem {
  id: string;                        // CAT_ prefixed ULID
  tenantId?: string;                 // null = global
  name: string;
  loincCode?: string;
  specimenType: string;
  isPanel: boolean;
  componentCodes?: string[];
  referenceRanges?: ReferenceRange[];
  active: boolean;
  createdAt: Date;
  updatedAt: Date;
}

export interface CriticalValuePolicy {
  id: string;                        // CVP_ prefixed ULID
  tenantId: string;
  testCode: string;
  lowCritical?: number;
  highCritical?: number;
  facilityId?: string;
  escalationWindowMinutes: number;
  active: boolean;
}

---

3. PostgreSQL Schema

CREATE TABLE accessions (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  accession_number TEXT NOT NULL,
  patient_id      TEXT NOT NULL,
  encounter_id    TEXT,
  order_id        TEXT,
  priority        TEXT NOT NULL DEFAULT 'routine',
  status          TEXT NOT NULL DEFAULT 'ordered',
  canceled_reason TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  version         INTEGER NOT NULL DEFAULT 1,
  UNIQUE (tenant_id, accession_number)
);

CREATE INDEX idx_accessions_tenant_status ON accessions(tenant_id, status);
CREATE INDEX idx_accessions_patient ON accessions(tenant_id, patient_id);
CREATE INDEX idx_accessions_order ON accessions(tenant_id, order_id);

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

---

CREATE TABLE specimens (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  accession_id    TEXT NOT NULL REFERENCES accessions(id),
  type            TEXT NOT NULL,
  status          TEXT NOT NULL DEFAULT 'ordered',
  collected_at    TIMESTAMPTZ,
  collector_id    TEXT,
  received_at     TIMESTAMPTZ,
  condition       TEXT,
  volume          TEXT,
  payload_json    JSONB,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_specimens_accession ON specimens(tenant_id, accession_id);
ALTER TABLE specimens ENABLE ROW LEVEL SECURITY;
CREATE POLICY specimens_tenant ON specimens
  USING (tenant_id = current_setting('app.current_tenant_id'));

---

CREATE TABLE lab_results (
  id                      TEXT PRIMARY KEY,
  tenant_id               TEXT NOT NULL,
  accession_id            TEXT NOT NULL REFERENCES accessions(id),
  test_code               TEXT NOT NULL,
  test_display            TEXT,
  value_quantity          NUMERIC,
  value_unit              TEXT,
  value_text              TEXT,
  reference_range_low     NUMERIC,
  reference_range_high    NUMERIC,
  reference_range_text    TEXT,
  abnormal_flag           TEXT,
  critical_flag           BOOLEAN NOT NULL DEFAULT FALSE,
  comments                TEXT,
  status                  TEXT NOT NULL DEFAULT 'draft',
  entered_by              TEXT NOT NULL,
  verified_by             TEXT,
  released_at             TIMESTAMPTZ,
  prior_result_id         TEXT REFERENCES lab_results(id),
  correction_reason       TEXT,
  fhir_observation_id     TEXT,
  terminology_validated   BOOLEAN NOT NULL DEFAULT FALSE,
  payload_json            JSONB,
  version                 INTEGER NOT NULL DEFAULT 1,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_lab_results_accession ON lab_results(tenant_id, accession_id);
CREATE INDEX idx_lab_results_patient ON lab_results(tenant_id, test_code, released_at DESC);
ALTER TABLE lab_results ENABLE ROW LEVEL SECURITY;
CREATE POLICY lab_results_tenant ON lab_results
  USING (tenant_id = current_setting('app.current_tenant_id'));

---

CREATE TABLE result_acknowledgments (
  id          TEXT PRIMARY KEY,
  tenant_id   TEXT NOT NULL,
  result_id   TEXT NOT NULL REFERENCES lab_results(id),
  ack_type    TEXT NOT NULL,
  ack_by      TEXT NOT NULL,
  ack_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  comments    TEXT,
  UNIQUE (tenant_id, result_id, ack_by)
);

CREATE INDEX idx_ack_result ON result_acknowledgments(tenant_id, result_id);
ALTER TABLE result_acknowledgments ENABLE ROW LEVEL SECURITY;
CREATE POLICY ack_tenant ON result_acknowledgments
  USING (tenant_id = current_setting('app.current_tenant_id'));

---

CREATE TABLE test_catalog_items (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT,               -- NULL = global
  name            TEXT NOT NULL,
  loinc_code      TEXT,
  specimen_type   TEXT NOT NULL,
  is_panel        BOOLEAN NOT NULL DEFAULT FALSE,
  component_codes TEXT[],
  reference_ranges JSONB,
  active          BOOLEAN NOT NULL DEFAULT TRUE,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (tenant_id, loinc_code)
);

CREATE INDEX idx_catalog_search ON test_catalog_items
  USING GIN (to_tsvector('english', name));

---

CREATE TABLE critical_value_policies (
  id                        TEXT PRIMARY KEY,
  tenant_id                 TEXT NOT NULL,
  test_code                 TEXT NOT NULL,
  low_critical              NUMERIC,
  high_critical             NUMERIC,
  facility_id               TEXT,
  escalation_window_minutes INTEGER NOT NULL DEFAULT 30,
  active                    BOOLEAN NOT NULL DEFAULT TRUE,
  UNIQUE (tenant_id, test_code, facility_id)
);

---

CREATE TABLE outbox_events (
  id          TEXT PRIMARY KEY,
  tenant_id   TEXT NOT NULL,
  subject     TEXT NOT NULL,
  event_type  TEXT NOT NULL,
  payload     JSONB NOT NULL,
  published   BOOLEAN NOT NULL DEFAULT FALSE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_outbox_unpublished ON outbox_events(published, created_at)
  WHERE published = FALSE;