Terminology Service — Data Model

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

1. ID Prefix Convention

Prefix	Entity
CON_	Concept
DI_	DrugInteraction
DC_	DrugClass
DCON_	DrugContraindication
VS_	ValueSet
CM_	ConceptMap

All IDs are ULIDs with the prefix prepended.

---

2. TypeScript Interfaces

// Concept
export interface Concept {
  id: string;                    // CON_*
  system: string;                // e.g. http://loinc.org
  code: string;
  display: string;
  definition: string | null;
  active: boolean;
  tenantId: string | null;       // null = global
  version: number;               // optimistic lock
  createdAt: Date;
  updatedAt: Date;
}

// DrugInteraction
export interface DrugInteraction {
  id: string;                    // DI_*
  drug1Code: string;             // RxNorm code
  drug2Code: string;             // RxNorm code
  severity: InteractionSeverity; // CONTRAINDICATED | HIGH | MODERATE | LOW
  description: string;
  active: boolean;
  createdAt: Date;
  updatedAt: Date;
}

// DrugClass
export interface DrugClass {
  id: string;                    // DC_*
  rxnormCode: string;
  className: string;
  active: boolean;
  createdAt: Date;
}

// DrugContraindication
export interface DrugContraindication {
  id: string;                    // DCON_*
  rxnormCode: string;
  icd10Code: string;
  severity: InteractionSeverity;
  description: string;
  active: boolean;
  createdAt: Date;
}

// ValueSet
export interface ValueSet {
  id: string;                    // VS_*
  url: string;                   // canonical FHIR URL
  name: string;
  description: string | null;
  systemFilter: string | null;   // if set, expansion is scoped to this system
  active: boolean;
  createdAt: Date;
}

// ValueSetConcept (junction)
export interface ValueSetConcept {
  valueSetId: string;
  conceptId: string;
}

// ConceptMap
export interface ConceptMap {
  id: string;                    // CM_*
  url: string;
  sourceSystem: string;
  targetSystem: string;
  active: boolean;
  createdAt: Date;
}

// ConceptMapEntry
export interface ConceptMapEntry {
  conceptMapId: string;
  sourceCode: string;
  targetCode: string;
  equivalence: string;           // equivalent | wider | narrower | unmatched
}

---

3. PostgreSQL Schema

-- Concepts table (global + tenant-scoped)
CREATE TABLE concepts (
  id          TEXT PRIMARY KEY,
  system      TEXT NOT NULL,
  code        TEXT NOT NULL,
  display     TEXT NOT NULL,
  definition  TEXT,
  active      BOOLEAN NOT NULL DEFAULT TRUE,
  tenant_id   TEXT,              -- NULL = global
  version     INTEGER NOT NULL DEFAULT 1,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Unique: global concept (tenant_id IS NULL)
CREATE UNIQUE INDEX uq_concepts_global
  ON concepts(system, code)
  WHERE tenant_id IS NULL;
-- Unique: tenant-scoped concept
CREATE UNIQUE INDEX uq_concepts_tenant
  ON concepts(tenant_id, system, code)
  WHERE tenant_id IS NOT NULL;
-- Full-text search index
CREATE INDEX idx_concepts_fts
  ON concepts USING gin(to_tsvector('english', display || ' ' || code));
-- Active lookup
CREATE INDEX idx_concepts_system_code_active
  ON concepts(system, code, active);
-- Tenant scope filter
CREATE INDEX idx_concepts_tenant_system
  ON concepts(tenant_id, system, active);

-- RLS: consumers may only see global concepts + own-tenant concepts
ALTER TABLE concepts ENABLE ROW LEVEL SECURITY;
CREATE POLICY concepts_tenant_isolation ON concepts
  USING (tenant_id IS NULL OR tenant_id = current_setting('app.tenant_id', TRUE));

-- Drug interactions
CREATE TABLE drug_interactions (
  id          TEXT PRIMARY KEY,
  drug1_code  TEXT NOT NULL,
  drug2_code  TEXT NOT NULL,
  severity    TEXT NOT NULL,    -- CONTRAINDICATED | HIGH | MODERATE | LOW
  description TEXT NOT NULL,
  active      BOOLEAN NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Pair lookup (canonical order: drug1_code <= drug2_code enforced at app level)
CREATE INDEX idx_drug_interactions_pair
  ON drug_interactions(drug1_code, drug2_code, active);

-- Drug classes
CREATE TABLE drug_classes (
  id           TEXT PRIMARY KEY,
  rxnorm_code  TEXT NOT NULL,
  class_name   TEXT NOT NULL,
  active       BOOLEAN NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_drug_classes_rxnorm
  ON drug_classes(rxnorm_code, active);

-- Drug contraindications
CREATE TABLE drug_contraindications (
  id           TEXT PRIMARY KEY,
  rxnorm_code  TEXT NOT NULL,
  icd10_code   TEXT NOT NULL,
  severity     TEXT NOT NULL,
  description  TEXT NOT NULL,
  active       BOOLEAN NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_drug_contraindications_drug
  ON drug_contraindications(rxnorm_code, active);

-- ValueSets
CREATE TABLE value_sets (
  id            TEXT PRIMARY KEY,
  url           TEXT NOT NULL UNIQUE,
  name          TEXT NOT NULL,
  description   TEXT,
  system_filter TEXT,
  active        BOOLEAN NOT NULL DEFAULT TRUE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ValueSet → Concept membership
CREATE TABLE value_set_concepts (
  value_set_id  TEXT NOT NULL REFERENCES value_sets(id),
  concept_id    TEXT NOT NULL REFERENCES concepts(id),
  PRIMARY KEY (value_set_id, concept_id)
);

-- Concept maps
CREATE TABLE concept_maps (
  id             TEXT PRIMARY KEY,
  url            TEXT NOT NULL UNIQUE,
  source_system  TEXT NOT NULL,
  target_system  TEXT NOT NULL,
  active         BOOLEAN NOT NULL DEFAULT TRUE,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Concept map entries
CREATE TABLE concept_map_entries (
  concept_map_id  TEXT NOT NULL REFERENCES concept_maps(id),
  source_code     TEXT NOT NULL,
  target_code     TEXT NOT NULL,
  equivalence     TEXT NOT NULL,
  PRIMARY KEY (concept_map_id, source_code)
);
CREATE INDEX idx_cme_map_source ON concept_map_entries(concept_map_id, source_code);

-- Outbox (transactional)
CREATE TABLE outbox (
  id          TEXT PRIMARY KEY,
  tenant_id   TEXT,
  subject     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(published, created_at) WHERE published = FALSE;

---

4. Indexes Summary

Table	Index	Purpose
concepts	uq_concepts_global	Prevent duplicate global codes
concepts	uq_concepts_tenant	Prevent duplicate tenant codes
concepts	idx_concepts_fts	Full-text search on display + code
concepts	idx_concepts_system_code_active	Exact lookup
drug_interactions	idx_drug_interactions_pair	Interaction check by pair
drug_classes	idx_drug_classes_rxnorm	Class lookup by RxNorm code
drug_contraindications	idx_drug_contraindications_drug	Contraindication check

---

5. Data Residency

Terminology data (SNOMED, LOINC, RxNorm, ICD-10) is global, non-PHI and may reside in any region. Tenant-scoped custom concepts contain no PHI and follow the tenant's data-residency configuration. Drug interaction and contraindication tables are global (no tenant partitioning). Licensed terminology data is never committed to the repository; it is loaded at deployment time via the ETL pipeline.