Population Health Service — Data Model

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

1. ID Prefix Registry

Prefix	Entity
coh_	PopulationCohort
cm_	CohortMembership snapshot
reg_	DiseaseRegistryEntry
rsk_	RiskScore
ors_	OutreachList
ori_	OutreachItem
qms_	QualityMetricSnapshot
hmis_	HmisExportJob
exp_	DeidentExportJob
rjob_	RefreshJob (transient, cleans up after TTL)

All IDs are ULIDs with the prefix above.

2. TypeScript Interfaces

// population-cohort.ts
export type PopulationCohortId = Branded<string, 'PopulationCohortId'>;

export interface PopulationCohort {
  id: PopulationCohortId;
  tenantId: string;
  nodeId: string;
  name: string;
  description: string | null;
  definitionJson: CohortExpressionNode;
  version: number;
  status: 'Draft' | 'Active' | 'Refreshing' | 'Archived';
  isShared: boolean;
  ownerId: string;
  refreshPolicy: 'manual' | 'schedule' | 'on_source_change';
  refreshCronExpr: string | null;
  lastEvaluatedAt: Date | null;
  membershipCount: number;
  createdAt: Date;
  updatedAt: Date;
}

export interface CohortExpressionNode {
  op: 'AND' | 'OR' | 'NOT' | 'LEAF';
  children?: CohortExpressionNode[];
  field?: string;
  operator?: 'in' | 'not_in' | '=' | '!=' | '>' | '>=' | '<' | '<=';
  value?: unknown;
}

// disease-registry-entry.ts
export type RegistryType =
  | 'tb' | 'malaria' | 'mch' | 'diabetes' | 'hypertension'
  | 'copd' | 'asthma' | 'chf' | 'ckd' | 'mental_health' | 'obesity';

export interface DiseaseRegistryEntry {
  id: string;
  tenantId: string;
  nodeId: string;
  patientId: string;
  registryType: RegistryType;
  controlStatus: 'controlled' | 'uncontrolled' | 'unknown';
  riskTier: 'low' | 'medium' | 'high' | 'critical';
  lastVisitAt: Date | null;
  lastLabAt: Date | null;
  activeGapFlags: string[];
  lastSyncedAt: Date;
}

// risk-score.ts
export interface RiskScore {
  id: string;
  tenantId: string;
  nodeId: string;
  patientId: string;
  modelKey: string;
  computedScore: number;
  computedTier: 'low' | 'medium' | 'high' | 'critical';
  driversJson: Record<string, number>;
  computedAt: Date;
  overrideTier: 'low' | 'medium' | 'high' | 'critical' | null;
  overrideReason: string | null;
  overriddenAt: Date | null;
  overriddenBy: string | null;
}

// quality-metric-snapshot.ts
export interface QualityMetricSnapshot {
  id: string;
  tenantId: string;
  nodeId: string;
  metricKey: string;
  program: 'hedis' | 'qof' | 'ohip_qip' | 'moph_custom' | 'donor';
  periodStart: Date;
  periodEnd: Date;
  numerator: number;
  denominator: number;
  exclusions: number;
  rate: number | null;
  computedAt: Date;
}

// hmis-export-job.ts
export interface HmisExportJob {
  id: string;
  tenantId: string;
  indicatorFamily: string;
  periodStart: Date;
  periodEnd: Date;
  status: 'queued' | 'running' | 'completed' | 'failed' | 'retrying';
  retryCount: number;
  dhis2Reference: string | null;
  completedAt: Date | null;
  failureReason: string | null;
  createdAt: Date;
}

// deident-export-job.ts
export interface DeidentExportJob {
  id: string;
  tenantId: string;
  requesterId: string;
  cohortId: string;
  purpose: string;
  irbReference: string | null;
  format: 'csv' | 'fhir_ndjson';
  status: 'queued' | 'running' | 'deidentifying' | 'completed' | 'failed';
  kValue: number;
  dpEpsilon: number;
  rowCount: number | null;
  downloadUrl: string | null;
  expiresAt: Date | null;
  createdAt: Date;
}

3. PostgreSQL Schema

