Patient Chart Service — Data Model

Status: populated Owner: TBD Last updated: 2026-04-17 Companion: Service Template · NAMING · 13 Security/Compliance/Tenancy

1. ID prefixes

Prefix	Entity
`prb_`	Problem
`alg_`	Allergy
`vit_`	VitalsSet
`obs_`	Observation (vital-signs)
`note_`	ClinicalNote
`sec_`	NoteSection
`add_`	NoteAddendum
`sig_`	NoteSignature
`acc_`	ChartAccess (break-glass / sensitive)
`prv_`	NoteAIProvenance
`ref_`	AllergyReaction

IDs are ULIDs with service prefix (e.g., prb_01HZYXW...).

2. TypeScript interfaces (domain shape)

// domain/problem/problem.ts
export interface Problem {
  id: ProblemId;
  tenantId: TenantId;
  patientId: PatientId;
  encounterId?: EncounterId;
  code: CodeableConcept;
  clinicalStatus: 'active' | 'inactive' | 'resolved';
  verificationStatus: 'unconfirmed' | 'provisional' | 'differential' | 'confirmed' | 'refuted' | 'entered-in-error';
  category?: Coding[];
  severity?: 'mild' | 'moderate' | 'severe';
  onsetDate?: string;
  abatementDate?: string;
  recordedBy: PractitionerId;
  recordedAt: string;
  version: number;
  history: ProblemHistoryEntry[];
}

// domain/allergy/allergy.ts
export interface Allergy {
  id: AllergyId;
  tenantId: TenantId;
  patientId: PatientId;
  substance: CodeableConcept;
  categories: Array<'medication'|'food'|'environment'|'biologic'|'other'>;
  clinicalStatus: 'active' | 'inactive' | 'resolved';
  verificationStatus: 'unconfirmed' | 'confirmed' | 'refuted' | 'entered-in-error';
  reactions: AllergyReaction[];
  assertion: 'none' | 'nka' | 'nkda';
  recordedBy: PractitionerId;
  recordedAt: string;
  version: number;
}

// domain/vitals/vitals-set.ts
export interface VitalsSet {
  id: VitalsSetId;
  tenantId: TenantId;
  patientId: PatientId;
  encounterId?: EncounterId;
  collectionLocationId?: LocationId;
  recordedBy: PractitionerId;
  recordedAt: string;
  method?: 'manual' | 'device';
  deviceId?: string;
  measurements: Observation[];
  version: number;
}

// domain/notes/clinical-note.ts
export interface ClinicalNote {
  id: NoteId;
  tenantId: TenantId;
  patientId: PatientId;
  encounterId?: EncounterId;
  templateId: string;
  sections: NoteSection[];
  signatures: NoteSignature[];
  addenda: NoteAddendum[];
  aiProvenance: NoteAIProvenance[];
  authorId: PractitionerId;
  status: 'draft' | 'pending_cosign' | 'signed' | 'amended' | 'entered-in-error';
  createdAt: string;
  signedAt?: string;
  version: number;
}

3. Postgres schema (Drizzle / SQL)

All tables live under schema patient_chart. RLS is enabled on every table; policy binds to current_setting('app.tenant_id'). All tables carry tenant_id UUID NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), and version INTEGER NOT NULL DEFAULT 1.

3.1 Problems

