[Infra] SQLite migration runner — Rails-style numbered migrations per user DB #43

New issue

Closed

opened 2026-03-23 11:10:11 -07:00 by pyr0ball · 1 comment

pyr0ball commented

2026-03-23 11:10:11 -07:00

Owner

Goal

Implement a lightweight, Rails-inspired migration system for staging.db. Each cloud user and self-hosted instance has their own SQLite file; migrations must apply to each DB independently on first use after a deploy.

Design

migrations/ directory — numbered .sql files (001_baseline.sql, 002_add_xyz.sql)
schema_migrations table in each DB tracks applied versions
scripts/db_migrate.py — migrate_db(db_path) runner called at API startup
Migration 001 uses CREATE TABLE IF NOT EXISTS everywhere so existing DBs silently skip it
New DBs built from scratch by running all migrations in order

Checklist

scripts/db_migrate.py with migrate_db(db_path: Path) runner
migrations/001_baseline.sql capturing current full schema
Wire migrate_db() into dev_api.py lifespan startup
Wire migrate_db() into Streamlit app.py startup
Convention: each future schema change ships a new numbered .sql file
Document the migration convention in docs/contributing.md

Why not Alembic?

Alembic assumes a single shared DB. Here each user has their own file — migrations must be lazy (run on first request) and path-agnostic. A ~50-line custom runner fits better than pulling in SQLAlchemy for this.

## Goal Implement a lightweight, Rails-inspired migration system for `staging.db`. Each cloud user and self-hosted instance has their own SQLite file; migrations must apply to each DB independently on first use after a deploy. ## Design - `migrations/` directory — numbered `.sql` files (`001_baseline.sql`, `002_add_xyz.sql`) - `schema_migrations` table in each DB tracks applied versions - `scripts/db_migrate.py` — `migrate_db(db_path)` runner called at API startup - Migration 001 uses `CREATE TABLE IF NOT EXISTS` everywhere so existing DBs silently skip it - New DBs built from scratch by running all migrations in order ## Checklist - [ ] `scripts/db_migrate.py` with `migrate_db(db_path: Path)` runner - [ ] `migrations/001_baseline.sql` capturing current full schema - [ ] Wire `migrate_db()` into `dev_api.py` lifespan startup - [ ] Wire `migrate_db()` into Streamlit `app.py` startup - [ ] Convention: each future schema change ships a new numbered `.sql` file - [ ] Document the migration convention in `docs/contributing.md` ## Why not Alembic? Alembic assumes a single shared DB. Here each user has their own file — migrations must be lazy (run on first request) and path-agnostic. A ~50-line custom runner fits better than pulling in SQLAlchemy for this.

pyr0ball added this to the Paid Tier GA milestone 2026-04-04 16:33:18 -07:00

pyr0ball referenced this issue from a commit

2026-04-04 19:38:00 -07:00

feat: wire circuitforge-core config.load_env at entry points (closes #68 partial)

pyr0ball referenced this issue

2026-04-04 19:38:17 -07:00

chore: wire cf-core shared modules (db, config, llm/router, tasks/scheduler, resources) #68

pyr0ball commented

2026-04-04 22:17:53 -07:00

Author

Owner

Implemented in 64554db. Rails-style numbered SQL migration runner:

migrations/001_baseline.sql — full schema baseline
scripts/db_migrate.py — runner with schema_migrations tracking
Wired into FastAPI + Streamlit startup paths
6 passing tests
Contributing guide updated

Implemented in `64554db`. Rails-style numbered SQL migration runner: - `migrations/001_baseline.sql` — full schema baseline - `scripts/db_migrate.py` — runner with `schema_migrations` tracking - Wired into FastAPI + Streamlit startup paths - 6 passing tests - Contributing guide updated

pyr0ball referenced this issue from a commit

2026-04-04 22:17:54 -07:00

feat(#43): numbered SQL migration runner (Rails-style)

pyr0ball closed this issue

2026-04-04 22:17:59 -07:00