# DB Migration

> Schema changes in lncrawl — SQLModel DAO models, Alembic migration workflow (dev migrate CLI), Postgres/SQLite dialect differences, enum-sync migrations. Use when adding/changing a DAO model field or writing/reviewing a migration.

- Skill: `lncrawl/db-migration` (Agent Skill)
- Install (CLI): `npx skillmds@latest add lncrawl/db-migration`
- Raw SKILL.md: https://api.skillmd.com/api/skills/lncrawl/db-migration/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: lncrawl (https://skillmd.com/u/lncrawl)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/lncrawl/db-migration

---


# Schema changes

ORM: SQLModel/SQLAlchemy. Models in `lncrawl/dao/`, migrations in
`lncrawl/migrations/versions/`. `ctx.db.bootstrap()` runs on startup and **evolves the schema
differently per dialect** (`services/db.py`):

- **Fresh DB (any dialect):** built directly from the models with `create_all()` + `stamp head`
  — the migration history is never replayed on a new install.
- **Existing SQLite (single-user CLI/desktop):** **migration scripts are never executed at
  runtime.** A fingerprint of the models is cached in `PRAGMA user_version`; a matching
  fingerprint makes startup a single PRAGMA read (no Alembic import). On a mismatch the schema
  is reconciled *additively* — create missing tables/columns/indexes, skip drops and type
  changes — so an older app runs safely against a newer DB and no migration can corrupt data.
- **Existing Postgres/MySQL (server):** real Alembic migrations (`upgrade head`); never
  auto-downgrades when the DB is ahead of the code.

**Migration scripts still matter** — they are the mechanism for the server and the correctness
gate in CI (`dev migrate verify` replays every script from base and strict-checks against the
models). SQLite just doesn't consume them at runtime. Always generate a script for a model
change; CI fails otherwise.

**Additive-first discipline (what keeps SQLite self-healing):** prefer backward-compatible
model changes — new columns must be **nullable or have a `server_default`** (SQLite adds them
NULLable regardless). Renames, type narrowing, splits, and backfills are *non-additive*: they
apply on the server via the script, but on SQLite degrade to additive (the old column lingers,
data is not migrated). For a genuinely destructive change on the single-user path, use
`dev migrate rebuild`: it drops and recreates every table from the models and copies rows back
at the raw driver level (keeping only columns that still exist, preserving JSON `extra`
verbatim), backing up and restoring on failure. SQLite only; the server uses migration scripts.

## DAO model conventions

- Every table extends `BaseTable` (`dao/_base.py`): UUID string `id` PK, `created_at`/
  `updated_at` as UNIX-ms `BigInteger` (auto-touched by a `before_update` event), and a JSON
  `extra` dict. Set `table=True` + `__tablename__`; composite indexes via `__table_args__`.
- Enum columns are stored as **plain scalars, never native DB enums**. IntEnum columns use
  `sa_type=IntEnumType(SomeEnum)` (`dao/_enum.py`) → `SMALLINT` holding the member **value**
  (correct numeric ordering on every dialect; reads return the enum member and tolerate legacy
  name strings). String-enum columns use `sa_type=sa.Enum(SomeEnum, native_enum=False)` →
  `VARCHAR` holding the member **name**. Because there is no native `ENUM` type, adding an enum
  member needs **no migration at all** — no more `sync_*` revisions. Enums live in
  `lncrawl/enums.py` and are re-exported from `dao/__init__.py`, which also maintains the
  `models`/`tables` lists Alembic metadata uses.
- Use `sa_type=sa.BigInteger` for large ints, `index=True` for queried fields, and a
  `server_default` when adding a NOT NULL column to an existing table.

## Recipe

1. Edit the DAO model in `lncrawl/dao/*.py`.
2. Generate a revision: `uv run python -m lncrawl dev migrate add "message"` (autogenerate by
   default; `-n/--no-auto` for a hand-written one). Files land in `migrations/versions/` with
   a timestamp filename template and are auto-formatted by a `black` post-write hook — the
   Alembic config is built in code (`services/db.py`), not `alembic.ini`.
3. Review the generated `upgrade()`/`downgrade()` — keep them symmetric
   (`add_column`+`create_index` ↔ `drop_index`+`drop_column`). House style: module docstring
   with Revision ID/Revises/Create Date, `revision`/`down_revision` constants, and a
   `dialect = op.get_context().dialect.name` guard when behavior differs per dialect.
4. **Adding a member to an existing enum needs no migration** — enum columns are plain
   `SMALLINT`/`VARCHAR` (see the enum bullet above), so new members just work. The legacy
   `sync_*` revisions and `2026_07_14_*_drop_native_enums` are the historical record of
   removing the native types; don't add new enum-sync migrations.
5. Verify: `uv run python -m lncrawl dev migrate verify` (upgrades to head + strict schema
   check — this is also the CI gate). Local apply/rollback/status:
   `dev migrate up` / `dev migrate down` / `dev migrate status`.

## Dialect notes

- SQLite is the default (`sqlite.db` in the data dir); `DATABASE_URL` switches to
  Postgres/MySQL. `env.py` enables `render_as_batch` only on SQLite (table-rebuild for
  ALTER/DROP support) — write normal Alembic ops and let batch mode handle it.
- Session pattern in services: `with ctx.db.session() as sess:` — `expire_on_commit=False`
  and **no auto-commit**; every writer calls `sess.commit()` explicitly.