Shadowling Db

---
name: shadowling-db
description: Use whenever you read, query, or mutate shadowling's sqlite database (shadowling.db) — any SELECT/INSERT/UPDATE/DELETE, ad-hoc SQL, dropping/clearing/resetting rows, inspecting data, or checking a migration's result. All DB access goes through a skill entrypoint, a repository method (`models/*`), or `sql.py` — never raw sqlite3. Schema/data-layer changes are NOT made here — they go through an appended migration in appdb.py; this skill only documents that rule and verifies the outcome.
---

# shadowling DB conventions

One sqlite database (`~/.shadowling/shadowling.db`), stdlib `sqlite3` only.
`plugins/shadowling/appdb.py` owns the connection (`connect()`: WAL,
`busy_timeout=5000`, `row_factory=Row`, migration runner, view recreation).
Never open the DB another way; ad-hoc reads and dev surgery go through
`sql.py` (the old `capture.py query` verb is folded into `sql.py`).

## Schema changes: MIGRATIONS only

Versioning = `PRAGMA user_version` + the ordered `MIGRATIONS` list in
`appdb.py`. To change schema, APPEND one new migration entry. The runner
backs up the file (`.bak`), applies pending steps each in its own
transaction, and bumps `user_version` atomically.

**Never:**
- edit an already-shipped migration or a `CREATE TABLE` in place — fresh
  installs replay the SAME migration chain old installs took; there is one
  schema history, not two;
- run ad-hoc `ALTER`/"add column if missing" checks at connect time;
- add a migrations table, alembic, aiosqlite, or any ORM (stdlib-only rule).

## Views are derived code, not schema

`*_ranked` views are ensured current on EVERY `connect()` (recreated whenever
the definition in code differs from `sqlite_master`). Changing a
ranking/aggregation = edit the view definition in `appdb.py`; no migration,
no version bump. Products (counters, created/updated, latest example) are
COMPUTED from incident rows — never stored.

## Data doctrine

- Category tables (grammar, friction, …) are **append-only event logs**: one
  INSERT per incident; never UPDATE/DELETE recorded text. Uniqueness lives in
  `GROUP BY` (keys pre-normalized: slugify / casefold).
- Mutable state is the explicit exception: `vocab.remaining/status`,
  `messages.langs/processed_at/kind`, and the tutor's `mastery` table
  (box/due_date — scheduling state). `attempts` is append-only like the
  incident tables; its `answer` is stored VERBATIM (it doubles as the
  mark-drills registry).

## Column naming convention (since migration 3)

- **`created_at`** is the row-creation column on EVERY table — full ISO
  datetime on the event logs (`messages`, `attempts`) and on `vocab`/`mastery`;
  **date-only** (`YYYY-MM-DD`, via `core.today()`) on the six daily incident
  tables, whose frequency product wants days, not seconds.
- **`updated_at`** is STORED only where a row mutates: `vocab` and `mastery`.
  The incident tables expose it as the view's `MAX(created_at)` (a product, not
  a stored column — see the doctrine above); `messages` uses `processed_at` for
  its one lifecycle event; append-only `attempts` has no `updated_at`.
- **`learner_wrote`** is the canonical "what the learner produced" column on
  `rephrasing`/`idioms`/`decode`/`friction` (the views still alias it to a
  category-readable header: "you wrote" / "you reached for"). `grammar` keeps
  its matched `original`/`fixed` pair.
- Suffix rule: `_at` = full ISO datetime, a bare date (`due_date`) = `YYYY-MM-DD`.
  The retired names `ts` and `date` must not come back.
- IDs: semantic slugs where meaning matters, bare `INTEGER PRIMARY KEY`
  where mechanics matter. No readable-ID generators.
- Always parameterized queries (`?`), transactions via `with con:`.

## Ad-hoc queries & dev surgery (sql.py)

`plugins/shadowling/sql.py` is the dev console — the replacement for any DB
MCP / raw `sqlite3` use. Raw `sqlite3` against the live db is forbidden
(WAL-consistency + perms).

- Prefer the data layer first: each skill's entrypoint (`<cat>.py record/select`,
  `loot.py add`, `drop.py remove`, `triage.py tag`, `debrief.py mark-processed`) and
  the repository methods in `models/*`. `db.py` export is replaced by
  `sql.py --md "SELECT …"`.
- Any read: `python3 sql.py "<SELECT …>" [param …]` (JSON per row) or
  `--md` (markdown table). Params bind to `?` — never inline values.
- Mutations: `python3 sql.py --write "<SQL>" [param …]` — LAST resort for
  surgery (deduping a corrupted row, fixing a bad tag). Auto-snapshots to
  `<data_dir>/backups/` (keep last 10) before executing.
- Manual snapshot before risky experiments: `python3 sql.py backup`.
- Temp-home testing: `export SHADOWLING_HOME=$(mktemp -d)` and EVERY command
  that depends on it MUST run in the SAME Bash invocation — env vars do not
  survive across calls; a lost SHADOWLING_HOME silently retargets every
  command at the real `~/.shadowling`. `sql.py --write` announces its target
  (`db: <path>` on stderr) — verify it before trusting a destructive demo.
- Restore: `cp <data_dir>/backups/<snap>.db <data_dir>/shadowling.db`
  (remove the `-wal`/`-shm` siblings first).