psa-systems/mokosh-server
Watch
9
0
Fork
You've already forked mokosh-server
 1
Code Issues Pull requests Projects Releases 10 Packages Wiki Activity Actions

feat(tenants): seed all editable lookups per tenant; reserve system-shared class #189

Merged
David merged 3 commits from feat/pms-259-seed-lookups-system-shared-class into main 2026-06-13 13:06:56 +02:00
Conversation 0 Commits 3 Files changed 4 +609 -34
David commented 2026-06-12 15:22:10 +02:00
Owner
Copy link

Under the personal-tenant-per-user isolation model (PMS-255) every user owns their own copy of the editable lookup / configuration tables, so a freshly provisioned tenant must start with a sensible default set instead of empty status / priority / type / work-type lists.

Extend TenantService::copy_default_config to seed the full editable-lookup set from the migration-023 default-tenant rows, re-scoped to the new tenant: business_hours, ticket_categories (parents + children), ticket_queues, time_rounding_rules, tax_rates, kb_categories, rate_cards + rate_card_items, and sla_policies + sla_targets now seed alongside the statuses / priorities / types / work types / task statuses / asset types / module config / notification templates that were already copied. Inter-lookup foreign keys (sla_policies -> business_hours, sla_targets -> sla_policies / ticket_priorities, rate_card_items -> rate_cards / work_types, child categories -> parents) are re-linked to the new tenant's freshly copied rows by name. The whole copy now runs in one transaction and is idempotent: it skips entirely when the tenant already holds ticket_statuses rows, so a retried provisioning never double-seeds.

Reserve the system-shared read-only class structurally (migration 038): a tenant_id IS NULL row means "global / system-shared". The migration-024 tenant_isolation RLS policy is recreated with an OR tenant_id IS NULL read clause so global rows are visible to every tenant, a mokosh_guard_system_shared_row() trigger function forbids writes to global rows unless the session sets app.allow_system_writes = 'on', and mokosh_enable_system_shared(table) lets a table opt in later (drop the NOT NULL, attach the guard). No table opts in and no global row exists yet; this only reserves the room. Flipping the tenant match fail-closed and adding WITH CHECK stays with PMS-257.

Record the table classification and the system-shared mechanism in dev-docs/rls-per-user-isolation.md, including the confirmed borderline calls (business_hours is editable-lookup; payment_gateway_configs and email_mailboxes are per-tenant business rows, not lookups). Add tests/personal_tenant_seed.rs: a fresh user gets the full default lookup set isolated from other users and the seed is idempotent, and the system-shared guard rejects an unprivileged global write but accepts a privileged one.

#PMS-259

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

Under the personal-tenant-per-user isolation model (PMS-255) every user owns their own copy of the editable lookup / configuration tables, so a freshly provisioned tenant must start with a sensible default set instead of empty status / priority / type / work-type lists. Extend `TenantService::copy_default_config` to seed the full editable-lookup set from the migration-023 default-tenant rows, re-scoped to the new tenant: business_hours, ticket_categories (parents + children), ticket_queues, time_rounding_rules, tax_rates, kb_categories, rate_cards + rate_card_items, and sla_policies + sla_targets now seed alongside the statuses / priorities / types / work types / task statuses / asset types / module config / notification templates that were already copied. Inter-lookup foreign keys (sla_policies -> business_hours, sla_targets -> sla_policies / ticket_priorities, rate_card_items -> rate_cards / work_types, child categories -> parents) are re-linked to the new tenant's freshly copied rows by name. The whole copy now runs in one transaction and is idempotent: it skips entirely when the tenant already holds ticket_statuses rows, so a retried provisioning never double-seeds. Reserve the system-shared read-only class structurally (migration 038): a `tenant_id IS NULL` row means "global / system-shared". The migration-024 tenant_isolation RLS policy is recreated with an `OR tenant_id IS NULL` read clause so global rows are visible to every tenant, a `mokosh_guard_system_shared_row()` trigger function forbids writes to global rows unless the session sets `app.allow_system_writes = 'on'`, and `mokosh_enable_system_shared(table)` lets a table opt in later (drop the NOT NULL, attach the guard). No table opts in and no global row exists yet; this only reserves the room. Flipping the tenant match fail-closed and adding WITH CHECK stays with PMS-257. Record the table classification and the system-shared mechanism in dev-docs/rls-per-user-isolation.md, including the confirmed borderline calls (business_hours is editable-lookup; payment_gateway_configs and email_mailboxes are per-tenant business rows, not lookups). Add tests/personal_tenant_seed.rs: a fresh user gets the full default lookup set isolated from other users and the seed is idempotent, and the system-shared guard rejects an unprivileged global write but accepts a privileged one. #PMS-259 Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
feat(tenants): seed all editable lookups per tenant; reserve system-shared class
All checks were successful
E2E (staging) / Playwright against staging (pull_request) Successful in 40s
Details
Check / fmt + clippy + compile + tests (pull_request) Successful in 1m44s
Details
Build OCI container / Build and push mokosh-api image (push) Successful in 3m38s
Details
78c5992147 
Under the personal-tenant-per-user isolation model (PMS-255) every user owns their own copy of the editable lookup / configuration tables, so a freshly provisioned tenant must start with a sensible default set instead of empty status / priority / type / work-type lists.

