felt/.planning/phases/01-tournament-engine/01-02-SUMMARY.md

phase	plan	subsystem	tags	requires	provides	affects	tech-stack	key-files	key-decisions	patterns-established	requirements-completed	duration	completed
01-tournament-engine	02	database	libsql sqlite fts5 migrations schema go-embed	phase provides none n/a	Complete Phase 1 database schema (23 tables) Embedded migration runner with statement splitting for go-libsql FTS5 player search with automatic sync triggers Seed data (venue settings, chip sets) Dev seed (default admin operator) First-run setup detection	01-tournament-engine player-management tournament-runtime financial-engine	added patterns go-libsql sqlite-fts5 embedded-sql-migrations statement-splitting go-embed-migrations append-only-audit-triggers	created modified internal/store/migrations/001_initial_schema.sql internal/store/migrations/002_fts_indexes.sql internal/store/migrations/003_seed_data.sql internal/store/migrations/004_dev_seed.sql internal/store/migrate.go internal/store/db.go cmd/leaf/main.go Makefile	go-libsql requires single-statement Exec: migration runner splits SQL files into individual statements go-libsql PRAGMAs need special handling: journal_mode returns a row (use QueryRow), foreign_keys/busy_timeout use fallback execPragma helper Force single DB connection during migrations (SetMaxOpenConns(1)) to ensure table visibility across migration steps Dev seed (004) is gated by --dev flag, not applied in production mode Bcrypt hash for default admin PIN (1234) generated and embedded in dev seed SQL	Migration files: sequential numbered SQL in internal/store/migrations/, embedded via go:embed Statement splitting: splitStatements() handles triggers (BEGIN...END blocks), comments, and semicolons Financial columns: all INTEGER (int64 cents), zero REAL/FLOAT in schema Audit trail: append-only enforced by SQLite triggers (no UPDATE except undone_by, no DELETE)	ARCH-03 ARCH-08 PLYR-01 PLYR-07 SEAT-01 SEAT-02	10min	2026-03-01

Phase 1 Plan 02: Database Schema + Migrations Summary

Complete LibSQL schema (23 tables) with embedded migration runner, FTS5 player search, and seed data for tournament engine

Performance

• Duration: 10 min
• Started: 2026-03-01T02:27:39Z
• Completed: 2026-03-01T02:37:45Z
• Tasks: 2
• Files modified: 8

Accomplishments

• 23-table schema covering venue settings, building blocks (chips, blinds, payouts, buy-ins, points), tournament templates, runtime tournaments, players, tables/seating, financial transactions, audit trail, and operators
• Embedded migration runner that splits SQL into individual statements for go-libsql compatibility
• FTS5 virtual table on player names/nicknames/emails with automatic sync triggers
• Audit trail tamper protection via SQLite triggers (reject UPDATE except undone_by, reject DELETE)
• All financial columns use INTEGER (int64 cents) -- zero REAL/FLOAT in schema
• Seed data: DKK venue settings, Standard and Copenhagen chip sets with denominations
• Dev mode: gated admin operator seed (PIN 1234, bcrypt hashed)

Task Commits

Each task was committed atomically:

1. Task B1: Design and write the initial schema migration - 17dbfc6 (feat)
2. Task B2: Implement migration runner and FTS5 indexes - 0afa04a (feat)

Files Created/Modified

• internal/store/migrations/001_initial_schema.sql - Complete Phase 1 schema (23 tables, indexes, audit triggers)
• internal/store/migrations/002_fts_indexes.sql - FTS5 virtual table and sync triggers for player search
• internal/store/migrations/003_seed_data.sql - Default venue settings and built-in chip sets
• internal/store/migrations/004_dev_seed.sql - Dev-only default admin operator
• internal/store/migrate.go - Embedded migration runner with statement splitting
• internal/store/db.go - Database open/close with PRAGMA configuration and migration wiring
• cmd/leaf/main.go - Dev mode flag and database integration
• Makefile - Added run-dev target

Decisions Made

• go-libsql statement splitting: go-libsql does not support multi-statement Exec. The migration runner splits each SQL file into individual statements, handling trigger bodies (BEGIN...END blocks) as a single statement.
• PRAGMA handling: go-libsql is inconsistent about which PRAGMAs return rows. journal_mode=WAL returns the mode string; foreign_keys=ON and busy_timeout=5000 do not. A fallback execPragma helper tries Exec first, falling back to QueryRow.
• Single connection during migration: Forcing SetMaxOpenConns(1) during migration ensures all tables created in earlier migrations are visible to later ones (go-libsql connection pooling issue).
• Dev seed gating: The 004_dev_seed.sql migration is only applied when --dev flag is set, preventing default admin credentials in production.

Deviations from Plan

Auto-fixed Issues

1. [Rule 3 - Blocking] go-libsql multi-statement Exec does not work

• Found during: Task B2 (Migration runner implementation)
• Issue: go-libsql's tx.Exec with multi-statement SQL silently fails -- tables in 001_initial_schema.sql were not created despite the migration being recorded as applied
• Fix: Implemented splitStatements() function that splits SQL files into individual statements, handling trigger BEGIN...END blocks correctly. Removed transaction wrapping since go-libsql single-connection mode ensures atomicity.
• Files modified: internal/store/migrate.go
• Verification: All 4 migrations apply successfully, 23 tables created, second run skips all
• Committed in: 0afa04a

2. [Rule 1 - Bug] go-libsql PRAGMA handling inconsistency

• Found during: Task B2 (db.go PRAGMA setup)
• Issue: PRAGMA journal_mode=WAL returns a row (Exec fails), PRAGMA foreign_keys=ON does not return a row (QueryRow fails). Standard approach of using either Exec or QueryRow for all PRAGMAs doesn't work.
• Fix: Separate handling: QueryRow for journal_mode, execPragma helper (tries Exec, falls back to QueryRow) for others, then verify with getter queries.
• Files modified: internal/store/db.go
• Verification: All PRAGMAs set correctly (journal_mode=wal, foreign_keys=1, busy_timeout=5000)
• Committed in: 0afa04a

3. [Rule 3 - Blocking] Go and gcc not installed

• Found during: Task B2 pre-requisite check
• Issue: Go 1.24.1 and gcc were not installed on the system. go-libsql requires CGO (gcc).
• Fix: Installed Go 1.24.1 and gcc/libc6-dev via apt-get
• Files modified: System packages only (not committed)
• Verification: go version returns 1.24.1, CGO_ENABLED=1 works

---

Total deviations: 3 auto-fixed (1 bug, 2 blocking) Impact on plan: All auto-fixes necessary for go-libsql compatibility. The statement-splitting pattern is essential for the migration system to work at all. No scope creep.

Issues Encountered

• go-libsql connection pooling causes table visibility issues across migrations -- resolved by forcing single connection with SetMaxOpenConns(1)
• Plan A (project scaffold) was executed concurrently, committing some of Plan B's files (db.go, main.go, Makefile) in its scaffold commit. This did not cause issues but meant Task B2's commit only included the migrate.go fix.

User Setup Required

None - no external service configuration required.

Next Phase Readiness

• Database schema complete for all Phase 1 features
• Migration system ready for future schema changes (add new numbered SQL files)
• FTS5 player search operational for typeahead
• Audit trail tamper protection active
• Ready for Plan C (Auth/Operators) and Plan D (Player Management)

Self-Check: PASSED

• All 9 files verified present
• Both task commits (17dbfc6, 0afa04a) verified in git log

---

Phase: 01-tournament-engine Completed: 2026-03-01