Tenant Service — Data Model

Status: populated Owner: TBD Last updated: 2026-04-18 Companion: Service Template · NAMING · 13 Security

Schema: tenant ID strategy: ULID + service-scoped prefix. All surrogate keys CHAR(26). Isolation: Postgres RLS on all tenant-scoped tables. SET app.tenant_id per connection.

1. ID prefix registry

Prefix	Entity	Table
ten_	Tenant	tenants
nod_	HierarchyNode	hierarchy_nodes
prf_	UserProfile	user_profiles
mem_	OrgMembership	org_memberships
rol_	Role	roles
roa_	RoleAssignment	role_assignments
pol_	AccessPolicy	access_policies

2. TypeScript interfaces (domain)

type TenantId = Branded<string, 'TenantId'>
type NodeId   = Branded<string, 'NodeId'>

interface Tenant {
  id: TenantId
  slug: string
  legalName: string
  displayName: string
  status: 'pending' | 'active' | 'suspended' | 'terminated'
  countryCode: string
  timezone: string
  locale: string
  hierarchyProfileId: string | null
  subscriptionTier: 'STARTER' | 'PROFESSIONAL' | 'ENTERPRISE'
  subscriptionStart: Date | null
  subscriptionEnd: Date | null
  rootNodeId: NodeId | null
  contactEmail: string
  createdAt: Date
  updatedAt: Date
  activatedAt: Date | null
  suspendedAt: Date | null
  terminatedAt: Date | null
}

interface HierarchyNode {
  id: NodeId
  tenantId: TenantId
  parentId: NodeId | null
  nodeType: 'organization' | 'facility' | 'department' | 'ward' | 'bed'
  name: string
  code: string
  attributes: Record<string, unknown>
  isArchived: boolean
  createdAt: Date
  updatedAt: Date
}

3. Postgres schema

CREATE SCHEMA IF NOT EXISTS tenant;
SET search_path TO tenant, public;

CREATE TABLE tenants (
  id                   CHAR(26) PRIMARY KEY,
  slug                 VARCHAR(100) NOT NULL UNIQUE,
  legal_name           VARCHAR(255) NOT NULL,
  display_name         VARCHAR(100) NOT NULL,
  status               VARCHAR(20) NOT NULL DEFAULT 'pending'
                         CHECK (status IN ('pending','active','suspended','terminated')),
  country_code         CHAR(2) NOT NULL,
  timezone             VARCHAR(50) NOT NULL DEFAULT 'UTC',
  locale               VARCHAR(20) NOT NULL DEFAULT 'en',
  hierarchy_profile_id VARCHAR(50),
  subscription_tier    VARCHAR(20) NOT NULL DEFAULT 'STARTER',
  subscription_start   DATE,
  subscription_end     DATE,
  root_node_id         CHAR(26),
  contact_email        VARCHAR(320) NOT NULL,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  activated_at         TIMESTAMPTZ,
  suspended_at         TIMESTAMPTZ,
  terminated_at        TIMESTAMPTZ
);
CREATE INDEX ix_tenants_status ON tenants(status);
CREATE INDEX ix_tenants_slug   ON tenants(slug);
-- No RLS on tenants table — SUPER_ADMIN queries all; TENANT_ADMIN filtered by guard

