UmbraTrans/docs/tenant-schema-classification.md

Which tables move into a per-tenant schema, and why

Written 2026-10-03, implementing Umbratools_2.md §5.0 (schema per organization). Every table in the public schema of umbratrans_scratch is classified below. The classification was made from the columns, foreign keys and check constraints read out of the database, not from what the names suggest:

-- the evidence for every claim in this document
SELECT c.relname, a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull
  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
  JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0
 WHERE n.nspname = 'public' AND c.relkind = 'r';
SELECT conrelid::regclass, confrelid::regclass, pg_get_constraintdef(oid)
  FROM pg_constraint WHERE connamespace = 'public'::regnamespace;

30 tables. Four move. Twenty-six stay in public. The summary at the end says why four is the honest number and not a smaller one.

---

1. The rule used

A table is tenant-owned if both of these hold:

1. Every row belongs to exactly one organization. No row is shared, and no row belongs to no organization.
2. It can be found given only the organization. Locating a tenant's copy of the table may not require first reading something that identifies the tenant.

Anything that fails either test stays in public. A table in public is not a weakness by itself: platform data is not one organization's data, so a platform table cannot leak another organization's rows by holding one organization's rows.

The rule matters because rule 2 has teeth. It is what keeps users and user_roles in public, and moving either of them is not a smaller version of this migration but a different one — see §4.

---

2. Tenant-owned — these move

2.1 projects

evidence	value
organization_id uuid NOT NULL REFERENCES organizations(id)	every row has one organization, and it is required
UNIQUE (organization_id, slug)	a slug is unique within an organization — the constraint is itself tenant-scoped
projects_id_organization_id_key UNIQUE (id, organization_id)	exists only to let invitations and user_roles point at a project within an organization
id uuid PRIMARY KEY	a project is globally unique, so a tenant cannot collide with another's identifier
policies	projects_select/insert/update/delete, all of which are organization_id filters or an app.can_access_project that reads organization_id

The unit of work — the book. It belongs to the organization that commissioned it and to nobody else. After the move it has no organization_id column at all: the schema it is found in is the organization, so the column is redundant with the directory entry. Two constraints go with it and are rebuilt inside the tenant schema: UNIQUE (organization_id, slug) becomes UNIQUE (slug) (identical meaning inside one organization), and UNIQUE (id, organization_id) disappears, because nothing outside the tenant can reference a project any more.

Project.organization_id survives in the model and is filled in by the adapter from the schema that answered the query. It is a fact about where the row was found, not a column that was read.

2.2 project_authors

evidence	value
no organization column	it is reached through its project
project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE	every row hangs off exactly one project, therefore one organization
author_id REFERENCES authors(id), created_by REFERENCES users(id)	outbound references only, to platform tables
policy	project_authors_tenant, an EXISTS over projects filtered on p.organization_id

Bylines for a book. The policy's own definition of the tenant boundary is "the organization of the project this row belongs to", which is exactly what the schema now says structurally.

2.3 project_publishers

Identical shape and identical argument: (project_id, publisher_id, lang), one outbound reference to public.publishers (which stays — see §3.6), one policy whose tenant test is the project's organization.

2.4 invitations

evidence	value
organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE	required
invitations_project_id_organization_id_fkey (project_id, organization_id) → projects(id, organization_id) ON DELETE CASCADE	already tenant-scoped
email citext NOT NULL, token_hash text NOT NULL UNIQUE	an outstanding invitation is an organization's record of an address it invited
invited_by REFERENCES users(id), role_id REFERENCES roles(id) ON DELETE RESTRICT	outbound references only
policies	invitations_manage, invitations_select — both filters on organization_id

The one tenant table whose rows are about a person rather than about work. It still belongs to the organization: it is the organization's outstanding invitation, and ON DELETE CASCADE from organizations already says the organization owns it. After the move the composite foreign key becomes project_id → projects(id) within the same schema, and organization_id is dropped.

2.5 What moving them costs the rest of the schema

Three foreign keys point into projects from tables that stay in public, and a foreign key cannot name a table whose schema depends on the row:

constraint	why it cannot survive
artifacts_project_id_fkey → projects(id) ON DELETE CASCADE	artifacts is platform-level (§3.7); the target schema varies per tenant
jobs_project_id_fkey → projects(id)	jobs is platform-level (§3.7) for the same reason
user_roles_project_id_organization_id_fkey → projects(id, organization_id)	user_roles stays platform-level (§4)

