Company tenancy migration¶

Migration 20260916090000_company_branches_and_scoped_authorization establishes canonical companies, owned branches, branch-owned programs, company memberships, and scoped RBAC.

Deployment contract¶

Deploy backend and web/mobile clients in one release window. New and existing program creation step 1 requires branchId.

Before applying the migration:

• take and verify a restorable production backup;
• stop or drain writes that create companies, programs, bank accounts, or MOU data;
• deploy client builds that understand headquarters branches and required branchId;
• verify SELECT COUNT(*) FROM companies WHERE branch_type = 'SUB' returns zero;
• verify every Company has its historical owner User;
• verify every legacy company-owned foreign key maps through Company.userId;
• test the migration against a production-shaped snapshot.

What the migration changes¶

Canonical tenancy¶

• Company.id becomes the only company/tenant key.
• Legacy foreign keys containing the company owner's User.id are rewritten to the matching Company.id and receive real company foreign keys.
• Company.userId, branch, branchType, and mainBranchId are removed.
• BranchType, legacy invitation status, and branch_invitations are removed.

Headquarters and operational data¶

• One active headquarters Branch is created for every company.
• Its deterministic ID is derived from the company ID, and its name uses the owner name with Head Office as fallback. Legacy branch identity is not carried forward.
• Headquarters branchCode remains null.
• Company registered/billing address data is copied into one operational BranchAddress; registered address wins when both types exist.
• Legacy user address rows are retained because the branch model can represent only one operational address and deleting additional source rows would lose data.
• Existing company bank accounts are moved to the headquarters branch.
• If legacy data contains multiple live primary bank accounts, the most recently updated account remains primary and the others are retained as non-primary.

Programs and auctions¶

• Every program receives non-null canonical companyId and headquarters branchId.
• Composite foreign keys prevent a program from referencing a branch owned by another company.
• Auction runtime history is intentionally purged. Deleting each auction cascades through bids, prebids, disqualifications, action logs, announcements, activity events/sequences, participants, and presence sessions.
• Programs, cycles, subscribers, invoices, payments, document submissions, and declared cycle winners are retained. Winner bid/prebid provenance is cleared, cycle auction hosts are reset to NONE, and hasFirstCycleAuction is reset.
• Platform/company auction policies, presets, configuration audit logs, and program auction settings are retained as configuration rather than runtime auction history.

Membership and authorization¶

• Every existing company owner receives an active CompanyMembership.
• Existing company refresh sessions are revoked because they were issued against the owner-user tenancy model. Company users must sign in again.
• The immutable permission catalog and built-in roles are inserted.
• Each owner membership receives a company-scoped OWNER assignment.
• Deferred database triggers enforce at least one active owner at commit.
• Assignment checks enforce role scope, custom-role company, and target tenancy.
• Employee invitation and authorization audit tables are created.

Database invariants¶

The schema enforces the critical boundaries independently of application code:

Migration failure boundaries¶

The migration intentionally aborts instead of inventing ownership when any of these assertions fails:

• any legacy SUB company row exists (the production cutover is explicitly based on the verified absence of sub-branches);
• a company does not map to exactly one headquarters branch;
• a company lacks an active owner membership;
• a program cannot map to exactly one canonical company and headquarters branch;
• a bank account cannot map to a headquarters branch.

Because Prisma applies a PostgreSQL migration transactionally where supported, an assertion failure must be investigated and repaired in source data before rerunning. Do not mark a failed migration as applied.

Release procedure¶

1. Start a maintenance/write-drain window.
2. Capture database backup and record application image/revision.
3. With application workers stopped, inspect and then purge auction BullMQ jobs and runtime presence keys:

pnpm cutover:auction-runtime:prod
pnpm cutover:auction-runtime:prod -- --apply

Use the staging command for the staging environment. The first invocation is a dry run; --apply pauses and obliterates only the auction queue and deletes only Redis keys matching auction:*. Application startup recreates the recurring auction reconciler.

1. Apply the migration with the environment's prisma migrate deploy command.
2. Run the post-migration assertions below.
3. Deploy the backend image and then enable compatible clients.
4. Require company users to sign in again, then run smoke tests as owner, scoped employee, subscriber, and superadmin.
5. Monitor authorization failures, invitation delivery, Redis invalidation, database constraint violations, and scoped list latency.
6. End the maintenance window only after verification passes.

