Role grant offer DDL — CREATE TABLE + index statements and the index-side
sentinel constants the queries / migrations interpolate.
Separated from auth/role_grant_offer_schema.ts so the schema module stays Zod-only (paired with auth/auth_ddl.ts and auth/audit_log_ddl.ts).
An offer is a pending grant awaiting recipient consent. Lifecycle states
are mutually exclusive via a CHECK constraint (role_grant_offer_single_terminal):
at most one of accepted_at / declined_at / retracted_at may be set.
On accept, the offer's resulting_role_grant_id links to the role_grant row
produced by query_accept_offer.
5 declarations
auth/role_grant_offer_ddl.ts view source
"\nCREATE INDEX IF NOT EXISTS role_grant_offer_inbox\n ON role_grant_offer (to_account_id, expires_at)\n WHERE accepted_at IS NULL\n AND declined_at IS NULL\n AND retracted_at IS NULL\n AND superseded_at IS NULL" Inbox lookup — pending offers for an account, ordered by soonest expiry.
auth/role_grant_offer_ddl.ts view source
"\nCREATE UNIQUE INDEX IF NOT EXISTS role_grant_offer_pending_unique\n ON role_grant_offer (\n to_account_id,\n role,\n COALESCE(scope_kind, 'GLOBAL'),\n COALESCE(scope_id, '00000000-0000-0000-0000-000000000000'::uuid),\n from_actor_id\n )\n WHERE accepted_at IS NULL\n AND declined_at IS NULL\n ... At most one pending offer per (to_account, role, scope_kind, scope, from_actor).
Including from_actor_id in the tuple lets multiple grantors coexist —
teacher A and teacher B can each have a pending classroom_student offer
for the same student and scope. A same-grantor re-offer upserts the
existing pending row. COALESCE collapses NULL scopes into the
sentinel values so Postgres's NULL-in-unique-index quirk does not allow
duplicate global pending offers; the scope_kind / scope_id pair is
always either both null (global) or both non-null (scoped) per the
role_grant_offer_scope_kind_paired CHECK, so the two COALESCE expressions
always agree. The ON CONFLICT target in query_role_grant_offer_create must
match this expression literally.
auth/role_grant_offer_ddl.ts view source
"\nCREATE TABLE IF NOT EXISTS role_grant_offer (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n from_actor_id UUID NOT NULL REFERENCES actor(id) ON DELETE CASCADE,\n to_account_id UUID NOT NULL REFERENCES account(id) ON DELETE CASCADE,\n to_actor_id UUID NULL REFERENCES actor(id) ON DELETE CASCADE,\n role T... auth/role_grant_offer_ddl.ts view source
"GLOBAL" Index-side token for the global case in the partial unique index. Uppercase
so it cannot collide with consumer-declared ScopeKindName values (which
are lowercase by regex). Never appears as a column value — column-level
scope_kind = NULL and scope_id = NULL together encode the global case.
auth/role_grant_offer_ddl.ts view source
"00000000-0000-0000-0000-000000000000" Sentinel UUID used inside the partial unique indexes to collapse scope_id IS NULL into a comparable value.