D1 Database

The initial migration creates a multi-table SQLite/D1 schema:

• players
• games
• game_players
• player_game_facts
• round_summaries
• player_mode_summary
• player_mode_daily
• global_daily_stats
• ranking_snapshots
• source_indexes
• ingest_jobs
• parse_errors
• coverage_days

The schema uses unique keys for idempotent loading:

• players.normalized_name
• games.tenhou_log_id
• game_players(game_id, seat)
• game_players(game_id, player_id)
• player_game_facts(player_id, game_id)
• round_summaries(game_id, round_index)
• source_indexes(source_name, file_name)
• ingest_jobs(job_type, job_key)

Run the migration locally with:

sqlite3 :memory: ".read libs/d1-schema/migrations/0001_initial.sql" "PRAGMA table_list;"

For the Wrangler local D1 used by pnpm api:dev:

pnpm api:migrate:local
pnpm api:seed:local

The seed inserts one small game and precomputed summaries so local API routes can be tested without fetching Tenhou data or running runtime aggregation.