fix/batch-dedup

inArray() for conflict target `where` may emit `= ANY(...)` not `IN (...)` — partial-index inference unverified

domains/utility/src/cfe-job/cfe-job.queries.ts:301

PostgreSQL partial-index inference for `ON CONFLICT ... WHERE` requires the predicate to match the index predicate verbatim (or semantically equivalent per predtest.c). Drizzle's `inArray(cfeJobs.status, ['queued', 'running'])` may emit `status = ANY(ARRAY['queued','running'])` instead of `status IN ('queued', 'running')`. PostgreSQL normalises both to `ScalarArrayOpExpr` so they should match, but this is implementation-dependent. The integration test (pinned-target test) provides runtime validation — if inference fails, the 'skips ONLY the row that lost the active-job race' test would also fail. Consider emitting `sql\`status IN ('queued', 'running')\`` (matching the index DDL verbatim) to remove any ambiguity.

PostHog rpu_count uses insertedJobs.length, not dispatch.length — minor ghost-row inconsistency

apps/platform/src/api/handlers/cfe-jobs.handler.ts:884

The comment says 'Jobs actually created' but uses `insertedJobs.length` (raw DB return). `dispatch.length` (after pairing) is the set that actually enters the SFN pipeline. In the ghost-row edge case (impossible in practice but typed as possible), `dispatch.length < insertedJobs.length`. Using `dispatch.length` would be semantically precise for 'jobs dispatched to the pipeline'.

dispatch.length > 0 gate is the correct guard for the SFN

apps/platform/src/api/handlers/cfe-jobs.handler.ts:822

dispatch = rows that are both (a) confirmed inserted by DB and (b) have a known input row for their config. If a ghost row existed in insertedJobs, dispatch would be empty even with insertedJobs.length > 0 — not starting the SFN is correct since no config is available for those rows.

Intent-loop state committed before TOCTOU conflict is pre-existing, not a regression

apps/platform/src/api/handlers/cfe-jobs.handler.ts:699

If the TOCTOU race fires between the intent commit and the bulk insert, the site+SUC+subscription are committed but no collect job is created. This is documented in the handler ('Pipeline failures cannot undo this') and pre-dates this PR. Not a regression.

SFN error sanitization correctly prevents AWS ARN / account-id leakage

apps/platform/src/api/handlers/cfe-jobs.handler.ts:870

The 503 response now returns a static 'Failed to start batch pipeline' message instead of the raw SDK error. AWS SDK errors for Step Functions routinely embed the execution ARN (account ID, region, state machine name) and occasionally IAM role ARNs. The fix is correct — full error stays in the server log, nothing sensitive reaches the client.

insertBatchSkippingActive uses fully parameterized Drizzle API — no SQL injection surface

domains/utility/src/cfe-job/cfe-job.queries.ts:293

`.values(rows)`, `.onConflictDoNothing({ target: [...] })`, and `inArray()` all emit parameterized SQL. No string interpolation or raw SQL fragments are present.

BatchInsertRow.config typed as Record<string,unknown> — should reference CfeJobConfig

apps/platform/src/api/utils/cfe-batch-dispatch.ts:16

The `config` field is `Record<string, unknown>`, forcing a `config: row.config as any` cast in the handler when building the `NewCfeJob` rows. The runtime shape is always `CfeJobConfig`. Importing `CfeJobConfig` from the utility domain (or from `@batu/database/schema`) would eliminate the cast and let TypeScript catch accidental key renames.

Handler JSDoc header says 'MUST NOT call queries directly' but createCfeJobBatchHandler does

apps/platform/src/api/handlers/cfe-jobs.handler.ts:12

The batch handler is the acknowledged 'large imperative shell' exception, but the file-level header doesn't carve this out. A future contributor would see a contradiction. Add a one-line exception note: 'Exception: createCfeJobBatchHandler is itself an imperative shell and calls jobQueries directly.'

Integration test updates RPU_A by rpu column, touching all RPU_A rows not just the one inserted

domains/utility/src/cfe-job/__tests__/cfe-job.integration.test.ts:239

The UPDATE at line 239 sets `status='completed' WHERE rpu=RPU_A` across the entire table for this org. Only one RPU_A row exists at that point in the current sequence, so the test passes, but this is fragile — a future test addition that inserts another RPU_A row before this test would flip it unexpectedly. Use `WHERE eq(cfeJobs.publicId, rows[0]!.publicId)` to pin the update to the specific row.

Only 'completed' status verified as non-blocking; 'failed', 'partial_success', 'cancelled' not tested

domains/utility/src/cfe-job/__tests__/cfe-job.integration.test.ts:237

The partial index predicate is `WHERE status IN ('queued', 'running')`. The test verifies only that a 'completed' job doesn't block a new insert. The schema defines at least three other non-blocking terminal statuses: 'failed', 'partial_success', 'cancelled'. Adding one more status (e.g. 'failed') to the test would give stronger evidence that the predicate boundary is correctly defined.

No unit test for single-row batch conflict

apps/platform/src/api/utils/__tests__/cfe-batch-dispatch.test.ts:19

The all-conflict test uses 2 rows. A single-item batch where that row conflicts (insertRows=[A], insertedJobs=[]) is not explicitly covered. This is the most common real-world case when a user retries a single RPU immediately after.

No handler-level test for dispatch=[] → SFN must not start (out-of-scope here)

apps/platform/src/api/handlers/cfe-jobs.handler.ts:822

The unit test proves pairBatchInsertResults returns dispatch=[] for all-conflict batches. No handler integration test confirms the SFN client is not invoked. Out-of-scope for this PR but worth a future follow-up.

pairBatchInsertResults iterates insertedJobs twice — can be single pass

apps/platform/src/api/utils/cfe-batch-dispatch.ts:40

The dispatch loop iterates insertedJobs once, then a separate `new Set(insertedJobs.map(...))` iterates again. After the dispatch loop, `insertedIds` can be built as `new Set(dispatch.map(d => d.job.publicId))` — eliminating the second scan.

BatchRecordError.error is unconstrained string — should be literal union

apps/platform/src/api/utils/cfe-batch-dispatch.ts:27

Only 'JobAlreadyExists' is ever emitted here; the handler pushes 'ServiceNameMissing' separately. A `'JobAlreadyExists' | 'ServiceNameMissing'` union would make the error catalog explicit and allow exhaustive switches on the error code later.

insertRows inline type in handler should use BatchInsertRow[] (already imported)

apps/platform/src/api/handlers/cfe-jobs.handler.ts:657

The local `insertRows` array is typed with an inline structural type identical to `BatchInsertRow`. Using `BatchInsertRow[]` would collapse two type definitions into one and make the data-flow to `pairBatchInsertResults` obvious at a glance.