# S04: Article Versioning + Pipeline Tuning Metadata — Research

**Date:** 2026-03-30

## Summary

This slice introduces version tracking for technique pages so the admin can compare pipeline outputs across prompt iterations — the "calibration loop" from spec §8.3. Currently, stage 5 does a slug-based upsert that silently overwrites the previous content whenever the pipeline re-runs. There is no way to compare old vs new output, correlate quality changes with prompt edits, or roll back to a previous version.

The work is a new `technique_page_versions` table (Alembic migration), a hook in stage 5 that snapshots the current page content + pipeline metadata before overwriting, API endpoints to list/retrieve versions for a technique page, and a minimal frontend version history panel on the technique detail page.

The approach is well-scoped: snapshot-on-write in stage 5, not a full event-sourcing system. Each version captures the full page content (body_sections, signal_chains, summary, etc.) plus pipeline metadata (model names, prompt file hashes, timestamps) so the admin can answer "which prompt/model combination produced this version?"

## Recommendation

**Snapshot-on-write versioning with JSONB content storage and pipeline metadata columns.**

- New `technique_page_versions` table with: `id`, `technique_page_id` (FK), `version_number` (integer, auto-incremented per page), `content_snapshot` (JSONB — full page content at that point), `pipeline_metadata` (JSONB — model names, prompt hashes, settings), `created_at`.
- Stage 5 synthesis: before overwriting an existing page, snapshot the current content + pipeline config into a version row. The "current" version is always the live `technique_pages` row; the versions table holds history.
- New API endpoints: `GET /techniques/{slug}/versions` (list with metadata summary), `GET /techniques/{slug}/versions/{version_number}` (full content snapshot).
- Minimal frontend: version history sidebar/dropdown on TechniquePage showing version list with timestamps and model info.

Why JSONB snapshots instead of duplicating all columns: the page content structure (body_sections, signal_chains) is already JSONB — storing the whole page as a single JSONB blob is simpler, forward-compatible (new columns don't require migration of the versions table), and the versions table is write-once/read-rarely.

Why pipeline metadata as JSONB: the metadata schema will evolve as we add more pipeline knobs. JSONB avoids schema churn while still being queryable via PostgreSQL JSON operators.

## Implementation Landscape

### Key Files

- `backend/models.py` — Add `TechniquePageVersion` model with FK to `technique_pages`, version_number, content_snapshot (JSONB), pipeline_metadata (JSONB), created_at
- `alembic/versions/002_technique_page_versions.py` — New migration adding the `technique_page_versions` table
- `backend/schemas.py` — Add `TechniquePageVersionRead`, `TechniquePageVersionList` schemas; extend `TechniquePageDetail` with `version_count: int` and `current_version: int`
- `backend/pipeline/stages.py` — In `stage5_synthesis`, before the existing upsert block, snapshot existing page content + pipeline metadata into a `TechniquePageVersion` row. Add a helper `_capture_pipeline_metadata()` that collects model names, prompt file hashes (SHA-256 of file content), and relevant settings.
- `backend/routers/techniques.py` — Add `GET /{slug}/versions` and `GET /{slug}/versions/{version_number}` endpoints
- `backend/tests/test_public_api.py` — Add tests for version list and version detail endpoints
- `backend/tests/test_pipeline.py` — Add test that re-running stage 5 creates a version row
- `frontend/src/pages/TechniquePage.tsx` — Add version info display (count, link to version history)
- `frontend/src/api/public-client.ts` — Add version list/detail API calls

### Build Order

1. **Model + Migration** (T01) — Add `TechniquePageVersion` to models.py and create Alembic migration. This unblocks everything else. Low risk — straightforward table creation following existing patterns.

2. **Stage 5 versioning hook + pipeline metadata capture** (T02) — Modify `stage5_synthesis` to snapshot before overwrite. Add `_capture_pipeline_metadata()` helper that hashes prompt files and reads model config. This is the riskiest task because it modifies the pipeline's write path. The sync SQLAlchemy session in stage 5 already has the existing page loaded — snapshotting it is a single INSERT before the UPDATE.

3. **API endpoints + schemas** (T03) — Add version list/detail endpoints and Pydantic schemas. Straightforward FastAPI/SQLAlchemy read-only endpoints following the existing pattern in `techniques.py`.

4. **Frontend version display** (T04) — Add version count to technique detail and link/panel for version history. Light frontend work — fetch version list, display in a collapsible section.

5. **Integration tests** (T05) — Test the full flow: stage 5 creates version on re-run, API returns versions, version detail contains correct snapshot. Can be combined with T03 if scope is tight.

### Verification Approach

1. **Migration**: `alembic upgrade head` succeeds on test DB; `technique_page_versions` table exists with correct columns.
2. **Pipeline versioning**: Run stage 5 twice for the same video → `technique_page_versions` has 1 row (snapshot of v1 before v2 overwrote it). The version row contains `content_snapshot` with the original body_sections/summary and `pipeline_metadata` with model names and prompt hashes.
3. **API**: `GET /techniques/{slug}/versions` returns version list sorted by version_number desc. `GET /techniques/{slug}/versions/1` returns the full content snapshot.
4. **Frontend**: `npm run build` passes. Technique page shows version count.
5. **Existing tests**: All existing tests in `test_pipeline.py` and `test_public_api.py` still pass (no regressions).

## Constraints

- Stage 5 uses **sync SQLAlchemy** (psycopg2) because it runs inside Celery. The version snapshot INSERT must use the same sync session — no async.
- Alembic env.py needs both local and Docker sys.path entries (KNOWLEDGE item). The new migration must follow the same pattern as `001_initial.py`.
- The existing slug-based upsert in stage 5 loads the page via `select()` then mutates it in-place. The snapshot must happen AFTER loading but BEFORE mutating, in the same transaction.
- Prompt file hashes must be computed at pipeline runtime (not stored in config) because the files can be edited between runs without restarting the worker.

## Common Pitfalls

- **Snapshot timing in stage 5** — The snapshot must capture the OLD content before the in-place mutation. If the code snapshots after `existing.title = page_data.title` etc., it captures the new content. The safest approach: read the existing row's relevant fields into a dict immediately after the `select()`, before any attribute assignment.
- **Version number race condition** — Two concurrent pipeline runs for different videos could both try to version the same technique page. Use `SELECT MAX(version_number) ... FOR UPDATE` or a simple `COUNT(*) + 1` within the transaction. Given this is a single-admin tool with sequential pipeline runs, a simple `COUNT + 1` is sufficient.
- **JSONB serialization of SQLAlchemy model fields** — `body_sections` and `signal_chains` are already Python dicts/lists (loaded from JSONB columns). They serialize to JSON naturally. But `topic_tags` and `plugins` are `ARRAY(String)` which come back as Python lists — these also serialize fine. No special handling needed.

## Open Risks

- The pipeline metadata concept (prompt hashes, model names) is new and not currently tested. If prompt files are large or change frequently, the hash computation adds trivial overhead but the metadata schema may need iteration. Starting with JSONB gives us room.
- Version comparison UI (side-by-side diff of two versions) is explicitly NOT in scope for this slice — the roadmap says "API returns version list." If the admin wants visual diff, that's a future slice. But the data model should support it (full content snapshots enable client-side diffing).

## Sources

- Spec §8.3 "Prompt tuning" — defines the calibration loop that motivates versioning
- Existing stage 5 upsert pattern in `backend/pipeline/stages.py` lines 561-595
- Existing Alembic migration pattern in `alembic/versions/001_initial.py`
