Database Schema

The entity pages describe what each field means to an API caller. This page is for an implementer: concrete Postgres types, constraints, and the indexes the documented query patterns actually need. See Deployment Architecture for why Postgres (Aurora Serverless v2) is the chosen engine.

1. Conventions used below
2. users
3. user_identities, sso_connections
4. sessions, refresh_tokens, auth_tokens, mfa_recovery_codes
5. organizations
6. organization_memberships
7. subscriptions
8. tenants
9. applications
10. entitlements
11. roles, user_role_assignments
12. profiles, app_profiles, settings, app_settings
    1. Typed fields: per-application views and expression indexes
13. audit_events
14. api_keys
15. audit_exports
16. platform_settings
17. scim_tokens
18. webhook_subscriptions, webhook_events, webhook_deliveries
19. push_credentials, devices, notifications, notification_deliveries
20. tier_change_requests, erasure_requests
21. stripe_events
22. idempotency_keys

---

Conventions used below

• Every table’s primary key is the entity’s own prefixed id (usr_…, ent_…, …) stored as text, not a surrogate bigint — the prefix convention in Conventions → IDs is the primary key, not a display layer on top of one.
• Every end-user-scoped table carries organization_id text null references organizations(id), per Non-Functional Requirements → Multi-tenancy, scoped in the data-access layer (see Non-Functional Requirements → Multi-tenancy) — not repeated per-table below. This is team grouping, not infrastructure placement.
• Every table scoped to one Application also carries tenant_id text not null references tenants(id), denormalized from applications.tenant_id at write time — a second, independent scoping dimension, enforced in the data-access layer and used to route a row to the right physical cluster (see Tenancy and Deployment Architecture → Tenancy tiers). Don’t conflate this with organization_id above — a row can carry both, either, or neither.
• Every table carries created_at timestamptz not null default now() (the two pure join tables name it for what it means: organization_memberships.invited_at and user_role_assignments.assigned_at); tables with mutable fields also carry updated_at timestamptz not null default now(), maintained by a trigger, not application code (so it’s correct even for a direct UPDATE run by a migration or a support script).
• Soft-deletable tables carry deleted_at timestamptz null rather than a boolean — null means active, a timestamp means both that it’s deleted and when, which a boolean throws away.
• Every table with a PATCH endpoint carries version integer not null default 1, incremented by the same updated_at trigger. It’s the source of the ETag (W/"<version>") and the If-Match comparison. See Conventions → Concurrency.
• Every table a test-mode credential can write, directly or as a side effect (a User’s Profile, Settings, memberships, Role assignments, and the Audit Events its writes produce), carries test_mode boolean not null default false, copied from the row or credential that caused the write. Every uniqueness constraint on such a table includes test_mode (the one exception is applications.slug, unique across both modes because it’s embedded in the Application’s id), and a third Row-Level Security policy keyed on app.current_test_mode keeps test and live rows invisible to each other. The two tables that policy doesn’t apply to are applications and push_credentials: catalog rows are shared reference data, readable in both modes, and applications.test_mode only marks an Application created for testing. An Application’s push credentials are part of that same configuration, so they have no test_mode column at all. Test rows older than 30 days are purged nightly.
• Every id is generated by the application as <prefix> + a ULID (26 Crockford base32 characters), never by a database sequence. The two exceptions are derived ids: applications.id (app_<slug>) and roles.id (see Conventions → IDs).

users

Column	Type	Constraint
id	text	primary key
email	citext	unique, not null
email_verified	boolean	not null, default false
status	text	not null, check (status in ('active','invited','suspended','deleted'))
pending_email	citext	nullable
signup_application_id	text	nullable, references applications(id)
stripe_customer_id	text	nullable, unique — set at the User’s first Checkout; internal, never returned by the API
test_mode	boolean	not null, default false
expires_at	timestamptz	nullable, check ((expires_at is not null) = test_mode), check (expires_at <= created_at + interval '30 days') — set on every test-mode User, never on a live one
expired_processed_at	timestamptz	nullable — internal, never returned. Set when the expiry sweep has revoked the User’s sessions and fired its events; cleared when expires_at moves later
created_at, updated_at, last_login_at	timestamptz	last_login_at nullable
version	integer	not null, default 1
deleted_at	timestamptz	nullable — soft-delete
protected	boolean	not null, default false — the platform owner’s account; see below

Indexes: (expires_at) where test_mode and expired_processed_at is null for the expiry sweep; unique (email, test_mode) where deleted_at is null (a deleted user’s email should be reusable by a new signup, which a plain unique index would block, and test-mode signups mustn’t collide with live ones); (lower(email) text_pattern_ops) for the q prefix search; (status) for admin filtering. No organization_id here — see organization_memberships below; a single column on users would cap a User at one Organization. No auth/password column here either — see user_identities below; a User can hold more than one login method, per Auth → Native auth and per-Organization SSO.

Protected superadmin. Triggers keep a protected User (the platform owner, re-asserted by every deploy) from being locked out, for every caller: an UPDATE that would clear protected, change status from active, set deleted_at, or change email or test_mode is rejected, as is a DELETE; user_role_assignments refuses to drop its role_platform_superadmin row; and erasure_requests refuses a scheduled erasure for it. Setting app.protected_user_maintenance = 'on' for one transaction lifts all three, for ops handing ownership to another account. See Users → The protected superadmin.

