SciDEX — Task: [Senate] Fix migration numbering and cleanup

Fix migration number collisions: 002_add_resource_cost.py vs 002_add_resource_tracking.py, 025_add_backup_log_table.sql vs 025_add_last_evidence_update.py. Ensure migration_history table is consistent. Remove duplicate scripts/ copies of migration files. ## REOPENED TASK — CRITICAL CONTEXT This task was previously marked 'done' but the audit could not verify the work actually landed on main. The original work may have been: - Lost to an orphan branch / failed push - Only a spec-file edit (no code changes) - Already addressed by other agents in the meantime - Made obsolete by subsequent work **Before doing anything else:** 1. **Re-evaluate the task in light of CURRENT main state.** Read the spec and the relevant files on origin/main NOW. The original task may have been written against a state of the code that no longer exists. 2. **Verify the task still advances SciDEX's aims.** If the system has evolved past the need for this work (different architecture, different priorities), close the task with reason "obsolete: " instead of doing it. 3. **Check if it's already done.** Run `git log --grep=''` and read the related commits. If real work landed, complete the task with `--no-sha-check --summary 'Already done in '`. 4. **Make sure your changes don't regress recent functionality.** Many agents have been working on this codebase. Before committing, run `git log --since='24 hours ago' -- ` to see what changed in your area, and verify you don't undo any of it. 5. **Stay scoped.** Only do what this specific task asks for. Do not refactor, do not "fix" unrelated issues, do not add features that weren't requested. Scope creep at this point is regression risk. If you cannot do this task safely (because it would regress, conflict with current direction, or the requirements no longer apply), escalate via `orchestra escalate` with a clear explanation instead of committing.

Completion Notes

Auto-completed by supervisor after successful deploy to main

Git Commits (1)

[Senate] Fix migration number collisions and remove duplicates [task:b350bf85-ce27-421a-97a7-dfabb488e650]2026-04-17

Spec File

Goal

Fix migration number collisions in the migrations/ directory and ensure the migration_history table is consistent. Remove duplicate copies of migration files.

Acceptance Criteria

☐ No two migration files share the same numeric prefix (e.g., 002_.py vs 002_.py)

☐ migration_history table has no duplicate version entries

☐ Duplicate scripts/archive/ copies of migration files are removed or clearly documented

☐ No stale migration files remain that should have been deleted

Approach

Audit migration numbering collisions

- List all numbered migrations in migrations/ (e.g., 002_*)
- Identify any numeric collisions (two files with same prefix)
- Fix by renaming one of the colliding migrations to the next available number

Audit duplicate migration files

- Compare files like migrations/064_add_backup_log_table.sql vs migrations/add_backup_log_table.sql
- Determine which is the authoritative version
- Remove the duplicate/stale copy

Check migration_history consistency

- Query migration_history table for duplicate version entries
- Verify all applied migrations correspond to existing migration files

Clean up scripts/archive/ duplicates

- Check scripts/archive/versioned_migrations/ for migration files
- Remove any that are exact duplicates of files in migrations/

Work Log

2026-04-17 07:30 PT — Slot minimax:63

Investigating migration numbering issues
Found collision candidates: 002_add_resource_tracking.py and migrations/add_resource_tracking.sql (different content, same intent)
Found duplicate: 064_add_backup_log_table.sql and add_backup_log_table.sql
Creating spec file

2026-04-17 08:15 PT — Slot minimax:63

Audit complete: 6 pairs of duplicate numbers found (011, 033, 042, 048, 084, 097)
Renamed collision files to unique numbers:

- 011_idea_sparks.sql → 018_idea_sparks.sql
- 033_crosslink_wiki_hypotheses.py → 019_crosslink_wiki_hypotheses.py
- 042_add_missing_columns_to_challenges_table.py → 020_add_missing_columns_to_challenges_table.py
- 048_add_wiki_pages_kg_node_id_index.sql → 021_add_wiki_pages_kg_node_id_index.sql
- 084_fix_paper_figures_schema.sql → 022_fix_paper_figures_schema.sql
- 097_edges_ci_indexes.py → 023_edges_ci_indexes.py

Removed 5 duplicate unnumbered migration files (already had numbered versions):

- add_backup_log_table.sql
- add_resource_tracking.sql
- add_notebooks_table.py
- add_failure_reason.py
- crosslink_wiki_hypotheses.py

Verified no more duplicate numeric prefixes in migrations/
scripts/archive/versioned_migrations/ only contains crosslink_wiki_hypotheses_v2.py (not a duplicate)
migration_history table: empty but properly schema'd; legacy entries use legacy_* versioning

Payload JSON

{
  "requirements": {
    "coding": 8,
    "safety": 9
  },
  "_reset_note": "This task was reset after a database incident on 2026-04-17.\n\n**Context:** SciDEX migrated from SQLite to PostgreSQL after recurring DB\ncorruption. Some work done during Apr 16-17 may have been lost.\n\n**Before starting work:**\n1. Check if the task's goal is ALREADY satisfied (run the relevant checks)\n2. Check `git log --all --grep=task:YOUR_TASK_ID` for prior commits\n3. If complete, verify and mark done. If partial, continue. If not done, proceed.\n\n**DB change:** SciDEX now uses PostgreSQL. `get_db()` auto-detects via\nSCIDEX_DB_BACKEND=postgres env var.",
  "_reset_at": "2026-04-18T06:29:22.046013+00:00",
  "_reset_from_status": "done"
}

Sibling Tasks in Quest (Code Health) ↗

○[Senate] Recurring code health sweepP94

✓[Senate] Break apart api.py god file into focused modulesP87

✓[Senate] Audit and archive versioned dead codeP85

✓[Senate] Consolidate database connection patternsP84

✓[Senate] Consolidate duplicated utility functionsP82

✓[Senate] Organize root-level file sprawl into packagesP80

✓[Senate] Add linting and formatting infrastructureP78

[Senate] Fix migration numbering and cleanup done coding:8 safety:9