feat/energia-led

updateEstimate: currentVersion accepted but never in WHERE — optimistic lock inoperative

domains/utility/src/finding/finding.queries.ts:173

`.where(eq(findings.id, id))` never checks version. A concurrent write cannot be detected — `updateEstimate` always returns a row, the null-return path is dead code, and the second writer silently overwrites the first. Fix: `.where(and(eq(findings.id, id), eq(findings.version, currentVersion)))`. Confirmed by correctness, conventions, tests, and improvement lenses (4/5).

updated counter increments before null-guard — conflict silently misreported as success

domains/utility/src/finding/finding.shells.ts:187

`updated += 1` at line 187 fires unconditionally; the `if (finding)` guard for the outbox event is at line 189. Once the WHERE version fix lands, a lost-update race would increment the counter but skip the outbox event — a conflict looks like a successful update to the caller. Move `updated += 1` inside `if (finding) { ... }`.

insert: non-null assertion row! panics on empty returning()

domains/utility/src/finding/finding.queries.ts:140

`const [row] = await db.insert(findings).values({...}).returning(); return toFinding(row!)`. If the driver returns zero rows, `row` is `undefined` and `row!` throws a runtime `TypeError`. The function signature is `Promise<Finding>` with no error path. Use a null check with a `FindingDatabaseError` or follow the `Result<Finding, FindingDatabaseError>` pattern.

Float equality comparison causes spurious update on every detector run for high-precision amounts

domains/utility/src/finding/finding.decisions.ts:125

`existing.estimatedMxnAnnual` is `string | null` (Drizzle numeric). `Number(string)` loses precision past ~15 significant digits. If a stored numeric value loses precision on the round-trip, `Number(storedString) !== commandNumber` is always true — producing a permanent false-positive `'update'` action and outbox event on every detector run. Mitigate by comparing as strings throughout or rounding both sides to a fixed decimal scale before comparison.

Silent discard on optimistic-lock conflict — no log, no caller signal in DetectFindingsResult

domains/utility/src/finding/finding.shells.ts:200

Once the WHERE version fix lands, a null return from updateEstimate is silently dropped. No warning log, no `conflicts` field in `DetectFindingsResult`. Consider adding a `conflicts: number` field.

findManyByDedupeKeys Map silently drops duplicates if UNIQUE constraint is relaxed

domains/utility/src/finding/finding.queries.ts:53

Not a present-day bug (UNIQUE constraint enforced). Noting for future-safety: `new Map(rows.map(...))` silently keeps last entry for duplicate key if constraint is relaxed.

updateStatus/updateEstimate filter only on id — latent IDOR for PR3 handler

domains/utility/src/finding/finding.queries.ts:188

No `orgId` in the WHERE clause. When PR3 ships a status-transition handler, a caller who learns any internal UUID can modify another org's finding if the handler author forgets the membership check. Add `AND org_id = $orgId` to both update functions before PR3 — the null return already signals not-found-or-conflict, so the addition is safe.

listByOrg accepts uncapped caller-supplied limit

domains/utility/src/finding/finding.queries.ts:80

No maximum cap on `filters.limit`. A PR3 handler passing `limit=1000000` would execute a full table scan. Add `Math.min(filters.limit ?? 50, 500)` inside `listByOrg` or enforce in the handler validator before the endpoint is exposed.

findByPublicId and findByDedupeKey are unscoped global lookups

domains/utility/src/finding/finding.queries.ts:33

Safe now (service-role callers only), but a future handler using `findByPublicId` with a user-supplied publicId will return findings from any org if the handler author forgets the membership check. Add optional `orgId` parameter to both functions.

No DB-level constraints on closed-vocabulary columns or year_month format

packages/database/drizzle/0058_lean_giant_girl.sql:14

`text({ enum })` is TypeScript-only. A pipeline bug or compromised Lambda can write arbitrary values. Add CHECK constraints for `category`, `severity`, `status` (closed vocab) and `year_month ~ '^\d{4}-\d{2}$'`.

details JSONB has no size guard at persistence

domains/utility/src/finding/finding.queries.ts:116

A future detector accidentally serialising a large graph would bloat the table without error. Add a runtime size guard in `insert()`. Values sourced from SQL LATERAL extraction of bill line items should be treated as untrusted text in PR3 renderers.

Shell orgId must be validated at handler/trigger layer — track for Lambda PR

domains/utility/src/finding/finding.shells.ts:82

The `::uuid` cast rejects non-UUIDs at DB level. A future Lambda trigger must also validate the orgId belongs to a real org the actor can act on. Track as required pre-condition for the trigger PR.