Use the repository's environment commands documented in migrations.md.

Post-migration assertions¶

The following queries must return zero rows unless stated otherwise.

-- Exactly one live headquarters branch per company.
SELECT c.id, COUNT(b.id)
FROM companies c
LEFT JOIN branches b
  ON b.company_id = c.id
 AND b.is_headquarters = true
 AND b.deleted_at IS NULL
GROUP BY c.id
HAVING COUNT(b.id) <> 1;

-- Every program has matching company/branch ownership.
SELECT p.id, p.company_id, p.branch_id
FROM programs p
LEFT JOIN branches b
  ON b.id = p.branch_id
 AND b.company_id = p.company_id
WHERE p.company_id IS NULL
   OR p.branch_id IS NULL
   OR b.id IS NULL;

-- Every active company retains an active owner.
SELECT c.id
FROM companies c
LEFT JOIN company_memberships m
  ON m.company_id = c.id
 AND m.status = 'ACTIVE'
LEFT JOIN membership_role_assignments a ON a.membership_id = m.id
LEFT JOIN access_roles r
  ON r.id = a.role_id
 AND r.is_system = true
 AND r.system_role = 'OWNER'
WHERE c.deleted_at IS NULL
GROUP BY c.id
HAVING COUNT(r.id) = 0;

-- No duplicate live branch code inside a company.
SELECT company_id, branch_code, COUNT(*)
FROM branches
WHERE deleted_at IS NULL AND branch_code IS NOT NULL
GROUP BY company_id, branch_code
HAVING COUNT(*) > 1;

-- No cross-company assignment target.
SELECT a.id
FROM membership_role_assignments a
JOIN company_memberships m ON m.id = a.membership_id
LEFT JOIN branches b ON b.id = a.branch_id
LEFT JOIN programs p ON p.id = a.program_id
WHERE (a.branch_id IS NOT NULL AND b.company_id IS DISTINCT FROM m.company_id)
   OR (a.program_id IS NOT NULL AND p.company_id IS DISTINCT FROM m.company_id);

Also verify migrated row counts against the pre-migration snapshot:

• number of companies equals number of live headquarters branches;
• number of companies equals number of migrated owner memberships;
• all existing programs remain present;
• all existing company bank accounts remain present under headquarters branches;
• all rewritten company-owned records retain their original business owner.
• auction runtime tables contain zero rows;
• retained cycle winners contain no auction bid/prebid identifiers;
• every program cycle has auctionHost = NONE after the purge.

Performance verification¶

Run EXPLAIN (ANALYZE, BUFFERS) with production-shaped parameters for:

• scoped program list by company, branch grants, program grants, and status;
• company/admin auction list through cycle and program ownership;
• branch list by company/status/code search;
• employee list and assignment lookup.

Expected supporting indexes include membership user/status lookup, branch company/status/code, program company/branch/status, invitation company/status, and assignment membership/scope/target indexes. Investigate sequential scans on large tenant tables before release.

Application verification¶

Run the project verification sequence:

pnpm docker:test:up
pnpm check
pnpm build:ci
pnpm test
pnpm docker:test:down

The functional-test reset preserves and restores immutable permissions, built-in roles, and their mappings after table truncation. Tenant fixtures create company, headquarters branch, owner membership, and owner assignment in one transaction so the same deferred owner invariant is exercised in tests.

Security smoke tests must cover:

• owner and company-admin inheritance;
• one-branch, multi-branch, and selected-program employees;
• auction-only and subscription-only separation;
• cross-company guessed IDs returning 404;
• immediate denial after assignment removal or membership suspension;
• company-manager ownership filtering;
• invitation replay and identity conflicts;
• concurrent duplicate branch code and invitation acceptance.

Rollback strategy¶

This migration drops legacy columns, enum values, invitation data, and auction runtime history. A simple down migration cannot reconstruct them. Rollback is therefore release-level:

1. stop writes;
2. restore the verified pre-migration database backup;
3. redeploy the previous backend image;
4. restore the previous clients;
5. verify owner login, program reads, and bank-account data before reopening.

Do not attempt to roll back only the application while keeping the migrated schema; the old application interprets company owner user IDs as tenant IDs and is not compatible with this database.

Related¶

• Company tenancy REST API
• Company authorization
• Database schema
• General migration workflow
• Production rollback