claude/zealous-lovelace-cbc97f

CLAUDE.md references old migration filename 0061_common_boomerang.sql

packages/database/CLAUDE.md

The Notable Tables entry for utility_contract_overview_reconcile_runs cites '0061_common_boomerang.sql' as where the out-of-band SQL (reconcile function + pg_cron) lives. That filename no longer exists — it was re-slotted to 0064_strong_echo.sql. A developer following this note to find the SQL or the operational runbook for applying the cron schedule out-of-band will look for a file that doesn't exist.

Runbook references old migration filename 0061_common_boomerang.sql

docs/development/rpu-history-promote-runbook.md

Line 109 refers to 0061_common_boomerang.sql under a Re-slot #215 note. Same stale reference as packages/database/CLAUDE.md — the actual file is now 0064_strong_echo.sql.

buildUniverseWhere terminated filter applies to statusCounts — verify lifecyclePhase never diverges from contractStatus

domains/utility/src/utility-contract-overview/utility-contract-overview.queries.ts

Adding ne(t.contractStatus,'terminated') to the universe means a terminated contract is completely invisible to countOverviewByLifecyclePhase. If a contract can be in contractStatus='terminated' while having a non-terminated lifecyclePhase value (the phase is a 3-level resolution that may not track contractStatus 1:1), the sidebar count would silently zero out that phase bucket. The design is intentional per the comment, but the invariant 'terminated contractStatus → all lifecycle phases excluded' should be verified.

Sub-ms PostgREST exposure window between CREATE FUNCTION and REVOKE

packages/database/drizzle/0064_strong_echo.sql

The CREATE OR REPLACE FUNCTION and REVOKE EXECUTE are two separate drizzle-breakpoint statements, not atomic. There is a sub-millisecond window where PostgREST could theoretically expose rpc/utility_contract_overview_reconcile to authenticated/anon callers before the REVOKE fires. In practice the reconcile function only writes aggregate counts to reconcile_runs (no data exfiltration risk) and the window is sub-ms. This is a systemic pattern consistent with 0045/0063. If hardening is desired, issue the REVOKE before the CREATE OR REPLACE in a single DO $$ block.

runOverviewReconcile throws instead of returning Result<T,E> — ADR-016 violation

domains/utility/src/utility-contract-overview/utility-contract-overview.queries.ts

ADR-016 requires all fallible operations return Result<T,E>; thrown exceptions are banned. runOverviewReconcile throws a bare Error when the SQL function returns no row. All other query functions in this module return Entity | null (for gets) or plain values (for aggregates) — only shells/decisions use Result. This fallible path that throws should return Result<OverviewReconcileSummary, {_tag:'ReconcileInvariantError', statusCode:500}> and propagate to the calling shell. The throw is seen in cfe-job.queries.ts:331 as well (pre-existing pattern), so this is a recurring deviation from ADR-016 worth tracking.

runOverviewReconcile JSDoc explains WHAT rather than WHY

domains/utility/src/utility-contract-overview/utility-contract-overview.queries.ts

The 8-line JSDoc on runOverviewReconcile has an opening paragraph describing WHAT the function does (recompute every overview row, diff, log, RAISE WARNING, pg_notify) — derivable from the function name and the SQL it calls. The genuine WHY paragraphs (why it delegates to a SQL function for cron/Lambda parity; why it takes Database not DbOrTx for the temp-table + advisory-lock constraint) are correct and non-obvious. The first paragraph should be removed, keeping only the constraint-explanation paragraphs.

schema/index.ts inline comment for reconcile_runs table explains purpose rather than constraint

packages/database/src/schema/index.ts

The two-line comment above the utilityContractOverviewReconcileRuns export ('Append-only audit log for the nightly read-model reconcile-and-repair job (ADR-020 drift follow-up). Backend-only; written by the reconcile function.') describes WHAT the table is, not a non-obvious constraint. 'Backend-only' is the one useful signal but is derivable from the service_role-only RLS policy. Borderline — consistent with the file's existing grouping comment style but technically against the 'only WHY' rule.

findOverviewByContractPublicId has zero test coverage including the cross-org null-return path

domains/utility/src/utility-contract-overview/__tests__/utility-contract-overview.integration.test.ts

The function is the org-access gate in the contract chapters handler (utility-contracts.handler.ts:942). Three cases need coverage: (a) returns null when orgId doesn't match — the cross-org probe → handler notFound security boundary; (b) returns row when orgId matches; (c) returns row for a terminated contract (status-blind by design). None of these cases appear in any test file. Case (a) is the security boundary: wrong-org request must get null to trigger notFound.

ne(t.contractStatus, 'terminated') in buildUniverseWhere has no regression test

domains/utility/src/utility-contract-overview/__tests__/utility-contract-overview.integration.test.ts

The guard is the sole thing preventing a terminated-but-still-linked contract (one with a site_utility_contracts row → overview row with contract_status='terminated') from appearing in listOverviewPage, countOverviewByLifecyclePhase, and listOverviewKeys. The derive function does NOT filter by uc.status, so terminated contracts with a live SUC do get overview rows. The existing hTerminated fixture deliberately has no SUC, so the filter never engages in any test. If removed, all three surfaces would silently start including terminated chapters — a billing-critical regression.

utilityService empty-values path not explicitly asserted for this specific filter case

domains/utility/src/utility-contract-overview/__tests__/utility-contract-overview.integration.test.ts

The utilityService buildRule case has the positive-filter and unknown-code paths tested. The empty-values guard is in buildRule itself (returns matchesNothing: true for empty values), tested for isMonitored but not explicitly for utilityService. Low risk — the shared guard applies to all multiselect cases.

SQL exclusion list (to_jsonb - 'id' - 'updated_at') is a manual-sync pair across SQL and TS

packages/database/drizzle/0064_strong_echo.sql

The FULL OUTER JOIN diff expression (to_jsonb(d) - 'id' - 'updated_at') IS DISTINCT FROM (to_jsonb(s) - 'id' - 'updated_at') appears in both the SQL reconcile function (migration line ~106) and the TS reconcileOverview query. These two sites must stay in sync manually. The migration comment acknowledges this ('the SAME FULL OUTER JOIN diff as UtilityContractOverviewFCIS.reconcileOverview'). If a new auto-managed column is added to utility_contract_overview in the future, both sites need updating. Known manual-sync pair — worth noting in a follow-up.

findOverviewByContractPublicId status-blind asymmetry not called out at barrel export

domains/utility/src/utility-contract-overview/utility-contract-overview.queries.ts

listOverviewPage/listOverviewKeys/countOverviewByLifecyclePhase all exclude terminated contracts via buildUniverseWhere. findOverviewByContractPublicId is deliberately status-blind (correct — the detail drawer must show terminated chapters). This is well-commented inline, but the overviewQueries barrel object exports both without indicating the asymmetry. A future caller substituting a list call with this one would be surprised. A brief JSDoc mention ('status-blind by design') or a distinct naming convention would prevent misuse.

buildUniverseWhere base conditions are embedded inline — invisible to callers reading buildFullWhere

domains/utility/src/utility-contract-overview/utility-contract-overview.queries.ts

The ne(t.contractStatus,'terminated') condition is embedded directly in the initial array in buildUniverseWhere, making it invisible to readers of buildFullWhere which delegates to it. A hypothetical admin-probe query that bypasses buildUniverseWhere would silently lose the terminated-exclusion filter. Low risk today (reconcileOverview and runOverviewReconcile do their own raw SQL), but worth noting if a third query entrypoint is added.