> **Plain-language companion:** [v0.47.0.md](v0.47.0.md)

## v0.47.0 — Embedding Pipeline Infrastructure & ANN Maintenance

**Status: Released.** Derived from [plans/ecosystem/PLAN_PGVECTOR.md](plans/ecosystem/PLAN_PGVECTOR.md) §6.2, rescheduled after the assessment-driven hardening arc in [plans/PLAN_OVERALL_ASSESSMENT_8.md](plans/PLAN_OVERALL_ASSESSMENT_8.md).

> **Release Theme**
> v0.47.0 resumes the deferred embedding programme once the roadmap has closed
> the immediate correctness and operational gaps. It adds post-refresh action
> hooks (ANALYZE, REINDEX, drift-based re-clustering), vector-aware monitoring,
> and a pgvector RAG cookbook for production embedding pipelines on PostgreSQL.

> **Hard prerequisite:** v0.38.0-v0.45.0 must complete their hardening exit
> criteria first. The embedding programme is intentionally sequenced after the
> correctness and operator-trust work, not in parallel with it.

---

### Features

| ID | Title | Effort | Priority |
|----|-------|--------|----------|
| VP-1 | `post_refresh_action` ST option (`none` / `analyze` / `reindex` / `reindex_if_drift`) | M | P1 |
| VP-2 | `reindex_drift_threshold` ST option plus drift counter in catalog | M | P1 |
| VP-3 | `pgtrickle.vector_status()` view: embedding lag, ANN age, drift % | S | P1 |
| VP-4 | Cookbook: always-fresh RAG with pg_trickle + pgvector | S | P2 |
| VP-5 | Citus vector-aggregate cases folded into distributed coverage | M | P2 |

**VP-1 — Post-refresh actions.**
Add a `post_refresh_action` option to vector-heavy stream tables so operators can
run `ANALYZE`, `REINDEX`, or `reindex_if_drift` after a refresh commits. The
work runs in a lower-priority queue after the refresh transaction, so it does
not lengthen the critical refresh window or turn ANN maintenance into hidden
refresh latency.

**VP-2 — Drift detection.**
Catalog state tracks `rows_changed_since_last_reindex` and a
`reindex_drift_threshold`. When a vector table changes enough since its last
index rebuild, the post-refresh action can queue a targeted REINDEX instead of
forcing operators to guess when ANN quality has drifted too far.

**VP-3 — Vector status view.**
Add `pgtrickle.vector_status()` to expose embedding lag, last refresh, last
reindex time, rows changed since reindex, drift percentage, and vector-aggregate
usage. This view plugs into the monitoring foundation built in the hardening
arc and gives RAG operators a first-class place to look for ANN maintenance
pressure.

**VP-4 — pgvector RAG cookbook.**
Publish `docs/tutorials/PGVECTOR_RAG_COOKBOOK.md` with copy-paste workflows for
pre-computed embeddings, denormalized corpora, hybrid search, and operational
sizing guidance. Embeddings are generated at the application layer (e.g. via
OpenAI, Cohere, or Ollama APIs) before being stored with pgvector. The key
outcome is that AI engineers can start with a working pattern instead of
reverse-engineering examples from tests.

**VP-5 — Citus vector-aggregate cases folded into distributed coverage.**
The v0.39 Citus rig becomes the place where shard-additive vector-aggregate
behavior is verified. This keeps vector work honest in distributed deployments
without treating Citus as an afterthought.

### Test Coverage

| ID | Title | Effort | Priority |
|----|-------|--------|----------|
| T-VP1 | Drift-based reindex integration test on HNSW stream table | M | P1 |
| T-VP2 | `vector_status()` accuracy and reset behavior | S | P1 |
| T-VP3 | Citus shard-additive vector aggregate verification | M | P2 |

**T-VP1.**
Create a vector stream table with `post_refresh_action = 'reindex_if_drift'`,
change more than the configured threshold, and verify that REINDEX is queued and
completed outside the refresh transaction.

**T-VP2.**
Verify that `pgtrickle.vector_status()` reports lag, drift, and last-reindex
metadata correctly before and after a drift-triggered REINDEX.

**T-VP3.**
Run vector-aggregate cases inside the v0.39 Citus rig so shard-local partial
results still converge to the same answer as a global `avg(vector)` or
`sum(vector)` computation.

### Conflicts & Risks

- **VP-1/VP-2** add queueable post-refresh work. Backlog visibility must be
  clear so operators can see if ANN maintenance is lagging behind the refresh
  pipeline.
- **VP-3** is observational, but large fleets still need scrape-friendly
  monitoring rather than expensive ad hoc queries across huge numbers of stream
  tables.
### Exit Criteria

- [x] VP-1: `post_refresh_action` is stored in the catalog and dequeued correctly after refresh commit
- [x] VP-2: drift threshold logic increments and resets correctly over repeated refresh cycles
- [x] VP-3: `pgtrickle.vector_status()` reports correct lag and drift for vector stream tables
- [x] VP-4: RAG cookbook published with multiple worked examples
- [x] VP-5: vector-aggregate cases pass inside the Citus distributed test rig
- [x] Extension upgrade path tested (`0.46.0 → 0.47.0`)
- [x] `just check-version-sync` passes

---
