identity — Phase 3

36 tables, 410 columns — AGENT_SKILL DROP (2026-07-17, Identity's 6th reopen, Phase 6 of 6 of the agents-v2/v3 build — CLOSES THE ENTIRE BUILD): identity.agent_skill (9 cols) and identity.agent_skill_assignment (10 cols) — 2 tables / 19 cols — DROPPED entirely (migration 20260717000000_identity_reopen_drop_legacy_agent_skill.sql; pre-migration audit found agent_skill_assignment had 0 live rows and agent_skill's 13 live rows were all test debris, not real data), superseded by agents.skill_definition/agents.skill_version/agents.agent_skill_assignment (built Phase 5) — skill assignment is now at SKILL VERSION granularity, not skill-identity granularity. IdentityService's 4 skill methods were rewritten to join through the agents schema instead (zero live HTTP consumers affected, grep-confirmed). This same reopen also added the agent_reader Postgres role + agentReaderDB() connection helper (glue code only — no table/column impact in this schema) — see the "Phase 6" subsection below and PROJECT_DECISIONS.md #67. 38 tables, 429 columns (unchanged — the pre-Phase-6 baseline) — HEADER/LINE REMEDIATION FOLLOW-UP (2026-07-10, Identity's 5th reopen): the orphan flagged by fix #3 below was resolved (isolated dev-seed junk, deleted) and 3 of the 5 candidate site_id-shaped columns were wired to real composite FKs → multi_loc.site (user_site_assignment.site_id, tenant_user.default_site_id, invitation_site_assignment.site_id); platform.tenant.primary_site_id, identity.user_permission_override.scope_id, and a newly-surfaced identity.access_request.requested_scope_id remain genuinely deferred. Zero column/table count change. See the "Header/Line Remediation — Follow-up" subsection below and PROJECT_DECISIONS.md #54. HEADER/LINE REMEDIATION FIX #3 (2026-07-10, Identity's 4th reopen): +identity.invitation_site_assignment (+1 table/+6 cols) — pre-acceptance staging of intended site access, mirroring user_site_assignment's own shape; site_id deliberately carries NO FK (matches user_site_assignment.site_id's own existing, disclosed gap, not newly introduced by this fix) — and user_site_assignment.created_from_invitation_site_assignment_id (+1 col), a nullable reciprocal-traceability composite FK back to the new table. identity.invitation gained a prerequisite unique('invitation_id_tenant_id_unique') and its site_assignments JSONB column is now DEPRECATED IN PLACE (comment only — confirmed zero live rows in identity.invitation at build time, so no backfill was needed or possible). A mandatory pre-migration orphan-check audit found ONE live orphaned row in user_site_assignment.site_id (absent from multi_loc.site) — because of this, the optional opportunistic bundle wiring all 3 site_id-shaped columns (this new table's own site_id, user_site_assignment.site_id, tenant_user.default_site_id) to real multi_loc.site FKs was explicitly NOT taken this pass; flagged for a human decision (null out the orphan vs. correct the reference), not unilaterally resolved — see the dedicated "Header/Line Remediation — Fix #3" subsection below, docs/open-items/OPEN_ITEMS.md, and PROJECT_DECISIONS.md #52. (Pre-fix-3 baseline: 37 tables, 422 columns — after the 2026-07-09 operator identity build, below.) OPERATOR IDENTITY BUILD (2026-07-09): +identity.operator (+14 cols) and +identity.operator_role_assignment (+8 cols) — an isolated Vrida-operator (cross-tenant SaaS staff) identity, carved out of the previous identity_user.is_platform_user=true boolean overlay. actor.actor_type CHECK widened to add 'operator' (structurally distinct from a tenant 'user'); trg_tenant_user_actor_type_check needed zero modification — 'operator' != 'user' was already rejected, live-reproduced. Both new tables get a belt-and-suspenders security backstop: explicit REVOKE SELECT, INSERT, UPDATE, DELETE ... FROM authenticated (belt) plus ENABLE ROW LEVEL SECURITY with zero policies (suspenders — Postgres denies all rows to authenticated even if a future change accidentally re-GRANTs table access). identity_user.is_platform_user is NOT dropped this pass (still readable/writable; no longer read by any auth path, replaced in practice by identity.operator) — see OPEN_ITEMS.md for the drop-trigger. See PROJECT_DECISIONS.md #45. (Pre-operator-build baseline: 35 tables, 400 columns — after Remediation Phase 3, below.) Originally locked 2026-06-09 at 11 tables / 140 cols; reopened to add password_policy (+12 cols) and scim_config (+11 cols) and tenant_user.external_id (+1 col); re-locked 2026-06-28 after Section 4 audit. Batch A LOCKED 2026-06-28 — Pass 1 (actor model): +actor table (+7 cols), −identity_user.status (−1 col), actor_id refactor (net +6 cols, +1 table). Pass 2 (groups, role inheritance, permission_group bundles): +actor_group (+10 cols), actor_group_member (+9 cols), permission_group (+8 cols), permission_group_permission (+4 cols), role.parent_role_id (+1 col) (net +32 cols, +4 tables). Batch B Pass 1 LOCKED 2026-06-28 — role management core: +role_assignment (+13 cols), +role_permission_group (+7 cols), +role_template (+8 cols), +role_template_permission_group (+4 cols), −tenant_user.role_id (−1 col) (net +31 cols, +4 tables). Batch B Pass 2 LOCKED 2026-06-28 — governance, session, access management: +sod_rule (+9 cols), +sod_rule_permission (+4 cols), +sod_violation (+16 cols), +identity_session (+13 cols), +access_request (+19 cols), +identity_access_event.session_id (+1 col); identity_access_event.event_type +9 CHECK values (4 SoD + 5 access_request lifecycle; 14→23) (net +62 cols, +5 tables). ALL OF BATCH B COMPLETE. Batch C LOCKED 2026-06-28 — machine + AI-agent identity: +agent_type_catalog (+8 cols), +service_account (+9 cols; shared PK), +api_key (+15 cols), +agent_identity (+12 cols; shared PK), +agent_skill (+9 cols), +agent_skill_assignment (+9 cols); +trigger trg_tenant_user_actor_type_check on tenant_user (DB-enforced human-only membership; closes Batch A deferred CHECK; first trigger in the identity schema) (net +62 cols, +6 tables). Batch D LOCKED 2026-06-28 — tenant_security_policy (+1 table, +8 cols); consent_record NOT BUILT (investigated: crm schema owns end-customer/shopper consent, platform.agreement_acceptance covers org-level platform legal consent, US SMB employment basis covers staff data processing — no v1 gap confirmed; see DR-29). Net Batch D: +1 table, +8 cols. IDENTITY SCHEMA DESIGN COMPLETE (Batches A–D, 34 tables / 365 cols). POST-BUILD RECONCILIATION (2026-06-29): Full doc-vs-DB diff performed after Phase 3 migration + Phase 4 build. Four tables had column drift; four had index-only drift. All resolved in migration 20260629020000_identity_drift_reconcile.sql and doc update. Net additions: +4 cols (identity_access_event.target_actor_id, identity_access_event.correlation_id, support_access_grant.correlation_id, agent_skill_assignment.assigned_by) → 34 tables / 369 cols. Doc now reflects live DB exactly. BUILD PHASE COMPLETE (2026-06-30): migration applied, IdentityService built (86 methods across 5 phases, 2026-06-29), tenant.vrida.app page mockups built (apps/web/tenant, 2026-06-30). Remaining: HTTP controller layer (zero endpoints exist for any IdentityService method today — see OPEN_ITEMS) and wiring the built pages to real data. AUTONOMY-FIRST BACKFILL (2026-07-06): applied as part of a cross-cutting, single-proposal retrofit across all 4 locked modules (platform, identity, shared, multi_loc) bringing them into compliance with the autonomy-first governing rule — see PROJECT_DECISIONS.md #19 for full rationale. role gained requires_approval_for_agents (+1 col) and sod_violation gained decision_snapshot (+1 col) → 34 tables / 371 cols. AGENT-DUTY-GRANT BUILD (2026-07-06): +agent_duty_grant (+25 cols; the A5 agent-authority passport, see PROJECT_DECISIONS.md #22) and identity_access_event.event_type +3 CHECK values (agent_duty_granted/revoked/updated; 39→42) → 35 tables / 396 cols. REMEDIATION PHASE 1 (2026-07-08): CHECK/RLS-only fixes closing gaps a senior-architect review found — no column/table count change (35 tables / 396 cols unchanged). See the "Remediation Phase 1" subsection below and PROJECT_DECISIONS.md #37. REMEDIATION PHASE 2 (2026-07-08): PK-generation-strategy-only change — no column/table count change (35 tables / 396 cols unchanged). See the "Remediation Phase 2" subsection below and PROJECT_DECISIONS.md #38. REMEDIATION PHASE 3 (2026-07-08): agent_identity gained the agent kill-switch — 4 new columns (status, suspended_at, suspended_by_actor_id, suspension_reason) + 2 CHECKs → 35 tables / 400 cols. See the "Remediation Phase 3" subsection below and PROJECT_DECISIONS.md #39. Source of truth for users, roles, permissions, site assignments, SSO providers, invitations, support-access grants, per-user permission overrides, global password policy, and per-tenant SCIM configuration. Sits between Supabase Auth (raw login / password / MFA / sessions) and Vrida application authorization. Supabase Auth remains the source of truth for credentials; identity is the source of truth for who has access to what tenant.

Cross-Phase Foreign Keys (identity)