user_identities, sso_connections

user_identities (
  id text primary key,                    -- uid_...
  user_id text not null references users(id),
  method text not null check (method in ('password','sso')),
  password_hash text,                      -- set iff method = 'password'
  sso_connection_id text references sso_connections(id),  -- set iff method = 'sso'
  external_subject_id text,                -- the IdP's own user id, set iff method = 'sso'
  mfa_enabled boolean not null default false,
  mfa_secret text,                         -- KMS-encrypted TOTP secret; set iff mfa_enabled
  mfa_pending_secret text,                 -- KMS-encrypted; set during enrollment, cleared on confirm
  mfa_pending_expires_at timestamptz,
  failed_login_count int not null default 0,
  locked_until timestamptz,
  last_used_at timestamptz,
  created_at timestamptz not null default now()
);

sso_connections (
  id text primary key,                     -- ssc_...
  organization_id text not null references organizations(id),
  provider text not null,                  -- e.g. 'workos'
  domain text,                             -- auto-routes a matching-email signup to this connection
  status text not null default 'active' check (status in ('active','inactive')),
  created_at timestamptz not null default now()
);

No API reads or writes sso_connections yet; its endpoints ship with the SSO broker integration (see Domain Model → SSOConnection).

Constraint: check (method = 'password' and password_hash is not null and sso_connection_id is null) or (method = 'sso' and sso_connection_id is not null and password_hash is null) — a user_identities row is exactly one method’s worth of credential, never a mix. Index: (user_id) on user_identities — a login attempt looks up every identity a User holds and tries to match the presented credential against one of them, per Domain Model → UserIdentity: there’s no single “the” credential row to look up directly. unique (domain) where domain is not null and status = 'active' on sso_connections — an email domain is claimed by at most one active connection on the whole platform, so routing a login by email domain is never ambiguous (a per-Organization constraint would let two Organizations claim acme.com).

sessions, refresh_tokens, auth_tokens, mfa_recovery_codes

sessions (
  id text primary key,                         -- ses_...
  user_id text not null references users(id),
  amr text[] not null,                         -- {'pwd','otp'}
  ip_address inet,
  user_agent text,
  created_at timestamptz not null default now(),
  last_seen_at timestamptz not null default now(),
  idle_expires_at timestamptz not null,        -- now() + 30 days, pushed forward on refresh
  absolute_expires_at timestamptz not null,    -- created_at + 90 days, never moves
  revoked_at timestamptz,
  revoked_reason text
);

refresh_tokens (
  token_hash text primary key,                 -- sha256 of rtk_...; the token itself is never stored
  session_id text not null references sessions(id),
  application_id text references applications(id),  -- null = platform refresh token; set = app refresh token
  rotated_at timestamptz,                      -- non-null = already used; presenting it again revokes the session
  created_at timestamptz not null default now()
);

auth_tokens (
  token_hash text primary key,                 -- sha256 of emv_/pwr_/inv_/mfa_/ac_ tokens
  kind text not null check (kind in ('email_verify','password_reset','invitation','mfa_challenge','authorization_code')),
  user_id text not null references users(id),
  payload jsonb not null default '{}',         -- e.g. new email; application_id, redirect_uri, code_challenge, state
  attempts int not null default 0,             -- mfa_challenge: invalidated at 5
  expires_at timestamptz not null,
  consumed_at timestamptz,
  created_at timestamptz not null default now()
);

mfa_recovery_codes (
  user_identity_id text not null references user_identities(id),
  code_hash text not null,                     -- argon2id of the code
  used_at timestamptz,
  primary key (user_identity_id, code_hash)
);

Indexes: (user_id) where revoked_at is null on sessions (list active sessions, revoke-all); (session_id) on refresh_tokens; (user_id, kind) where consumed_at is null on auth_tokens (issuing a new token of a kind invalidates the user’s earlier unconsumed ones). Every request validates its token’s sid with a primary-key lookup on sessions, which is why logout takes effect immediately. A nightly job deletes expired auth_tokens, and sessions/refresh_tokens 30 days after expiry or revocation.

organizations

Column	Type	Constraint
id	text	primary key
name	text	not null
status	text	not null, check (status in ('active','suspended'))
created_by	text	not null, references users(id)
stripe_customer_id	text	nullable, unique — set at the Organization’s first Checkout; internal
test_mode	boolean	not null, default false
created_at, updated_at	timestamptz
version	integer	not null, default 1

No surprising constraints — small table, lookup is by primary key and via organization_memberships below.

organization_memberships

organization_memberships (
  user_id text not null references users(id),
  organization_id text not null references organizations(id),
  role text not null check (role in ('org_admin','member')),
  status text not null default 'active' check (status in ('pending','active')),
  invited_by text references users(id),        -- null for the creator and for direct adds by organizations.manage
  invited_at timestamptz not null default now(),
  joined_at timestamptz,                        -- set when status becomes 'active'
  test_mode boolean not null default false,
  primary key (user_id, organization_id),
  check ((status = 'active') = (joined_at is not null))
);

