Identity Service — Data Model

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

Schema: identity ID strategy: ULID + service-scoped prefix (see table). All surrogate keys CHAR(26) ULID bodies behind a branded type in code. Isolation: Postgres row-level security (RLS) on every table that holds tenant data. SET app.tenant_id set by connection per request; policy USING tenant_id = current_setting('app.tenant_id')::uuid.

1. ID prefix registry

Prefix	Entity	Table
`usr_`	User	`users`
`ses_`	Session	`sessions`
`crd_`	Credential	`credentials`
`mfa_`	MFAFactor	`mfa_factors`
`dev_`	Device	`devices`
`bdc_`	BindingCertificate	`binding_certificates`
`apk_`	APIKey	`api_keys`
`svc_`	ServiceAccount	`service_accounts`
`eid_`	ExternalIdentity	`external_identities`
`mod_`	Module	`modules`
`lic_`	LicenseAssignment	`license_assignments`
`lih_`	LicenseAssignmentHistory	`license_assignment_history`
`bun_`	ModuleBundle	`module_bundles`

2. TypeScript interfaces (domain)

// domain/types.ts
type UserId = Branded<string, 'UserId'>
type TenantId = Branded<string, 'TenantId'>
type SessionId = Branded<string, 'SessionId'>
type DeviceId = Branded<string, 'DeviceId'>
type ModuleCode = Branded<string, 'ModuleCode'>

interface User {
  id: UserId
  tenantId: TenantId
  email: string
  status: 'pending_verification' | 'active' | 'suspended' | 'deactivated'
  backend: 'in_house' | 'keycloak_broker' | 'oidc' | 'saml'
  keycloakUserId?: string
  firstName: string
  lastName: string
  locale: string
  timezone: string
  mfaEnrolled: boolean
  createdAt: Date
  updatedAt: Date
}

interface Session {
  id: SessionId
  userId: UserId
  tenantId: TenantId
  deviceId?: DeviceId
  refreshHash: string // argon2-hashed
  amr: Array<'pwd' | 'mfa' | 'webauthn' | 'fed'>
  ip: string
  userAgent: string
  createdAt: Date
  lastSeenAt: Date
  absoluteExpiresAt: Date
  revokedAt?: Date
  revokeReason?: string
}

interface LicenseAssignment {
  id: string
  tenantId: TenantId
  moduleId: string
  nodeId: string
  scope: 'exact' | 'inherit-down'
  status: 'trial' | 'active' | 'suspended' | 'expired' | 'terminated'
  constraints: { seats?: number; expiresAt?: Date; usageCap?: number }
  effectiveFrom: Date
  effectiveTo?: Date
  assignedBy: UserId
  createdAt: Date
  updatedAt: Date
}

3. Postgres schema (core)

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

