Facility Service — Data Model

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

1. Storage

Concern	Tech
Primary	PostgreSQL 16, schema `facility`
Cache	Redis 7 (context lookup, availability search)
Encryption	AES-256 TDE at rest; RLS on every tenant-scoped table
ORM	Drizzle
Migrations	drizzle-kit

2. ID Prefixes

Entity	Prefix	Example
HierarchyNode	`hnd_`	`hnd_01HN2K...`
HierarchyEdge	`hed_`	`hed_01HN2K...`
HierarchyProfile	`hpf_`	`hpf_01HN2K...`
Location	`loc_`	`loc_01HN2K...`
Bed	`bed_`	`bed_01HN2K...`
ResourceCatalogItem	`res_`	`res_01HN2K...`
ProviderNodeMembership	`pnm_`	`pnm_01HN2K...`

3. TypeScript Interfaces

See DOMAIN_MODEL §3-4. Those are the canonical shapes.

4. Postgres DDL

4.1 hierarchy_profiles

CREATE TABLE facility.hierarchy_profiles (
  id                    text PRIMARY KEY,
  tenant_id             text,                    -- NULL = platform-level
  name                  text NOT NULL,
  allowed_node_types    text[] NOT NULL,
  allowed_root_types    text[] NOT NULL,
  allowed_relationships text[] NOT NULL,
  constraints           jsonb NOT NULL DEFAULT '{}'::jsonb,
  is_default            boolean NOT NULL DEFAULT false,
  version               integer NOT NULL DEFAULT 1,
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ix_hierarchy_profiles_tenant_name ON facility.hierarchy_profiles (COALESCE(tenant_id,'PLATFORM'), name);
CREATE UNIQUE INDEX ix_hierarchy_profiles_default ON facility.hierarchy_profiles (tenant_id) WHERE is_default = true AND tenant_id IS NOT NULL;
ALTER TABLE facility.hierarchy_profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY hierarchy_profiles_tenant_isolation ON facility.hierarchy_profiles
  USING (tenant_id IS NULL OR tenant_id = current_setting('app.tenant_id', true));

4.2 hierarchy_nodes

CREATE TABLE facility.hierarchy_nodes (
  id            text PRIMARY KEY,
  tenant_id     text NOT NULL,
  profile_id    text NOT NULL REFERENCES facility.hierarchy_profiles(id),
  type          text NOT NULL,
  name          text NOT NULL,
  code          text,
  metadata      jsonb NOT NULL DEFAULT '{}'::jsonb,
  status        text NOT NULL CHECK (status IN ('active','inactive')),
  version       integer NOT NULL DEFAULT 1,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_hierarchy_nodes_tenant_status ON facility.hierarchy_nodes (tenant_id, status);
CREATE INDEX ix_hierarchy_nodes_type ON facility.hierarchy_nodes (tenant_id, type);
CREATE UNIQUE INDEX ix_hierarchy_nodes_tenant_code ON facility.hierarchy_nodes (tenant_id, code) WHERE code IS NOT NULL;
ALTER TABLE facility.hierarchy_nodes ENABLE ROW LEVEL SECURITY;
CREATE POLICY hierarchy_nodes_tenant_isolation ON facility.hierarchy_nodes
  USING (tenant_id = current_setting('app.tenant_id', true));

4.3 hierarchy_edges

CREATE TABLE facility.hierarchy_edges (
  id              text PRIMARY KEY,
  tenant_id       text NOT NULL,
  parent_node_id  text NOT NULL REFERENCES facility.hierarchy_nodes(id),
  child_node_id   text NOT NULL REFERENCES facility.hierarchy_nodes(id),
  relationship_type text NOT NULL CHECK (relationship_type IN ('contains','manages','refers-to')),
  created_at      timestamptz NOT NULL DEFAULT now(),
  CHECK (parent_node_id <> child_node_id)
);
CREATE UNIQUE INDEX ix_hierarchy_edges_triplet ON facility.hierarchy_edges (tenant_id, parent_node_id, child_node_id, relationship_type);
CREATE INDEX ix_hierarchy_edges_parent ON facility.hierarchy_edges (parent_node_id, relationship_type);
CREATE INDEX ix_hierarchy_edges_child  ON facility.hierarchy_edges (child_node_id,  relationship_type);
ALTER TABLE facility.hierarchy_edges ENABLE ROW LEVEL SECURITY;
CREATE POLICY hierarchy_edges_tenant_isolation ON facility.hierarchy_edges
  USING (tenant_id = current_setting('app.tenant_id', true));

Cycle detection for contains edges uses a recursive CTE in the repository adapter before insert.

4.4 locations

CREATE TABLE facility.locations (
  id                    text PRIMARY KEY,
  tenant_id             text NOT NULL,
  hierarchy_node_id     text NOT NULL REFERENCES facility.hierarchy_nodes(id),
  name                  text NOT NULL,
  service_type          text NOT NULL CHECK (service_type IN ('OUTPATIENT','INPATIENT','EMERGENCY','ICU','DIAGNOSTIC')),
  capacity              integer CHECK (capacity >= 0),
  timezone              text NOT NULL,
  locale                text NOT NULL,
  currency              text,
  contact_info          jsonb,
  status                text NOT NULL DEFAULT 'active' CHECK (status IN ('active','inactive')),
  version               integer NOT NULL DEFAULT 1,
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_locations_tenant_hnode ON facility.locations (tenant_id, hierarchy_node_id);
CREATE INDEX ix_locations_tenant_status_type ON facility.locations (tenant_id, status, service_type);
ALTER TABLE facility.locations ENABLE ROW LEVEL SECURITY;
CREATE POLICY locations_tenant_isolation ON facility.locations
  USING (tenant_id = current_setting('app.tenant_id', true));

4.5 location_hours

CREATE TABLE facility.location_hours (
  location_id text NOT NULL REFERENCES facility.locations(id) ON DELETE CASCADE,
  day_of_week smallint NOT NULL CHECK (day_of_week BETWEEN 0 AND 6),
  open_time   time,
  close_time  time,
  is_available boolean NOT NULL DEFAULT true,
  PRIMARY KEY (location_id, day_of_week)
);

4.6 beds

CREATE TABLE facility.beds (
  id                  text PRIMARY KEY,
  tenant_id           text NOT NULL,
  location_id         text NOT NULL REFERENCES facility.locations(id),
  bed_number          text NOT NULL,
  bed_class           text NOT NULL CHECK (bed_class IN ('GENERAL','SEMI_PRIVATE','PRIVATE','ICU','NICU','ISOLATION')),
  status              text NOT NULL CHECK (status IN ('AVAILABLE','RESERVED','OCCUPIED','CLEANING','MAINTENANCE','OUT_OF_SERVICE')),
  isolation_capable   boolean NOT NULL DEFAULT false,
  housekeeping_status text CHECK (housekeeping_status IN ('clean','in_progress','dirty')),
  current_patient_id  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_beds_location_number ON facility.beds (location_id, bed_number);
CREATE INDEX ix_beds_tenant_status ON facility.beds (tenant_id, status);
CREATE INDEX ix_beds_tenant_location_status ON facility.beds (tenant_id, location_id, status);
ALTER TABLE facility.beds ENABLE ROW LEVEL SECURITY;
CREATE POLICY beds_tenant_isolation ON facility.beds
  USING (tenant_id = current_setting('app.tenant_id', true));

4.7 resource_catalog_items

CREATE TABLE facility.resource_catalog_items (
  id            text PRIMARY KEY,
  tenant_id     text NOT NULL,
  location_id   text REFERENCES facility.locations(id),
  resource_type text NOT NULL CHECK (resource_type IN ('ROOM','EQUIPMENT','VEHICLE','OTHER')),
  name          text NOT NULL,
  status        text NOT NULL DEFAULT 'active' CHECK (status IN ('active','inactive','maintenance')),
  attributes    jsonb,
  version       integer NOT NULL DEFAULT 1,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_resources_tenant_location_type ON facility.resource_catalog_items (tenant_id, location_id, resource_type);
ALTER TABLE facility.resource_catalog_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY resources_tenant_isolation ON facility.resource_catalog_items
  USING (tenant_id = current_setting('app.tenant_id', true));

4.8 provider_node_memberships

CREATE TABLE facility.provider_node_memberships (
  id              text PRIMARY KEY,
  tenant_id       text NOT NULL,
  provider_id     text NOT NULL,
  node_id         text NOT NULL REFERENCES facility.hierarchy_nodes(id),
  is_primary      boolean NOT NULL DEFAULT false,
  effective_from  date NOT NULL,
  effective_to    date,
  version         integer NOT NULL DEFAULT 1,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_pnm_tenant_provider ON facility.provider_node_memberships (tenant_id, provider_id);
CREATE INDEX ix_pnm_tenant_node ON facility.provider_node_memberships (tenant_id, node_id);
CREATE UNIQUE INDEX ix_pnm_primary_per_provider ON facility.provider_node_memberships (tenant_id, provider_id) WHERE is_primary = true AND effective_to IS NULL;
ALTER TABLE facility.provider_node_memberships ENABLE ROW LEVEL SECURITY;
CREATE POLICY pnm_tenant_isolation ON facility.provider_node_memberships
  USING (tenant_id = current_setting('app.tenant_id', true));

4.9 outbox / inbox

CREATE TABLE facility.outbox (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  subject text NOT NULL, payload jsonb NOT NULL,
  tenant_id text NOT NULL, aggregate_id text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(), published_at timestamptz
);
CREATE INDEX ix_outbox_unpublished ON facility.outbox (published_at) WHERE published_at IS NULL;

CREATE TABLE facility.inbox (
  event_id text PRIMARY KEY, subject text NOT NULL,
  received_at timestamptz NOT NULL DEFAULT now()
);

5. RLS Summary

Table	Policy name	Predicate
hierarchy_profiles	hierarchy_profiles_tenant_isolation	`tenant_id IS NULL OR tenant_id = app.tenant_id`
hierarchy_nodes	hierarchy_nodes_tenant_isolation	`tenant_id = app.tenant_id`
hierarchy_edges	hierarchy_edges_tenant_isolation	`tenant_id = app.tenant_id`
locations	locations_tenant_isolation	`tenant_id = app.tenant_id`
beds	beds_tenant_isolation	`tenant_id = app.tenant_id`
resource_catalog_items	resources_tenant_isolation	`tenant_id = app.tenant_id`
provider_node_memberships	pnm_tenant_isolation	`tenant_id = app.tenant_id`

6. Seed Data

On deploy, insert platform-level profiles per §5 of hierarchy SPEC: AFG_MOPH, UAE_DOH, PRIVATE_HOSPITAL, CLINIC_SIMPLE, TELEMEDICINE.

1. Storage​

2. ID Prefixes​

3. TypeScript Interfaces​

4. Postgres DDL​

4.1 hierarchy_profiles​

4.2 hierarchy_nodes​

4.3 hierarchy_edges​

4.4 locations​

4.5 location_hours​

4.6 beds​

4.7 resource_catalog_items​

4.8 provider_node_memberships​

4.9 outbox / inbox​

5. RLS Summary​

6. Seed Data​