iam-service — Data Model

Catalog summary: docs/03-microservices/iam-service.md · DOMAIN_MODEL · SECURITY_MODEL · 06 Data Models · 07 Security & Tenancy · Standards · NAMING

1. Storage Choices

Store	Engine	Use
Primary	Cloud SQL — PostgreSQL 16 (regional HA)	All durable aggregates, outbox, audit.
Cache	Memorystore — Redis 7 (HA)	Session validate cache, refresh-token reuse window, rate-limit counters, idempotency-key index, magic-link nonces, SSO state.
Sync state	Firestore (multi-region)	Per-device sync cursors for `Device` deltas (read by `sync-service`).
Secret material	Cloud KMS (regional)	JWT signing key (EdDSA Ed25519), tenant device-CA root, breach-list HMAC pepper.
Secrets	Secret Manager	OIDC/SAML client secrets, breach-list API key, SMTP credentials.
Read model (analytics)	BigQuery (federated via Datastream)	Read-only — login funnel, MFA adoption.

PostgreSQL is the system of record; Redis is purely opportunistic. Any value in Redis MUST be re-derivable from Postgres. See 02 §11.

2. ID Conventions

All IDs follow NAMING §11: <prefix>_<26-char Crockford ULID>. Prefixes owned by iam-service:

Aggregate / Entity	Prefix	Sample
`User`	`usr_`	`usr_01HZ8X2K3M4N5P6Q7R8S9T0V1W`
`Credential`	`crd_`	`crd_01HZ…`
`Session`	`ses_`	`ses_01HZ…`
`Device`	`dev_`	`dev_01HZ…`
`MFAFactor`	`mfa_`	`mfa_01HZ…`
`APIKey`	`key_`	`key_01HZ…`
`ExternalIdentity`	`ext_`	`ext_01HZ…`
`RefreshToken`	`rft_`	`rft_01HZ…`
`OfflineBinding`	`obc_`	`obc_01HZ…`
`PasswordResetReq`	`prr_`	`prr_01HZ…`
`MagicLinkNonce`	`mln_`	`mln_01HZ…`

Foreign tenant identifier ten_… originates in tenant-service and is treated as opaque here.

3. Schema Layout

All tables live in the dedicated Postgres schema iam. The schema is created by Flyway migration V001__init.sql. PII tables enable pgcrypto for HMAC and citext for case-insensitive email columns.

CREATE SCHEMA IF NOT EXISTS iam;
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA pg_catalog;
CREATE EXTENSION IF NOT EXISTS citext  WITH SCHEMA pg_catalog;

Encoding UTF-8, time zone UTC, all timestamps timestamptz.

4. Tables

4.1 `iam.users`

