feat(migrations): no-op backfill + verification for co-mingled default-tenant rows

David commented

2026-06-13 16:00:55 +02:00

Owner

Step 8 of PMS-255 per-user isolation. The product owner resolved the open "Legacy co-mingled data ownership" decision (2026-06-13): Mokosh is not in production and the database is wiped before go-live, so there is no legacy co-mingled business data to re-home or quarantine. The data backfill is therefore intentionally a no-op. This also sidesteps the unsafe one-shot path flagged earlier, where personal tenants are provisioned lazily on login (PMS-243/245) and a bulk SQL backfill would have no tenant to resolve most owners to.

Adds migration 040_backfill_comingled_default_tenant.sql: instead of moving data it asserts, fail-loud at migrate time, the invariant this issue exists to prove: under personal-tenant isolation no user-created (business) row may sit in the shared default tenant 00000000-0000-0000-0000-000000000001 (the co-mingling landing zone every normal user is re-homed off of). Seed lookup/config rows (migration 023) legitimately stay in the default tenant as the per-tenant copy template and are excluded; auth and sequence tables are excluded too. The check is read-only, so it is idempotent and safe to re-run, and it runs in every #[sqlx::test] setup. On a fresh/wiped DB the business tables hold zero default-tenant rows, so it passes by construction; a co-mingled row RAISES with the offending tables instead of leaving a silent cross-user leak.

Adds dev-docs/audits/pms-263-verify-no-comingled-business-rows.sql: the standalone, human-runnable form of the same query (one row per business table, comingled_rows must be 0) for attaching audit output to a PR. Verified against a migrated Postgres: all 36 business tables report 0; the negative case (an injected default-tenant row) trips the assertion. Business-table set and child-table parent joins (invoice_lines, kb_article_versions) follow the PMS-255 inventory in dev-docs/rls-per-user-isolation.md, which is updated to mark the decision resolved.

#PMS-263

Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com

Step 8 of PMS-255 per-user isolation. The product owner resolved the open "Legacy co-mingled data ownership" decision (2026-06-13): Mokosh is not in production and the database is wiped before go-live, so there is no legacy co-mingled business data to re-home or quarantine. The data backfill is therefore intentionally a no-op. This also sidesteps the unsafe one-shot path flagged earlier, where personal tenants are provisioned lazily on login (PMS-243/245) and a bulk SQL backfill would have no tenant to resolve most owners to. Adds migration 040_backfill_comingled_default_tenant.sql: instead of moving data it asserts, fail-loud at migrate time, the invariant this issue exists to prove: under personal-tenant isolation no user-created (business) row may sit in the shared default tenant 00000000-0000-0000-0000-000000000001 (the co-mingling landing zone every normal user is re-homed off of). Seed lookup/config rows (migration 023) legitimately stay in the default tenant as the per-tenant copy template and are excluded; auth and sequence tables are excluded too. The check is read-only, so it is idempotent and safe to re-run, and it runs in every `#[sqlx::test]` setup. On a fresh/wiped DB the business tables hold zero default-tenant rows, so it passes by construction; a co-mingled row RAISES with the offending tables instead of leaving a silent cross-user leak. Adds dev-docs/audits/pms-263-verify-no-comingled-business-rows.sql: the standalone, human-runnable form of the same query (one row per business table, comingled_rows must be 0) for attaching audit output to a PR. Verified against a migrated Postgres: all 36 business tables report 0; the negative case (an injected default-tenant row) trips the assertion. Business-table set and child-table parent joins (invoice_lines, kb_article_versions) follow the PMS-255 inventory in dev-docs/rls-per-user-isolation.md, which is updated to mark the decision resolved. #PMS-263 Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

David added 1 commit

2026-06-13 16:00:55 +02:00

feat(migrations): no-op backfill + verification for co-mingled default-tenant rows

E2E (staging) / Playwright against staging (pull_request) Failing after 32s

Details

Check / fmt + clippy + compile + tests (pull_request) Has been cancelled

Details

Build OCI container / Build and push mokosh-api image (push) Successful in 3m53s

Details

8968aa0538

Step 8 of PMS-255 per-user isolation. The product owner resolved the open "Legacy co-mingled data ownership" decision (2026-06-13): Mokosh is not in production and the database is wiped before go-live, so there is no legacy co-mingled business data to re-home or quarantine. The data backfill is therefore intentionally a no-op. This also sidesteps the unsafe one-shot path flagged earlier, where personal tenants are provisioned lazily on login (PMS-243/245) and a bulk SQL backfill would have no tenant to resolve most owners to.

Adds migration 040_backfill_comingled_default_tenant.sql: instead of moving data it asserts, fail-loud at migrate time, the invariant this issue exists to prove: under personal-tenant isolation no user-created (business) row may sit in the shared default tenant 00000000-0000-0000-0000-000000000001 (the co-mingling landing zone every normal user is re-homed off of). Seed lookup/config rows (migration 023) legitimately stay in the default tenant as the per-tenant copy template and are excluded; auth and sequence tables are excluded too. The check is read-only, so it is idempotent and safe to re-run, and it runs in every `#[sqlx::test]` setup. On a fresh/wiped DB the business tables hold zero default-tenant rows, so it passes by construction; a co-mingled row RAISES with the offending tables instead of leaving a silent cross-user leak.

Adds dev-docs/audits/pms-263-verify-no-comingled-business-rows.sql: the standalone, human-runnable form of the same query (one row per business table, comingled_rows must be 0) for attaching audit output to a PR. Verified against a migrated Postgres: all 36 business tables report 0; the negative case (an injected default-tenant row) trips the assertion. Business-table set and child-table parent joins (invoice_lines, kb_article_versions) follow the PMS-255 inventory in dev-docs/rls-per-user-isolation.md, which is updated to mark the decision resolved.

#PMS-263

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

David added 1 commit

2026-06-13 16:03:23 +02:00

Merge branch 'main' into feat/pms-263-comingled-backfill-verification

Create release / Create release from merged PR (pull_request) Has been skipped

Details

E2E (staging) / Playwright against staging (pull_request) Failing after 15s

Details

Check / fmt + clippy + compile + tests (pull_request) Successful in 1m30s

Details

2e65ee8df1