SPEC-029 — v1 Corpus ETL

ETL plan for migrating v1 SciDEX corpus into the v2 artifact graph.

Source: docs/design/spec-029-v1-corpus-etl.md

SPEC-029: v1-corpus ETL — bringing 17.5K wikis / 2.4K hypotheses / 29.6K papers to v2

Field Value
Status Draft
Owner kris.ganjam@gmail.com
Date 2026-04-29
Pillar Atlas

Work Log

Last updated: 2026-05-15 by minimax:32 (orchestra/task:03d7e76a-spec-029-7-ship-12-pr-v1-v2-etl-cutover)

Status summary (2026-05-15)

PR Scope Status Evidence
29.1 Logical replication v1 → scidex_v1_legacy.* schema NOT IMPLEMENTED — host-level PG config; no code in this repo spec §3.4 only
29.2 scidex_etl_runs + ETL framework helpers SHIPPED ✅ migrations/0023_etl_run_audit.sql (commit 515e77ff), etl/runner.py, etl/cli.py, skill/verbs/etl_runs_list.py
29.3 Worker: etl.papers SHIPPPED ✅ etl/mappers/papers.py (commit f6a319e6/SPEC-044)
29.4 Worker: etl.wiki_pages SHIPPED ✅ etl/mappers/wiki_pages.py (commit f6a319e6/SPEC-044)
29.5 Worker: etl.hypotheses (evidence_for → links[]) SHIPPED ✅ etl/mappers/hypotheses.py + M40 backfill scripts/backfill/hypotheses_from_v1.py (commits cc421cfe, d994ee41)
29.6 Worker: etl.knowledge_edges (710K rows, batched) SHIPPED ✅ etl/mappers/kg_edges.py + M40 backfill scripts/backfill/kg_edges_from_v1.py (commits f6a319e6, b23e6881)
29.7 Worker: etl.analyses_and_debates SHIPPED ✅ etl/mappers/analyses.py, etl/mappers/debate_sessions.py (commit cc421cfe/SPEC-044)
29.8 systemd timer cluster SHIPPED (combined, not per-worker staggered) ⚠️ ops/systemd/scidex-etl-corpus.timer (30-min half-hour tick), ops/systemd/scidex-etl-corpus.service — runs --table all in one process; spec called for staggered per-worker timers
29.9 /admin/etl-status Prism page PARTIAL ⚠️ backend done, Prism page not built scidex.etl.runs.list verb shipped (SPEC-044 commit); no Prism route found in this repo
29.10 Cutover runbook + DNS flip SHIPPED ✅ docs/design/spec-019-cutover-runbook.md, docs/cutover/v1-to-v2-cutover-plan.md, ops/smoke-test.sh
29.11 v1-token migration grant verb NOT IMPLEMENTED ❌ described in spec §7.4; no scidex.v1.token_grant verb found
29.12 Drop replication / archive scidex_v1_legacy NOT IMPLEMENTED — correctly deferred to 90 days post-cutover N/A

