feat(api): switch createDb to WebSocket Pool driver to enable applyTransition transactions

[cdcore09]

Summary

packages/api/src/lib/lifecycle/applyTransition.ts documents itself as "sequential, not transactional" because @neondatabase/serverless HTTP driver (via drizzle-orm/neon-http) does not support db.transaction(). A crash between insertReview and updateArtifactStatus could leave a stranded review row that bumps the count toward a future publish without flipping the artifact.

Requirements

• Evaluate switching createDb (packages/api/src/db/index.ts) from neon-http to neon-serverless (WebSocket Pool driver)
• If switching, measure cold/warm latency impact (current is ~360ms cold / <200ms warm on neon-http)
• Wrap applyTransition body in db.transaction(async tx => { ... }) once Pool driver is in place
• Update the comment in applyTransition.ts to remove the TODO
• Re-run the full integration test suite (216+ tests) against staging to confirm no regressions

Context

Flagged in the Plan 1 final review. At v1 admin write volumes the non-atomicity is acceptable; this issue gets more urgent as concurrent write volumes grow.

Implementation Notes

The Cloudflare Workers + neon-serverless integration requires nodejs_compat flag in wrangler.jsonc — verify before committing to the swap. Some HTTP-only Neon features may not be available via WebSocket.