ai_ops2/.app_factory/claude_home/.claude/plans/precious-bouncing-hopper.md

Replace Postgres Sync Bridge with Native Async pg.Pool

Context

The app currently uses a "sync bridge" (dbPostgresSyncBridge.js) that wraps async Postgres queries in a worker thread and blocks the main thread with Atomics.wait polling at 10ms intervals. Every query pays: serialization + MessageChannel IPC + worker thread round-trip + 10ms polling tax. This makes Postgres mode ~10-100x slower than SQLite and fragile (SQLite SQL translated on the fly).

The fix: use pg.Pool natively with async/await. The async pool, query helpers, and full Postgres schema already exist in dbPostgres.js. The work is making the call chain async.

Design

Async DB Interface

Both SQLite and Postgres return the same async interface:

{
  engine: 'postgres' | 'sqlite',
  prepare(sql) → { async get(...p), async all(...p), async run(...p) },
  async exec(sql),
  async close(),
}

• prepare() is synchronous (captures SQL, returns statement object)
• get/all/run/exec are async (return Promises)
• For SQLite: the async methods resolve synchronously (zero overhead)

Transaction Isolation via AsyncLocalStorage

Problem: With pg.Pool, concurrent requests get different connections — a BEGIN on one connection doesn't affect queries on another.

Solution: Module-scoped AsyncLocalStorage in dbPostgres.js. When tx() starts a transaction, it checks out a client from the pool, pins it in AsyncLocalStorage, and all db.prepare() calls within that async context automatically route to the pinned client. The callback uses the same db variable from the closure — no parameter changes needed.

// Postgres tx
const client = await db.pool.connect();
await client.query('BEGIN');
const result = await txStorage.run({ client }, fn);  // fn's db calls see pinned client
await client.query('COMMIT');
client.release();

Nested tx() calls detect the existing store and run inline (no double-BEGIN).

openDatabase() becomes async

• Postgres: createPostgresPool() → initializePostgresSchema(pool) → createAsyncPostgresDb(pool) → return
• SQLite: new DatabaseSync() → sync initializeSchema() → wrapSyncDbAsAsync() → return

Schema init stays engine-specific: sync for SQLite (unchanged), async for Postgres (already exists).

Return value contracts (unchanged)

• .get() → row or undefined
• .all() → array (empty if no results)
• .run() → { changes: number, lastInsertRowid: null }

Implementation Steps

Step 1: src/dbPostgres.js — Add async db adapter

• Add AsyncLocalStorage import and module-scoped txStorage
• Add createAsyncPostgresDb(pool) — returns async db object with getClient() routing via txStorage
• Add export async function asyncTx(db, fn) — acquires client, pins in ALS, runs fn, commits/rollbacks
• Keep all existing exports (pool creation, schema init, param conversion)

Step 2: src/db.js — Add SQLite async wrapper + async openDatabase

• Add wrapSyncDbAsAsync(syncDb) — wraps sync prepare/exec/close in async functions
• Make openDatabase() async:
  • Postgres path: pool + async schema init + createAsyncPostgresDb(pool)
  • SQLite path: sync init + wrapSyncDbAsAsync(syncDb)
• Remove openPostgresSyncBridge import
• Keep sync initializeSchema() (only used for SQLite, called on raw DatabaseSync)

Step 3: src/store/coreStore.js — Convert store layer to async

The bulk of the work (~200 lines touched, all mechanical):

• Make tx() async and driver-aware (calls asyncTx for Postgres, inline BEGIN/COMMIT for SQLite)
• 82 exported functions → async function
• ~15 internal helpers → async function
• 85 db.prepare().get/all/run() calls → await
• 12 tx(db, () => ...) calls → await tx(db, async () => ...)
• Store-to-store calls (e.g. listRoster inside getGroupSummary) → await

Step 4: src/itemCatalog.js — Convert to async

• 7 functions become async
• 9 db calls get await
• Manual BEGIN/COMMIT in refreshItemCatalog and rollbackItemCatalogToSnapshot → use tx()

Step 5: All 7 route files — Make handlers async

80 route handlers across:

• src/routes/syncRoutes.js (14)
• src/routes/groupRoutes.js (27)
• src/routes/authRoutes.js (13)
• src/routes/webRoutes.js (10)
• src/routes/billingRoutes.js (7 + 2 direct db calls)
• src/routes/opsRoutes.js (6)
• src/routes/internalRoutes.js (3)

Each: (req, res) => → async (req, res) =>, add await before store calls. Express 5 handles async errors natively.

Step 6: src/server.js — Async startup + local functions

• const db = await openDatabase() (top-level await in ESM)
• ~12 local functions that call db become async: probeDatabaseHealth, wipeRuntimeDataForDev, findGroupByJoinCode, lookupGroupAccessByUser, readAuthenticatedWebUser, requireAuthenticatedWebUser, requireGroupMembership, getRequestPremiumState, getRequestFeatureState, isApiFeatureEnabled, requireApiFeatureFlag, publishDashboardEventForUserGroups, etc.
• await seedDemoData(db) at startup
• Add pool shutdown on SIGINT/SIGTERM

Step 7: src/activityWebhookDelivery.js

• Already async, just add await to 2 store function calls

Step 8: Delete sync bridge

• Delete src/dbPostgresSyncBridge.js
• Delete src/dbPostgresSyncWorker.js
• Update resolveDatabaseRuntimeConfig() to remove sync_bridge compatibility reference

Step 9: Update tests

• 32 test files: test callbacks become async, db created via wrapSyncDbAsAsync(), store calls get await

Files Modified

File	Change
src/dbPostgres.js	Add createAsyncPostgresDb, asyncTx, txStorage (~60 new lines)
src/db.js	Add wrapSyncDbAsAsync; async openDatabase; remove sync bridge import
src/store/coreStore.js	All 82 exports + ~15 helpers async; await 85 prepare + 12 tx calls
src/itemCatalog.js	7 functions async; await 9 db calls; replace manual tx
src/routes/*.js (7 files)	80 handlers async; await store calls
src/server.js	Async startup; ~12 local functions async; pool shutdown
src/activityWebhookDelivery.js	Await 2 store calls

Files Deleted

• src/dbPostgresSyncBridge.js
• src/dbPostgresSyncWorker.js

Key Risks

1. SQLite transaction interleaving: Won't happen — SQLite async wrapper resolves synchronously (no real yielding between BEGIN/COMMIT). Rule: no network I/O inside tx() callbacks.
2. Pool exhaustion: asyncTx uses try/finally to guarantee client.release(). Pool timeout (10s default) prevents silent hangs.
3. Return value .get() undefined vs null: New adapter returns undefined (matching SQLite behavior). All call sites use if (!row) — works for both.
4. Cascading async in server.js: getRequestPremiumState → getRequestFeatureState → isApiFeatureEnabled etc. chain needs async. All call sites are in route handlers (already being made async).

Verification

1. npm test — all 32 test files pass with async wrapper
2. docker compose down -v && docker compose up --build -d — app starts cleanly
3. Full onboarding flow: mock OAuth → create group → plugin sync → vault shows items
4. Check Docker logs: no Atomics, no worker thread, no 10ms polling
5. Compare response times: time curl http://localhost:3000/api/v1/groups/:id/vault (before/after)
6. Verify SQLite mode still works: DB_DRIVER=sqlite npm start