Shadowling db
Skill OleksandrHavuka/shadowling/.claude/skills/shadowling-db
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.From its SKILL.md
npx -y skills add OleksandrHavuka/shadowling --skill shadowling-dbAssembled from the repository path, not quoted from the project. Check it against their README if it does not work.
One thing to look at
- 2 stars2 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.
SKILL.md
5.0 KB, as published. Nobody here has run it
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 TABLEin 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'smasterytable (box/due_date — scheduling state).attemptsis append-only like the incident tables; itsansweris stored VERBATIM (it doubles as the mark-drills registry).
Column naming convention (since migration 3)
created_atis the row-creation column on EVERY table — full ISO datetime on the event logs (messages,attempts) and onvocab/mastery; date-only (YYYY-MM-DD, viacore.today()) on the six daily incident tables, whose frequency product wants days, not seconds.updated_atis STORED only where a row mutates:vocabandmastery. The incident tables expose it as the view'sMAX(created_at)(a product, not a stored column — see the doctrine above);messagesusesprocessed_atfor its one lifecycle event; append-onlyattemptshas noupdated_at.learner_wroteis the canonical "what the learner produced" column onrephrasing/idioms/decode/friction(the views still alias it to a category-readable header: "you wrote" / "you reached for").grammarkeeps its matchedoriginal/fixedpair.- Suffix rule:
_at= full ISO datetime, a bare date (due_date) =YYYY-MM-DD. The retired namestsanddatemust not come back. - IDs: semantic slugs where meaning matters, bare
INTEGER PRIMARY KEYwhere mechanics matter. No readable-ID generators. - Always parameterized queries (
?), transactions viawith 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 inmodels/*.db.pyexport is replaced bysql.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 --writeannounces 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/-shmsiblings first).