StarPunk/docs/reports/2025-11-19-migration-implementation-quick-reference.md

Migration System - Quick Reference Card

TL;DR: Add fresh database detection to migrations.py to solve chicken-and-egg problem.

The Problem

• SCHEMA_SQL includes code_verifier column (line 60, database.py)
• Migration 001 tries to add same column
• Fresh databases fail: "column already exists"

The Solution

SCHEMA_SQL = Target State (complete current schema)

• Fresh installs: Execute SCHEMA_SQL, skip migrations (already at target)
• Existing installs: Run migrations to reach target

Code Changes Required

1. Add to migrations.py (before run_migrations):

def is_schema_current(conn):
    """Check if database schema matches current SCHEMA_SQL"""
    try:
        cursor = conn.execute("PRAGMA table_info(auth_state)")
        columns = [row[1] for row in cursor.fetchall()]
        return 'code_verifier' in columns
    except sqlite3.OperationalError:
        return False

2. Modify run_migrations() in migrations.py:

After create_migrations_table(conn), before applying migrations, add:

# Check if this is a fresh database
cursor = conn.execute("SELECT COUNT(*) FROM schema_migrations")
migration_count = cursor.fetchone()[0]

# Discover migration files
migration_files = discover_migration_files(migrations_dir)

# Fresh database detection
if migration_count == 0 and is_schema_current(conn):
    # Mark all migrations as applied (schema already current)
    for migration_name, _ in migration_files:
        conn.execute(
            "INSERT INTO schema_migrations (migration_name) VALUES (?)",
            (migration_name,)
        )
    conn.commit()
    logger.info(f"Fresh database: marked {len(migration_files)} migrations as applied")
    return

3. Optional Helpers (add to migrations.py for future use):

def table_exists(conn, table_name):
    cursor = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
        (table_name,)
    )
    return cursor.fetchone() is not None

def column_exists(conn, table_name, column_name):
    try:
        cursor = conn.execute(f"PRAGMA table_info({table_name})")
        columns = [row[1] for row in cursor.fetchall()]
        return column_name in columns
    except sqlite3.OperationalError:
        return False

Test It

# Test 1: Fresh database
rm data/starpunk.db && uv run flask --app app.py run
# Expected: "Fresh database: marked 1 migrations as applied"

# Test 2: Legacy database (before PKCE)
# Create old schema, run app
# Expected: "Applied migration: 001_add_code_verifier..."

All Other Questions Answered

• Q2: schema_migrations only in migrations.py ✓ (already correct)
• Q3: Accept non-idempotent SQL, rely on tracking ✓ (already works)
• Q4: Flexible filename validation ✓ (already implemented)
• Q5: Automatic transition via Q1 solution ✓
• Q6: Helpers provided for advanced use ✓ (see above)
• Q7: SCHEMA_SQL is target state ✓ (no changes needed)

Full Details

See: /home/phil/Projects/starpunk/docs/reports/2025-11-19-migration-system-implementation-guidance.md

Architecture Reference

See: /home/phil/Projects/starpunk/docs/decisions/ADR-020-automatic-database-migrations.md (New section: "Developer Questions & Architectural Responses")