Identity Expansion — Design Intent
Captured: 2026-06-28
Status: IDENTITY SCHEMA DESIGN COMPLETE — all batches A–D locked 2026-06-28. 34 tables / 365 cols. Build phase remains: module spec, migration, IdentityService, pages. Batch A LOCKED 2026-06-28 — Pass 1 (actor model) + Pass 2 (groups, role inheritance, permission_group bundles). 18 tables / 202 cols. Batch B Pass 1 LOCKED 2026-06-28 — role management core: role_assignment (OQ-4 resolved: replaces tenant_user.role_id), role_permission_group (DR-13 wiring delivered), role_template, role_template_permission_group. 22 tables / 233 cols. Batch B Pass 2 LOCKED 2026-06-28 — ALL OF BATCH B COMPLETE — governance, session, access management: sod_rule, sod_rule_permission, sod_violation, identity_session, access_request. Also: identity_access_event.session_id (FK correlation) + identity_access_event.event_type +9 CHECK values (4 SoD + 5 access_request lifecycle; 14→23) (locked-table touches). 27 tables / 295 cols. Batch C LOCKED 2026-06-28 — machine + AI-agent identity: agent_type_catalog (+1 beyond the original 5-table plan, per design decision to use FK not CHECK for agent_type), service_account (shared PK), api_key, agent_identity (shared PK), agent_skill, agent_skill_assignment; trigger trg_tenant_user_actor_type_check (DB-enforced human-only membership; closes Batch A deferred CHECK; first trigger in identity schema). 33 tables / 357 cols. Batch D LOCKED 2026-06-28 — tenant_security_policy (per-tenant session timeout, MFA enforcement, concurrent-session cap, api-key rotation policy; 4 enterprise knobs deferred); consent_record NOT BUILT (investigated: no v1 gap — see DR-29 in identity.md). 34 tables / 365 cols. SCHEMA DESIGN COMPLETE.
Why this doc exists: A multi-session planning conversation produced ~10 design decisions that must survive into the build. This file is the durable record of intent and rationale so every design and build turn starts from the same ground truth.
Why the expansion
The pre-expansion identity schema (13 tables, 164 cols) was foundationally correct but thin — it models staff authN/authZ for a single-business ERP. (Batch A Pass 1 LOCKED 2026-06-28: now 14 tables / 170 cols with polymorphic actor root.) Two forces make expansion necessary BEFORE building, not after:
Top-tier competitive requirement. The product goal is ERP-grade identity (Workday/Salesforce/SAP territory), not basic RBAC. Benchmarking 12 identified gaps against Salesforce Permission Sets, Workday RBAC, SAP roles, Okta + AWS IAM policy engine, NIST SP 800-63B/SP 800-207 (ZTA), SCIM 2.0, SOC 2 Type II showed that the locked schema would require a painful retrofit mid-build.
AI agents operating the ERP near-term. Agents are not a future phase — they are part of the current identity expansion. An agent that creates a PO or runs a report needs to exist as a first-class actor with roles, permissions, and audit attribution, using the identical mechanism as a human user. Bolting machine actors onto a human-only schema after the fact is the most expensive path; designing for them now costs almost nothing.
Sequencing rationale: This expansion is INTERLEAVED with finishing identity (not deferred to a later module). The actor refactor touches platform and audit — both locked but with no real data yet, making this the cheapest window.
Decision 1 — Full polymorphic actor model (BIGGEST DECISION)
Decision: Replace the flat identity_user-as-actor model with a polymorphic actor table (actor_type: 'user' | 'service_account' | 'agent', plus status). identity_user becomes the human-detail table linked to actor. service_account and agent_identity are the other detail tables. All three actor types share a single PK namespace.
Why: The 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.
Scope — AGGRESSIVE REFACTOR, not deferred addition:
All ~20 FK columns that currently reference (or will reference) identity.identity_user across identity, platform, and audit will reference identity.actor instead:
Identity columns (currently reference identity_user):
tenant_user.user_idtenant_user.invited_by_user_idinvitation.invited_by_user_id,invitation.accepted_by_user_id,invitation.revoked_by_user_idsupport_access_grant.vrida_user_id,support_access_grant.granted_by_user_id,support_access_grant.revoked_by_user_iduser_permission_override.granted_by_user_ididentity_access_event.user_id,identity_access_event.actor_user_id,identity_access_event.target_user_id
Platform deferred FKs (8 columns — created as plain UUIDs in Phase 1; constraints added at Phase 3 identity migration):
Outcome (Batch A actor model, 2026-06-28): The original intent was all 8 →
identity.actor. The final outcome is a 4+4 split based on semantic meaning. 4 →identity.actor(any actor type may perform these operations):granted_by_user_id,completed_by_user_id,requested_by_user_id,actor_user_id. 4 →identity.identity_user(human-semantics records — legal, compliance, CS):identity_user_id,accepted_by_user_id,performed_by_user_id,operator_user_id. Authoritative split inPROJECT_DECISIONS.mdand the Cross-Phase FK table inidentity.md.
platform.tenant_contact.identity_user_id→identity.identity_userplatform.tenant_entitlement.granted_by_user_id→identity.actorplatform.agreement_acceptance.accepted_by_user_id→identity.identity_userplatform.tenant_setup_task.completed_by_user_id→identity.actorplatform.tenant_data_lifecycle.requested_by_user_id→identity.actorplatform.tenant_lifecycle_event.actor_user_id→identity.actorplatform.tenant_internal_activity.performed_by_user_id→identity.identity_userplatform.operator_audit_log.operator_user_id→identity.identity_user
Audit (when built):
audit.audit_log.actor_user_id→actor; addactor_typecolumn covering'user'|'service_account'|'agent'
Why aggressive (not additive):
- Platform has no real migration data yet — the deferred FK constraints have never been added. This is the exact window described in OPEN_ITEMS for closing them. The cost to do
→ identity_userand then→ actoris strictly higher than doing→ actornow. - If platform columns are wired to
identity_userand later need to accept service-account or agent actors, every platform query and service method needs a conditional branch. "Just add a nullableactor_idalongside the existinguser_id" is the architectural smell this decision avoids. - Audit integrity:
audit.audit_logis the central compliance log. Its actor attribution must handle all actor types from day one.
Rejected: Additive parallel columns (actor_user_id + optional actor_service_account_id per table). Rejected because: (1) resolution ambiguity — which column is authoritative when both are populated? (2) every query and display layer needs to handle both; (3) every future actor type adds another column; (4) the column-per-type pattern breaks down at scale.
Guard: identity_user continues to exist as the human-detail table — it is NOT deleted or replaced. The relationship to actor is shared PK / class table inheritance (OQ-1 resolution, overrides the original actor_id FK plan): identity_user.id IS actor.id. No separate actor_id column on identity_user. Insertion order: insert actor first (UUID generated there), then identity_user with the same UUID as its PK. Application code that resolves a human actor joins on the shared PK: JOIN identity.actor a ON a.id = u.id. FK columns in other tables that represent "who did this" point to identity.actor, not identity_user, except the 11 named semantic exceptions listed in DR-11 of identity.md.
Decision 2 — Agent identity as first-class actor
Decision: Build agent_identity as a first-class identity-layer table (model, version, framework, status), linked to actor. Agents are built in the current expansion batch, not a future phase.
Why: Near-term product vision has agents operating the ERP — creating POs, running reconciliation, flagging anomalies. An agent that acts on data must exist as a named, auditable principal with role-controlled permissions, not as a background process running as a service account. The semantic difference matters for compliance and tenant transparency: "the AI Purchasing Agent created this PO, acting with the Buyer role" is the audit story, not "an unknown process created this PO."
Skill model: Agents have two orthogonal capability axes:
- Roles/permissions (authorization — what the agent is allowed to do): same mechanism as humans. Agents are assigned roles via the same
role_assignmenttable;role_permissionrows determine access. - Skills (competencies — how the agent does a job):
agent_skilldefines named competencies (reconcile_inventory,generate_daily_report,flag_anomaly).agent_skill_assignmentmaps which skills an agent instance has.
Skills are VRIDA-DEFINED ONLY. Vrida ships agents with skills and their default permissions. Tenants CAN change an agent's role/permissions (e.g., restrict what it accesses). Tenants CANNOT add, remove, or define skills. This prevents tenants from creating misuse scenarios by redefining what a Vrida agent does.
Why skills are a separate layer from permissions: A skill is a capability description ("this agent knows how to reconcile inventory") while a permission is an access gate ("this agent is allowed to read stock records"). An agent with the reconcile_inventory skill still needs the inventory.item.read and inventory.stock.read permissions — the skill doesn't grant access, it declares competency.
Owner model: Agents act independently — no mandatory human owner. Every agent action is audit-attributed to the agent actor. The decision not to require an "owner user" is deliberate and diverges from current IETF agent-owner drafts. Rationale: in a multi-tenant ERP, agent ownership is organizational (the tenant owns the agent) not personal (a specific user owns it). A "responsible party" column may be added later if regulatory requirements emerge, but it is not in the current design.
Decision 3 — Role inheritance (single-parent)
Decision: Single-parent role inheritance via role.parent_role_id (nullable self-FK). A child role inherits all parent role permissions in addition to its own. The effective permission set for a role = own permissions UNION ancestor permissions, with deny from any ancestor or descendant overriding allow from any other.
Why single not multi-parent: Matches the org hierarchy model natural to retail staff (cashier ← supervisor ← manager ← owner). Multi-parent inheritance creates diamond inheritance problems (contradictory inherited effects) that require conflict-resolution rules more complex than the underlying use cases justify. Single-parent trees are deterministic and auditable.
Implementation: Resolved at query time by walking the parent_role_id chain (bounded depth, typically ≤4 levels). Permission resolution already fetches role_permission rows — adding ancestor traversal is a single recursive CTE on the role table.
Decision 4 — Permission groups (Vrida-defined bundles)
Decision: permission_group and permission_group_permission tables. A permission group is a named bundle of permissions (e.g., "Inventory Manager Bundle") that can be assigned to a role. VRIDA-DEFINED ONLY — tenants cannot create groups, only assign pre-defined groups to custom roles.
Why: Single-permission-at-a-time role setup is impractical for tenant admins. With 100+ permission codes across modules, "add the inventory read bundle" as a single role-assignment action is the usable pattern.
Why Vrida-defined only: Tenant-defined groups add complexity (groups of groups? group validation? naming conflicts?) with little incremental value. Vrida curates sensible bundles; tenants build custom roles by combining groups.
Decision 5 — Role templates
Decision: role_template table — Vrida-seeded templates for tenant custom role creation. A template defines a starting permission set; a tenant "clones" the template to create a tenant_custom role. The template is a static reference record; the cloned role is independent (changes to the template do not propagate to cloned roles).
Why: Without templates, creating a custom role requires adding permissions one by one from a list of 100+. Templates for common patterns (Floor Staff, Seasonal Cashier, Inventory Viewer) give tenant admins a productive starting point.
Decision 6 — Role assignment as a first-class table
Decision: role_assignment table — time-bounded role assignments with starts_at, ends_at (nullable = permanent), and assignment history. Replaces the current tenant_user.role_id single-column assignment.
Why: Seasonal nursery staffing is a core Vrida vertical use case. A seasonal worker should have the Cashier role from May 1 to September 30 without a manual role change on each date. The role_assignment table supports this natively; the current tenant_user.role_id does not.
History: Role assignments are never deleted (append-only). Expiry and replacement create new rows. This gives a complete "this user had this role from T1 to T2" history without querying identity_access_event.
Decision 7 — Separation of Duties: detect-and-flag, not block
Decision: sod_rule defines incompatible permission pairs (e.g., pos.sale.create + pos.sale.void on the same user). sod_violation records detected violations. Enforcement mode: DETECT-AND-FLAG, not block-on-assignment.
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. A hard block on assigning a conflicting permission would break legitimate multi-hat scenarios — the owner who is also the cashier who also approves refunds. The flag gives a compliance signal and audit trail without breaking operations. As tenants grow and formalize their access controls, the violation report becomes the compliance tool.
Why SoD at all for SMB: Even at 10-person nurseries, PO creation vs. PO approval is a live fraud vector. A Workday-tier product includes SoD; omitting it entirely would be a competitive gap against mid-market ERP.
Violations surface in audit: All sod_violation rows surface in the audit module compliance cluster, not inline during the operation that triggers them.
Decision 8 — Session table (Vrida-side shadow of Supabase sessions)
Decision: identity_session — a Vrida-side record of login sessions mirroring Supabase Auth session state. Captures: session start/end, device info, IP, user agent, last_active_at, revoked_at, revoked_by.
Why: Three concrete needs not addressable from Supabase Auth alone:
- "Log out all devices" management surface — Supabase Auth supports session revocation but does not expose a tenant-facing "active sessions" list via Vrida's own APIs. Vrida needs its own session rows to power this UI.
- Audit correlation — tie
identity_access_eventrows to a session context ("all events in this session").correlation_idis a text field; a proper FK toidentity_sessionis cleaner. - Session anomaly detection — concurrent session alerts, impossible travel detection, device trust enforcement.
Scope: Login/logout session lifecycle. Does NOT replace Supabase Auth session management (JWT generation, refresh, MFA enforcement remain Supabase's domain).
Decision 9 — Groups (team model + SCIM /Groups)
Decision: group + group_member tables. Groups represent teams or organizational units (e.g., "Purchasing Team," "Managers"). Groups can be assigned roles via the same role_assignment mechanism as individual actors. SCIM /Groups provisioning maps to this table.
Why: Two drivers: (1) team-level access management ("give the Purchasing Team the Buyer role" instead of assigning each person individually); (2) SCIM 2.0 requires a /Groups endpoint when enterprise IdPs provision users via directory sync.
Decision 10 — Per-tenant security policy
Decision: tenant_security_policy table — per-tenant configuration of session timeout, IP allowlist, MFA enforcement, password policy override (overrides the Vrida-wide password_policy), and maximum concurrent sessions.
Why: Enterprise-tier tenants need configurability. A single Vrida-wide password policy (DR-7 in the locked identity schema) is correct for Starter/Pro customers; Enterprise customers with IT teams expect to configure their own policies. The tenant_security_policy row is additive — it does not invalidate the global password_policy for tenants without one.
Decision 11 — Access governance (lighter for SMB)
Decision: access_request + approval_workflow + approval_step tables. An access request is a structured ask by a user for a role or permission; an approval workflow governs who must approve it and in what order.
Depth: Lighter than enterprise governance platforms (no multi-stage approval chains, no SLA escalation). Two-step max for initial build: requester → approver(s). Designed for the SMB context: a manager approving a cashier's request for refund access, not an enterprise IAM committee process.
Why: Provides an auditable path for role changes even in SMB. Especially important for PO approval and refund authorization scenarios where informal verbal approval currently leaves no record.
Decision 11a — Post-v1 workflow engine design (documented for OQ-B4 resolution)
Context: OQ-B4 resolved: access_request ships in Batch B Pass 2 with a single inline approver (approver_actor_id). approval_workflow and approval_step were originally planned for Batch D but deferred post-v1 when Batch D scope was finalized as tenant_security_policy only. This section documents the intended design so it is not lost. See OPEN_ITEMS and DR-22.
Post-v1 tables (to be designed when first multi-step approval use case arrives):
approval_workflow — a named multi-step workflow template. One row defines a reusable approval configuration: steps, approver assignments per step (role-based, actor-based, or dynamic), SLA per step. Vrida may ship global templates; tenants may define custom workflows. Global/mixed-scope pattern like role (nullable tenant_id).
approval_step — per-request step record. One row per step of an in-progress or completed workflow for a specific access_request. Columns: access_request_id, workflow_id, step_index, status (pending/approved/denied/skipped), approver_actor_id, reviewed_at, review_note. Append-only lifecycle.
Attachment to access_request: access_request gains two nullable columns when these tables are built — workflow_id UUID nullable FK → approval_workflow and workflow_step_index integer nullable (current active step). No other change to access_request structure. When workflow_id IS NULL: single-approver v1 flow. When workflow_id IS NOT NULL: workflow engine drives approval.
Activation pattern: when a tenant admin enables multi-step approval for a request_type, new access_request rows get workflow_id populated at creation time. Existing (pending) requests without workflow_id continue on the v1 single-approver path — no migration needed.
Why designed now: The attachment point design (two nullable columns on access_request) is frozen here so access_request is not reopened when the engine is eventually built. The interface is defined here; the implementation tables (approval_workflow, approval_step) are built post-v1.
Decision 12 — Consent record (RESOLVED — NOT BUILT)
RESOLVED 2026-06-28 — consent_record NOT BUILT. Investigation completed in Batch D: crm schema owns end-customer/shopper consent; platform.agreement_acceptance covers org-level platform legal consent; US SMB employment relationship (GDPR Art. 6(1)(b)) provides the legal basis for staff data processing — no v1 identity schema gap confirmed. See DR-29 in identity.md for the full decision rationale and build guard.
Build guard (from DR-29): 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. Tracked in OPEN_ITEMS.
Decision 13 — Data-access logging (GDPR/CCPA, sensitive resources)
Decision: Log access to PII/sensitive resources (who/what/when/purpose). NOT every read — only reads of designated sensitive resource types (customer PII, financial records, medical/personal data).
Implementation path: audit.audit_log already has a granularity_level column (full / sensitive / minimal). Sensitive-resource access logging writes audit_log rows at granularity_level='sensitive'. No new table needed; this is a service-layer convention (IdentityService / InventoryService / CRMService emit audit rows on sensitive reads) backed by the existing audit_log structure.
Why not a separate table: The audit module's audit_log integrity hash-chain applies to these rows by design. A separate access-log table outside the chain would lose the tamper-evidence property.
Batch sequencing
Four batches, each processed as a complete pipeline pass (design → Section 4 audit → lock → all related docs updated in same pass):
| Batch | Scope | Rationale |
|---|---|---|
| A — Foundation | actor, group, group_member, role inheritance (role.parent_role_id), permission_group + permission_group_permission |
Required before anything else. Every other batch builds on the polymorphic actor. Role inheritance and groups are structural and affect how role_assignment resolves. |
| B — Roles & Governance | role_assignment, role_template, sod_rule + sod_violation, identity_session, access_request only (approval_workflow + approval_step deferred post-v1 — see Decision 11a) |
Operational role management. All depend on Batch A's actor + permission_group. |
| C — Machine & Agent | service_account, service_account_credential, agent_identity, agent_skill, agent_skill_assignment |
Machine actors. Depend on Batch A's actor table. Service accounts go before agents (simpler; validates the actor extension pattern). |
| D — Security & Compliance | tenant_security_policy (+1 table, +8 cols); consent_record NOT BUILT (no v1 gap — DR-29) |
Per-tenant security config. LOCKED 2026-06-28. 4 v1 knobs: session_timeout_minutes, mfa_required (OR-reconciliation with password_policy), max_concurrent_sessions, api_key_rotation_days. 4 enterprise knobs deferred (ip_allowlist, require_reauth_for_sensitive, max_session_duration_minutes, password_expiry_days_override). |
Each batch: design → Section 4 audit → lock → docs updated. Do not start Batch B until Batch A is locked.
What this expansion does NOT do
- Does NOT reopen
identityschema counts mid-batch. Each batch locks independently; counts update at each batch lock. - Does NOT touch the
platformschema tables (beyond closing the 8 deferred FK constraints that now point toactorinstead ofidentity_user— no new platform tables, no platform columns added). - Does NOT change RLS. The actor refactor is FK target changes only; all RLS policies continue to filter on
tenant_idas before. - Does NOT design the SCIM 2.0 API endpoints.
group+group_memberprovide the DB anchor for SCIM/Groups; the API endpoints remain deferred (see OPEN_ITEMS).