pgx-two-write-create-atomicity-tx-scoped-repo-variants-sentinel-validation

Answer

The repository referenced in the problem isn't present locally (/workspace only contained problem.json), so I built a self-contained, runnable reproduction of the pattern and verified it against a real PostgreSQL 18 cluster.

Deliverables

• /workspace/SOLUTION.md — the complete write-up (title, root-cause analysis, exact fix, verification).
• /workspace/solution/ — runnable Go module demonstrating and testing the fix.

/workspace/solution/
├── queryer.go        # shared {Exec; QueryRow} interface for pool *and* tx
├── errors.go         # ErrCreatorProfileNotFound sentinel
├── repos.go          # Create/CreateTx, Add/AddTx + compile-time assertions
├── service.go        # validate-first + Begin/committed/deferred Rollback
├── handler.go        # sentinel -> 404 PROFILE_NOT_FOUND
├── schema.sql
└── integration_test.go

Verification (real PG, not mocks)

=== RUN   TestCreateWorkspace_UnknownProfile_404AndNothingPersisted
--- PASS
=== RUN   TestCreateWorkspace_HappyPath
--- PASS
=== RUN   TestCreateWorkspace_ChildInsertFailureRollsBackParent
--- PASS
PASS
ok  example.com/pgxtwo  0.020s

The third test is the key one: it forces the child member insert to fail via a check constraint and asserts count(*) == 0 for the parent workspace, proving the transaction boundary genuinely rolls back rather than just relying on validate-before-write.

Root cause in one line

Two pool-backed writes ran sequentially with autocommit (workspaces.Create then members.Add), so a child FK failure left the parent committed, and the untyped error fell through the handler switch to 500. The fix validates the profile first (sentinel error), runs both writes through shared *Tx helpers on one SQL body inside a pool.Begin + deferred-rollback transaction, and maps the sentinel to 404 PROFILE_NOT_FOUND.