All three are dropped. That is a real loss of a database-enforced invariant and it is recorded rather than worked around: after this migration nothing in the database stops public.jobs naming a project in an organization it does not belong to. The application's own rules still do — a job is submitted for an organization, and the organization is the schema the write lands in — but the statement that could contradict them is no longer refused by the server.

A compensating trigger was considered and rejected: to check a project_id against the tenant schemas, a function would have to know which tenant, which is the thing that is not recorded on the row, and would need dynamic SQL over a list of schemas determined at write time. That is the hand-maintained-grant fragility §5.0 was written to remove, re-introduced as a trigger.

---

3. Platform-level — these stay in public

3.1 organizations

The tenant root, and the record that names the tenant's schema. It cannot live inside the schema it names. Every other table's tenant reference resolves through it.

3.2 users

evidence	value
no organization column at all	the control plane stores tenancy on user_roles, not here
email citext NOT NULL UNIQUE, username citext UNIQUE	the login identifier is unique across the whole platform, not per tenant
referenced by sessions, user_credentials, mfa_methods, bootstrap_tokens, user_roles, audit_events, projects.created_by, artifacts.created_by_user_id, invitations.invited_by, project_authors.created_by, project_publishers.created_by	it is the identity every one of those hangs off
SELECT id, email … FROM users WHERE users.id = app.current_user_id()	the invitations_select policy reads users from inside a policy on a tenant table

The login path is get_user_by_email → _resolve_assignment. The first statement needs no tenant and the second returns the tenant. A table that must be read to find the tenant cannot be inside the tenant.

3.3 user_roles — the one that argues hardest, and stays

This is the table the plan expected to move. It carries organization_id, it carries project_id, and it is the assignment table. It stays in public for three reasons, each of which is load-bearing on its own:

1. It is the index that routes a request to a schema. Principal.organization_id comes from _resolve_assignment, which reads this table filtered by user_id alone. Putting the index inside the thing it indexes makes resolution impossible: to answer "which tenant is this caller in?" you would have to search every tenant schema for a row. The lookup would become one query per organization on the login path, at one hundred organizations.
2. It holds rows that belong to no organization. user_roles_check requires scope = 'platform' to carry organization_id IS NULL. A platform administrator's assignment has no tenant, so in a per-tenant world it has no schema to live in except a reserved one — which is a second table with the same name and a rule for which rows go where.
3. It is what makes a caller visible to a platform administrator. app.is_platform_admin, app.is_organization_admin, app.can_view_user and find_users(organization_id=…) all read it, and all four run with no tenant in hand.

The consequence is recorded plainly: after this migration one table is shared between organizations, and it is user_roles. §5.0's headline — no table is shared between organizations — is not fully achievable while the session-to-tenant resolution is a table. What is achievable is that no table of tenant data is shared, and §4 says what closing the remaining gap would cost.

3.4 user_credentials, sessions, mfa_methods, bootstrap_tokens

All four are keyed by user_id and none carries an organization. They are what makes a login work, which happens before a tenant exists. Moving them would mean the login path could not read the credential for an account whose tenant it does not yet know.

3.5 roles, permissions, role_permissions

A closed vocabulary seeded once: roles_scope_name_key UNIQUE (scope, name), permissions.key primary key. invitations.role_id and user_roles.role_id both point here ON DELETE RESTRICT, and app.validate_user_role_scope reads roles on every assignment write. A per-tenant copy would let one organization's idea of what translator means diverge from another's, which would defeat the point of a role catalogue.

3.6 authors, publishers

evidence	value
owner_organization_id uuid REFERENCES organizations(id) — nullable	a row may belong to no organization
authors_slug_key UNIQUE (slug), publishers_slug_key UNIQUE (slug)	unique across the platform, which is what makes the row shared
publishers_read / authors_read policies: USING (app.current_user_id() IS NOT NULL)	readable by every authenticated principal, deliberately, and not gated on ownership
publishers_write: USING (app.is_platform_admin() OR app.is_organization_admin(owner_organization_id))	shared read, owner-only write

These are third-party reference data — a publishing house, a byline — not one organization's content, and the schema says so twice: the owner column is nullable and the read policy admits any signed-in caller. test_rls_coverage.py asserts that read policy by name, because losing it would hide the imprint a tenant's own project is published under. Moving these into a tenant schema would make a publisher created by organization A invisible to organization B, which is the opposite of what the existing decision requires.

