feat(migrations): split 1688-line initial schema into per-feature files (PMS-128) #66

Merged

nrupard merged 1 commit from feat/pms-128-migration-split into main

2026-06-04 21:03:31 +02:00

nrupard commented

2026-06-04 20:51:25 +02:00

Owner

Summary

Split the 1688-line migrations/001_initial_schema.sql monolith into 16 per-feature files (002_tenants ... 017_settings) so each future feature module adds its own migration without touching others. Closes cross-cutting issue F14 from dev-docs/codebase-state.md.
Renumbered the existing follow-up migrations (and 002_seed_data) so they continue to run after every table they touch exists. New tail: 018_user_oauth_identities ... 022_rmm_mesh_central_provider, then 023_seed_data, then 024_triggers_and_rls.
Extracted the updated_at trigger function and the dynamic RLS-enable DO $$ ... $$ blocks from the tail of the old monolith into 024_triggers_and_rls.sql. Keeping them dynamic (vs hand-rolling triggers per file) means any future feature can add a table with tenant_id/updated_at and inherit both behaviors without touching 024. Cost: the loop now also scans tables added in 018+; the one such table (user_oauth_identities) has neither column so it is correctly skipped.

Breaking change

_sqlx_migrations on any non-fresh database only knows about the old 001 + 002_seed_data + 003-007. After this PR, sqlx::migrate! will refuse to start against such a DB. The dev compose stack wipes volumes via just dev-clean; CI uses fresh per-test databases via #[sqlx::test]. Both flows work without intervention. Anyone with a long-lived local DB should just dev-clean (or drop + recreate the DB) before pulling.

Test plan

CI: cargo fmt --all --check
CI: cargo clippy --all-targets -- -D warnings
CI: cargo check --all-targets
CI: integration tests pass against a fresh Postgres applying the full chain (auth, contacts, tenants, tickets, time_tracking, notifications, rmm, dispatch_stub).
Manual: just dev-clean && just dev && just migrate-run succeeds end-to-end.

Closes PMS-128.

## Summary - Split the 1688-line `migrations/001_initial_schema.sql` monolith into 16 per-feature files (`002_tenants` ... `017_settings`) so each future feature module adds its own migration without touching others. Closes cross-cutting issue F14 from `dev-docs/codebase-state.md`. - Renumbered the existing follow-up migrations (and `002_seed_data`) so they continue to run after every table they touch exists. New tail: `018_user_oauth_identities` ... `022_rmm_mesh_central_provider`, then `023_seed_data`, then `024_triggers_and_rls`. - Extracted the `updated_at` trigger function and the dynamic RLS-enable `DO $$ ... $$` blocks from the tail of the old monolith into `024_triggers_and_rls.sql`. Keeping them dynamic (vs hand-rolling triggers per file) means any future feature can add a table with `tenant_id`/`updated_at` and inherit both behaviors without touching `024`. Cost: the loop now also scans tables added in `018+`; the one such table (`user_oauth_identities`) has neither column so it is correctly skipped. ## Breaking change `_sqlx_migrations` on any non-fresh database only knows about the old `001` + `002_seed_data` + `003-007`. After this PR, `sqlx::migrate!` will refuse to start against such a DB. The dev compose stack wipes volumes via `just dev-clean`; CI uses fresh per-test databases via `#[sqlx::test]`. Both flows work without intervention. Anyone with a long-lived local DB should `just dev-clean` (or drop + recreate the DB) before pulling. ## Test plan - [ ] CI: `cargo fmt --all --check` - [ ] CI: `cargo clippy --all-targets -- -D warnings` - [ ] CI: `cargo check --all-targets` - [ ] CI: integration tests pass against a fresh Postgres applying the full chain (`auth`, `contacts`, `tenants`, `tickets`, `time_tracking`, `notifications`, `rmm`, `dispatch_stub`). - [ ] Manual: `just dev-clean && just dev && just migrate-run` succeeds end-to-end. Closes PMS-128.

nrupard added 1 commit

2026-06-04 20:51:25 +02:00

feat(migrations): split 1688-line initial schema into per-feature files (PMS-128)

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

Details

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

Details

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

Details

813c1c1767

Closes cross-cutting issue F14 (`dev-docs/codebase-state.md`): the single `001_initial_schema.sql` covered 71 tables across every PSA module, so every feature story that needed a schema tweak had to touch the same monolith. The audit notes pre-prod is the cheapest moment to break it apart; this PR does that.

New per-feature files, in dependency order:

- `002_tenants.sql` (extensions + tenants + default tenant row)
- `003_auth.sql` (users, sessions, api keys, password resets, teams)
- `004_contacts.sql` (companies, contacts, sites)
- `005_tickets.sql` (ticket configuration + tickets + automation + email integration + SLA — SLA targets reference ticket_priorities, so the SLA tables live in the same file as tickets)
- `006_time_tracking.sql`
- `007_projects.sql`
- `008_calendar.sql`
- `009_contracts.sql`
- `010_billing.sql`
- `011_assets.sql`
- `012_knowledge_base.sql`
- `013_notifications.sql`
- `014_rmm.sql`
- `015_audit.sql`
- `016_files.sql`
- `017_settings.sql`

Follow-up migrations slot in after the split so they keep altering the tables they previously altered. Old 003-007 + old 002_seed_data renumbered:

- `018_user_oauth_identities.sql` (was 003)
- `019_tenant_kind.sql` (was 004)
- `020_notification_dispatcher_backoff.sql` (was 005)
- `021_notification_dispatcher_defaults.sql` (was 006)
- `022_rmm_mesh_central_provider.sql` (was 007)
- `023_seed_data.sql` (was 002, now runs last so every referenced table exists)
- `024_triggers_and_rls.sql` (extracted from the tail of 001 — the `updated_at` trigger function plus the two `DO $$ ... $$` blocks that iterate `information_schema` to attach triggers and enable RLS on every matching table; must run last so the dynamic scan sees the new tables added by 018+)

The dynamic trigger/RLS blocks were not removed: keeping them lets a future feature module add a `CREATE TABLE` with `tenant_id` / `updated_at` and inherit both behaviors without a manual `ENABLE ROW LEVEL SECURITY` per migration. The cost is that the dynamic loop now also runs over the `user_oauth_identities` table created in 018; that table has neither `tenant_id` nor `updated_at`, so the loops correctly skip it.

BREAKING for any non-fresh database. `_sqlx_migrations` only knows about `001` and the old follow-ups; after this PR sqlx will refuse to start against a DB that already has those rows. The dev compose stack wipes volumes via `just dev-clean`; CI uses fresh per-test databases via `#[sqlx::test]`. Both flows work without intervention. Anyone with a long-lived local DB should `just dev-clean` (or drop + recreate the DB) before pulling.

#PMS-128