> ## Documentation Index > Fetch the complete documentation index at: https://docs.causeloop.ai/llms.txt > Use this file to discover all available pages before exploring further. # Tenancy and data model > Schema-per-tenant isolation, the control-plane tables, what a tenant schema contains, and how Alembic's two migration branches apply each. Causeloop's product database is one PostgreSQL instance split into a fixed `control` schema (platform-wide state: employees, tenants, jobs, cross-tenant audit) and one `tenant_` 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_", 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 The `control` 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. | Column | Notes | | -------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `slug` | Unique; `CHECK (slug ~ '^[a-z0-9][a-z0-9-]{1,61}[a-z0-9]$')` — same pattern `services/api/tenancy/db.py`'s `validate_tenant_slug()` enforces in application code before it's ever interpolated into DDL. | | `lifecycle_state` | `CHECK` constrained to `draft, provisioning, onboarding, training, review, live, suspended, offboarded`. Login (`login_tenant_user`) only succeeds when this is `live`. | | `share_onboarding_outputs` | Whether onboarding-generated insight collections are client-visible before the tenant formally goes live — read by the console's `useConsoleData()` to decide `locked` vs `loading`. | | `schema_name` | The tenant's Postgres schema, e.g. `tenant_acme-bank`. Unique; always `tenant_` (`tenant_schema_name()`). | | `storage_prefix` | The tenant's object-storage key prefix, `tenants/`. Unique. | | `created_by` | FK to `control.employees` — which staff member created the tenant. | ### `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](/architecture/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](/architecture/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=`). 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. The `tenant_template` Alembic branch will refuse to run without `-x schema=` (a schema-target guard in every tenant-branch revision — `_require_tenant_schema_target()` in most, `_require_schema()` in `20260727_t005_source_uploads.py`) — it is a template applied per tenant by the provisioner and `scripts/migrate_all_tenants.py`, never run against a bare database. | Table | Purpose | | ----------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `users` | Tenant-scoped user accounts. `UNIQUE(tenant_id, email)`. | | `user_sessions` | Opaque tenant-user sessions (`session_token_hash`, `expires_at`, `revoked_at`). | | `role_profiles` | Named permission sets (`org_admin`, `risk_admin`, `analyst`, `viewer`), seeded by the provisioner's `seed_role_profiles` step. `UNIQUE(tenant_id, key)`. | | `user_roles` | Join table: which users hold which role profiles. | | `sources` | Configured data connectors (`kind` `CHECK`-constrained to `file_upload, s3, database, kafka`), `encrypted_credentials` (see [Security](/architecture/security)), `checkpoint` (opaque per-connector sync cursor, added by `20260723_t003`). | | `datasets` | One row per committed ingest, `UNIQUE(tenant_id, version)`. | | `records` | Individual ingested rows, deduplicated by `UNIQUE(tenant_id, dataset_id, content_hash)`. | | `source_uploads` | Immutable metadata for an object-storage-backed file upload (`object_provider`/`object_container`/`object_key`, `content_sha256`, `status` staged/committed/failed) — added by `20260727_t005`. | | `ingest_jobs` | Async ingest job state (`status`, `stage`, `progress`). | | `model_checkpoints` | Trained-model versions, `UNIQUE(tenant_id, version)`, with `is_active` marking the live one. | | `llm_settings` | Per-tenant LLM provider config — `provider`/`model`/`base_url`, `encrypted_api_key`, token budget/usage. One row per tenant (`UNIQUE(tenant_id)`). | | `insight_collections` | The ten named collections (`summary`, `themes`, `issues`, `severity`, `lens`, `fishbone`, `theme_summaries`, `narratives`, `recommendations`, `cause_remediation`) keyed by `(tenant_id, collection, model_version, dataset_version)`, with `client_visible` gating what the console can see. `cause_remediation` is export-only — excluded from the client snapshot the console reads. | | `reviews` | Human review decisions on findings; `version` column (added by `20260725_t004`) supports optimistic concurrency on PATCH. | | `caps` | Corrective Action Plans — `status`, `owner_id`, `due_date`, freeform `detail` JSONB (CAPA text, remediation tier, per-step completion, proof-window dates), `version` for optimistic concurrency. | | `settings` | One JSONB blob per tenant (`key_values`) holding small preference groups like `data_policy` and `training_config` — no dedicated table per preference. | | `pii_field_policies` | Per-field masking rules (`mask_strategy`: `redact`, `hash`, `partial`) applied by `services/api/auth/pii_masking.py` to any response a caller without `pii.unmasked` receives. | | `password_reset_tokens` | Tenant-user password reset tokens — the tenant-schema counterpart to `control.password_reset_tokens`. | | `audit_log` | Tenant-scoped audit trail (who did what to which CAP/review/export, from which session/IP) — distinct from `control.audit_log`, which only records employee/control-plane actions. | 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: ```sql theme={null} ALTER TABLE "" ENABLE ROW LEVEL SECURITY; ALTER TABLE "
" FORCE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation ON "
" USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid) WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid); ``` `FORCE` matters specifically for the owning/migrator role: on some hosts (Neon's `_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 in `services/api/tenancy/db.py`: | Env var | Role | Used by | | --------------------------------------------------------------------------------------------------- | --------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `MIGRATIONS_DATABASE_URL` (falls back to `CAUSELOOP_POSTGRES_DSN`, then `DATABASE_URL`) | Owning/migrator role | Alembic, `services/api/tenancy/provisioner.py`'s DDL/GRANT steps — needs a direct (non-pooled) connection for DDL and advisory locks. | | `APP_DATABASE_URL` (falls back to `DATABASE_URL`/`CAUSELOOP_POSTGRES_DSN`, re-targeted to `app_rw`) | `app_rw` — non-superuser, `NOSUPERUSER NOBYPASSRLS` | Every request-serving `control_uow()`/`tenant_uow()` session. RLS policies only actually bind to a role like this; the owning role typically has `BYPASSRLS` and must never serve tenant-data requests. | | — | Owner (direct) | `owner_uow()` — schema creation and `GRANT`/`ALTER DEFAULT PRIVILEGES`, called only from the provisioner. | `_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: ```mermaid theme={null} flowchart LR A[create_schema] --> B[run_tenant_migrations] B --> C[grant_app_rw] C --> D[seed_role_profiles] D --> E[verify_object_storage] E --> F[activate_onboarding] ``` 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. ## Entity relationships ```mermaid theme={null} erDiagram CONTROL_TENANTS ||--o{ CONTROL_TENANT_INVITATIONS : invites CONTROL_TENANTS ||--o{ CONTROL_PLATFORM_JOBS : "runs jobs for" CONTROL_EMPLOYEES ||--o{ CONTROL_EMPLOYEE_SESSIONS : authenticates CONTROL_EMPLOYEES ||--o{ CONTROL_TENANTS : creates CONTROL_TENANTS ||--|| TENANT_SCHEMA : "owns one schema" TENANT_SCHEMA { schema tenant_slug } USERS ||--o{ USER_SESSIONS : authenticates USERS ||--o{ USER_ROLES : holds ROLE_PROFILES ||--o{ USER_ROLES : grants SOURCES ||--o{ DATASETS : produces SOURCES ||--o{ SOURCE_UPLOADS : stages DATASETS ||--o{ RECORDS : contains DATASETS ||--o{ MODEL_CHECKPOINTS : trains DATASETS ||--o{ INSIGHT_COLLECTIONS : versions MODEL_CHECKPOINTS ||--o{ INSIGHT_COLLECTIONS : versions USERS ||--o{ REVIEWS : decides USERS ||--o{ CAPS : owns USERS ||--o{ AUDIT_LOG : "acts in" ``` ## Related * [Security model](/architecture/security) * [Backend architecture](/architecture/backend) * [Migrations](/deployment/migrations) * [Client onboarding pipeline](/features/client-onboarding-pipeline)