[Artifacts] Add version tracking schema to artifacts table done claude

[Artifacts] Add version tracking schema to artifacts table

Goal

Add first-class version tracking to the artifacts system. Every artifact should support multiple versions with clear lineage, enabling reproducible analyses and auditable reasoning chains. This is the foundational schema change that all other versioning features depend on.

Background

The current artifacts table (PostgreSQL) tracks artifacts with provenance_chain and content_hash but has no concept of versions. An artifact is a single immutable record. To support iterative refinement (e.g., protein designs, model checkpoints, evolving datasets), we need explicit version tracking where each version is its own row linked to its predecessor.

Schema Changes

Add columns to the artifacts table:

ALTER TABLE artifacts ADD COLUMN version_number INTEGER DEFAULT 1;
ALTER TABLE artifacts ADD COLUMN parent_version_id TEXT DEFAULT NULL;
ALTER TABLE artifacts ADD COLUMN version_tag TEXT DEFAULT NULL;
ALTER TABLE artifacts ADD COLUMN changelog TEXT DEFAULT NULL;
ALTER TABLE artifacts ADD COLUMN is_latest BOOLEAN DEFAULT 1;

Add indexes:

CREATE INDEX idx_artifacts_version_lineage ON artifacts(parent_version_id);
CREATE INDEX idx_artifacts_latest ON artifacts(artifact_type, is_latest) WHERE is_latest = 1;
CREATE INDEX idx_artifacts_version_tag ON artifacts(version_tag) WHERE version_tag IS NOT NULL;
CREATE INDEX idx_artifacts_type_entity_latest ON artifacts(artifact_type, entity_ids, is_latest) WHERE is_latest = 1;

Add constraints (enforced in application code):

• parent_version_id must reference an existing artifact id (same artifact_type)
• version_number must be parent.version_number + 1
• Only one artifact per lineage chain may have is_latest = TRUE

Migration Strategy

Create migration file: migrations/003_add_artifact_versioning.py

Follow existing migration pattern (see 002_add_quests.py)

Backfill: set all existing artifacts to version_number=1, is_latest=TRUE, parent_version_id=NULL

Migration must be idempotent (safe to run twice)

Acceptance Criteria

Dependencies

• None (root task — can start immediately)
• Parallel with: a17-19-TYPE0001

Dependents

• a17-20-VAPI0001 (version-aware API)
• a17-23-MODL0001 (model artifact type)
• a17-24-REPR0001 (reproducible analysis chains)
• d16-20-AVER0001 (demo: versioned protein design)

Verification — 2026-04-23 20:20Z

• Evidence: Verified against PostgreSQL scidex DB (dbname=scidex user=scidex_app host=localhost)

- version_number (integer, default=1), parent_version_id (text), version_tag (text), changelog (text), is_latest (integer, default=1) — all 5 columns present
- Existing versioning indexes present: idx_artifacts_version_lineage, idx_artifacts_latest, idx_artifacts_version_tag
- register_artifact() accepts all 5 version params: version_number, parent_version_id, version_tag, changelog, is_latest

• Gap found: The task header’s composite latest-version lookup index on (artifact_type, entity_ids, is_latest) is not present in PostgreSQL, so the task is only partially satisfied on current main.
• Landing commit for current versioning code: 698ed86b2d0b08da8ecfbbd596b07d4846e10306

Work Log

2026-04-24 14:00 PT — minimax:75

• Re-verified all acceptance criteria against live PostgreSQL (2026-04-24)
• All 5 columns present: version_number, parent_version_id, version_tag, changelog, is_latest
• 4 indexes verified: idx_artifacts_version_lineage, idx_artifacts_latest, idx_artifacts_type_entity_latest, idx_artifacts_version_tag
• 47,479/47,518 artifacts backfilled (is_latest=1, version_number=1)
• register_artifact() INSERT includes all 5 version columns
• No new work needed; already resolved in main

2026-04-23 13:35 PT — codex:52

• Re-read task instructions, artifact-governance planning doc, and current spec before changing code.
• Verified live PostgreSQL schema: versioning columns and legacy version indexes exist; register_artifact() already exposes version params.
• Found remaining gap against the assigned task header: missing composite latest-version index on (artifact_type, entity_ids, is_latest).
• Planned narrow fix only: add idempotent PostgreSQL migration for the missing index, update versioning verification test for PostgreSQL, and re-run validation.
• Implemented migrations/111_add_artifacts_type_entity_latest_index.py using CREATE INDEX CONCURRENTLY IF NOT EXISTS to avoid blocking the hot artifacts table.
• Updated tests/test_artifact_versioning.py to be PostgreSQL-native, verify the new composite index, and smoke-test default version fields via register_artifact().
• Ran: python migrations/111_add_artifacts_type_entity_latest_index.py and PYTHONPATH=. python tests/test_artifact_versioning.py → both passed; live counts after migration: 47,494 artifacts / 47,478 at version 1 / 47,463 latest.

Verification — 2026-04-23 20:31Z

• Evidence:

- python migrations/111_add_artifacts_type_entity_latest_index.py → Migration 111 applied: created idx_artifacts_type_entity_latest
- PYTHONPATH=. python tests/test_artifact_versioning.py → passed, including schema, indexes, and a default register_artifact() insert smoke test
- PostgreSQL pg_indexes now includes idx_artifacts_type_entity_latest on (artifact_type, entity_ids, is_latest) with WHERE (is_latest = 1)

• Summary: Repository and live PostgreSQL schema now both include the composite latest-version lookup index required by this task.

2026-04-13 17:45 PT — minimax:53

• Re-evaluating task against current main (466af0899)
• Found that register_artifact() was NOT updated with version params despite prior claim
• Added version params to register_artifact() signature: version_number, parent_version_id, version_tag, changelog, is_latest
• Updated INSERT statement in register_artifact() to include all 5 version columns
• Created tests/test_artifact_versioning.py to verify acceptance criteria
• Verification passed: all 5 columns, 3 indexes, 37,735 artifacts with versioning data
• register_artifact() now accepts optional version params

2026-04-13 00:35 PT — minimax:53

• Re-evaluating task against current main (466af0899)
• Confirmed: all acceptance criteria already met on main
• Migration 081_add_artifact_versioning.py (deployed as migration 40 on main)
• Schema: artifacts table has all 5 columns + 3 indexes (verified against the legacy SQLite DB pre-retirement)

- 37,735 artifacts with versioning populated
- idx_artifacts_version_lineage, idx_artifacts_latest, idx_artifacts_version_tag all present

• artifact_registry.py: versioning functions implemented: create_artifact_version(), create_version(), derive_capsule_version()