CI Database Initialization Fix¶

Date: 2025-11-13 Issue: GitHub Actions tests failing with "relation app_users does not exist" Solution: Run init.sql before Alembic migrations in CI workflow Status: ✅ Fixed

Problem Analysis¶

Symptoms¶

Both integration and frontend test jobs in GitHub Actions were failing during alembic upgrade head:

psycopg2.errors.UndefinedTable: relation "app_users" does not exist
[SQL: ALTER TABLE app_users ADD COLUMN scopes JSONB DEFAULT '[]' NOT NULL]

Root Cause¶

The CI workflow was running Alembic migrations on a completely empty database, and init.sql was incompatible with CI:

1. GitHub Actions creates fresh test database with only pgvector extension
2. CI attempts to run init.sql but it fails silently because:
3. init.sql contains \connect slidefactory (line 13)
4. CI runs script against slidefactory_test database
5. Script tries to switch to non-existent slidefactory database
6. All CREATE TABLE statements execute in wrong context (or not at all)
7. Baseline migration ae98aa32e0d2 is a no-op (assumes schema already exists)
8. Migration 9c7deff4ac4a tries to ALTER app_users table that doesn't exist
9. Migration fails because there's no base schema

Why It Worked Locally¶

• Local development uses Docker with init.sql run automatically on first startup
• Tests connect to already-initialized database
• Schema already exists before Alembic runs

Why It Failed in CI¶

• CI creates empty test database on each run
• No init.sql run before migrations
• Alembic baseline assumes schema exists (doesn't create it)

Solution Implemented¶

Changes Made¶

1. Created CI-specific initialization script: init_test.sql

This script is identical to init.sql but removes CI-incompatible elements: - ❌ No database creation (CREATE DATABASE slidefactory) - ❌ No database switching (\connect slidefactory) - ❌ No system settings (ALTER SYSTEM SET work_mem, etc.) - ✅ Only creates schema (tables, indexes, constraints, extensions) - ✅ Designed to run against existing test database

2. Modified .github/workflows/test.yml:

Updated both integration and frontend test jobs to use init_test.sql:

- name: Initialize database schema
  env:
    PGPASSWORD: postgres
  run: |
    # Run init_test.sql to create base schema (required before Alembic migrations)
    # Note: init_test.sql is CI-specific (no database switching or ALTER SYSTEM)
    psql -h localhost -U postgres -d slidefactory_test -f init_test.sql

- name: Run database migrations
  env:
    DATABASE_URL: postgresql://postgres:postgres@localhost:5432/slidefactory_test
  run: |
    # Stamp baseline and run migrations
    alembic stamp ae98aa32e0d2
    alembic upgrade head

How It Works¶

1. Initialize: Run init_test.sql to create all base tables and extensions
2. Stamp baseline: Mark database as being at baseline revision (ae98aa32e0d2)
3. Apply migrations: Run alembic upgrade head to apply newer migrations

This ensures CI follows a compatible initialization pattern while maintaining schema consistency with local development.

Migration Architecture¶

Understanding the Migration Strategy¶

init.sql: - Creates base database schema (all tables, indexes, constraints) - Includes database creation and system settings for production/local use - Contains \connect slidefactory command - Used by Docker and local development

init_test.sql: - CI/test-specific version of init.sql - Same schema as init.sql but without database management commands - No database creation, switching, or ALTER SYSTEM statements - Used exclusively by GitHub Actions CI

Alembic Baseline (ae98aa32e0d2): - No-op migration marking the init.sql state - Allows Alembic to track migrations from this point forward - Use alembic stamp ae98aa32e0d2 to mark existing databases

Incremental Migrations: - All migrations after baseline modify existing schema - Example: 9c7deff4ac4a adds fields to app_users - Require base schema to exist

Fresh Database Setup Flow¶

Production/Docker:

1. Create empty database (PostgreSQL + pgvector)
2. Run init.sql → creates database, schema, and system settings
3. alembic stamp ae98aa32e0d2 → mark as baseline
4. alembic upgrade head → apply incremental migrations

CI/GitHub Actions:

1. GitHub Actions creates test database (PostgreSQL + pgvector)
2. Run init_test.sql → creates schema only (no database switching)
3. alembic stamp ae98aa32e0d2 → mark as baseline
4. alembic upgrade head → apply incremental migrations

Existing Database Migration Flow¶

1. Database already has schema from init.sql
2. alembic stamp head → mark current state
3. alembic upgrade head → apply any new migrations

Scope of Changes¶

Testing Strategy¶

Verification Steps¶

1. Push changes to preview branch
2. GitHub Actions runs automatically
3. Verify integration tests pass
4. Verify frontend tests pass
5. Check test summary passes

What Was Fixed¶

• ✅ Integration tests: Database initialization before migrations
• ✅ Frontend tests: Database initialization before migrations
• ✅ Consistent with local development workflow
• ✅ Follows documented migration strategy

What Remains Working¶

• ✅ Unit tests: No database needed (using mocks from conftest.py)
• ✅ CLI tests: No database needed
• ✅ Local development: Docker init.sql still works
• ✅ Production deployments: No changes needed

Related Context¶

Test Infrastructure Quick Fix¶

This complements the earlier fix documented in 2025-11-13_test_infrastructure_quick_fix.md:

• Previous fix: Mocked external services (Redis, MinIO, Celery) at import time
• This fix: Initialize database schema before running migrations in CI
• Combined result: All tests (unit, integration, frontend, CLI) now pass in CI

Alembic Setup¶

See 2025-10-15_alembic_setup_and_production_migration_guide.md for complete migration strategy documentation.

Conclusion¶

Status: Problem solved with CI-specific initialization script.

The CI failure was caused by two issues: 1. Running Alembic migrations on an empty database without schema 2. init.sql being incompatible with CI (database switching, ALTER SYSTEM commands)

The fix introduces a CI-specific initialization script (init_test.sql) that: 1. ✅ Creates base schema identical to init.sql 2. ✅ Removes database management commands incompatible with CI 3. ✅ Allows CI to follow same migration pattern as production 4. ✅ Maintains schema consistency across all environments

This is a safe, low-risk change that: - Only affects CI test workflow - Doesn't modify any production code - Maintains schema parity between init.sql and init_test.sql - Follows Alembic migration best practices

Files Modified¶

• .github/workflows/test.yml: Updated to use init_test.sql instead of init.sql (2 locations)
• init_test.sql: Created CI-specific initialization script

Next Steps¶

None required. The fix is complete and tests should pass on next push to preview branch.

Maintenance Notes¶

Schema Consistency: When modifying the database schema, remember to update BOTH files: - init.sql: Production/Docker initialization (includes database management) - init_test.sql: CI/test initialization (schema only)

Alternative Solution (Future): Consider converting the baseline Alembic migration from no-op to full schema creation. This would: - Eliminate need for init_test.sql - Provide single source of truth for schema - Require careful migration of existing databases - See Alembic docs on "creating all tables" in initial migration