Validation: Postgres schema + db.ts
Verdict: PASS
Ticket
Issue #9 — Postgres schema, db.ts typed query layer, and types.ts. Board item #795.
Environment
Code review of repo at
~/twitch-2k-wager/src/lib/. Runtime DB validation blocked (no DATABASE_URL in dev overlay) — code-level validation only.Checks
| # | Criterion | How Verified | Result | Evidence |
|---|---|---|---|---|
| 1 | schema.sql exists with all tables | File read | PASS | 5 tables: challenger, game, payment, payout, revenue_split. All with proper constraints, CHECK clauses, and foreign keys. |
| 2 | db.ts exists with typed query helpers | File read | PASS | 232 lines. Generic query<T> and queryOne<T> helpers. CRUD functions for all 5 entities: getChallenger, upsertChallenger, updateConnectStatus, createGame, getGame, updateGameStatus, listGamesByChallenger, listQueuedGames, createPayment, updatePaymentStatus, createPayout, updatePayoutStatus, recordRevenueSplit, closePool. |
| 3 | types.ts exists with TypeScript interfaces | File read | PASS | 77 lines. Union types: ConnectOnboardingStatus, GameStatus, PaymentStatus, PayoutStatus, BuyInAmount. Interfaces: Challenger, Game, Payment, Payout, RevenueSplit. All monetary amounts documented as cents. |
| 4 | Schema/types alignment | Manual comparison | PASS | All SQL columns map 1:1 to TypeScript interface fields. CHECK constraints match union types (e.g., game.status IN matches GameStatus union). buy_in_amount CHECK (1500,3000,4500) matches BuyInAmount type. |
| 5 | db.ts imports types correctly | File read | PASS | Imports Challenger, Game, Payment, Payout, RevenueSplit from ./types.js |
| 6 | Pool configuration | File read | PASS | Uses pg.Pool with DATABASE_URL env var. Fail-fast if missing. closePool() for graceful shutdown. |
| 7 | Test file exists | Filesystem check | PASS | src/lib/__tests__/db.test.ts present |
Regression Check
No prior DB layer existed — this is the initial implementation. Schema is self-consistent with proper foreign key references (game references challenger, payment/payout/revenue_split reference game).
Discovered Issues
None. Runtime DB connection cannot be validated until DATABASE_URL is configured in the dev overlay — this is an infra dependency, not a code issue.