fix/batch-dedup

TOCTOU-conflicted RPUs: intent committed, no job created — silent orphan

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

RPUs that pass the pre-check dedup but then lose the race at insert (TOCTOU conflict) are included in commitCfeJobIntentBatchShell — a site, SUC, or monitoring subscription is committed for them — but no CFE job is ever created. The JobAlreadyExists error is added AFTER the intent shell has already run. This leaves side effects committed with no corresponding collect job running. Pre-existing structural issue, but the new TOCTOU path makes it reachable at higher frequency.

onConflictDoNothing() without target swallows ALL unique-constraint conflicts

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

Without a `target` argument, onConflictDoNothing applies to ALL unique constraints on the table, including publicId uniqueness. A ULID collision (near-impossible) would be silently dropped and reported as JobAlreadyExists — an incorrect error. Pinning to the specific partial index via `target: [cfeJobs.rpu, cfeJobs.orgId, cfeJobs.pipeline]` constrains the guard to the intended constraint.

Analytics event tracks attempted RPUs, not inserted ones — inflated on partial-conflict batches

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

The PostHog cfe_job.created event tracks rpu_count: records.length (all submitted), not insertedJobs.length (actually created). On a batch where some RPUs hit the TOCTOU conflict guard, this over-counts jobs created. Pre-existing issue but now reachable at non-trivial frequency.

SFN error message echoed verbatim to API caller

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

When StartExecution fails, `sfnError.message` is included in the 503 response body. AWS SDK SFN errors can include ARNs, region, and account IDs. The error is already logged server-side; the response should return a generic 'Failed to start batch pipeline' message in all branches. Pre-existing behavior, but this refactor is the right moment to fix it.

Non-null assertion on insertRowByPublicId.get() — unhandled exception on invariant break

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

insertRowByPublicId.get(job.publicId)! will throw TypeError if returning() ever yields a publicId not in insertRows (e.g. via a DB trigger). Invariant holds today; a defensive check or explicit throw with a curated message would prevent leaking internal details via unhandled exception.

Inline drizzle .onConflictDoNothing() in handler violates ADR-016 §Pattern 4

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

ADR-016 §Pattern 4: drizzle query builder API belongs in domains/{domain}/src/{entity}/{entity}.queries.ts, never in handlers. The .onConflictDoNothing() clause is drizzle API appended inside the handler. The correct location is the domain query function exposed via the FCIS namespace. This extends pre-existing tech debt (the raw bulk insert was already in the handler).

New JobAlreadyExists error entries use plain object shape, not discriminated union

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

Project FCIS/ADR-016 convention requires discriminated union errors with `_tag` + `statusCode`. The new entries use `{ rpu, error: 'JobAlreadyExists', message }` — an ad-hoc shape with an `error` string field. Pre-existing pattern in this handler, but the PR extends it with a new error code path.

No test for conflict-skipped rows → JobAlreadyExists error population

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

The new branch that detects insertedJobs.length < insertRows.length, builds the insertedIds Set, and pushes JobAlreadyExists per skipped row has zero test coverage. No integration test stubs the DB insert to return fewer rows than submitted and asserts that (a) errors[] is populated with the correct rpu and 'JobAlreadyExists' tag (not the pre-check message), and (b) surviving inserted rows still get their SFN execution. This is the observable behaviour of the entire race-condition fix.

No test for SFN skip when all rows conflict (insertedJobs.length === 0)

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

The guard `if (insertedJobs.length > 0)` prevents StartExecution when every row was skipped. No test covers the all-conflict scenario to assert: handler returns 201 (not 503), response body contains errors[] for all RPUs, and StartExecution is never called.

No test for publicId-map lookup replacing index-based row access

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

Lines 830-835 replace the prior `insertRows[i]` index lookup with `insertRowByPublicId.get(job.publicId)`. No test asserts that when DB returns a subset of rows (e.g. rows 0 and 2 of 3, with row 1 skipped), the SFN jobs array contains exactly the correct config objects for the inserted rows. A wrong publicId lookup passes `undefined`, producing a runtime crash or garbled SFN input.

Staging manual test is the only verification gate — no CI regression guard

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

The PR test plan marks CI as unchecked and relies on a manual staging repro. The fix touches correctness-critical logic (TOCTOU dedup, error reporting, SFN input correctness) with no automated regression. A future refactor can silently regress the fix.

Integration test for uq_cfe_jobs_active_rpu_org does not cover bulk-insert path

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

Existing integration tests cover dedup via createJobShell (single-job path). The batch handler uses a raw db.insert().values([...]).onConflictDoNothing() bypassing the shell. No integration test covers the DB-level behaviour of onConflictDoNothing on the partial index for a multi-row batch insert.

Partial-conflict scenario (some inserted, some skipped) untested end-to-end

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

The most nuanced case — N submitted, M < N inserted, N-M skipped — exercises all three new code branches simultaneously (Set-based skip detection, publicId map lookup, conditional SFN guard). No test asserts created=M, errors.length=N-M, and SFN receives exactly M jobs.

insertRowByPublicId Map constructed outside the insertedJobs guard — wasted on all-conflict path

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

The Map is built unconditionally from all insertRows (line 830), but is only consumed inside `if (insertedJobs.length > 0)` (line 849). On the all-conflicted path the Map is allocated and immediately discarded. Moving it inside the guard block eliminates the waste and keeps the Map adjacent to its consumer.