D1 Schema Additions

These two tables are added on top of the base Aelghar D1 schema. Apply as a separate migration after the base schema migrations.

Migration file: migrations/0200_minigame.sql

---

Table: minigame_session

Stores server-issued minigame challenge tokens. One row per issued session. Row is marked used = 1 after a valid result submission. Expired unused rows pruned daily.

CREATE TABLE IF NOT EXISTS minigame_session (
    jti             TEXT PRIMARY KEY,               -- crypto.randomUUID() — matches token jti
    character_id    INTEGER NOT NULL
                    REFERENCES character(id) ON DELETE CASCADE,
    activity_type   TEXT NOT NULL,                  -- expedition_accept | craft_* | gather_* | ...
    activity_ref_id TEXT NOT NULL,                  -- board item id, template id, etc.
    minigame_type   TEXT NOT NULL,                  -- rhythm | whack | sequence | fishing | ...
    seed            INTEGER NOT NULL,               -- 32-bit signed, seeded content generation
    skill_band      INTEGER NOT NULL DEFAULT 0,     -- 0–6, character's band at issue time
    issued_at       INTEGER NOT NULL,               -- Unix epoch seconds
    min_duration_ms INTEGER NOT NULL,               -- minimum valid play time (ms)
    max_duration_ms INTEGER NOT NULL,               -- maximum valid play time (ms)
    expires_at      INTEGER NOT NULL,               -- Unix epoch seconds (issued_at + 300)
    used            INTEGER NOT NULL DEFAULT 0      -- 0 = pending, 1 = consumed
);

CREATE INDEX IF NOT EXISTS idx_minigame_session_character
    ON minigame_session(character_id);

CREATE INDEX IF NOT EXISTS idx_minigame_session_expires
    ON minigame_session(expires_at)
    WHERE used = 0;                                 -- partial index: only unused sessions need pruning

Notes

• jti is the primary key and is included in the signed token payload. The Worker looks up by jti on submission.
• seed is stored to allow server-side event hash derivation verification.
• skill_band is stored at issue time — not re-fetched at submission. Prevents skill changing mid-session edge cases.
• expires_at = issued_at + 300 (5 minutes). Sessions that expire while the player is mid-game will fail on submission; the client must fetch a new session.

---

Table: minigame_result

Stores the validated result of each consumed minigame session. One row per consumed minigame_session. Written in the same D1 transaction that marks the session used.

CREATE TABLE IF NOT EXISTS minigame_result (
    jti              TEXT PRIMARY KEY
                     REFERENCES minigame_session(jti),
    character_id     INTEGER NOT NULL
                     REFERENCES character(id) ON DELETE CASCADE,
    score            REAL NOT NULL,                 -- 0.0–1.0, server-clamped
    duration_ms      INTEGER NOT NULL,              -- actual play duration
    event_hash       TEXT NOT NULL,                 -- SHA-256 of seed + event indices
    modifier_applied REAL NOT NULL,                 -- 0.7 + score * 0.7, clamped
    submitted_at     INTEGER NOT NULL               -- Unix epoch seconds
);

CREATE INDEX IF NOT EXISTS idx_minigame_result_character
    ON minigame_result(character_id, submitted_at DESC);

Notes

• jti is the PK and FK to minigame_session. Inserting a duplicate jti fails with a unique constraint — this is the idempotency guard (see architecture doc).
• modifier_applied is stored after calculation so the settlement pass can read it directly without recalculating.
• score and modifier_applied are both stored for audit and analytics purposes.

---

Table: character_flag (addition to base schema)

The base schema does not include a character flags table. Add one to support Passive Mode and future per-character toggles.

CREATE TABLE IF NOT EXISTS character_flag (
    character_id  INTEGER NOT NULL
                  REFERENCES character(id) ON DELETE CASCADE,
    flag_name     TEXT NOT NULL,
    flag_value    INTEGER NOT NULL DEFAULT 1,       -- 1 = true, 0 = false
    set_at        INTEGER NOT NULL,
    PRIMARY KEY (character_id, flag_name)
);

Used flags:

Flag name	Meaning
passive_mode	Skip minigames, use modifier 1.0

---

Combined Migration File

-- migrations/0200_minigame.sql

CREATE TABLE IF NOT EXISTS minigame_session (
    jti             TEXT PRIMARY KEY,
    character_id    INTEGER NOT NULL REFERENCES character(id) ON DELETE CASCADE,
    activity_type   TEXT NOT NULL,
    activity_ref_id TEXT NOT NULL,
    minigame_type   TEXT NOT NULL,
    seed            INTEGER NOT NULL,
    skill_band      INTEGER NOT NULL DEFAULT 0,
    issued_at       INTEGER NOT NULL,
    min_duration_ms INTEGER NOT NULL,
    max_duration_ms INTEGER NOT NULL,
    expires_at      INTEGER NOT NULL,
    used            INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_minigame_session_character
    ON minigame_session(character_id);

CREATE INDEX IF NOT EXISTS idx_minigame_session_expires
    ON minigame_session(expires_at) WHERE used = 0;

CREATE TABLE IF NOT EXISTS minigame_result (
    jti              TEXT PRIMARY KEY REFERENCES minigame_session(jti),
    character_id     INTEGER NOT NULL REFERENCES character(id) ON DELETE CASCADE,
    score            REAL NOT NULL,
    duration_ms      INTEGER NOT NULL,
    event_hash       TEXT NOT NULL,
    modifier_applied REAL NOT NULL,
    submitted_at     INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_minigame_result_character
    ON minigame_result(character_id, submitted_at DESC);

CREATE TABLE IF NOT EXISTS character_flag (
    character_id  INTEGER NOT NULL REFERENCES character(id) ON DELETE CASCADE,
    flag_name     TEXT NOT NULL,
    flag_value    INTEGER NOT NULL DEFAULT 1,
    set_at        INTEGER NOT NULL,
    PRIMARY KEY (character_id, flag_name)
);

Apply:

wrangler d1 migrations apply --database-name aelghar-db
wrangler d1 migrations apply --database-name aelghar-db --env staging

---

Cron: Prune Expired Sessions

Daily cron (03:30 UTC, same slot as idempotency prune) deletes expired unused minigame sessions. Results are kept indefinitely for audit; sessions older than 7 days with used = 0 are deleted.

DELETE FROM minigame_session
WHERE used = 0 AND expires_at < unixepoch() - 86400;

Add to the existing daily prune Worker cron handler.

---

Indexes Summary (new rows)

Table	Index	Columns	Type
minigame_session	idx_minigame_session_character	character_id	BTree
minigame_session	idx_minigame_session_expires	expires_at WHERE used=0	Partial BTree
minigame_result	idx_minigame_result_character	character_id, submitted_at DESC	BTree