claude/zealous-lovelace-cbc97f

runOverviewReconcile accepts DbOrTx but SQL function is unsafe inside a caller-supplied transaction

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

utility_contract_overview_reconcile() uses CREATE TEMP TABLE _uco_drift ON COMMIT DROP and accumulates pg_advisory_xact_lock locks via utility_contract_overview_refresh(). If a caller passes an in-progress transaction as db, the temp table survives until the outer transaction commits and the advisory locks are held for the outer transaction's lifetime — potentially deadlocking other writers. All current call sites pass a raw db handle so this is not triggered today, but the DbOrTx signature communicates that wrapping in a transaction is safe, which it is not. The parameter type should be narrowed to Database.

No explicit REVOKE on utility_contract_overview_reconcile_runs table

packages/database/drizzle/0057_chubby_starhawk.sql:15

The function ships REVOKE EXECUTE FROM authenticated, anon, public (line 187) but the audit table itself has only RLS + a service_role PERMISSIVE policy. Supabase auto-grants table-level SELECT to authenticated at creation, so RLS is the sole barrier against a future policy misconfiguration exposing cross-org (org_id, contract_id) pairs in the details JSONB. An explicit REVOKE SELECT, INSERT, UPDATE, DELETE ON utility_contract_overview_reconcile_runs FROM authenticated, anon would add the same defence-in-depth layer used for the SECURITY DEFINER functions.

CREATE TEMP TABLE uses unqualified name while DROP uses pg_temp-qualified name

packages/database/drizzle/0057_chubby_starhawk.sql:84

DROP TABLE IF EXISTS pg_temp._uco_drift (qualified) vs CREATE TEMP TABLE _uco_drift (unqualified). In plpgsql, CREATE TEMP TABLE always targets pg_temp regardless of search_path, so this is safe. All subsequent reads are schema-qualified. No exploitable path since the function is SECURITY DEFINER revoked from all client roles. Documenting for clarity.

Mixed import quote styles in new schema/index.ts exports

packages/database/src/schema/index.ts:47

The new exports (tariffTouSchedules, savingsConfigs) use single-quote paths while tariffJobs and the reconcile-runs export use double-quote paths. The existing utility block at lines 41-56 uses single quotes uniformly. Not introduced by this commit alone but the re-slot bundles these additions without normalising the style.

DESC index missing .nullsLast() chain in TypeScript schema

packages/database/src/schema/utility-contract-overview-reconcile.ts:59

ranAtIdx uses .on(table.ranAt.desc()) without .nullsLast(). The existing utility_contract_overview.ts index (line 173) chains .desc().nullsLast() explicitly. Drizzle emits NULLS LAST by default for DESC, so the migration SQL is correct, but the TypeScript source doesn't declare the intent explicitly — inconsistent with the established convention in this schema package.

c_repair_cap (2000) is hardcoded in SQL — not tunable without a migration

packages/database/drizzle/0057_chubby_starhawk.sql:64

The repair cap is a DECLARE constant inside plpgsql. If a large trigger-bypass disaster requires raising the cap for a one-off reconcile run, the only option is deploying a new migration. A GUC-backed default (current_setting('batu.reconcile_repair_cap', true)::integer with fallback to 2000) would allow a superuser to tune it at runtime without a deploy. Low priority given the cap is intentionally conservative and manual bulk reconcile is the documented human path.

Schema exports for 0055/0056 entities bundled into the 0057 re-slot commit

packages/database/src/schema/index.ts

Exports for tariffTouSchedules, savingsConfigs, savingsReports, tariffJobs, siteExceptionStates belong to migrations 0055/0056 merged from main. Bundling them into the re-slot commit conflates two concerns and makes git-bisect / blame less clean. For future re-slots, consider a separate commit for schema/index.ts export additions.