Rate-history baseline + complete history invariant — design (#2413)

Date: 2026-06-11 Issue: #2413 Related: #631 (rate-history table), #637 (line-item calculator/ops preview)

Problem

org_specialist_assignment_rate_history only receives rows from the updateSpecialistAssignment rate-change flow. Two consequences:

1. Never-repriced assignments have zero rows → LineItemCalculatorService.getRateWindows throws "No rate history found" → the ops line-item preview (GET /ops/billing/assignments/:id/line-items/:start/:end) 400s for the common case.
2. Repriced assignments have no coverage before their first change — the close-out logic only closes a prior open row if one exists, so days between assigned_at and the first change are permanently uncovered (gap-validation throws for any period spanning them).
3. (Found during design) assignSpecialist's upsert branch updates an existing assignment's monthlyRateConfig without writing a history row at all — a rate changed via re-assign silently corrupts the timeline.

Invariant being established

Every assignment has a contiguous, gap-free rate history from assigned_at::date onward: exactly one open row (end_date IS NULL) holding the currently-effective rate; earlier rows closed off contiguously. Every code path that writes assignment rates maintains it.

Design

1. Shared writer — api/src/organizations/assignment-rate-history.writer.ts

New injectable AssignmentRateHistoryWriter (registered in OrganizationsModule), two methods, both taking the rate-history Repository as a parameter so callers can pass a transaction-scoped repo (em.getRepository(...)) or the injected default:

• seedBaseline(repo, { assignmentId, assignedAt, rateConfig, userId }) — inserts the open-ended baseline row: effective_date = assignedAt date, end_date = NULL, change_reason = "baseline (assignment created)". Idempotent via the (assignment_id, effective_date) unique (find-then-skip; a concurrent duplicate resolves via the constraint). Skips (returns false) when rateConfig is null — rate_config is NOT NULL and "no rate configured" must stay detectable rather than seeded as 0.
• recordChange(repo, { assignmentId, effectiveDateIso, rateConfig, userId, reason }) — the close-out + idempotent-upsert logic extracted verbatim from updateSpecialistAssignment.runHistoryWrite: close the prior open row at effectiveDate − 1 (skipping when the prior open row IS the row being re-upserted), then upsert on (assignment_id, effective_date).

Rate resolution for the baseline mirrors AssignmentBillingInputResolver: assignment monthlyRateConfig else the Specialist's catalog monthlyRateConfig, else null (skip).

2. Call sites

Path	Action
assignSpecialist, create branch (after saved = …save(assignment))	seedBaseline with the resolved config; userId = amId
assignSpecialist, existing-assignment branch when dto.monthlyRateConfig !== undefined and the config actually changed	recordChange with effectiveDateIso = today, reason "rate changed via re-assign"
updateSpecialistAssignment.runHistoryWrite	delegates to recordChange — behavior-preserving refactor; the existing transaction wrapper, 30-day/365-day effective-date validation, and rateChanged detection stay where they are

History writes in assignSpecialist are not fail-open (unlike billing hooks): they are cheap local DB writes that uphold a data invariant; failing loudly is correct. They run after the assignment save (no existing transaction in that path — acceptable: a crash between save and seed leaves the pre-#2413 state, which the backfill query pattern or a re-run of the migration repairs; not worth adding a transaction to the long method).

3. Backfill migration — migrations/1799000000002-BackfillRateHistoryBaseline.ts

Single data migration (Postgres-only guard like 1796000000003):

INSERT INTO org_specialist_assignment_rate_history
  (id, assignment_id, effective_date, end_date, rate_config, changed_by_user_id, change_reason)
SELECT gen_random_uuid(), a.id, a.assigned_at::date, NULL,
       COALESCE(a.monthly_rate_config, s.monthly_rate_config), NULL, 'backfill #2413'
FROM org_specialist_assignments a
LEFT JOIN specialists s ON s.id = a.specialist_id
WHERE NOT EXISTS (SELECT 1 FROM org_specialist_assignment_rate_history h WHERE h.assignment_id = a.id)
  AND COALESCE(a.monthly_rate_config, s.monthly_rate_config) IS NOT NULL
ON CONFLICT (assignment_id, effective_date) DO NOTHING

down deletes rows with change_reason = 'backfill #2413'.

Deliberately not backfilled: assignments that already have history rows. Their pre-first-change rate is unknowable — inventing one is false data. Documented limitation: the ops preview works for those from the first recorded change onward.

4. Untouched

LineItemCalculatorService — its strict gap validation becomes the invariant enforcer. The seeding turns its "No rate history found" path from a common-case bug into a genuine anomaly signal.

Testing

1. Writer unit spec: baseline insert shape; skip-on-null-config; recordChange close-out; idempotent re-upsert at same effective date; close skipped when prior open row is the upsert target.
2. assignSpecialist: create → baseline row exists (assignment-override rate and catalog-rate fallback variants; none → no row); re-assign with changed rate → change row + prior baseline closed; re-assign without rate → no history write.
3. updateSpecialistAssignment: existing specs stay green (refactor is behavior-preserving).
4. Line-item preview: never-repriced assignment with seeded baseline returns one full-period window at the current rate (was: throws).
5. Migration exercised by the CI api-postgres-migrations job.