CREATE TABLE patient_chart.problems (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  patient_id UUID NOT NULL,
  encounter_id UUID,
  code JSONB NOT NULL,
  clinical_status TEXT NOT NULL CHECK (clinical_status IN ('active','inactive','resolved')),
  verification_status TEXT NOT NULL,
  category JSONB,
  severity TEXT CHECK (severity IN ('mild','moderate','severe')),
  onset_date DATE,
  abatement_date DATE,
  recorded_by UUID NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL,
  notes TEXT,
  version INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ix_problems_tenant_patient_status ON patient_chart.problems (tenant_id, patient_id, clinical_status);
CREATE INDEX ix_problems_tenant_recorded_at ON patient_chart.problems (tenant_id, recorded_at DESC);
CREATE INDEX ix_problems_code_gin ON patient_chart.problems USING GIN (code jsonb_path_ops);

CREATE POLICY problems_tenant_isolation ON patient_chart.problems
  USING (tenant_id::text = current_setting('app.tenant_id'));
ALTER TABLE patient_chart.problems ENABLE ROW LEVEL SECURITY;

CREATE TABLE patient_chart.problem_history (
  id UUID PRIMARY KEY,
  problem_id UUID NOT NULL REFERENCES patient_chart.problems(id),
  tenant_id UUID NOT NULL,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  changed_by UUID NOT NULL,
  change_type TEXT NOT NULL,
  change_payload JSONB NOT NULL
);
CREATE INDEX ix_problem_history_problem ON patient_chart.problem_history (problem_id, changed_at DESC);

3.2 Allergies

CREATE TABLE patient_chart.allergies (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  patient_id UUID NOT NULL,
  substance JSONB NOT NULL,
  substance_norm_text TEXT, -- normalized free-text for duplicate check
  categories TEXT[] NOT NULL,
  clinical_status TEXT NOT NULL,
  verification_status TEXT NOT NULL,
  assertion TEXT NOT NULL DEFAULT 'none' CHECK (assertion IN ('none','nka','nkda')),
  reactions JSONB NOT NULL DEFAULT '[]'::jsonb,
  highest_severity TEXT,
  recorded_by UUID NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL,
  version INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, patient_id, substance_norm_text) -- partial index below for active-only
);

CREATE UNIQUE INDEX ix_allergies_active_unique ON patient_chart.allergies (tenant_id, patient_id, (substance->'codings'->0->>'code'))
  WHERE clinical_status = 'active';

CREATE INDEX ix_allergies_patient_status ON patient_chart.allergies (tenant_id, patient_id, clinical_status);

CREATE POLICY allergies_tenant_isolation ON patient_chart.allergies
  USING (tenant_id::text = current_setting('app.tenant_id'));
ALTER TABLE patient_chart.allergies ENABLE ROW LEVEL SECURITY;

3.3 Vitals

