Provider Directory Service — Data Model

Status: populated Owner: TBD Last updated: 2026-04-17 Companion: NAMING · 13 Security & Tenancy

1. Storage

Concern	Tech
Primary	PostgreSQL 16, schema provider_directory
Search	OpenSearch index practitioners_{tenant}
Cache	Redis 7 (privilege check, endpoint health)
Encryption	AES-256 TDE at rest; RLS
ORM	Drizzle

2. ID Prefixes

Entity	Prefix
Practitioner	prc_
PractitionerRole	prr_
Credential	crd_
HealthcareService	hcs_
Endpoint	end_

3. DDL

3.1 practitioners

CREATE TABLE provider_directory.practitioners (
  id                 text PRIMARY KEY,
  tenant_id          text NOT NULL,
  user_id            text,
  kind               text NOT NULL CHECK (kind IN ('physician','nurse','midwife','pharmacist','dentist','technician','therapist','other')),
  names              jsonb NOT NULL,          -- array of NameVariant
  identifiers        jsonb NOT NULL DEFAULT '[]'::jsonb,
  telecom            jsonb NOT NULL DEFAULT '[]'::jsonb,
  specialties        jsonb NOT NULL DEFAULT '[]'::jsonb,
  preferred_language text,
  status             text NOT NULL CHECK (status IN ('active','suspended','deactivated')),
  employment_status  text NOT NULL CHECK (employment_status IN ('employed','contract','visiting','inactive')),
  active             boolean NOT NULL DEFAULT true,
  version            integer NOT NULL DEFAULT 1,
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_practitioners_tenant_status ON provider_directory.practitioners (tenant_id, status);
CREATE INDEX ix_practitioners_tenant_kind ON provider_directory.practitioners (tenant_id, kind);
CREATE INDEX ix_practitioners_user ON provider_directory.practitioners (user_id) WHERE user_id IS NOT NULL;
CREATE INDEX ix_practitioners_identifiers_gin ON provider_directory.practitioners USING GIN (identifiers jsonb_path_ops);
CREATE INDEX ix_practitioners_specialties_gin ON provider_directory.practitioners USING GIN (specialties jsonb_path_ops);
ALTER TABLE provider_directory.practitioners ENABLE ROW LEVEL SECURITY;
CREATE POLICY practitioners_tenant_isolation ON provider_directory.practitioners
  USING (tenant_id = current_setting('app.tenant_id', true));

3.2 credentials

CREATE TABLE provider_directory.credentials (
  id                text PRIMARY KEY,
  tenant_id         text NOT NULL,
  practitioner_id   text NOT NULL REFERENCES provider_directory.practitioners(id) ON DELETE CASCADE,
  type              text NOT NULL CHECK (type IN ('license','certification','degree')),
  number            text NOT NULL,
  issuing_authority text NOT NULL,
  issued_at         date NOT NULL,
  expires_at        date,
  status            text NOT NULL CHECK (status IN ('active','expired','suspended','revoked')),
  notes             text,
  version           integer NOT NULL DEFAULT 1,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ix_credentials_authority_number ON provider_directory.credentials (tenant_id, issuing_authority, number);
CREATE INDEX ix_credentials_practitioner ON provider_directory.credentials (practitioner_id);
CREATE INDEX ix_credentials_expiry_status ON provider_directory.credentials (expires_at, status);
ALTER TABLE provider_directory.credentials ENABLE ROW LEVEL SECURITY;
CREATE POLICY credentials_tenant_isolation ON provider_directory.credentials
  USING (tenant_id = current_setting('app.tenant_id', true));

3.3 practitioner_roles

CREATE TABLE provider_directory.practitioner_roles (
  id                    text PRIMARY KEY,
  tenant_id             text NOT NULL,
  practitioner_id       text NOT NULL REFERENCES provider_directory.practitioners(id),
  hierarchy_node_id     text,
  healthcare_service_id text,
  role                  text NOT NULL,
  privileges            jsonb NOT NULL DEFAULT '[]'::jsonb,
  period_start          date NOT NULL,
  period_end            date,
  active                boolean NOT NULL DEFAULT true,
  version               integer NOT NULL DEFAULT 1,
  created_at            timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_roles_practitioner_active ON provider_directory.practitioner_roles (practitioner_id, active);
CREATE INDEX ix_roles_node ON provider_directory.practitioner_roles (tenant_id, hierarchy_node_id);
ALTER TABLE provider_directory.practitioner_roles ENABLE ROW LEVEL SECURITY;
CREATE POLICY roles_tenant_isolation ON provider_directory.practitioner_roles
  USING (tenant_id = current_setting('app.tenant_id', true));

3.4 healthcare_services

CREATE TABLE provider_directory.healthcare_services (
  id                 text PRIMARY KEY,
  tenant_id          text NOT NULL,
  hierarchy_node_id  text,
  location_id        text,
  name               text NOT NULL,
  category           text NOT NULL,
  specialties        jsonb NOT NULL DEFAULT '[]'::jsonb,
  active             boolean NOT NULL DEFAULT true,
  version            integer NOT NULL DEFAULT 1,
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_hcs_tenant_node ON provider_directory.healthcare_services (tenant_id, hierarchy_node_id);
ALTER TABLE provider_directory.healthcare_services ENABLE ROW LEVEL SECURITY;
CREATE POLICY hcs_tenant_isolation ON provider_directory.healthcare_services
  USING (tenant_id = current_setting('app.tenant_id', true));

3.5 endpoints

CREATE TABLE provider_directory.endpoints (
  id                     text PRIMARY KEY,
  tenant_id              text NOT NULL,
  organization_node_id   text,
  location_id            text,
  type                   text NOT NULL CHECK (type IN ('fhir-r4','hl7v2','dicom','webhook','other')),
  address                text NOT NULL,
  status                 text NOT NULL CHECK (status IN ('active','inactive','error','deprecated')),
  auth_method            text NOT NULL CHECK (auth_method IN ('oauth2','mtls','basic','none')),
  last_health_check      jsonb,
  version                integer NOT NULL DEFAULT 1,
  created_at             timestamptz NOT NULL DEFAULT now(),
  updated_at             timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_endpoints_tenant_type_status ON provider_directory.endpoints (tenant_id, type, status);
ALTER TABLE provider_directory.endpoints ENABLE ROW LEVEL SECURITY;
CREATE POLICY endpoints_tenant_isolation ON provider_directory.endpoints
  USING (tenant_id = current_setting('app.tenant_id', true));

3.6 outbox / inbox

CREATE TABLE provider_directory.outbox ( ... same pattern as facility-service ... );
CREATE TABLE provider_directory.inbox  ( ... same pattern ... );

4. RLS Summary

Table	Policy
practitioners	tenant-isolation
credentials	tenant-isolation
practitioner_roles	tenant-isolation
healthcare_services	tenant-isolation
endpoints	tenant-isolation

5. Indexing strategy

Query	Index
Search by identifier value	GIN on identifiers
Filter by specialty code	GIN on specialties
List roles at node	ix_roles_node
Expiring credentials	ix_credentials_expiry_status

6. OpenSearch mapping (sketch)

Fields: id, tenantId, names.given, names.family, names.scriptCode, identifiers.value, specialties.code, kind, status, active.