harden(lookups): DB partial unique index for single-default per tenant #242

Merged

nrupard merged 1 commit from feat/PMS-323-single-default-unique-index into main

2026-06-15 18:23:22 +02:00

David commented

2026-06-15 18:02:53 +02:00

Owner

Adds migration 049 with a partial unique index idx_<table>_one_default ON <table> (tenant_id) WHERE is_default for the five tenant-scoped lookup tables that carry an is_default flag (tax_rates, ticket_statuses, ticket_priorities, ticket_queues, project_types), so Postgres guarantees at most one default per tenant independent of the service layer.

Each index is preceded by a defensive UPDATE that collapses any pre-existing duplicate defaults to a single surviving row per tenant so the index builds cleanly. tax_rates has no sort_order column and is deduped by (created_at, id); the other four keep the lowest sort_order as the surviving default.

No service change was required: every create/update already clears the prior default before setting the new row inside one begin_with_tenant transaction, so the immediate (non-deferred) index is satisfied at statement end. The existing project_type_new_default_clears_prior and tickets setting_new_default_status_clears_prior tests confirm the clear-then-set flow still succeeds under the index. Adds project_type_second_default_violates_db_index asserting a direct-SQL second default in one tenant raises a unique_violation (23505).

#PMS-323

Adds migration 049 with a partial unique index `idx_<table>_one_default ON <table> (tenant_id) WHERE is_default` for the five tenant-scoped lookup tables that carry an `is_default` flag (tax_rates, ticket_statuses, ticket_priorities, ticket_queues, project_types), so Postgres guarantees at most one default per tenant independent of the service layer. Each index is preceded by a defensive UPDATE that collapses any pre-existing duplicate defaults to a single surviving row per tenant so the index builds cleanly. tax_rates has no sort_order column and is deduped by (created_at, id); the other four keep the lowest sort_order as the surviving default. No service change was required: every create/update already clears the prior default before setting the new row inside one begin_with_tenant transaction, so the immediate (non-deferred) index is satisfied at statement end. The existing project_type_new_default_clears_prior and tickets setting_new_default_status_clears_prior tests confirm the clear-then-set flow still succeeds under the index. Adds project_type_second_default_violates_db_index asserting a direct-SQL second default in one tenant raises a unique_violation (23505). #PMS-323

David added 1 commit

2026-06-15 18:02:53 +02:00

harden(lookups): DB partial unique index for single-default per tenant

E2E / Playwright against staging (pull_request) Successful in 44s

Details

Check / fmt + clippy + compile + unit/doc tests (pull_request) Successful in 1m9s

Details

Integration / integration tests (pull_request) Successful in 3m56s

Details

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

Details

fd150231ec

Adds migration 049 with a partial unique index `idx_<table>_one_default ON <table> (tenant_id) WHERE is_default` for the five tenant-scoped lookup tables that carry an `is_default` flag (tax_rates, ticket_statuses, ticket_priorities, ticket_queues, project_types), so Postgres guarantees at most one default per tenant independent of the service layer.

Each index is preceded by a defensive UPDATE that collapses any pre-existing duplicate defaults to a single surviving row per tenant so the index builds cleanly. tax_rates has no sort_order column and is deduped by (created_at, id); the other four keep the lowest sort_order as the surviving default.

No service change was required: every create/update already clears the prior default before setting the new row inside one begin_with_tenant transaction, so the immediate (non-deferred) index is satisfied at statement end. The existing project_type_new_default_clears_prior and tickets setting_new_default_status_clears_prior tests confirm the clear-then-set flow still succeeds under the index. Adds project_type_second_default_violates_db_index asserting a direct-SQL second default in one tenant raises a unique_violation (23505).

#PMS-323