CREATE TABLE users (
  id               CHAR(26) PRIMARY KEY,                -- ULID body, prefix handled by mapper
  tenant_id        CHAR(26) NOT NULL,
  email            VARCHAR(320) NOT NULL,
  status           VARCHAR(30) NOT NULL DEFAULT 'pending_verification',
  backend          VARCHAR(30) NOT NULL DEFAULT 'in_house',
  keycloak_user_id VARCHAR(255),
  first_name       VARCHAR(100) NOT NULL,
  last_name        VARCHAR(100) NOT NULL,
  locale           VARCHAR(20) NOT NULL DEFAULT 'en',
  timezone         VARCHAR(50) NOT NULL DEFAULT 'UTC',
  mfa_enrolled     BOOLEAN NOT NULL DEFAULT FALSE,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  deactivated_at   TIMESTAMPTZ,
  UNIQUE (tenant_id, email)
);
CREATE INDEX ix_users_tenant_status ON users(tenant_id, status);
CREATE INDEX ix_users_backend       ON users(backend);

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY users_tenant_isolation ON users
  USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE credentials (
  id         CHAR(26) PRIMARY KEY,
  user_id    CHAR(26) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  tenant_id  CHAR(26) NOT NULL,
  kind       VARCHAR(20) NOT NULL,        -- password | webauthn | magic_link
  secret     TEXT NOT NULL,               -- argon2id hash or webauthn public key
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  revoked_at TIMESTAMPTZ
);
CREATE INDEX ix_credentials_user ON credentials(user_id, kind);
ALTER TABLE credentials ENABLE ROW LEVEL SECURITY;
CREATE POLICY credentials_tenant_isolation ON credentials USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE sessions (
  id                   CHAR(26) PRIMARY KEY,
  user_id              CHAR(26) NOT NULL REFERENCES users(id),
  tenant_id            CHAR(26) NOT NULL,
  device_id            CHAR(26),
  refresh_hash         TEXT NOT NULL,
  amr                  TEXT[] NOT NULL DEFAULT '{}',
  ip                   INET,
  user_agent           TEXT,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  absolute_expires_at  TIMESTAMPTZ NOT NULL,
  revoked_at           TIMESTAMPTZ,
  revoke_reason        VARCHAR(60)
);
CREATE INDEX ix_sessions_user_active   ON sessions(user_id) WHERE revoked_at IS NULL;
CREATE INDEX ix_sessions_refresh_hash  ON sessions USING hash (refresh_hash);
ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
CREATE POLICY sessions_tenant_isolation ON sessions USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE mfa_factors (
  id          CHAR(26) PRIMARY KEY,
  user_id     CHAR(26) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  tenant_id   CHAR(26) NOT NULL,
  kind        VARCHAR(20) NOT NULL,  -- totp | webauthn | recovery
  secret      TEXT,                   -- TOTP seed (kms-wrapped) or webauthn pubkey
  verified_at TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE mfa_factors ENABLE ROW LEVEL SECURITY;
CREATE POLICY mfa_factors_tenant_isolation ON mfa_factors USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE devices (
  id                CHAR(26) PRIMARY KEY,
  user_id           CHAR(26) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  tenant_id         CHAR(26) NOT NULL,
  fingerprint_hash  CHAR(64) NOT NULL,
  public_key        TEXT,
  trust_level       VARCHAR(20) NOT NULL DEFAULT 'untrusted',
  user_agent        TEXT,
  registered_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen_at      TIMESTAMPTZ,
  revoked_at        TIMESTAMPTZ
);
CREATE UNIQUE INDEX ux_devices_pubkey_user ON devices(user_id, public_key) WHERE public_key IS NOT NULL;
ALTER TABLE devices ENABLE ROW LEVEL SECURITY;
CREATE POLICY devices_tenant_isolation ON devices USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE binding_certificates (
  id          CHAR(26) PRIMARY KEY,
  device_id   CHAR(26) NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
  tenant_id   CHAR(26) NOT NULL,
  cert_pem    TEXT NOT NULL,
  issued_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at  TIMESTAMPTZ NOT NULL,
  revoked_at  TIMESTAMPTZ
);
ALTER TABLE binding_certificates ENABLE ROW LEVEL SECURITY;
CREATE POLICY binding_certificates_tenant_isolation ON binding_certificates USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE external_identities (
  id         CHAR(26) PRIMARY KEY,
  user_id    CHAR(26) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  tenant_id  CHAR(26) NOT NULL,
  issuer     TEXT NOT NULL,
  subject    TEXT NOT NULL,
  linked_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (issuer, subject)
);
ALTER TABLE external_identities ENABLE ROW LEVEL SECURITY;
CREATE POLICY external_identities_tenant_isolation ON external_identities USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE api_keys (
  id          CHAR(26) PRIMARY KEY,
  tenant_id   CHAR(26) NOT NULL,
  user_id     CHAR(26),
  name        VARCHAR(120) NOT NULL,
  scopes      TEXT[] NOT NULL DEFAULT '{}',
  secret_hmac TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  revoked_at  TIMESTAMPTZ
);
ALTER TABLE api_keys ENABLE ROW LEVEL SECURITY;
CREATE POLICY api_keys_tenant_isolation ON api_keys USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE service_accounts (
  id         CHAR(26) PRIMARY KEY,
  tenant_id  CHAR(26),                 -- NULL = platform-level
  name       VARCHAR(120) NOT NULL,
  client_id  VARCHAR(255) NOT NULL UNIQUE,
  status     VARCHAR(20) NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

4. Licensing tables

CREATE TABLE modules (
  id             CHAR(26) PRIMARY KEY,
  code           VARCHAR(100) NOT NULL UNIQUE,
  name           VARCHAR(255) NOT NULL,
  description    TEXT,
  category       VARCHAR(40) NOT NULL,
  dependencies   TEXT[] NOT NULL DEFAULT '{}',
  min_node_types TEXT[] NOT NULL DEFAULT '{}',
  is_always_on   BOOLEAN NOT NULL DEFAULT FALSE,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- modules is a global catalogue: no RLS; readable by all tenants.

CREATE TABLE license_assignments (
  id             CHAR(26) PRIMARY KEY,
  tenant_id      CHAR(26) NOT NULL,
  module_id      CHAR(26) NOT NULL REFERENCES modules(id),
  node_id        CHAR(26) NOT NULL,
  scope          VARCHAR(20) NOT NULL DEFAULT 'inherit-down'
                   CHECK (scope IN ('exact','inherit-down')),
  status         VARCHAR(20) NOT NULL DEFAULT 'active'
                   CHECK (status IN ('trial','active','suspended','expired','terminated')),
  constraints    JSONB NOT NULL DEFAULT '{}',
  effective_from DATE NOT NULL DEFAULT CURRENT_DATE,
  effective_to   DATE,
  assigned_by    CHAR(26) NOT NULL,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (module_id, node_id)
);
CREATE INDEX ix_lic_tenant_node    ON license_assignments (tenant_id, node_id);
CREATE INDEX ix_lic_module_status  ON license_assignments (module_id, status);
CREATE INDEX ix_lic_effective      ON license_assignments (tenant_id, effective_from, effective_to);
ALTER TABLE license_assignments ENABLE ROW LEVEL SECURITY;
CREATE POLICY lic_tenant_isolation ON license_assignments USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE license_assignment_history (
  id             CHAR(26) PRIMARY KEY,
  tenant_id      CHAR(26) NOT NULL,
  assignment_id  CHAR(26) NOT NULL REFERENCES license_assignments(id),
  changed_by     CHAR(26) NOT NULL,
  change_type    VARCHAR(30) NOT NULL,
  before_state   JSONB,
  after_state    JSONB NOT NULL,
  changed_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_lic_hist_assignment ON license_assignment_history(assignment_id, changed_at DESC);
ALTER TABLE license_assignment_history ENABLE ROW LEVEL SECURITY;
CREATE POLICY lic_hist_tenant_isolation ON license_assignment_history USING (tenant_id = current_setting('app.tenant_id', true)::char(26));

CREATE TABLE module_bundles (
  id          CHAR(26) PRIMARY KEY,
  code        VARCHAR(100) NOT NULL UNIQUE,
  name        VARCHAR(255) NOT NULL,
  module_codes TEXT[] NOT NULL
);

5. Outbox / inbox

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,    -- source event id
  subject       VARCHAR(200) NOT NULL,
  received_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  processed_at  TIMESTAMPTZ
);

6. RLS bypass

A privileged role identity_rls_bypass is used by background workers (outbox relay, scheduled expiry job) with BYPASSRLS — only accessible via separate connection pool with strict Postgres GRANT.

7. Volume & retention

Table	Target volume (year 1)	Retention	Cleanup
`users`	500 k	forever	soft-delete on deactivation
`sessions`	50 M	400 days	cron purge revoked+expired
`mfa_factors`	800 k	while user active	cascade
`devices`	2 M	while user active	cascade
`binding_certificates`	10 M	90 days after expiry	cron purge
`api_keys`	100 k	forever (soft-delete)	—
`license_assignments`	200 k	forever	never purged
`license_assignment_history`	2 M	7 years	archive to S3 glacier after 2y
`outbox`	rolling	7 days after publish	cron purge
`inbox`	rolling	30 days	cron purge

8. Open questions

Should large tenants get their own Postgres schema instead of RLS? Decision gated on ops cost vs blast-radius analysis.

1. ID prefix registry​

2. TypeScript interfaces (domain)​

3. Postgres schema (core)​

4. Licensing tables​

5. Outbox / inbox​

6. RLS bypass​

7. Volume & retention​

8. Open questions​