approvals — module #21, the cross-cutting tenant-side approval-workflow engine

8 tables, 97 columns — schema-locked 2026-07-09 (PROJECT_DECISIONS #44). approvals is the central, cross-cutting tenant-side business-process approval-workflow engine: any module may depend on it; it depends on NO domain module (only platform.tenant, identity.actor, and multi_loc.site — foundational infra, not business-vertical domains). Domains connect via a polymorphic (source_module, source_type, source_ref) triple, never a real FK — source_ref can point at 8 different schemas.

This build MOVES 3 tables out of admin (approval_workflow, approval_routing_rule, approval_request — REVERSING PROJECT_DECISIONS #34 Section 5's "Option B," which decided the engine should stay Admin-internal; Sections 1–4 of #34, the tenant-identity absorption into platform.tenant_profile, are UNTOUCHED) and adds 5 net-new tables (approval_policy, approval_step, approval_delivery, approval_token, approval_event). The 3 moved tables used ALTER TABLE ... SET SCHEMA, which preserves the table object, its RLS policies, its set_updated_at trigger, and its existing GRANTs — none of those are schema-scoped, all persisted across the move (confirmed live). All 3 moving tables had zero rows at migration time — no data-safety risk in the accompanying column changes (renames, NOT NULL, DROP COLUMN, CHECK widen/replace).

Preceded by a codebase-wide 81-mechanism sweep, a design proposal, and TWO independent adversarial verification passes (10 agents total) that found and required fixing 2 genuine correctness bugs before the migration was written: (1) a null-approver bypass ("approved by nobody," the identical NULL-in-CHECK class as the historical chk_purchase_order_sent_requires_approval bug), and (2) an unenforced C8 financial-autonomy boundary (blocks_agent_approver was asserted in the design but not DB-backed). Both are closed in the migration, live-reproduced against the local Supabase Postgres DB (127.0.0.1:54322), and covered by the regression suite (apps/api/src/approvals/__tests__/approvals-schema.spec.ts, 22 tests, all passing). A THIRD bug was found live during the build itself (not by either earlier verification pass) — see Guard 4 below.

Depends on platform (tenant — every table's tenant scope), identity (actor — all approver/initiator/actor attribution, including actor.actor_type for the agent-boundary trigger), multi_loc (site — approval_routing_rule.site_id's optional site-level scoping).

PROJECT_DECISIONS entry: #44.

Groups: Workflow Definition (approval_workflow, approval_routing_rule, approval_policy — tenant governance config) / Runtime Engine (approval_request, approval_step — the in-flight/resolved instances) / Notification & Security (approval_delivery, approval_token — outbound notifications and one-click redemption) / Audit (approval_event — append-only ledger).

Global rules for this schema:

• Reverse-dependency rule. Any module may depend on approvals; approvals depends on NO domain module. This is enforced by construction — every FK target in this schema is platform.tenant, identity.actor, multi_loc.site, or another approvals table. approval_request.source_ref is deliberately a plain, un-FK'd UUID so that adding a new consumer module never requires a migration here.
• Uniform tenant-scoping — all 8 tables carry tenant_id NOT NULL FK → platform.tenant, RLS enabled with a permissive <table>_tenant_isolation policy, FOR ALL TO authenticated, USING/WITH CHECK both tenant_id = current_setting('app.current_tenant_id')::uuid. Plain tenant_id index on all 8.
• updated_at trigger-maintained on 5 of 8 tables via platform.set_updated_at() — the 3 moved tables (approval_workflow, approval_routing_rule, approval_request) retained their existing trigger across the schema move (confirmed live, not re-created), and the 2 new tables with an updated_at column (approval_policy, approval_step) got a fresh one. approval_delivery/approval_token/approval_event have no updated_at column at all — a deliberate discrete-fact / append-only shape (a notification, a token, and an audit event are written once and never revised in place), so no trigger applies.
• Soft delete (deleted_at) on 4 of 8 tables — the 3 moved config tables plus approval_policy (all DEFINITION/config rows, revisable). approval_request/approval_step/approval_delivery/approval_token/approval_event have no deleted_at — these are runtime/audit facts, not editable configuration; a request or step is resolved or cancelled via its own status/decision column, never soft-deleted.
• workflow_type is free text everywhere, no CHECK, on both approval_workflow and approval_routing_rule and approval_request. The live admin-era CHECK (IN ('po_approval','discount_approval','refund_approval','other')) baked 3 specific business-process names into the engine's own schema — the one domain-knowledge leak the design-verification pass found and required removing (chk_approval_workflow_workflow_type, chk_approval_routing_rule_workflow_type, chk_approval_request_workflow_type all DROPPED this migration). A purchasing/crm/inventory engineer's own naming choice must never force a migration against this schema.
• source_type (on approval_request) is also free text, no CHECK — the original design draft proposed mirroring tax/billing's CHECK-enumerated (source_module, source_type) pairing, but the verification pass found this would be a genuinely NEW coupling (the live table never had a source_type CHECK) that would recur every time an already-supported module adds a new record kind (e.g. purchasing alone has 3 separate OPEN_ITEMS convergence candidates under source_module='purchasing'). Only source_module stays CHECK-enumerated (structural polymorphism — which of 8 known schemas a UUID points into is a referential-integrity risk, not a business-semantics one).
• No automation_source/review_status/decision_provenance columns anywhere in this module — a deliberate scoping decision, not an oversight. Every other recently-built module carries this autonomy pack on its agent-writable tables (the "canonical agent-actor/review/provenance pattern," PROJECT_DECISIONS #19). approvals does not, because it sits on the other side of that pattern: it is the pre-execution review/gate mechanism a review_status-carrying row elsewhere ultimately routes into (or could), not a second row that itself needs reviewing. Adding a parallel review-status plane inside the engine that gates approvals would be circular. approval_request.initiator_is_agent (whether the request's own initiator was an agent-type actor) and the C8 blocks_agent_approver / trg_approval_step_blocks_agent_approver mechanism (Guard 4 below) are this module's actual autonomy-boundary surface, in lieu of the standard pack.
• Table-move mechanics. ALTER TABLE admin.approval_workflow SET SCHEMA approvals; (and the same for approval_routing_rule/approval_request) — this preserves the table's RLS-enabled state, its <table>_tenant_isolation policy, its set_updated_at trigger, and its GRANTs, none of which are schema-scoped in Postgres. Confirmed live immediately before writing the migration.

Cross-Phase / Cross-Module Foreign Keys (approvals)

Column	Target	Notes
*.tenant_id (all 8 tables)	platform.tenant	NOT NULL
approval_routing_rule.approval_workflow_id, approval_request.approval_workflow_id	approvals.approval_workflow	NOT NULL, intra-module
approval_routing_rule.site_id	multi_loc.site	nullable — NULL = tenant-wide rule
approval_request.initiator_actor_id	identity.actor	NOT NULL — renamed from requested_by_actor_id, and made NOT NULL (was nullable pre-move); the null-approver-bypass fix, see Guard 1
approval_request.resolved_by_actor_id	identity.actor	nullable — NULL until resolved
approval_step.approval_request_id	approvals.approval_request	NOT NULL, intra-module
approval_step.assigned_approver_actor_id, approval_step.acted_by_actor_id	identity.actor	nullable
approval_delivery.approval_request_id	approvals.approval_request	NOT NULL
approval_delivery.approval_step_id	approvals.approval_step	nullable — a request-level notification (e.g. "request submitted") precedes any step
approval_delivery.recipient_actor_id	identity.actor	NOT NULL
approval_token.approval_step_id	approvals.approval_step	NOT NULL
approval_token.actor_id	identity.actor	NOT NULL
approval_event.approval_request_id	approvals.approval_request	NOT NULL
approval_event.approval_step_id	approvals.approval_step	nullable — some event types (e.g. created) precede any step
approval_event.actor_id	identity.actor	nullable — some event types (e.g. expired) have no acting actor
approval_request.source_ref	polymorphic — purchasing/pos/orders/identity/crm/inventory/ai/other	Plain UUID, discriminated by source_module; no FK — cross-module, consuming schemas locked and unchanged

---

approvals.approval_workflow (12 cols, human-only — governance definition) — MOVED from admin.approval_workflow (+2 cols)

Configurable approval-chain DEFINITION. Each workflow type has one active definition per tenant; steps defines the ordered sequence of approvers and conditions. A governance decision over financial-approval authority itself — not delegable to the same system whose actions the workflow exists to check.

Tenant-scoped. RLS enabled — approval_workflow_tenant_isolation (persisted across the move). Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at() (persisted across the move, confirmed live).

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
name	text	NOT NULL	—	Human label, e.g. "Large PO Approval"
workflow_type	text	NOT NULL	—	Free text, no CHECK — the admin-era chk_approval_workflow_workflow_type (IN ('po_approval','discount_approval','refund_approval','other')) was DROPPED this migration, the one domain-knowledge-leak fix
is_active	boolean	NOT NULL	true	Only one active workflow per (tenant, workflow_type) — DB-enforced via partial unique
steps	jsonb	NOT NULL	—	Ordered approval steps: [{"step":1,"approver_role":"manager","condition":{"amount_cents_gt":500000}},...]
step_mode	text	NOT NULL	'sequential'	NEW. CHECK IN (sequential,parallel,conditional)
blocks_agent_approver	boolean	NOT NULL	false	NEW. The C8 financial-autonomy boundary anchor — declared at workflow-creation time, backed by a real trigger on approval_step (Guard 4), and write-locked at the grant layer (see below) so a generic tenant-scoped write path cannot flip it
description	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (1): chk_approval_workflow_step_mode. (chk_approval_workflow_workflow_type DROPPED this migration.)

Indexes: PK; tenant_id; workflow_type; UNIQUE on (tenant_id,name) WHERE deleted_at IS NULL; UNIQUE on (tenant_id,workflow_type) WHERE is_active = true AND deleted_at IS NULL — one active workflow per type per tenant, DB-enforced.

Triggers: set_updated_at (persisted from admin).

Grants (security-critical, see Guard 4 below): the blanket table-level GRANT UPDATE ... TO authenticated was REVOKEd and re-GRANTed column-by-column on every column except blocks_agent_approver — that column cannot be UPDATEd by any tenant-scoped session at all, regardless of RLS.

Autonomy: human-only, explicitly ruled out — workflow DEFINITIONS (including the new blocks_agent_approver anchor) are a governance decision over approval authority itself, not delegable to the same system whose actions the workflow exists to check.

---

approvals.approval_routing_rule (11 cols, human-only) — MOVED from admin.approval_routing_rule, unchanged shape

Routes a workflow trigger (workflow_type + threshold_cents + optional site_id) to the correct approval_workflow. Multiple rules per type allowed; priority breaks ties. A routing-rule DEFINITION, same governance rationale as approval_workflow.

Tenant-scoped. RLS enabled — approval_routing_rule_tenant_isolation (persisted across the move). Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at() (persisted).

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
approval_workflow_id	UUID	NOT NULL	—	FK → approvals.approval_workflow
workflow_type	text	NOT NULL	—	Free text — matches approval_workflow's own resolution; chk_approval_routing_rule_workflow_type DROPPED this migration
threshold_cents	bigint	nullable	—	Amount above which this rule applies; NULL = applies always
site_id	UUID	nullable	—	FK → multi_loc.site; NULL = tenant-wide rule
priority	integer	NOT NULL	0	Higher value = higher priority when multiple rules match
is_active	boolean	NOT NULL	true
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints: none (chk_approval_routing_rule_workflow_type DROPPED this migration, matching approval_workflow's own free-text resolution).

Indexes: PK; tenant_id; workflow_type; approval_workflow_id.

Triggers: set_updated_at (persisted).

Autonomy: human-only, explicitly ruled out — routing-rule DEFINITIONS are a governance decision, same rationale as approval_workflow.

---

approvals.approval_policy (10 cols, human/tenant-config) — NET-NEW

Self-approval & Segregation-of-Duties (SoD) configuration, read by approval_step's own trigger logic at decision time (Guard 3 below). allow_self_approval is declared intent only — it can never override the hard CHECK/trigger guards on approval_request/approval_step regardless of its value, a distinction the design-verification pass required be explicit rather than implied. min_distinct_approvers is DB-enforced (not just advisory) via trg_approval_step_distinct_approvers. require_role_separation stays service-layer-only (reads identity.role_assignment, never duplicated here) — disclosed as such, not silently unenforced.

Tenant-scoped. RLS enabled — approval_policy_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
workflow_type	text	nullable	—	NULL = tenant-wide default; SET = per-workflow-type override
allow_self_approval	boolean	NOT NULL	false	Declared intent only — never overrides the hard DB guards (Guard 2) regardless of value
min_distinct_approvers	integer	NOT NULL	1	DB-enforced via trg_approval_step_distinct_approvers (Guard 3), not merely advisory
require_role_separation	boolean	NOT NULL	false	Service-layer-only — reads identity.role_assignment, never duplicated here; disclosed
escalation_timeout_minutes	integer	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (1): chk_approval_policy_min_distinct_approvers (min_distinct_approvers > 0).

Indexes: PK; tenant_id; UNIQUE approval_policy_tenant_type_unique on (tenant_id,workflow_type) WHERE workflow_type IS NOT NULL AND deleted_at IS NULL; UNIQUE approval_policy_tenant_wide_unique on (tenant_id) WHERE workflow_type IS NULL AND deleted_at IS NULL — the 2-partial-unique pattern (admin.tenant_setting's own precedent): a single unique on (tenant_id,workflow_type) with nullable workflow_type would NOT block two tenant-wide-default rows, since NULL != NULL in Postgres.

Triggers: set_updated_at.

Autonomy: human/tenant-config — a tenant editing its own approval policy via the Owner Dashboard; not an agent-writable surface.

---

approvals.approval_request (20 cols, engine-written runtime) — MOVED from admin.approval_request (+2 net: −1 step_history dropped, +3 initiator_is_agent/escalated_at/expires_at)

In-flight and resolved approval INSTANCES — the routing engine's runtime surface. The engine routes; the source record (pos.sale, purchasing.purchase_order, etc.) records the outcome — no changes to any locked source schema.

initiator_actor_id (renamed from requested_by_actor_id) is now NOT NULL — this was the root cause of the historical null-approver bypass (Guard 1): a nullable initiator let both self-approval guards fail open, since x <> NULL evaluates to NULL (not FALSE) in Postgres, and a CHECK only rejects on FALSE. Identical bug class to the historical chk_purchase_order_sent_requires_approval NULL-bypass.

Tenant-scoped. RLS enabled — approval_request_tenant_isolation (persisted across the move). Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at() (persisted).

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
approval_workflow_id	UUID	NOT NULL	—	FK → approvals.approval_workflow
workflow_type	text	NOT NULL	—	Free text — chk_approval_request_workflow_type DROPPED this migration
source_module	text	NOT NULL	—	CHECK IN (purchasing,pos,orders,identity,crm,inventory,ai,other) — widened from 4 to 8 values; structural polymorphism, a referential-integrity concern, not business semantics
source_type	text	NOT NULL	—	Free text, no CHECK (the original draft's proposed CHECK-enumerated pairing with source_module was removed — see Global rules above) — e.g. 'purchase_order', 'sale_discount', 'sale_refund'
source_ref	UUID	NOT NULL	—	The record needing approval — polymorphic, plain UUID, discriminated by source_module; no FK
amount_cents	bigint	nullable	—	The amount that triggered approval routing
status	text	NOT NULL	'pending'	CHECK IN (pending,approved,rejected,cancelled,expired) — expired is new
current_step	integer	NOT NULL	1	Which step in the workflow is currently awaiting action
initiator_actor_id	UUID	NOT NULL	—	FK → identity.actor — renamed from requested_by_actor_id, made NOT NULL — the null-approver-bypass fix (Guard 1)
initiator_is_agent	boolean	NOT NULL	false	NEW — whether the initiator was an agent-type actor; informational, does not itself drive any guard (the guards key off approval_step.acted_by_actor_id's actor type, not this flag)
resolved_by_actor_id	UUID	nullable	—	FK → identity.actor — who made the final decision
resolved_at	timestamptz	nullable	—	When status reached approved/rejected/cancelled/expired
resolution_note	text	nullable	—	Approver comment
escalated_at	timestamptz	nullable	—	NEW — when this request was escalated (per approval_policy.escalation_timeout_minutes)
expires_at	timestamptz	nullable	—	NEW — deadline for resolution before auto-expiry
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete

Dropped column: step_history (jsonb) — superseded by the queryable approval_step table below.

CHECK constraints (4): chk_approval_request_source_module (widened 4→8 values); chk_approval_request_status (widened to add expired); chk_approval_request_resolver_not_initiator (renamed from chk_approval_request_reviewer_not_creator, distinctness only: resolved_by_actor_id IS NULL OR resolved_by_actor_id <> initiator_actor_id); chk_approval_request_resolved_requires_actor_at (NEW — the approved-by-nobody fix, Guard 1: status NOT IN ('approved','rejected') OR (resolved_by_actor_id IS NOT NULL AND resolved_at IS NOT NULL), mirrors purchasing.purchase_order's own chk_purchase_order_approved_requires_actor_at).

Indexes: PK; tenant_id; status WHERE status = 'pending' — the pending-approval queue; (source_module,source_type,source_ref) — widened from a 2-column (source_module,source_ref) index to 3 columns this migration (index dropped and recreated under the same name); approval_workflow_id.

Triggers: set_updated_at (persisted).

Autonomy: this table is the runtime surface an initiating module's own agent (if any) writes into. initiator_is_agent records whether the initiator was an agent — informational, not a gate by itself. Mechanical creation/routing is legitimate agent work; the resolution decision itself is gated by the guards on approval_step below, not by an automation_source/review_status pair — this module deliberately does not carry that pack (see Global rules).

---

approvals.approval_step (14 cols, engine-written runtime — the core guard surface) — NET-NEW

Per-request step instance, replacing the dropped step_history JSON with a queryable table. resolution_mode distinguishes an AND-parallel group (all_must_approve — every row at this step_index must approve) from an OR-candidate-fanout group (any_one_resolves — any one resolves it) — the original draft left "multiple rows share the same step_index" ambiguous between the two; the verification pass required this be explicit. step_mode/condition are snapshotted from approval_workflow.steps at creation time (mirrors ai.agent_execution.authority_level_applied's own snapshot-not-live-reference precedent), so a later edit to the workflow definition never retroactively changes an in-flight approval's already-committed shape.

This table carries Guards 2, 3, and 4 in full, plus half of Guard 5 as real Postgres triggers (a same-row CHECK cannot express any of them — each requires reading a different row or a different table):

Tenant-scoped. RLS enabled — approval_step_tenant_isolation. No soft delete, no deleted_at — a step is a runtime fact, never edited away. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
approval_request_id	UUID	NOT NULL	—	FK → approvals.approval_request
step_index	integer	NOT NULL	—	Ordinal position; multiple rows may share the same value (a parallel/fanout group)
step_mode	text	NOT NULL	'sequential'	CHECK IN (sequential,parallel,conditional) — snapshotted from the parent workflow's steps at creation
resolution_mode	text	NOT NULL	'any_one_resolves'	CHECK IN (all_must_approve,any_one_resolves) — distinguishes AND-parallel from OR-fanout groups sharing a step_index
condition	jsonb	nullable	—	Snapshotted from approval_workflow.steps at creation; e.g. {"amount_cents_gt": 500000}
assigned_approver_actor_id	UUID	nullable	—	FK → identity.actor
acted_by_actor_id	UUID	nullable	—	FK → identity.actor — who actually decided this step
decision	text	nullable	—	CHECK decision IS NULL OR decision IN ('approved','rejected') — NULL = not yet decided
decided_at	timestamptz	nullable	—
note	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained

CHECK constraints (3): chk_approval_step_step_mode; chk_approval_step_resolution_mode; chk_approval_step_decision (NULL-safe: nullable because "not yet decided" is a valid state).

Indexes: PK; tenant_id; approval_request_id; (approval_request_id,step_index).

Triggers (5, platform.set_updated_at() / approvals.check_step_no_self_approval() / approvals.check_step_distinct_approvers() / approvals.check_step_blocks_agent_approver() / approvals.supersede_tokens_on_step_resolved()):

1. set_updated_at — standard.
2. trg_approval_step_no_self_approval (BEFORE INSERT OR UPDATE) — Guard 2. Looks up the parent request's initiator_actor_id; if NEW.acted_by_actor_id equals it, raises an exception. Fires on every row regardless of step_index — closes self-approval on non-final steps too, not just the header.
3. trg_approval_step_distinct_approvers (BEFORE INSERT OR UPDATE) — Guard 3. No-ops unless NEW.acted_by_actor_id/NEW.decision are both set. Resolves the effective min_distinct_approvers from approval_policy (per-workflow_type row first, then the tenant-wide NULL-workflow_type row, else 1); if > 1, counts sibling rows at the same approval_request_id+step_index already decided by the same acted_by_actor_id and rejects if any exist — closes parallel-step quorum-spoofing.
4. trg_approval_step_blocks_agent_approver (BEFORE INSERT OR UPDATE) — Guard 4, the C8 boundary. No-ops if NEW.acted_by_actor_id IS NULL. Otherwise joins approval_request → approval_workflow to read blocks_agent_approver, looks up identity.actor.actor_type for the acting actor, and rejects if blocks_agent_approver AND actor_type = 'agent'.
5. trg_approval_step_supersede_tokens_on_resolve (AFTER UPDATE) — part of Guard 5 (token security): when NEW.decision transitions to non-NULL or changes value, marks every still-outstanding approval_token row for this step as superseded_at = now() — closing the sibling-token replay gap regardless of which channel (UI or token-click) resolved the step.

Autonomy: this is the actual decision surface. It is not gated by an automation_source/review_status pair; it is gated by the 3 BEFORE triggers above plus the C8 write-lock on approval_workflow.blocks_agent_approver — the module's substitute for the standard autonomy pack (see Global rules).

---

approvals.approval_delivery (12 cols, system-written) — NET-NEW

Outbound approver notifications (email/in-app/SMS). Not a duplicate of platform.outbox — different audience: outbox carries domain-facing state-change events, this carries human-facing approver notifications. Is a disclosed, deliberate, TEMPORARY duplication of a slice of the not-yet-built notifications module's own planned "delivery attempts" scope (docs/modules/MODULE_INDEX.md already lists an 11-table/166-col Notifications module as planned) — logged to OPEN_ITEMS with a reconciliation trigger, not silently carried.

Tenant-scoped. RLS enabled — approval_delivery_tenant_isolation. No soft delete. No updated_at column at all — a delivery attempt is a discrete, append-oriented fact; its lifecycle is tracked via status/sent_at/delivered_at/opened_at, not row revision.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
approval_request_id	UUID	NOT NULL	—	FK → approvals.approval_request
approval_step_id	UUID	nullable	—	FK → approvals.approval_step — NULL for a request-level notification preceding any step
recipient_actor_id	UUID	NOT NULL	—	FK → identity.actor
channel	text	NOT NULL	—	CHECK IN (email,in_app,sms)
status	text	NOT NULL	'pending'	CHECK IN (pending,sent,delivered,opened,failed)
sent_at	timestamptz	nullable	—
delivered_at	timestamptz	nullable	—
opened_at	timestamptz	nullable	—
error	text	nullable	—	Failure detail when status = 'failed'
created_at	timestamptz	NOT NULL	now()

CHECK constraints (2): chk_approval_delivery_channel; chk_approval_delivery_status.

Indexes: PK; tenant_id; approval_request_id.

Triggers: none.

Autonomy: system-written — the engine's own notification dispatch, not a human or agent decision surface.

---

approvals.approval_token (10 cols, security-critical, system-written) — NET-NEW

One-click approve/reject tokens. token_hash only — SHA-256 of a ≥256-bit CSPRNG value, mirroring admin.api_key's own hash-only convention (specified explicitly per the verification pass, which found the original draft left entropy/algorithm unspecified). expires_at defaults to a 72-hour window at the service layer (not a DB default — set explicitly per-issue). superseded_at (distinct from used_at) is set when a newer token issues for the same (approval_step_id, actor_id, action) triple, or when the step resolves through any channel — closing the sibling-token replay gap the verification pass found (an opposite-action token for the same step+actor otherwise remained independently valid and redeemable after its sibling, or a UI-driven decision, already resolved the step).

Redemption is one atomic gate, not expressible as a Drizzle/CHECK constraint — a single UPDATE folding all three conditions together:

UPDATE approvals.approval_token SET used_at = now()
WHERE id = :id AND used_at IS NULL AND superseded_at IS NULL AND expires_at > now()
RETURNING *

This closes a TOCTOU race the original draft's used_at-only predicate left open on the expiry dimension.

Access model: token redemption is a pre-session action (no app.current_tenant_id context at click time) — the redemption code path runs via a service-role connection deriving tenant scope solely from the matched token row, the same RLS-bypass precedent this codebase already documents for identity.service_account/api_key's own auth handshake. This exact gap exists, unaddressed, in identity.invitation today — closed here instead of reproduced (see OPEN_ITEMS for the note that identity.invitation itself remains open).

Tenant-scoped. RLS enabled — approval_token_tenant_isolation. No soft delete, no updated_at — a token is issued once and reaches a terminal state (used_at/superseded_at/expires_at) without row revision beyond those 2 timestamp fields.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
approval_step_id	UUID	NOT NULL	—	FK → approvals.approval_step
actor_id	UUID	NOT NULL	—	FK → identity.actor — who this token authorizes
action	text	NOT NULL	—	CHECK IN (approve,reject)
token_hash	text	NOT NULL	—	SHA-256 hash of a ≥256-bit CSPRNG value; raw value never stored
expires_at	timestamptz	NOT NULL	—	Service-layer default 72hr from issue
used_at	timestamptz	nullable	—	Set atomically on redemption
superseded_at	timestamptz	nullable	—	Set when a newer sibling token issues, or the step resolves via any channel
created_at	timestamptz	NOT NULL	now()

CHECK constraints (1): chk_approval_token_action.

Indexes: PK; tenant_id; approval_step_id; UNIQUE approval_token_token_hash_unique on (token_hash) — inbound redemption lookup.

Triggers (1, approvals.supersede_prior_tokens()): trg_approval_token_supersede_on_issue (AFTER INSERT) — on issuing a new token, marks every other outstanding (used_at IS NULL AND superseded_at IS NULL) token for the same (approval_step_id, actor_id, action) as superseded. Paired with trg_approval_step_supersede_tokens_on_resolve (documented under approval_step above, fires when the step itself resolves through any channel) — together these are Guard 5.

Autonomy: system-written, security-critical — token issuance/redemption is engine infrastructure, not a decision surface itself (the decision is recorded on approval_step).

---

approvals.approval_event (8 cols, ZERO — append-only audit) — NET-NEW

Append-only audit log. Reuses platform.reject_append_only_mutation() (already defined in 20260708140000_phase1_append_only_ledgers.sql, already consumed by 9 tables across 6 schemas) rather than reimplementing it. PK default platform.uuid_generate_v7() matches Remediation Phase 2's own established rule for genuinely append-only tables (time-ordered PKs keep future time-range partitioning possible without a PK rewrite).

Tenant-scoped, append-only. RLS enabled — approval_event_tenant_isolation. No updated_at/deleted_at at all — a structural guarantee, not a convention.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	PK — time-ordered, matches Remediation Phase 2's append-only-table rule
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
approval_request_id	UUID	NOT NULL	—	FK → approvals.approval_request
approval_step_id	UUID	nullable	—	FK → approvals.approval_step — NULL for request-level events (e.g. created)
event_type	text	NOT NULL	—	CHECK IN (created,routed,notified,viewed,approved,rejected,escalated,expired,cancelled,token_used,agent_recommendation,agent_decided)
actor_id	UUID	nullable	—	FK → identity.actor — NULL for actor-less events (e.g. expired)
payload	jsonb	nullable	—	Event-specific detail
created_at	timestamptz	NOT NULL	now()

CHECK constraints (1): chk_approval_event_event_type (12 values).

Indexes: PK; tenant_id; approval_request_id.

Triggers (1, reused platform.reject_append_only_mutation()): trg_approval_event_append_only (BEFORE UPDATE OR DELETE) — rejects both, for every role including service_role/superuser (a BEFORE trigger fires regardless of who owns the write; belt-and-suspenders alongside the REVOKE UPDATE, DELETE ON approvals.approval_event FROM authenticated grant change below).

Grants: REVOKE UPDATE, DELETE ON approvals.approval_event FROM authenticated (in addition to the trigger).

Autonomy: ZERO — this table records what happened; it is never itself a decision surface, human or agent.

---

The seams

The polymorphic source seam. approval_request.source_ref is a plain UUID discriminated by source_module/source_type — no FK to any of pos.sale, purchasing.purchase_order, orders.order_header, crm.customer_merge_candidate, inventory.stock_adjustment_request, ai.import_record, or any other consumer. The engine routes and records outcomes; consuming schemas stay locked and unchanged. This is the mechanism that lets approvals depend on nothing while everything else may depend on it.

The notifications overlap (disclosed, temporary). approval_delivery duplicates a slice of the not-yet-built notifications module's own planned "delivery attempts" scope. See OPEN_ITEMS — the eventual reconciliation direction (thin producer into notifications, or notifications absorbing this table outright) is an open decision, not decided here.

The platform.outbox non-duplication. approval_delivery is deliberately NOT the same mechanism as platform.outbox — different audience (domain-facing state-change events vs. human-facing approver notifications) — so no seam or FK exists between them, by design.

The purchasing PO-approval non-convergence (by design, for now). purchasing.purchase_order.approval_status/.approved_by_actor_id is a separate, bespoke, already-locked gate; it deliberately does NOT converge onto this engine yet (decided: leave the redundant gate as-is until purchasing's service layer is built — see OPEN_ITEMS row 11).

The 14 documented future-convergence candidates. identity.access_request, identity.sod_violation, crm.customer_tax_certificate, crm.customer_merge_candidate/customer_merge, inventory.stock_adjustment_request, inventory.item_merge_candidate/item_merge, inventory.stock_count reconciliation sign-off, ai.import_record accept/reject, pos.sale_refund.approved_by_actor_id, purchasing.vendor_invoice approve/dispute/void, purchasing.vendor_invoice_match discrepancy-resolution, and purchasing.vendor_return RMA-authorization each carry their own review/approval-shaped gate today, built before this engine existed. None of them are migrated or FK'd into approvals by this build — each is logged as its own OPEN_ITEMS row, triggered on that module's own service-layer build, not bundled in here. See "What's deliberately NOT here" below.

The agent-as-approver (role 3) non-enablement. trg_approval_step_blocks_agent_approver exists and is live-reproduced, but no workflow may set blocks_agent_approver = false and let an agent actually act as approver in production until a dedicated enablement decision is made AND the guard is re-proven live against real production data at that time (OPEN_ITEMS row 17) — today, every workflow that matters financially should set blocks_agent_approver = true.

The proxy self-approval limitation (permanent, disclosed, schema-unsolvable). An agent-initiated request resolved by a different, human-controlled actor who is nonetheless a proxy for the same interest is not schema-detectable — no trigger can close this; it is a permanent limitation, not a deferred gap (OPEN_ITEMS row 16).

---

What's deliberately NOT here

This module does not attempt to converge, absorb, or replace the review/approval-shaped columns already living on other modules' own tables — the broader codebase-wide autonomy-review surface (automation_source/review_status/decision_provenance-carrying tables, and purpose-built approval-shaped columns like purchase_order.approval_status) established by the 2026-07-06 autonomy-first backfill and every module built since. That surface is intentionally excluded from this build:

• It is a post-hoc review pattern (an agent already took an action; a human reviews it after the fact via review_status), whereas approvals is a pre-execution gate pattern (a decision is blocked until approved). The two are related but distinct mechanisms, and conflating them here would have meant reopening and migrating dozens of already-locked, already-shipped tables across a dozen modules for zero immediate benefit.
• The 14 concrete convergence candidates identified during this build's own design pass (identity, crm ×2, inventory ×3, ai, pos, purchasing ×3, plus the identity mixed-scope-template question) are each logged as their own OPEN_ITEMS row with a specific trigger ("when that module's service layer is built"), not silently deferred and not bundled into this migration. See "The seams" above for the full list and OPEN_ITEMS.md rows 1–14 for the authoritative trigger text on each.
• Two of those candidates (crm.customer_merge_candidate/customer_merge and inventory.stock_adjustment_request) have a review_status column that IS the business approval gate itself (the row's existence gates whether the merge/adjustment happens) rather than an autonomy-seam-only marker — their eventual convergence decision (drop it, keep as a denormalized cache of approval_request.status, or leave as system-of-record) is explicitly unresolved, not assumed.
• billing.ar_adjustment's own disconnected state-machine gap (an agent-proposed write-off can reach status='posted' while review_status is still 'pending') and ai.agent_execution's missing reviewer-attribution column are both flagged to whoever owns those modules next (OPEN_ITEMS rows 23–24) — this module is the natural place either eventually closes, but neither is touched by this build.

---

approvals — Design Patterns Summary

The 6 critical guards

All 6 are live-reproduced with real before/after proof against the local Supabase Postgres DB (127.0.0.1:54322), in addition to the regression suite.

1. Approved-by-nobody — chk_approval_request_resolved_requires_actor_at. Live-reproduced: with the constraint and the initiator_actor_id NOT NULL both temporarily dropped, an INSERT with status='approved', resolved_by_actor_id NULL, and zero approval_step rows SUCCEEDED (the bug, reproduced on purpose); with the fix restored, the identical attempt fails both at INSERT time (NOT NULL violation on initiator_actor_id) and via the presence CHECK (a real initiator, still-NULL resolver).
2. Self-approval, all paths — chk_approval_request_resolver_not_initiator + trg_approval_step_no_self_approval. Live-reproduced 3 sub-cases: (a) header self-approval (resolved_by_actor_id = initiator_actor_id) rejected; (b) self-approval on a NON-FINAL step (step_index 1 of a 2-step chain) rejected — the trigger fires on every row, not just the terminal one; (c) the null-approver path (zero approval_step rows ever created, initiator tries to self-resolve the header directly) rejected.
3. Parallel-step quorum-spoofing — trg_approval_step_distinct_approvers. Live-reproduced: the same actor deciding 2 sibling parallel steps (same step_index, min_distinct_approvers=2) is rejected on the second attempt; a genuinely different actor succeeds.
4. C8 agent-money-boundary — trg_approval_step_blocks_agent_approver + a column write-lock on blocks_agent_approver. Live-reproduced: an agent-type actor resolving a step on a blocks_agent_approver=true workflow is rejected; the same step resolved by a human succeeds (the trigger targets agents specifically, not blanket-blocks all decisions). A THIRD bug was found live during this build (not by either earlier design/verification pass): the first-attempt column-level REVOKE UPDATE (blocks_agent_approver) FROM authenticated did NOT actually restrict anything, because Postgres ACLs are additive across granularities — a column-level REVOKE cannot subtract from a broader table-level GRANT UPDATE that already covers the same privilege (confirmed via information_schema.column_privileges AND a live UPDATE that unexpectedly succeeded). Fixed: REVOKE UPDATE ON approvals.approval_workflow FROM authenticated, then GRANT UPDATE (col1, col2, ...) naming every column except blocks_agent_approver. Re-tested live: the flip attempt now fails with permission denied for table approval_workflow; an ordinary column (description) on the same table remains writable by the same role.
5. Token security — hash-only storage (token_hash, no raw value column), atomic single-use redemption (used_at/superseded_at/expires_at folded into ONE UPDATE ... WHERE statement), plus the sibling-supersession trigger pair (trg_approval_token_supersede_on_issue, trg_approval_step_supersede_tokens_on_resolve). Live-reproduced: first redemption succeeds; replay of the same token is rejected (0 rows updated); issuing a 2nd token for the same (step, actor, action) supersedes the older one (superseded_at set), and the superseded token can no longer be redeemed; an expired token is rejected by the combined atomic predicate.
6. Append-only audit — approval_event, REVOKE UPDATE, DELETE ... FROM authenticated + reused platform.reject_append_only_mutation(). Live-reproduced: both UPDATE and DELETE against approval_event are rejected, even as the Postgres superuser — the trigger blocks every role, not just authenticated.

NULL-in-CHECK sweep

All 13 CHECK constraints in this schema were examined for NULL-bypass risk. Every CHECK-guarded enum/threshold column is itself NOT NULL (channel, status, event_type, min_distinct_approvers, source_module, action, resolution_mode, step_mode — 8 columns confirmed via information_schema), so no NULL-bypass surface exists on any of them. The 2 CHECKs on legitimately-nullable columns use NULL-safe IS NULL OR patterns deliberately, matching intended semantics rather than a bypass: chk_approval_request_resolver_not_initiator on resolved_by_actor_id (nullable because "not yet resolved" is valid), and chk_approval_step_decision on decision (nullable because "not yet decided" is valid). The one NEW presence CHECK, chk_approval_request_resolved_requires_actor_at, uses only NULL-safe predicates (NOT IN against a NOT NULL column, IS NOT NULL checks) — never bare equality against a nullable column — confirmed fully NULL-safe by construction. The 2 trigger functions with the greatest cross-row reach (check_step_distinct_approvers, check_step_blocks_agent_approver) rely on FK + NOT NULL-enforced referential integrity to guarantee certain looked-up values are non-NULL, rather than being self-contained NULL-safe in total isolation — a legitimate design given Postgres's own FK guarantees, disclosed rather than silently assumed.

Section 4 self-audit result

Builder's own pass, independently re-derivable (not to be trusted blind): Items A–P plus T and U all PASS or N/A, zero FAILs. 3 minor GAPs logged, none blocking:

1. approval_step.condition lacked a concrete example JSONB shape in the schema doc — closed above ({"amount_cents_gt": 500000}).
2. No index yet on approval_request.expires_at / approval_token.expires_at for a future escalation/expiry-sweep job — deferred with the trigger "add when the sweep job is built," matching this codebase's own established "add this index when the alerting query is defined" precedent (OPEN_ITEMS row 21).
3. No CHECK enforcing step_mode/resolution_mode semantic consistency (resolution_mode is only meaningful when step_mode='parallel') — deferred with the trigger "add when real step-creation logic is built and the production shape is known" (OPEN_ITEMS row 22).

Column-count reconciliation

Table	Cols		Table	Cols
approval_workflow	12		approval_step	14
approval_routing_rule	11		approval_delivery	12
approval_policy	10		approval_token	10
approval_request	20		approval_event	8
			Total	97

Verified against packages/db/src/schema/approvals/*.ts and packages/db/migrations/20260709070000_approvals_module.sql: 8 tables, 97 columns.

Move accounting — admin's 3 approval tables (39 cols: approval_workflow 10, approval_routing_rule 11, approval_request 18) are the seed. This build's changes to them: approval_workflow 10 + 2 (step_mode, blocks_agent_approver) = 12; approval_routing_rule 11 + 0 (unchanged shape) = 11; approval_request 18 − 1 (step_history dropped) + 3 (initiator_is_agent/escalated_at/expires_at) = 20 (+2 net). The 3 moved tables total 12 + 11 + 20 = 43 cols. The 5 wholly new tables total approval_policy 10 + approval_step 14 + approval_delivery 12 + approval_token 10 + approval_event 8 = 54 cols. 43 + 54 = 97, exact.

Admin's own reconciliation: 161 (pre-move) − 39 (approval_workflow 10 + approval_routing_rule 11 + approval_request 18) = 122 cols, 10 tables remaining in admin — see docs/database/schema_docs/admin.md.

Service layer

No ApprovalsService yet — schema-only this pass, same as every module's deferred service layer at build time.

JSONB columns

• approval_workflow.steps — ordered approval-step definitions: [{"step":1,"approver_role":"manager","condition":{"amount_cents_gt":500000}},...] (v1/admin-era shape preserved verbatim).
• approval_step.condition — snapshotted per-step condition from the parent workflow's steps at creation time, e.g. {"amount_cents_gt": 500000}.
• approval_event.payload — event-specific detail, shape varies per event_type.

Triggers, full list (7 functions, 11 trigger objects — 5 set_updated_at instances across 5 tables, plus 6 named triggers)

Trigger	Table	Fires	Function
set_updated_at	approval_workflow, approval_routing_rule, approval_request, approval_policy, approval_step	BEFORE UPDATE	platform.set_updated_at() (shared, reused)
trg_approval_step_no_self_approval	approval_step	BEFORE INSERT OR UPDATE	approvals.check_step_no_self_approval()
trg_approval_step_distinct_approvers	approval_step	BEFORE INSERT OR UPDATE	approvals.check_step_distinct_approvers()
trg_approval_step_blocks_agent_approver	approval_step	BEFORE INSERT OR UPDATE	approvals.check_step_blocks_agent_approver()
trg_approval_step_supersede_tokens_on_resolve	approval_step	AFTER UPDATE	approvals.supersede_tokens_on_step_resolved()
trg_approval_token_supersede_on_issue	approval_token	AFTER INSERT	approvals.supersede_prior_tokens()
trg_approval_event_append_only	approval_event	BEFORE UPDATE OR DELETE	platform.reject_append_only_mutation() (shared, reused — already consumed by 9 tables/6 schemas before this module)

Open items carried forward (see OPEN_ITEMS.md)

No ApprovalsService yet. 14 named future-convergence candidates across identity/crm/inventory/ai/pos/purchasing, each triggered on that module's own service-layer build (OPEN_ITEMS rows 1–14); the identity global/mixed-scope workflow-template question (row 2, approval_workflow.tenant_id is blanket NOT NULL today, no global-template representation). approval_delivery's disclosed, temporary overlap with the not-yet-built notifications module (row 15). The proxy self-approval limitation — permanent, schema-unsolvable (row 16). Agent-as-approver (role 3) enablement — requires a dedicated decision plus a fresh live-reproduction of trg_approval_step_blocks_agent_approver against real production data at enablement time, not just this build's own proof (row 17). platform.outbox has no dispatcher/consumer anywhere yet — pre-existing, not introduced here, but relevant background for anyone eventually wiring approval_delivery/notifications against it (rows 18–19). approval_step.condition's example JSONB shape — now documented above, low priority, closed. No index yet on approval_request.expires_at/approval_token.expires_at — add when an escalation/expiry-sweep job is built (row 21). No CHECK enforcing step_mode/resolution_mode consistency — add when real step-creation logic is built and the production shape is known (row 22). billing.ar_adjustment's disconnected status/review_status state machine (row 23) and ai.agent_execution's missing reviewer-attribution column (row 24) are flagged to whoever owns those modules next, not fixed here.