Auth Service — Data Model

Status: populated Owner: Platform Engineering + Security Last updated: 2026-04-19

Change log

• v1.2 (2026-04-19) — Multi-IdP rebaseline: added tenant_identity_providers, external_identities, and idp_session_audit tables; replaced users.firebase_uid with provider-agnostic federation via external_identities; added users.primary_provider_id. Keycloak manages its own keycloak schema in the same PostgreSQL instance (outside this service's ownership).

Schema: auth. Owned exclusively by auth-service. Keycloak maintains a separate keycloak schema which is not read by auth-service directly (interaction is via Keycloak Admin REST + OIDC endpoints).

1. Tables

CREATE TABLE auth.accounts (
  account_id   UUID PRIMARY KEY,
  tenant_id    UUID NOT NULL,
  name         TEXT NOT NULL,
  plan_id      TEXT NOT NULL,
  status       TEXT NOT NULL CHECK (status IN ('active','suspended','deleted')),
  quota_tier   TEXT NOT NULL DEFAULT 'standard',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE auth.users (
  user_id               UUID PRIMARY KEY,
  account_id            UUID NOT NULL REFERENCES auth.accounts(account_id) ON DELETE CASCADE,
  email                 TEXT NOT NULL,
  primary_provider_id   TEXT NOT NULL,        -- 'keycloak' | 'tenant-oidc:<tid>' | 'tenant-saml:<tid>' | 'firebase-legacy' | 'native'
  password_hash         TEXT,                 -- argon2id; NULL unless primary_provider_id = 'native'
  totp_secret           TEXT,                 -- encrypted (KMS); only for native / Keycloak-managed users
  status                TEXT NOT NULL CHECK (status IN ('active','locked','suspended','deleted')),
  failed_attempts       SMALLINT NOT NULL DEFAULT 0,
  locked_until          TIMESTAMPTZ,
  last_login_at         TIMESTAMPTZ,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (account_id, email)
);

CREATE TABLE auth.tenant_identity_providers (
  provider_id          TEXT PRIMARY KEY,       -- 'keycloak' | 'tenant-oidc:<tid>' | 'tenant-saml:<tid>' | 'firebase-legacy'
  tenant_id            UUID NOT NULL,
  kind                 TEXT NOT NULL CHECK (kind IN ('oidc','saml','firebase','native')),
  status               TEXT NOT NULL CHECK (status IN ('active','disabled','failing')),
  is_default           BOOLEAN NOT NULL DEFAULT FALSE,
  discovery_url        TEXT,                   -- OIDC
  metadata_ref         TEXT,                   -- SAML metadata URL or Vault ref
  keycloak_idp_alias   TEXT,                   -- alias in Keycloak realm when brokered
  attribute_mappers    JSONB NOT NULL DEFAULT '{}',
  last_validated_at    TIMESTAMPTZ,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ux_tip_default_per_tenant
  ON auth.tenant_identity_providers(tenant_id) WHERE is_default = TRUE;

CREATE TABLE auth.external_identities (
  user_id                 UUID NOT NULL REFERENCES auth.users(user_id) ON DELETE CASCADE,
  provider_id             TEXT NOT NULL REFERENCES auth.tenant_identity_providers(provider_id),
  external_subject        TEXT NOT NULL,
  linked_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_authenticated_at   TIMESTAMPTZ,
  claims                  JSONB,              -- trimmed, normalised
  PRIMARY KEY (provider_id, external_subject)
);
CREATE INDEX ix_external_identities_user ON auth.external_identities(user_id);

CREATE TABLE auth.idp_session_audit (
  audit_id       UUID PRIMARY KEY,
  provider_id    TEXT NOT NULL REFERENCES auth.tenant_identity_providers(provider_id),
  user_id        UUID REFERENCES auth.users(user_id),
  tenant_id      UUID NOT NULL,
  outcome        TEXT NOT NULL CHECK (outcome IN ('success','failure')),
  failure_reason TEXT,
  ip             INET,
  user_agent     TEXT,
  trace_id       TEXT,
  occurred_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_idp_audit_tenant_time ON auth.idp_session_audit(tenant_id, occurred_at DESC);

CREATE TABLE auth.api_keys (
  key_id        TEXT PRIMARY KEY,       -- ULID key_*
  account_id    UUID NOT NULL REFERENCES auth.accounts(account_id) ON DELETE CASCADE,
  key_hash      TEXT NOT NULL UNIQUE,   -- sha256(raw key)
  scopes        TEXT[] NOT NULL,
  status        TEXT NOT NULL CHECK (status IN ('active','revoked','expired')),
  created_by    UUID NOT NULL REFERENCES auth.users(user_id),
  last_used_at  TIMESTAMPTZ,
  expires_at    TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_api_keys_hash ON auth.api_keys(key_hash) WHERE status = 'active';

CREATE TABLE auth.refresh_tokens (
  token_id      UUID PRIMARY KEY,
  user_id       UUID NOT NULL REFERENCES auth.users(user_id) ON DELETE CASCADE,
  token_hash    TEXT NOT NULL UNIQUE,
  expires_at    TIMESTAMPTZ NOT NULL,
  revoked_at    TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE auth.rbac_roles (
  role_id   TEXT PRIMARY KEY,           -- 'account.admin' etc.
  name      TEXT NOT NULL,
  scopes    TEXT[] NOT NULL
);

CREATE TABLE auth.user_roles (
  user_id    UUID NOT NULL REFERENCES auth.users(user_id) ON DELETE CASCADE,
  role_id    TEXT NOT NULL REFERENCES auth.rbac_roles(role_id),
  assigned_by UUID NOT NULL,
  assigned_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, role_id)
);

CREATE TABLE auth.jwk_keys (
  kid          TEXT PRIMARY KEY,
  alg          TEXT NOT NULL,
  status       TEXT NOT NULL CHECK (status IN ('active','next','retiring')),
  public_jwk   JSONB NOT NULL,
  vault_path   TEXT NOT NULL,       -- pointer to private key in Vault
  activated_at TIMESTAMPTZ,
  retires_at   TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE auth.outbox (
  event_id     UUID PRIMARY KEY,
  subject      TEXT NOT NULL,
  payload      JSONB NOT NULL,
  published_at TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_outbox_unpublished ON auth.outbox(created_at) WHERE published_at IS NULL;

2. Redis keys

Key	TTL	Purpose
auth:session:{userId}	15m	Access token blacklist check (rare — on logout)
auth:jwks	5m	JWKS response cache
auth:apikey:{hash}	30s	API key lookup response cache (mirrors what Kong caches)
auth:loginattempts:{email}	15m	Failed login counter
auth:idp:cfg:{tenantId}	5m	Tenant → provider binding + mapper config
auth:idp:oidc:jwks:{providerId}	10m	Cached JWKS for upstream tenant OIDC IdPs (via Keycloak broker discovery)
auth:scim:token:{tenantId}	—	Opaque bearer-token hash index (no TTL; rotated on regeneration)

3. RLS

Accounts, users, api_keys, refresh_tokens all have RLS on account_id (scoped to caller account). Admin bypass via platform-admin role with audit log.

4. ID prefixes

Prefix	Entity
key_	ApiKey (ULID)
jwt_	JWT jti
ses_	Session id (refresh token row)
k_	JWK kid

Users + accounts use UUIDv4 directly.

5. PII + encryption

• totp_secret encrypted at rest via AWS KMS / Vault Transit.
• password_hash argon2id (already one-way).
• email not encrypted but masked in downstream analytics.