CREATE TABLE iam.users (
  id                   text PRIMARY KEY
                          CHECK (id ~ '^usr_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id            text                                              -- nullable for platform.super_admin
                          CHECK (tenant_id IS NULL OR tenant_id ~ '^ten_[0-9A-HJKMNP-TV-Z]{26}$'),
  primary_email        citext NOT NULL,
  primary_email_hmac   bytea  NOT NULL,                                  -- HMAC-SHA256(pepper, email) for breach lookups
  email_verified       boolean NOT NULL DEFAULT false,
  status               text   NOT NULL
                          CHECK (status IN ('active','locked','disabled','pending_verification','anonymized')),
  user_type            text   NOT NULL
                          CHECK (user_type IN ('staff','guest','platform_admin')),
  home_tenant_id       text,
  locale               text   NOT NULL DEFAULT 'en-US',
  failed_attempts      smallint NOT NULL DEFAULT 0,
  locked_until         timestamptz,
  version              integer NOT NULL DEFAULT 1,
  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now(),
  anonymized_at        timestamptz
);

CREATE UNIQUE INDEX users_email_uk          ON iam.users (lower(primary_email)) WHERE status <> 'anonymized';
CREATE        INDEX users_tenant_idx        ON iam.users (tenant_id);
CREATE        INDEX users_status_idx        ON iam.users (status) WHERE status IN ('locked','pending_verification');
CREATE        INDEX users_email_hmac_idx    ON iam.users (primary_email_hmac);

ALTER TABLE iam.users ENABLE ROW LEVEL SECURITY;

-- Tenant-isolated users
CREATE POLICY users_tenant_rw ON iam.users
  USING (
    tenant_id IS NULL                                                     -- platform users always visible to platform_admin policy
    OR tenant_id = current_setting('app.tenant_id', true)
  );

-- Platform admin escape hatch
CREATE POLICY users_platform_admin ON iam.users
  USING (current_setting('app.role', true) = 'platform_admin');

-- Self-access (for `/users/me`)
CREATE POLICY users_self ON iam.users
  USING (id = current_setting('app.user_id', true));

Notes:

primary_email_hmac is computed in the application layer using the KMS-managed HMAC pepper; never stored alongside the plain email in logs.
version participates in optimistic concurrency on every update. Update statement: UPDATE iam.users SET …, version = version + 1, updated_at = now() WHERE id = $1 AND version = $2.

4.2 `iam.credentials`

CREATE TABLE iam.credentials (
  id                   text PRIMARY KEY CHECK (id ~ '^crd_[0-9A-HJKMNP-TV-Z]{26}$'),
  user_id              text NOT NULL REFERENCES iam.users(id) ON DELETE CASCADE,
  kind                 text NOT NULL CHECK (kind IN ('password','webauthn','magic_link')),
  password_hash        text,                                              -- argon2id encoded string
  password_algo_v      smallint,                                          -- bumped on hashing-param change
  webauthn_credential  jsonb,                                             -- { credentialId, publicKey, counter, transports[] }
  rotation_history     text[] NOT NULL DEFAULT ARRAY[]::text[],           -- last 5 password hashes
  last_rotated_at      timestamptz NOT NULL DEFAULT now(),
  breached_at          timestamptz,                                       -- set when matched in HIBP
  created_at           timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT crd_password_xor_webauthn
    CHECK ((kind = 'password'   AND password_hash       IS NOT NULL AND webauthn_credential IS NULL)
        OR (kind = 'webauthn'   AND password_hash       IS NULL     AND webauthn_credential IS NOT NULL)
        OR (kind = 'magic_link' AND password_hash       IS NULL     AND webauthn_credential IS NULL))
);

CREATE UNIQUE INDEX credentials_user_password_uk ON iam.credentials (user_id) WHERE kind = 'password';
CREATE        INDEX credentials_user_idx         ON iam.credentials (user_id);

ALTER TABLE iam.credentials ENABLE ROW LEVEL SECURITY;
CREATE POLICY credentials_owner ON iam.credentials
  USING (user_id IN (SELECT id FROM iam.users));        -- delegates to users RLS

4.3 `iam.sessions`

CREATE TABLE iam.sessions (
  id                       text PRIMARY KEY CHECK (id ~ '^ses_[0-9A-HJKMNP-TV-Z]{26}$'),
  user_id                  text NOT NULL REFERENCES iam.users(id) ON DELETE CASCADE,
  tenant_id                text NOT NULL,
  device_id                text REFERENCES iam.devices(id) ON DELETE SET NULL,
  session_family_id        text NOT NULL,                                  -- shared across rotations
  refresh_token_hash       bytea NOT NULL,                                  -- SHA-256 of opaque refresh
  previous_token_hashes    bytea[] NOT NULL DEFAULT ARRAY[]::bytea[],      -- last 5; reuse detection
  amr                      text[] NOT NULL,                                 -- pwd, totp, webauthn, magic_link, sso
  ip_masked                inet  NOT NULL,                                  -- /24 v4, /48 v6
  user_agent               text  NOT NULL,
  issued_at                timestamptz NOT NULL DEFAULT now(),
  expires_at               timestamptz NOT NULL,
  rotated_at               timestamptz NOT NULL DEFAULT now(),
  revoked_at               timestamptz,
  revoked_reason           text                                            -- logout|reuse|admin|password_change|tenant_deleted|gdpr
);

CREATE UNIQUE INDEX sessions_refresh_hash_uk     ON iam.sessions (refresh_token_hash);
CREATE        INDEX sessions_user_active_idx     ON iam.sessions (user_id, expires_at) WHERE revoked_at IS NULL;
CREATE        INDEX sessions_family_idx          ON iam.sessions (session_family_id);
CREATE        INDEX sessions_tenant_idx          ON iam.sessions (tenant_id);

ALTER TABLE iam.sessions ENABLE ROW LEVEL SECURITY;
CREATE POLICY sessions_tenant ON iam.sessions
  USING (tenant_id = current_setting('app.tenant_id', true));
CREATE POLICY sessions_self ON iam.sessions
  USING (user_id = current_setting('app.user_id', true));
CREATE POLICY sessions_platform_admin ON iam.sessions
  USING (current_setting('app.role', true) = 'platform_admin');

4.4 `iam.devices`

CREATE TABLE iam.devices (
  id                   text PRIMARY KEY CHECK (id ~ '^dev_[0-9A-HJKMNP-TV-Z]{26}$'),
  user_id              text NOT NULL REFERENCES iam.users(id) ON DELETE CASCADE,
  tenant_id            text NOT NULL,
  fingerprint          text NOT NULL,                                       -- HMAC over normalised attributes
  public_key           bytea NOT NULL,                                       -- Ed25519 (raw)
  os_family            text NOT NULL,
  app_version          text NOT NULL,
  display_name         text,
  trusted              boolean NOT NULL DEFAULT false,
  first_seen_at        timestamptz NOT NULL DEFAULT now(),
  last_seen_at         timestamptz NOT NULL DEFAULT now(),
  revoked_at           timestamptz,
  revoked_reason       text
);

CREATE UNIQUE INDEX devices_user_fingerprint_uk ON iam.devices (user_id, fingerprint) WHERE revoked_at IS NULL;
CREATE        INDEX devices_tenant_idx          ON iam.devices (tenant_id);

ALTER TABLE iam.devices ENABLE ROW LEVEL SECURITY;
CREATE POLICY devices_tenant ON iam.devices
  USING (tenant_id = current_setting('app.tenant_id', true));
CREATE POLICY devices_self ON iam.devices
  USING (user_id = current_setting('app.user_id', true));

4.5 `iam.device_offline_bindings`

CREATE TABLE iam.device_offline_bindings (
  id                   text PRIMARY KEY CHECK (id ~ '^obc_[0-9A-HJKMNP-TV-Z]{26}$'),
  device_id            text NOT NULL REFERENCES iam.devices(id) ON DELETE CASCADE,
  tenant_id            text NOT NULL,
  certificate_pem      text NOT NULL,                                       -- X.509 with Ed25519 pubkey, signed by tenant CA
  serial               text NOT NULL,
  issued_at            timestamptz NOT NULL DEFAULT now(),
  not_after            timestamptz NOT NULL,                                 -- max issued_at + 7d
  max_offline_grace_h  smallint NOT NULL DEFAULT 168,                        -- ≤ 168
  revoked_at           timestamptz
);

CREATE UNIQUE INDEX dob_serial_uk      ON iam.device_offline_bindings (serial);
CREATE        INDEX dob_device_active  ON iam.device_offline_bindings (device_id) WHERE revoked_at IS NULL;
CREATE        INDEX dob_tenant_idx     ON iam.device_offline_bindings (tenant_id);

ALTER TABLE iam.device_offline_bindings ENABLE ROW LEVEL SECURITY;
CREATE POLICY dob_tenant ON iam.device_offline_bindings
  USING (tenant_id = current_setting('app.tenant_id', true));

4.6 `iam.mfa_factors`

CREATE TABLE iam.mfa_factors (
  id                   text PRIMARY KEY CHECK (id ~ '^mfa_[0-9A-HJKMNP-TV-Z]{26}$'),
  user_id              text NOT NULL REFERENCES iam.users(id) ON DELETE CASCADE,
  kind                 text NOT NULL CHECK (kind IN ('totp','webauthn','recovery_codes')),
  label                text,
  totp_secret_enc      bytea,                                                -- KMS-wrapped DEK envelope
  webauthn_credential  jsonb,
  recovery_codes_hash  text[],                                               -- 10 × SHA-256, single-use
  consumed_codes_idx   smallint[] NOT NULL DEFAULT ARRAY[]::smallint[],
  enrolled_at          timestamptz NOT NULL DEFAULT now(),
  last_used_at         timestamptz,
  disabled_at          timestamptz
);

CREATE INDEX mfa_user_idx ON iam.mfa_factors (user_id) WHERE disabled_at IS NULL;
CREATE UNIQUE INDEX mfa_user_totp_uk ON iam.mfa_factors (user_id) WHERE kind = 'totp' AND disabled_at IS NULL;

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

4.7 `iam.api_keys`

CREATE TABLE iam.api_keys (
  id                   text PRIMARY KEY CHECK (id ~ '^key_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id            text NOT NULL,
  user_id              text REFERENCES iam.users(id) ON DELETE SET NULL,
  prefix               text NOT NULL,                                        -- first 8 chars, safe to log
  hash                 text NOT NULL,                                        -- argon2id of secret
  scopes               text[] NOT NULL,
  description          text,
  created_by           text NOT NULL,                                         -- usr_… or actor system id
  created_at           timestamptz NOT NULL DEFAULT now(),
  last_used_at         timestamptz,
  expires_at           timestamptz,
  revoked_at           timestamptz,
  revoked_reason       text
);

CREATE UNIQUE INDEX api_keys_prefix_uk ON iam.api_keys (prefix);
CREATE        INDEX api_keys_tenant_idx ON iam.api_keys (tenant_id);

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

4.8 `iam.external_identities`

CREATE TABLE iam.external_identities (
  id                   text PRIMARY KEY CHECK (id ~ '^ext_[0-9A-HJKMNP-TV-Z]{26}$'),
  user_id              text NOT NULL REFERENCES iam.users(id) ON DELETE CASCADE,
  provider             text NOT NULL,                                         -- google|microsoft|saml:<idp>|oidc:<idp>
  external_subject     text NOT NULL,
  raw_claims           jsonb NOT NULL,
  linked_at            timestamptz NOT NULL DEFAULT now(),
  last_login_at        timestamptz
);

CREATE UNIQUE INDEX ext_provider_subject_uk ON iam.external_identities (provider, external_subject);
CREATE        INDEX ext_user_idx            ON iam.external_identities (user_id);

4.9 `iam.password_reset_requests`

CREATE TABLE iam.password_reset_requests (
  id                   text PRIMARY KEY CHECK (id ~ '^prr_[0-9A-HJKMNP-TV-Z]{26}$'),
  user_id              text NOT NULL REFERENCES iam.users(id) ON DELETE CASCADE,
  token_hash           bytea NOT NULL,                                        -- SHA-256 of opaque token
  requested_at         timestamptz NOT NULL DEFAULT now(),
  expires_at           timestamptz NOT NULL,
  consumed_at          timestamptz,
  ip_masked            inet NOT NULL
);

CREATE UNIQUE INDEX prr_token_uk ON iam.password_reset_requests (token_hash);
CREATE        INDEX prr_user_active ON iam.password_reset_requests (user_id) WHERE consumed_at IS NULL;

4.10 `iam.magic_link_nonces`

Stored in Redis with EXPIRE, mirrored to Postgres only for audit:

CREATE TABLE iam.magic_link_nonces (
  id                   text PRIMARY KEY CHECK (id ~ '^mln_[0-9A-HJKMNP-TV-Z]{26}$'),
  email_hmac           bytea NOT NULL,
  token_hash           bytea NOT NULL,
  tenant_id            text,
  issued_at            timestamptz NOT NULL DEFAULT now(),
  expires_at           timestamptz NOT NULL,
  consumed_at          timestamptz
);
CREATE UNIQUE INDEX mln_token_uk ON iam.magic_link_nonces (token_hash);

4.11 `iam.audit_events`

CREATE TABLE iam.audit_events (
  id                   bigserial PRIMARY KEY,
  occurred_at          timestamptz NOT NULL DEFAULT now(),
  tenant_id            text,
  actor_user_id        text,
  actor_type           text NOT NULL,                                          -- user|system|api_key|sso
  action               text NOT NULL,                                          -- e.g., user.login.succeeded
  target_type          text NOT NULL,                                          -- user|session|device|api_key|mfa_factor
  target_id            text,
  ip_masked            inet,
  user_agent           text,
  metadata             jsonb NOT NULL DEFAULT '{}'::jsonb,
  prev_chain_hash      bytea,                                                  -- previous record hash
  chain_hash           bytea NOT NULL                                          -- SHA-256(prev_chain_hash || canonical(this))
) PARTITION BY RANGE (occurred_at);

-- 1 partition per month
CREATE TABLE iam.audit_events_2026_04 PARTITION OF iam.audit_events
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE INDEX audit_tenant_time_idx ON iam.audit_events (tenant_id, occurred_at);
CREATE INDEX audit_action_idx      ON iam.audit_events (action);

Audit rows are append-only — REVOKE UPDATE, DELETE from every role except the dedicated GDPR erasure principal.

4.12 `iam.outbox`

Standard transactional outbox (see 02 §11.2). Same shape as platform template; not reproduced here.

4.13 `iam.idempotency_keys`

CREATE TABLE iam.idempotency_keys (
  key                  text PRIMARY KEY,
  tenant_id            text,
  request_fingerprint  bytea NOT NULL,                                         -- SHA-256 of method+path+body
  response_status      smallint NOT NULL,
  response_body        jsonb NOT NULL,
  created_at           timestamptz NOT NULL DEFAULT now(),
  expires_at           timestamptz NOT NULL                                    -- now() + 24h
);
CREATE INDEX idemp_expires_idx ON iam.idempotency_keys (expires_at);

A nightly job deletes WHERE expires_at < now().

5. RLS Activation Pattern

Every connection checked out from pgbouncer first runs:

SET LOCAL app.tenant_id = $1;          -- tenant ULID or empty
SET LOCAL app.user_id   = $2;
SET LOCAL app.role      = $3;          -- guest|staff|platform_admin|system

These GUCs are referenced by every policy. A dedicated test verifies that no IAM table is readable when app.tenant_id is unset.

6. Indexing & Performance

Concern	Index
Login by email	`users_email_uk` (unique, lower(email))
Refresh validate	`sessions_refresh_hash_uk`
Session reuse detect	`sessions_family_idx`
Rate-limit / lockout scan	`users_status_idx` (partial on locked)
API key lookup	`api_keys_prefix_uk` (key prefix only — body verified via argon2)
Device dedup	`devices_user_fingerprint_uk`

Target plan: every write path ≤ 3 index lookups; every read path on a single index. EXPLAIN-asserts run in CI for the hot 12 queries.

7. Encryption Posture

Datum	Mechanism
Password	argon2id (`m=64MB, t=3, p=1, len=32`)
Refresh token	client receives opaque; DB stores SHA-256
TOTP secret	KMS-wrapped DEK (envelope), unwrap only inside enclave-style Cloud Run with attached CMEK
Device public key	plain (public material)
Email (analytics)	HMAC-SHA256(pepper) — pepper in KMS
Audit chain	per-row SHA-256 hash chain; daily Merkle root anchored to BigQuery + GCS WORM

See SECURITY_MODEL §4.

8. Retention & Erasure

Table	Retention	GDPR Erasure
`users`	indefinite while account active; tombstone on `anonymized`	Set `status='anonymized'`, null PII columns, retain row for FK integrity.
`credentials`	hard-delete on user erasure	Cascading delete from `users`.
`sessions`	90 d after `expires_at`; revoke on erasure	Hard-delete.
`devices`	1 y after `revoked_at`	Hard-delete on erasure.
`mfa_factors`	hard-delete on erasure	Cascading.
`api_keys`	7 y (regulated) — value already hashed	Tenant-scoped retention; survives user erasure.
`external_identities`	hard-delete on erasure	Cascading.
`audit_events`	7 y (regulated) — never deleted by erasure	Pseudonymise actor_user_id only.

9. Migration Discipline

All schema changes via Flyway under db/migrations/V<n>__<slug>.sql.
Backwards-compatible only inside a release; breaking changes require expand → contract over two releases.
CI runs migrations against ephemeral Cloud SQL fork + production fixture before PR merge.
Rollback: every migration ships an idempotent Vn_rollback.sql.

10. Cross-References

Postgres tenant pattern: 07 §3
Outbox pattern: 02 §11
Branded IDs / NAMING: Standards · NAMING
Failure handling for KMS / Postgres: FAILURE_MODES

1. Storage Choices​

2. ID Conventions​

3. Schema Layout​

4. Tables​

4.1 iam.users​

4.2 iam.credentials​

4.3 iam.sessions​

4.4 iam.devices​

4.5 iam.device_offline_bindings​

4.6 iam.mfa_factors​

4.7 iam.api_keys​

4.8 iam.external_identities​

4.9 iam.password_reset_requests​

4.10 iam.magic_link_nonces​

4.11 iam.audit_events​

4.12 iam.outbox​

4.13 iam.idempotency_keys​

5. RLS Activation Pattern​

6. Indexing & Performance​

7. Encryption Posture​

8. Retention & Erasure​

9. Migration Discipline​

10. Cross-References​