poimen-memory/tasks/M2.4-pgvector-repo.md

# M2.4 — pgvector repository

| Field | Value |
|---|---|
| Phase | M2 — Projections |
| Size | M — 1–3 days |
| Status | ⬜ Not started |
| Flags | — |
| Spec | inlined below |
| Blocks | M2.3, M2.1 |

## Goal

Write the projection into Postgres idempotently, so rebuild is safe to run at any
time and produces the same rows.

## Facts (inlined — no spec read needed)

```rust
async fn upsert_node(&self, node: &MemoryNode) -> Result<()>;
async fn upsert_vector(&self, sha: &Sha256Hash, kind: VectorKind, embedding: &[f32]) -> Result<()>;
async fn insert_edges(&self, child: &Sha256Hash, parents: &[Sha256Hash]) -> Result<()>;
async fn search(&self, q: &[f32], kind: VectorKind, levels: &[Level],
                project: Scope, k: usize) -> Result<Vec<ScoredNode>>;
async fn lookup_signature(&self, sig_sha: &str) -> Result<Option<SignatureHit>>;
async fn parents_of(&self, sha: &Sha256Hash) -> Result<Vec<MemoryNode>>;
async fn clear_project(&self, project: &ProjectId) -> Result<()>;
```

`upsert_node` is `ON CONFLICT (sha256) DO NOTHING`. Content identity means an
identical node is the same node; re-running rebuild must not duplicate or churn
rows. Same for edges on the composite key.

`search` orders by `embedding <=> $1` — cosine distance, matching the
`vector_cosine_ops` index. Any other operator silently drops to a seq scan.

**`kind` must be a literal predicate in the SQL, not a bind parameter, and not a
filter applied to results.** The indexes are partial (`WHERE kind = 'text'`), and
the planner only uses a partial index when the query's predicate provably matches
it. A `WHERE kind = $2` defeats that and silently degrades to a scan over every
vector of both kinds — the same failure mode as the wrong opclass, and just as
invisible.

**`Scope` is not a `ProjectId`.** Tool-failure lookups federate across projects
because an `ERESOLVE` lesson is not project-specific, while ordinary standing-query
memories stay scoped. `Scope::Project(id)` filters; `Scope::AllProjects` does not
and lets project relevance act as a rank boost later instead of a hard filter.

`lookup_signature` is the exact-match tier: a primary-key hit on
`failure_signature`, no vector involved. It is the cheapest and highest-precision
answer the store can give, so it belongs in the repository rather than being
assembled from a `search` call by a caller who does not know it exists.

Edges are inserted **after** both endpoints exist, or the foreign key rejects
them. Rebuild therefore has two passes: all nodes, then all edges. This is not an
optimisation; a single-pass insert fails on the first forward reference.

Embeddings are generated in batches of ≤32 (M2.1) and are the expensive part of
rebuild — batch across nodes, not per node.

## Steps

1. `PgRepo::connect(url)` with a pool; run migrations on connect.
2. Implement the five methods above.
3. `upsert_many(nodes)` batching embedding calls at 32 and inserting with a
   multi-row statement. Batch across *both* vector kinds — a node with a symptom
   projection contributes two texts to the same batch, not two batches.
4. Three-pass write: nodes, then vectors and signatures, then edges. Vectors and
   signatures carry foreign keys to nodes, so they cannot precede them, and edges
   still need both endpoints present.
5. Separate query builders per `kind` so the literal predicate is guaranteed at
   compile time rather than by convention.
5. `clear_project` deletes nodes for one project; edges cascade.
6. Return `ScoredNode { node, distance, matched_kind }` — keep the raw distance,
   do not convert to a similarity score here. The reranker (M3.2) wants the
   ordering, and a lossy conversion hides ties. `matched_kind` tells the caller
   whether the hit came from the memory text or its symptom projection, which is
   the difference between "this is about your topic" and "this explains your
   error".
7. Exclude superseded nodes by default: `LEFT JOIN memory_supersede` on
   `old_sha`, filter where the join is null. An `include_superseded` flag exists
   for audit, off everywhere else.

## Acceptance

- Upserting the same node twice leaves one row.
- Edges referencing not-yet-inserted parents fail; two-pass write succeeds.
- `search` returns nearest-first and respects the level filter.
- `clear_project` removes only that project.

## Verify

**Harness:** disposable Postgres with the production image; a deterministic fake
embedder (hash → fixed vector) so vector assertions are exact.

**Integration test** — `tests/it_pg_repo.rs`:
1. `a1_upsert_idempotent` — upsert twice, assert `count(*) == 1`.
2. `a2_two_pass_required` — single-pass insert with a forward edge reference
   fails; two-pass succeeds. Proves the ordering constraint is real.
3. `a3_search_orders_by_distance` — insert three known vectors, assert returned
   order matches hand-computed cosine distance.
4. `a4_level_filter` — L0/L1/L2 present; search with `levels=[L1]` returns only
   L1.
5. `a5_project_isolation` — two projects with identical text; search one, assert
   no cross-project results.
6. `a6_clear_project_scoped` — clear one, assert the other is intact and no
   orphan edges remain.
7. `a7_batching` — upsert 100 nodes, assert the embedder saw exactly 4 calls.
8. `a8_parents_of` — walk a two-level graph, assert the returned parents match.

**Command:** `cargo test -p mem-store pg_repo`

**False pass:**
- Using a random embedder. Assertion 3 becomes untestable and is usually deleted,
  which removes the only check that the distance operator matches the index.
- Testing `search` with one project in the database. Assertion 5 is the only one
  that catches a missing `WHERE project = $1`, and that bug leaks another
  project's memory into every answer.

## Traps

- Converting distance to similarity in the repo. It loses precision, and the
  reranker downstream wants candidates in order rather than scores.
- Per-node embedding calls. 412 chunks becomes 412 HTTP round trips where 13
  would do, and rebuild goes from seconds to minutes.

---

Background: [DESIGN.md](../DESIGN.md) — pgvector, retrieval