Fix Alembic migration drift (blocks CI build-and-push) #176

New issue

Closed

opened 2026-03-15 00:03:02 +00:00 by forgejo_admin · 0 comments

forgejo_admin commented

2026-03-15 00:03:02 +00:00

Owner

Lineage

incident-paledocs-alembic-drift-2026-03-14 — discovered during plan-pal-e-platform → Phase 7a

Repo

forgejo_admin/pal-e-docs

User Story

As a platform operator
I want alembic check to pass in CI
So that the build-and-push step runs and new images deploy automatically

Context

The CI migration-test step runs alembic upgrade head then alembic check. The upgrade succeeds but alembic check detects model-vs-DB drift and exits with code -1 (255 unsigned). This blocks the build-and-push step, preventing any automated deploys.

The drift was discovered by querying the Woodpecker log_entries table directly (Woodpecker log API returns empty — separate issue). The specific drift items detected by alembic check:

modify_nullable on blocks.created_at, blocks.updated_at (models say NOT NULL, DB allows NULL)
modify_nullable on board_items.created_at, board_items.updated_at
modify_nullable on boards.created_at, boards.updated_at
modify_nullable on compiled_pages.html, compiled_pages.compiled_at
remove_index ix_board_items_board_column (index exists in DB but not in models)
remove_constraint boards_slug_key + change ix_boards_slug unique flag
remove_index ix_notes_parent_note_id_position (index exists in DB but not in models)
remove_index ix_notes_search_vector (index exists in DB but not in models)
remove_column notes.search_vector (column exists in DB but not in models)

File Targets

Files to create:

alembic/versions/XXXX_fix_schema_drift.py — new migration that resolves ALL drift items

Files NOT to touch:

src/pal_e_docs/models.py — models are correct, DB needs to match them
Any other existing migration files

Acceptance Criteria

alembic upgrade head succeeds on a fresh Postgres DB with pgvector
alembic check passes with no detected drift after upgrade
Existing data is preserved (nullable→NOT NULL changes need sensible defaults)
Migration is reversible (downgrade works)

Test Expectations

Run locally: PALDOCS_DATABASE_URL=postgresql+psycopg2://postgres:postgres@localhost:5432/paledocs_test alembic upgrade head && alembic check
CI pipeline passes (test + migration-test + build-and-push)
Run command: alembic upgrade head && alembic check

Constraints

Use server_default=func.now() for nullable→NOT NULL timestamp conversions (backfill existing NULLs first)
The notes.search_vector column removal must handle the case where it doesn't exist (idempotent)
Do NOT modify models — the models are the source of truth
Test against Postgres with pgvector extension (not SQLite)

Checklist

PR opened
Tests pass
No unrelated changes

incident-paledocs-alembic-drift-2026-03-14 — the incident this fixes
plan-pal-e-platform — Phase 7a blocked on this

### Lineage `incident-paledocs-alembic-drift-2026-03-14` — discovered during `plan-pal-e-platform` → Phase 7a ### Repo `forgejo_admin/pal-e-docs` ### User Story As a platform operator I want `alembic check` to pass in CI So that the build-and-push step runs and new images deploy automatically ### Context The CI migration-test step runs `alembic upgrade head` then `alembic check`. The upgrade succeeds but `alembic check` detects model-vs-DB drift and exits with code -1 (255 unsigned). This blocks the build-and-push step, preventing any automated deploys. The drift was discovered by querying the Woodpecker `log_entries` table directly (Woodpecker log API returns empty — separate issue). The specific drift items detected by `alembic check`: 1. `modify_nullable` on `blocks.created_at`, `blocks.updated_at` (models say NOT NULL, DB allows NULL) 2. `modify_nullable` on `board_items.created_at`, `board_items.updated_at` 3. `modify_nullable` on `boards.created_at`, `boards.updated_at` 4. `modify_nullable` on `compiled_pages.html`, `compiled_pages.compiled_at` 5. `remove_index ix_board_items_board_column` (index exists in DB but not in models) 6. `remove_constraint boards_slug_key` + change `ix_boards_slug` unique flag 7. `remove_index ix_notes_parent_note_id_position` (index exists in DB but not in models) 8. `remove_index ix_notes_search_vector` (index exists in DB but not in models) 9. `remove_column notes.search_vector` (column exists in DB but not in models) ### File Targets Files to create: - `alembic/versions/XXXX_fix_schema_drift.py` — new migration that resolves ALL drift items Files NOT to touch: - `src/pal_e_docs/models.py` — models are correct, DB needs to match them - Any other existing migration files ### Acceptance Criteria - [ ] `alembic upgrade head` succeeds on a fresh Postgres DB with pgvector - [ ] `alembic check` passes with no detected drift after upgrade - [ ] Existing data is preserved (nullable→NOT NULL changes need sensible defaults) - [ ] Migration is reversible (downgrade works) ### Test Expectations - [ ] Run locally: `PALDOCS_DATABASE_URL=postgresql+psycopg2://postgres:postgres@localhost:5432/paledocs_test alembic upgrade head && alembic check` - [ ] CI pipeline passes (test + migration-test + build-and-push) - Run command: `alembic upgrade head && alembic check` ### Constraints - Use `server_default=func.now()` for nullable→NOT NULL timestamp conversions (backfill existing NULLs first) - The `notes.search_vector` column removal must handle the case where it doesn't exist (idempotent) - Do NOT modify models — the models are the source of truth - Test against Postgres with pgvector extension (not SQLite) ### Checklist - [ ] PR opened - [ ] Tests pass - [ ] No unrelated changes ### Related - `incident-paledocs-alembic-drift-2026-03-14` — the incident this fixes - `plan-pal-e-platform` — Phase 7a blocked on this