Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / docs/MIGRATE_TO_PER_TENANT.md |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 9bde0f9a74b89d01980fa658e5253acda7528d0c |
| Branch | per-tenant-routing |
| Path or archive member | docs/MIGRATE_TO_PER_TENANT.md |
| Original SHA-256 | 7d37badf870299d804892e24bd816c81e3f33db55edae187f4410c8c9f8ebc9c |
| Git blob SHA | fd8517cd4004eefe656cd5520dbd5aa1f5ee69a1 |
| Reading-copy SHA-256 | 7d37badf870299d804892e24bd816c81e3f33db55edae187f4410c8c9f8ebc9c |
Every recorded occurrence
These 3 records contain identical original source bytes. Their paths and revisions remain part of the archive.
| Original record | Branch or archive | Revision / identifier | Original location |
|---|---|---|---|
| Janady13/missionmatch / docs/MIGRATE_TO_PER_TENANT.md | per-tenant-routing | 9bde0f9a74b89d01980fa658e5253acda7528d0c | Open original |
| Janady13/missionmatch / docs/MIGRATE_TO_PER_TENANT.md | deploy-and-ops | bdacd9d0b4d5998d25fc74bf4007f4c3ec538c7a | Open original |
| Janady13/missionmatch / docs/MIGRATE_TO_PER_TENANT.md | per-tenant-routing | 9bde0f9a74b89d01980fa658e5253acda7528d0c | Open original |
Recovered source transcript
Download complete source text ↓126 lines · 6.3 KB
# Plan: move the shared database tenants into the per tenant model
This is the ordered, rollback ready plan for the OPERATOR to move the existing shared database production
tenant or tenants onto their own isolated databases. It is authored, not executed. Validate every step on
a staging copy first (the per tenant routing itself is already proven on M4 staging, see BUILD_REPORT).
Do NOT run this against production until you have a verified backup of the shared database and a
maintenance window. The cutover is reversible up to the final routing flip.
## Shape of the move
The existing shared database already holds identity (organizations, users, the auth tables), the tenant
directory (migration 0020), and the business data for every org under RLS. The move is:
1. Promote the shared database to the role of CONTROL PLANE: it keeps identity, the auth tables, and the
directory, and it stops holding business data as each org moves out.
2. For each org, stand up its own tenant database, copy that org's business data into it, register it in
the directory, and verify.
3. Once every org has its own database, flip `MM_TENANCY` to `per_tenant` and deploy, so requests route to
the tenant databases. The control plane keeps serving identity and the directory.
Inventory first: `SELECT id, name, slug FROM organizations;` on the shared database, so you know the full
set of orgs to move and can confirm none are missed.
## Step 1: stand up the control plane
The shared database becomes the control plane. Confirm it is at migration head (`migrate-all-tenants
-check` should report the control plane at head once at least one tenant exists, or `migrate-status`).
Confirm the directory table exists (`SELECT 1 FROM tenant_directory;`). No data moves in this step.
Rollback: none needed (no change yet). If you prefer a SEPARATE control plane database, create it, run the
migrations as the migrator, and copy `organizations`, `users`, and the auth tables into it before
continuing; that is a larger variant and is not required.
## Step 2 (per org): provision the tenant database
For each org, create its database:
```
provision-tenant -admin-dsn "$ADMIN" -migrator-dsn "$MIG" -name "tenant_<slug>"
```
Rollback: `DROP DATABASE "tenant_<slug>"` (empty so far).
## Step 3 (per org): register the tenant and mark it migrating
Register the org in the directory with status `migrating`, so the API will not yet route live traffic to
it (a non active tenant is refused with a typed error), and store its app and compliance DSNs under the
secret reference (see RUNBOOK_ONBOARDING step 3 and 4). Status `migrating` is the offline marker for the
move.
Rollback: `DELETE FROM tenant_directory WHERE organization_id='<org>';`.
## Step 4 (per org): seed identity into the tenant database
```
sync-tenant-identity -control-dsn "$CP_MIG" -org "<org>"
```
This copies the org and its users (so the business foreign keys resolve). Rollback: drop the rows or the
database.
## Step 5 (per org): copy the org's business data into its database
This is the irreversible to the prior layout step. Copy each business table's rows for this org from the
shared database into the tenant database, in dependency order (parents before children), filtering by
`organization_id`. The org and users were seeded in step 4, so their foreign keys resolve. Use a per org
filtered copy (a `COPY (SELECT ... WHERE organization_id='<org>') TO STDOUT` from the source piped into a
`COPY ... FROM STDIN` on the target, or a filtered `pg_dump`), one table at a time, in this order:
1. requirements, required_skills, required_certifications
2. candidates, candidate_skills, candidate_certifications, candidate_customer_experience
3. candidate_evaluations
4. slates, slate_members
5. recommendation_runs, staffing_recommendations
6. ai_memory
7. sourcing_log, aedt_inventory, aedt_candidate_notices
8. the eeo schema rows (eeo.candidate_demographics) using the compliance role on both ends
9. audit_logs for the org (optional but recommended for continuity)
Do a row count assertion per table after each copy: the count in the tenant database must equal the count
in the shared database for that org. Then embed the tenant's vectors:
```
embed-backfill -dsn "$MIG_to_tenant" -megamind "$MM_MEGAMIND_URL" -model qllama/bge-small-en-v1.5 -what all -batch 64
```
Rollback: drop the tenant database and re provision; the shared database still holds the authoritative
rows until the final flip, so nothing is lost.
## Step 6 (per org): verify, then activate
Run the isolation checks from RUNBOOK_ONBOARDING step 7 (head matches, app role denied on eeo, RLS forced,
row counts equal). Only then set the directory status to `active`:
```
psql "$CP_MIG" -c "UPDATE tenant_directory SET status='active', updated_at=now() WHERE organization_id='<org>';"
```
Rollback: set the status back to `migrating`; the API stops routing to it.
## Step 7: flip routing to per tenant
Once EVERY org has its own active tenant database and is verified, deploy the API with `MM_TENANCY=per_tenant`
(and the secrets backend and pool bounds set). Requests now route to the tenant databases; the control
plane serves identity and the directory.
Rollback: redeploy with `MM_TENANCY=shared`. Because the shared database still holds the business rows
(they were COPIED, not deleted), shared mode serves them again immediately. Keep this fallback available
until you have run on per_tenant long enough to trust it.
## Step 8: decommission the shared copy of the business data (only when confident)
After a soak period on per_tenant with backups verified, remove the now stale business rows from the
shared database so it is a clean control plane. This is the point of no easy return; take a final backup
first. The control plane keeps identity, the auth tables, and the directory.
## Notes and limits
- Per org connection pool budget: with one pool per tenant, confirm `MaxPools` times `MaxConnsPerPool`
plus the control plane and compliance pools stays under the server `max_connections`.
- A pooler in front of any database must be in TRANSACTION mode, never statement mode, or the per request
org GUC (SET LOCAL) isolation breaks.
- Needs a human decision: whether to use the shared database as the control plane or stand up a separate
control plane database; the cutover window per org; and the soak period before step 8.