Column	Target	Notes
identity_user.supabase_auth_user_id	external auth.users (Supabase)	Never an enforced FK — Supabase Auth lives in its own schema outside Vrida's migrations. Treated as a reference UUID; integrity enforced by application code and Supabase webhooks.
tenant_user.tenant_id, sso_provider.tenant_id, invitation.tenant_id, support_access_grant.tenant_id, user_site_assignment.tenant_id, user_permission_override.tenant_id, identity_access_event.tenant_id, role.tenant_id, scim_config.tenant_id, actor_group.tenant_id, actor_group_member.tenant_id, role_assignment.tenant_id, role_permission_group.tenant_id, sod_violation.tenant_id, access_request.tenant_id, service_account.tenant_id, api_key.tenant_id, agent_identity.tenant_id, tenant_security_policy.tenant_id	platform.tenant	Platform exists at Phase 1; FK enforced at migration time. (agent_skill_assignment.tenant_id removed from this list, 2026-07-17 — the table it named was dropped; see the "Phase 6" subsection below.)
tenant_user.default_site_id, user_site_assignment.site_id	multi_loc.site	Real composite FK as of 2026-07-10 (tenant_user_default_site_tenant_fkey, user_site_assignment_site_tenant_fkey) → multi_loc.site(id, tenant_id). See PROJECT_DECISIONS #54.
user_permission_override.scope_id (when scope_type='site'), access_request.requested_scope_id (when requested_scope_type='site')	multi_loc.site	Still deferred — plain UUID columns, no FK constraint. Not part of the 2026-07-10 wiring pass (PROJECT_DECISIONS #54); tracked in OPEN_ITEMS.

Platform → identity deferred FKs — the Phase 3 identity migration emits 8 ALTER TABLE statements that close the deferred FK columns from Platform's Phase 1 migration. With the Batch A actor model, these split into two groups based on semantic meaning:

4 columns → identity.actor (any actor type — human, service account, or agent):

Platform column	Target	Nullability in platform schema
platform.tenant_entitlement.granted_by_user_id	identity.actor	nullable
platform.tenant_setup_task.completed_by_user_id	identity.actor	nullable
platform.tenant_data_lifecycle.requested_by_user_id	identity.actor	nullable
platform.tenant_lifecycle_event.actor_user_id	identity.actor	nullable

4 columns → identity.identity_user (semantically must be a human — legal, compliance, or CS record):

Platform column	Target	Nullability in platform schema
platform.tenant_contact.identity_user_id	identity.identity_user	nullable
platform.agreement_acceptance.accepted_by_user_id	identity.identity_user	NOT NULL
platform.tenant_internal_activity.performed_by_user_id	identity.identity_user	NOT NULL
platform.operator_audit_log.operator_user_id	identity.identity_user	NOT NULL

Each forward-reference column is marked (FK constraint deferred — see Cross-Phase FKs) in its column-definition row below.

---

identity.actor

Polymorphic actor root. Every principal that can perform actions in Vrida — human users, service accounts, AI agents, and Vrida operators — has exactly one actor row. Detail tables (identity_user, service_account, agent_identity, operator) extend this via shared PK (class table inheritance): the detail table's id IS the actor's id.

NOT tenant-scoped — global actor record. Tenant scoping lives entirely in the detail tables (service accounts and agents are tenant-scoped; users are global via identity_user).

RLS: not applied — global actor record.

Access control: an actor row is readable if (a) the actor is a user who shares a tenant with the requesting principal (resolved via tenant_user), or (b) the actor is a service account or agent whose detail table's tenant_id matches the requesting principal's tenant. All other cross-actor reads require service_role. Writes — including INSERT on new actor rows — are performed only via service_role or application provisioning code; never directly by client-side code.

Insertion order: always insert actor first (UUID generated here), then insert the corresponding detail row with the same UUID as its PK.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK — also serves as PK of the corresponding detail table
actor_type	text	NOT NULL	—	CHECK IN ('user','service_account','agent','operator') — 'operator' added 2026-07-09 (PROJECT_DECISIONS #45), structurally distinguishing a cross-tenant Vrida operator from a tenant 'user'; enforced separately by trg_tenant_user_actor_type_check on tenant_user
status	text	NOT NULL	'active'	CHECK IN ('active','suspended','deactivated') — shared lifecycle state for all actor types
display_name	text	nullable	—	Human-readable label for audit UIs and logs. Populated by the detail table's insertion logic; nullable to avoid requiring a name at actor-row creation time.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on actor_type — filter by type (list all agents, list all service accounts)

---

identity.identity_user

Global user record. Seam to Supabase Auth via supabase_auth_user_id. Distinguishes tenant staff from Vrida platform staff via is_platform_user. Extends identity.actor via shared PK (class table inheritance): identity_user.id IS actor.id.

NOT tenant-scoped — global user record (analogous to platform.tenant). No tenant_id.

RLS: not applied — global user record.

Access control: a user may read only their own row, or rows of users within tenants they belong to (resolved via tenant_user). Cross-user reads outside that scope require service_role.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	—	PK and FK → identity.actor (shared PK / class table inheritance — no gen_random_uuid() default; UUID is generated on actor and passed in)
supabase_auth_user_id	UUID	NOT NULL	—	Seam to Supabase auth.users (never an enforced FK — see Cross-Phase FKs). Uniqueness enforced as partial unique index — see Indexes
email	text	NOT NULL	—
email_normalized	text	NOT NULL	—	Lowercased / trimmed, used for uniqueness
full_name	text	nullable	—
phone	text	nullable	—
is_platform_user	boolean	NOT NULL	false	DEPRECATED 2026-07-09 (PROJECT_DECISIONS #45) — Vrida operators now live in identity.operator, never identity_user; this column is no longer read or written by any auth path (AdminAuthGuard resolves against identity.operator exclusively). Still readable/writable — not dropped this pass, see OPEN_ITEMS.md for the drop-trigger. Always false for any row created after 2026-07-09.
last_login_at	timestamptz	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (supabase_auth_user_id) WHERE deleted_at IS NULL — partial unique (soft-delete pattern). A deactivated (soft-deleted) staff member can be re-onboarded; prefer reactivating the soft-deleted row over creating a new one, but the partial unique permits a fresh row if needed.
• UNIQUE on (email_normalized) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)

---

identity.tenant_user

Pure membership join — the row that says "this actor is a member of this tenant." Role assignment is now handled by role_assignment (Batch B Pass 1); tenant_user no longer carries a role_id column. actor_id references only actors with actor_type = 'user' (human staff) — enforced by trigger trg_tenant_user_actor_type_check (Batch C). Non-human actors (service accounts, agents) obtain tenant scope via their own detail tables (service_account.tenant_id, agent_identity.tenant_id) and never receive tenant_user rows. Vrida platform users do NOT get tenant_user rows — they reach tenant data via support_access_grant. See DR-26.

Tenant-scoped, many per tenant.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
actor_id	UUID	NOT NULL	—	FK → identity.actor. DB-enforced invariant (Batch C): must reference an actor with actor_type = 'user'. Enforced by trigger trg_tenant_user_actor_type_check (BEFORE INSERT OR UPDATE — raises EXCEPTION if actor.actor_type != 'user'). Non-human actors (service accounts, agents) use their own detail table tenant_id for tenant scoping — they never get tenant_user rows. See DR-26.
status	text	NOT NULL	'invited'	CHECK IN ('invited','active','suspended','removed')
default_site_id	UUID	nullable	—	Composite FK (tenant_user_default_site_tenant_fkey) → multi_loc.site (default_site_id, tenant_id) → (id, tenant_id) — real as of 2026-07-10, see Cross-Phase FKs and PROJECT_DECISIONS #54
invited_by_user_id	UUID	nullable	—	FK → identity.identity_user
external_id	text	nullable	—	External IdP user ID for SCIM provisioning/deprovisioning. Unused until SCIM is implemented — see scim_config.
joined_at	timestamptz	nullable	—
removed_at	timestamptz	nullable	—
last_login_at	timestamptz	nullable	—	Last login to THIS tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (tenant_id, actor_id) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)
• on (tenant_id, external_id) WHERE external_id IS NOT NULL — SCIM lookup index; unused until SCIM is implemented
• on (actor_id) — "which tenants does this actor belong to" query at login; not served by the compound partial unique above
• on (tenant_id, status) — list active / invited users within a tenant

Vrida platform users (identity_user.is_platform_user = true) do NOT have tenant_user rows. They access tenant data only through active, time-boxed support_access_grant records.

---

identity.role

Role definition. Built-in / Vrida-internal roles have tenant_id IS NULL and are shared across tenants. Custom roles have tenant_id populated and are tenant-isolated.

Tenant-scoped OR global (depending on tenant_id). Mixed-scope table.

RLS policy: USING (tenant_id IS NULL OR tenant_id = current_setting('app.current_tenant_id')::uuid) — global roles (tenant_id NULL) readable by all tenants; custom roles tenant-isolated. The tenant_id IS NULL branch is load-bearing; omitting it hides all built-in / Vrida-internal roles from every tenant (bug class #5).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	nullable	—	NULL = built-in / Vrida-internal role; populated = custom tenant role. FK → platform.tenant
role_code	text	NOT NULL	—	owner / manager / cashier / buyer / seasonal / admin / support / cs / read_only
name	text	NOT NULL	—	Display name
description	text	nullable	—
role_type	text	NOT NULL	—	CHECK IN ('system','vrida_internal','tenant_custom'). 'system' = Vrida-built application roles visible to and usable by tenants (e.g. owner, manager, cashier, buyer, seasonal, read_only). 'vrida_internal' = Vrida staff / operator roles, never exposed to tenants (e.g. admin, support, cs). 'tenant_custom' = roles created by the tenant admin.
is_builtin	boolean	NOT NULL	false
is_editable	boolean	NOT NULL	true
is_active	boolean	NOT NULL	true
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
parent_role_id	UUID	nullable	—	Self-FK → identity.role.id. NULL = root role (no inheritance). Single-parent only (Decision 3 / DR-12). DB CHECK: parent_role_id IS NULL OR parent_role_id != id (self-loop prevention). Cross-tenant guard (parent.tenant_id IS NULL OR = child.tenant_id) and cycle prevention (recursive ancestor walk, depth cap 5) are app-enforced — see DR-12.
requires_approval_for_agents	boolean	NOT NULL	false	Added 2026-07-06 — Autonomy-First Backfill (PROJECT_DECISIONS #19). Schema-level piece of an agent-elevation approval gate: when true, an agent-type actor being assigned this role must be routed through identity.access_request for human approval rather than assigned directly. identity.access_request already has the shape needed for enforcement. DEFAULT false is a zero-behavior-change default for every existing role. No service-layer rule was built this pass — assignRole() does not yet check this flag; that enforcement is deferred to future work.

Indexes:

• PK on id
• UNIQUE on (tenant_id, role_code) WHERE deleted_at IS NULL — constrains custom tenant roles only; does not apply to tenant_id IS NULL rows (Postgres NULL ≠ NULL).
• UNIQUE on (role_code) WHERE tenant_id IS NULL AND deleted_at IS NULL — prevents duplicate global / built-in / Vrida-internal role codes (NULL tenant_id rows do not collide under the standard partial unique above — bug class #1). Belongs in the Phase 3 schema migration, NOT the seed migration. This index also serves the "find global roles" query pattern (lookups WHERE tenant_id IS NULL).
• on (tenant_id) — covers the (tenant_id IS NULL OR tenant_id = $1) RLS disjunction query pattern; the partial uniques above do not efficiently serve this combined filter
• on (parent_role_id) WHERE parent_role_id IS NOT NULL — "list all roles that directly inherit from role X" (admin UI inheritance-tree display)

---

identity.permission

Catalog of all permission codes the application recognizes (e.g. pos.sale.refund, inventory.item.adjust). Reference data — shared across all tenants.

NOT tenant-scoped — reference data. No tenant_id, no deleted_at.

RLS: not applied — reference data.

Access control: readable by all authenticated users (needed for permission resolution at every authorization check). Writable only via service_role and the seed migration — application code never INSERTs / UPDATEs permission rows at runtime.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
permission_code	text	NOT NULL	—	UNIQUE — e.g. pos.sale.refund
module_code	text	NOT NULL	—	pos / inventory / crm / platform / identity / etc.
resource	text	NOT NULL	—	sale / item / customer / etc.
action	text	NOT NULL	—	refund / create / adjust / read / etc.
description	text	nullable	—
is_active	boolean	NOT NULL	true
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on permission_code

---

identity.role_permission

Links a role to a permission with an effect. deny overrides allow during permission resolution.

RLS policy: enforced via subquery on role — a row is visible if its role_id references a role where (role.tenant_id IS NULL OR role.tenant_id = current_setting('app.current_tenant_id')::uuid). Postgres has no policy inheritance; this is an explicit subquery policy. Equivalent SQL: USING (EXISTS (SELECT 1 FROM identity.role r WHERE r.id = role_permission.role_id AND (r.tenant_id IS NULL OR r.tenant_id = current_setting('app.current_tenant_id')::uuid))).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
role_id	UUID	NOT NULL	—	FK → identity.role
permission_id	UUID	NOT NULL	—	FK → identity.permission
effect	text	NOT NULL	'allow'	CHECK IN ('allow','deny'). deny overrides allow in resolution.
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (role_id, permission_id)
• on (permission_id) — reverse lookup: "which roles have permission X"

---

identity.user_site_assignment

For tenants with multiple sites: which sites a tenant_user has access to, optionally with a site-specific role that overrides the tenant-level role.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
tenant_user_id	UUID	NOT NULL	—	FK → identity.tenant_user
site_id	UUID	NOT NULL	—	Composite FK (user_site_assignment_site_tenant_fkey) → multi_loc.site (site_id, tenant_id) → (id, tenant_id) — real as of 2026-07-10, see Cross-Phase FKs and PROJECT_DECISIONS #54
site_role_id	UUID	nullable	—	FK → identity.role. NULL = use tenant-level role; populated = site-specific role differs
status	text	NOT NULL	'active'	CHECK IN ('active','suspended','removed')
is_primary_site	boolean	NOT NULL	false
created_from_invitation_site_assignment_id	UUID	nullable	—	Composite FK (user_site_assignment_created_from_invitation_tenant_fkey) → identity.invitation_site_assignment (created_from_invitation_site_assignment_id, tenant_id) → (id, tenant_id) — no bare .references(). Reciprocal traceability: which invitation-time staged assignment (if any) this real, post-acceptance row was created from. Nullable — most rows predate this column or were created outside the invitation flow (e.g. added directly by an admin). Added 2026-07-10, Header/Line Remediation fix #3 — see identity.invitation_site_assignment's own section above, including its mandatory orphan-check audit.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id) — RLS filters on tenant_id on every query; needs a leading index
• UNIQUE on (tenant_user_id, site_id) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)

---

identity.identity_access_event

Append-only event log covering both Vrida-initiated auth/session events and application-level authorization events. Captures: login/logout/MFA/password/session-revocation events that Vrida's own service layer initiates (distinct from raw Supabase auth.* internal events, which live in Supabase's own audit log); tenant selection; permission denials; role changes; site-access denials; invitation lifecycle; support-access grant/use/revoke; permission overrides; SSO login events; SoD violation lifecycle; access-request lifecycle. The identity_session table (added Batch B Pass 2) is Vrida's own session record — revoking a Vrida session is a Vrida action and is logged here, not in Supabase's tables.

LOCKED-TABLE TOUCHES (Batch B Pass 2): (1) event_type CHECK expanded from 14 to 23 values (+4 SoD violation event types + 5 access_request lifecycle event types). (2) session_id UUID nullable FK column added (+1 col; 13→14 cols).

POST-LOCK CORRECTIONS (2026-06-29 — two rounds):

• Round 1 (migration drift): event_type CHECK expanded from 23 → 39 values (union of Drizzle-authored auth-level set + doc app-level set, reconciled naming). ip_address type corrected text → inet. Column count unchanged.
• Round 2 (post-build doc-vs-DB reconciliation): Column set updated to match live DB. The Drizzle schema was independently authored with a different column model (actor_id NOT NULL, occurred_at, outcome, failure_reason, resource_type, resource_id) vs. the locked doc (subject_actor_id, event_status, target_actor_id, reason_code, correlation_id). The live model was built and used by IdentityService Phases 3–4; it is the authoritative column set. target_actor_id and correlation_id recovered to DB (+2 cols). Doc column count: 14 → 16.

Tenant-scoped (nullable for platform-level events), append-only. No updated_at, no deleted_at.

RLS: enabled — tenant isolation on tenant_id (rows with tenant_id IS NULL are platform-level events readable only by service_role).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
actor_id	UUID	NOT NULL	—	Who performed the action. FK → identity.actor
event_type	text	NOT NULL	—	CHECK IN (39 values — see groups below): Auth/session (Vrida-initiated): 'login','logout','login_failed','mfa_challenge','mfa_success','mfa_failed','password_reset_request','password_reset_complete','password_changed','account_locked','account_unlocked','session_revoked','all_sessions_revoked','sudo_granted','sudo_revoked' — SSO: 'sso_login','sso_login_failed' — Authorization decisions: 'tenant_selected','permission_denied','site_access_denied' — Role/permission mgmt: 'role_changed','permission_override_applied' — Invitation lifecycle: 'invitation_sent','invitation_accepted','invitation_expired','invitation_revoked' — Support access: 'support_access_granted','support_access_used','support_access_revoked','support_access_denied' — SoD governance: 'sod_violation_detected','sod_violation_acknowledged','sod_violation_waived','sod_violation_resolved' — Access requests: 'access_request_submitted','access_request_approved','access_request_denied','access_request_withdrawn','access_request_expired'
occurred_at	timestamptz	NOT NULL	now()	When the event occurred. Distinct from created_at to support backfill / replay.
ip_address	inet	nullable	—
user_agent	text	nullable	—
session_id	UUID	nullable	—	Not an enforced FK — a loose historical reference to identity.identity_session.id, corrected 2026-07-18 (pre-Phase-2 confirmation pass) from this doc's own prior claim that it was an enforced FK backed by a partial index; neither has ever existed in any migration, including the original 0001_true_loners.sql. A live sweep found 99%+ of session_id-populated rows orphaned — root-caused entirely to a test fixture (identity-session.spec.ts) that hard-deleted identity_session rows in its own teardown, violating this table's own documented "permanent audit history" design (see the identity_session table note and DR-21); zero production code path ever deletes a session row. That test bug is now fixed (teardown no longer deletes identity_session), but the FK stays unenforced for now because the accumulated orphaned rows already live inside this hard append-only ledger and cannot be backfilled or cleaned without bypassing the append-only trigger — see DR-21 for the full reasoning and OPEN_ITEMS for the disposition. The authenticated session during which this event occurred. NULL for pre-session events (login flow before identity_session is created), platform-level events, and webhook-triggered events. Enables "all events in session S" forensic query when the referenced session still exists. LOCKED-TABLE TOUCH (Batch B Pass 2).
tenant_id	UUID	nullable	—	NULL for platform-level events (e.g. login before tenant is selected). FK → platform.tenant
resource_type	text	nullable	—	Type of resource affected (e.g. 'role', 'permission', 'api_key'). Pairs with resource_id.
resource_id	UUID	nullable	—	UUID of the affected resource.
target_actor_id	UUID	nullable	—	Actor on the receiving end of the event (e.g. for role_changed — who got the role change). FK → identity.actor. Added in post-build reconciliation.
outcome	text	NOT NULL	'success'	CHECK IN ('success','failure','partial'). 'partial' covers split-success cases (e.g. bulk revocation where some items succeed).
failure_reason	text	nullable	—	Machine-readable failure reason (e.g. 'token_expired', 'insufficient_role'). Populated when outcome = 'failure'.
correlation_id	text	nullable	—	Ties to originating request / job for tracing. Added in post-build reconciliation.
metadata	JSONB	nullable	—	Event-specific context bag. Shape varies by event_type. Example ('permission_denied'): {"denial_reason":"insufficient_role","required_permission":"pos.sale.refund"}
created_at	timestamptz	NOT NULL	now()	Row creation timestamp.

Indexes:

• PK on id
• on (actor_id, occurred_at) — "all events by actor X" audit trail
• on (tenant_id, occurred_at) WHERE tenant_id IS NOT NULL — tenant event timeline
• on (event_type) — type-based lookups
• on (target_actor_id) WHERE target_actor_id IS NOT NULL — "all events targeting actor X" (role changes, status changes applied to this actor)

---

identity.sso_provider

Per-tenant SSO configuration (SAML or OIDC). Stores references to secrets and certificates — never raw values.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
provider_type	text	NOT NULL	—	CHECK IN ('saml','oidc')
provider_name	text	NOT NULL	—	Display name
status	text	NOT NULL	'testing'	CHECK IN ('active','disabled','testing')
issuer_url	text	nullable	—	OIDC issuer / SAML IdP
metadata_url	text	nullable	—	SAML metadata XML URL
entity_id	text	nullable	—	SAML entity ID
sso_url	text	nullable	—	IdP login endpoint
client_id	text	nullable	—	OIDC client ID
client_secret_ref	text	nullable	—	Reference to secret manager — NEVER raw secret
certificate_ref	text	nullable	—	Reference — NEVER raw certificate
allowed_domains	JSONB	nullable	'[]'	Email domains permitted via this provider. Example: ["greenthumb.com"]
require_sso	boolean	NOT NULL	false	If true, users with matching domain MUST use SSO
auto_provision_users	boolean	NOT NULL	false	Create tenant_user on first successful SSO login
default_role_id	UUID	nullable	—	FK → identity.role. Role assigned to auto-provisioned users.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id) WHERE status IN ('active','testing') AND deleted_at IS NULL — v1 constraint: enforces at most one active-or-testing SSO provider per tenant. See note below.

Multi-provider design note (post-build reconciliation 2026-06-29): The locked doc anticipated multi-provider support and stated "no unique constraint on (tenant_id, provider_type)." The live DB was migrated with a partial UNIQUE on (tenant_id) filtered to active/testing status — more restrictive than documented and functionally limits each tenant to one active provider. Recommendation: keep as v1 simplification. SSO is post-v1 scope; the single-active-provider constraint avoids ambiguity in the v1 login flow and is safe to ship. Multi-provider support (e.g. SAML for staff + OIDC for contractors) requires dropping this index when that feature is built. Document the constraint here rather than silently removing it. Domain routing via allowed_domains remains valid for the multi-provider design when the index is eventually lifted.

---

identity.invitation

Pending invites to join a tenant. The raw token is never stored — only token_hash. Includes optional site_assignments so a single accept flow can provision both tenant-level and site-level access.

DEPRECATED COLUMN (2026-07-10, Header/Line Remediation fix #3): site_assignments is superseded by the new identity.invitation_site_assignment table (see below) — deprecated via column comment only, still readable/writable. Confirmed live: identity.invitation had ZERO rows at build time, so this deprecation carried zero data migration and there was nothing to backfill into the new table.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
email	text	NOT NULL	—
email_normalized	text	NOT NULL	—
role_id	UUID	NOT NULL	—	FK → identity.role. Intended tenant-level role on accept.
site_assignments	JSONB	nullable	'[]'	Intended site access. Example: [{"site_id":"uuid","role_id":"uuid"}]
token_hash	text	NOT NULL	—	Hash of invite token — NEVER raw token
status	text	NOT NULL	'pending'	CHECK IN ('pending','accepted','expired','revoked')
invited_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user
message	text	nullable	—
expires_at	timestamptz	NOT NULL	—
accepted_at	timestamptz	nullable	—
accepted_by_user_id	UUID	nullable	—	FK → identity.identity_user
revoked_at	timestamptz	nullable	—
revoked_by_user_id	UUID	nullable	—	FK → identity.identity_user
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id, status)
• on (expires_at) WHERE status = 'pending' — cleanup job sweeping expiring invitations
• UNIQUE on token_hash — globally unique cryptographic value; no soft-delete scoping
• UNIQUE on (tenant_id, email_normalized) WHERE status = 'pending' — prevents duplicate pending invitations to the same email address within a tenant
• UNIQUE on (id, tenant_id) — added 2026-07-10, Header/Line Remediation fix #3 (invitation_id_tenant_id_unique); confirmed missing before this migration; prerequisite for invitation_site_assignment's own composite FK below

---

identity.invitation_site_assignment

Pre-acceptance staging of intended site access, recorded at invite time — converts invitation.site_assignments (JSONB, deprecated in place above) into real per-row staging data. Reuses identity.user_site_assignment's own shape almost verbatim — the correctly-modeled POST-acceptance version of this exact concept. Added 2026-07-10, Header/Line Remediation fix #3, Identity's 4th reopen.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
invitation_id	UUID	NOT NULL	—	Composite FK (invitation_site_assignment_invitation_tenant_fkey) → identity.invitation (invitation_id, tenant_id) → (id, tenant_id) — no bare .references().
site_id	UUID	NOT NULL	—	Composite FK (invitation_site_assignment_site_tenant_fkey) → multi_loc.site (site_id, tenant_id) → (id, tenant_id) — real as of 2026-07-10, closing the gap this column started with at its own creation earlier the same day (PROJECT_DECISIONS #52 → #54).
site_role_id	UUID	nullable	—	FK → identity.role. NULL = use tenant-level role on accept; populated = intended site-specific role differs.
created_at	timestamptz	NOT NULL	now()	No updated_at/deleted_at — write-once staging data.

Indexes:

• PK on id
• on (tenant_id)
• on (invitation_id)
• UNIQUE on (id, tenant_id) (invitation_site_assignment_id_tenant_id_unique) — prerequisite for user_site_assignment.created_from_invitation_site_assignment_id's own composite FK (see below)

Header/Line Remediation, Fix #3 (2026-07-10) — mandatory pre-migration audits.

1. JSONB inspection: identity.invitation had ZERO live rows at build time — no real site_assignments shape existed to inspect, and there was nothing to backfill into this new table (the design's own post-backfill count-reconciliation step is trivially 0=0 here).

2. Mandatory orphan-check audit (required before deciding on an optional opportunistic FK-wiring bundle) — found exactly ONE live orphaned row in identity.user_site_assignment: a site_id value with no matching row in multi_loc.site (id 4cd177f7-6f3a-42aa-a977-6fe0b34e89e6, site_id 3587ced4-62ba-47a8-b5ba-4cb0ae43ce29). identity.tenant_user.default_site_id has zero non-null rows (zero risk there). Because of this orphan, the optional bundle the design doc suggested — wiring user_site_assignment.site_id, tenant_user.default_site_id, AND this table's own new site_id all to real multi_loc.site FKs in one pass — was deliberately NOT taken. This is flagged for a human decision (null out the orphan vs. correct the reference), not unilaterally resolved — see docs/open-items/OPEN_ITEMS.md and PROJECT_DECISIONS.md #52.

   RESOLVED the same day (2026-07-10, follow-up pass): the orphaned row was investigated — confirmed isolated dev-seed fixture junk via a DB-wide tenant-isolation scan — and DELETED, then all 3 columns named in the bundle above were wired to real composite FKs. See PROJECT_DECISIONS #54 and the Cross-Phase FKs section above.

---

identity.support_access_grant

Time-boxed authorization for a Vrida support actor to access a specific tenant. Combines authorization (the grant) with audit (who granted, when, why, ends-at, access level).

Tenant-scoped — the tenant being accessed.

RLS: enabled — tenant isolation policy on tenant_id.

POST-BUILD RECONCILIATION (2026-06-29): Column set updated to match live DB. The Drizzle schema was authored with actor-generic FKs (→ actor) and an additional access_level column absent from the locked doc. Column renames from doc: vrida_user_id → support_actor_id, granted_by_user_id → granted_by_actor_id, revoked_by_user_id → revoked_by_actor_id. FK targets widened from identity_user to actor. metadata renamed to audit_log. access_level added (NOT NULL, CHECK). correlation_id recovered to DB. Service-layer invariant: support_actor_id MUST reference an actor whose identity_user.is_platform_user = true; this is enforced by application code (not a DB constraint), consistent with DR-5 (service-layer-enforced conditional column constraints).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	Tenant being accessed. FK → platform.tenant
support_actor_id	UUID	NOT NULL	—	FK → identity.actor. Must reference an actor whose identity_user.is_platform_user = true (service-layer enforced).
granted_by_actor_id	UUID	NOT NULL	—	Who approved the grant. FK → identity.actor
reason	text	NOT NULL	—	Why access was granted
access_level	text	NOT NULL	'read_only'	CHECK IN ('read_only','standard','admin'). The scope of access granted.
starts_at	timestamptz	NOT NULL	now()
ends_at	timestamptz	NOT NULL	—	Time-boxed — REQUIRED. CHECK ends_at > starts_at.
revoked_at	timestamptz	nullable	—
revoked_by_actor_id	UUID	nullable	—	FK → identity.actor
status	text	NOT NULL	'active'	CHECK IN ('active','expired','revoked')
correlation_id	text	nullable	—	Ticket ID, incident ID, etc. Referenced when logging support_access_used events. Added in post-build reconciliation.
audit_log	JSONB	nullable	—	Grant-specific context (ticket details, incident links). Example: {"ticket_id":"SUP-1234","incident_url":"https://helpdesk.vrida.app/tickets/1234"}
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• on (support_actor_id)
• on (ends_at) WHERE status = 'active' — cron sweep that auto-expires grants

AUTHORIZATION RULE (load-bearing): A Vrida support actor may set the tenant context for a tenant ONLY IF an active, unexpired grant exists — that is, status = 'active' AND now() BETWEEN starts_at AND ends_at. Do NOT rely on status alone; always check the time window. Expiry or revoke immediately stops access on the next request. Every use is logged as a support_access_used row in identity_access_event.

---

identity.user_permission_override

Per-user permission grant or denial that overrides what the user's role would normally produce. Use rarely — prefer adjusting roles. Overrides can be scoped to the whole tenant, a single site, or a module.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
tenant_user_id	UUID	NOT NULL	—	FK → identity.tenant_user
permission_id	UUID	NOT NULL	—	FK → identity.permission
effect	text	NOT NULL	—	CHECK IN ('allow','deny'). deny overrides allow.
scope_type	text	NOT NULL	'tenant'	CHECK IN ('tenant','site','module')
scope_id	UUID	nullable	—	site_id when scope_type = 'site'. FK → multi_loc.site when applicable (FK constraint deferred — see Cross-Phase FKs)
scope_code	text	nullable	—	Module code when scope_type = 'module', e.g. 'inventory'
(table CHECK)	—	—	—	CHECK ( (scope_type = 'site' AND scope_id IS NOT NULL AND scope_code IS NULL) OR (scope_type = 'module' AND scope_code IS NOT NULL AND scope_id IS NULL) OR (scope_type = 'tenant' AND scope_id IS NULL AND scope_code IS NULL) ) — enforces that only the column matching scope_type is populated. Without this, scope_type='module' rows could silently stash a UUID in scope_id.
reason	text	nullable	—
granted_by_user_id	UUID	NOT NULL	—	FK → identity.actor
starts_at	timestamptz	NOT NULL	now()
ends_at	timestamptz	nullable	—	NULL = permanent
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id) — RLS filters on tenant_id on every query; needs a leading index
• on (tenant_user_id, permission_id)
• UNIQUE on (tenant_user_id, permission_id, scope_type) WHERE deleted_at IS NULL — prevents duplicate active overrides for the same user + permission + scope; eliminates resolution ambiguity when deny-beats-allow is evaluated

---

identity.password_policy

Vrida-wide global password policy. Single active policy (is_active = true). Governs email+password users — Google/SSO users bypass this policy. App + Supabase Auth enforce these rules; this table holds the authoritative configuration.

NOT tenant-scoped — global reference data. No tenant_id, no deleted_at.

RLS: not applied — global reference data.

Access control: readable by all authenticated users (needed at login/profile-update). Writable only via service_role and the seed migration — application code never INSERTs / UPDATEs at runtime.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
min_length	integer	NOT NULL	8	Minimum password character length
require_uppercase	boolean	NOT NULL	true	At least one uppercase letter required
require_lowercase	boolean	NOT NULL	true	At least one lowercase letter required
require_number	boolean	NOT NULL	true	At least one numeric digit required
require_symbol	boolean	NOT NULL	false	At least one special character required
mfa_required	boolean	NOT NULL	false	MFA enforced via Supabase Auth; this column flags the policy requirement for app enforcement
password_expiry_days	integer	nullable	—	NULL = no expiry. Days before a password must be changed.
history_count	integer	nullable	—	NULL = no reuse restriction. Number of previous passwords a user may not reuse.
is_active	boolean	NOT NULL	true	Exactly one row should have is_active = true at any time.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (is_active) WHERE is_active = true — singleton invariant: enforces at most one active policy row at any time; an INSERT of a second is_active = true row fails at the DB level

---

identity.scim_config

Per-tenant SCIM (directory-sync) configuration. SKELETON — schema only; the SCIM 2.0 API endpoints, provisioning/deprovisioning lifecycle, and IdP testing are deferred (see OPEN_ITEMS). Unused until SCIM is built. v1 live shape may be redesigned when SCIM is implemented — particularly around token storage (v1 stores a hashed token directly; the full implementation may prefer a secret-manager reference).

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

POST-BUILD RECONCILIATION (2026-06-29): Column set updated to match live DB. The Drizzle schema was independently authored with an operational sync-state model (hashed token, sync flags, last-sync timestamp) rather than the secret-manager-reference model in the locked doc. Since SCIM is deferred/skeleton, the live shape is the ground truth. The locked doc's bearer_token_ref / idp_type / endpoint_base_url / default_role_id / status / deleted_at columns are NOT in the live DB. The live DB has no deleted_at (no soft-delete for the SCIM config singleton), so the UNIQUE is a plain unique constraint on tenant_id, not partial.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
is_enabled	boolean	NOT NULL	false	Whether SCIM sync is active for this tenant
token_hash	text	nullable	—	Hash of the SCIM bearer token. Token value is never stored raw. When SCIM is fully implemented, consider migrating to a secret-manager reference (same pattern as sso_provider.client_secret_ref).
base_url	text	nullable	—	Base URL for the SCIM endpoint exposed to the IdP
sync_users	boolean	NOT NULL	true	Whether user provisioning/deprovisioning is enabled
sync_groups	boolean	NOT NULL	true	Whether group sync is enabled
last_sync_at	timestamptz	nullable	—	Timestamp of last successful sync run
sync_errors	JSONB	nullable	—	Most recent sync error details. Shape TBD at implementation time.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id) — one SCIM configuration per tenant (plain unique; no deleted_at on this table)

---

identity.role_assignment

Time-bounded role assignment — maps a role to an actor OR an actor group. Replaces the single tenant_user.role_id column with a full history table: each assignment is a row with a lifecycle (active / revoked), optional time bounds (starts_at / ends_at), and full attribution (who assigned, who revoked). Append-only for creation — only status is ever mutated (set to 'revoked'). No deleted_at — history is permanent; revocation is via status + revoked_at only. See DR-15.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Assignee polymorphism (OQ-B1 resolution): two nullable FK columns (actor_id, actor_group_id) + a table-level CHECK enforcing exactly one is populated. This preserves DB-level FK integrity for both target types while allowing either an actor OR an actor group to hold a role in a single unified table.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant. RLS anchor.
actor_id	UUID	nullable	—	FK → identity.actor. Exactly one of actor_id / actor_group_id must be non-NULL — see table CHECK.
actor_group_id	UUID	nullable	—	FK → identity.actor_group. Exactly one of actor_id / actor_group_id must be non-NULL — see table CHECK.
(table CHECK)	—	—	—	CHECK ((actor_id IS NOT NULL AND actor_group_id IS NULL) OR (actor_id IS NULL AND actor_group_id IS NOT NULL)) — DB-enforced assignee polymorphism. Prevents ambiguous rows where both or neither FKs are populated.
role_id	UUID	NOT NULL	—	FK → identity.role. The role being assigned.
status	text	NOT NULL	'active'	CHECK IN ('active','revoked'). Only mutable column — status transitions from active to revoked only; no other update permitted.
starts_at	timestamptz	NOT NULL	now()	Assignment effective start.
ends_at	timestamptz	nullable	—	NULL = permanent. Expiry evaluated at resolution time (no background job required); an active, unexpired assignment satisfies status = 'active' AND (ends_at IS NULL OR now() < ends_at).
assigned_by_actor_id	UUID	nullable	—	FK → identity.actor. Who created this assignment. NULL for system-provisioned assignments (SCIM, seed).
revoked_at	timestamptz	nullable	—	Set when status transitions to 'revoked'. NULL while active.
revoked_by_actor_id	UUID	nullable	—	FK → identity.actor. Who revoked this assignment. NULL while active.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Updated only when status is set to 'revoked'; otherwise immutable post-insert.

Indexes:

• PK on id
• on (tenant_id, actor_id) WHERE actor_id IS NOT NULL — "all assignments for this actor in this tenant" (hot-path at permission resolution)
• on (tenant_id, actor_group_id) WHERE actor_group_id IS NOT NULL — "all assignments for this group in this tenant" (group resolution path)
• on (role_id) — "which actors/groups hold role X" (admin UI, role-impact assessment before role edit)
• on (ends_at) WHERE ends_at IS NOT NULL AND status = 'active' — expiring-soon sweep and optional future cron that flips status to 'revoked' on expiry

---

identity.role_permission_group

Tenant-scoped wiring table — attaches a Vrida-defined permission_group bundle to a tenant's custom role. A single row means "this role includes all permissions in this bundle." Soft-delete (not append-only): a bundle-to-role attachment is a tenant configuration choice with a meaningful add/remove lifecycle. Guard: role_id must reference a role with role_type = 'tenant_custom' — system and Vrida-internal roles are bundled via seed migration only, never via this table. App-enforced. See DR-17.

Tenant-scoped — a bundle-to-role wiring belongs to the tenant that holds the custom role.

RLS: enabled — tenant isolation on tenant_id directly (the table carries tenant_id NOT NULL; no subquery needed): USING (tenant_id = current_setting('app.current_tenant_id')::uuid).

Global permission_group rows are readable by all tenants (no RLS on permission_group itself). This table is the per-tenant wiring; permission_group remains global reference data (see DR-13).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant. Required directly for RLS (SCHEMA_CONVENTIONS §10); redundant with role.tenant_id but avoids a subquery-only RLS policy.
role_id	UUID	NOT NULL	—	FK → identity.role. Must have role_type = 'tenant_custom' — app-enforced.
permission_group_id	UUID	NOT NULL	—	FK → identity.permission_group
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete. Removing a bundle from a role soft-deletes this row; re-attaching creates a new row. See DR-17.

Indexes:

• PK on id
• UNIQUE on (role_id, permission_group_id) WHERE deleted_at IS NULL — one active wiring per (role, bundle) pair; prevents duplicate attachment
• on (tenant_id) — RLS filter anchor; also serves "list all bundle wirings in tenant" admin query
• on (permission_group_id) — "which roles include this bundle" reverse lookup (admin UI, impact assessment before bundle change)

---

identity.role_template

Vrida-seeded catalog of role templates — starting-point definitions for common custom role patterns (e.g., "Floor Staff", "Seasonal Cashier", "Inventory Viewer"). A tenant "clones" a template to create a tenant_custom role; the clone materializes the template's permission_group references into role_permission rows on the new role, making the cloned role fully independent. Changes to the template after cloning do NOT propagate to cloned roles. VRIDA-DEFINED ONLY — tenants may clone templates but cannot create or modify them. See DR-16.

NOT tenant-scoped — global reference data. No tenant_id. No RLS.

Access control: readable by all authenticated users (needed for the role-creation UI). Writable only via service_role and the seed migration — application code never INSERTs / UPDATEs role_template rows at runtime.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
code	text	NOT NULL	—	UNIQUE. Machine identifier — e.g. floor_staff, seasonal_cashier, inventory_viewer.
name	text	NOT NULL	—	Display name
description	text	nullable	—
module_code	text	nullable	—	Primary module this template serves (e.g. 'pos', 'inventory'). Nullable — a template may span modules. Informational only.
is_active	boolean	NOT NULL	true	Inactive templates are hidden from the clone UI without deleting them.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on code

Clone operation: creating a custom role from a template reads role_template_permission_group rows for the template, then expands each permission_group via permission_group_permission into individual permissions, and inserts the result as role_permission rows on the new role — all in a single transaction. See DR-16.

---

identity.role_template_permission_group

Join between role_template and permission_group — defines which permission bundles are included in a template. Append-only (no updated_at, no deleted_at): same pattern as role_permission and permission_group_permission. Changes to a template's bundle set are made by deleting the old row and inserting a new one.

NOT tenant-scoped — global reference data. No tenant_id. No RLS.

Access control: readable by all authenticated users. Writable only via service_role and the seed migration.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
role_template_id	UUID	NOT NULL	—	FK → identity.role_template
permission_group_id	UUID	NOT NULL	—	FK → identity.permission_group
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (role_template_id, permission_group_id)
• on (permission_group_id) — "which templates include bundle X" reverse lookup

---

identity.actor_group

Tenant-scoped named collection of actors (team, department, or custom grouping). Groups are the DB anchor for SCIM /Groups provisioning (Batch D). Group-level role assignment — assigning a role to a group so all members inherit it — is now supported via role_assignment (Batch B Pass 1); all members of an active group inherit the group's role assignments at permission resolution time (Step 2 of the Permission Resolution Order).

Tenant-scoped, many per tenant.

RLS: enabled — tenant isolation policy on tenant_id.

Note on Postgres reserved word: group is a reserved word in SQL. This table is always referenced as identity.actor_group (schema-qualified). Drizzle declares it via pgSchema('identity').table('actor_group') — no quoting issues in ORM code; only raw SQL must schema-qualify.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
name	text	NOT NULL	—	Display name. Unique per tenant while active — see partial-unique below.
description	text	nullable	—
group_type	text	NOT NULL	'custom'	CHECK IN ('department','team','custom'). Informational — not enforced by access rules; used for admin UI organization.
status	text	NOT NULL	'active'	CHECK IN ('active','inactive'). Inactive groups retain member rows but are excluded from role-assignment resolution at service layer.
external_id	text	nullable	—	SCIM Group externalId — the IdP's identifier for this group. Unused until SCIM Batch D.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (tenant_id, name) WHERE deleted_at IS NULL — one active group per name per tenant (soft-delete pattern)
• on (tenant_id) — RLS filter and "list all groups in tenant" query
• on (tenant_id, external_id) WHERE external_id IS NOT NULL — SCIM group lookup; unused until Batch D

---

identity.actor_group_member

Join between an actor_group and an actor — the row that says "this actor is a member of this group." Accepts any actor type (human, service account, agent) via FK → identity.actor; the service layer applies any admin-UI restrictions on actor type. In the current build (pre-Batch C), actor_type = 'user' in practice.

SCIM note: when Batch D builds the SCIM /Groups/{id}/Members API, it will filter to actor_type = 'user' at the service layer when responding to IdP provisioning calls. The schema requires no change.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant. Required on the join table directly for RLS (SCHEMA_CONVENTIONS §10).
group_id	UUID	NOT NULL	—	FK → identity.actor_group
actor_id	UUID	NOT NULL	—	FK → identity.actor. Any actor type — no DB type filter.
added_at	timestamptz	NOT NULL	now()	When this membership was established. Separate from created_at to support back-dated SCIM imports where the IdP reports an earlier membership date.
added_by_actor_id	UUID	nullable	—	FK → identity.actor. Who added this member (admin action). NULL for SCIM system provisioning.
created_at	timestamptz	NOT NULL	now()	Row creation timestamp.
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete. Removing a member soft-deletes the row; re-adding the same actor creates a new row.

Indexes:

• PK on id
• UNIQUE on (group_id, actor_id) WHERE deleted_at IS NULL — one active membership per actor per group (soft-delete pattern)
• on (tenant_id) — RLS filter
• on (actor_id) — "which groups does this actor belong to?" (hot-path at permission resolution — group role resolution active from Batch B Pass 1)
• on (group_id) — "list all members of this group" (admin UI)

---

identity.permission_group

Vrida-defined bundle of permissions (e.g. "Inventory Manager Bundle," "Cashier Basic"). A permission group is a named set of permission rows that can be attached to a role as a unit. VRIDA-DEFINED ONLY — tenants read and use bundles but cannot create or modify them.

The wiring table that attaches a bundle to a role (role_permission_group) was authored in Batch B Pass 1 — see DR-13. Pass 2 shipped the bundle catalog; Batch B Pass 1 shipped the role-level wiring.

NOT tenant-scoped — global reference data. No tenant_id. No RLS.

Access control: readable by all authenticated users (needed for role-management admin UI). Writable only via service_role and the seed migration — application code never INSERTs / UPDATEs permission_group rows at runtime.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
code	text	NOT NULL	—	UNIQUE. Machine identifier — e.g. inventory_manager, cashier_basic, reporting_read_only.
name	text	NOT NULL	—	Display name
description	text	nullable	—
module_code	text	nullable	—	Primary module this bundle serves (e.g. 'inventory'). Nullable — a bundle may span modules. Informational only.
is_active	boolean	NOT NULL	true	Deactivate to hide from role-assignment UI without deleting.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on code

---

identity.permission_group_permission

Join between permission_group and permission — lists which permissions are included in each bundle. Append-only (no updated_at, no deleted_at) — same pattern as role_permission. Changes to a bundle's contents are made by deleting the old row and inserting a new one.

NOT tenant-scoped — global reference data. No tenant_id. No RLS.

Access control: readable by all authenticated users. Writable only via service_role and the seed migration.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
permission_group_id	UUID	NOT NULL	—	FK → identity.permission_group
permission_id	UUID	NOT NULL	—	FK → identity.permission
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (permission_group_id, permission_id)
• on (permission_id) — "which bundles include permission X" reverse lookup

---

identity.sod_rule

Vrida-defined SoD (Separation of Duties) rule catalog. Each rule identifies a set of permissions that no single actor should hold simultaneously (e.g., pos.sale.create + pos.sale.void). VRIDA-DEFINED ONLY — SoD rules are global; tenants waive individual violations but cannot create or modify rules. See DR-19.

NOT tenant-scoped — global reference data. No tenant_id. No RLS. No deleted_at.

Access control: readable by all authenticated users (needed for compliance UI). Writable only via service_role and the seed migration — application code never INSERTs / UPDATEs sod_rule rows at runtime.

Min-permissions guard: a rule with fewer than 2 rows in sod_rule_permission is invalid and may not be activated. is_active = true requires ≥2 permissions — validated at service layer before any is_active SET. Detection engine skips rules with <2 permissions via HAVING COUNT(*) >= 2 as a second-layer guard.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
rule_code	text	NOT NULL	—	UNIQUE machine identifier — e.g. pos.sale_create_plus_void, purchasing.po_create_plus_approve
name	text	NOT NULL	—	Display name
description	text	nullable	—
module_code	text	nullable	—	Primary module this rule governs (e.g. 'pos', 'purchasing'). Nullable — a rule may span modules. Informational; used for compliance UI module-filter.
severity	text	NOT NULL	'high'	CHECK IN ('high','medium','low'). Informs compliance reporting priority.
is_active	boolean	NOT NULL	true	Inactive rules are excluded from detection entirely. May not be set to true until ≥2 permissions exist in sod_rule_permission — service-layer enforced.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on rule_code

---

identity.sod_rule_permission

Join table that lists which permissions are combined in an SoD rule. A violation fires when an actor's effective permission set (resolved via the full 5-step Permission Resolution Order) contains all permissions in the rule's set (effective_perms ⊇ rule_perms). Append-only — same pattern as permission_group_permission. Changes to a rule's permission set are made by deleting the old row and inserting a new one.

NOT tenant-scoped — global reference data. No tenant_id. No RLS.

Access control: readable by all authenticated users. Writable only via service_role and the seed migration.

Violation semantics: detection queries the actor's full effective permission set, then evaluates HAVING COUNT(*) = (SELECT COUNT(*) FROM identity.sod_rule_permission WHERE sod_rule_id = $rule_id). A rule with <2 permissions naturally produces no results from this query even if is_active is inadvertently set to true.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
sod_rule_id	UUID	NOT NULL	—	FK → identity.sod_rule
permission_id	UUID	NOT NULL	—	FK → identity.permission
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (sod_rule_id, permission_id)
• on (permission_id) — "which rules involve permission X" reverse lookup (used when a permission is removed from a role — check if any open violations reference it)

---

identity.sod_violation

Tenant-scoped record of a detected SoD rule violation for a specific actor. At most one open violation per (tenant, actor, rule) — enforced via partial unique. After resolution or waiver, a new open row can be created if the condition is re-detected. No deleted_at — violation records are permanent audit history. See DR-19.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Waiver semantics: a violation with status = 'waived' suppresses re-detection while the waiver is active. Detection checks: if a waived violation exists for (tenant, actor, rule) AND (waiver_expires_at IS NULL OR now() < waiver_expires_at), skip creating a new open violation. An expired waiver (now() >= waiver_expires_at) means detection resumes; a new open row is created alongside the expired waiver row.

Detection trigger: hybrid — see DR-20. On-change: fired when role_assignment or user_permission_override changes for an actor; evaluates SoD for the affected actor immediately. Daily sweep: covers role-level permission catalog changes (adding/removing a permission from a role affects all role holders) and acts as a safety net for any missed on-change events.

Conditional column population: acknowledged_by_actor_id/acknowledged_at (when status = 'acknowledged') and waiver_reason (when status = 'waived') are enforced at service layer only — no DB CHECK by design, per DR-5 pattern (support_access_grant).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
actor_id	UUID	NOT NULL	—	FK → identity.actor. The actor whose effective permissions triggered the violation.
sod_rule_id	UUID	NOT NULL	—	FK → identity.sod_rule. The rule that was violated.
first_detected_at	timestamptz	NOT NULL	now()	When the violation was first created (this open row).
last_detected_at	timestamptz	NOT NULL	now()	Updated by the detection engine on each re-detection sweep confirming the violation is still active.
status	text	NOT NULL	'open'	CHECK IN ('open','acknowledged','waived','resolved'). Transitions: open → acknowledged / waived / resolved; acknowledged → waived / resolved.
acknowledged_by_actor_id	UUID	nullable	—	FK → identity.actor. Who acknowledged the risk.
acknowledged_at	timestamptz	nullable	—
resolved_by_actor_id	UUID	nullable	—	FK → identity.actor. Who resolved or confirmed resolution.
resolved_at	timestamptz	nullable	—
resolution_note	text	nullable	—	Explanation of how the violation was resolved.
waiver_reason	text	nullable	—	Required when status = 'waived'. Documents the business rationale for accepting the risk.
waiver_expires_at	timestamptz	nullable	—	NULL = permanent waiver. Populated = waiver expires at this timestamp; detection resumes after expiry.
decision_snapshot	JSONB	nullable	—	Added 2026-07-06 — Autonomy-First Backfill (PROJECT_DECISIONS #19). Decision-provenance carrier for this violation (the decision_provenance canonical piece, table-specific-named per existing precedent). Exact key shape is deliberately deferred to whoever next builds SoD detection's next iteration; intent is to capture "permission-set/role-combination active at detection time."
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (tenant_id, actor_id, sod_rule_id) WHERE status = 'open' — at most one open violation per (tenant, actor, rule); allows a new open row after the previous is resolved or waived
• on (tenant_id, status) — compliance dashboard listing violations by status
• on (actor_id) — "all violations for this actor" actor-detail view
• on (sod_rule_id) — "all violations of this rule" rule-impact view; also serves waiver-check query during detection

---

identity.identity_session

Global session record for an authenticated actor. Captures device context, lifetime, and end reason. Three uses: (1) log-out-all-devices UI (list and revoke active sessions); (2) audit correlation (identity_access_event.session_id, a loose reference — not an enforced FK, corrected 2026-07-18 — links all events in a session); (3) session anomaly detection (unusual IP/device patterns). GLOBAL — no tenant_id. No deleted_at — sessions are permanent audit history, ended via ended_at, never hard-deleted, by design. See DR-21.

NOT tenant-scoped — global session record (a user session spans the authentication context, not any one tenant).

RLS: not applied — global session record. Same pattern as identity_user.

Access control: an actor may read only their own session rows. Vrida platform staff access via support_access_grant. Admin operations (log-out-all-devices, expiry sweep) use service_role.

AI Capability Plane (confirmed at Batch B Pass 2 lock): Session anomaly detection (use case 3) is service-layer only — the AI reads identity_session and identity_access_event rows and emits signals; no AI state columns are required on this table. No schema additions needed for the AI plane.

Conditional column population: end_reason (when ended_at IS NOT NULL) is enforced at service layer only — no DB CHECK by design, per DR-5 pattern (support_access_grant).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
actor_id	UUID	NOT NULL	—	FK → identity.actor
supabase_session_id	UUID	nullable	—	Reference to Supabase Auth session. Not an enforced FK — DR-1 pattern; Supabase controls auth.sessions. Nullable: platform-initiated sessions (admin, SCIM) have no Supabase session.
ip_address	inet	nullable	—	Client IP at session start.
user_agent	text	nullable	—	HTTP User-Agent string.
device_fingerprint	text	nullable	—	Opaque device identifier derived from headers/browser fingerprint. NULL if unavailable.
started_at	timestamptz	NOT NULL	now()	Session creation time.
last_active_at	timestamptz	NOT NULL	now()	Updated on each authenticated request. Used for idle-timeout detection.
ended_at	timestamptz	nullable	—	NULL = session is active. Populated when the session ends by any means.
end_reason	text	nullable	—	NULL while active. CHECK IN ('logout','expired','admin_revoked','all_devices_revoked'). Populated when ended_at is set.
revoked_by_actor_id	UUID	nullable	—	FK → identity.actor. Who revoked this session. NULL for natural expiry or self-logout.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (actor_id) WHERE ended_at IS NULL — "active sessions for this actor" (log-out-all-devices UI; concurrent-session count)
• on (actor_id, started_at) — session history for actor (full timeline view)
• on (supabase_session_id) WHERE supabase_session_id IS NOT NULL — Supabase Auth webhook → Vrida session lookup
• on (last_active_at) WHERE ended_at IS NULL — idle-timeout expiry sweep (locates sessions inactive beyond tenant_security_policy.session_timeout_minutes, Batch D)

Session correlation with identity_access_event: identity_access_event.session_id is a nullable, unenforced (not a real FK, no supporting index — corrected 2026-07-18, see DR-21) loose reference to identity_session. All events written during a session carry the session UUID; pre-session and platform-level events have session_id = NULL. This enables the forensic query "all events in session S" via a direct UUID join, when the referenced session row still exists.

---

identity.access_request

Tenant-scoped request for a role assignment or permission override. An actor submits a request with justification; an approver reviews and approves or denies. On approval, service layer creates the corresponding role_assignment or user_permission_override row. Permanent record — no deleted_at. See DR-22.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Single-approver flow (v1): approver_actor_id is the designated reviewer; reviewed_by_actor_id captures who actually reviewed (may differ if the assigned approver delegates). No multi-step workflow in v1 — see DR-22 for the Batch D workflow engine attachment point.

Approval seam: when status → 'approved':

• request_type = 'role' → service layer INSERTs role_assignment (actor_id=requester_actor_id, role_id=requested_role_id, starts_at=starts_at OR now(), ends_at=ends_at, assigned_by_actor_id=reviewed_by_actor_id, tenant_id=tenant_id)
• request_type = 'permission_override' → service layer INSERTs user_permission_override (tenant_user_id=lookup(requester_actor_id, tenant_id), permission_id=requested_permission_id, effect='allow', scope_type=requested_scope_type, scope_id=requested_scope_id, starts_at=starts_at, ends_at=ends_at, granted_by_user_id=reviewed_by_actor_id) In both cases: identity_access_event row written (role_changed or permission_override_applied).

Access_request lifecycle events: service layer writes one identity_access_event row at each status transition: access_request_submitted (on create), access_request_approved / access_request_denied (on review), access_request_withdrawn (on requester withdrawal), access_request_expired (on expiry sweep). These 5 event types are included in identity_access_event.event_type CHECK as of Batch B Pass 2.

Conditional column population: reviewed_by_actor_id/reviewed_at (when status = 'approved' or 'denied') are enforced at service layer only — no DB CHECK by design, per DR-5 pattern (support_access_grant).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
requester_actor_id	UUID	NOT NULL	—	FK → identity.actor. The actor making the request.
request_type	text	NOT NULL	—	CHECK IN ('role','permission_override') — see request_type CHECK below.
requested_role_id	UUID	nullable	—	FK → identity.role. Required when request_type = 'role' — see request_type CHECK.
requested_permission_id	UUID	nullable	—	FK → identity.permission. Required when request_type = 'permission_override' — see request_type CHECK.
requested_scope_type	text	nullable	—	CHECK IN ('tenant','site','module'). Scope for permission_override requests. NULL for role requests.
requested_scope_id	UUID	nullable	—	Site UUID when requested_scope_type = 'site'. Required for site-scoped overrides (needed by the approval seam to create the user_permission_override row with the correct scope_id) — see scope CHECK. NULL otherwise. FK → multi_loc.site (FK constraint deferred — see Cross-Phase FKs).
justification	text	NOT NULL	—	Required business justification from requester.
status	text	NOT NULL	'pending'	CHECK IN ('pending','approved','denied','withdrawn','expired'). Pending requests past expires_at are treated as 'expired' at query time: (status = 'pending' AND (expires_at IS NULL OR now() < expires_at)) — same on-read pattern as role_assignment.ends_at.
approver_actor_id	UUID	nullable	—	FK → identity.actor. Designated approver.
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor. Who actually performed the review (may differ from approver_actor_id).
reviewed_at	timestamptz	nullable	—
review_note	text	nullable	—	Approval or denial note from reviewer.
expires_at	timestamptz	nullable	—	NULL = no expiry. A pending request past expires_at is treated as expired.
starts_at	timestamptz	nullable	—	Desired start for the resulting assignment or override. NULL = effective immediately on approval.
ends_at	timestamptz	nullable	—	Desired end for the resulting assignment or override. NULL = permanent. Passed through to role_assignment.ends_at or user_permission_override.ends_at on approval.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
(request_type CHECK)	—	—	—	CHECK ((request_type = 'role' AND requested_role_id IS NOT NULL) OR (request_type = 'permission_override' AND requested_permission_id IS NOT NULL)) — enforces FK consistency: role requests must name a role; permission_override requests must name a permission.
(scope CHECK)	—	—	—	CHECK (requested_scope_type IS NULL OR requested_scope_type = 'tenant' OR (requested_scope_type = 'site' AND requested_scope_id IS NOT NULL) OR requested_scope_type = 'module') — enforces that requested_scope_id is populated for site-scoped requests (the approval seam needs the site UUID to create the correctly-scoped user_permission_override).

Indexes:

• PK on id
• on (tenant_id) — RLS filter anchor and "all requests in tenant X" admin query (all statuses — approved, denied, historical)
• on (tenant_id, status) WHERE status = 'pending' — "all pending requests in this tenant" (admin overview hot-path)
• on (requester_actor_id) — "what have I requested" (actor self-view)
• on (approver_actor_id) WHERE approver_actor_id IS NOT NULL AND status = 'pending' — "what is waiting for my approval" (approver inbox — hot-path)
• on (expires_at) WHERE expires_at IS NOT NULL AND status = 'pending' — expiry sweep

Post-v1 workflow engine attachment point (see DR-22; deferred from Batch D — see completeness table): approval_workflow and approval_step are built when the first multi-step approval use case arrives. access_request gains one nullable FK column (workflow_id UUID nullable FK → approval_workflow) when built — no structural change to access_request needed now. When workflow_id IS NULL: single-approver v1 flow (use approver_actor_id directly). When workflow_id IS NOT NULL: multi-step workflow engine manages the approval path, ultimately writing the same status transitions on this table.

---

identity.agent_type_catalog

Vrida-defined catalog of AI agent types. Each row defines a named type of agent Vrida ships (e.g., "Inventory Reconciliation", "Financial Reporting"). agent_identity.agent_type_id references this table. VRIDA-DEFINED ONLY — tenant operators may provision agent instances of existing types but cannot create new types. New types are added by Vrida via the seed migration; this table avoids CHECK-constraint schema migrations each time a new agent type is introduced.

NOT tenant-scoped — global reference data. No tenant_id. No deleted_at.

RLS: not applied — global reference data.

Access control: readable by all authenticated users (needed for agent provisioning UI). Writable only via service_role and the seed migration — application code never INSERTs / UPDATEs rows at runtime.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
code	text	NOT NULL	—	UNIQUE. Machine identifier. Namespace pattern: <module>.<verb> — e.g. inventory.reconcile, reporting.financial_summary, pricing.suggest. Stable — service layer and seed data reference agent types by code.
name	text	NOT NULL	—	Display name (e.g., "Inventory Reconciliation Agent")
description	text	nullable	—	What agents of this type do
default_config	JSONB	nullable	—	Per-type default configuration. Agent instances may override specific keys via agent_identity.config. Example: {"temperature": 0.3, "max_tokens": 4096, "enabled_tools": ["read_inventory", "write_reconciliation_result"]}
is_active	boolean	NOT NULL	true	Inactive types are hidden from provisioning UI; existing agent instances unaffected.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on code

---

identity.service_account

Machine-identity detail table for integration clients, API consumers, webhook receivers, and automated jobs. Extends identity.actor via shared PK (class table inheritance): service_account.id IS actor.id. Tenant-scoped — each service account belongs to exactly one tenant. Role assignment, permission resolution, and audit attribution all use the actor root; service_account adds machine-identity attributes. Lifecycle state (active / suspended / deactivated) lives on actor.status. See DR-23.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id. The actor root remains global / no-RLS; queries join actor + service_account, and this table's RLS filters by tenant.

Access control: readable within tenant context via RLS. All writes via service_role or application provisioning code; never directly by client-side code.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	—	PK and FK → identity.actor (shared PK / class table inheritance — no gen_random_uuid() default; UUID is generated on actor and passed in)
tenant_id	UUID	NOT NULL	—	FK → platform.tenant. Tenant this service account belongs to.
name	text	NOT NULL	—	Human-readable label (e.g., "QuickBooks Integration", "Nightly Inventory Sweep")
description	text	nullable	—	Purpose description
client_id	text	NOT NULL	—	UNIQUE globally. Public OAuth2-style client identifier — system-generated with vsvc_ prefix (e.g., vsvc_a1b2c3d4). Used with api_key.key_hash for credential verification. Never manually set by tenant admin. See DR-23.
created_by_actor_id	UUID	nullable	—	FK → identity.actor. Who provisioned this service account. NULL for system-provisioned accounts.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id) — RLS anchor; "list all service accounts in tenant" query
• UNIQUE on (client_id) — globally unique; auth fast-path lookup
• UNIQUE on (tenant_id, name) WHERE deleted_at IS NULL — no duplicate names within tenant (soft-delete pattern)

---

identity.api_key

Credential for a service account to authenticate against Vrida's API. Each service account may hold multiple keys (rotation, purpose separation). The raw key is shown to the caller exactly once on creation and never stored — only key_hash (hashed) and key_prefix (first chars, for display/lookup) are persisted. API keys are for service accounts only — agents operate via internal service-layer calls, not external API credentials. See DR-23.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Hash/rotation pattern: On creation, the service layer generates a random raw key (format: vrk_<random>), returns it to the caller ONCE, stores only key_hash (bcrypt or SHA-256) and key_prefix (first 12 chars). If the key is lost, revoke and generate a new key — no recovery path. Auto-rotation (expiry + auto-renewal) deferred to Batch D (tenant_security_policy). See DR-23.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK — own PK (not shared; api_key has its own lifecycle independent of actor)
tenant_id	UUID	NOT NULL	—	FK → platform.tenant. RLS anchor.
service_account_id	UUID	NOT NULL	—	FK → identity.service_account. The service account this key authenticates.
key_hash	text	NOT NULL	—	Hash of the raw key (bcrypt / SHA-256). Same pattern as invitation.token_hash — plaintext never stored or logged.
key_prefix	text	NOT NULL	—	First 12 chars of raw key (e.g., vrk_a1b2c3d4ef56). Stored plaintext for display ("which key am I revoking?") and auth fast-path candidate narrowing.
name	text	NOT NULL	—	Human label (e.g., "Production webhook key", "QuickBooks connector key")
scopes	text[]	nullable	—	Optional scope restrictions. e.g., {'read:inventory','write:pos'}. Application-enforced — no scope registry yet; text[] sufficient at SMB scale.
last_used_at	timestamptz	nullable	—	Updated on each successful auth. Used for "unused key" cleanup sweep.
expires_at	timestamptz	nullable	—	NULL = no expiry. Service layer sweeps expires_at < now() AND status = 'active' and sets status to 'expired'.
revoked_at	timestamptz	nullable	—	Set on explicit revocation. NULL while active.
status	text	NOT NULL	'active'	CHECK IN ('active','revoked','expired')
created_by_actor_id	UUID	nullable	—	FK → identity.actor. Who created this key.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete (retains audit trail even after revocation)

Indexes:

• PK on id
• on (tenant_id) — RLS anchor
• on (service_account_id) — "all keys for this service account"
• on (key_prefix) — auth fast-path: client sends prefix → service layer finds candidate rows by prefix, then verifies hash
• on (tenant_id, status) WHERE status = 'active' — "all active keys in tenant" management view
• UNIQUE on (key_hash) — global, no WHERE clause. Revoked/deleted keys permanently consume their hash slot — same pattern as invitation.token_hash. Prevents hash-collision ambiguity at auth verification.
• on (expires_at) WHERE expires_at IS NOT NULL AND status = 'active' — expiry sweep: service layer sets status = 'expired' on keys past their expires_at. Same pattern as support_access_grant.ends_at and role_assignment.ends_at sweep indexes.

---

identity.agent_identity

AI agent detail table. Extends identity.actor via shared PK (class table inheritance): agent_identity.id IS actor.id. Tenant-scoped — each agent instance belongs to exactly one tenant. Agents receive roles via role_assignment (same mechanism as human users) and have skills via agents.agent_skill_assignment (relocated from this schema's own agent_skill_assignment, dropped 2026-07-17 — see the "Phase 6" subsection below). No mandatory human owner — agents act independently (organizational ownership, not personal). See DR-24.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id. The actor root remains global / no-RLS; queries join actor + agent_identity, and this table's RLS filters by tenant.

Access control: readable within tenant context via RLS. All writes via service_role or application provisioning code.

AI Capability Plane (confirmed at Batch C authoring): agent_identity is the identity record — the AI reads it to understand which agent is acting. No AI state columns are required on this table. Agent execution history (per-run inputs/outputs/token cost) belongs in the ai schema alongside ai_request — it is an operational concern, not an identity concern. No schema additions needed here for the AI plane.

Kill-switch (added Remediation Phase 3, 2026-07-08 — PROJECT_DECISIONS #39): status/suspended_at/suspended_by_actor_id/suspension_reason are the agent-level link in a precedence chain evaluated before any agent action is permitted to proceed — see the "Remediation Phase 3" subsection below for the full chain and its ordering.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	—	PK and FK → identity.actor (shared PK / class table inheritance — no gen_random_uuid() default; UUID is generated on actor and passed in)
tenant_id	UUID	NOT NULL	—	FK → platform.tenant. Tenant this agent belongs to.
name	text	NOT NULL	—	Human-readable agent instance label
agent_type_id	UUID	NOT NULL	—	FK → identity.agent_type_catalog. Defines what kind of agent this is. Lookup table (not CHECK) — adding new agent types is a seed-data operation, not a schema migration. See DR-24.
model	text	nullable	—	LLM model reference (e.g., "claude-opus-4-8"). Informational only — not enforced. Nullable: some agents may be rule-based or model is configured elsewhere.
framework	text	nullable	—	Agent framework (informational — e.g., "claude-code", "custom")
version	text	nullable	—	Agent version string (semver) for rollback/audit attribution
config	JSONB	nullable	—	Sparse per-instance configuration overrides on agent_type_catalog.default_config. Example: {"max_tokens": 8192, "enabled_tools": ["read_inventory"]}. Full resolved config = merge(type.default_config, instance.config). Never a bare JSONB — document shape at implementation time.
created_by_actor_id	UUID	nullable	—	FK → identity.actor. Who deployed this agent instance. NULL for Vrida system-provisioned agents. Optional per DR-24 (no mandatory owner) — retained for audit traceability.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
status	text	NOT NULL	'active'	Added Remediation Phase 3. CHECK chk_agent_identity_status IN ('active','suspended','killed'). The agent kill-switch. 'killed' is treated as terminal by the service layer only — no trigger blocks a killed → active transition; that judgment is deliberately left to IdentityService, not baked into schema.
suspended_at	timestamptz	nullable	—	Added Remediation Phase 3. Set iff status != 'active' — see chk_agent_identity_status_suspended_at_consistency below.
suspended_by_actor_id	UUID	nullable	—	Added Remediation Phase 3. FK → identity.actor. Independently nullable even when status != 'active': a system-initiated suspension (e.g. cascading from a tenant-level or credit-account-level shutoff) may have no specific human actor to attribute.
suspension_reason	text	nullable	—	Added Remediation Phase 3. Free-text rationale for the suspension/kill.
(table CHECK)	—	—	—	chk_agent_identity_status_suspended_at_consistency — Added Remediation Phase 3. CHECK ((status = 'active' AND suspended_at IS NULL) OR (status IN ('suspended','killed') AND suspended_at IS NOT NULL)) — suspended_at is present if and only if status is not 'active'.

Indexes:

• PK on id
• on (tenant_id) — RLS anchor
• on (tenant_id, agent_type_id) — "all agents of type X in this tenant" (provisioning view, compliance audit)

No uniqueness on name by design — agents are identified by PK (actor.id); duplicate display names are allowed (a tenant may run multiple instances of the same agent type with the same label, e.g., two "Inventory Reconciliation Agent" instances for different sites). Name serves as a human-readable label, not a unique identifier.

---

identity.operator

Isolated Vrida-operator (cross-tenant SaaS staff) identity — extends identity.actor via shared PK (class table inheritance): operator.id IS actor.id, with actor_type = 'operator'. Built 2026-07-09 (PROJECT_DECISIONS #45), replacing the prior identity_user.is_platform_user=true boolean overlay. Never touched by IdentityService.provisionUser() (that method always creates a tenant_user row too, wrong for an operator — not a member of any single tenant); creation is backend-only (create-admin-user.ts for dev, OperatorService.createOperator() recommended but not built this pass for production — see OPEN_ITEMS.md).

NOT tenant-scoped — cross-tenant global identity, structurally distinct from identity_user (a tenant employee).

RLS: enabled, zero policies (deny-all). Not the usual "tenant-isolation policy" shape — there is no tenant to isolate by. Belt-and-suspenders security backstop: an explicit REVOKE SELECT, INSERT, UPDATE, DELETE ON identity.operator, identity.operator_role_assignment FROM authenticated (belt — closes the automatic ALTER DEFAULT PRIVILEGES grant every new table in this schema inherits, per Remediation Phase 1) plus RLS-enabled-with-no-policy (suspenders — Postgres denies all rows to any non-superuser/non-owner role when RLS is enabled and no policy applies, independently blocking authenticated even if a future change accidentally re-GRANTs table-level access). Both layers live-reproduced at build time (SELECT and INSERT both confirmed blocked for the authenticated role).

Access control: all reads/writes go through getAdminDb() (superuser, bypasses both the REVOKE and RLS) — never tenantDB()/authenticated. AdminAuthGuard resolves the signed-in operator by supabase_auth_user_id on every admin-console request.

Kill-switch (mirrors agent_identity's Remediation Phase 3 precedent): status/suspended_at/suspended_by_actor_id/suspension_reason. AdminAuthGuard returns 401 if no operator row exists for the auth user at all (genuine non-operator), 403 if a real operator row exists but status != 'active' (suspended/deactivated).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	—	PK and FK → identity.actor (shared PK / class table inheritance)
supabase_auth_user_id	UUID	NOT NULL	—	Seam to Supabase auth.users (never an enforced FK). Uniqueness enforced as partial unique index.
email	text	NOT NULL	—
email_normalized	text	NOT NULL	—	Lowercased / trimmed, used for uniqueness
full_name	text	nullable	—
created_by_actor_id	UUID	nullable	—	FK → identity.actor. Nullable — the bootstrap operator has no creator.
status	text	NOT NULL	'active'	CHECK chk_operator_status IN ('active','suspended','deactivated') — own local lifecycle, not reused from actor.status (mirrors agent_identity's precedent).
suspended_at	timestamptz	nullable	—	Present iff status != 'active' — see chk_operator_status_suspended_at_consistency.
suspended_by_actor_id	UUID	nullable	—	FK → identity.actor. Independently nullable — a system-initiated suspension may have no specific actor to attribute.
suspension_reason	text	nullable	—	Free-text rationale.
last_login_at	timestamptz	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
(table CHECK)	—	—	—	chk_operator_status_suspended_at_consistency — CHECK ((status = 'active' AND suspended_at IS NULL) OR (status IN ('suspended','deactivated') AND suspended_at IS NOT NULL))

Indexes:

• PK on id
• UNIQUE on (supabase_auth_user_id) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)
• UNIQUE on (email_normalized) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)
• on (status) WHERE status != 'active' — kill-switch sweep (find suspended/deactivated operators)

No MFA column (Supabase Auth owns MFA state). No password column (Supabase owns credentials).

---

identity.operator_role_assignment

Revocable role grant for an operator — mirrors identity.role_assignment's established shape (status/assigned_at/assigned_by_actor_id/revoked_at/revoked_by_actor_id). A fixed, small role list (role_code CHECK, not a catalog table) since operator roles are a Vrida-internal, rarely-changing taxonomy, unlike tenant-facing identity.role.

NOT tenant-scoped.

RLS: enabled, zero policies (deny-all) — same belt-and-suspenders backstop as identity.operator (see above).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
operator_id	UUID	NOT NULL	—	FK → identity.operator
role_code	text	NOT NULL	—	CHECK IN ('super_admin','admin','support') — a deliberately minimal, grounded list (design-phase adversarial verification cut 3 invented values — billing_ops/sales_ops/read_only — the last of which collided with a differently-scoped existing tenant-facing role of the same name in identity.role). Widen later against a real, described need.
status	text	NOT NULL	'active'	CHECK IN ('active','revoked')
assigned_at	timestamptz	NOT NULL	now()
assigned_by_actor_id	UUID	nullable	—	FK → identity.actor. Nullable — the bootstrap assignment has no prior assigner.
revoked_at	timestamptz	nullable	—
revoked_by_actor_id	UUID	nullable	—	FK → identity.actor
(table CHECK)	—	—	—	chk_operator_role_assignment_no_self_issue — CHECK (assigned_by_actor_id IS NULL OR assigned_by_actor_id != operator_id) — NULL-safe self-issue guard (mirrors agent_duty_grant's stricter precedent, but allows NULL for the bootstrap case where no prior assigner exists). Live-reproduced: self-issue rejected, distinct-assigner succeeds, NULL-bootstrap succeeds.

Indexes:

• PK on id
• on (operator_id) — "list this operator's role assignments"
• UNIQUE on (operator_id, role_code) WHERE status = 'active' — at most one active grant of a given role per operator; re-grant after revoke is permitted (partial unique, soft-revoke pattern)

---

Removed: identity.agent_skill and identity.agent_skill_assignment (Phase 6, 2026-07-17 reopen)

identity.agent_skill (9 cols) and identity.agent_skill_assignment (10 cols) — 2 tables / 19 cols — DROPPED entirely (migration 20260717000000_identity_reopen_drop_legacy_agent_skill.sql; pre-migration audit found agent_skill_assignment had 0 live rows and agent_skill's 13 live rows were all test debris — test.skill.%/ti3.skill.% patterns — cleaned up in the migration, not real data). Superseded by agents.skill_definition, agents.skill_version, and agents.agent_skill_assignment (built Phase 5 of the agents-v2/v3 build). Skill assignment is now at SKILL VERSION granularity (agents.agent_skill_assignment.skill_version_id), not skill-identity granularity. See docs/database/schema_docs/agents.md for the current, full column-level definition of all 3 tables, and PROJECT_DECISIONS.md #67 for the full narrative.

---

identity.agent_duty_grant

The A5 agent-authority passport (built 2026-07-06, PROJECT_DECISIONS.md #22). Per (agent_identity, permission) authority grant: how autonomously this agent may exercise a permission it already holds via role_assignment → role_permission, and within what spend/quantity ceiling. Additive to permission resolution — never overrides its allow/deny; only constrains how freely an already-allowed action may be exercised. Absence of a row for a given (agent, permission) = 'never' (no elevated autonomy) — the same "no row = no capability" pattern agent_skill_assignment already uses.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Complements, does not conflict with, role.requires_approval_for_agents + assignRole()'s gate (FIX2, DR-30, PROJECT_DECISIONS #21): that gate governs whether an agent may come to HOLD a role at all (grant-time gate); this table governs how autonomously it may exercise a permission once held (execution-time governance record). Sequential, not competing.

First identity table to wire the shared platform.set_updated_at() trigger (cross-schema call, precedented by multi_loc.site). Every other identity table's updated_at is set once at insert and never auto-maintained on UPDATE — a pre-existing, undocumented gap distinct from shared's own documented/accepted equivalent; logged to OPEN_ITEMS.md, not retrofitted onto existing tables here.

Two bugs caught by independent adversarial verification during design, before this table was built — see DR-31/DR-32 below.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant. RLS anchor.
agent_identity_id	UUID	NOT NULL	—	FK → identity.agent_identity.
permission_id	UUID	NOT NULL	—	FK → identity.permission. The specific action/tool this duty governs.
authority_level	text	NOT NULL	—	CHECK IN (may_act_alone,draft_only,needs_approval). 'never' = no row (not a stored value).
spend_limit_cents	bigint	nullable	—	Per-action dollar ceiling. NULL = no dollar limit. Security limitation, logged to OPEN_ITEMS: per-action only, no cumulative/period tracking — does not protect against volume-based abuse (many small actions under the ceiling).
spend_limit_currency_code	char(3)	NOT NULL	'USD'	FK → shared.currency.iso_code. Meaningful only when spend_limit_cents is set.
quantity_limit	integer	nullable	—	Per-action quantity ceiling. NULL = no quantity limit.
scope_type	text	NOT NULL	'tenant'	CHECK IN (tenant,site,module) — mirrors user_permission_override's scope pattern.
scope_id	UUID	nullable	—	FK → multi_loc.site. Site UUID when scope_type='site'. A real, enforced FK — unlike user_permission_override.scope_id's deferred FK (which predates multi_loc being locked), this column was created after multi_loc locked, so no deferral was needed (DR-32).
scope_code	text	nullable	—	Module code when scope_type='module'.
status	text	NOT NULL	'active'	CHECK IN (active,suspended,revoked) — mirrors user_site_assignment's 3-state pattern (not role_assignment's simpler 2-state): temporary pause without full revoke/re-grant is a realistic agent-governance operation. Grant/revoke lifecycle, not soft-delete.
starts_at	timestamptz	NOT NULL	now()	Mirrors role_assignment.
ends_at	timestamptz	nullable	—	Time-bounded elevated authority. Checked on-read only, no expiry sweep — same accepted pattern as role_assignment.ends_at.
granted_by_actor_id	UUID	nullable	—	FK → identity.actor. The delegating human. Mirrors role_assignment.assigned_by_actor_id.
revoked_at	timestamptz	nullable	—
revoked_by_actor_id	UUID	nullable	—	FK → identity.actor.
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed) — canonical pattern, PROJECT_DECISIONS.md #19.
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected) — same shape as shared.plant's review seam. Service layer decides when to require review (e.g. a large spend_limit_cents) — schema doesn't hardcode a threshold.
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor.
reviewed_at	timestamptz	nullable	—
decision_provenance	JSONB	nullable	—	Why this authority/limit was set. Example: {"reason": "matches tenant's Reorder Agent policy", "confidence": 0.92, "policy_ref": "reorder-agent-v1", "memory_refs": [], "delegated_by_actor_id": null}.
created_at / updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id) — RLS anchor
• on (agent_identity_id) WHERE status = 'active' — hot-path: "this agent's active duties," checked at every dispatch-time authority lookup
• on (permission_id) — "which agents/duties reference this permission," admin/audit view
• UNIQUE on (agent_identity_id, permission_id, scope_type) WHERE status = 'active' — one active duty per (agent, permission, scope_type). Deliberately EXCLUDES scope_id/scope_code — mirrors user_permission_override_unique's real shape exactly (DR-31). Including nullable scope_id/scope_code in the key (the original design's mistake, caught by independent verification before build) would let Postgres treat NULL as distinct, silently allowing two active tenant-scoped grants for the same (agent, permission) to coexist.
• on (review_status) WHERE review_status = 'pending' — review-queue support, mirrors shared.plant's precedent
• on (ends_at) WHERE ends_at IS NOT NULL AND status = 'active' — expiry sweep support (no sweep built yet)

DR-31 (unique-index NULL-uniqueness bug, caught pre-build): the original proposal's unique index included scope_id/scope_code, claiming to mirror user_permission_override_unique. Independent adversarial verification (two separate reviewers, one reproducing it empirically in Postgres) found this false and the index broken: two scope_type='tenant' grants for the same (agent, permission) both have scope_id IS NULL/scope_code IS NULL, and Postgres treats NULL as distinct in unique indexes, so both would insert successfully — defeating the one-active-grant invariant. The real user_permission_override_unique shape (assignment.ts — (tenant_user_id, permission_id, scope_type) WHERE deleted_at IS NULL) deliberately excludes those columns. Fixed before migration was written; regression-tested (identity-agent-duty.spec.ts D1).

DR-32 (deferred-FK reconsideration, caught pre-build): the original proposal deferred scope_id's FK to multi_loc.site, citing user_permission_override.scope_id's precedent. Independent verification noted that precedent's justification (multi_loc not yet locked) no longer holds — multi_loc is locked (module #4) — so there is no reason to defer. Fixed: scope_id is a real, enforced FK.

---

identity.tenant_security_policy

Per-tenant security configuration overlay. A singleton row per tenant (enforced by UNIQUE (tenant_id)) that adds per-tenant security knobs on top of the Vrida-wide password_policy. Tenants without a row inherit all Vrida platform defaults. Tenant admins write this row via the Admin UI; Vrida support can override via service_role. See DR-27 (knob scope) and DR-28 (mfa_required reconciliation). Batch D.

Tenant-scoped singleton. One row per tenant. tenant_id is UNIQUE (no WHERE clause — no soft-delete; the singleton invariant is a plain UNIQUE constraint, not partial). RLS: enabled — tenant isolation policy on tenant_id. No deleted_at (configuration record — to reset, UPDATE the row, not delete it).

Relationship to password_policy: password_policy is the Vrida-wide global floor (see DR-7). tenant_security_policy is the per-tenant overlay — it does NOT replace password_policy. Tenants without a security policy row inherit all platform defaults. mfa_required reconciliation: effective = password_policy.mfa_required OR COALESCE(tenant_security_policy.mfa_required, false) — a tenant raises the MFA floor, never lowers it (see DR-28).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant. UNIQUE — singleton: one security policy per tenant.
session_timeout_minutes	integer	nullable	—	NULL = Vrida platform default (defined as service-layer constant). Idle timeout in minutes; feeds the identity_session.last_active_at expiry sweep already indexed for this purpose. Minimum enforced by service layer (e.g., 5-minute floor).
mfa_required	boolean	nullable	—	NULL = inherit global password_policy.mfa_required. TRUE = this tenant forces MFA for all its users regardless of global setting. Effective MFA = password_policy.mfa_required OR COALESCE(this, false) — tenant raises floor, never lowers it. See DR-28.
max_concurrent_sessions	integer	nullable	—	NULL = no per-tenant limit. Maximum number of active identity_session rows per actor at any time. Must be > 0 when set (0 would lock out all users); minimum enforced by service layer. Service layer enforces at session creation — either rejects the new session or revokes the oldest active session (policy TBD at IdentityService build).
api_key_rotation_days	integer	nullable	—	NULL = no forced rotation (preferred over setting 0). Must be > 0 when set; 0 is not a valid set value (semantically ambiguous and effectively useless); minimum enforced by service layer. When set, the service layer auto-expires api_key rows where created_at < now() - interval '? days' via background sweep. api_key.expires_at already supports expiry; this column drives the enforcement schedule. Closes the Batch C api_key auto-rotation deferral (OPEN_ITEMS DR-23).
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (tenant_id) — singleton invariant; inserting a second row for the same tenant fails at the DB level

Deferred enterprise knobs (not in v1 schema — logged to OPEN_ITEMS; see DR-27):

• ip_allowlist cidr[] — restrict logins to specific IP ranges / CIDR subnets
• require_reauth_for_sensitive boolean — force re-authentication for high-sensitivity actions
• max_session_duration_minutes integer — absolute wall-clock session ceiling regardless of activity
• password_expiry_days_override integer — per-tenant override of password_policy.password_expiry_days

---

Identity — Design Notes

Permission Resolution Order

For a (actor, permission) pair, resolve in this order:

1. Direct role assignments: collect all role_assignment rows for the actor where status = 'active' AND (ends_at IS NULL OR now() < ends_at). Additionally, when the actor is acting at a specific site and user_site_assignment.site_role_id IS NOT NULL, include that role directly in the set of roles to expand in Step 3 — site_role_id is a direct FK → identity.role and bypasses role_assignment (site-context role override, not a separate assignment row).

2. Group role assignments: for each group the actor belongs to (via actor_group_member WHERE deleted_at IS NULL AND actor_group.status = 'active'), collect role_assignment rows for the group using the same active/unexpired filter.

3. Permission expansion per role (for every role collected in steps 1 and 2):

   • Direct permissions: role_permission rows for the role
   • Inherited permissions: role_permission rows for all ancestor roles via recursive CTE on role.parent_role_id (see DR-12, depth cap 5)
   • Bundle permissions: role_permission_group rows for the role (WHERE deleted_at IS NULL) → joined to permission_group_permission → expanded to individual permissions
   • Union all three sources per role; deny beats allow across the full union

4. Override: user_permission_override rows that apply (matching tenant_user_id + permission_id and within starts_at / ends_at). deny beats allow. More-specific scope wins: site / module overrides tenant.

5. Final result: allow or deny.

Key Rules

• Vrida platform users do NOT get tenant_user rows. They access tenant data only through active, time-boxed support_access_grant records, and every access is logged in identity_access_event as support_access_used.
• Non-human actors (service accounts, agents) do NOT get tenant_user rows. They obtain tenant scope via their own detail tables (service_account.tenant_id, agent_identity.tenant_id) and receive roles via role_assignment directly. The trigger trg_tenant_user_actor_type_check enforces this at the DB level: any attempt to insert a tenant_user row with a non-human actor raises an exception. See DR-26.
• deny overrides allow throughout (both in role_permission and in user_permission_override).
• identity_access_event logs application-level authorization events, not raw Supabase Auth events (logins, passwords, MFA challenges, session refreshes are all Supabase's domain).
• Secrets and tokens are never stored raw: SSO client_secret_ref and certificate_ref are references to a secret manager; invitation.token_hash, api_key.key_hash, and scim_config.token_hash are hashes of their respective tokens. Raw values are discarded or never stored. Validation hashes the inbound value and compares.
• support_access_grant time-window check is load-bearing: authorization requires status = 'active' AND now() BETWEEN starts_at AND ends_at. Status alone is insufficient.
• password_policy governs email+password users only. Google/SSO/SCIM users are exempt. A single is_active = true row is the Vrida-wide policy; application code reads this at login and profile-update.
• scim_config is a schema skeleton. The SCIM 2.0 API, provisioning lifecycle, and IdP integration are deferred. The table exists to anchor the configuration; tenant_user.external_id maps to the SCIM user's IdP identity when active.

---

Identity — Design Patterns Summary

Tables WITHOUT tenant_id

• actor — global actor root (tenant scoping lives in detail tables)
• identity_user — global user record
• permission — reference data, shared across tenants
• password_policy — global policy, shared across all tenants
• permission_group — Vrida-defined bundle catalog (global reference data)
• permission_group_permission — bundle→permission join (global reference data)
• role_template — Vrida-seeded role template catalog (global reference data)
• role_template_permission_group — template→bundle join (global reference data)
• sod_rule — Vrida-defined SoD rule catalog (global reference data)
• sod_rule_permission — rule→permission join (global reference data)
• identity_session — global session record (no tenant scope — sessions span the authentication context, not any one tenant)
• agent_type_catalog — Vrida-defined agent type catalog (global reference data; Batch C)
• operator — cross-tenant Vrida-operator identity (2026-07-09)
• operator_role_assignment — operator→role grant, no tenant scope (2026-07-09)

Mixed-scope table

• role — tenant_id nullable; NULL rows are built-in / Vrida-internal roles shared across tenants; populated rows are custom tenant roles

Tables with nullable tenant_id

• identity_access_event — tenant_id IS NULL for platform-level events (e.g. SSO login mapping before tenant is selected)

Self-FK (role hierarchy)

• role.parent_role_id — nullable self-FK → identity.role.id. Single-parent inheritance; see DR-12.

Append-only (no updated_at / no deleted_at)

• identity_access_event
• role_permission (no updated_at — changes are made by delete + re-insert)
• permission_group_permission (same pattern as role_permission — delete + re-insert for changes)
• role_template_permission_group (same pattern — delete + re-insert for changes to a template's bundle set)
• sod_rule_permission (same pattern — delete + re-insert for changes to a rule's permission set)
• invitation_site_assignment (write-once pre-acceptance staging data; 2026-07-10, Header/Line Remediation fix #3)

No deleted_at — permanent records

• permission (reference data; deactivate via is_active)
• password_policy (global config; deactivate via is_active)
• permission_group (reference data; deactivate via is_active)
• permission_group_permission (append-only join)
• role_template (reference data; deactivate via is_active)
• role_template_permission_group (append-only join)
• identity_access_event (append-only)
• role_permission (append-only)
• support_access_grant (audit record; expire / revoke via status + ends_at, never soft-delete)
• role_assignment (assignment history; revoke via status = 'revoked' + revoked_at, never delete)
• sod_rule (reference data; deactivate via is_active)
• sod_violation (violation audit record; lifecycle via status machine — open → acknowledged / waived / resolved)
• identity_session (session audit record; end via ended_at + end_reason, never delete)
• access_request (request audit record; permanent after creation — full authorization audit trail)
• agent_type_catalog (reference data; deactivate via is_active — Batch C)
• tenant_security_policy (configuration singleton; reset via UPDATE — no soft-delete semantics; there is only ever one row per tenant, so soft-deletion would leave a permanent gap with no replacement — Batch D)
• scim_config (configuration singleton; reset via UPDATE — no soft-delete; UNIQUE on tenant_id is a plain constraint, not partial — post-build reconciliation)
• operator_role_assignment (assignment history; revoke via status = 'revoked' + revoked_at, never delete — mirrors role_assignment; 2026-07-09)

Cross-phase forward-reference FKs (deferred to Phase 4 multi_loc)

RESOLVED 2026-07-10 (multi_loc's 1st reopen, identity's 5th) — 3 of 5: tenant_user.default_site_id, user_site_assignment.site_id, and invitation_site_assignment.site_id are now real composite FKs → multi_loc.site(id, tenant_id) (tenant_user_default_site_tenant_fkey, user_site_assignment_site_tenant_fkey, invitation_site_assignment_site_tenant_fkey). The blocking orphan (one live user_site_assignment row with a site_id that had never existed in multi_loc.site) was investigated — confirmed isolated dev-seed fixture junk via a DB-wide tenant-isolation scan — and deleted first. See PROJECT_DECISIONS.md #54.

Still genuinely bare — not part of this pass:

• user_permission_override.scope_id (when scope_type = 'site') — named alongside the 3 above in fix #3's original bundle proposal but deliberately excluded from this pass's scope; independently confirmed via \d/pg_constraint to still carry zero FK.
• access_request.requested_scope_id (when requested_scope_type = 'site') — a 4th column with the exact same shape and target, present in this list all along but never named in fix #3's bundle, this pass's task scope, or its independent verification; newly logged as its own OPEN_ITEMS row rather than silently swept in.

Reference / hash — never raw

• sso_provider.client_secret_ref — reference to secret manager
• sso_provider.certificate_ref — reference
• scim_config.token_hash — hash of SCIM bearer token (v1 live; see scim_config design note re: possible future migration to secret-manager reference)
• invitation.token_hash — hash of token
• api_key.key_hash — hash of API key (bcrypt / SHA-256); raw key returned once at creation, then discarded (Batch C)

Triggers (first triggers in identity schema)

Convention: all DB triggers in the identity schema are documented here. Triggers are used when the invariant requires cross-row validation that CHECK constraints cannot express (Postgres CHECKs are row-scoped and cannot subquery other rows).

• trg_tenant_user_actor_type_check — BEFORE INSERT OR UPDATE ON identity.tenant_user. Reads identity.actor.actor_type for NEW.actor_id and raises EXCEPTION 'actor_type must be user to join tenant_user' if not 'user'. DB-enforced human-only membership — prevents service accounts and agents from accidentally receiving tenant_user rows. Added in Batch C migration alongside service_account and agent_identity. See DR-26. Service-layer note (BEFORE trigger + missing actor): Because this is a BEFORE trigger, it fires before FK constraint checking. If NEW.actor_id has no matching identity.actor row, the trigger's SELECT returns NULL and the EXCEPTION fires with message "must be user" rather than a FK violation message. The INSERT is blocked either way — the error message is slightly misleading. Only reachable via direct SQL (application code always inserts actor first per DR-11's insertion-order rule). Service-layer developers should be aware when writing test fixtures that bypass the ORM.

Shared PK — class table inheritance detail tables

These tables use a shared PK from identity.actor — no gen_random_uuid() default; UUID is generated on actor.id and passed in at insert time. Insertion order is always actor first, then the detail table.

• identity_user — id = actor.id; actor_type = 'user'
• service_account — id = actor.id; actor_type = 'service_account' (Batch C)
• agent_identity — id = actor.id; actor_type = 'agent' (Batch C)
• operator — id = actor.id; actor_type = 'operator' (2026-07-09)

Supabase Auth seam — not an enforced FK

• identity_user.supabase_auth_user_id — UUID reference to Supabase auth.users. No DB-level FK; integrity is maintained by application code and Supabase webhooks (user created / deleted / email changed).

JSONB columns by purpose

Table	Column	Purpose
invitation	site_assignments	DEPRECATED (2026-07-10, Header/Line Remediation fix #3) — superseded by identity.invitation_site_assignment; still readable/writable, no code path currently populates it (confirmed live: zero rows in invitation at build time). [{"site_id":"uuid","role_id":"uuid"}] — intended site access on accept
sso_provider	allowed_domains	["greenthumb.com"] — email domains permitted via this provider
identity_access_event	metadata	Event-specific context bag
support_access_grant	audit_log	Grant-specific context (ticket details, incident links)
scim_config	sync_errors	Most recent sync error details from SCIM sync runs
agent_type_catalog	default_config	Per-type default agent configuration. Example: {"temperature": 0.3, "max_tokens": 4096, "enabled_tools": ["read_inventory"]} — (Batch C)
agent_identity	config	Sparse per-instance overrides on type default_config. Example: {"max_tokens": 8192} — merged at resolution time (Batch C)

Partial unique indexes (soft-delete pattern)

• identity_user: UNIQUE (supabase_auth_user_id) WHERE deleted_at IS NULL AND UNIQUE (email_normalized) WHERE deleted_at IS NULL
• tenant_user: UNIQUE (tenant_id, actor_id) WHERE deleted_at IS NULL
• role: two partial uniques — UNIQUE (tenant_id, role_code) WHERE deleted_at IS NULL for custom tenant roles, PLUS UNIQUE (role_code) WHERE tenant_id IS NULL AND deleted_at IS NULL for built-in / Vrida-internal roles (NULL tenant_id rows do not collide under the first index — bug class #1). Both belong in the Phase 3 schema migration.
• user_site_assignment: UNIQUE (tenant_user_id, site_id) WHERE deleted_at IS NULL
• invitation: UNIQUE (tenant_id, email_normalized) WHERE status = 'pending' — one pending invite per email per tenant
• user_permission_override: UNIQUE (tenant_user_id, permission_id, scope_type) WHERE deleted_at IS NULL — one active override per user + permission + scope
• scim_config: UNIQUE (tenant_id) — one SCIM config per tenant (plain unique; no deleted_at on this table)
• password_policy: UNIQUE (is_active) WHERE is_active = true — singleton active policy
• actor_group: UNIQUE (tenant_id, name) WHERE deleted_at IS NULL — one active group per name per tenant
• actor_group_member: UNIQUE (group_id, actor_id) WHERE deleted_at IS NULL — one active membership per actor per group
• role_permission_group: UNIQUE (role_id, permission_group_id) WHERE deleted_at IS NULL — one active bundle wiring per (role, bundle) pair
• service_account: UNIQUE (client_id) globally + UNIQUE (tenant_id, name) WHERE deleted_at IS NULL — (Batch C)
• sod_violation: UNIQUE (tenant_id, actor_id, sod_rule_id) WHERE status = 'open' — at most one open violation per (tenant, actor, rule); permits a new open row after previous is resolved/waived. Added in post-build reconciliation migration (was blocking Phase 5).
• sso_provider (v1): UNIQUE (tenant_id) WHERE status IN ('active','testing') AND deleted_at IS NULL — v1 single-active-provider constraint; see design note in table definition
• tenant_security_policy: UNIQUE (tenant_id) — per-tenant singleton; no WHERE clause (no soft-delete on this table — plain unique constraint, not partial). Contrast with the password_policy singleton (UNIQUE (is_active) WHERE is_active = true) — there the singleton is enforced by filtering the active row; here it is enforced by uniqueness on the tenant FK itself. (Batch D)

---

Identity — Design Rationale

Non-obvious schema design choices. Each entry: Decision / Why / Rejected / Guard. Entries drafted at pre-lock audit; incorporated into the identity module spec when authored.

---

DR-1: supabase_auth_user_id is not an enforced FK

Decision: identity_user.supabase_auth_user_id is stored as a plain UUID column with a partial unique index. No REFERENCES constraint to auth.users.

Why: Supabase Auth lives in its own schema (auth) outside Vrida's migration-controlled schemas (platform, identity, etc.). Cross-schema FK constraints to Supabase-managed tables would be fragile — Supabase controls schema migrations on auth.users and does not guarantee FK-safe behavior from application schemas. Integrity is maintained by application code (creating/deleting identity_user rows in response to Supabase Auth webhooks) and by the partial unique index (ensuring no duplicate mappings).

Rejected: Enforced FK via REFERENCES auth.users(id). Rejected because: (1) Supabase controls auth.users schema; (2) deleting a Supabase Auth user would cascade-fail or require identity rows to be deleted first, breaking the webhook-driven flow; (3) deferred FKs in Postgres don't cross schema ownership boundaries cleanly.

Guard: Every identity_user INSERT must include a valid supabase_auth_user_id. Application code must verify the Supabase Auth user exists before writing. The unique partial index prevents duplicate mappings but not dangling references — webhook handling is the safety net.

---

DR-2: identity_user is global, not tenant-scoped

Decision: identity_user has no tenant_id. A single row represents the user across all tenants they belong to. Tenant membership is recorded in tenant_user.

Why: A person is one person. If the same human belongs to three tenants (e.g., a multi-site owner who also manages a family member's business), they should have one identity_user row with one Supabase Auth account — not three separate identities. This mirrors how enterprise identity systems (LDAP, Okta) work: the person is the principal; group/tenant memberships are separate join records. It also means password changes, MFA settings, and contact info update once and propagate everywhere.

Rejected: Tenant-scoped user table (one row per user per tenant). Rejected because: (1) it forces a user to maintain separate credentials per tenant; (2) it breaks SSO flows where IdP identity maps to the person, not the tenant membership; (3) it duplicates identity data that fundamentally belongs to one human.

Guard: Access control on identity_user must enforce that a user can only read their own row or rows of users within shared tenants (resolved via tenant_user). Cross-user reads outside that scope require service_role. No row may be written to identity_user at runtime by application code except via the registration / webhook path.

---

DR-3: role has nullable tenant_id (mixed-scope design)

Decision: role.tenant_id is nullable. NULL rows are Vrida-built roles shared with all tenants; populated rows are custom tenant-created roles. Both live in the same table.

Why: Vrida ships a set of built-in roles (owner, manager, cashier, etc.) that every tenant uses out of the box. These must be visible to every tenant's RLS context without requiring one copy per tenant. A single shared row is the minimal representation. Custom tenant roles extend this set without creating a parallel table. The RLS policy handles both cases in one expression: USING (tenant_id IS NULL OR tenant_id = current_setting('app.current_tenant_id')::uuid).

Rejected: Two separate tables (builtin_role and tenant_role). Rejected because: (1) role_permission, tenant_user.role_id, user_site_assignment.site_role_id, and user_permission_override.scope_type would all need two FK paths; (2) permission resolution logic would need to union two sources; (3) the uniqueness invariants (no duplicate role_code per scope) are expressible in one table but require two tables + coordinated checks if split.

Guard: The tenant_id IS NULL branch of the RLS policy is load-bearing (bug class #5). It must never be removed. The two-partial-index pattern on role_code (one for custom roles, one for NULL-tenant rows) must both remain — the standard UNIQUE (tenant_id, role_code) index silently allows duplicate role_codes among NULL-tenant rows because Postgres treats NULL ≠ NULL in unique checks.

---

DR-4: role_permission is append-only (delete + re-insert for changes)

Decision: role_permission has no updated_at and no deleted_at. Modifying a role's permissions means deleting the old row and inserting a new one.

Why: A role's permission set is a set relationship — the row either exists or it doesn't. There is nothing to update on a role_permission row: the role_id, permission_id, and effect are all identity fields. If you want to change effect from allow to deny, the semantically correct operation is "remove the allow grant and add a deny grant" — which maps naturally to delete + insert. This keeps the table clean: no nullable update fields, no updated_at drift, no soft-delete rows accumulating.

Rejected: Soft-delete with effect update (update effect on the existing row). Rejected because: (1) it suggests the row has mutable state beyond identity, which is misleading; (2) soft-delete would accumulate historical grant records in a junction table, adding bloat for no query benefit (history belongs in identity_access_event); (3) the UNIQUE on (role_id, permission_id) would require soft-delete scoping, complicating the index.

Guard: Any bulk permission assignment must wrap all deletes and inserts in a single transaction. The UNIQUE on (role_id, permission_id) prevents duplicate grants; the reverse lookup index on permission_id must be present for "which roles have this permission" queries.

---

DR-5: support_access_grant has no deleted_at

Decision: support_access_grant is a permanent audit record. Access ends via status + ends_at; rows are never soft-deleted.

Why: Every instance of a Vrida employee accessing a tenant must be permanently on record for compliance and audit purposes. Soft-deletion would allow the record to be hidden from normal queries, which is unacceptable for an access audit trail. The time-bounded design (starts_at / ends_at) makes soft-delete semantically redundant — a grant that has expired is already effectively inactive. The revoked_at / revoked_by_user_id fields provide the "was this ended early?" signal without needing a deletion marker.

Rejected: Soft-delete with deleted_at. Rejected because: (1) compliance requires the full audit trail to be visible; (2) any partial deletion of support access history is a security red flag; (3) the time-window design already signals inactive grants without needing deleted_at.

Guard: No application code path may hard-delete or soft-delete support_access_grant rows. Access expiry is handled by: (1) ends_at expiring naturally; (2) explicit revoke via status = 'revoked' + revoked_at + revoked_by_user_id. The authorization check must always evaluate BOTH status = 'active' AND now() BETWEEN starts_at AND ends_at — status alone is insufficient (DR-10).

---

DR-6: Two-partial-index pattern on role.role_code

Decision: role carries two partial unique indexes instead of one standard unique: UNIQUE (tenant_id, role_code) WHERE deleted_at IS NULL for custom roles, AND UNIQUE (role_code) WHERE tenant_id IS NULL AND deleted_at IS NULL for built-in/Vrida-internal roles.

Why: Postgres evaluates uniqueness with the rule NULL ≠ NULL. A standard UNIQUE (tenant_id, role_code) index would allow multiple rows with tenant_id IS NULL and the same role_code, because each NULL is treated as distinct. The second partial index closes this gap by explicitly constraining the tenant_id IS NULL subset. Without it, you could accidentally seed two owner roles with tenant_id IS NULL and the constraint would not fire (bug class #1).

Rejected: Single UNIQUE on role_code only. Rejected because it would prevent two different tenants from each having a custom role with the same role_code (e.g., both tenants defining a 'cashier_senior' custom role would conflict). Rejected: UNIQUE on (COALESCE(tenant_id, '00000000-0000-0000-0000-000000000000'), role_code). Rejected because it introduces a magic sentinel UUID, breaks FK integrity, and is non-obvious to future readers.

Guard: Both partial unique indexes must be present. Neither may be dropped alone. Both must be created in the Phase 3 identity schema migration (not the seed migration, which runs after).

---

DR-7: password_policy is Vrida-wide, not per-tenant

Decision: password_policy has no tenant_id. A single active row (is_active = true) sets the password rules for all users on the platform.

Why: Vrida targets small-to-medium plant nurseries (typically ≤50 employees). These businesses do not have internal IT/security teams that would configure custom password policies. A single Vrida-wide policy is sufficient for the market and dramatically simpler to reason about — there is no per-user or per-tenant policy negotiation at login. Supabase Auth handles the actual enforcement; this table is the configuration source that both the app and Supabase Auth policy sync from.

Rejected: Per-tenant password policy (one row per tenant). Rejected because: (1) the target market doesn't have the IT overhead to configure it; (2) it adds tenant_id, RLS, and policy-resolution logic to every password check; (3) enterprise tenants who need custom policies will use SSO (which bypasses this table entirely).

Guard: The singleton invariant is enforced by UNIQUE (is_active) WHERE is_active = true — inserting a second is_active = true row fails at the DB level. To rotate the policy: set the old row's is_active = false, then insert the new row with is_active = true. Application code must never write to password_policy at runtime; only service_role migrations may.

---

DR-8: scim_config is a schema skeleton (deferred implementation)

Decision: The scim_config table exists in the Phase 3 schema but the SCIM 2.0 API endpoints, provisioning/deprovisioning lifecycle, and IdP integration are deferred to a later build phase.

Why: SCIM (System for Cross-domain Identity Management) is an enterprise feature used by companies with >500 employees running centralized IdPs (Okta, Azure AD). Vrida's initial market is small retail nurseries (≤50 employees) where SCIM provides no value. Building SCIM now would add significant API surface and provisioning logic with zero near-term customers. The table is included in the Phase 3 schema because the tenant_user.external_id column (the SCIM user's IdP identity) needs a home in the schema — the anchor table must exist even if unused.

Rejected: Omit scim_config entirely from Phase 3. Rejected because: (1) tenant_user.external_id has nowhere to anchor without the config table; (2) adding a table mid-module after lock requires a schema reopen; (3) the cost of a skeleton table is negligible — it adds 11 columns and one FK but zero API endpoints.

Guard: All scim_config columns must remain nullable or defaulted except id, tenant_id, is_enabled, and status. No application code may read or write scim_config rows until the SCIM build phase begins. bearer_token_ref must always reference a secret manager — never store the raw bearer token. Track build-start trigger in OPEN_ITEMS.

---

DR-9: user_permission_override uses one table with scope_type + table CHECK

Decision: All per-user permission overrides live in a single user_permission_override table with a scope_type column ('tenant', 'site', 'module') and a table-level CHECK that enforces mutual exclusivity of scope_id / scope_code.

Why: A per-user override is the same concept regardless of scope — it grants or denies one permission to one user within some boundary. Splitting into three tables (tenant_override, site_override, module_override) would force permission resolution to union three queries, duplicate the effect / starts_at / ends_at / granted_by / reason columns three times, and complicate the override dedup unique index. The table CHECK enforces the mutual exclusivity invariant at the DB level with zero application code.

Rejected: Three separate tables per scope type. Rejected because: (1) permission resolution must check all three scopes on every authorization decision — three tables mean three queries or a UNION; (2) the deny-beats-allow + site/module-beats-tenant precedence rules are easier to express over one table; (3) future scope types (e.g., 'zone') require adding a table instead of adding a CHECK value.

Guard: The table CHECK — (scope_type='site' AND scope_id IS NOT NULL AND scope_code IS NULL) OR (scope_type='module' AND scope_code IS NOT NULL AND scope_id IS NULL) OR (scope_type='tenant' AND scope_id IS NULL AND scope_code IS NULL) — must remain. The UNIQUE on (tenant_user_id, permission_id, scope_type) WHERE deleted_at IS NULL prevents duplicate active overrides and must also remain. Permission resolution must apply the site/module > tenant precedence rule explicitly in application code.

---

DR-10: support_access_grant time-window authorization rule

Decision: Authorizing a Vrida platform user's access to a tenant requires checking BOTH status = 'active' AND now() BETWEEN starts_at AND ends_at. Status alone is insufficient.

Why: A grant row enters status = 'expired' only after an explicit expiry job or trigger runs. In a system without real-time triggers, a grant whose ends_at has passed but whose status has not yet been updated to 'expired' would appear active by status alone — allowing unauthorized access in the gap between expiry time and the status-update job. The time-window check is instantaneous and requires no background job to be timely. Defense-in-depth: even if a bug leaves status = 'active' on an expired grant, the time-window check blocks access.

Rejected: Status-only check (WHERE status = 'active'). Rejected because it creates a race condition between expiry time and any background expiry job. Rejected: time-window check without status (WHERE now() BETWEEN starts_at AND ends_at). Rejected because it ignores explicit revocation — a revoked grant within its time window would incorrectly appear valid.

Guard: Every code path that evaluates a support access grant MUST include both conditions: status = 'active' AND now() BETWEEN starts_at AND ends_at. Add a linter / code review checklist item for this check. The identity_access_event row written on every support_access_used event provides the audit trail to detect any bypass.

---

DR-11: Polymorphic actor model — shared PK / class table inheritance

Decision: identity.actor is the root table for all principals. identity_user, service_account, and agent_identity are detail tables whose id column IS the actor's id (shared PK — no separate actor_id FK column). All FK columns that represent "who did this / who triggered this / who was affected" point to identity.actor, not to detail tables — with seven named semantic exceptions that must stay → identity_user (see Guard).

Why polymorphic actor: The product goal is "an agent did X works exactly like a user did X everywhere" — uniform audit attribution, uniform role assignment, uniform permission resolution. Any design short of a polymorphic actor base requires special-casing in every audit log writer, every role assignment, and every permission check. The special-cases accumulate and the inconsistency shows up in admin UIs, compliance reports, and every future actor type.

Why shared PK (not separate actor_id FK): Zero existing data — identity has not been migrated. Shared PK is the standard ORM class-table-inheritance pattern. It removes one column per detail table, eliminates ambiguity (UUID serves as both detail-table PK and actor PK with no indirection), and makes joins trivial (JOIN identity.actor a ON a.id = u.id).

Rejected: (1) Parallel nullable FK columns per actor type (actor_user_id + actor_service_account_id etc.) — resolution ambiguity when both populated; every query requires multi-column handling; every future actor type adds another column. (2) Separate actor_id FK column on each detail table (identity_user, service_account, agent_identity) — duplicates the UUID and requires two columns where the shared PK requires one.

Why status lives on actor, not detail tables: Every authorization check needs to know if a principal is active/suspended/deactivated regardless of actor type. Putting status on actor means one check, not one per detail table. identity_user.status was removed in the Batch A expansion; actor.status replaces it uniformly.

Why display_name is nullable on actor: Insertion order is actor-first (UUID generated here), detail-second. At the moment the actor row is inserted, the display name may not yet be available (e.g., the detail table hasn't been inserted yet). Nullable avoids requiring two round-trips or a default placeholder.

Semantic exceptions — 7 FK columns that stay → identity_user (must be a named human):

• tenant_user.invited_by_user_id — invitation is a human action with consent implications
• invitation.invited_by_user_id, invitation.accepted_by_user_id, invitation.revoked_by_user_id — invitation lifecycle is definitionally human-to-human
• support_access_grant.vrida_user_id, support_access_grant.granted_by_user_id, support_access_grant.revoked_by_user_id — compliance record for named Vrida employees

Semantic exceptions — 4 platform FK columns that stay → identity_user (human semantics):

• platform.tenant_contact.identity_user_id — contact is a person
• platform.agreement_acceptance.accepted_by_user_id — legal click-wrap acceptance must be a human
• platform.tenant_internal_activity.performed_by_user_id — CS ops record for named human operators
• platform.operator_audit_log.operator_user_id — compliance record for named human operators

Guard: The 11 semantic exceptions above must NOT be changed to → identity.actor without a deliberate design decision logged in PROJECT_DECISIONS. Any new FK column representing "who did this" should default to → identity.actor unless the semantic meaning is explicitly human-only. Insertion order is always actor → detail; never insert a detail row without a corresponding actor row.

---

DR-12: Role inheritance — single-parent, app-enforced cycle prevention and cross-tenant guard

Decision: role.parent_role_id is a nullable self-FK implementing single-parent role inheritance. A child role's effective permission set = own role_permission rows UNION all ancestor role_permission rows (resolved via recursive CTE on parent_role_id), with deny from any ancestor or descendant overriding allow from any other. Cycle prevention and the cross-tenant inheritance constraint are application-enforced; only the self-loop case has a DB-level guard.

Why single-parent: Matches the org hierarchy model natural to retail staff (cashier ← supervisor ← manager ← owner). Multi-parent creates diamond inheritance — contradictory inherited effects requiring conflict-resolution rules more complex than any real use case justifies. Single-parent trees are deterministic and auditable via a simple recursive walk.

Why app-enforced cycle prevention (not DB): Postgres CHECK constraints are row-scoped — they cannot query other rows. A CHECK can catch parent_role_id = id (self-loop) but cannot detect A→B→A or longer cycles without a recursive query. The self-loop CHECK is on the table (parent_role_id IS NULL OR parent_role_id != id). All longer cycles are prevented by service-layer code that walks the ancestor chain before allowing a parent_role_id SET: if the target role appears anywhere in the current role's ancestor chain, the SET is rejected. Depth cap is 5 levels (enforced at service layer; typical retail org hierarchy is 3–4 levels).

Why app-enforced cross-tenant guard (not trigger): A trigger could query the parent row and compare tenant_ids — but it adds operational complexity (trigger maintenance, testing, error message clarity). The service layer already validates the parent before writing; a trigger would be redundant enforcement with worse error messages. The constraint is documented and code-reviewed instead. The rule: parent.tenant_id IS NULL OR parent.tenant_id = child.tenant_id. Valid inheritance paths:

• System/built-in role (tenant_id IS NULL) → any tenant's custom role: allowed
• Custom role of tenant X → custom role of tenant X: allowed
• Custom role of tenant Y → custom role of tenant X: FORBIDDEN (cross-tenant data leak)

Permission resolution update: this recursive CTE implements the inherited permissions branch of Step 3 in the Permission Resolution Order (see Design Notes → Permission Resolution Order). For each role collected in Steps 1–2, fetch role_permission rows for the role AND all ancestor roles via WITH RECURSIVE cte AS (SELECT id, parent_role_id FROM identity.role WHERE id = $role_id UNION ALL SELECT r.id, r.parent_role_id FROM identity.role r JOIN cte ON r.id = cte.parent_role_id) SELECT role_id FROM cte. Then apply deny-beats-allow across the full union of direct + inherited + bundle permissions (Step 3).

Rejected: (1) Multi-parent inheritance via a separate role_parent join table — diamond inheritance complexity; (2) Depth-unlimited recursion — DoS vector via pathological chains; depth cap of 5 bounds query cost; (3) DB trigger for cross-tenant enforcement — operationally complex, worse error experience than service-layer validation.

Guard: Service layer must validate cross-tenant constraint AND cycle absence before any parent_role_id write. The self-loop DB CHECK is the only schema-level guard. Document the depth cap (5) in the identity module spec when authored. The parent_role_id index (WHERE parent_role_id IS NOT NULL) must be present to serve the recursive CTE efficiently.

---

DR-13: Permission groups — Vrida-defined bundles, join-table model, role wiring authored in Batch B Pass 1

Decision: permission_group + permission_group_permission provide a Vrida-curated catalog of named permission bundles (e.g. "Inventory Manager Bundle"). Bundles attach to roles via a join table (role_permission_group, role_id + permission_group_id) rather than by expanding into individual role_permission rows at assignment time. role_permission_group was authored in Batch B Pass 1 — Pass 2 shipped the bundle catalog; Batch B Pass 1 shipped the role-level wiring. Bundles are VRIDA-DEFINED ONLY; tenants may assign bundles to custom roles but cannot create or modify them.

Why Vrida-defined only: With 100+ permission codes across modules, single-permission role setup is impractical for tenant admins. Vrida curates sensible bundles; tenants build custom roles by combining bundles. Tenant-defined groups add complexity (groups-of-groups? naming conflicts? validation?) with little incremental value.

Why join-table (not expansion): If assigning a bundle expanded it into individual role_permission rows, Vrida could not update a bundle after assignment — fixing a bug (e.g. removing an incorrect permission from "Inventory Manager") would require a data migration across every role that ever used the bundle. The join table preserves bundle identity: when Vrida patches a bundle, all roles using it see the updated permission set on the next resolution. It also enables the query "which roles include bundle X" — not possible post-expansion. The added join path in permission resolution (one extra CTE or JOIN through role_permission_group → permission_group_permission → permission) is bounded and acceptable.

Why role_permission_group is Batch B, not Pass 2: Permission groups are reference/seed data — the catalog can exist and be seeded before the wiring is built. Deferring the wiring to Batch B keeps Pass 2's scope on structural tables (groups, inheritance). The bundle tables are inert until Batch B lands; this is the same pattern as scim_config (schema skeleton, deferred implementation).

Rejected: (1) Expansion model (assign-time expansion into role_permission rows) — Vrida bundle updates don't propagate; no audit of which roles use a bundle; requires data migration on any bundle correction. (2) Tenant-customizable bundles — adds validation complexity, naming conflict risk, and a nested bundle hierarchy nobody needs. (3) Including role_permission_group in Pass 2 — the wiring belongs with the full role management overhaul in Batch B.

Guard: permission_group and permission_group_permission are writable only via service_role and seed migration. No application code writes to these tables at runtime. role_permission_group was authored in Batch B Pass 1 and is tenant-scoped (not global like permission_group) — a bundle-to-role assignment belongs to the tenant that created the custom role.

---

DR-15: role_assignment replaces tenant_user.role_id

Decision: tenant_user.role_id is removed (Batch B Pass 1). All role assignments — including the initial membership role — are recorded in role_assignment. tenant_user is now a pure membership table.

Why: tenant_user.role_id supported only a single, non-time-bounded role per tenant membership. role_assignment supports: multiple concurrent roles (a user can hold Manager and Buyer simultaneously), time-bounded assignments (a seasonal worker holds Cashier from May 1 to September 30), full assignment history (who assigned what role, when, whether revoked and by whom), and group-level assignments (assign a role to an entire actor group). A single column can model none of these — and seasonal nursery staffing is a core Vrida vertical use case.

Rejected: (1) Extending tenant_user with role_id[] (array of role IDs) — array columns require unnesting for joins; history is lost on every update; group-level assignment has no anchor in tenant_user. (2) Adding starts_at/ends_at to tenant_user.role_id — still a single role; no history; no group path.

Guard: Any code path that previously read tenant_user.role_id must be updated to query role_assignment for the actor's active, unexpired roles (step 1 of the Permission Resolution Order). The column no longer exists in the schema. Migration: on creation of the first role_assignment row for an actor+tenant pair, that row supplants the old role_id — no data migration needed for pre-migration data since the schema is not yet migrated. No UNIQUE on (tenant_id, actor_id, role_id) — intentional. Time-windowed re-assignment creates a new row without revoking the expired predecessor; resolution filters by ends_at. The same actor can hold the same role across non-overlapping time windows with two status = 'active' rows (one effectively expired by time but not yet revoked). Deduplication of truly overlapping same-role grants is app-layer responsibility. Do not add this constraint — it would break time-windowed renewal.

---

DR-16: role_template clone — materialize to role_permission at clone time

Decision: Cloning a role_template to create a tenant_custom role materializes the template's role_template_permission_group references into individual role_permission rows on the new role. The cloned role is fully independent post-clone: changes to the template (adding/removing bundles) do NOT propagate to any cloned role.

Why: Two models are possible. (1) Join-table: keep the template FK alive post-clone, updates propagate. (2) Materialization: expand to role_permission rows at clone time, independence guaranteed. The join-table model means a Vrida bundle patch silently alters every custom role cloned from a template — a tenant's carefully tuned role is unexpectedly changed. The materialization model gives tenants a stable starting point they can freely customize without Vrida bundle updates retroactively reaching into their configuration.

Rejected: Join-table model (template→role link preserved post-clone). Rejected because: (1) silent propagation of Vrida bundle changes onto tenant-owned roles; (2) prevents tenants from fine-tuning cloned roles by adding/removing individual role_permission rows without the template reimposing its bundle set on next resolution.

Guard: The clone operation must be atomic: the new role row and all role_permission rows are inserted in a single transaction. The clone reads role_template_permission_group → permission_group → permission_group_permission to expand bundles into individual permissions. After cloning, the role row carries no FK back to the source template — independence is enforced by the absence of such a column.

---

DR-17: role_permission_group is soft-delete, not append-only

Decision: role_permission_group uses deleted_at soft-delete for bundle-to-role wiring, unlike role_permission (append-only) and permission_group_permission (append-only).

Why: The choice diverges intentionally from the append-only pattern on the other two join tables. role_permission is an identity join — a row either says "role R has permission P with effect E" or it doesn't; there is nothing to update. role_permission_group is a tenant configuration choice — "this bundle is attached to this role." The attachment has a meaningful lifecycle: added on date D, removed on date D2. Soft-delete preserves the history of which bundles were attached to which roles over time, which is important for audit ("who removed the Inventory Manager Bundle from the Floor Staff role, and when?"). Append-only would require hard-deletes for removal, forfeiting that history.

Rejected: Append-only with hard-delete for removal. Rejected because: (1) hard-delete loses the attachment history entirely — no record that the bundle was ever attached; (2) identity_access_event would need a dedicated bundle_removed event to compensate, duplicating semantics a deleted_at timestamp already provides at zero cost; (3) the UNIQUE on (role_id, permission_group_id) WHERE deleted_at IS NULL allows the same bundle to be re-attached after removal — impossible with append-only + hard-delete without special handling.

Guard: All queries that resolve bundle permissions for a role must filter WHERE deleted_at IS NULL. The UNIQUE constraint enforces at most one active wiring per (role, bundle) pair. Step 3 of the Permission Resolution Order explicitly includes this filter.

---

DR-18: role_assignment polymorphic assignee — two nullable FKs + table CHECK

Decision: role_assignment supports two assignee types (individual actor or actor group) via two nullable FK columns (actor_id → identity.actor, actor_group_id → identity.actor_group) plus a table-level CHECK constraint enforcing exactly one is populated: CHECK ((actor_id IS NOT NULL AND actor_group_id IS NULL) OR (actor_id IS NULL AND actor_group_id IS NOT NULL)).

Why: The design goal is DB-level FK integrity for both target types in a single unified table. Alternative polymorphic designs (a discriminator column + single assignee_id UUID, or a text assignee_type + generic reference) lose FK integrity — the DB cannot enforce that assignee_id actually references a valid actor or actor_group row. The two-nullable-FK pattern preserves FK integrity for both types while the table CHECK enforces mutual exclusivity, making invalid states (both NULL, both NOT NULL) impossible at the DB level. This is the same pattern used in user_permission_override (scope_type + conditional scope_id/scope_code) — a table CHECK replacing a polymorphic-reference column.

Rejected: (1) Generic polymorphic reference: assignee_type text + assignee_id UUID — no DB FK integrity on either target; a corrupt assignee_id silently references nothing. (2) Union table: separate actor_role_assignment and group_role_assignment tables — permission resolution must query two tables and union the results; Steps 1 and 2 of the resolution order split across two different tables for no schema gain. (3) JSONB assignee blob — loses FK, loses indexes, loses type clarity.

Guard: The table CHECK is the DB-level guard — do not drop it. Both actor_id and actor_group_id partial indexes (WHERE actor_id IS NOT NULL and WHERE actor_group_id IS NOT NULL) must remain to serve the resolution hot-paths efficiently. Service layer code that reads role_assignment must always branch on which FK is populated to determine resolution path (actor direct vs. group member expansion).

---

DR-19: SoD model — permission-level detection, detect-and-flag, waiver semantics

Decision: SoD (Separation of Duties) detection is permission-level only (not role-level) and detect-and-flag (not block). sod_rule defines incompatible permission combinations; sod_violation records instances where an actor's effective permission set (resolved via the full 5-step Permission Resolution Order) contains all permissions in a rule. Tenants may waive individual violations; waivers can be time-bounded (waiver_expires_at) or permanent (NULL). Vrida defines all SoD rules globally (v1); tenant-custom rules are deferred to v2. SoD violations surface in the audit module compliance cluster — they do not block access.

Why permission-level (not role-level): Permission-level SoD subsumes role-level SoD for correctness. A dangerous role combination is dangerous because of the permissions it grants. The 5-step resolution (including parent role inheritance and bundle expansion via role_permission_group) already expands all effective permissions regardless of which roles granted them. A permission-level check naturally catches toxic role combinations via their constituent permissions. Role-level SoD would be a weaker, bypassable check (rename the role, bypass the rule) requiring a separate detection algorithm (collect effective roles, not effective permissions) with no incremental correctness benefit for the SMB market.

Why detect-and-flag (not block): Vrida's initial market is businesses with ≤50 employees, many of them family operations where one person fills multiple roles legitimately. A hard block on assigning a conflicting permission would break valid multi-hat scenarios — the owner who is also the cashier who also approves refunds. The detect-and-flag model surfaces violations for compliance review without disrupting daily operations. Future enterprise customers who need hard blocks can be served by a policy layer in tenant_security_policy (Batch D) that elevates specific rules to blocking mode.

Why waiver_expires_at: Without time-bounded waivers, all waivers are permanent. A compliance-conscious tenant needs time-limited waivers (e.g., "we accept this for the May–September seasonal period"). NULL = permanent; populated = detection resumes after that timestamp. Detection engine checks: if a waived violation exists for (tenant, actor, rule) AND (waiver_expires_at IS NULL OR now() < waiver_expires_at), skip creating a new open violation.

Rejected: (1) Role-level SoD via sod_rule_role (pairs of roles that should not coexist on the same actor) — weaker than permission-level; bypassable by renaming roles; requires a separate detection algorithm; deferred to v2 in OPEN_ITEMS. (2) Blocking SoD via access denial at permission check — operationally unacceptable for SMB; deferred to a tenant_security_policy flag (Batch D). (3) Tenant-custom SoD rules — adds validation complexity, naming conflicts; deferred to v2.

Guard: sod_rule and sod_rule_permission are writable only via service_role and the seed migration. Service layer must enforce ≥2 permissions before allowing is_active = true. Detection engine uses HAVING COUNT(*) >= 2 to naturally skip underpopulated rules. No code path may hard-delete or soft-delete sod_violation rows — only status transitions are permitted.

---

DR-20: SoD detection trigger — hybrid on-change + daily sweep

Decision: SoD detection uses a hybrid trigger model: (1) On-change evaluation — fired when role_assignment or user_permission_override changes for a specific actor; evaluates that actor's SoD state immediately and creates/resolves violations for the affected actor only. (2) Daily scheduled sweep — covers role-level permission catalog changes (adding/removing a permission from a role via role_permission affects all role holders at once); also acts as a safety net for any missed on-change events.

Why hybrid (not on-change only): On-change handles the common case (individual assignment changes) responsively. However, changing a permission on a role affects all actors who hold that role — a batch event that on-change cannot track efficiently without evaluating all role holders on every role_permission change.

Why hybrid (not scheduled-only): A purely scheduled approach introduces up to 24 hours of latency. For an SMB compliance tool, near-real-time detection (visible when a problematic role is assigned) is important for UX — tenants should see a violation warning at assignment time, not the next morning.

Rejected: (1) On-change only — cannot efficiently handle role_permission catalog changes; requires evaluating N actors on every permission-per-role change. (2) Scheduled-only — up to 24-hour detection latency; poor assignment-time UX.

Guard: On-change detection must be transactionally decoupled from the role/override assignment (fire after commit, not before — violations are informational, not blocking). Daily sweep must respect active waivers: skip creating an open violation if a valid waiver exists for (tenant, actor, rule).

---

DR-21: identity_session is global + session_id loose correlation with identity_access_event

Decision: identity_session has no tenant_id — sessions are global records anchored to an actor, not a tenant. A user session spans the authentication context; tenant context is selected within a session, not bound to it. identity_access_event gains session_id UUID nullable (Batch B Pass 2 locked-table touch) so all events written during a session carry the session UUID, as a loose historical reference — not an enforced FK.

Why global (no tenant_id): An authenticated session can span multiple tenant selections (the user logs in once, then selects a tenant from their list). Binding the session to tenant_id would require creating a new session row on every tenant switch — losing the cross-tenant activity trail and making "log out all devices" impossible to implement correctly (a session in tenant A would be invisible when managing sessions from tenant B). Access control is actor-based: an actor reads only their own session rows; platform staff via support_access_grant; service_role for admin.

Why session_id on identity_access_event NOW (not deferred): Batch B Pass 2 is the natural window — identity_session is designed here. Deferring to Batch D means all access events written between deployment and Batch D permanently lack session correlation. The column is nullable — pre-session events (login flow before identity_session is created), platform-level events, and webhook-triggered events have session_id = NULL; no ordering constraint risk. Once deployed, all events written during a session carry the session UUID; the "all events in session S" forensic query becomes a direct UUID join (when the referenced session still exists) rather than a string scan on correlation_id. The correlation_id text field remains available for cross-system tracing (request IDs, job IDs).

CORRECTION (2026-07-18, pre-Phase-2 confirmation pass): this record originally claimed session_id was an enforced FK with a supporting (session_id) WHERE session_id IS NOT NULL partial index. Neither has ever existed in any migration, including the original 0001_true_loners.sql that added the column — a genuine doc-vs-schema divergence dating to the initial build (2026-06-28), only discovered when a live sweep found 99%+ of session_id-populated rows orphaned (no matching identity_session row). Root cause, fully traced: identity_session is designed to be permanent audit history (no deleted_at, sessions end via ended_at, never hard-deleted — see the table's own note above), and every real production write path (createSession/recordLogin/recordLogout in IdentityService) respects that — zero application code ever deletes a session row. The orphans were 100% caused by a test fixture, identity-session.spec.ts, whose own teardown hard-deleted identity_session rows after every test run, violating the table's documented permanence and silently orphaning the session_id already written into the append-only identity_access_event ledger for that test. That test bug is now fixed (teardown no longer deletes identity_session). The FK itself is still deliberately left unenforced, not because deletion is expected, but because the ~1575 already-orphaned rows accumulated by the fixed test bug live permanently inside the hard append-only identity_access_event ledger (REVOKE + reject_append_only_mutation() trigger, Remediation Phase 1 Item 4) and cannot be backfilled or cleaned without bypassing that trigger — adding a real FK today would either fail immediately against that data or require a disruptive one-time superuser cleanup. Revisit once/if that cleanup is ever justified; until then, this is the correct, disclosed final state, not a deferred TODO. See docs/decisions/PROJECT_DECISIONS.md (Phase 1 Item 6 and the pre-Phase-2 confirmation pass) and docs/open-items/OPEN_ITEMS.md.

Rejected: (1) identity_session tenant-scoped — breaks cross-tenant session management and "log out all devices" UI; loses session context on tenant switch. (2) Defer session_id to Batch D — permanently loses session correlation for all events written between Pass 2 and Batch D deployment. (3) supabase_session_id as an enforced FK — DR-1 pattern applies; Supabase controls auth.sessions. (4) Enforcing session_id as a real FK now (2026-07-18) — would fail against the pre-existing orphaned rows described above; revisit only after a deliberate cleanup decision.

Guard: Service layer convention: write identity_session row at login BEFORE writing any identity_access_event rows for the session; pass session_id to all subsequent event writes during the session. Pre-session events set session_id = NULL. Test fixtures that create sessions must NOT hard-delete identity_session rows in teardown (mirrors this table's own "permanent audit history" design) — see identity-session.spec.ts's teardown for the corrected pattern.

---

DR-22: access_request approval seam + Batch D workflow engine attachment

Decision: access_request is a permanent tenant-scoped record for requesting role assignments or permission overrides. Single-approver flow (v1): approver_actor_id designates the reviewer; on approval, service layer creates the corresponding role_assignment or user_permission_override row. approval_workflow and approval_step (Post-v1 — deferred from Batch D; see completeness table) attach to access_request via a single nullable FK column added when built — no structural change to access_request at that time.

Why single-approver now, workflow engine post-v1: The SMB market (≤50 employees) rarely needs multi-step approval workflows — a manager approving a cashier's refund-access request is the dominant case. Building approval_workflow + approval_step now adds 2 tables and a routing algorithm for a use case no current customers have. The table is designed so the post-v1 engine attaches as an overlay: access_request.workflow_id UUID nullable FK → approval_workflow is added when the engine is built. When workflow_id IS NULL: single-approver v1 logic applies (use approver_actor_id). When workflow_id IS NOT NULL: the workflow engine drives the approval path to the same status machine transitions.

Post-v1 engine design (documented for future implementation): approval_workflow defines a named workflow template (steps, approver assignments per step, SLA). approval_step records each step's state for a specific access_request. The workflow engine evaluates step completion and transitions access_request.status when all required steps pass. access_request gains: workflow_id UUID nullable FK → approval_workflow, workflow_step_index integer nullable (current active step). These two columns are the only structural additions — all approval state lives in approval_step.

Why access_request is permanent (no deleted_at): Every approval or denial is part of the authorization audit trail. "Who requested what, when, who approved, and what was created" is a compliance-level question. Soft-deletion would allow hiding requests; hard-deletion loses the trail. The status machine captures the full lifecycle without deletion.

Rejected: (1) Build approval_workflow + approval_step now — premature for SMB; adds 2 tables and routing engine with zero near-term customers. (2) Embed approval steps as JSONB on access_request — loses relational structure; no FK integrity on approver IDs; no independent lifecycle per step. (3) Soft-delete access_request — hides authorization audit trail; permanent record is the correct pattern.

Guard: access_request rows are immutable after creation except for status, approver_actor_id, reviewed_by_actor_id, reviewed_at, and review_note. No other column may be updated post-insert. Both table CHECKs (request_type consistency and scope consistency) must not be dropped. When Batch D adds workflow_id, it must be nullable to preserve v1 single-approver compatibility. Service layer must write identity_access_event rows for each lifecycle transition using the 5 access_request event types added to the event_type CHECK in Batch B Pass 2 (access_request_submitted, access_request_approved, access_request_denied, access_request_withdrawn, access_request_expired).

---

DR-14: actor_group naming — reserved-word avoidance

Decision: The group table is named actor_group (and its join table actor_group_member), not group / group_member.

Why: group is a reserved word in SQL and Postgres. Using it as a bare table name requires double-quoting everywhere ("group"); a missed quote causes a syntax error or silently references the wrong token. Prefixing with actor_ eliminates quoting requirements and makes the table's domain explicit.

Rejected: group — reserved word; double-quoting requirement is an ongoing maintenance hazard. team — too narrow; the table represents departments, teams, and custom groupings, not just teams.

Guard: Always reference as identity.actor_group (schema-qualified) in raw SQL. Drizzle ORM uses pgSchema('identity').table('actor_group') — no quoting needed in ORM code. Reserved-word collisions only surface in unqualified raw SQL; schema qualification avoids them entirely.

---

---

DR-23: service_account + api_key — machine identity and credential model

Decision: Machine principals are modeled as service_account detail rows under the polymorphic actor root (shared PK). Credentials are stored in api_key with hash-only storage (key_hash + key_prefix). api_key references service_account only — agents do not use api_key. service_account.client_id is system-generated (not admin-editable).

Why hash-only + prefix: The raw API key is generated by the service layer, returned to the caller exactly once, and never stored. key_hash (bcrypt / SHA-256) allows verification without plaintext storage — same pattern as invitation.token_hash. key_prefix (first 12 chars, stored plaintext) enables display ("which key am I revoking?") and auth fast-path candidate narrowing without exposing the secret.

Why api_key → service_account only (not agents): Agents operate as internal service-layer components — they act via intra-service calls, not external API authentication. Giving agents API keys would create an unnecessary external credential surface and blur the model. If an agent ever needs external credentials (e.g., calling a third-party API), that belongs in the integrations schema credential store, not here.

Why client_id on service_account (not api_key): client_id is the public identity of the service account — the "who is this caller" half of OAuth2 client credentials. It belongs on the identity row, not on an individual key. A service account may have multiple keys; the client_id is fixed and shared across all of them.

Rejected: (1) actor_id → actor on api_key (covering both service accounts and agents) — agents don't need external keys; conflating the two adds attack surface. (2) Plaintext key storage — security baseline violation. (3) client_id on api_key instead of service_account — client_id is identity, not credential.

Guard: api_key.key_hash must never be logged, serialized into audit records, or returned via API after creation. Service layer returns the raw key exactly once and immediately discards it. client_id must be system-generated at service-account creation time — never settable by tenant admin. Auto-rotation (key expiry + auto-renewal) deferred to Batch D (tenant_security_policy) — track in OPEN_ITEMS.

---

DR-24: agent_identity — lookup table for agent_type, no mandatory owner, shared PK

Decision: agent_identity is a shared-PK detail under actor (same pattern as identity_user and service_account). Agent type is a FK → agent_type_catalog (a global reference table), not a CHECK constraint. Agents act independently — created_by_actor_id is nullable (optional audit attribution, not mandatory ownership).

Why lookup table over CHECK constraint: A CHECK on agent_type text would require a schema migration (DDL ALTER TABLE) every time Vrida adds a new agent type. agent_type_catalog makes adding types a seed-data operation (INSERT a row, no DDL). This matters because agent types are expected to grow as Vrida expands its AI capability plane. The same pattern applies to agent_skill.category — which does use a CHECK because categories are a small, slow-growing organizational grouping, not a rapidly expanding capability catalog.

Why no mandatory owner: Agents are organizational assets — the tenant owns the agent, not any individual user. Mandating an owner creates operational friction (owner leaves the company → orphaned agent) with no security benefit (tenants manage agents via tenant-admin roles). created_by_actor_id is retained as nullable for audit traceability.

Why shared PK: Identical reasoning to DR-11 (identity_user). Zero existing data; shared PK eliminates a separate actor_id FK column, makes joins trivial, and follows the established class-table-inheritance pattern.

Rejected: (1) CHECK constraint on agent_type — DDL migration per new type. (2) Mandatory owner_actor_id — operational burden, no security benefit, contradicts Decision 2. (3) Separate actor_id FK column — adds a column, complicates joins, no benefit.

Guard: Insertion order is always actor → agent_identity. agent_type_catalog is writable only via service_role and seed migration. agent_identity.config must document its shape at implementation time — never a bare JSONB. Agent execution history (inputs/outputs/token cost per run) belongs in the ai schema, not on agent_identity.

---

DR-25: agent_skill — competency separate from permissions, per-instance, Vrida-provisioned

SUPERSEDED (2026-07-17, Phase 6 reopen) — identity.agent_skill and identity.agent_skill_assignment were DROPPED; the underlying decision (skills are orthogonal to permissions, assigned per-agent-instance, Vrida-provisioned) still holds and now lives in agents.skill_definition/skill_version/agent_skill_assignment at SKILL VERSION granularity. Retained here as the historical record of the original design reasoning. See the "Removed" table-level section above and docs/database/schema_docs/agents.md.

Decision: AI agent competencies are represented by a separate agent_skill catalog (global reference, Vrida-defined) and agent_skill_assignment per-instance join table. Skills are orthogonal to permissions: a skill declares what an agent is capable of doing (competency); a role assignment grants what an agent is authorized to do (access). Assignments are per-agent-instance (not per-agent-type) and are created by Vrida's provisioning service — not by tenant admins.

Why skills separate from permissions: The skill layer answers "can this agent execute this task technically?" The permission layer answers "is this agent authorized to do so in this tenant?" Both are necessary and independent. Merging them would force Vrida to define a permission code per AI capability — coupling the skill catalog to the permission catalog and making both harder to evolve independently.

Why per-instance (not per-type): Different instances of the same agent type could carry different skills at different versions (e.g., an early instance with v1 skills vs. a newer instance with v2). Per-instance rows enable per-instance audit ("when was this skill assigned, by whom?") and revocation without affecting other instances.

Why Vrida-provisioned (not tenant-assignable): Skills are technical capability declarations. A tenant admin granting a reconciliation skill to a reporting agent creates an invalid configuration. Vrida's provisioning service seeds the correct skills at agent_identity creation time based on the agent type. Tenants assign roles — not skills.

Rejected: (1) Skill as a permission code — conflates authorization with capability. (2) Per-type assignment — loses per-instance history; one instance's revocation affects all. (3) Tenant-assignable skills — capability configuration is Vrida's domain.

Guard: agent_skill writable only via service_role and seed migration. agent_skill_assignment rows created only by Vrida's provisioning service (vrida_seed / provisioning) or platform admin override. Tenant admin UI must not expose skill assignment controls. UNIQUE partial index on (agent_id, agent_skill_id) WHERE deleted_at IS NULL prevents duplicate active skills per agent instance.

---

DR-26: tenant_user trigger — DB-enforced human-only membership

Decision: tenant_user.actor_id must reference an actor with actor_type = 'user'. This invariant is enforced by DB trigger trg_tenant_user_actor_type_check — a BEFORE INSERT OR UPDATE on identity.tenant_user that reads identity.actor.actor_type for NEW.actor_id and raises EXCEPTION 'actor_type must be user to join tenant_user' if the value is not 'user'. Non-human actors use their own detail-table tenant_id for tenant scoping and never receive tenant_user rows.

Why trigger (not CHECK): Postgres CHECK constraints are row-scoped — they cannot subquery other rows. CHECK (actor_type = 'user') would require actor_type to be a column on tenant_user, violating the polymorphic actor design (actor_type lives on actor). A trigger reads identity.actor and enforces the invariant at the DB level — no writer (application code, migration, admin SQL) can bypass it.

Why trigger (not app-enforced only): The invariant was app-enforced since Batch A. Batch C introduces two new non-user actor types (service_account, agent). With three possible non-user types, app-level enforcement requires discipline across every code path that creates tenant_user rows. A trigger provides unforgeable protection.

Why now: The Batch A deferred-CHECK note specified "when service_account / agent_identity exist." Those tables now exist. The trigger belongs in the Batch C migration alongside the new detail tables.

This is the first trigger in the identity schema — establishes the convention documented in Design Patterns Summary → Triggers.

Rejected: (1) DB CHECK subquerying actor.actor_type — not valid Postgres syntax. (2) App-enforced only — bypassable by direct SQL. (3) Replicating actor_type as a generated column on tenant_user — redundant data; drift risk; adds a column to a locked table.

Guard: trg_tenant_user_actor_type_check must fire on BOTH INSERT and UPDATE. Must be created in the Batch C migration. May not be dropped without a logged design decision. All future identity-schema triggers are listed in Design Patterns Summary → Triggers.

---

DR-27: tenant_security_policy v1 knob scope — 4 knobs, 4 deferred

Decision: tenant_security_policy ships with 4 columns in v1: session_timeout_minutes, mfa_required, max_concurrent_sessions, and api_key_rotation_days. Four additional enterprise knobs (ip_allowlist, require_reauth_for_sensitive, max_session_duration_minutes, password_expiry_days_override) are deferred — not added to the schema now.

Why: Vrida's primary market is US SMB plant nurseries (typically ≤50 staff). The 4 v1 knobs cover the real security needs of this segment: session idle-timeout control, per-tenant MFA enforcement, credential-sharing prevention via concurrent session limits, and API key rotation policy. The 4 deferred knobs require either enterprise IT context (ip_allowlist needs CIDR matching, VPN edge-case handling, IPv6 support) or currently undefined prerequisites (require_reauth_for_sensitive requires a "sensitive action" taxonomy not yet modeled; max_session_duration_minutes and password_expiry_days_override address compliance frameworks not yet required by any confirmed customer).

Rejected: Building all 8 knobs upfront. Rejected because: (1) over-engineering for SMB v1; (2) deferred knobs have undefined service-layer dependencies; (3) ip_allowlist cidr[] would need matching logic, UI, and VPN guidance that adds significant scope with no confirmed SMB demand.

Guard: ip_allowlist must not be added to the schema until the enterprise tier is designed and the service-layer matching logic (CIDR evaluation on inbound request IP) is specified. Do not add as JSONB as a workaround — if added, use cidr[] (PostgreSQL native CIDR array). The other 3 deferred knobs each have their own prerequisite logged in OPEN_ITEMS.

---

DR-28: mfa_required reconciliation — global floor + per-tenant additive

Decision: tenant_security_policy.mfa_required is nullable. The effective MFA requirement for a tenant is resolved as: effective = password_policy.mfa_required OR COALESCE(tenant_security_policy.mfa_required, false). A tenant with mfa_required = true forces MFA for its users even if the global policy is false. A tenant with mfa_required = NULL or false does not lower the MFA requirement if the global policy is true. A tenant cannot opt its users out of Vrida-wide MFA.

Why: Security posture must have a global floor. If Vrida enables MFA platform-wide (breach response, regulatory requirement, subscription-tier upgrade), no individual tenant configuration should override it. The OR logic ensures the stricter of the two settings always wins. Nullable mfa_required (NULL = inherit global) avoids the misleading case where a tenant's explicit false silently disables global true.

Rejected: COALESCE(tenant_security_policy.mfa_required, password_policy.mfa_required) — tenant fully overrides global. Rejected because a tenant with mfa_required = false would silently disable Vrida-wide MFA for their users, creating a security bypass if the global policy is later set to true.

Guard: Service layer must read BOTH password_policy.mfa_required AND tenant_security_policy.mfa_required and apply the OR resolution. Reading only tenant_security_policy.mfa_required (skipping global) is a security defect. The resolution must be documented in IdentityService.

---

DR-29: No consent_record in identity schema (v1)

Decision: consent_record is not built as part of the identity schema. The Batch D investigation confirmed no identity-specific consent gap exists for the v1 US SMB target market.

Why: Three distinct consent domains exist in Vrida: (1) end-customer/shopper consent — owned by the crm schema (consent log); (2) tenant-organization platform legal consent — covered by platform.agreement_acceptance (ToS, MSA, DPA, privacy_policy records); (3) individual staff/actor consent to data processing. Domain 3 is the candidate for an identity consent_record, but for US SMB nurseries the employment relationship provides the legal basis for staff data processing (GDPR Article 6(1)(b); US equivalents). GDPR Article 7 explicit per-user consent tracking is not required until either an EU customer or an enterprise customer with a specific consent compliance obligation is onboarded. Building the table now without a consumer is premature schema with no enforcing obligation.

Rejected: Building consent_record for AI-agent consent (agents acting on behalf of a user). Rejected because the AI Capability Plane governs agent authority via A-series controls (ON/OFF, authority boundaries, human-in-the-loop) — this is a governance/configuration mechanism, not a per-user consent record in the database sense.

Guard: Add consent_record to the identity schema when: (1) the first EU-based customer requires GDPR Article 7 explicit per-user consent records for staff, OR (2) an enterprise customer requires per-user consent tracking as a contractual compliance obligation. At that point, design the table within the identity schema, target platform.agreement_acceptance as the precedent for legal-agreement records, and run the full Section 4 audit. Do not build speculatively.

---

Remediation Phase 1 (2026-07-08)

Cross-cutting remediation pass closing gaps a senior-architect review found; no column/table count change (35 tables / 396 cols unchanged). See PROJECT_DECISIONS #37 for the full cross-module record.

• agent_duty_grant gained chk_agent_duty_grant_granted_by_required_and_distinct — CHECK (granted_by_actor_id IS NOT NULL AND granted_by_actor_id <> agent_identity_id). The A5 authority-passport self-issue guard: closes a gap where an agent could hold a duty grant with no recorded human granter, or self-issue its own grant.
• identity_access_event (previously "no RLS — access via service_role only" by original design) gained RLS enabled + a tenant_isolation policy identical in shape to its sibling tables. This phase's own blanket GRANT to the authenticated role invalidated the original service_role-only premise, so the table needed the same tenant-isolation policy every other tenant-scoped identity table already carries.

Remediation Phase 2 (2026-07-08)

Cross-cutting remediation pass changing PK-generation strategy on this module's append-only tables; no column/table count change (35 tables / 396 cols unchanged). See PROJECT_DECISIONS #38 for the full cross-module record.

• identity_access_event and 3 catalog-wiring join tables — permission_group_permission, role_permission, role_template_permission_group, sod_rule_permission — had their id column DEFAULT changed from gen_random_uuid() to platform.uuid_generate_v7() (Remediation Phase 2, Item 6). All 4 are explicitly self-documented in their own Drizzle source as "Append-only (no updated_at, no deleted_at)" — a genuine catalog-wiring pattern distinct from a hot ledger, but still benefiting from time-ordered PK insertion locality.
• Why: UUIDv7 is time-ordered (embeds a millisecond timestamp in its high bits), so rows insert in roughly chronological key order. This keeps future time-range partitioning possible on these append-only tables without a PK rewrite — a rewrite that becomes impossible once data has already landed on a random UUIDv4 PK. DEFAULT-only change; no existing row values are altered.

Remediation Phase 3 (2026-07-08)

Cross-cutting remediation pass adding the agent kill-switch (Item 12); 4 new columns on agent_identity → 35 tables / 400 cols (up from 396). See PROJECT_DECISIONS #39 for the full cross-module record. Migration: packages/db/migrations/20260708250000_phase3_item12_agent_kill_switch.sql.

• agent_identity gained status (text, NOT NULL DEFAULT 'active'; CHECK chk_agent_identity_status IN ('active','suspended','killed')), suspended_at (timestamptz, nullable), suspended_by_actor_id (uuid, nullable, FK → identity.actor), and suspension_reason (text, nullable), plus a new table CHECK chk_agent_identity_status_suspended_at_consistency — suspended_at is populated if and only if status != 'active'. Additive and backward-compatible: agent_identity had 247 live rows at build time; status defaults 'active' (identical to every existing row's implicit behavior today), so the new consistency CHECK is satisfied by every existing row by construction of the DEFAULT.

• suspended_by_actor_id is deliberately nullable even for a non-'active' row — a system-initiated suspension (e.g. cascading from a tenant-level shutoff or a platform.ai_credit_account exhaustion event) may have no specific human actor to attribute, unlike a human-initiated suspend/kill which should populate it.

• 'killed' is service-layer terminal, not DB-enforced terminal. No trigger blocks a killed → active transition — that judgment is deliberately left to IdentityService (not yet built for this column), not baked into schema. Treat any documentation or downstream reasoning that assumes DB-level irreversibility as incorrect.

• This is one link in a precedence chain of independently-owned half-mechanisms, evaluated in order, that predate this column:

  1. tenant status (an entire tenant can be off)
  2. platform.ai_credit_account status (a tenant's AI budget can be exhausted/suspended) — platform-owned; note there is no ai.ai_credit_account table, a citation corrected before this item was built
  3. this agent's own agent_identity.status (NEW — this item)
  4. identity.agent_duty_grant (per-permission authority, can be revoked/expired)
  5. identity.agent_skill_assignment (what the agent is allowed to attempt) — relocated 2026-07-17 to agents.agent_skill_assignment (Phase 6 reopen); the link in the chain is unchanged, only its schema location moved
  6. role assignment
  7. feature flags

  No single mechanism was authoritative before this column existed, and this column does not make the chain authoritative alone either — it fills the missing top-of-agent link, still evaluated in order with the rest of the chain. Every module that consumes identity.agent_duty_grant (crm, inventory, pos, orders, purchasing, tax, billing, payments, admin) is affected by this precedence chain.

Header/Line Remediation — Fix #3 (2026-07-10)

Identity's 4th reopen, and Identity's own contribution to the second batch of the coordinated, 6-module "Header/Line Remediation" design (vrida-header-line-remediation-design-2026-07-10.md, based on vrida-header-line-pattern-audit-2026-07-10.md), already run through an independent adversarial verification pass before any of it was built. Inventory landed first in this batch (fixes #5/#9, PROJECT_DECISIONS #49); Orders second (fix #6, PROJECT_DECISIONS #50); Billing third (fix #2, PROJECT_DECISIONS #51); identity is fourth. A separate, not-yet-docs'd "bare-FK fixes" pass touches other modules under this same effort but is out of scope here.

• New table identity.invitation_site_assignment (6 cols) — pre-acceptance staging of intended site access, mirroring user_site_assignment's own shape. See the table's own section above for full column/constraint detail.
• New column identity.user_site_assignment.created_from_invitation_site_assignment_id — nullable composite FK reciprocal-traceability column, corrected from the original design draft's bare-FK mistake (the same bug class independently caught on purchasing.vendor_credit_line/billing.ar_charge_line in the prior batch) before this build even started.
• Prerequisite UNIQUE(id, tenant_id) added to identity.invitation — confirmed missing before this migration.
• identity.invitation.site_assignments (JSONB) deprecated in place — comment only, zero data migration since identity.invitation had zero live rows at build time.
• Both mandatory pre-migration audits are the most consequential part of this reopen (full detail in invitation_site_assignment's own section above): (1) the JSONB-inspection audit found zero rows to convert; (2) the orphan-check audit found ONE live orphaned user_site_assignment.site_id row — this is why the optional opportunistic 3-column multi_loc.site FK-wiring bundle was deliberately NOT taken this pass. Flagged for a human decision, not resolved. See docs/open-items/OPEN_ITEMS.md and PROJECT_DECISIONS.md #52. RESOLVED the same day — see PROJECT_DECISIONS #54.

Column-count impact. +1 table / +6 cols (the new table) + +1 col (user_site_assignment's new column) → 37 tables / 422 cols → 38 tables / 429 cols.

Independent verification (separate agent, adversarial, live-DB-checked) — pasted, attributed. Described in its own report as checking "exactly the kind of reopen where scope creep or a rushed shortcut is most likely to slip through unnoticed," given this is Identity's weakest-justified 4th reopen. Findings: PASS on every single check, zero findings of concern. Schema shape confirmed exactly as claimed, including confirming site_id genuinely has no FK at all (not accidentally added, not accidentally missing something else). Both composite FKs confirmed genuinely 2-column. Both pre-migration audits independently re-verified live (the exact same orphan row ID/site_id reconfirmed; the exact zero-count on tenant_user reconfirmed). The "optional bundle was NOT built" claim was independently verified directly — queried all 28 FKs referencing multi_loc.site codebase-wide; none touch any of the 3 columns in question. All 5 live-reproduction scenarios independently reproduced in a fresh two-tenant fixture built entirely from scratch (not reusing any build-time IDs). Grants match the established sibling-table pattern exactly. The deprecation comment was confirmed to have landed on the right column with the column itself otherwise unchanged. A full scope-discipline review of the entire migration file's DDL inventory found zero scope creep — the migration touches exactly what fix #3 claims and nothing more. The only gap noted: no regression tests existed AT THE TIME of that verification pass (since resolved — see Tests below).

Tests. apps/api/src/identity/__tests__/identity-governance.spec.ts — new Group E (5 tests, E1–E5): valid same-tenant invitation_site_assignment insert; cross-tenant invitation_id rejected; valid same-tenant created_from_invitation_site_assignment_id on user_site_assignment; cross-tenant rejected; NULL succeeds. File went 21 → 26 tests.

Migration: packages/db/migrations/20260710060000_headerline_identity_fix3.sql. Schema files: packages/db/src/schema/identity/governance.ts (new invitationSiteAssignment export; invitation gains unique('invitation_id_tenant_id_unique')), packages/db/src/schema/identity/assignment.ts (userSiteAssignment gains created_from_invitation_site_assignment_id + its composite FK). See PROJECT_DECISIONS #52.

Header/Line Remediation — Follow-up: multi_loc.site FK Wiring (2026-07-10, Identity's 5th Reopen)

Direct follow-up to the human decision fix #3 above flagged. Resolved the same day.

Orphan resolved. The blocking row (user_site_assignment id 4cd177f7-6f3a-42aa-a977-6fe0b34e89e6) was investigated: its tenant had exactly 5 rows total anywhere in the schema, all boilerplate/system rows — isolated dev-seed fixture junk, confirmed via a DB-wide tenant-isolation scan (not just the original claim). DELETED (site_id is NOT NULL, nulling wasn't an option).

3 of the bundle's columns wired. user_site_assignment.site_id, tenant_user.default_site_id, invitation_site_assignment.site_id are now real composite FKs → multi_loc.site(id, tenant_id). multi_loc.site itself gained the prerequisite UNIQUE(id, tenant_id) (site_id_tenant_id_unique) — multi_loc's 1st reopen since its 2026-06-29 lock.

2 columns confirmed still genuinely deferred (independently verified via pg_constraint/\d to carry zero FK): platform.tenant.primary_site_id (a different module's reopen) and identity.user_permission_override.scope_id (not part of this bundle). A 4th bare column surfaced during this entry's own docs pass, not previously connected to this bundle in OPEN_ITEMS or PROJECT_DECISIONS even though this doc's own Cross-phase forward-reference FKs list (below) had tracked it all along: identity.access_request.requested_scope_id. See OPEN_ITEMS for its own dedicated row.

Independent verification (separate agent, adversarial, live-DB-checked) — pasted, attributed. Overall verdict: CLEAN — zero findings. Gave particular scrutiny to the irreversible DELETE, independently re-verifying tenant isolation via its own DB-wide scan. All 4 new constraints confirmed correctly composite via pg_constraint, no leftover bare duplicates. Both sibling deferred columns confirmed genuinely untouched. All 9 live-reproduction scenarios (same-tenant/cross-tenant/orphan × 3 columns) independently reconstructed from scratch and reproduced exactly as claimed, including one built specifically to isolate the new invitation_site_assignment_site_tenant_fkey from the pre-existing invitation_id FK. Full suite 873/873 (second run reproduced the known, pre-existing admin-tenants.spec.ts pagination race — confirmed clean in isolation, not a new regression). Grants on all 4 touched tables confirmed unchanged. One bonus finding: CROSS_MODULE_CONTRACTS.md also needed the same correction (folded into this pass).

Tests. permission-engine.spec.ts and identity-governance.spec.ts (Group E) both required new insertSite() helpers since they'd used unenforced randomUUID() placeholders for site_id — 36/36 passing.

Migration: packages/db/migrations/20260710080000_headerline_multiloc_site_fk_wiring.sql. Column-count impact: none — pure constraint-shape change. See PROJECT_DECISIONS #54.

Phase 6 (2026-07-17, Identity's 6th reopen) — agent_skill drop + agent_reader role

Phase 6 of 6 of the agents-v2/v3 build — closes the entire build (Phase 1 platform, Phase 2 ai, Phase 3 semantics, Phase 4 signals, Phase 5 agents, Phase 6 this reopen). Two independent pieces bundled into one reopen:

• identity.agent_skill (9 cols) and identity.agent_skill_assignment (10 cols) DROPPED entirely — 2 tables / 19 cols, taking the module from 38 tables / 429 cols to 36 tables / 410 cols. Migration: packages/db/migrations/20260717000000_identity_reopen_drop_legacy_agent_skill.sql. Pre-migration audit: agent_skill_assignment had 0 live rows; agent_skill had 13 live rows, all test debris (test.skill.%/ti3.skill.% patterns), cleaned up in the migration, not real data lost. Superseded by agents.skill_definition/agents.skill_version/agents.agent_skill_assignment (built Phase 5) — skill assignment moved from skill-identity granularity to SKILL VERSION granularity. IdentityService's 4 skill methods (listSkillCatalog, assignSkillToAgent, removeSkillFromAgent, listAgentSkillAssignments, plus authorizeAgentAction's skill-check half) were rewritten in apps/api/src/identity/identity.service.ts to join through agents.skill_version/agents.skill_definition instead; callers now pass skillVersionId (renamed from skillId), and listAgentSkillAssignments's return field is now skillVersionId. listSkillCatalog's return shape changed to {id, code, name, lifecycleStatus} — category/moduleCode dropped, no equivalent on the flatter agents.skill_definition catalog. Zero controllers/DTOs referenced any of these 4 methods at build time (grep-confirmed) — no live HTTP consumer affected.
• New agent_reader Postgres role (NOLOGIN NOINHERIT) — mirrors consumer_authenticated's shape exactly, granted to authenticator. Migration: packages/db/migrations/20260717000001_agent_reader_role.sql. No table/column impact on identity itself — this is glue infrastructure (the role + a new agentReaderDB() connection helper in packages/db/src/client.ts, mirroring consumerDB()) that grants a future AgentsService read-only, tenant-scoped access into files.document_chunk/document_index and signals's get_feature_as_of/get_forecast_as_of/get_anomaly_score_as_of functions — see docs/database/schema_docs/files.md and docs/database/schema_docs/signals.md for the grant/RLS-policy detail on those schemas. A real gap was found and fixed during this build: the pre-existing authenticated-scoped RLS policies on document_chunk/document_index do NOT automatically extend to agent_reader (Postgres role-scoped policies apply only to the exact role named) — closed with 2 new dedicated SELECT-only policies. Also added: an ESLint rule (apps/api/eslint.config.mjs, scoped to src/agents/**/*.ts) banning adminDb/getAdminDb/tenantDB imports and raw set_config(...) calls from agent-execution code paths, verified firing via a temporary probe file (no src/agents/ directory exists in the committed tree yet — no AgentsService exists to violate it).
• Honest disclosure: agent_reader/agentReaderDB() have ZERO real call sites today (no AgentsService or other agent-execution read path exists yet) — logged as a new OPEN_ITEMS row, not silently treated as fully closed.
• Full apps/api suite green at 1205/1205 (up from 1201 — 4 new agent_reader regression tests in apps/api/src/database/__tests__/rls-cross-tenant.spec.ts).

See PROJECT_DECISIONS.md #67 for the full narrative, including the design-phase reasoning for choosing SKILL VERSION-granularity assignment and the agent_reader guard live-reproduction detail.

Other patterns

• All PKs: UUID with default gen_random_uuid() (except identity_user, service_account, agent_identity which use shared PK from identity.actor)
• All enums: stored as text with CHECK constraints
• All timestamps: timestamptz, stored UTC

---

Identity — Schema Design Completeness (A–D)

Batch D is the final schema batch. After Batch D locks, the identity schema design is complete for v1. The following items are confirmed post-schema — they are build-phase, future-tier, or dependent-module work, NOT schema gaps:

Category	Item	Status
Build-phase	Platform deferred FK constraints (8 columns → identity.actor / identity.identity_user) — FK ALTER TABLE statements emitted in Phase 3 identity migration	Added at migration time; columns already exist in platform schema
Build-phase	identity_session idle-timeout sweep + tenant_security_policy.session_timeout_minutes enforcement — background sweep in IdentityService	Service-layer implementation; schema complete
Build-phase	api_key auto-rotation sweep using tenant_security_policy.api_key_rotation_days	Service-layer implementation; schema complete
Future / when built	identity_access_event.event_type missing scim_user_provisioned / scim_user_deprovisioned — deferred until SCIM API is built	OPEN_ITEMS — trigger: when SCIM is built
Future / when built	audit.audit_log actor generalization — generalize to identity.actor + add actor_type column	OPEN_ITEMS — trigger: audit schema design/build phase; trigger condition met (actor model locked)
Future / when built	Approval workflow engine (approval_workflow / approval_step) — access_request attachment point defined in DR-22	Post-v1; attachment columns on access_request documented in intent doc
Future / enterprise	tenant_security_policy enterprise knobs: ip_allowlist, require_reauth_for_sensitive, max_session_duration_minutes, password_expiry_days_override	OPEN_ITEMS — trigger: enterprise tier / confirmed demand
Future / EU-enterprise	consent_record — per-user GDPR Article 7 explicit consent tracking	OPEN_ITEMS — trigger: first EU/enterprise customer requiring per-user consent records (DR-29)
Future / AI module	ai.agent_execution log — per-run agent execution record	Belongs in ai schema, not identity (DR-24)