docs(sla): JSONB-vs-normalized decision for schedule/holidays (PMS-585) #414

Merged

longjacksonle merged 1 commit from docs/PMS-585-sla-jsonb-decision into main

2026-06-30 21:25:56 +02:00

longjacksonle commented

2026-06-30 21:14:20 +02:00

Owner

PMS-585 (raised in PMS-567 SLA smoke)

Evaluate whether business_hours.schedule and holiday_calendars.holidays (JSONB) should be normalized into child tables. This is a design/research ticket; the deliverable is a recorded decision.

Decision: keep JSONB

Adds dev-docs/sla-jsonb-vs-normalized.md. Grounded in how the SLA module actually uses the data (verified across src/modules/sla/):

No SQL ever reaches into the JSON (no -> / ->> / @> / jsonb_array_elements / json-path WHERE anywhere). Every read SELECTs the whole column.
The due-time engine (clock.rs) loads each blob whole, parses to BusinessSchedule + HashSet<NaiveDate>, and computes deadlines in Rust, writing back plain timestamp columns.
Writes are whole-blob replace; data is small/bounded (~7 weekday entries, short holiday list); nothing queries across calendars/days.

Normalizing would add a migration + backfill + read/write/editor rewrites for SQL queryability and constraints the app does not use. The layer that needs relational querying (sla_policies / sla_targets) is already normalized. The doc captures the full trade-off table and the triggers that would justify revisiting (cross-tenant holiday reporting, DB-enforced per-day constraints, unbounded growth).

One real gap (storage-independent)

Upserts validate only name; schedule/holidays accept any JSON shape and malformed content is silently skipped at read time, which can distort SLA math. That is a validation gap, not a storage one - fix with Rust parse-and-reject on write (422), no normalization required. Filed as a separate follow-up ticket.

No code/behavior change

Documentation only.

🤖 Generated with Claude Code

## PMS-585 (raised in PMS-567 SLA smoke) Evaluate whether `business_hours.schedule` and `holiday_calendars.holidays` (JSONB) should be normalized into child tables. This is a design/research ticket; the deliverable is a recorded decision. ## Decision: keep JSONB Adds `dev-docs/sla-jsonb-vs-normalized.md`. Grounded in how the SLA module actually uses the data (verified across `src/modules/sla/`): - No SQL ever reaches into the JSON (no `->` / `->>` / `@>` / `jsonb_array_elements` / json-path WHERE anywhere). Every read SELECTs the whole column. - The due-time engine (`clock.rs`) loads each blob whole, parses to `BusinessSchedule` + `HashSet<NaiveDate>`, and computes deadlines in Rust, writing back plain timestamp columns. - Writes are whole-blob replace; data is small/bounded (~7 weekday entries, short holiday list); nothing queries across calendars/days. Normalizing would add a migration + backfill + read/write/editor rewrites for SQL queryability and constraints the app does not use. The layer that needs relational querying (`sla_policies` / `sla_targets`) is already normalized. The doc captures the full trade-off table and the triggers that would justify revisiting (cross-tenant holiday reporting, DB-enforced per-day constraints, unbounded growth). ## One real gap (storage-independent) Upserts validate only `name`; `schedule`/`holidays` accept any JSON shape and malformed content is silently skipped at read time, which can distort SLA math. That is a validation gap, not a storage one - fix with Rust parse-and-reject on write (422), no normalization required. Filed as a separate follow-up ticket. ## No code/behavior change Documentation only. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

longjacksonle added 1 commit

2026-06-30 21:14:20 +02:00

docs(sla): record JSONB-vs-normalized decision for schedule/holidays (PMS-585)

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

Details

Check / fmt + clippy + build + tests (pull_request) Successful in 1m44s

Details

Integration / integration tests (pull_request) Successful in 8m37s

Details

Create release / Gate (release-branch merges only) (pull_request) Successful in 1s

Details

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

Details

028c196b78

PMS-567 asked whether the SLA business-hours/holiday JSON storage is the right approach. Decision: keep business_hours.schedule and holiday_calendars.holidays as JSONB. Grounded in how the code uses them: no SQL ever reaches into the JSON, the due-time engine loads each blob whole and computes in Rust, writes are whole-blob, the data is small/bounded (~7 weekday entries, short holiday list), and nothing queries across calendars/days. Normalizing would add a migration + backfill + read/write/editor rewrites for queryability and constraints the app does not use; the layer that needs relational querying (sla_policies/targets) is already normalized. Doc captures the trade-offs, the revisit triggers, and the one real (storage-independent) gap: upserts do not validate the JSON shape - tracked separately.

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