Multi-Tenancy, Data Ownership, and Schema Evolution

Multi-tenancy is an end-to-end isolation property, not a tenant_id column. Choose shared or separated infrastructure from risk, scale, customization, and operations; then enforce ownership through composite relationships, query scope, row-level security, jobs, files, retrieval, logs, and backups. Evolve that design with backward-compatible migrations and independently verifiable backfills.

What will you be able to do?

• Compare database-per-tenant, schema-per-tenant, and shared-schema models.
• Encode ownership in keys and foreign keys.
• Map authorization context into transactions safely.
• Plan expand/backfill/validate/switch/contract evolution.

Prerequisites and mental model

Every request carries a tenant security context. Every path to data must either prove that context or be an explicitly privileged cross-tenant operation. Isolation is only as strong as the weakest worker, export, vector query, or support script.

Which tenancy model fits?

SignalDesk begins shared-schema with composite tenant keys and later row-level security. This is a default, not a universal answer.

How do schemas evolve without breaking applications?

Use an expand-and-contract deployment:

1. Add a nullable/backward-compatible new structure.
2. Deploy code that writes old and new forms when necessary.
3. Backfill in small resumable batches.
4. Reconcile counts and semantic equivalence.
5. Validate constraints, sometimes using staged validation options.
6. Switch reads and observe.
7. Stop old writes.
8. Remove old structure only after rollback no longer depends on it.

Backfills need stable keys, checkpoints, rate limits, lock timeouts, WAL/replica monitoring, and idempotent reruns.

Why does this matter in AI-native systems?

Tenant ownership must propagate to documents, chunks, embeddings, conversations, evaluation cases, traces, tool approvals, and caches. A vector index is not an authorization boundary. Schema changes to embedding dimensions, chunk identity, or policy metadata require dual-version serving and measured reindexing, not an in-place hope.

Prove it worked

Create two tenants with overlapping customer emails and ticket subjects. Run positive and negative query tests through every current table. Draft a migration that renames a status without breaking old and new application versions, and define rollback cutoffs.

Production judgment

• Per-tenant restore is harder in shared tables; plan export/reconstruction requirements early.
• Global unique indexes may leak or prevent legitimate tenant-local values.
• Tenant context in a pooled connection must be transaction-local and reset safely.
• Cross-tenant support/admin access needs explicit role, reason, duration, and audit.

Common mistakes

• One missed query leaks rows: isolation left to convention → combine scoped data access, RLS, tests, and least privilege.
• Backfill blocks production: one huge transaction → batch, bound, monitor, resume.
• New code cannot roll back: schema contracted too soon → preserve compatibility window.
• Vector results cross tenants: filter applied after retrieval → enforce authorization in candidate selection.

AI Pair-Programmer Prompt

Hands-on exercise

Plan adding tenant ownership to a legacy notes(id, body) table with existing rows. Include quarantine for unassignable rows.

Acceptance criterion: no row is guessed into a tenant, old code remains compatible during backfill, ownership is validated before NOT NULL, and rollback is explicit.

Knowledge check

1. Why is a tenant column not sufficient isolation?
2. What is the safe order for a breaking schema change?
3. Why must vector filtering happen during candidate selection?

---

Answers

1. Every query, relationship, job, cache, file, log, and privileged path must also enforce ownership.
2. Expand, backfill/validate, switch, observe, then contract after rollback expires.
3. Post-filtering can expose or rank unauthorized data and reduce authorized recall.

Completion checklist

• Tenancy model matches stated risk and operations.
• Ownership propagates through every data product.
• Negative isolation tests exist.
• Migrations are backward-compatible, bounded, and reversible until cutoff.

Primary references

• Schemas
• Row security
• ALTER TABLE
• Constraint validation