Implement SQLite migration framework with versioned schema migrations

[devJaja]

Summary

The backend uses better-sqlite3 with raw SQL schema setup. There is a migrations/ directory in db/ and a events.sql file, but no formal, versioned migration framework. Schema changes are applied ad-hoc, risking data loss or schema drift between environments.

Priority

P1 — Schema changes cannot currently be applied safely to existing databases.

Files to work on

backend/src/db/migrations/ (create migrations framework)
backend/src/db/index.ts
backend/src/config/index.ts

Requirements

• Create a MigrationRunner class that tracks applied migrations in a schema_migrations table
• Create migration files 0001_init.sql, 0002_indexes.sql, etc. in backend/src/db/migrations/
• Each migration: { id, filename, sql, appliedAt } tracked in schema_migrations
• Runner applies pending migrations in order inside a transaction, then records them
• Down migrations not required (forward-only for this stage)
• Support --include-down-migrations flag during development
• Enforce: migrations are idempotent (safe to run multiple times)
• Add a npm run db:migrate script

Acceptance Criteria

• npm run db:migrate applies all pending migrations
• Running migrate twice is a no-op (idempotent)
• schema_migrations table records applied migration versions
• If a migration fails, the transaction rolls back and remaining migrations are not applied
• Existing tasks, agents, payments schema is captured in migrated files
• Migration history works from a fresh empty database

[achebejohnpaul]

Am a full-Stack Engineer focused on frontend, backend, APIs, automation, and modern web technologies. Passionate about solving real-world problems, contributing to open source, and delivering clean, scalable, review-ready solutions.