SPEC-049 — Polymorphic Write-Path Consistency

Validator + WriteEnvelope guarantees across create/update/comment/signal/link verbs.

Source: docs/design/spec-049-polymorphic-write-path-consistency.md

SPEC-049: Polymorphic write-path consistency (artifacts envelope)

Field Value
Status Draft
Owner kris.ganjam@gmail.com
Date 2026-04-30
Pillar Cross-cutting
Target repos: scidex-substrate (helper, tests, CI guard, this doc), SciDEX (v1 worker migration — separate PR).
Master: SPEC-001 — polymorphic substrate.

Adjacent context:

  • docs/audit/polymorphic_fk_gap.md — orphan-row audit that motivated this spec.

  • PR #401 — backfilled 51,284 orphan rows; explicitly punted the “future writes” gap to a follow-up SPEC. This is that follow-up.

  • _insert_artifact_row in src/scidex_substrate/skill/handlers.py — the canonical envelope-writer used by typed_create.

  • scripts/backfill_artifacts_from_polymorphic_tables.py — the catch-up script. Idempotent; usable as a circuit-breaker if a future writer is missed again.

TL;DR

Today, every code path that INSERTs into a polymorphic type-specific table (hypotheses, papers, wiki_pages, experiments, notebooks, analyses, datasets, clinical_trial) MUST also INSERT a matching row into artifacts. The framework’s typed_create does this via _insert_artifact_row. Any writer that goes around typed_create re-creates orphans, breaking ~20 FKs that reference artifacts.id (artifact_comments, artifact_syntheses, chamber_artifacts, latex_exports, usage_cache, etc.).

This spec:

  1. Extracts a thin, reusable ensure_artifact_row helper in src/scidex_substrate/skill/polymorphic_helpers.py. Same SQL as _insert_artifact_row (idempotent INSERT … ON CONFLICT (id) DO NOTHING), but exported and callable from any writer that doesn’t (or can’t) route through typed_create.

  2. Adds a regression CI check (scripts/ci_check_polymorphic_orphans.py) that fails if a polymorphic-type table contains rows with no matching artifacts.id. Skips silently when no PG is reachable, so it doesn’t break local dev.

  3. Calls out the v1-side worker migration (3 origin types: gap_debate, debate_synthesizer, debate_round_mining, contributing 2,151 of the 51,284 orphans) as scope for a parallel PR in the v1 SciDEX repo.

1. Problem statement

Per docs/audit/polymorphic_fk_gap.md (2026-04-30), every polymorphic type had >40% orphans. Worst-case: papers (99.06%), wiki_pages (99.92%), experiments (100%), datasets (100%), clinical_trial (100%). For hypotheses (42.88%), the orphan-creators concentrate on three v1 worker labels:

origin_type orphan count
gap_debate 1048
debate_synthesizer 715
debate_round_mining 388
subtotal 2,151

These are scheduled v1 workers that issue INSERT INTO hypotheses … (or UPDATE … SET origin_type=… against rows that were inserted upstream) without writing the corresponding artifacts envelope.

PR #401 backfilled all 51,284 orphans. Going forward, the gap re-opens the moment a writer that bypasses typed_create runs again. This spec closes the gap structurally.

2. Two fix paths

2.1 Helper migration (chosen)

Extract ensure_artifact_row(conn, *, type, id, title, created_by, content_hash, metadata) into a public skill module. Every writer that doesn’t go through typed_create calls it immediately after the type-specific INSERT. The helper is idempotent (ON CONFLICT (id) DO NOTHING) so duplicate calls are safe.

Pros

  • Failure surface is in Python: a missing helper call shows up in tests and reviews, not as a deferred FK error 4 hours later when a downstream writer hits artifact_comments.

  • No DB-side magic. The control flow is grep-able.

  • Same primitive used by _insert_artifact_row and the backfill script — single source of truth, single audit surface.

  • Works across the substrate / v1 boundary. v1 writers that share the PG can ship a near-clone (or import from substrate via a thin v1 wrapper).

Cons

  • Requires migrating each writer. We have 3 known worker paths and ~2 ETL mappers — not infinite, but not free.

  • Trusts every future writer to call the helper. New writers that forget will re-introduce orphans (same problem as today).

2.2 AFTER INSERT trigger (deferred)

Define a single PL/pgSQL trigger function mirror_polymorphic_to_artifacts(), attach as AFTER INSERT FOR EACH ROW to each type-specific table. Trigger derives (id, type, title) from a per-table mapping and inserts into artifacts ON CONFLICT DO NOTHING.

Pros

  • Catches every writer, including ones we don’t know about.

  • DB-level invariant — survives accidental schema-bypassing writes, e.g. psql ad-hoc INSERTs.

Cons

  • DB-side magic. Hard to grep, hard to debug (a new column rename in any one of 8 tables silently breaks the trigger).

  • Per-table column mapping (id is paper_id for papers, nct_id for clinical_trial, id for the rest) lives in PL/pgSQL string-literals — fragile.

  • A trigger can’t fail gracefully without aborting the original INSERT. We’d need to commit-on-best-effort, which complicates the contract.

  • 8 tables × N columns of mapping > the 3 worker paths we’d otherwise migrate. Higher LOC, higher review burden.

  • Coordinates worse with typed_create — both write the artifact row, but typed_create carries the canonical metadata and content_hash; the trigger has only what’s on the row. If both fire we end up with stale metadata in artifacts unless the trigger uses ON CONFLICT DO NOTHING (which is fine, but means typed_create always wins, so the trigger only helps non-typed_create writers — exactly the same population the helper targets).

