Problem
backend/ingestion/db.py's _init_tables uses a single CREATE TABLE IF NOT EXISTS script called on every connection. There is no versioning, no migration runner, and no mechanism to alter an existing table's columns once data has been ingested — any future schema change (a new column, a renamed field, a new index) has no defined path other than manually dropping and recreating the database.
Why this matters
As the fundamentals store grows (currently backing SEC filings and transcript claims, likely to grow with future providers), schema changes are inevitable. Without a migration path, every schema change becomes a breaking, manual, undocumented operation — risky for anyone running the ingestion pipeline against real accumulated data, and a recurring source of "works on my machine" bugs between contributors on different schema versions.
Acceptance Criteria
Files likely to modify
finverify-terminal/backend/ingestion/db.py
finverify-terminal/backend/tests/test_db_migrations.py
Skills required
Python, SQLite, database migration concepts
Estimated difficulty
3/5
Estimated effort
6–10 hours
Problem
backend/ingestion/db.py's _init_tables uses a single CREATE TABLE IF NOT EXISTS script called on every connection. There is no versioning, no migration runner, and no mechanism to alter an existing table's columns once data has been ingested — any future schema change (a new column, a renamed field, a new index) has no defined path other than manually dropping and recreating the database.
Why this matters
As the fundamentals store grows (currently backing SEC filings and transcript claims, likely to grow with future providers), schema changes are inevitable. Without a migration path, every schema change becomes a breaking, manual, undocumented operation — risky for anyone running the ingestion pipeline against real accumulated data, and a recurring source of "works on my machine" bugs between contributors on different schema versions.
Acceptance Criteria
A lightweight migration approach is adopted (a lightweight tool, or a hand-rolled versioned-migrations directory with a schema_version table) — either is acceptable as long as it is documented
Existing _init_tables behavior is preserved for a fresh database
A test exercises applying migrations to both an empty database and one seeded with the current schema
CONTRIBUTING.md documents how to add a new migration
Tests added where appropriate
Files likely to modify
finverify-terminal/backend/ingestion/db.py
finverify-terminal/backend/tests/test_db_migrations.py
Skills required
Python, SQLite, database migration concepts
Estimated difficulty
3/5
Estimated effort
6–10 hours