-- population_cohorts
CREATE TABLE population_cohorts (
  id             TEXT        PRIMARY KEY,                  -- coh_ULID
  tenant_id      UUID        NOT NULL,
  node_id        UUID        NOT NULL,
  name           TEXT        NOT NULL,
  description    TEXT,
  definition_json JSONB      NOT NULL,
  version        INTEGER     NOT NULL DEFAULT 1,
  status         TEXT        NOT NULL DEFAULT 'Draft'
                             CHECK (status IN ('Draft','Active','Refreshing','Archived')),
  is_shared      BOOLEAN     NOT NULL DEFAULT FALSE,
  owner_id       UUID        NOT NULL,
  refresh_policy TEXT        NOT NULL DEFAULT 'manual',
  refresh_cron_expr TEXT,
  last_evaluated_at TIMESTAMPTZ,
  membership_count  INTEGER  NOT NULL DEFAULT 0,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX ix_population_cohorts_tenant_id_status
  ON population_cohorts (tenant_id, status);
CREATE INDEX ix_population_cohorts_node_id
  ON population_cohorts (node_id);
CREATE INDEX ix_population_cohorts_definition_gin
  ON population_cohorts USING GIN (definition_json);

ALTER TABLE population_cohorts ENABLE ROW LEVEL SECURITY;
CREATE POLICY population_cohorts_tenant_isolation
  ON population_cohorts
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- disease_registry_entries
CREATE TABLE disease_registry_entries (
  id              TEXT        PRIMARY KEY,                 -- reg_ULID
  tenant_id       UUID        NOT NULL,
  node_id         UUID        NOT NULL,
  patient_id      UUID        NOT NULL,
  registry_type   TEXT        NOT NULL,
  control_status  TEXT        NOT NULL DEFAULT 'unknown',
  risk_tier       TEXT        NOT NULL DEFAULT 'low',
  last_visit_at   TIMESTAMPTZ,
  last_lab_at     TIMESTAMPTZ,
  active_gap_flags TEXT[]     NOT NULL DEFAULT '{}',
  last_synced_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (tenant_id, node_id, patient_id, registry_type)
);

CREATE INDEX ix_disease_registry_entries_tenant_type_risk
  ON disease_registry_entries (tenant_id, registry_type, risk_tier);

ALTER TABLE disease_registry_entries ENABLE ROW LEVEL SECURITY;
CREATE POLICY disease_registry_entries_tenant_isolation
  ON disease_registry_entries
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- risk_scores
CREATE TABLE risk_scores (
  id              TEXT        PRIMARY KEY,                 -- rsk_ULID
  tenant_id       UUID        NOT NULL,
  node_id         UUID        NOT NULL,
  patient_id      UUID        NOT NULL,
  model_key       TEXT        NOT NULL,
  computed_score  NUMERIC(6,4) NOT NULL,
  computed_tier   TEXT        NOT NULL,
  drivers_json    JSONB       NOT NULL DEFAULT '{}',
  computed_at     TIMESTAMPTZ NOT NULL,
  override_tier   TEXT,
  override_reason TEXT,
  overridden_at   TIMESTAMPTZ,
  overridden_by   UUID
);

CREATE INDEX ix_risk_scores_tenant_patient_model
  ON risk_scores (tenant_id, patient_id, model_key);

ALTER TABLE risk_scores ENABLE ROW LEVEL SECURITY;
CREATE POLICY risk_scores_tenant_isolation
  ON risk_scores
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- quality_metric_snapshots
CREATE TABLE quality_metric_snapshots (
  id           TEXT        PRIMARY KEY,                    -- qms_ULID
  tenant_id    UUID        NOT NULL,
  node_id      UUID        NOT NULL,
  metric_key   TEXT        NOT NULL,
  program      TEXT        NOT NULL,
  period_start DATE        NOT NULL,
  period_end   DATE        NOT NULL,
  numerator    INTEGER     NOT NULL,
  denominator  INTEGER     NOT NULL,
  exclusions   INTEGER     NOT NULL DEFAULT 0,
  rate         NUMERIC(6,4),
  computed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX ix_quality_metric_snapshots_tenant_metric_period
  ON quality_metric_snapshots (tenant_id, metric_key, period_start, period_end);

ALTER TABLE quality_metric_snapshots ENABLE ROW LEVEL SECURITY;
CREATE POLICY quality_metric_snapshots_tenant_isolation
  ON quality_metric_snapshots
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- hmis_export_jobs
CREATE TABLE hmis_export_jobs (
  id               TEXT        PRIMARY KEY,               -- hmis_ULID
  tenant_id        UUID        NOT NULL,
  indicator_family TEXT        NOT NULL,
  period_start     DATE        NOT NULL,
  period_end       DATE        NOT NULL,
  status           TEXT        NOT NULL DEFAULT 'queued',
  retry_count      INTEGER     NOT NULL DEFAULT 0,
  dhis2_reference  TEXT,
  completed_at     TIMESTAMPTZ,
  failure_reason   TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (tenant_id, indicator_family, period_start, period_end, status)
    DEFERRABLE INITIALLY DEFERRED
);

ALTER TABLE hmis_export_jobs ENABLE ROW LEVEL SECURITY;
CREATE POLICY hmis_export_jobs_tenant_isolation
  ON hmis_export_jobs
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- outbox (standard platform outbox)
CREATE TABLE population_health_outbox (
  id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    UUID        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 ix_population_health_outbox_unpublished
  ON population_health_outbox (published, created_at) WHERE published = FALSE;

-- inbox (deduplication)
CREATE TABLE population_health_inbox (
  event_id     TEXT        PRIMARY KEY,   -- CloudEvents id
  event_type   TEXT        NOT NULL,
  processed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);