Decision: helper-first. Defer the trigger to a future SPEC once we have evidence the helper isn’t enough (e.g., a writer that can’t be source-modified — vendor SDK, external ETL).

3. Helper API

async def ensure_artifact_row(
    conn,
    *,
    artifact_id: str,
    artifact_type: str,
    title: str,
    content_hash: str = "",
    created_by: str = "system",
    metadata: dict | None = None,
) -> None:
    """Idempotently insert a row into `artifacts` to mirror a type-specific row.

    Re-uses the same SQL as `handlers._insert_artifact_row`. Safe to call
    multiple times — `ON CONFLICT (id) DO NOTHING` makes it a no-op if the
    envelope already exists.

    Callers SHOULD pass a non-empty content_hash if the type carries one
    (`hypotheses.content_hash`, `wiki_pages.content_hash`); otherwise the
    backfill script's deterministic placeholder (`sha256(id+title)`) is a
    reasonable fallback. Metadata SHOULD be the same shape `typed_create`
    would have written.

    Tolerant to missing optional `artifacts` columns the way
    `_insert_artifact_row` is — populates only the core column set.
    """

The helper module additionally exports POLYMORPHIC_TYPES, the canonical 8-type set, used by the CI check and any future tooling.

4. Out of scope

  • kg_edges: kg_edges.id is INTEGER; artifacts.id is TEXT. Onboarding kg_edges to the polymorphic surface needs either a schema change (text id) or a dual-id model (envelope id ≠ row id). That’s a separate spec.

  • v1 writer migration: 3 file changes in legacy SciDEX repo shipped as a parallel PR in the v1 SciDEX repo. Not gating; the substrate-side helper + tests + CI guard are independently useful (the ETL mappers in etl/mappers/papers.py and etl/mappers/wiki_pages.py benefit from the helper too).

  • DB-level trigger: deferred per §2.2.

  • Bulk ETL completeness for orphan-rich types (paper, wiki_page): SPEC-044 already deferred this. We close the FK gap, not the data-completeness gap.

5. Migration plan

Phase A — substrate (this spec, single PR)

  1. Author this SPEC doc.

  2. Add src/scidex_substrate/skill/polymorphic_helpers.py with ensure_artifact_row + POLYMORPHIC_TYPES.

  3. Migrate the two ETL mappers to call the helper after their INSERT:

    • src/scidex_substrate/etl/mappers/papers.py

    • src/scidex_substrate/etl/mappers/wiki_pages.py

  4. Add unit tests for the helper covering all 8 polymorphic types.

  5. Add scripts/ci_check_polymorphic_orphans.py and call it from scripts/ci.sh (DB-required → skipped when SCIDEX_TEST_DB unset, same convention as the service-test stanza).

Phase B — v1 worker migration (separate PR in SciDEX repo)

  1. scripts/run_pending_debates.py — INSERTs origin_type='debate_synthesizer'. Add envelope-row write right after the INSERT.

  2. scidex/agora/debate_hypothesis_miner.py — INSERTs origin_type='debate_round_mining'. Same fix.

  3. scidex/agora/run_hypothesis_debates.py — UPDATEs origin_type='gap_debate' on existing rows. The orphan source is upstream of this UPDATE; identify and fix the upstream INSERT, OR (cheaper) call the envelope-write at this UPDATE point as belt-and-suspenders.

Phase C — trigger (future SPEC, deferred)

Open if the helper-migration approach proves insufficient (e.g., re-emergent orphans from an unknown writer in CI).

6. CI guard

scripts/ci_check_polymorphic_orphans.py:

For each (type, table, id_col) in POLYMORPHIC_TYPES:
  count = SELECT COUNT(*) FROM <table> t
          WHERE NOT EXISTS (SELECT 1 FROM artifacts a WHERE a.id = t.<id_col>)
  if count > 0: print + accumulate

if accumulated_total > 0: exit 1
else: exit 0

(skip cleanly if no DSN reachable — same convention as service-test stanza)

Wired into scripts/ci.sh in the SCIDEX_TEST_DB block. Failing CI signals “a writer landed that doesn’t use the helper” — the regression we’re guarding against.

7. Acceptance criteria

  • polymorphic_helpers.ensure_artifact_row ships and is importable.

  • Unit tests cover all 8 polymorphic types and the idempotency property.

  • ETL mappers (papers.py, wiki_pages.py) call the helper after their INSERT.

  • scripts/ci_check_polymorphic_orphans.py runs against the live scidex_v2 and reports 0 orphans (post-PR-401 baseline).

  • scripts/ci.sh invokes the orphan-check (skipped without DSN).

  • v1 worker migration tracked as a separate PR (out of scope here).

8. References

  • docs/audit/polymorphic_fk_gap.md — orphan numbers, FK referrer matrix.

  • src/scidex_substrate/skill/handlers.py:64 — _insert_artifact_row (private, in-handlers).

  • scripts/backfill_artifacts_from_polymorphic_tables.py — catch-up tool; same SQL, full per-type metadata mappings.

  • v1 writers: legacy SciDEX scripts/run_pending_debates.py, legacy SciDEX scidex/agora/debate_hypothesis_miner.py, legacy SciDEX scidex/agora/run_hypothesis_debates.py.