SQLite database layer for session persistence.

Tracks game sessions:

• id (TEXT PRIMARY KEY): Unique session identifier
• created_at (INTEGER): Unix timestamp
• current_turn (INTEGER): Index of current player
• max_players (INTEGER): Max active players (default 10)
• status (TEXT): waiting | active | completed

Tracks players in each session:

• id (INTEGER PRIMARY KEY AUTOINCREMENT)
• session_id (TEXT): Foreign key to sessions
• name (TEXT): Player display name
• avatar (TEXT): Emoji avatar
• is_active (INTEGER): 1 for active player, 0 for spectator
• turn_order (INTEGER): Turn sequence number
• last_seen (INTEGER): Heartbeat timestamp

Stores played cards:

• id (INTEGER PRIMARY KEY AUTOINCREMENT)
• session_id (TEXT): Foreign key to sessions
• player_name (TEXT): Who played the card
• card_type (TEXT): role | tone | token-limit | custom | image | remove
• content (TEXT): Card instruction/content
• image_url (TEXT): Optional blob storage URL
• timestamp (INTEGER): When card was played

Stores AI-generated responses:

• id (INTEGER PRIMARY KEY AUTOINCREMENT)
• session_id (TEXT): Foreign key to sessions
• prompt (TEXT): Full system prompt
• response (TEXT): AI response
• token_count (INTEGER): Tokens used
• timestamp (INTEGER): When generated

All database operations are in queries.ts:

• createSession(id): Create new session
• getSession(id): Fetch session by ID
• updateSessionTurn(id, turn): Advance turn
• updateSessionStatus(id, status): Change status

• addPlayer(sessionId, name, avatar, turnOrder): Add player
• getPlayers(sessionId): Get all players
• getActivePlayers(sessionId): Get active players only
• updatePlayerHeartbeat(sessionId, name): Update last_seen

• playCard(sessionId, playerName, type, content, imageUrl?): Add card to stack
• getPromptStack(sessionId): Get all played cards
• removeLastCard(sessionId): Undo last card
• removeCardsByType(sessionId, type): Clear specific card type

• saveVibeResult(sessionId, prompt, response, tokens): Save AI output
• getLatestVibeResult(sessionId): Get most recent result

Tables are versioned (e.g., sessions_v1). To change schema:

1. Update VERSION constant in schema.ts
2. Old data remains in old tables
3. New tables created automatically
4. No migration needed (Val Town pattern)

import { initDatabase } from "./database/schema.ts";
import * as db from "./database/queries.ts";

// Initialize tables
await initDatabase();

// Create session
const session = await db.createSession("my-game-123");

// Add player
const player = await db.addPlayer("my-game-123", "Alice", "🎨", 0);

// Play card
await db.playCard("my-game-123", "Alice", "role", "Act as a Rust expert");

// Get stack
const stack = await db.getPromptStack("my-game-123");