control schema (platform-wide state: employees, tenants, jobs, cross-tenant audit) and one tenant_<slug> schema per tenant (everything scoped to that tenant’s data). Every tenant table additionally carries FORCE ROW LEVEL SECURITY, so isolation is enforced twice: once by which schema a connection’s search_path points at, and again by a tenant_id policy that applies even to a query that somehow lands in the right schema.
Why schema-per-tenant, not a shared tenant_id column everywhere
A single shared-schema, tenant_id-column design is simpler to migrate, but every query in every code path becomes responsible for filtering by tenant — one omitted WHERE tenant_id = ... is a cross-tenant data leak. Schema-per-tenant collapses that risk into the connection’s search_path: services/api/db/session.py’s tenant_uow() sets search_path to "tenant_<slug>", public transaction-locally, so an unqualified SELECT * FROM users inside that transaction can only ever see one tenant’s users table — there is no users table anywhere else it could resolve to. Row-level security is layered on top as defense in depth against the case a raw connection (e.g. a superuser/owner role) bypasses search_path discipline.
Control schema
Thecontrol schema (created by migrations/versions/20260722_c001_control_schema.py, branch label control) holds platform-wide state. All models are declared in services/api/db/models.py on ControlBase.
control.tenants
The tenant registry — one row per tenant, created in draft state and moved through its lifecycle by the onboarding pipeline.
control.employees and control.employee_sessions
Staff accounts for the Onboarding Portal. employees.control_role is CHECK-constrained to onboarding_admin or support_readonly — there is no self-signup route; accounts are seeded by scripts/create_employee.py. employee_sessions is a standard opaque-token session table: session_token_hash (never the raw token), expires_at, revoked_at, plus ip_address/user_agent captured at login.
control.tenant_invitations
One row per pending/accepted/revoked/expired invitation (status CHECK-constrained to those four values). invite_token_hash is the SHA-256 hash of the raw token mailed to the invitee; role_profile_key is the tenant role profile (org_admin, risk_admin, analyst, viewer) the accepted user is granted.
control.audit_log
Cross-tenant, employee-facing audit trail — distinct from the per-tenant audit_log inside each tenant schema (below). actor_type is CHECK-constrained to employee, tenant_user, or system. Every impersonation grant (impersonate_tenant_user) writes a row here with action="employee_impersonation" unconditionally, not just on request, because it is a real access grant into customer data.
control.platform_jobs
The durable, retryable work queue behind provisioning, ingest materialization, model training, and email delivery. status is CHECK-constrained to queued, running, succeeded, failed, cancelled; progress to 0–100. idempotency_key has a partial unique index (WHERE idempotency_key IS NOT NULL) so a caller can safely re-request the same logical job. steps_completed (JSONB array) is how a crashed provisioning run resumes from its last completed step rather than restarting from scratch — see Workers and jobs.
control.runtime_heartbeats
One row per running worker component (component is the primary key, so a new instance simply upserts over the previous one), carrying instance_id and a detail JSONB blob advertising which job types it currently supports. GET /health/ready reads this to decide worker readiness — see Observability.
control.password_reset_tokens
Employee-only password reset tokens (token_hash, expires_at, used_at). The tenant-user equivalent lives inside each tenant schema, not here — see below.
Tenant schema (tenant_template branch)
Every tenant schema is created from the same template, applied once per tenant by services/api/tenancy/provisioner.py’s run_tenant_migrations() (alembic upgrade tenant_template@head -x schema=<tenant_schema>). The mappings live on TenantBase in services/api/db/models.py, deliberately unqualified (no schema= in __table_args__) — tenant_uow() installs the correct schema onto search_path per transaction, so the same mapped class resolves to a different tenant’s table depending only on which TenantRecord opened the session.
Every table except
password_reset_tokens, audit_log, and source_uploads (each added by a later migration that applies its own policy) gets its RLS policy in the base 20260722_t001 migration via a shared _apply_rls() helper:
FORCE matters specifically for the owning/migrator role: on some hosts (Neon’s <project>_owner, reportedly BYPASSRLS) plain ENABLE ROW LEVEL SECURITY would be silently skipped for that role, but a plain Postgres owner (e.g. Render’s) is not automatically BYPASSRLS and FORCE applies the policy to owners too — this is why the provisioner’s _seed_role_profiles() step deliberately opens a tenant_uow() (which sets app.tenant_id) rather than the raw owner_uow() connection to insert seed rows.
Connection roles
Three connection strings, resolved inservices/api/tenancy/db.py:
_grant_app_rw() runs GRANT USAGE ON SCHEMA, GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES, and ALTER DEFAULT PRIVILEGES ... GRANT ... ON TABLES for app_rw against the new tenant schema as part of provisioning, so app_rw never needs CREATE/DROP on tenant data — only the CRUD the product API actually performs.
Provisioning flow
provision_tenant() runs six idempotent, independently-resumable steps, tracked in control.platform_jobs.steps_completed so a crash mid-provision resumes rather than restarts:
Each step is safe to re-run: CREATE SCHEMA IF NOT EXISTS, Alembic’s own per-schema alembic_version tracking (version_table_schema in migrations/env.py), ON CONFLICT DO NOTHING role-profile seeding, and idempotent GRANT/ALTER DEFAULT PRIVILEGES. activate_onboarding moves lifecycle_state from draft/provisioning to onboarding only — it never moves a tenant past that automatically; go-live is a separate, explicit staff action.