Open gaps

  1. PR 29.1 — requires host-level PostgreSQL replication slot config (no code artifact possible in this repo alone; blocked on DBA/host access)

  2. PR 29.8 gap — timer fires every 30 min on the half-hour for ALL scopes; spec called for per-worker staggering. Current design (one combined scidex-etl-corpus.timer + --table all) is operationally simpler; may be intentional simplification per PR 7885de57

  3. PR 29.9 — /admin/etl-status Prism page not built. Backend (scidex.etl.runs.list) is live. Prism page would need to live in the SciDEX-Prism repo (separate from substrate). No [link:prism-pr#N] footer needed from this repo — this gap is in Prism, not substrate

  4. PR 29.11 — v1-token migration grant verb not implemented; depends on agent mapping being locked (per spec §7.4)

KG seed corpus note — 2026-05-16

  • Task 2499045d added the compatibility seeder scripts/seed_kg_ontologies.py on top of the existing M7 tools/kg_seed package. The dry-run path reports 16,000 HGNC/MONDO/GO/HPO entities and 8 curated bootstrap KG edges; live runs write entities to artifacts.artifact_type='kg_entity' and edges to the unified edges table without schema changes.

Already Resolved — 2026-05-15 04:58:00Z

  • PRs 29.3–29.7 fully shipped: All five ETL workers exist in src/scidex_substrate/etl/ — papers, wiki_pages, hypotheses, kg_edges, analyses, debate_sessions. Mappers are in etl/mappers/. Backfills shipped via M40 wave (commits d994ee41, b23e6881, d1258282 etc.)

  • PR 29.2 fully shipped: scidex_etl_runs audit table (migration 0023), ETL runner (etl/runner.py), CLI (etl/cli.py), verb (skill/verbs/etl_runs_list.py)

  • PR 29.10 fully shipped: cutover runbook + DNS flip docs exist (spec-019-cutover-runbook.md, v1-to-v2-cutover-plan.md)

  • PR 29.12 correctly deferred: post-cutover maintenance task; no action needed before cutover

What this task still needs: Host-level PG replication config (29.1) and Prism page (29.9) live in separate repos/systems. v1-token grant (29.11) pending agent mapping lock. This task is effectively a coordination/shipping task at this point — the substantive ETL work was done by SPEC-044 and M40 waves.

Adjacent specs / memories:

  • SPEC-001 — polymorphic substrate; v1 rows must land as substrate-shaped envelopes.

  • project_v1_corpus_inventory_for_v2_parity.md — confirmed v1 inventory: 17.5K wikis / 2.4K hypotheses / 29.6K papers / 710K KG edges / 187K tokens at /exchange.

  • reference_v1_db_access.md — v1 corpus is in PG db scidex; scidex_ro replica DSN already configured for read-through.

  • project_scidex_artifact_content_hash_semantics.md — v1’s content_hash is a dedup key (sha256(title+metadata)), NOT a file hash. v2’s substrate already encodes file integrity in metadata.file_sha256. ETL must NOT collide these semantics.

TL;DR

v2 has clean primitives but no content. v1 has 30K+ artifacts and a live token economy but legacy schema. Until we either migrate or proxy v1’s corpus, v2 looks like an empty demo to anyone who lands on prism.scidex.ai.

This spec evaluates four migration approaches, recommends Option D — PG logical replication for static tables + per-table ETL with re-shaping for high-touch tables, and lays out the phased plan to retire scidex.ai (v1) in favor of prism.scidex.ai (v2 → eventually scidex.ai) over ~6 weeks.

1. Why now

After SPEC-022 / 023 / 024 / 025 / 027 / 028 phase-1 PRs, v2’s substrate has:

  • 80+ verbs across agents / signals / colliders / benchmarks / senate / squads / missions.

  • A polymorphic type system (24 registered types post-PR-#179).

  • Working /senate dashboard with 14 quality gates running hourly.

  • /collider, /benchmark, /missions, /landscape, /squad pages all returning 200.

…but those pages are mostly empty. The substrate is a Ferrari with no fuel. Without v1’s corpus, the user-visible site looks like a polished demo, not a working scientific intelligence platform.

Per the loop directive: “when you are done, you can re-route scidex.ai to the new site.” That re-route is gated on v2 being content-equivalent, not just feature-equivalent.

2. Inventory: what’s in v1 that v2 needs

v1 table rows v2 destination re-shape required?
wiki_pages (content_md, mermaid_diagrams, etc.) ~17,500 substrate wiki_page type (new) No — direct copy + envelope wrap. content_md is a-text.
hypotheses (composite_score, evidence_for, citations_count, …) ~2,400 hypothesis type (existing) Yes — schema columns differ; evidence_for migrates from JSONB array to links[].
papers (DOI, abstract, authors, …) ~29,600 paper type (existing) Light — column rename + drop a few unused fields.
knowledge_edges ~710,000 substrate links table Yes — link type taxonomy must be normalized to v2’s enum.
actors (~30 named personas) ~30 agent type (existing; wave-1 already seeded via PR 161; v1 actors table is now v2 agents) Already done — skip.
analyses, debate_sessions, predictions, signals varies existing v2 types Yes — substantial; defer to phase 2.
/exchange token state (LMSR markets) 187K tokens DEAD — see §6 N/A — not migrated.

Total: ~750K rows across 5+ tables to migrate. The dominant volume is the KG edges (~95% of all rows).

3. Four options evaluated

3.1. Option A — bulk one-shot ETL

Custom Python script reads v1 PG, transforms, writes v2 PG. One command, runs once, takes hours.

pro con
Clean break — v2 owns the data v1 keeps writing during ETL → drift; need re-sync window
Re-shape freely Re-running the script is dangerous (idempotency gymnastics)
No coupling to v1 schema after KG edges are 710K rows — slow without batching

Verdict: wrong for the live transition window (v1 + v2 both running). Right for the final cutover (point-in-time freeze + final sync).

3.2. Option B — HTTP read-through proxy

scidex.v1.search / .list verbs in v2 substrate that fetch from v1’s HTTP API on-demand.

pro con
Zero migration; v1 content visible from v2 immediately Forever-dependent on v1’s HTTP reliability
No DB coupling Latency = v2 + v1 round-trip
Reversible Can’t re-shape data; v2 sees v1’s legacy schema raw

Verdict: rejected. Memory project_scidex_artifact_content_hash_semantics.md documents v1’s content_hash is a dedup-key not a file-hash. A proxy would propagate v1’s schema confusions into v2’s substrate envelopes. Solving the conflation in-flight in a proxy is more code than re-shaping in an ETL.

3.3. Option C — PG logical replication only

Set up logical replication: v1’s scidex DB → v2’s scidex_v2 DB into a scidex_v1_legacy.* schema. v2 builds views on top that expose substrate envelopes.

pro con
Real-time CDC; v2 has fresh data automatically Replication slot disk-bloat risk; v1 must support logical replication (it does, same cluster)
Read-only on v2 side — no risk of double-write conflicts View layer over messy v1 schema = significant SQL — leaks into every query
No bespoke ETL code Every v2 query path must know about the legacy schema

Verdict: rejected as the primary mechanism — too much schema-translation logic in views. But useful as a safety net during the transition window.

3.4. Option D — hybrid: logical replication for raw + per-table ETL with re-shaping (RECOMMENDED)

Phase 1 — replication: enable logical replication of all 5 tables from v1 → v2’s scidex_v1_legacy.* schema. v2 has live read access to v1 rows. NO views over them — they’re a staging area for the ETL workers, not a query path.

Phase 2 — per-table ETL workers: 5 idempotent workers (one per source table), implemented as standard substrate verbs:

  • scidex.v1.etl.wiki_pages

  • scidex.v1.etl.hypotheses

  • scidex.v1.etl.papers

  • scidex.v1.etl.knowledge_edges

  • scidex.v1.etl.analyses_and_debates (phase 2.5)

Each worker:

  1. Reads from scidex_v1_legacy.<table> (the replicated copy).

  2. Transforms rows into substrate envelopes (re-shaped).

  3. UPSERTs into the matching v2 type table (ON CONFLICT (id) DO UPDATE SET ... keyed on a deterministic v2 id derived from v1 id, e.g. v1-wiki-{v1_id}).

  4. Records ETL run metadata in scidex_etl_runs (audit trail).

Workers are idempotent — running twice is a no-op. Cron-driven (systemd timer, mirrors scidex-gate-evaluator.timer):

  • Hot tables (hypotheses, papers): every 15 min.

  • Cold tables (wiki_pages): hourly.

  • KG edges: hourly, with batch size 10K.

Phase 3 — final cutover: when all workers report lag_rows < N, freeze v1 (read-only), run final ETL, flip DNS so scidex.ai → v2 substrate + Prism. v1 stays live at v1.scidex.ai for 90 days.

Phase 4 — replication retirement: after 90 days of zero v1 writes, drop the logical replication slot and the scidex_v1_legacy schema.

pro con
Re-shaping happens in clean Python verbs (testable, debuggable) Up-front scaffolding cost (replication setup + 5 workers)
Idempotent — safe to re-run after schema fixes KG edges worker has 710K rows × hourly = need batched ETL with cursor
Substrate-native: ETL workers are just verbs Replication slot cost on v1 (disk + WAL retention) for the transition window
Cutover is a config flip, not a data move v2 must understand v1’s links[] semantics for the evidence_for re-shape

Verdict: adopted. Best balance of correctness, ops simplicity, and re-shape freedom.

4. ETL worker shape

Every worker is a verb of shape scidex.v1.etl.<table>(batch_size=N, dry_run=False):

@verb(
    name="scidex.v1.etl.hypotheses",
    spec=Spec(
        input_model=EtlIn,
        output_model=EtlOut,
        idempotent=True,
        gated_by="senate_etl_admin",  # SPEC-024 gate
    ),
)
def etl_hypotheses(ctx: Context, body: EtlIn) -> EtlOut:
    with ctx.db.tx() as tx:
        with tx.savepoint("etl_hypotheses"):
            for batch in _stream_v1(tx, "hypotheses", body.batch_size, body.cursor):
                envelopes = [_shape_v1_hypothesis(row) for row in batch]
                _upsert_envelopes(tx, "hypothesis", envelopes)
            _record_run(tx, table="hypotheses", n=len(batch), …)
    return EtlOut(…)

Anti-pattern guards (lifted from SPEC-024 §6):

  • SAVEPOINT-wrapped per-batch — one bad row doesn’t kill the run.

  • Single-txn per batch (no sequential UPDATEs).

  • No singleton state holders.

5. ID strategy

v2 ids derived from v1 ids: <type-prefix>-v1-<v1-id>. e.g.:

  • v1 hypothesis h-1234 → v2 hypothesis hyp-v1-1234.

  • v1 paper p-5678 → v2 paper pap-v1-5678.

  • v1 KG edge (h-1234, supports, p-5678) → v2 link lnk-v1-{hash}.

This makes ETL idempotent (re-running overwrites the same row) and makes ancestry visible at a glance.

After cutover, new v2 artifacts use the standard <type>-<uuid12> pattern. The v1- infix is a permanent provenance marker.

6. What is NOT migrated

  • /exchange tokens (187K) — v1’s LMSR markets are dead (10/10 Hypothesis_contradiction stuck at 0.500; per SPEC-022 §1.1). Token balances are not portable to v2’s economy because v2 uses a different signal-flow accounting (SPEC-018). Existing v1 traders get a one-time migration grant of v2 tokens proportional to their v1 calibration (not balance) — one-time gesture, not a port. Detail in §7.4.

  • v1 user accounts — v2 uses ATProto / oxa.dev (per SPEC-021). v1 username → v2 agent mapping is curated for the ~30 named personas only; anonymous v1 users are not migrated.

  • v1 cron jobs / agent fleet config — v2’s agent worker is a different shape (SPEC-020). No port.

  • v1 admin UI — v2 governance is via Senate proposals (SPEC-024), not admin clicks.

7. Phased plan

PR scope depends-on
29.1 enable logical replication v1 → v2 (scidex_v1_legacy.* schema) host-level config
29.2 scaffolding: scidex_etl_runs table + ETL framework helpers in skill.framework 29.1
29.3 worker: etl.papers (small re-shape) 29.2
29.4 worker: etl.wiki_pages 29.2
29.5 worker: etl.hypotheses (heavy re-shape — evidence_for → links[]) 29.2
29.6 worker: etl.knowledge_edges (volume — batched cursor) 29.5 (links into existing artifacts)
29.7 worker: etl.analyses_and_debates 29.5
29.8 systemd timer cluster (one per worker, staggered minutes) 29.3-29.7
29.9 /admin/etl-status Prism page — surfaces lag + run history 29.2
29.10 cutover runbook + DNS flip — scidex.ai → v2 29.3-29.8 all in green
29.11 v1-token migration grant verb (one-shot, then retired) agent mapping locked
29.12 post-cutover: drop replication, archive scidex_v1_legacy 90 days post-29.10

Estimated 4-6 weeks for 29.1-29.10 (the user-visible cutover); 29.11-29.12 are tail tasks.

8. Operational concerns

  • Replication slot disk bloat: monitor pg_replication_slots.confirmed_flush_lsn lag. If a v2 worker stalls, the v1 cluster’s WAL retains everything since the lag → disk fills. Mitigation: alarm at 5GB lag; runbook in 29.10.

  • Schema drift: v1 might add columns mid-transition. Workers must tolerate unknown columns (extract whitelisted fields, ignore the rest). Each worker’s schema-expected map is explicit code, not auto-discovery.

  • Double-source-of-truth window: phase 2-3 (weeks 1-5), the same content exists in both v1 (live) and v2 (synced). Users could see lag. Banner on /etl-status page communicates “syncing from v1 — last seen 2 minutes ago.”

  • DNS rollback plan: 29.10 cutover keeps v1.scidex.ai live for 90 days. If v2 has a critical regression post-cutover, flip DNS back. Detail in 29.10 runbook.

  • Trust transfer: existing v1 users land on v2 expecting their content. Identity claim flow needs UX (already specced in SPEC-021 ATProto alignment).

9. Why this earns its slot

The substrate work in SPEC-022 / 023 / 024 / 025 / 027 / 028 is what v2 will be. SPEC-029 is what makes v2 populated and what unlocks the cutover. Without it, v2 is a polished demo; with it, v2 is the next phase of scidex.ai.

Anti-sprawl wins built in:

  • ETL workers are just substrate verbs — same auth, same observability, same test pattern as everything else. No bespoke ETL pipeline framework.

  • Cron uses the same systemd-timer pattern (scidex-gate-evaluator.timer precedent from SPEC-024 PR 24.7) — no new scheduling tech.

  • ID strategy makes provenance visible without a side-table — no schema sprawl.

  • Cutover is a DNS flip — no big-bang code rollout.