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. tenants
8. applications
9. entitlements
10. roles, user_role_assignments
11. profiles, app_profiles, settings, app_settings
    1. Typed fields: per-application views and expression indexes
12. audit_events
13. api_keys
14. webhook_subscriptions, webhook_events, webhook_deliveries
15. tier_change_requests, erasure_requests
16. stripe_events
17. 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, with a Row-Level Security policy — not repeated per-table below. This is team/seat 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 RLS dimension 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(); 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 key can write carries test_mode boolean not null default false. Every uniqueness constraint on such a table includes test_mode, and a third Row-Level Security policy keyed on app.current_test_mode keeps test and live rows invisible to each other. 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.

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)
test_mode	boolean	not null, default false
created_at, updated_at, last_login_at	timestamptz	last_login_at nullable
version	integer	not null, default 1
deleted_at	timestamptz	nullable — soft-delete

Indexes: 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.

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()
);

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 (organization_id, domain) where domain is not null on sso_connections — one connection claims a given email domain per Organization, not several competing for it.

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)
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')),
  joined_at timestamptz not null default now(),
  primary key (user_id, organization_id)
);

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.

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'))
plan	text	not null, default 'starter', check (plan in ('starter','team','enterprise')) — see Pricing
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')
stripe_customer_id	text	nullable, unique — set when the Tenant is created (the Stripe call happens just after commit; a retrying job fills it if that call fails)
stripe_subscription_id	text	nullable, unique — null on Starter
subscription_status	text	not null, default 'none', check (subscription_status in ('none','active','trialing','past_due','canceled','unpaid','incomplete','incomplete_expired','paused'))
current_period_end	timestamptz	nullable
cancel_at_period_end	boolean	not null, default false
restricted	boolean	not null, default false
seat_count_synced	integer	nullable — last quantity written to Stripe
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. check (plan = 'enterprise' or tier = 'shared') — enforces Pricing’s rule that isolated/dedicated_region are Enterprise-only at the database level, not just in application code. check (plan != 'starter' or owner_type = 'user') — enforces Pricing → Enforcement’s rule that a Starter Tenant is always User-owned; auto-provisioning (see Tenancy) sets plan: 'team', not the column’s own 'starter' default, the moment owner_type: 'organization' is resolved, so this constraint is enforced by the provisioning path, not hit as a runtime rejection. 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
slug	text	unique, not null, check (slug ~ '^[a-z][a-z0-9-]{1,63}$') — 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'))
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'))
review_notes	text	nullable — check (review_notes is not null or review_status not in ('rejected','suspended')) enforces it’s required on exactly those two transitions, at the database level, not just in application code
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_01JAG0SUBSTRATAL0000000000 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.

entitlements

Column	Type	Constraint
id	text	primary key
user_id	text	nullable, references users(id)
organization_id	text	nullable, references organizations(id) — the org-seat grant, 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'))
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 ((source = 'org_seat') = (organization_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 unique (organization_id, application_id, test_mode) where organization_id is not null and status in ('active','disabled'). 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. (application_id, order_id) where order_id is not null for reconciliation lookups; (status, ends_at) where status = 'active' and ends_at is not null for the 5-minute expiry sweep; (application_id, status) for GET /v1/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_underscored>_<name>
  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,
  unique (scope, name)
);

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
  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 '{}',
  updated_at timestamptz not null default now(),
  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 '{}',
  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,
  locale text not null default 'en-US',
  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}',
  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. app_settings also gets version integer not null default 1. 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
create or replace view app_settings_timetrack 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';

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

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. Index names are app_settings_<slug>_<property>. 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; null for platform-level events (e.g. user.created) with no single Application to place
before, after	jsonb	nullable
request_id	text	nullable
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); revoke UPDATE/DELETE grants on this table for the application’s own database role, so an application-layer bug can’t violate the “never edited” guarantee even accidentally. The one exception to “never deleted” is the scheduled archival job (see Non-Functional Requirements → Audit log lifecycle and root DEPLOYMENT.md → Audit log archival), which runs as a separate, elevated role specifically for that one job — rows it has already archived shape-only to cold storage are deleted from this table, nothing else ever is. 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) — 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
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.

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; the plan limit counts per tenant
  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 DEPLOYMENT.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) and (next_retry_at) where status = 'pending' on deliveries.

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,
  tenant_id text references tenants(id),
  received_at timestamptz not null default now(),
  processed_at timestamptz
);

Kept 90 days. See root DEPLOYMENT.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.