CREATE TABLE tenant_configurations (
  tenant_id  CHAR(26) NOT NULL REFERENCES tenants(id),
  key        VARCHAR(100) NOT NULL,
  value      JSONB NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, key)
);
ALTER TABLE tenant_configurations ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_config_isolation ON tenant_configurations
  USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE hierarchy_nodes (
  id          CHAR(26) PRIMARY KEY,
  tenant_id   CHAR(26) NOT NULL REFERENCES tenants(id),
  parent_id   CHAR(26) REFERENCES hierarchy_nodes(id),
  node_type   VARCHAR(20) NOT NULL,
  name        VARCHAR(255) NOT NULL,
  code        VARCHAR(50),
  attributes  JSONB NOT NULL DEFAULT '{}',
  is_archived BOOLEAN NOT NULL DEFAULT FALSE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_hierarchy_nodes_tenant    ON hierarchy_nodes(tenant_id);
CREATE INDEX ix_hierarchy_nodes_parent    ON hierarchy_nodes(parent_id) WHERE parent_id IS NOT NULL;
ALTER TABLE hierarchy_nodes ENABLE ROW LEVEL SECURITY;
CREATE POLICY hierarchy_nodes_isolation ON hierarchy_nodes
  USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE user_profiles (
  id           CHAR(26) PRIMARY KEY,
  tenant_id    CHAR(26) NOT NULL REFERENCES tenants(id),
  user_id      CHAR(26) NOT NULL,            -- identity-service UserId
  first_name   VARCHAR(100),
  last_name    VARCHAR(100),
  specialty    VARCHAR(100),
  credentials  TEXT,
  locale       VARCHAR(20),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, user_id)
);
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_profiles_isolation ON user_profiles
  USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE org_memberships (
  id          CHAR(26) PRIMARY KEY,
  tenant_id   CHAR(26) NOT NULL REFERENCES tenants(id),
  user_id     CHAR(26) NOT NULL,
  node_id     CHAR(26) NOT NULL REFERENCES hierarchy_nodes(id),
  status      VARCHAR(20) NOT NULL DEFAULT 'active'
                CHECK (status IN ('active','suspended','removed')),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, node_id)
);
CREATE INDEX ix_org_mem_tenant_user ON org_memberships(tenant_id, user_id);
ALTER TABLE org_memberships ENABLE ROW LEVEL SECURITY;
CREATE POLICY org_memberships_isolation ON org_memberships
  USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE roles (
  id         CHAR(26) PRIMARY KEY,
  tenant_id  CHAR(26) REFERENCES tenants(id),   -- NULL = built-in platform role
  code       VARCHAR(60) NOT NULL,
  name       VARCHAR(120) NOT NULL,
  is_builtin BOOLEAN NOT NULL DEFAULT FALSE,
  permissions TEXT[] NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, code)
);

CREATE TABLE role_assignments (
  id          CHAR(26) PRIMARY KEY,
  tenant_id   CHAR(26) NOT NULL REFERENCES tenants(id),
  user_id     CHAR(26) NOT NULL,
  role_id     CHAR(26) NOT NULL REFERENCES roles(id),
  node_id     CHAR(26) REFERENCES hierarchy_nodes(id),
  assigned_by CHAR(26) NOT NULL,
  assigned_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, role_id, node_id)
);
ALTER TABLE role_assignments ENABLE ROW LEVEL SECURITY;
CREATE POLICY role_assignments_isolation ON role_assignments
  USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE outbox (
  id           CHAR(26) PRIMARY KEY,
  tenant_id    CHAR(26),
  subject      VARCHAR(200) NOT NULL,
  payload      JSONB NOT NULL,
  headers      JSONB NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at TIMESTAMPTZ,
  attempt      INT NOT NULL DEFAULT 0,
  last_error   TEXT
);
CREATE INDEX ix_outbox_unpublished ON outbox(published_at) WHERE published_at IS NULL;

CREATE TABLE inbox (
  id           CHAR(26) PRIMARY KEY,
  subject      VARCHAR(200) NOT NULL,
  received_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  processed_at TIMESTAMPTZ
);

4. Volume estimates

Table	Year-1 rows	Retention
tenants	500	Forever
tenant_configurations	5 000	Forever
hierarchy_nodes	50 000	Forever (archived soft-delete)
user_profiles	500 000	Forever (anonymized on deactivation)
org_memberships	2 M	Forever
role_assignments	3 M	Forever
outbox	rolling	7 days after publish
inbox	rolling	30 days

5. RLS bypass role

tenant_rls_bypass — used by cron jobs (subscription expiry) with BYPASSRLS. Separate connection pool with strict GRANT.