Every access query (org-wide grants, org_admin checks, covered-member counts) filters on status = 'active'; a pending row only means “invited, hasn’t accepted”. Index: (organization_id) for member-listing queries — the inverse of the primary key’s natural lookup direction (by user). This is what makes a User’s membership many-to-many: no organization_id column on users to collide with.

subscriptions

The Free/Standard subscription (see Pricing and API Reference → Billing). Free has no row; a row exists once a User or Organization first starts a Checkout.

subscriptions (
  id text primary key,                          -- sbs_ prefix
  user_id text references users(id),            -- an individual subscription
  organization_id text references organizations(id),  -- or an Organization's
  plan text not null default 'standard' check (plan in ('standard')),
  status text not null default 'incomplete'
    check (status in ('active','trialing','past_due','canceled','unpaid','incomplete','incomplete_expired','paused')),
  stripe_subscription_id text unique,          -- Stripe's sub_…; may change across resubscribes, the row doesn't
  current_period_start timestamptz,
  current_period_end timestamptz,
  cancel_at_period_end boolean not null default false,
  member_scope text check (member_scope in ('all_members','allowlist','denylist')),
  member_overrides text[] check (cardinality(member_overrides) <= 1000),
  quantity_synced integer,                     -- last covered-member count written to Stripe
  quantity_synced_at timestamptz,
  test_mode boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  version integer not null default 1,
  check ((user_id is null) <> (organization_id is null)),
  check (organization_id is not null or (member_scope is null and member_overrides is null)),
  check (organization_id is null or member_scope is not null)
);

Indexes: unique (user_id) where user_id is not null and unique (organization_id) where organization_id is not null — one row per subscriber, updated in place across lapses and resubscribes; (status) for the daily quantity sync. It carries the test/live RLS policy like every other table a test credential writes. active, trialing, and past_due are the statuses that cover users; the platform keeps source: subscription rows in entitlements in step with them (see Entitlements → Platform-managed).

tenants

Column	Type	Constraint
id	text	primary key
owner_type	text	not null, check (owner_type in ('user','organization')), and check ((owner_type = 'user') = (owner_user_id is not null))
owner_user_id	text	nullable, references users(id)
owner_organization_id	text	nullable, references organizations(id)
tier	text	not null, default 'shared', check (tier in ('shared','isolated','dedicated_region'))
region	text	not null, default 'us-east-1', check (region in ('us-east-1','us-west-2','ca-central-1','eu-west-1','eu-central-1','ap-southeast-2')), and check (tier = 'dedicated_region' or region = 'us-east-1')
created_at, updated_at	timestamptz
status	text	not null, default 'active', check (status in ('active','migrating','suspended'))

Constraint: check (owner_user_id is null or owner_organization_id is null) and check (owner_user_id is not null or owner_organization_id is not null) — exactly one owner, same pattern as applications.owner_* below. Tenants carry no billing columns: Pricing bills Users and Organizations, through subscriptions above. Indexes: unique (owner_user_id) where owner_user_id is not null, unique (owner_organization_id) where owner_organization_id is not null — one Tenant per owner, not a list. See Tenancy.

applications

Column	Type	Constraint
id	text	primary key, check (id = 'app_' || slug) — derived from slug, never random
slug	text	unique (across both modes), not null, check (slug ~ '^[a-z][a-z0-9-]{1,63}$' and slug <> 'platform') — no _, so Role ids derived from it are unambiguous. See Domain Model → Applications
name, description, icon_url, launch_url, support_url, email_from_name	text	name, launch_url not null
redirect_uris	text[]	not null, default '{}', check (cardinality(redirect_uris) <= 10)
settings_schema	jsonb	not null, default '{"type":"object","properties":{}}'
permissions	jsonb	not null, default '[]' — array of {key, description}; every key validated by the API to start with app.<slug>.
available_app_roles	text[]	not null, default '{}'
default_app_role	text	nullable, check (default_app_role is null or default_app_role = any(available_app_roles))
visibility	text	not null, check (visibility in ('public','invite_only','internal'))
access_tier	text	not null, default 'standard', check (access_tier in ('free','standard')) — which Pricing tier unlocks it
owner_user_id	text	nullable, references users(id)
owner_organization_id	text	nullable, references organizations(id)
review_status	text	not null, default 'approved', check (review_status in ('approved','pending_review','rejected','suspended','retired'))
review_notes	text	nullable — check (review_notes is not null or review_status not in ('rejected','suspended','retired')) enforces it’s required on exactly those three transitions, at the database level, not just in application code
review_requested_at	timestamptz	nullable — set by the API in the same write that moves review_status into pending_review, and kept otherwise. No not null check for pending_review rows, so the previous release can still write during a deploy; the review queue sorts by coalesce(review_requested_at, created_at)
self_registered	boolean	not null, default false — true for a self-service submission; never changes
tenant_id	text	not null, references tenants(id) — resolved from whichever owner column is set at insert time, see Tenancy
test_mode	boolean	not null, default false
created_at, updated_at	timestamptz
version	integer	not null, default 1

