Skip to main content

Dev stack: recovering from a wedged upsquad-migrate (dirty schema version)

Scope: DEV / sandbox compose stacks only. Never run the force / down / volume-reset steps below against a production database. Always confirm the container and volume names belong to a dev stack (upsquad-* for docker-compose.dev.yml, upsquad-ics-* for docker-compose.ics.yml) before touching anything.

Tracks core#1153.

Symptom​

  • docker ps shows upsquad-context-engine (or the dev backend) stuck in Created and never reaching Up.
  • upsquad-migrate logs error: Dirty database version <N>. Fix and force version. and refuses to apply or roll back.
  • Downstream: the dev client / portal proxy (src/app/api/rpc/[...path]) returns 502 backend unreachable or ENOTFOUND because its upstream (CE_INTERNAL_URL, default http://context-engine:8080) never came up.

golang-migrate sets schema_migrations.dirty = true and writes the target version before it runs the migration's SQL, then clears dirty only after the statements commit. If the migrate process is killed between those two points (container OOM/kill, dev box reboot, Ctrl-C, lost DB connection), the row is left dirty = true even though the SQL either fully rolled back or was never run. golang-migrate then refuses every subsequent run until a human confirms the real schema state and clears the flag.

Most common root cause​

An interrupted apply, not a buggy migration. The migrations in internal/context/store/migrations/ are written to be transactional and idempotent (CREATE TABLE/INDEX IF NOT EXISTS, RLS enable/force, etc.) and run inside lib/pq's implicit single-statement-batch transaction, so a SQL failure rolls the whole migration back atomically. A dirty flag with no SQL error in the logs therefore almost always means the process was interrupted mid-run (this is exactly what happened in core#1153 — dirty version 87 / 087_quad_threads got stuck during the 2026-04-26 redesign deploy churn on the founder dev box).

Recovery​

1. Confirm which stack and find the real schema state​

# DEV stack DB (docker-compose.dev.yml): user/db = upsquad/upsquad
docker exec upsquad-postgres \
  psql -U upsquad -d upsquad -c "SELECT * FROM schema_migrations;"

# ICS sandbox DB (docker-compose.ics.yml): user=upsquad db=upsquad_ics
docker exec upsquad-ics-postgres \
  psql -U upsquad -d upsquad_ics -c "SELECT * FROM schema_migrations;"

If dirty = t at version N, decide whether N half-applied. Open internal/context/store/migrations/<NNN>_*.up.sql and check whether its objects already exist:

# Example for 087_quad_threads: did the table/index/policy land?
docker exec upsquad-postgres psql -U upsquad -d upsquad -c "\d+ quad_threads"
docker exec upsquad-postgres psql -U upsquad -d upsquad -c \
  "SELECT polname FROM pg_policy WHERE polrelid = 'quad_threads'::regclass;"

2a. Clear the dirty flag (preserve data)​

  • If the objects do NOT exist → the up rolled back / never ran. Force back to the previous version so migrate re-applies N:

    docker compose -f docker-compose.dev.yml run --rm migrate force <N-1>
    docker compose -f docker-compose.dev.yml run --rm migrate up

  • If the objects fully exist and match the up's expected result → the up succeeded but the flag never cleared. Force to N:

    docker compose -f docker-compose.dev.yml run --rm migrate force <N>
    docker compose -f docker-compose.dev.yml run --rm migrate up   # -> "no change"

    (The Makefile wraps this as make dev-migrate.)

2b. Clean reset (DEV ONLY, data is disposable)​

If the dev data is throwaway junk it's faster to wipe and rebuild. Verify the volume name is the dev stack's first.

make dev-reset    # docker-compose.dev.yml: down -v && up -d && run --rm migrate
# ICS sandbox equivalent (drops ONLY upsquad_ics_* volumes):
make ics-reset && make ics-up

3. Bring the stack back healthy and verify​

make dev-up                      # up -d + migrate (expect "no change")
docker ps | grep upsquad-        # context-engine should be Up, not Created

# Smoke: gateway health + an auth-enforced RPC (401 == server up + routing OK)
curl -s -o /dev/null -w "%{http_code}\n" http://127.0.0.1:8090/health         # 200
curl -s -o /dev/null -w "%{http_code}\n" -X POST \
  http://127.0.0.1:8083/upsquad.agent.v1.AgentService/ListAgents \
  -H 'Content-Type: application/json' -d '{}'                                  # 401

The dev client proxy defaults CE_INTERNAL_URL to http://context-engine:8080 when unset, so once the dev context-engine is Up the client routes back to it automatically — no env change needed unless a workaround explicitly set CE_INTERNAL_URL to the ICS gateway.

Tracks #1359. Distinct from a dirty DB: here schema_migrations.dirty = f and the stack is healthy, but the dev DB sits at an OLD version while main has newer migrations, so new RPCs 500 on missing tables even though the deployed image already carries the handlers.

Root cause​

docker-compose.dev.yml's migrate one-shot bind-mounts ./internal/context/store/migrations from the dev-box checkout (/opt/upsquad/upsquad-core). The Deploy Dev Stack workflow used to only docker pull the GHCR images — it never git-synced /opt. So /opt drifted behind main, migrate had no new files to apply, and every "successful" reconcile silently skipped schema changes. On 2026-06-15 /opt sat at migration 100 while main was at 104; the new OrgVersion/OrgDraft RPCs 500'd because their tables never got created.

Fix (shipped)​

.github/workflows/dev-deploy.yml now runs a "Sync the dev-box working tree to origin/main" step BEFORE login/reconcile: git fetch origin main then git reset --hard origin/main. It uses reset --hard (not pull) because a merge-based pull aborts on untracked-vs-incoming-tracked collisions (e.g. an untracked infra/pulumi/upsquad-infra/package-lock.json on the box) and on a half-finished merge (a stray UU unmerged path has been seen on /opt). It does not git clean, so untracked files are preserved.

Safety guard (non-blocking, recovery-by-archive): reset --hard discards uncommitted tracked changes. By policy nobody works directly in /opt (agents use /tmp worktrees, see CLAUDE.md), so dirtiness there is drift, not protected work — a hard-fail guard would block every deploy on benign drift and reintroduce the manual step this issue removes. Instead the step archives any uncommitted tracked diff to /var/tmp/dev-deploy-discarded-<UTC>.patch and logs it before resetting, so nothing is silently lost (also recoverable via git reflog). If you ever need to recover discarded work, look there.

Long-term durable fix (follow-up): bake the migrations INTO a built image and stop bind-mounting /opt, removing the corpus/main divergence class entirely. Tracked as a separate issue; the migrate service currently runs the upstream migrate/migrate image, so this needs a dedicated migrate image (new GHCR package) or embedding migrations in the Go binary.

Manual detection / recovery​

# What version is the dev DB at?
docker exec upsquad-postgres \
  psql -U upsquad -d upsquad -c "SELECT * FROM schema_migrations;"

# What does /opt have vs main?
git -C /opt/upsquad/upsquad-core fetch origin main
git -C /opt/upsquad/upsquad-core log --oneline HEAD..origin/main \
  -- internal/context/store/migrations/   # any lines == /opt is behind

# Recover: sync /opt, re-run migrate, recreate the backend.
git -C /opt/upsquad/upsquad-core reset --hard origin/main
docker compose -f docker-compose.dev.yml run --rm migrate up
docker compose -f docker-compose.dev.yml up -d --force-recreate context-engine

If make ics-up / make dev-up containers crash-loop with DB/redis connection errors, check whether the stateful containers came back:

docker ps -a --format '{{.Names}}\t{{.Status}}' | grep -E 'postgres|redis'

The compose stateful services use restart: no, so a host reboot or a manual docker stop can leave postgres/redis Exited (0) while the app containers (which restart) crash-loop against missing infra. Start the infra first, then the app containers self-recover on their next restart cycle:

docker start upsquad-ics-postgres upsquad-ics-redis   # or upsquad-postgres/redis
# app containers reconnect on backoff; restart them to skip the wait if needed

Prevention (recommended): set restart: unless-stopped on the postgres and redis services in the dev/ICS compose files so a reboot brings infra back before the app containers retry. Tracked as a follow-up to core#1153.

Edit this page
Last updated on by upsquad-backend-sme[bot]