2 Commits

Author	SHA1	Message	Date
黄云龙	88a1311fe8	fix(DB): legacy backfill creates missing Index objects on existing tables (#4090) ... * fix(DB): legacy backfill creates missing Index objects on existing tables _run_baseline_create_all_sync calls create_all(tables=..., checkfirst=True). SQLAlchemy's Table.create(checkfirst=True) skips the table AND all its Index objects when the table already exists, so an index added to the ORM model after the table was first provisioned (e.g. the partial unique index uq_channel_connection_active_identity on channel_connections) is never created, and stamping 0001_baseline skips alembic's own create_index call too. Fix: after create_all, explicitly create every Index on every baseline table with Index.create(checkfirst=True), which checks for the specific index independently of the table. Co-Authored-By: Claude <noreply@anthropic.com> * docs(bootstrap): note future-index backfill collision risk for revisions Add a short forward-looking block under the legacy index-level backfill (at backend/packages/harness/deerflow/persistence/bootstrap.py:308-322) pointing the next contributor at the same shape risk the module already documents for _baseline_TABLE_NAMES: if a future post-baseline revision adds an index to a baseline table via op.create_index(...) without checkfirst=True, the backfill above will already have pre-created it and the upgrade will collide with 'index already exists'. Mirror safe_add_column and use checkfirst=True (or a future safe_create_index helper) in such a revision. * fix(#4090): scope legacy backfill index loop to _BASELINE_INDEX_NAMES willem-bd identified that the index loop iterated table.indexes (current ORM models full set), which includes post-baseline indexes like uq_runs_thread_active from 0004. Creating these prematurely before their owning revisions dedup step raises IntegrityError on legacy DBs with duplicate active rows -- bricking bootstrap. Changes: - Add _BASELINE_INDEX_NAMES: frozenset of 23 indexes that 0001_baseline actually creates (mirrors _BASELINE_TABLE_NAMES pattern) - Guard index-creation loop with _BASELINE_INDEX_NAMES filter - Wrap each idx.create() in try/except for graceful duplicate-data handling (uq_channel_connection_active_identity has no owning revision dedup) - Fix misleading checkfirst=True comment for alembic op.create_index - Add guard test pinning _BASELINE_INDEX_NAMES against 0001 output - Add regression test with duplicate active runs (the exact crash case) - Add regression test with duplicate channel connections (graceful handling) * fix(tests): update legacy backfill fixtures to current schema - Seed runs and channel_connections using the actual 0001 baseline columns. - Preserve the duplicate-active-row conditions exercised by the regression tests. - Apply ruff formatting required by backend CI. --------- Co-authored-by: Claude <noreply@anthropic.com>	2026-07-14 21:41:35 +08:00
AnoobFeng	debb0fd161	feat(persistence): wire alembic migrations, bootstrap schema on startup (#3706) ... * feat(persistence): wire alembic migrations + bootstrap schema on startup Closes #3682. Pre-#3658 DBs lack the `runs.token_usage_by_model` column because alembic was never wired up — startup only ran `create_all`, which never ALTERs existing tables. Adds a hybrid bootstrap in FastAPI lifespan (replaces bare `create_all`): - empty DB → create_all + stamp head - legacy DB → stamp 0001_baseline + upgrade head - versioned DB → upgrade head Concurrency: Postgres `pg_advisory_lock` (cross-process); SQLite per-engine `asyncio.Lock` + 30s `PRAGMA busy_timeout` on both prod and alembic engines. Column revisions use `safe_add_column` / `safe_drop_column` idempotent helpers as fallback. Other bits: - 0001 baseline (chain root) + 0002 add `runs.token_usage_by_model` - `include_object` filter so alembic ignores LangGraph checkpointer tables - `make migrate-rev MSG="..."` for authoring new revisions (no migrate/stamp targets — startup is the only execution path) - Tests: three-branch decision, concurrency, #3682 regression, env filter, blocking-IO gate anchor - CLAUDE.md: new "Schema migrations" section * fix(style): fix lint error * perf(persistence): address review feedback on alembic bootstrap Behavioural fixes - _SQLITE_LOCKS now keyed via WeakKeyDictionary so id-reuse after GC cannot return a stale, loop-bound lock and the cache cannot leak one entry per disposed engine. - safe_add_column compares nullable / server_default against the desired column when the name already exists and emits a warning on drift, surfacing manual-ALTER workarounds instead of silently no-op'ing. - _postgres_lock issues SET LOCAL idle_in_transaction_session_timeout=0 before pg_advisory_lock, so managed Postgres cannot kill the idle lock-holding session mid-upgrade and silently release the advisory lock. - legacy branch now backfills missing baseline tables via a restricted create_all (Base.metadata.create_all scoped to _BASELINE_TABLE_NAMES). Restores pre-#1930 upgraders whose channel_* tables were never provisioned, without pre-empting future create_table revisions for newly-added models. Schema parity - runs.token_usage_by_model gains server_default=text("'{}'") in both the ORM model and the 0001_baseline create_table, matching what 0002 adds via ALTER. create_all and alembic-upgrade paths now produce identical column definitions. - New parity test compares Base.metadata.create_all output against a pure alembic upgrade base->head, asserting column-set, nullable, and server_default agree across all tables (normalized through the same helper safe_add_column's drift check uses). Guards - test_baseline_table_names_constant_matches_0001 pins _BASELINE_TABLE_NAMES to 0001_baseline.upgrade()'s actual output -- the constant cannot drift silently when someone edits 0001. - test_legacy_backfill_skips_non_baseline_tables verifies the restricted backfill does not create a phantom table on Base.metadata, modelling a future revision that would otherwise collide on op.create_table. Doc residuals - Three-branch decision table is now consistent across bootstrap.py top docstring, engine.py comment, test module docstring, and CLAUDE.md. - Stale test anchor in blocking_io/test_persistence_engine_sqlite.py docstring now points at the real file. * fix(style): fix lint error * fix(persistence): close drift detection holes - _check_column_drift compares column type via a family equivalence allowlist ({JSON, JSONB}). Catches the wrong-type workaround `TEXT NOT NULL DEFAULT '{}'` that previously slipped through silently, while keeping Postgres JSON/JSONB dialect reflection quiet. Reflected and desired type are also echoed in every drift warning's payload for operator triage. - Extract _escape_url_for_alembic so bootstrap._alembic_safe_url and scripts/_autogen_revision share the ConfigParser % escape rule instead of duplicating it. - backend/README.md: add `make migrate-rev MSG=...` to Commands and a Schema Migrations section per the repo's README/CLAUDE.md sync policy. - test_base_to_dict.py: scope the test ORM class to an isolated MetaData so the create_all-vs-alembic parity test (added in the previous commit) is not polluted by the phantom table on the full pytest session.	2026-06-24 13:57:12 +08:00