identity — Phase 3

Locked at 11 tables. Source of truth for users, roles, permissions, site assignments, SSO providers, invitations, support-access grants, and per-user permission overrides. 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	platform.tenant	Platform exists at Phase 1; FK enforced at migration time.
tenant_user.default_site_id, user_site_assignment.site_id, user_permission_override.scope_id (when scope_type='site')	multi_loc.site	multi_loc built at Phase 4. Created as plain UUID columns in Phase 3 identity migration; FK constraint added in Phase 4 multi_loc migration.

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

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.

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	uuid_generate_v4()	PK
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	—
status	text	NOT NULL	'active'	CHECK IN ('active','suspended','deactivated')
is_platform_user	boolean	NOT NULL	false	true = Vrida employee (cross-tenant access via support grants); false = tenant staff
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

Join between a global identity_user and a platform.tenant — the row that says "this user has access to this tenant, with this tenant-level role." Vrida platform users do NOT get tenant_user rows — they reach tenant data via support_access_grant.

Tenant-scoped, many per tenant.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
user_id	UUID	NOT NULL	—	FK → identity.identity_user
role_id	UUID	NOT NULL	—	FK → identity.role (tenant-level role)
status	text	NOT NULL	'invited'	CHECK IN ('invited','active','suspended','removed')
default_site_id	UUID	nullable	—	FK → multi_loc.site (FK constraint deferred — see Cross-Phase FKs)
invited_by_user_id	UUID	nullable	—	FK → identity.identity_user
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, user_id) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)

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	uuid_generate_v4()	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')
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

Indexes:

• PK on id
• UNIQUE on (tenant_id, role_code) WHERE deleted_at IS NULL — partial unique (soft-delete pattern). 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).

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

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	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
tenant_user_id	UUID	NOT NULL	—	FK → identity.tenant_user
site_id	UUID	NOT NULL	—	FK → multi_loc.site (FK constraint deferred — see Cross-Phase FKs)
site_role_id	UUID	nullable	—	FK → identity.role. NULL = use tenant-level role; populated = site-specific role differs
access_status	text	NOT NULL	'active'	CHECK IN ('active','suspended','removed')
is_primary_site	boolean	NOT NULL	false
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 application-level authorization event log: tenant selection, permission denials, role changes, site-access denials, invitation lifecycle, support-access grant/use/revoke, permission overrides, SSO login mappings. Does NOT log raw Supabase Auth events (logins, passwords, MFA challenges) — those live in Supabase Auth's own audit log.

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	uuid_generate_v4()	PK
tenant_id	UUID	nullable	—	NULL for platform-level events. FK → platform.tenant
user_id	UUID	nullable	—	Subject of the event. FK → identity.identity_user
event_type	text	NOT NULL	—	CHECK IN ('tenant_selected','permission_denied','role_changed','site_access_denied','invitation_sent','invitation_accepted','support_access_granted','support_access_used','support_access_revoked','support_access_denied','permission_override_applied','sso_login_mapped')
event_status	text	NOT NULL	'success'	CHECK IN ('success','failure')
actor_user_id	UUID	nullable	—	Who triggered the event. FK → identity.identity_user
target_user_id	UUID	nullable	—	For events like role_changed. FK → identity.identity_user
reason_code	text	nullable	—
ip_address	inet	nullable	—
user_agent	text	nullable	—
correlation_id	text	nullable	—	Ties to source request / job
metadata	JSONB	nullable	—	Event-specific bag
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id, created_at)
• on (tenant_id, event_type) — admin lookups like "all permission_denied events for tenant X"
• on (user_id, created_at)

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

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.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	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
• on token_hash

identity.support_access_grant

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

Tenant-scoped — the tenant being accessed.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	Tenant being accessed. FK → platform.tenant
vrida_user_id	UUID	NOT NULL	—	FK → identity.identity_user (must have is_platform_user = true)
granted_by_user_id	UUID	NOT NULL	—	Who approved the grant. FK → identity.identity_user
reason	text	NOT NULL	—	Why access was granted
status	text	NOT NULL	'active'	CHECK IN ('active','expired','revoked')
starts_at	timestamptz	NOT NULL	now()
ends_at	timestamptz	NOT NULL	—	Time-boxed — REQUIRED
revoked_at	timestamptz	nullable	—
revoked_by_user_id	UUID	nullable	—	FK → identity.identity_user
correlation_id	text	nullable	—	Ticket ID, incident ID, etc.
metadata	JSONB	nullable	—	Grant-specific context
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

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

AUTHORIZATION RULE (load-bearing): A Vrida platform user 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	uuid_generate_v4()	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 (Item M / conditional-column consistency rule).
reason	text	nullable	—
granted_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user
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)

---

Identity — Design Notes

Permission Resolution Order

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

1. Base: role_permission via tenant_user.role_id — and site_role_id from user_site_assignment when the user is acting at a specific site.
2. 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.
3. 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.
• 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 is the hash of the invite token. Validation hashes the inbound token and compares.

---

Identity — Design Patterns Summary

Tables WITHOUT tenant_id

• identity_user — global user record
• permission — reference data, shared across tenants

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)

Append-only (no updated_at / no deleted_at)

• identity_access_event
• role_permission (no updated_at — changes are made by delete + re-insert)

No deleted_at — permanent records

• permission (reference data; deactivate via is_active)
• identity_access_event (append-only)
• role_permission (append-only)
• support_access_grant (audit record; expire / revoke via status + ends_at, never soft-delete)

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

• tenant_user.default_site_id
• user_site_assignment.site_id
• user_permission_override.scope_id (when scope_type = 'site')

Reference / hash — never raw

• sso_provider.client_secret_ref — reference to secret manager
• sso_provider.certificate_ref — reference
• invitation.token_hash — hash of token

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	[{"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	metadata	Grant-specific context (ticket details, incident links)

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, user_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
• sso_provider: no unique beyond PK (multiple providers per tenant supported, e.g. SAML + OIDC)
• invitation: no unique beyond PK and token_hash index (re-invites are allowed; old rows move to expired / revoked)

Other patterns

• All PKs: UUID with default uuid_generate_v4()
• All enums: stored as text with CHECK constraints
• All timestamps: timestamptz, stored UTC

---