Constraint: check (owner_user_id is null or owner_organization_id is null) and check (owner_user_id is not null or owner_organization_id is not null) — an Application has exactly one owner, never both and never neither; a Substratal-run “system” Application still sets owner_user_id to Substratal’s own reserved internal account (the usr_01JAG0SYSTEM00000000000000 used throughout this site’s own examples), not a null owner — see Applications. Index: (owner_user_id), (owner_organization_id) for “my apps” queries; (visibility, review_status) where review_status = 'approved' for the public catalog listing; (tenant_id) for a support/ops query of “every Application on this Tenant” during a tier-change migration; (access_tier) for the reconcile that keeps platform-managed Entitlements in step.

entitlements

Column	Type	Constraint
id	text	primary key
user_id	text	nullable, references users(id)
organization_id	text	nullable, references organizations(id) — the org-wide grant’s holder, not the general multi-tenancy column
application_id	text	not null, references applications(id)
tenant_id	text	not null, references tenants(id) — denormalized from applications.tenant_id, see conventions above
status	text	not null, check (status in ('active','disabled','expired','revoked'))
source	text	not null, check (source in ('purchase','trial','admin_grant','org_seat','free','subscription')) — free and subscription are platform-managed
subscription_id	text	nullable, references subscriptions(id) — set exactly on source: subscription rows
order_id	text	nullable, check (char_length(order_id) <= 255) — an opaque developer-supplied reference, not a foreign key
starts_at, ends_at	timestamptz	ends_at nullable
disabled_reason	text	nullable
member_scope	text	nullable, check (member_scope in ('all_members','allowlist','denylist')) — only set when organization_id is set, see Entitlements → Org-wide entitlements
member_overrides	text[]	nullable — user_ids this grant’s default is flipped for, only meaningful alongside member_scope in ('allowlist','denylist'); check (cardinality(member_overrides) <= 1000)
test_mode	boolean	not null, default false
created_at, updated_at	timestamptz
version	integer	not null, default 1

Constraints: check ((user_id is null) <> (organization_id is null)) (exactly one holder); check (organization_id is null or source in ('org_seat','subscription')) and check (source <> 'org_seat' or organization_id is not null) (org rows are seat grants or an Organization’s subscription grants); check (source <> 'free' or user_id is not null); check ((source = 'subscription') = (subscription_id is not null)); check (source <> 'trial' or ends_at is not null); check (status not in ('disabled','revoked') or disabled_reason is not null). Indexes: unique (user_id, application_id, test_mode) where user_id is not null and status in ('active','disabled') and source not in ('free','subscription') and the same on organization_id. These are what entitlement_already_exists enforces at the database level, not just in application code; expired/revoked history rows don’t block a new grant. Platform-managed rows have their own pair, unique (user_id, application_id, test_mode, source) and unique (organization_id, application_id, test_mode, source), both where status in ('active','disabled') and source in ('free','subscription'), so a developer grant and a platform-managed row can coexist; (subscription_id) where subscription_id is not null. (application_id, order_id) where order_id is not null for reconciliation lookups; (status, ends_at) where status in ('active','disabled') and ends_at is not null for the 5-minute expiry sweep (a disabled row past ends_at becomes expired too); (starts_at) where status = 'active' for the same sweep’s other job, firing access.granted for rows whose future starts_at has just passed; (application_id, status) for GET /entitlements filtering (see API Reference → Entitlements); (organization_id) for org-seat listing; member_overrides is read with = any(member_overrides) at resolution time rather than its own index — the row count per org-wide grant is small enough that a sequential scan of one array is cheaper than maintaining a GIN index for it.

roles, user_role_assignments

roles (
  id text primary key,            -- role_platform_<name> | role_<slug>_<name> (slug verbatim; slugs never contain '_')
  name text not null check (name ~ '^[a-z][a-z0-9_]{1,40}$'),
  scope text not null,            -- 'platform' or an application_id
  description text,
  permissions text[] not null default '{}',
  seed boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  version integer not null default 1,
  test_mode boolean not null default false,
  unique (scope, name, test_mode)
);

user_role_assignments (
  user_id text not null references users(id),
  role_id text not null references roles(id),
  application_id text,            -- denormalized from roles.scope, null for platform roles
  assigned_at timestamptz not null default now(),
  assigned_by_type text not null check (assigned_by_type in ('user','api_key','system')),
  assigned_by_id text not null,   -- usr_..., key_..., or 'system' — not a foreign key, since it spans tables
  test_mode boolean not null default false,
  primary key (user_id, role_id)
);

application_id on the assignment is denormalized from the Role’s own scope specifically so “does this user hold any role for app X” is an index lookup ((user_id, application_id)) rather than a join through roles on every Access Control check — this table is read on every single authorized request, so it’s worth the denormalization.

profiles, app_profiles, settings, app_settings

Four small tables, same shape pattern — global vs. per-app, each keyed by user_id (profiles/settings) or (user_id, application_id) (app_profiles/app_settings). app_settings stores only the overrides object described in API Reference → Settings — the resolved view is computed at read time, never materialized, so an Application’s default change (in its own settings_schema) is reflected for every user who never overrode it without a backfill.