3.7 The compute surface — named in the constraint, recorded anyway

compute_nodes, compute_devices, node_credentials, node_heartbeats, node_models, jobs, job_events, job_artifacts, artifacts, model_registry.

These stay because a compute node is not owned by an organization, it serves organizations. model_registry is the catalogue every node is measured against; jobs is the queue the scheduler drains across every tenant; artifacts is content addressed against content_hash with a unique index scoped to (project_id, …) so that two organizations holding identical bytes do not collide. Put any of them in a tenant schema and the controller can only see the tenants whose schema it happens to be pointed at — a node in organization A becomes invisible to the scheduler while a job for organization B is queued.

artifacts and jobs both carry organization_id, so they are tenant-tagged without being tenant-owned, and that is worth being explicit about: the tag says whose work this is, not who may see it. The controller must see all of them.

3.8 node_pairing_codes, node_pairing_attempts

Platform, for a reason that is about when rather than who: pairing is how a node joins the platform, and the request that redeems a pairing code is authenticated by the code itself. There is no session, no principal and therefore no organization to resolve a schema from. A pairing request that had to choose a tenant schema would be choosing one from a bearer secret. node_pairing_codes.organization_id records which organization's administrator authorised the machine — an audit fact about the authorisation, in the same way audit_events.organization_id is.

3.9 audit_events

organization_id is nullable, and the rows where it is null are the ones an auditor most wants: a failed sign-in, a rejected token, a request that named no tenant. Nothing forces that ordering — authentication is resolved against users and bootstrap_tokens first — so an event with no tenant is a normal event rather than an exception. It is also append-only (app.prevent_audit_mutation), which is why the test suite cannot clean up selectively and refuses to run against a populated database at all. A tenant schema would have nowhere to put the events that precede tenancy.

3.10 schema_migrations

The ledger. It records which migrations ran, which is a fact about the database, and a tenant schema is created and dropped by those migrations.

---

4. What it would take to move user_roles too

Recorded because §5.0 promised eight organization_id columns disappearing and the honest number is two (projects, invitations). The gap is entirely user_roles, and it is not closed by moving the table — it is closed by splitting it:

• public.user_tenant_index(user_id, organization_id, scope) — the routing index, no role in it, searchable by user_id alone, plus the platform-scope rows.
• <tenant>.user_roles — the roles themselves: organization-scoped and project-scoped assignments, which is what the rest of §3.3's reasons are about.

Every one of the readers in §3.3 then changes: the five app functions, the six user_roles policies, _resolve_assignment, find_users, count_users_with_role, list_project_member_ids, assign_user_to_project, and verify_rls_contract. It also introduces a table that has to be kept in step with the tenant tables — a membership index that disagrees with the roles it indexes is a privilege bug with no constraint anywhere behind it. That is a larger and more dangerous change than the one §5.0 scoped, so it is not done here, and it is not done implicitly either.

---

5. Summary

Move into <organization>'s schema (4): projects, project_authors, project_publishers, invitations.

Stay in public (26): organizations, users, user_roles, user_credentials, sessions, mfa_methods, bootstrap_tokens, roles, permissions, role_permissions, authors, publishers, audit_events, compute_nodes, compute_devices, node_credentials, node_heartbeats, node_models, jobs, job_events, job_artifacts, artifacts, model_registry, node_pairing_codes, node_pairing_attempts, schema_migrations.

What the plan said, and what is actually true:

§5.0 said	measured
"organization_id disappears from eight tables"	two: projects and invitations. Four of the eight are named platform-level by the same decision (artifacts, jobs, compute_nodes, node_pairing_codes); user_roles cannot move (§3.3); authors and publishers are shared reference data by their own read policies (§3.6).
"nineteen table references in the Python need a schema-qualified form"	twenty-three references to the two tables in postgres_repository.py: ten to projects and thirteen to user_roles. Only the ten change, and they change in the opposite direction — they stop naming public. and become unqualified, reaching the tenant through search_path. The thirteen user_roles references keep naming public., because that table stays (§3.3).
"no table is shared between organizations"	one table is: user_roles (§3.3), for the reason that the session-to-tenant index cannot live inside the thing it indexes.

Also not in the plan: moving projects forces the dropping of three foreign keys that point at it from platform tables (§2.5). The plan does not mention this, and it is the only place where the decision costs a database-enforced guarantee.