pal-e-docs

Incident: SQLite Migration Crash — PR #61 Deployment

incident-2026-03-02-sqlite-migration-crash-pr61 Doc

deployment resolved sre
Project

Incident: SQLite Migration Crash — PR #61 Deployment (2026-03-02)

Summary

pal-e-docs API went down for ~10 minutes after merging PR #61 (note decomposition schema). Same root cause as the PR #29 incident from 2026-02-26: SQLite auto-commits DDL statements, so Alembic migrations that add multiple columns leave the DB in a partial state when they fail.

Timeline

  • T+0: PR #61 merged (squash). Woodpecker CI pipeline #81 succeeds. Image pushed to Harbor.
  • T+~2m: ArgoCD deploys new image. Pod starts, runs Alembic migration.
  • T+~2m: Migration adds 3 of 4 columns (note_type, status, parent_note_id) but crashes before adding position column. alembic_version NOT stamped. Pod enters CrashLoopBackOff.
  • T+~5m: Detected via MCP tool returning 502. Confirmed pod 1/2 Ready, CrashLoopBackOff.
  • T+~8m: Fixed manually: installed sqlite3 in litestream sidecar, added missing position column, created composite index, stamped alembic_version to d4e5f6a7b8c9.
  • T+~10m: Deleted pod. New pod started 2/2 Running. API returned 200.

Root Cause

SQLite auto-commits each DDL statement (ALTER TABLE, CREATE INDEX) individually. Alembic expects transactional DDL — either all migration steps succeed and the version is stamped, or none do. With SQLite, each ALTER TABLE commits immediately, but the alembic_version update only happens at the end. If any step fails mid-migration, the DB has partial schema changes with the old version stamp. Every subsequent restart tries the migration from the beginning and fails on the first already-applied DDL.
This is the second time this has happened (first was PR #29, 2026-02-26). The same manual fix was required both times.

Impact

  • pal-e-docs API down for ~10 minutes
  • All MCP tool calls failed (502) — affected all active Claude Code sessions using pal-e-docs tools
  • Browse frontend returned 502
  • No data loss

What Failed

  • No migration testing in CI (known gap: todo-migration-testing-ci-pal-e-docs)
  • No health check or readiness probe that would prevent traffic routing to a crashing pod
  • No rollback mechanism — ArgoCD kept deploying the same crashing image
  • SQLite fundamentally cannot do transactional DDL — this WILL happen again with any multi-step migration

Resolution

Manual fix via litestream sidecar container: apk add sqlite, then ALTER TABLE + CREATE INDEX + UPDATE alembic_version.

Prevention

  • Migrate to Postgres — Postgres supports transactional DDL. Alembic migrations are atomic. This class of bug is eliminated entirely. Plan: plan-2026-02-26-tf-modularize-postgres.
  • Migration testing in CI — run Alembic upgrade against a copy of production schema before deploying. Would catch column conflicts before they hit prod.
  • Readiness probes — prevent traffic routing to pods that haven't completed startup.
  • Previous incident: SQLite Migration Crash — PR #29 (2026-02-26)
  • plan-2026-02-26-tf-modularize-postgres — Postgres migration plan
  • todo-migration-testing-ci-pal-e-docs — CI migration testing TODO
  • deployment-lessons — deployment lessons learned