app_settings (
  user_id text not null references users(id),
  application_id text not null references applications(id),
  tenant_id text not null references tenants(id),
  overrides jsonb not null default '{}',
  test_mode boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  version integer not null default 1,
  primary key (user_id, application_id)
);

app_profiles (
  user_id text not null references users(id),
  application_id text not null references applications(id),
  tenant_id text not null references tenants(id),
  display_handle text check (char_length(display_handle) between 1 and 64),
  custom jsonb not null default '{}',
  test_mode boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  version integer not null default 1,
  primary key (user_id, application_id)
);

profiles (
  user_id text primary key references users(id),
  display_name text not null,
  avatar_url text, contact_email citext, contact_phone text,
  -- no locale column: Profile.locale is a read-only mirror of settings.locale
  test_mode boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  version integer not null default 1
);

settings (
  user_id text primary key references users(id),
  locale text not null default 'en-US',
  timezone text not null default 'UTC',
  theme text not null default 'system' check (theme in ('light','dark','system')),
  notifications jsonb not null default '{"email": true}',
  test_mode boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  version integer not null default 1
);

app_profiles carries the same tenant_id, denormalized from applications.tenant_id the same way. Both are per-Application data, so both are placed by that Application’s Tenant. All four tables carry version because all four have a PATCH endpoint. locale is stored only on settings; the API returns it on Profile too, read from there. A display_name defaults to the local part of the email address when signup doesn’t supply one.

Typed fields: per-application views and expression indexes

Why views and expression indexes rather than one generated column per declared property on the shared app_settings table: two Applications declaring the same key with different types would collide on one column name. Postgres’s 1,600-column cap would put a hard ceiling on Applications × declared keys. And a cast inside a generated column makes every insert fail once a stored value stops matching a changed schema. No endpoint queries settings across users today, so the typed projection serves internal/reporting queries only; add a query endpoint only if a real Application needs one.

This is what “storage” means on this platform. overrides (and custom, on app_profiles) stays jsonb as the source of truth, so a developer never has to pre-declare a migration to store a new field. Every scalar property an Application has declared in its settings_schema also becomes a real, typed, indexed Postgres field. It’s done with one view per Application and partial expression indexes, not physical columns on the shared table, because physical columns break three ways on a table shared by every Application:

• Name collisions. Two apps declaring week_start as text and as integer would need the same column.
• The 1,600-column ceiling across all Applications’ declared keys combined.
• Cast failures. A GENERATED ... ::boolean column makes every insert fail once a stored value stops matching a later schema change.

The mechanism, run by the same code path that validates an Application’s schema whenever it changes:

-- 1. Null-on-failure casts (one per supported type), so a stale value can never break a read or a write
create function substratal.try_bool(j jsonb) returns boolean immutable language sql
  as $$ select case when jsonb_typeof(j) = 'boolean' then (j #>> '{}')::boolean end $$;
-- likewise try_int, try_num, try_text, try_timestamptz

-- 2. A typed view per Application: real Postgres types, one column per declared scalar property.
--    Named by a short hash of the application id (see "Naming" below), recorded in a comment.
create or replace view app_settings_3f9a1c2b7d4e as
  select user_id,
         substratal.try_bool(overrides->'default_billable') as default_billable,
         substratal.try_text(overrides->'week_start')       as week_start
  from app_settings
  where application_id = 'app_timetrack';
comment on view app_settings_3f9a1c2b7d4e is 'app_timetrack';

-- 3. A partial expression index per declared scalar property
create index app_settings_3f9a1c2b7d4e_8c41e07a52d9
  on app_settings ((substratal.try_text(overrides->'week_start')))
  where application_id = 'app_timetrack';
comment on index app_settings_3f9a1c2b7d4e_8c41e07a52d9 is 'app_timetrack.week_start';

Naming. Postgres identifiers are capped at 63 bytes, and a slug (up to 64 characters) plus a property name (up to 63) doesn’t fit, so names are built from hashes instead: the view is app_settings_<a> and each index app_settings_<a>_<p>, where <a> is the first 12 hex characters of sha256(application_id) and <p> the first 12 of sha256(property_name). That’s at most 38 characters, contains no - (so no quoting), and is stable across schema changes. The comment on each object records the readable name for anyone browsing the catalog. (The hashes in the example above are illustrative.)

The view is replaced, and indexes created or dropped, with CREATE INDEX CONCURRENTLY from an async job, never inside the API request that changed the schema. object and array properties get no typed projection; they’re stored and validated, but queried as jsonb. The view and indexes contain no data of their own, so a Tenant tier migration moves only the app_settings rows and re-runs this DDL on the target cluster.

No public API endpoint queries settings across users today, so this typed projection serves internal, reporting, and support queries. If an Application needs “all users where week_start = 'monday'”, that’s a new endpoint to add, and these indexes are what make it cheap.

This only applies to fields an Application has declared. An arbitrary, undeclared key can’t be written into overrides at all: writes are validated against the schema, and only the reserved global keys are exempt.

A general-purpose storage API (arbitrary collections, file/blob storage) is deliberately out of scope. Typed JSON fields cover the flag/preference/small-record use case every Application needs, and the mechanism doesn’t change as that use case grows: a new declared field just adds another view column and index, not a new kind of storage object. Revisit only if a real Application hits something a typed JSON field genuinely can’t represent, such as actual file bytes.

audit_events

Column	Type	Constraint
id	text	primary key
action	text	not null, check against the Action catalog
actor_type	text	not null, check (actor_type in ('user','api_key','system'))
actor_id	text	not null — usr_…, key_…, or 'system'
target_type	text	not null
target_id	text	not null
target_user_id	text	nullable — the User whose access/data changed, when there is one
application_id, organization_id	text	nullable
tenant_id	text	nullable — set whenever application_id is (denormalized the same way as other per-Application tables) and on tenant.* events (the Tenant itself); null for platform-level events (e.g. user.created, subscription.*) with neither
before, after	jsonb	nullable
request_id	text	nullable
test_mode	boolean	not null, default false — copied from the change that produced it, so test traffic never shows in a live audit query
timestamp	timestamptz	not null, default now()

No updated_at, no soft-delete column — this table is append-only by design (see Non-Functional Requirements → Audit). A trigger (audit_events_append_only) rejects every UPDATE and DELETE unless the transaction has set app.audit_maintenance = 'on', so neither an application bug nor a direct SQL session can violate the “never edited” guarantee. Only two scheduled jobs set it: the archival job (see Non-Functional Requirements → Audit log lifecycle and infra/README.md → Audit log archival), which deletes rows it has already archived shape-only to cold storage, and the erasure cascade, which redacts before/after for an erased User. Indexes: (target_user_id, timestamp desc), (application_id, timestamp desc), (actor_id, timestamp desc), (target_type, target_id, timestamp desc), (organization_id, timestamp desc), (tenant_id, timestamp desc), (request_id, timestamp desc) — one per documented filter in API Reference → Audit, the last one being what a tier-change migration (and the archival job itself) uses to pull “every event for this Tenant” without scanning the whole table.

api_keys

Column	Type	Constraint
id	text	primary key
name	text	not null
scope	text	not null
permissions	text[]	not null
mode	text	not null, check (mode in ('live','test'))
intended_use	text	not null, default 'service', check (intended_use in ('service','agent'))
restrict_destructive	boolean	not null, default computed from intended_use at insert time (true iff intended_use = 'agent'), overridable — see API Keys → Agent keys
secret_hash	text	not null — never store the secret itself, only a hash (e.g. SHA-256) of it, the same way a password would be stored. The API can verify a presented key against the hash; it can never display the original value again, which is exactly the contract API Keys describes (“returned once”).
secret_hint	text	not null — last 4 characters of the current secret
expires_at	timestamptz	nullable
created_by_type, created_by_id	text	not null; check (created_by_type = 'user') — an API Key can never create API Keys
test_mode	boolean	not null — mode = 'test'
created_at, updated_at	timestamptz
version	integer	not null, default 1
last_used_at, revoked_at	timestamptz	nullable

Index: unique (secret_hash). Authentication is a single lookup by the SHA-256 of the presented secret. A fast hash is fine here, unlike passwords, because API Key secrets are 128 random bits.

Enforcement, not just storage: the restrict_destructive check happens in the same request-handling layer as the permission check — both read from the authenticated key’s row, both must pass. The classification of which operations count as destructive lives in exactly one place in the implementation (a lookup by HTTP method + the specific Entitlement/Role/Organization-member/Tenant-tier mutations named in API Keys → Agent keys & restrict_destructive), shared by this check and by the MCP server’s destructiveHint tool-annotation generation — not reimplemented twice and left to drift.

audit_exports

Column	Type	Constraint
id	text	PK, aex_…
requested_by_type, requested_by_id	text	not null — user or api_key, and its id
scope	jsonb	not null — the scope the requester was confined to (application_id, target_user_id, or tenant_id), or {} for audit.view
filters	jsonb	not null — as requested, me resolved
format	text	ndjson or csv
status	text	pending, running, ready, or failed
row_count, object_key	integer, text	set exactly when ready
error	text	set exactly when failed
test_mode	boolean	mode policy, like every other test-mode table
created_at, started_at, completed_at	timestamptz
expires_at	timestamptz	not null — created_at + 7 days; the nightly cleanup deletes the row, and an S3 lifecycle rule the file

Index: (created_at) where status in ('pending','running') for the export worker, which claims one at a time with for update skip locked and picks up a running export again if its worker hasn’t finished within 20 minutes.

platform_settings

Column	Type	Constraint
test_mode	boolean	primary key: exactly one row per mode, both seeded by migration 0018
signup_open	boolean	not null, default true
read_only	boolean	not null, default false
client_flags	jsonb	not null, default '{}', an object of flag name to boolean (at most 100, enforced by the API)
announcement	jsonb	nullable, {text, level}
created_at, updated_at	timestamptz
version	integer	not null, default 1; the ETag

Mode policy, like every other test-mode table. Each API instance caches a mode’s row for 30 seconds, so a change applies everywhere within that. See Platform settings.

scim_tokens

Column	Type	Constraint
id	text	PK, sct_…
organization_id	text	FK → organizations, unique: at most one token per Organization
secret_hash	text	not null, unique — SHA-256 of the scim_… secret, never the secret
created_by	text	FK → users
test_mode	boolean	the mode the token’s calls run in; no mode policy, like api_keys, since it’s resolved by hash first
created_at, last_used_at	timestamptz

webhook_subscriptions, webhook_events, webhook_deliveries

webhook_subscriptions (
  id text primary key,                         -- whk_...
  scope text not null,                         -- 'platform' or an application_id
  tenant_id text references tenants(id),       -- null for platform scope
  url text not null,
  events text[] not null,                      -- or '{*}'
  description text,
  api_version text not null,                   -- e.g. '2026-10-05'
  secret_ciphertext text not null,             -- KMS-encrypted: the API must sign with it, so it can't be a hash
  previous_secret_ciphertext text,             -- set for 24h after a rotation
  previous_secret_expires_at timestamptz,
  status text not null default 'healthy' check (status in ('healthy','unhealthy','disabled')),
  consecutive_failures int not null default 0,
  unhealthy_since timestamptz,
  test_mode boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  version integer not null default 1
);

webhook_events (                               -- the transactional outbox
  id text primary key,                         -- wev_...
  type text not null,
  application_id text,
  payload jsonb not null,
  test_mode boolean not null default false,
  created_at timestamptz not null default now(),
  dispatched_at timestamptz                    -- set once fan-out to subscriptions is enqueued
);

webhook_deliveries (
  id text primary key,                         -- dlv_...
  subscription_id text not null references webhook_subscriptions(id) on delete cascade,
  event_id text not null references webhook_events(id),
  attempt int not null,
  status text not null check (status in ('pending','succeeded','failed')),
  response_status int,
  duration_ms int,
  error text,                                  -- first 1 KB only
  next_retry_at timestamptz,
  created_at timestamptz not null default now()
);

A webhook_events row is inserted in the same transaction as the change it describes. A dispatcher (SQS-fed, see infra/README.md) fans it out to matching subscriptions. That makes “every committed change produces its event” structural rather than best-effort. Events and deliveries are deleted after 30 days. Indexes: (dispatched_at) where dispatched_at is null; (subscription_id, created_at desc), (status, created_at desc), and (next_retry_at) where status = 'pending' on deliveries.

push_credentials, devices, notifications, notification_deliveries

The tables behind Push Notifications. See Domain Model → Notifications for what each field means.

push_credentials (
  id text primary key,                         -- pcr_...
  application_id text not null references applications(id),
  tenant_id text not null references tenants(id),
  provider text not null check (provider in ('apns','fcm','web_push')),
  config jsonb not null,                       -- non-secret: team_id/key_id/topic | project_id/client_email | subject/vapid_public_key
  secret_ciphertext text not null,             -- KMS-encrypted: the .p8 key | the service-account JSON | the VAPID private key
  status text not null default 'active' check (status in ('active','failed','disabled')),  -- disabled: the Application was retired
  failure_reason text,
  failed_at timestamptz,
  last_used_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  version integer not null default 1,
  unique (application_id, provider),
  check ((status = 'failed') = (failed_at is not null))
);

devices (
  id text primary key,                         -- dvc_...
  user_id text not null references users(id),
  application_id text not null references applications(id),
  tenant_id text not null references tenants(id),
  session_id text references sessions(id) on delete set null,  -- null when an app key registered it
  provider text not null check (provider in ('apns','fcm','web_push')),
  environment text check (environment in ('production','sandbox')),
  token_hash text not null,                    -- sha256 of the token, or of the web_push endpoint: the dedup key
  token_ciphertext text not null,              -- KMS-encrypted token, or the whole PushSubscription JSON
  token_hint text not null,                    -- last 4 characters
  name text check (char_length(name) <= 100),
  app_version text check (char_length(app_version) <= 50),
  status text not null default 'active' check (status in ('active','inactive')),
  inactive_reason text check (inactive_reason in
    ('unregistered','session_revoked','user_suspended','user_deleted','test_user_expired','stale','limit','credential_removed',
     'application_retired')),
  deactivated_at timestamptz,
  last_registered_at timestamptz not null default now(),
  last_delivery_at timestamptz,
  test_mode boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  version integer not null default 1,
  check ((provider = 'apns') = (environment is not null)),
  check ((status = 'inactive') = (inactive_reason is not null)),
  check ((status = 'inactive') = (deactivated_at is not null))
);

notifications (
  id text primary key,                         -- ntf_...
  user_id text not null references users(id),
  application_id text references applications(id),  -- null for a platform alert
  tenant_id text references tenants(id),       -- set exactly when application_id is
  source text not null check (source in ('application','platform')),
  category text not null check (category ~ '^[a-z][a-z0-9_]*(\.[a-z0-9_]+)*$' and char_length(category) <= 64),
  priority text not null default 'normal' check (priority in ('normal','high')),
  title text not null check (char_length(title) between 1 and 120),
  body text check (char_length(body) <= 1000),
  data jsonb not null default '{}',
  action_url text check (char_length(action_url) <= 2048),
  badge integer check (badge between 0 and 99999),
  sound text check (sound = 'default'),
  collapse_key text check (char_length(collapse_key) <= 64),
  ttl_seconds integer not null default 86400 check (ttl_seconds between 0 and 2419200),
  push_requested boolean not null default true,
  push_status text not null check (push_status in ('queued','sent','failed','skipped')),
  skip_reason text check (skip_reason in
    ('push_not_requested','push_disabled','no_devices','no_credentials','credential_failed','expired')),
  push_devices_targeted integer not null default 0,  -- the push summary; kept here because deliveries
  push_devices_sent integer not null default 0,      -- are deleted after 30 days and notifications
  push_devices_failed integer not null default 0,    -- after 90
  sender_type text not null check (sender_type in ('api_key','user','system')),
  sender_id text not null,                     -- key_..., usr_..., or 'system'; not a foreign key, since it spans tables
  read_at timestamptz,
  archived_at timestamptz,
  test_mode boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  version integer not null default 1,
  check ((source = 'platform') = (application_id is null)),
  check ((application_id is null) = (tenant_id is null)),
  check ((push_status = 'skipped') = (skip_reason is not null)),
  check (source = 'platform' or category !~ '^(security|account|billing)\.')
);

notification_deliveries (
  id text primary key,                         -- ndl_...
  notification_id text not null references notifications(id) on delete cascade,
  device_id text references devices(id) on delete set null,
  provider text not null check (provider in ('apns','fcm','web_push')),
  status text not null default 'pending' check (status in ('pending','sent','failed','skipped')),
  attempts int not null default 0 check (attempts between 0 and 4),
  provider_status int,
  error text,                                  -- the provider's reason code, or 'timeout'
  next_attempt_at timestamptz,
  sent_at timestamptz,
  test_mode boolean not null default false,
  created_at timestamptz not null default now()
);

The notifications row and its notification_deliveries rows are inserted in the same transaction, one delivery per Device eligible at that moment. That’s the same transactional outbox as webhook_events: a worker picks up pending deliveries (nudged through SQS after commit, plus a sweep), so a committed send can never lose its push. A platform alert is inserted in the same transaction as the action that raises it, before any session revocation that action makes.

push_credentials is Application configuration, so like applications it isn’t covered by the test/live Row-Level Security policy. It has no test_mode column, because one credential serves both modes. The other three tables carry test_mode and are covered. devices, notifications, and notification_deliveries hold personal data: the hard-delete cascade deletes a User’s rows from all three.

Indexes: unique (application_id, provider, token_hash, test_mode) on devices, which is what makes registration an upsert keyed on the token; (user_id, application_id) where status = 'active' (fan-out at send time, and the 20-device cap); (session_id) where session_id is not null (deactivate on session revocation); (last_registered_at) where status = 'active' (the 270-day stale sweep); (deactivated_at) where status = 'inactive' (deleting devices 30 days after deactivation). On notifications: (user_id, created_at desc, id) where archived_at is null (the default inbox list); (user_id, application_id, created_at desc) (an app key’s view, and the per-recipient rate limit’s count of the last hour); (user_id) where read_at is null and archived_at is null (unread counts); (created_at) (the 90-day purge). On notification_deliveries: (next_attempt_at) where status = 'pending' (the worker’s queue) and (notification_id). Deliveries are deleted after 30 days and notifications after 90. Both purges run nightly.

tier_change_requests, erasure_requests

tier_change_requests (
  id text primary key,                         -- tcr_...
  tenant_id text not null references tenants(id),
  from_tier text not null, from_region text not null,
  requested_tier text not null check (requested_tier in ('isolated','dedicated_region')),
  requested_region text,
  status text not null default 'pending'
    check (status in ('pending','scheduled','in_progress','completed','cancelled')),
  reason text not null,
  notes text,
  scheduled_for timestamptz,
  requested_by text not null references users(id),
  created_at timestamptz not null default now(),
  started_at timestamptz, completed_at timestamptz,
  version integer not null default 1
);
-- at most one open request per tenant:
create unique index on tier_change_requests (tenant_id) where status in ('pending','scheduled','in_progress');

erasure_requests (
  user_id text primary key references users(id),
  requested_by_type text not null, requested_by_id text not null,
  reason text,
  requested_at timestamptz not null default now(),
  scheduled_for timestamptz not null,          -- requested_at + 7 days
  status text not null default 'scheduled' check (status in ('scheduled','cancelled','completed')),
  completed_at timestamptz
);

stripe_events

stripe_events (
  id text primary key,                         -- Stripe's own evt_... id; insert-or-skip makes handling idempotent
  type text not null,
  subscription_id text references subscriptions(id),  -- the subscription it was applied to, once processed
  received_at timestamptz not null default now(),
  processed_at timestamptz
);

Kept 90 days. See infra/README.md → Stripe Billing.

idempotency_keys

idempotency_keys (
  caller_id text not null,
  idempotency_key text not null check (char_length(idempotency_key) between 1 and 255),
  state text not null check (state in ('in_flight','completed')),
  request_body_hash text not null,
  response_status int,                         -- null while in_flight
  response_body jsonb,
  expires_at timestamptz not null,
  primary key (caller_id, idempotency_key)
);

Per Conventions → Idempotency’s implementation note — a scheduled job (see Deployment Architecture) deletes expired rows rather than relying on unbounded table growth.