# Auto-Schema Migration Design

**Goal:** When a developer changes a lexicon, hatk automatically migrates the database schema on startup and re-syncs affected data. No migration files, no manual commands.

## How It Works

On every startup, after `initDatabase` creates any missing tables but before setup hooks or indexing:

1. For each collection table, query the DB for its current columns
   - SQLite: `PRAGMA table_info(tableName)`
   - DuckDB: `SELECT column_name, data_type FROM information_schema.columns WHERE table_name = ?`

2. Compare against `generateTableSchema()` output from the current lexicons

3. Emit changes:
   - **New column in lexicon** → `ALTER TABLE ADD COLUMN name TYPE [DEFAULT NULL]`
   - **Column removed from lexicon** → `ALTER TABLE DROP COLUMN name`
   - **Column type changed** → drop column + re-add with new type
   - **New child/union table** → already handled by `CREATE TABLE IF NOT EXISTS`
   - **Child/union table removed** → `DROP TABLE IF EXISTS`

4. Apply same logic to child tables and union branch tables

5. Log every change:
   ```
   [migration] added column "rating" INTEGER to "fm.teal.alpha.feed.play"
   [migration] dropped column "old_field" from "fm.teal.alpha.feed.play"
   ```

6. No automatic re-sync for column changes — existing data is already indexed, and new fields won't exist in old network records. But if any collection table is **empty** (newly added collection), mark all repos as `pending` so the backfill cycle picks up records for the new collection.

## What This Means for Developers

- Change a lexicon field → restart dev server → schema updates automatically → data re-syncs
- Add a new collection → restart → table created → backfill picks it up
- Remove a collection → restart → warning logged, run `hatk destroy collection` to clean up
- No migration files, no migration history, no `hatk migrate` command

## Edge Cases

- **SQLite ALTER TABLE limitations**: SQLite doesn't support `DROP COLUMN` before 3.35.0. `better-sqlite3` bundles SQLite 3.45+, so this is fine.
- **Indexes**: If a ref column is added, the corresponding index is created. If removed, the index is dropped with the column.
- **FTS**: FTS indexes are rebuilt on every startup anyway, so schema changes are automatically reflected.
- **Data loss on column drop/type change**: Acceptable because data comes from the AT Protocol network and is recoverable via backfill. Log clearly so developers understand what happened.

## Implementation Location

Add a `migrateSchema()` function in `database/db.ts` called from `initDatabase` after DDL execution. It receives the `TableSchema[]` and compares against the live database state.