Production learnings: patterns from homelab enrichment registry worth upstreaming

## Background

While building a homelab LLM enrichment pipeline (wiki parse + md-parse over an exchange archive stored in Postgres), I've been running a lightweight hand-rolled version of torno's core concepts. This issue documents what worked well in practice, where the current torno design held up, and a handful of gaps worth closing.

---

## What the homelab schema looks like

Three tables:

```
enrichment_registry — one row per (strategy, version); identified by version_hash
enrichment_runs — one row per execution batch; FK → enrichment_registry
exchange_*_results — per-enrichment feature table; FK → enrichment_runs
```

`EnrichmentDefinition` + `EnrichmentVersion` roughly map to `enrichment_registry`.
`EnrichmentJob` roughly maps to `enrichment_runs`.
`FeatureSet` maps to the per-enrichment result tables.

---

## Findings and suggested changes

### 1. Storage location belongs outside the version hash

`EnrichmentVersion.create()` currently hashes `output_schema.fields` as part of the version ID. We ran into a problem when we wanted to add a `result_table` field (the fully-qualified table that holds feature values for this strategy) — if we embedded it in `output_schema`, changing it would silently create a new version and orphan existing feature rows.

**Decision:** add `result_table` (or equivalent storage pointer) as a first-class field on `EnrichmentDefinition` or `EnrichmentVersion`, *outside* the hash payload. It's a deployment concern, not a versioning concern.

```python
@dataclass
class EnrichmentVersion:
...
result_table: str | None = None # not part of version_hash
```

---

### 2. Auto-deprecation on upsert with explicit-status escape hatch

The `upsert` pattern (hash-based idempotency) is exactly right. In practice we also needed:

- **Default active path**: when registering a new `active` version, auto-deprecate any other `active` rows for the same strategy name so there's always exactly one unambiguous current version.
- **Explicit-status path**: when registering an `experimental` or `dev` variant, skip auto-deprecation so it doesn't disturb the live version.

```python
def upsert_strategy(conn, defn, *, status="active"):
version_hash = compute_version_hash(...)
if status == "active":
# deprecate stale active rows for this strategy name
conn.execute(
"UPDATE enrichment_registry SET status='deprecated' "
"WHERE name=%s AND version_hash!=%s AND status='active'",
(defn["name"], version_hash),
)
conn.execute("INSERT ... ON CONFLICT (version_hash) DO UPDATE SET status=EXCLUDED.status ...")
```

Suggested addition to torno's `FeatureStore.register()` / `publish()` API: accept an optional `status` kwarg with the same semantics.

---

### 3. Status values — add `experimental`

`EnrichmentStatus` currently has DRAFT / PUBLISHED / DEPRECATED. In practice a fourth value is useful:

- `experimental` — registered but not auto-promoted to current; useful for A/B or shadow-mode variants that should coexist with the live version without deprecating it.

```python
class EnrichmentStatus(Enum):
DRAFT = "draft"
ACTIVE = "active" # rename PUBLISHED → ACTIVE for clarity
EXPERIMENTAL = "experimental"
DEPRECATED = "deprecated"
```

---

### 4. Full SHA-256 vs truncated hash

`EnrichmentVersion.create()` truncates to 12 hex chars (`hexdigest()[:12]`). Collision probability is low but non-zero (~1 in 16^12 ≈ 10^-14). Since the hash is used as a primary-key surrogate for idempotent upserts, a full 64-char SHA-256 is safer and the storage cost is trivial.

---

### 5. Per-record version guard before re-processing

When re-running an enrichment after bumping its version, the queue view needs to surface records that have *no result from the current version* specifically — not just any result. Pattern:

```sql
-- eligibility CTE
LEFT JOIN exchange_wiki_parse_results r
ON r.exchange_id = e.id
AND r.run_id IN (
SELECT id FROM enrichment_runs WHERE registry_id = current_registry_id('wiki_parse:single_pass')
)
WHERE r.exchange_id IS NULL -- no current-version result → needs processing
```

And at the task level, a pre-check guard:

```python
if already_has_current_result(conn, exchange_id, registry_id):
continue
```

This is the idempotency guarantee that makes re-runs safe. Worth encoding this as a first-class pattern in torno's worker base class (`base.py`).

---

### 6. `EnrichmentJob` vs `enrichment_runs` — scope field

Our `enrichment_runs` table has a `scope` JSONB column for storing arbitrary run-level metadata (batch filters, eval flags, date ranges). `EnrichmentJob` has `metadata` which serves the same purpose but the naming is ambiguous. Suggest renaming or aliasing to `scope` to make the intent explicit.

---

### 7. Non-destructive versioning — never drop feature rows

Key invariant we enforced: deprecating a strategy row in the registry **never** touches feature value rows. Old results remain queryable via their `run_id` FK. This enables:
- rollback to a prior version by re-activating its registry row
- A/B comparison between versions using the same result table

Worth making this a documented guarantee in torno's README and enforcing it in the `FeatureStore` implementation (i.e., `deprecate()` should only touch the registry, never the feature table).

---

## Summary table

| Pattern | Homelab status | Torno gap |
|---|---|---|
| `result_table` outside version hash | Done (migration 032) | Add as non-hashed field on `EnrichmentVersion` |
| Auto-deprecate on active upsert | Done | Add to `FeatureStore.register()` / `publish()` |
| `experimental` status | Done | Add to `EnrichmentStatus` |
| Full SHA-256 hash | Done | Remove `[:12]` truncation |
| Per-record version guard | Done (flow-level) | Encode in `base.py` worker |
| `scope` on run records | Done | Rename `EnrichmentJob.metadata` → `scope` |
| Non-destructive deprecation guarantee | Enforced by convention | Document + enforce in `FeatureStore` |

Start by reading EnrichmentVersion.create(), EnrichmentStatus, FeatureStore.register()/publish()/deprecate(), and the worker base.py mentioned in the issue. Review the README for the proposed non-destructive versioning guarantee. Done means the maintainers agree on the scope and each selected gap has an implemented or documented outcome without affecting existing feature rows.

Written by the indexing model from the issue text.