Day 04: Forging the Codex: SQLite as the Memory Seal

"Day 04 — Forging the Codex: SQLite as the Memory Seal" date: 2026-01-20 series: "Gammoridin Dev Log" tags: [tauri, rust, react, typescript, sqlite, local-first, schema]

A Chronicle without memory is just smoke—so we sealed ours in SQLite.

What we set out to build

Problem: A launcher UI can look organized while the underlying truth rots: duplicate game entries, unstable identifiers, “phantom installs,” and metadata that can’t survive rescans. If the Sanctum forgets, the player pays the price.

Promise: Keep Gammoridin local-first and deterministic. The Codex must be a real source of truth: scans become upserts, launches become sessions, and enrichment becomes cached knowledge—without relying on cloud state or fragile frontend storage.

ARCH-NOTES

Today was about naming the memory vault clearly: what we store, why we store it, and how we keep it stable as the app evolves.

DB-SCHEMA

What shipped today

✅ Codified the “four-table core” of Gammoridin’s local database:
games, game_igdb_metadata, play_sessions, settings.
This is the minimal spine that supports a launcher, a Codex, and the Chronicle—without bloat.

✅ Locked in what makes a game record “real” in the Codex:
Display + normalized naming, launcher identity (including Steam AppID when available), executable + install fields, and the user-facing state (backlog_status, cached art paths).

✅ Confirmed board-grade ordering support via sort_index to persist within-column ordering for the Backlog Board (and keep drag/drop from being a “temporary illusion”).

✅ Aligned the “data lives here” story end-to-end: SQLite sits behind the Rust ward, stored in the app data directory, and surfaced through Tauri commands—not directly mutated by the UI.

Behind the curtain

The choice — SQLite over “just state”

SQLite isn’t here because it’s trendy—it’s here because it’s boring in the best way.

Scanning needs idempotence. A scan should update what changed, not duplicate what exists. That means the backend needs a persistent catalog that can be upserted safely and queried quickly.

Play sessions are historical truth. The Chronicle needs start/end/duration records that don’t evaporate when the UI reloads or the user restarts.

Metadata is expensive and should be cached. IGDB enrichment is valuable, but it’s also network-bound and not guaranteed. Caching game_igdb_metadata keeps the Codex readable even when the outside world is quiet.

Local-first means ownership. The player’s library, statuses, and history live on their machine—full stop.

In short: the Sanctum must remember, even when the UI is asleep.

The cut — not over-normalizing (yet)

We could have broken everything into many tiny tables: genres, platforms, developers, publishers, tags, board ordering tables, and more.

We didn’t—on purpose.

The current schema stores some IGDB arrays as