Extend `TenantService::copy_default_config` to seed the full editable-lookup set from the migration-023 default-tenant rows, re-scoped to the new tenant: business_hours, ticket_categories (parents + children), ticket_queues, time_rounding_rules, tax_rates, kb_categories, rate_cards + rate_card_items, and sla_policies + sla_targets now seed alongside the statuses / priorities / types / work types / task statuses / asset types / module config / notification templates that were already copied. Inter-lookup foreign keys (sla_policies -> business_hours, sla_targets -> sla_policies / ticket_priorities, rate_card_items -> rate_cards / work_types, child categories -> parents) are re-linked to the new tenant's freshly copied rows by name. The whole copy now runs in one transaction and is idempotent: it skips entirely when the tenant already holds ticket_statuses rows, so a retried provisioning never double-seeds.

Reserve the system-shared read-only class structurally (migration 038): a `tenant_id IS NULL` row means "global / system-shared". The migration-024 tenant_isolation RLS policy is recreated with an `OR tenant_id IS NULL` read clause so global rows are visible to every tenant, a `mokosh_guard_system_shared_row()` trigger function forbids writes to global rows unless the session sets `app.allow_system_writes = 'on'`, and `mokosh_enable_system_shared(table)` lets a table opt in later (drop the NOT NULL, attach the guard). No table opts in and no global row exists yet; this only reserves the room. Flipping the tenant match fail-closed and adding WITH CHECK stays with PMS-257.

Record the table classification and the system-shared mechanism in dev-docs/rls-per-user-isolation.md, including the confirmed borderline calls (business_hours is editable-lookup; payment_gateway_configs and email_mailboxes are per-tenant business rows, not lookups). Add tests/personal_tenant_seed.rs: a fresh user gets the full default lookup set isolated from other users and the seed is idempotent, and the system-shared guard rejects an unprivileged global write but accepts a privileged one.

#PMS-259

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
# Conflicts:
#	dev-docs/rls-per-user-isolation.md
fix(rls): renumber system-shared migration to 039; layer on PMS-257 fail-closed policy
All checks were successful
E2E (staging) / Playwright against staging (pull_request) Successful in 39s
Details
Create release / Create release from merged PR (pull_request) Has been skipped
Details
Check / fmt + clippy + compile + tests (pull_request) Successful in 1m54s
Details
Build OCI container / Build and push mokosh-api image (push) Successful in 3m47s
Details
57804d1df4 
PMS-257 (038_rls_fail_closed.sql) landed on main and also recreates the tenant_isolation policy on every tenant_id table, so two issues had to be resolved when merging main into this branch. First, both migrations were numbered 038, which sqlx rejects as a duplicate version; the system-shared migration is renumbered to 039 so it applies after the fail-closed flip. Second, the system-shared migration was written against the original fail-open 024 policy and recreated it USING-only with a COALESCE fallback, which would have silently reverted PMS-257's fail-closed USING and WITH CHECK when it ran after 038. The read-side recreation is rebased to preserve PMS-257's posture (fail-closed tenant match plus WITH CHECK) and only adds the `tenant_id IS NULL` disjunct on the read side; WITH CHECK is deliberately not widened, so the unprivileged application role still cannot create a global row.

Verified by applying every migration in order against a scratch database (resulting policy is USING ((tenant_id IS NULL) OR tenant_id = guc) with WITH CHECK (tenant_id = guc)) and by running tests/personal_tenant_seed.rs and main's tests/rls_isolation.rs, all green. Doc and test comments updated from migration 038 to 039.

#PMS-259

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
David merged commit 4028957292 into main 2026-06-13 13:06:56 +02:00
David deleted branch feat/pms-259-seed-lookups-system-shared-class 2026-06-13 13:06:57 +02:00
Sign in to join this conversation.
Reviewers
No reviewers
Labels
Clear labels
No items
No labels
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
psa-systems/mokosh-server!189
No description provided.