refactor: Move baseline schema into migration v1 for single source of truth

[dean0x]

Overview

Refactor the database initialization to move all baseline schema definition from createTables() into the first migration (v1), making migrations the single source of truth for the database schema.

Current Implementation

Location: src/implementations/database.ts:81-170 + src/implementations/database.ts:263-266

Currently, the baseline schema is defined in two places:

1. createTables() (lines 99-170): Creates all tables and indexes
2. Migration v1 (lines 263-266): Empty migration that just records baseline version

// createTables() creates tables directly
this.db.exec(`CREATE TABLE IF NOT EXISTS tasks (...)`);
this.db.exec(`CREATE TABLE IF NOT EXISTS task_dependencies (...)`);

// Migration v1 is empty
{
  version: 1,
  description: 'Baseline schema with tasks, dependencies, and output tables',
  up: (db) => {
    // Migration v1 is the baseline - tables are already created in createTables()
    // This just records the baseline version
  }
}

Problem

• Schema definition is split between createTables() and migrations
• Migrations are not the single source of truth
• Violates database migration best practices
• Makes it harder to understand schema evolution over time
• Fresh databases vs upgraded databases follow different code paths

Proposed Solution

Move all schema creation into migration v1:

private createTables(): void {
  // SCHEMA MIGRATIONS: Only create the migrations table here.
  // All other tables should be created within migrations.
  this.db.exec(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      version INTEGER PRIMARY KEY,
      applied_at INTEGER NOT NULL,
      description TEXT
    )
  `);

  // Get current schema version
  const currentVersion = this.getCurrentSchemaVersion();

  // Apply all pending migrations
  this.applyMigrations(currentVersion);
}

private getMigrations() {
  return [
    {
      version: 1,
      description: 'Baseline schema with tasks, dependencies, and output tables',
      up: (db) => {
        // This migration establishes the baseline schema
        db.exec(`
          CREATE TABLE IF NOT EXISTS tasks (
            id TEXT PRIMARY KEY,
            ...
          );
          
          CREATE TABLE IF NOT EXISTS task_output (...);
          CREATE TABLE IF NOT EXISTS task_dependencies (...);
          
          CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
          ...
        `);
      }
    }
  ];
}

Benefits

• Migrations become single source of truth for schema
• Consistent code path for fresh and upgraded databases
• Easier to understand schema evolution
• Follows database migration best practices
• Makes testing and debugging schema changes easier

Trade-offs

• Slightly longer first-run initialization (negligible)
• Requires careful migration testing for existing databases

Implementation Plan

1. Move all CREATE TABLE and CREATE INDEX statements from createTables() into migration v1's up() function
2. Update createTables() to only create schema_migrations table
3. Test with fresh database (should work identically)
4. Test with existing v0.3.0 database to ensure smooth upgrade
5. Update documentation to reflect new pattern

Migration Safety

This change is safe because:

• CREATE TABLE IF NOT EXISTS ensures idempotency
• Existing databases already have schema_migrations table with version 1 recorded
• Migration system will skip v1 for existing databases (currentVersion >= 1)

References

• Identified in PR feat: Task Dependencies v0.3.0 - Post-Review Fixes #9 review by qodo-merge-for-open-source
• Database migration best practices: migrations as single source of truth

Target

v0.3.1 - Architecture Improvements