Data Model

:::info Source Sourced from services/identity-service/DATA_MODEL.md in the documentation repo. :::

Companion: 12 Data Models · DOMAIN_MODEL · 13 Security & Tenancy

1. Storage

Primary: PostgreSQL 16+, schema identity
Cache: Redis 7+ for rate limits, idempotency keys, password reset tokens, SSO state, JWKS
Encryption: AES-256 at rest (TDE); RLS on every tenant-scoped table
Encoding: UTF-8, timestamptz UTC everywhere

2. Schema DDL

2.1 users

CREATE TABLE identity.users (
  id                    text PRIMARY KEY,  -- 'usr_' + ULID
  primary_email         citext NOT NULL,
  primary_email_norm    text GENERATED ALWAYS AS (lower(primary_email)) STORED,
  email_verified        boolean NOT NULL DEFAULT false,
  status                text NOT NULL CHECK (status IN ('active', 'locked', 'disabled', 'pending_verification')),
  home_tenant_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 users_primary_email_uk ON identity.users (primary_email_norm);
CREATE INDEX users_home_tenant_idx ON identity.users (home_tenant_id);
CREATE INDEX users_status_idx ON identity.users (status) WHERE status IN ('locked', 'pending_verification');

-- RLS: users table is NOT tenant-scoped by RLS (users are global principals).
-- Access control is via application-layer ABAC + platform-admin only for direct DB access.
ALTER TABLE identity.users ENABLE ROW LEVEL SECURITY;
CREATE POLICY users_platform_admin ON identity.users
  USING (current_setting('app.role', true) = 'platform_admin');
CREATE POLICY users_self_access ON identity.users
  USING (id = current_setting('app.user_id', true));

2.2 credentials

CREATE TABLE identity.credentials (
  id                    text PRIMARY KEY,  -- 'crd_' + ULID
  user_id               text NOT NULL REFERENCES identity.users(id) ON DELETE CASCADE,
  kind                  text NOT NULL CHECK (kind IN ('password', 'webauthn', 'magic_link')),
  hash                  text,               -- argon2id string, for password
  webauthn_credential   jsonb,              -- WebAuthn public key material
  rotated_at            timestamptz NOT NULL DEFAULT now(),
  failed_attempts       integer NOT NULL DEFAULT 0,
  locked_until          timestamptz,
  rotation_history      text[] NOT NULL DEFAULT '{}',  -- last 5 password hashes, for rotation policy
  created_at            timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX credentials_user_idx ON identity.credentials (user_id);
CREATE UNIQUE INDEX credentials_user_password_uk ON identity.credentials (user_id) WHERE kind = 'password';

ALTER TABLE identity.credentials ENABLE ROW LEVEL SECURITY;
CREATE POLICY credentials_platform_admin ON identity.credentials
  USING (current_setting('app.role', true) = 'platform_admin');
CREATE POLICY credentials_self ON identity.credentials
  USING (user_id = current_setting('app.user_id', true));

2.3 sessions

CREATE TABLE identity.sessions (
  id                    text PRIMARY KEY,  -- 'ses_' + ULID
  user_id               text NOT NULL REFERENCES identity.users(id) ON DELETE CASCADE,
  device_id             text REFERENCES identity.devices(id) ON DELETE SET NULL,
  tenant_id             text NOT NULL,
  issued_at             timestamptz NOT NULL DEFAULT now(),
  expires_at            timestamptz NOT NULL,
  refresh_token_hash    text NOT NULL,
  previous_token_hashes text[] NOT NULL DEFAULT '{}',  -- detect rotation reuse
  ip                    inet NOT NULL,
  ua                    text NOT NULL,
  amr                   text[] NOT NULL,
  revoked_at            timestamptz,
  revoked_reason        text,
  session_family_id     text NOT NULL         -- shared across rotated tokens
);
CREATE INDEX sessions_user_expires_idx ON identity.sessions (user_id, expires_at) WHERE revoked_at IS NULL;
CREATE UNIQUE INDEX sessions_refresh_hash_uk ON identity.sessions (refresh_token_hash);
CREATE INDEX sessions_tenant_idx ON identity.sessions (tenant_id);
CREATE INDEX sessions_family_idx ON identity.sessions (session_family_id);

ALTER TABLE identity.sessions ENABLE ROW LEVEL SECURITY;
CREATE POLICY sessions_tenant_isolation ON identity.sessions
  USING (tenant_id = current_setting('app.tenant_id', true));
CREATE POLICY sessions_self ON identity.sessions
  USING (user_id = current_setting('app.user_id', true));

2.4 devices

CREATE TABLE identity.devices (
  id                      text PRIMARY KEY,  -- 'dev_' + ULID
  user_id                 text NOT NULL REFERENCES identity.users(id) ON DELETE CASCADE,
  fingerprint             text NOT NULL,       -- SHA-256
  public_key              text NOT NULL,        -- PEM
  public_key_fingerprint  text NOT NULL,       -- SHA-256 of public key
  user_agent              text NOT NULL,
  trusted_at              timestamptz,
  last_seen_at            timestamptz NOT NULL DEFAULT now(),
  offline_cert            text,                 -- X.509 PEM
  offline_cert_expires_at timestamptz,
  offline_cert_issued_at  timestamptz,
  offline_cert_kid        text,                 -- CA signing key ID
  revoked_at              timestamptz,
  revoked_reason          text,
  created_at              timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX devices_user_fingerprint_uk ON identity.devices (user_id, fingerprint);
CREATE INDEX devices_user_idx ON identity.devices (user_id);
CREATE INDEX devices_offline_cert_expires_idx ON identity.devices (offline_cert_expires_at) WHERE offline_cert IS NOT NULL AND revoked_at IS NULL;

ALTER TABLE identity.devices ENABLE ROW LEVEL SECURITY;
CREATE POLICY devices_self ON identity.devices
  USING (user_id = current_setting('app.user_id', true));
CREATE POLICY devices_platform_admin ON identity.devices
  USING (current_setting('app.role', true) = 'platform_admin');

2.5 mfa_factors

CREATE TABLE identity.mfa_factors (
  id                text PRIMARY KEY,  -- 'mfa_' + ULID
  user_id           text NOT NULL REFERENCES identity.users(id) ON DELETE CASCADE,
  kind              text NOT NULL CHECK (kind IN ('totp', 'sms', 'webauthn', 'recovery_codes')),
  metadata          jsonb NOT NULL,     -- encrypted at application layer for TOTP secret, phone, codes
  enrolled_at       timestamptz NOT NULL DEFAULT now(),
  last_used_at      timestamptz,
  verified          boolean NOT NULL DEFAULT false,
  disabled_at       timestamptz
);
CREATE INDEX mfa_factors_user_idx ON identity.mfa_factors (user_id) WHERE disabled_at IS NULL;
CREATE UNIQUE INDEX mfa_factors_user_kind_uk ON identity.mfa_factors (user_id, kind)
  WHERE kind IN ('totp', 'sms') AND disabled_at IS NULL;

ALTER TABLE identity.mfa_factors ENABLE ROW LEVEL SECURITY;
CREATE POLICY mfa_self ON identity.mfa_factors
  USING (user_id = current_setting('app.user_id', true));

2.6 api_keys

CREATE TABLE identity.api_keys (
  id              text PRIMARY KEY,  -- 'apk_' + ULID
  tenant_id       text NOT NULL,
  owner_user_id   text REFERENCES identity.users(id) ON DELETE SET NULL,
  name            text NOT NULL,
  prefix          text NOT NULL,      -- first 8 chars of raw key
  hash            text NOT NULL,      -- SHA-256 of raw key
  scopes          text[] NOT NULL,
  expires_at      timestamptz,
  created_at      timestamptz NOT NULL DEFAULT now(),
  created_by      text NOT NULL REFERENCES identity.users(id),
  last_used_at    timestamptz,
  revoked_at      timestamptz,
  revoked_reason  text,
  revoked_by      text REFERENCES identity.users(id)
);
CREATE UNIQUE INDEX api_keys_hash_uk ON identity.api_keys (hash);
CREATE INDEX api_keys_tenant_idx ON identity.api_keys (tenant_id);
CREATE INDEX api_keys_prefix_idx ON identity.api_keys (prefix);
CREATE INDEX api_keys_active_idx ON identity.api_keys (tenant_id) WHERE revoked_at IS NULL;

ALTER TABLE identity.api_keys ENABLE ROW LEVEL SECURITY;
CREATE POLICY api_keys_tenant_isolation ON identity.api_keys
  USING (tenant_id = current_setting('app.tenant_id', true));

2.7 external_identities

CREATE TABLE identity.external_identities (
  id           text PRIMARY KEY,       -- 'eid_' + ULID
  user_id      text NOT NULL REFERENCES identity.users(id) ON DELETE CASCADE,
  provider     text NOT NULL CHECK (provider IN ('oidc', 'saml', 'google', 'microsoft')),
  subject      text NOT NULL,
  issuer       text NOT NULL,
  metadata     jsonb NOT NULL DEFAULT '{}',
  linked_at    timestamptz NOT NULL DEFAULT now(),
  last_login_at timestamptz
);
CREATE UNIQUE INDEX external_identities_provider_subject_uk
  ON identity.external_identities (provider, issuer, subject);
CREATE INDEX external_identities_user_idx ON identity.external_identities (user_id);

ALTER TABLE identity.external_identities ENABLE ROW LEVEL SECURITY;
CREATE POLICY external_identities_self ON identity.external_identities
  USING (user_id = current_setting('app.user_id', true));

2.8 outbox (event producer)

CREATE TABLE identity.outbox (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  tenant_id     text NOT NULL,
  topic         text NOT NULL,
  envelope      jsonb NOT NULL,
  partition_key text NOT NULL,
  published_at  timestamptz,
  attempts      integer NOT NULL DEFAULT 0,
  last_error    text
);
CREATE INDEX outbox_unpublished_idx ON identity.outbox (occurred_at) WHERE published_at IS NULL;
CREATE INDEX outbox_tenant_idx ON identity.outbox (tenant_id);
CREATE INDEX outbox_partition_idx ON identity.outbox (partition_key);

2.9 inbox (event consumer)

CREATE TABLE identity.inbox (
  event_id     text PRIMARY KEY,
  consumer     text NOT NULL,
  processed_at timestamptz NOT NULL DEFAULT now(),
  result       text NOT NULL
);
CREATE INDEX inbox_processed_idx ON identity.inbox (processed_at);

2.10 audit_log

CREATE TABLE identity.audit_log (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  tenant_id     text,
  user_id       text,
  action        text NOT NULL,
  target_type   text,
  target_id     text,
  result        text NOT NULL CHECK (result IN ('success', 'failure')),
  reason        text,
  ip            inet,
  ua            text,
  trace_id      text,
  metadata      jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX audit_log_user_idx ON identity.audit_log (user_id, occurred_at DESC);
CREATE INDEX audit_log_tenant_idx ON identity.audit_log (tenant_id, occurred_at DESC);
CREATE INDEX audit_log_action_idx ON identity.audit_log (action, occurred_at DESC);

-- Append-only: no UPDATE or DELETE except for retention cleanup
CREATE POLICY audit_log_append_only ON identity.audit_log FOR INSERT WITH CHECK (true);
REVOKE UPDATE, DELETE ON identity.audit_log FROM PUBLIC;

2.11 idempotency_keys (optional DB persistence; primary storage is Redis)

CREATE TABLE identity.idempotency_keys (
  tenant_id        text NOT NULL,
  user_id          text,
  route            text NOT NULL,
  key              text NOT NULL,
  request_hash     text NOT NULL,
  response_status  integer NOT NULL,
  response_body    jsonb NOT NULL,
  created_at       timestamptz NOT NULL DEFAULT now(),
  expires_at       timestamptz NOT NULL,
  PRIMARY KEY (tenant_id, user_id, route, key)
);
CREATE INDEX idempotency_keys_expires_idx ON identity.idempotency_keys (expires_at);

3. Row-Level Security Policy

Per 13 Security §4:

users table: NOT tenant-scoped (users are global); access gated by platform_admin role OR self-access.
sessions, api_keys: tenant-scoped via app.tenant_id.
credentials, mfa_factors, devices, external_identities: user-scoped via app.user_id, enforced in application layer.
outbox, inbox, audit_log: tenant-scoped where applicable; platform-admin access for ops.

Per-connection setup: The application pool wrapper sets on every checkout:

SET LOCAL app.tenant_id = 'ten_...';
SET LOCAL app.user_id = 'usr_...';
SET LOCAL app.role = 'learner';

4. Indexes Summary

Table	Index	Purpose
users	`users_primary_email_uk` UNIQUE	Login lookup, uniqueness
users	`users_home_tenant_idx`	Tenant user lists
users	`users_status_idx` (partial)	Pending user cleanup, lockout sweeps
credentials	`credentials_user_idx`	Credential load on login
credentials	`credentials_user_password_uk` (partial unique)	One password per user invariant
sessions	`sessions_user_expires_idx` (partial)	Active session lookup
sessions	`sessions_refresh_hash_uk` UNIQUE	Refresh token validation
sessions	`sessions_family_idx`	Family revocation on rotation reuse
devices	`devices_user_fingerprint_uk` UNIQUE	Device uniqueness per user
devices	`devices_offline_cert_expires_idx` (partial)	Certificate rotation scans
mfa_factors	`mfa_factors_user_kind_uk` (partial unique)	One TOTP / one SMS per user
api_keys	`api_keys_hash_uk` UNIQUE	API key validation lookup
api_keys	`api_keys_prefix_idx`	Key identification in logs
external_identities	`external_identities_provider_subject_uk` UNIQUE	SSO subject lookup
outbox	`outbox_unpublished_idx` (partial)	Relay polling

5. Redis Keys

Key Pattern	TTL	Purpose
`identity:idem:{tenantId}:{userId}:{route}:{key}`	24h	Idempotency response cache
`identity:rate:{kind}:{key}`	per window	Rate limit counters (token bucket)
`identity:pwreset:{tokenHash}`	1h	Password reset tokens
`identity:emailverify:{tokenHash}`	24h	Email verification tokens
`identity:sso:state:{state}`	5min	OIDC PKCE state
`identity:sso:saml:{requestId}`	5min	SAML request correlation
`identity:mfa:challenge:{token}`	5min	In-flight MFA challenge during login
`identity:jwks`	1h	Cached JWKS response
`identity:lockout:{userId}`	2h	Lockout counters
`identity:magiclink:{tokenHash}`	15min	Magic link tokens

6. Migrations

Using Flyway / Prisma Migrate / node-pg-migrate (pick one at M0).

Migration folder layout:

migrations/
  V001__create_identity_schema.sql
  V002__create_users_table.sql
  V003__create_credentials_table.sql
  V004__create_sessions_table.sql
  V005__create_devices_table.sql
  V006__create_mfa_factors_table.sql
  V007__create_api_keys_table.sql
  V008__create_external_identities_table.sql
  V009__create_outbox_table.sql
  V010__create_inbox_table.sql
  V011__create_audit_log_table.sql
  V012__create_idempotency_keys_table.sql
  V013__enable_rls_policies.sql

Migration rules:

Every migration is reversible (explicit DOWN or separate rollback).
Every migration is backward-compatible within a major service version.
Additive changes (new columns, indexes) are deployed before code changes.
Destructive changes (drop column, rename) require two-phase: deploy code that tolerates both → drop column.
Migrations tested against a production-size clone in CI.

7. Partitioning Strategy (Future)

For scale, partition candidates:

sessions — partition by issued_at (monthly) once > 100M rows.
audit_log — partition by occurred_at (monthly), retention of 7 years.
outbox — partition by occurred_at (weekly) to speed relay scanning; drop published partitions after retention window.

8. Retention

Table	Hot Retention	Archive
users	indefinite (soft-delete via `status=disabled`)	N/A
credentials	indefinite	N/A
sessions	180 days post-revocation	purge thereafter
devices	indefinite (soft via `revoked_at`)	purge 2 years post-revocation
mfa_factors	indefinite	purge 2 years post-disable
api_keys	indefinite (soft via `revoked_at`)	purge 2 years post-revocation
external_identities	indefinite	purge with user deletion
outbox	7 days post-publish	delete
inbox	30 days	delete
audit_log	7 years (regulated)	cold archive to S3
idempotency_keys	24 hours	delete

9. Backup & Recovery

Daily logical backups (pg_dump) to S3, encrypted at rest.
Continuous WAL archiving with 30-day PITR window.
Weekly restore drill to staging; verified via data-integrity checksums.
Per-tenant export capability via analytics-service for GDPR compliance.

10. Data Residency

Postgres cluster is region-pinned. For tenants with non-default residency:

US region: us-east-1, us-west-2 (HA)
EU region: eu-west-1, eu-central-1
ME region: me-south-1
AP region: ap-southeast-1

Cross-region user migration is handled by the data residency migration saga (tenant-service orchestrates; identity-service participates).

11. Performance Targets

Operation	p95 Target
Login (password verify + session insert)	< 100ms
Refresh token rotation	< 30ms
JWKS fetch	< 10ms (Redis-cached)
Device registration	< 50ms
API key validation	< 10ms (Redis-cached hash lookup)
Outbox relay publish	< 100ms per event

1. Storage​

2. Schema DDL​

2.1 users​

2.2 credentials​

2.3 sessions​

2.4 devices​

2.5 mfa_factors​

2.6 api_keys​

2.7 external_identities​

2.8 outbox (event producer)​

2.9 inbox (event consumer)​

2.10 audit_log​

2.11 idempotency_keys (optional DB persistence; primary storage is Redis)​

3. Row-Level Security Policy​

4. Indexes Summary​

5. Redis Keys​

6. Migrations​

7. Partitioning Strategy (Future)​

8. Retention​

9. Backup & Recovery​

10. Data Residency​

11. Performance Targets​