Tenant Workspace Persistence

Tenant Workspace Persistence

CAVRA R2.2 starts with a public-safe tenant/workspace persistence contract and reference stores.

Implemented Foundation

Component	Purpose
TenantWorkspaceStore	JSON reference store for local tenant and workspace records.
SQLiteTenantWorkspaceStore	SQLite reference store for local tenant and workspace records.
ActivityStore / SQLiteActivityStore	Runtime decision and session history can carry and filter by tenant_id and workspace_id.
ApprovalStore / SQLiteApprovalStore	Approval queues and approval history can carry and filter by tenant_id and workspace_id.
EvidenceMetadataStore / SQLiteEvidenceMetadataStore	Evidence metadata can carry and filter by tenant_id and workspace_id.
InventoryStore / SQLiteInventoryStore	Repository and policy rollout inventory can carry and filter by tenant_id and workspace_id.
IntegrationStore / SQLiteIntegrationStore	Integration inventory can carry and filter by tenant_id and workspace_id.
assert_tenant_workspace_scope	Rejects actor/resource tenant or workspace mismatches.
build_tenant_persistence_contract	Publishes required tenant/workspace fields and isolation rules.
build_tenant_persistence_readiness	Produces the R2.2 foundation readiness result.
build_postgres_rls_contract	Publishes the public-safe Postgres/RLS table and session-setting contract.
build_postgres_import_rows	Converts JSON/SQLite reference rows into validated Postgres import rows.
apply_postgres_tenant_scope	Applies transaction-local Postgres RLS session variables for each request.
scripts/validate_postgres_tenant_rls_smoke.py	Runs a private live Postgres RLS smoke test when a DSN is supplied externally.
migrations/postgres/001_tenant_scoped_operational_stores.sql	Defines the tenant-scoped Postgres tables and RLS policies.

Isolation Rules

• Every tenant-scoped record must include tenant_id.
• Every workspace-scoped record must include tenant_id and workspace_id.
• Actor tenant_id must match resource tenant_id.
• Actor workspace_id must match resource workspace_id when the resource is workspace-scoped.
• Production Managed or Enterprise deployments should bind this contract to Postgres with row-level security or equivalent tenant predicates.

Operational Scope Queries

The current public reference implementation includes scoped helpers for local JSON and SQLite persistence:

• list_decisions_for_scope, list_sessions_for_scope, and summarize_sessions_for_scope on activity stores.
• list_for_scope on approval stores.
• list_for_scope and search_for_scope on evidence metadata stores.
• list_repositories_for_scope and list_policy_rollouts_for_scope on inventory stores.
• list_integrations_for_scope on integration stores.
• Nullable tenant_id and workspace_id SQLite columns with in-place migration for existing local databases.

Production deployments should carry the same predicates into the managed database layer and run cross-tenant negative tests before production readiness gates pass.

Postgres/RLS Contract

The public R2.2 contract now defines the production table model without exposing private database endpoints or credentials.

Source	Postgres table	Scope
Tenant records	cavra.tenants	tenant_id
Workspace records	cavra.workspaces	tenant_id, workspace_id
Evidence metadata	cavra.evidence_metadata	tenant_id, workspace_id
Approval history	cavra.approvals	tenant_id, workspace_id
Activity sessions	cavra.activity_sessions	tenant_id, workspace_id
Activity decisions	cavra.activity_decisions	tenant_id, workspace_id
Repository inventory	cavra.inventory_repositories	tenant_id, workspace_id
Policy rollouts	cavra.inventory_policy_rollouts	tenant_id, workspace_id
Integrations	cavra.integrations	tenant_id, workspace_id

Private Enterprise runtime code must set:

SET LOCAL cavra.tenant_id = '<tenant-id>';
SET LOCAL cavra.workspace_id = '<workspace-id>';

The public session adapter uses transaction-local set_config(..., true) so tenant scope does not leak across pooled connections:

apply_postgres_tenant_scope(connection, tenant_id="tenant-a", workspace_id="prod")

The SQL contract enables and forces RLS, with predicates bound to current_setting('cavra.tenant_id', true) and current_setting('cavra.workspace_id', true). The runtime database role should not own these tables and should not have BYPASSRLS.

Live RLS Smoke Harness

Public CI can run the smoke harness without credentials and receive a skipped packet. Enterprise deployments should run it with a private DSN secret:

export CAVRA_ENTERPRISE_POSTGRES_DSN='<private-postgres-dsn>'
python3 scripts/validate_postgres_tenant_rls_smoke.py \
  --apply-migration \
  --require-live \
  --output dist/enterprise/postgres-tenant-rls-smoke.json

The output packet reports dsn_value_included: false. A passing live result proves tenant A/workspace A can read its own smoke row while tenant B/workspace B cannot read that row with the same runtime role.

Validation

python3 scripts/validate_tenant_persistence_readiness.py
python3 scripts/validate_postgres_tenant_rls_smoke.py --output dist/test/postgres-rls-smoke-skipped.json
python3 -m pytest tests/test_postgres_tenancy.py tests/test_tenancy.py tests/test_activity.py tests/test_approvals.py tests/test_evidence.py tests/test_inventory.py tests/test_integrations.py -q

Detailed repo document: Tenant And Workspace Persistence.