RLS SELECT policy requires createRLSDb transactions — by design

packages/database/src/schema/findings.ts:96

Confirmed intentional design. Future authenticated mutation policies (for PR3+ status transitions) must be narrowed to specific column sets (status, not estimated_mxn_annual or details). The PR description correctly defers this to PR3.

updateStatus: docstring promises null-on-conflict but WHERE clause never checks version

domains/utility/src/finding/finding.queries.ts:188

JSDoc: 'Returns null if no row was updated (e.g. concurrent modification raced past the version the shell fetched).' The WHERE is `eq(findings.id, id)` only — version check absent. PR3's status-transition shell depends on this null-return signal for 409 responses. Fix before PR3: add `eq(findings.version, currentVersion)` to the AND clause. Canonical pattern: `context.queries.ts:84`, `site.queries.ts:265`.

No 23505 unique-constraint handler in detectFindingsForOrgShell

domains/utility/src/finding/finding.shells.ts:148

domain-patterns.md §Shells: 'Handle unique constraint 23505 as a race condition.' Two concurrent detector runs both deciding 'insert' for the same dedupeKey would cause the second `findingQueries.insert` to throw a Postgres 23505 inside the transaction, propagating as an unhandled rejection. Fix: wrap the insert or use `onConflictDoNothing`.

OrgModuleKey and OrgModuleKeySchema widened in lockstep — correct

domains/core/src/organization/organization.type.ts:13

ORG_MODULES, OrgModuleKeySchema, GREYED set, and GATED_PATH_MODULES all include 'energia'. Covariance invariant satisfied.

rpu not a column in findings table — by design, documented

packages/database/src/schema/findings.ts:76

Intentional. The dedupeKey encodes rpu; the shell restores it from FindingCandidate for outbox events. Explicitly documented in the schema comment.

Outbox event delivery not verified by any integration test

domains/utility/src/finding/__tests__/finding.integration.test.ts:270

The shell emits `utility.finding.detected` on every insert and update. The integration test exercises the shell end-to-end (idempotent double-run) but never queries `outbox_events` to confirm atomic delivery. A regression where `outboxQueries.insert` is accidentally removed would not be caught. The outbox delivery guarantee is the non-negotiable FCIS invariant.

Spec verification point #3 absent from integration tests

domains/utility/src/finding/__tests__/finding.integration.test.ts:14

Test file header references spec checklist items 1, 2, and 4, skipping 3. If item 3 was 'listByOrg status filter returns correct subset,' it has no test coverage.

fetchBillFactRowsForOrg legado SQL flag untested at integration level

domains/utility/src/finding/__tests__/finding.integration.test.ts:138

Fixtures always use non-legado (50x prefix) contracts. The SQL computes `is_legado` as `uc.account_number LIKE '84%'`. A typo in the LIKE pattern would be invisible to the current test suite.

listByOrg status filter and pagination not covered by integration tests

domains/utility/src/finding/finding.queries.ts:76

Integration tests call `listByOrg` only without filters and under RLS. A test passing `{ status: 'verified' }` confirming subset filtering, or pagination boundaries, does not exist.

isValidFindingStatusTransition self-transition untested end-to-end through decideTransitionFindingStatus

domains/utility/src/finding/__tests__/finding.decisions.test.ts:312

The self-transition guard (from === to → true) is trivially correct and tested at the function level. No end-to-end test through the full decision flow.

listByOrg filters missing category and severity — will need adding for PR3 UI triage axes

domains/utility/src/finding/finding.queries.ts:58

`ListFindingsFilters` exposes only `status`. The energía UI's primary triage axes are `category` (cfe_error vs operational) and `severity` (warning vs critical). Adding these post-PR3 requires a follow-up migration of any callers. Consider adding `category?: FindingCategory` and `severity?: FindingSeverity` now.

BART path org-scoping gap not signposted in shell result

domains/utility/src/finding/finding.queries.ts:248

The query uses sites → SUC → contracts (subscriber path only). Orgs with contracts only via entity_relationships get `inserted:0` with no explanation. Consider logging a `contractsScanned` count so callers can distinguish 'no bills yet' from 'bills exist but were not scoped'.

resolveDetectorVersion fallback silently swallows unregistered detector types

domains/utility/src/finding/finding.shells.ts:45

Fallback to `${findingType}@1` for unknown types means a new detector ships without a registry entry, accumulating findings under an untracked version string — a future version bump misses them. Consider throwing for unrecognized findingType instead.