🚨 Auto-apply data/sql migrations + fix NN collision (root cause 500 ошибки) #150

New issue

Closed

opened 2026-05-15 04:53:28 +00:00 by lekss361 · 0 comments

lekss361 commented

2026-05-15 04:53:28 +00:00

(Migrated from github.com)

Root cause production 500 errors

User report 2026-05-15: GET /api/v1/admin/site-finder/weight-profiles?user_id=admin → 500

mcp__postgres-gendesign__execute_sql SELECT to_regclass(...) подтвердил 3 unapplied migrations на prod:

File	PR	Status
87_engineering_networks_191fz.sql	#118 merged	❌ not applied
89_drop_dead_brin_rosreestr_deals.sql	#122 merged	❌ not applied
90_user_weight_profiles.sql	#136 merged	❌ not applied — причина 500

Why auto-apply не работало

backend/alembic/versions/ пустой — Alembic configured но не используется
deploy.yml не имеет migration step
backend/app/main.py startup не вызывает Alembic
Per CLAUDE.md "raw SQL artifacts требуют manual apply" — но 47+ файлов накопилось, easy to forget

NN collision discovered

Также два файла на 87_:

87_engineering_networks_191fz.sql (PR #118 — мой renumber 85→87 был неправильным!)
87_on_demand_indexes.sql (предсуществующий)

Нужен новый renumber → 91_engineering_networks_191fz.sql до first auto-apply.

Proposed Fix

A. Auto-apply mechanism (deploy.yml)

# В deploy step после `docker compose pull`, перед `up -d`:

# Ensure tracking table
psql "$DB_URL" -c "CREATE TABLE IF NOT EXISTS _schema_migrations (
  filename TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
)"

# Apply pending in NN order
for sql_file in $(ls data/sql/*.sql | sort); do
  fname=$(basename "$sql_file")
  count=$(psql "$DB_URL" -tA -c "SELECT COUNT(*) FROM _schema_migrations WHERE filename='$fname'")
  if [ "$count" = "0" ]; then
    echo "Applying $fname..."
    psql "$DB_URL" -v ON_ERROR_STOP=on -f "$sql_file" || exit 1
    psql "$DB_URL" -c "INSERT INTO _schema_migrations (filename) VALUES ('$fname')"
  fi
done

B. One-time bootstrap

После первого merge auto-apply PR — bootstrap tracking table со списком уже-applied файлов:

INSERT INTO _schema_migrations (filename) VALUES
  ('01_schema_rosreestr_deals.sql'),
  ('02_...'),
  ...
  ('86_v_bucket_success_score.sql'),
  ('88_nspd_quarter_dumps.sql')  -- skip 87, 89, 90 — they'll be applied next deploy
ON CONFLICT (filename) DO NOTHING;

Bootstrap семя — список только тех, что физически в prod (table_exists check для каждого). Тогда первый auto-deploy безопасно apply только #87/#89/#90.

C. Fix NN collision

Renumber data/sql/87_engineering_networks_191fz.sql → 91_engineering_networks_191fz.sql до first auto-apply.

Acceptance

_schema_migrations tracking table создана + seed-ed
deploy.yml имеет auto-apply step ДО compose up -d
First deploy after merge → applies #87(→91)/#89/#90 automatically
Subsequent deploys без новых SQL — no-op (idempotent)
Failed apply → deploy fails (no partial state)
User's 500 error closes когда #90 apply

Refs

User reports: body-stream double-read + weight-profiles 500

## Root cause production 500 errors User report 2026-05-15: `GET /api/v1/admin/site-finder/weight-profiles?user_id=admin → 500` `mcp__postgres-gendesign__execute_sql SELECT to_regclass(...)` подтвердил **3 unapplied migrations** на prod: | File | PR | Status | |---|---|---| | 87_engineering_networks_191fz.sql | #118 merged | ❌ not applied | | 89_drop_dead_brin_rosreestr_deals.sql | #122 merged | ❌ not applied | | 90_user_weight_profiles.sql | #136 merged | ❌ not applied — **причина 500** | ## Why auto-apply не работало - `backend/alembic/versions/` **пустой** — Alembic configured но не используется - `deploy.yml` **не имеет migration step** - `backend/app/main.py` startup **не вызывает Alembic** - Per CLAUDE.md "raw SQL artifacts требуют manual apply" — но 47+ файлов накопилось, easy to forget ## NN collision discovered Также два файла на 87_: - `87_engineering_networks_191fz.sql` (PR #118 — мой renumber 85→87 был неправильным!) - `87_on_demand_indexes.sql` (предсуществующий) Нужен **новый renumber → 91_engineering_networks_191fz.sql** до first auto-apply. ## Proposed Fix ### A. Auto-apply mechanism (deploy.yml) ```bash # В deploy step после `docker compose pull`, перед `up -d`: # Ensure tracking table psql "$DB_URL" -c "CREATE TABLE IF NOT EXISTS _schema_migrations ( filename TEXT PRIMARY KEY, applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW() )" # Apply pending in NN order for sql_file in $(ls data/sql/*.sql | sort); do fname=$(basename "$sql_file") count=$(psql "$DB_URL" -tA -c "SELECT COUNT(*) FROM _schema_migrations WHERE filename='$fname'") if [ "$count" = "0" ]; then echo "Applying $fname..." psql "$DB_URL" -v ON_ERROR_STOP=on -f "$sql_file" || exit 1 psql "$DB_URL" -c "INSERT INTO _schema_migrations (filename) VALUES ('$fname')" fi done ``` ### B. One-time bootstrap После первого merge auto-apply PR — **bootstrap** tracking table со списком **уже-applied** файлов: ```sql INSERT INTO _schema_migrations (filename) VALUES ('01_schema_rosreestr_deals.sql'), ('02_...'), ... ('86_v_bucket_success_score.sql'), ('88_nspd_quarter_dumps.sql') -- skip 87, 89, 90 — they'll be applied next deploy ON CONFLICT (filename) DO NOTHING; ``` Bootstrap семя — список **только тех, что физически в prod** (table_exists check для каждого). Тогда первый auto-deploy безопасно apply только #87/#89/#90. ### C. Fix NN collision Renumber `data/sql/87_engineering_networks_191fz.sql` → `91_engineering_networks_191fz.sql` до first auto-apply. ## Acceptance - [ ] `_schema_migrations` tracking table создана + seed-ed - [ ] deploy.yml имеет auto-apply step ДО `compose up -d` - [ ] First deploy after merge → applies #87(→91)/#89/#90 automatically - [ ] Subsequent deploys без новых SQL — no-op (idempotent) - [ ] Failed apply → deploy fails (no partial state) - [ ] User's 500 error closes когда #90 apply ## Refs User reports: body-stream double-read + weight-profiles 500