Review: V2 Schema — Pool, Proposal, Peer-to-Peer Models

review-845-2026-04-05 Doc

review needs-refinement

Project

Template Completeness

[x] Type -- Feature
[x] Lineage -- "Foundation for v2. All other v2 tickets depend on this. Part of #66."
[x] Repo -- forgejo_admin/twitch-2k-wager
[x] User Story -- present, well-formed (As a player...)
[x] Context -- thorough, includes design decisions with dates
[x] File Targets -- 3 files with detailed change descriptions
[x] Acceptance Criteria -- 8 items
[x] Test Expectations -- 5 items
[x] Constraints -- 4 constraints, well-scoped
[x] Checklist -- present
[x] Related -- references parent #66, project page, downstream tickets #68-#73

Traceability

[x] story:player-pool label -- "Any Player: Deposit once, bet instantly from pool. No Stripe redirect per game."
[x] story note verified -- found in project-twitch-2k-wager user-stories table (key: player-pool)
[x] arch:domain-twitch-2k-wager label -- present on board item
[ ] arch note MISSING -- [SCOPE] No arch-domain-twitch-2k-wager note exists in pal-e-docs. The project page has an Architecture section with the data model, but no standalone arch-domain-* note backs this label. arch-dataflow-twitch-2k-wager exists (different component). Create architecture note arch-domain-twitch-2k-wager for the domain model component.
[x] Forgejo issue -- https://forgejo.tail5b443a.ts.net/forgejo_admin/twitch-2k-wager/issues/67, open

File Targets

[x] src/lib/schema.sql -- verified: exists (70 lines), contains current challenger/game/payment/payout/revenue_split tables. Ticket correctly identifies tables to modify/create (player_pools, proposals, game changes).
[x] src/lib/types.ts -- verified: exists (93 lines), contains Challenger, Game, BuyInAmount, GameWithChallenger, Payment, Payout, RevenueSplit types. Ticket correctly identifies types to add (PlayerPool, Proposal) and modify (Game).
[x] src/lib/db.ts -- verified: exists (306 lines), lazy pg.Pool, query helpers, CRUD for Challenger/Game/Payment/Payout/RevenueSplit. Ticket correctly identifies new functions needed (pool, proposal, updated game). Currently has no explicit transaction handling -- ticket's transaction requirement (BEGIN/COMMIT + SELECT...FOR UPDATE) is a net-new pattern for this codebase.

Repo Placement

OK. Issue filed on forgejo_admin/twitch-2k-wager. All 3 file targets are in that repo. Single repo change.

Dependencies

[x] This is the foundation ticket -- all v2 tickets (#68 deposit, #69 proposals, #70 dashboard, #71 result, #72 withdrawal, #73 cleanup) depend on this. No upstream blockers.
[x] Parent issue #66 (v2 vision) referenced in Lineage.
[x] Board item #795 (twitch-2k-wager#9 "Postgres schema + db.ts") is in done -- the v1 schema this builds on.
[x] No items in in_progress that would conflict. All 8 v2 items are in backlog.

Acceptance Criteria

[x] AC 1-3 (table creation/modification) -- verifiable via schema inspection
[x] AC 4 (migration safe for existing prod data) -- verifiable but requires prod-like data seed. Ticket mentions "26 games, multiple challengers" -- agent would need this data or a fixture.
[ ] AC 5 (Marcus seeded) -- ambiguous. Ticket says "from his Keycloak twitch_id" but does not provide the concrete twitch_id value or a reference to where it lives (Keycloak admin API, secrets file, or hardcoded value). Agent cannot seed deterministically without this.
[x] AC 6 (existing guests get pool records) -- verifiable via migration query
[x] AC 7 (pool mutations atomic) -- verifiable via test expectations (concurrent proposals test)
[x] AC 8 (backwards compatible) -- verifiable but broad. 22 files import from db/types. Agent needs to verify all existing callers still compile and pass.

Concern: AC 5 (Marcus seed) lacks the concrete twitch_id value. Agent will need to query Keycloak or a secrets file to get it.

Blast Radius

HIGH. 22 files across the codebase import from db.ts or types.ts. Key consumers:

src/lib/stripe.ts -- references challenger_id in Stripe metadata
src/routes/api/checkout/+server.ts -- uses BuyInAmount type, creates games with fixed tiers
src/routes/api/webhooks/stripe/+server.ts -- references challenger_id in webhook metadata
src/routes/api/admin/result/+server.ts -- uses getChallengerById, game.challenger_id
src/routes/admin/+page.server.ts -- uses GameWithChallenger type, buy_in_amount
6 test files reference challenger_id, buy_in_amount, BuyInAmount

The ticket's "Keep existing functions working" constraint mitigates this, but the agent must not break existing function signatures or types. The removal of BuyInAmount tier CHECK (mentioned in File Targets) must be additive only -- existing callers pass BuyInAmount values that must remain valid. Rollback: reversible if migration is written with DROP IF EXISTS guards, but 22 downstream files make partial rollback risky.

Decomposition Assessment

NEEDS DECOMPOSITION.

3 file targets in 1 repo -- passes file/repo threshold
8 acceptance criteria + 5 test expectations = 13 total verification points -- exceeds the 5-AC ceiling
Estimated agent work: 15-20 minutes. New tables, migration logic, ~10 new db functions, transaction patterns (new to codebase), type additions, backwards compatibility verification across 22 consumers.
Independent subtasks identified: (1) schema.sql migration + new tables, (2) types.ts updates, (3) db.ts pool functions with transactions, (4) db.ts proposal + game functions, (5) migration test + backwards compatibility verification

[DECOMPOSE] 13 criteria across 3 files with new transaction patterns. Route to skill-decompose-ticket.

Recommendation

[SCOPE] Create architecture note arch-domain-twitch-2k-wager for the domain model component (tables, types, db functions).
[BODY] Add Marcus's twitch_id value (or a concrete reference: Keycloak admin API endpoint, secrets path, or env var) to AC 5 so the migration agent can seed deterministically.
[DECOMPOSE] 13 criteria across 3 files with new transaction patterns. Route to skill-decompose-ticket for sub-board creation.