CREATE TABLE patient_chart.vitals_sets (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  patient_id UUID NOT NULL,
  encounter_id UUID,
  collection_location_id UUID,
  recorded_by UUID NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL,
  method TEXT,
  device_id TEXT,
  version INTEGER NOT NULL DEFAULT 1,
  superseded_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ix_vitals_sets_patient_recorded ON patient_chart.vitals_sets (tenant_id, patient_id, recorded_at DESC);

CREATE TABLE patient_chart.observations (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  vitals_set_id UUID NOT NULL REFERENCES patient_chart.vitals_sets(id),
  patient_id UUID NOT NULL,
  code JSONB NOT NULL,
  value NUMERIC NOT NULL,
  unit TEXT NOT NULL,
  abnormal_flag TEXT,
  derived_from UUID,
  effective_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_observations_patient_code_time ON patient_chart.observations (tenant_id, patient_id, (code->'codings'->0->>'code'), effective_at DESC);
CREATE INDEX ix_observations_vset ON patient_chart.observations (vitals_set_id);

ALTER TABLE patient_chart.vitals_sets ENABLE ROW LEVEL SECURITY;
ALTER TABLE patient_chart.observations ENABLE ROW LEVEL SECURITY;
CREATE POLICY vitals_sets_tenant_isolation ON patient_chart.vitals_sets USING (tenant_id::text = current_setting('app.tenant_id'));
CREATE POLICY observations_tenant_isolation ON patient_chart.observations USING (tenant_id::text = current_setting('app.tenant_id'));

3.4 Clinical notes

CREATE TABLE patient_chart.clinical_notes (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  patient_id UUID NOT NULL,
  encounter_id UUID,
  template_id TEXT NOT NULL,
  author_id UUID NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('draft','pending_cosign','signed','amended','entered-in-error')),
  signed_at TIMESTAMPTZ,
  signed_by UUID,
  document_reference_id UUID, -- pointer into document-service
  version INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_notes_patient_time ON patient_chart.clinical_notes (tenant_id, patient_id, created_at DESC);
CREATE INDEX ix_notes_author_status ON patient_chart.clinical_notes (tenant_id, author_id, status);

CREATE TABLE patient_chart.note_sections (
  id UUID PRIMARY KEY,
  note_id UUID NOT NULL REFERENCES patient_chart.clinical_notes(id),
  tenant_id UUID NOT NULL,
  section_key TEXT NOT NULL,
  body_text TEXT,
  body_structured JSONB,
  position INTEGER NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (note_id, section_key)
);

CREATE TABLE patient_chart.note_signatures (
  id UUID PRIMARY KEY,
  note_id UUID NOT NULL,
  tenant_id UUID NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('author','cosigner','witness')),
  signer_id UUID NOT NULL,
  signed_at TIMESTAMPTZ NOT NULL,
  signature_method TEXT NOT NULL
);

CREATE TABLE patient_chart.note_addenda (
  id UUID PRIMARY KEY,
  note_id UUID NOT NULL,
  tenant_id UUID NOT NULL,
  author_id UUID NOT NULL,
  body_text TEXT NOT NULL,
  reason TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE patient_chart.note_ai_provenance (
  id UUID PRIMARY KEY,
  note_id UUID NOT NULL,
  tenant_id UUID NOT NULL,
  section_key TEXT NOT NULL,
  feature TEXT NOT NULL,
  model_version TEXT NOT NULL,
  actor_id UUID NOT NULL,
  accepted_at TIMESTAMPTZ NOT NULL,
  content_hash TEXT NOT NULL
);

CREATE TABLE patient_chart.note_read_receipts (
  note_id UUID NOT NULL,
  tenant_id UUID NOT NULL,
  user_id UUID NOT NULL,
  first_read_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (note_id, user_id)
);

ALTER TABLE patient_chart.clinical_notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY notes_tenant_isolation ON patient_chart.clinical_notes USING (tenant_id::text = current_setting('app.tenant_id'));
-- (analogous policies on section/signature/addenda/ai_provenance/read_receipts)

3.5 Chart access

CREATE TABLE patient_chart.chart_access (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  patient_id UUID NOT NULL,
  actor_id UUID NOT NULL,
  event_type TEXT NOT NULL CHECK (event_type IN ('breakglass','sensitive_access','item_opened','snapshot_exported')),
  reason TEXT,
  segment TEXT,
  policy_result TEXT,
  duration_minutes INTEGER,
  at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_chart_access_tenant_patient ON patient_chart.chart_access (tenant_id, patient_id, at DESC);

3.6 Outbox / Inbox (standard)

CREATE TABLE patient_chart.outbox (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  subject TEXT NOT NULL,
  envelope JSONB NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','dispatched','failed')),
  attempt_count INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  dispatched_at TIMESTAMPTZ
);
CREATE INDEX ix_outbox_pending ON patient_chart.outbox (status, created_at) WHERE status = 'pending';

CREATE TABLE patient_chart.inbox (
  event_id UUID PRIMARY KEY,
  subject TEXT NOT NULL,
  tenant_id UUID NOT NULL,
  processed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

4. Index strategy

Patient+status+time is the dominant query shape. All aggregates carry such an index.
JSONB GIN for codings where search by code is hot (problems).
Partial uniqueness on active allergies prevents duplicates (§3.2).
Observations indexed on (tenant, patient, code, effective_at) — vitals trend is the dominant workload.

5. Encryption classes

Column type	Class
`notes` (free text, clinical), section bodies, addenda bodies	Class C (encrypted at rest via PG TDE / volume crypto)
IDs, tenant_id	Class A (not encrypted; column-scoped)
`code`, `substance`, `category` JSONB	Class B (pseudonymous when exported)

6. Retention

Table	Retention	Rationale
`problems`, `allergies`, `vitals_sets`, `observations`, `clinical_notes`, `note_*`	10 years (or per jurisdiction)	Clinical legal record
`outbox` (dispatched)	14 days	Operational
`inbox`	30 days	Dedupe
`chart_access` (breakglass, sensitive, snapshot_exported)	7 years	Audit
`chart_access` (item_opened)	1 year	Fine-grained audit, shorter retention per policy

7. Open Questions

Per-tenant override of retention policy — config-service scope?
Partial archive of very old observations to cold storage (>5y) with pointer rows — decision deferred.

1. ID prefixes​

2. TypeScript interfaces (domain shape)​

3. Postgres schema (Drizzle / SQL)​

3.1 Problems​

3.2 Allergies​

3.3 Vitals​

3.4 Clinical notes​

3.5 Chart access​

3.6 Outbox / Inbox (standard)​

4. Index strategy​

5. Encryption classes​

6. Retention​

7. Open Questions​