Spec: Diagram content search on the Diagrams page

> **Origin:** distilled from the completed wayfinding map [Map: CVM diagram content search](https://github.com/mattpocock/personal-wiki/issues/129) on the personal-wiki tracker. Every decision below traces to a resolved ticket on that map (linked inline). This spec is the map's destination — the feature is meant to be buildable in one clean session. ## Problem Statement Matt has a growing library of TLDraw diagrams in CVM, browsed as a grid on the Diagrams page. Today he can only find a diagram by its **name** — the grid's filter does an `ILIKE` on `diagrams.name`. But the thing he actually remembers about a diagram is usually a word he *drew inside it* — a label on a box, a note, a frame title — not what he named the file. There is no way to ask "which diagram has the word `boundary` in it?", and because a diagram evolves over time (its canvas is cut into immutable snapshots), the word he remembers may live in an older version he can no longer see from the grid at all. ## Solution Add a **content search** to the Diagrams page: one search box in the header that filters diagram tiles by the **text drawn inside them**, matching across a diagram's **whole snapshot history** *and* its current live canvas. Typing a query turns the grid into a flat, recency-ordered stream of the snapshot versions that matched — each shown as its own thumbnail with the diagram's name as a chip — so Matt sees *the exact version* that contained his word, and clicking it opens the diagram at that version. Search is backed by a proper Postgres full-text index (tsvector + GIN), not a naive substring scan, so it matches whole/stemmed words without the false positives of raw JSON matching. ## User Stories 1. As a diagram author, I want to type a word into a search box on the Diagrams page, so that I can find diagrams by their **content**, not just their name. 2. As a diagram author, I want the same box to also match diagram **names**, so that I don't have to think about whether I'm searching by name or content — one box does both. 3. As a diagram author, I want multi-word queries to match diagrams containing **all** the words, so that adding a word **narrows** the results rather than widening them. 4. As a diagram author, I want the ability to type `or` between words to widen the search, so that I can match either term when I'm not sure which I used. 5. As a diagram author, I want search to match **whole/stemmed words** (so `boundary` finds "boundaries"), so that trivial word-form differences don't hide a result. 6. As a diagram author, I want search to look across a diagram's **entire snapshot history**, so that I can find a word even if it only ever existed in an older version of the canvas. 7. As a diagram author, I want search to also cover the diagram's **current live canvas** (the working head), so that words I've drawn but not yet cut into a snapshot are still findable. 8. As a diagram author, I want each matching **version** shown as its own thumbnail card (not one tile per diagram), so that I can see *which* version contained my word. 9. As a diagram author, I want a **diagram-name chip** on each result card, so that I know which diagram a matched version belongs to. 10. As a diagram author, I want the current-canvas ("Current") match **deduped against snapshots**, so that when the live canvas is identical to a preserved snapshot I don't see the same picture twice. 11. As a diagram author, I want results ordered by the **same recency** the grid already uses, so that search reads as a filtered view of my normal grid rather than a re-sorted list. 12. As a diagram author, I want the results to update **as I type** (with a short debounce), so that I get fast feedback without hammering the server on every keystroke. 13. As a diagram author, I want a clear **empty state** ("No diagrams match …") when nothing matches, so that I can tell the difference between "no results" and "still loading". 14. As a diagram author, I want the box **cleared / empty query** to restore the normal diagram grid, so that search is non-destructive to my default view. 15. As a diagram author, I want a small **snippet of the matched text** on each card, so that I can see *why* a version matched. 16. As a diagram author, when I click a matched **older** version, I want the diagram to open **at that version** (its scene loaded onto the live canvas), so that I can pick up work from exactly the state I found. 17. As a diagram author, when opening an older version replaces my current live canvas, I want my current canvas **preserved automatically first**, so that I never silently lose the work I had going. 18. As a diagram author, I want that auto-preserve to happen **silently** (no confirmation dialog) when I arrive via a search click, so that navigation isn't interrupted by an "Are you sure?" I didn't ask for. 19. As a diagram author, I don't want the auto-preserve to create **duplicate snapshots** when my current canvas already matches an existing snapshot, so that my timeline doesn't fill with redundant saves. 20. As a diagram author, I want clicking the **Current** entry (when it's already the live canvas) to be a **no-op**, so that "opening" the version I'm already on changes nothing. 21. As a diagram author, I want text extraction to cover the shapes that actually carry my words — text shapes, geo-shape labels, notes, arrow labels, and **frame names** — so that searching finds the labels I care about. 22. As a diagram author, I don't want URLs, colors, or internal ids to be searchable, so that my query for a real word doesn't drown in machine-generated false positives. 23. As a maintainer, I want the search index to be **backfilled** over all existing snapshots and diagrams, so that content search works on the library I already have, not just on things created after launch. 24. As a maintainer, I want the index to **stay fresh automatically** as I create snapshots and edit canvases, so that I never have to think about reindexing. 25. As a maintainer, I want this schema change shipped as a **versioned migration** (not an ad-hoc push), so that the DB change is reproducible and recorded. ## Implementation Decisions This feature spans five layers, each locked by a resolved map ticket: **text extraction → schema/index → migration workflow → backfill/keep-fresh → query → UI/interaction.** ### A. Migration workflow: adopt drizzle `generate`/`migrate` — [#132](https://github.com/mattpocock/personal-wiki/issues/132) CVM currently uses `drizzle-kit push` (schema-diff, no versioned files). Before shipping the index, switch to `generate`/`migrate`: - **Baseline the existing DB.** `drizzle-kit generate` a real `0000_*.sql` matching the *current* schema exactly, then **mark it already-applied** by seeding drizzle's bookkeeping — create `drizzle.__drizzle_migrations` (if absent) and insert one row = the `0000` file's sha256 + its `meta/_journal.json` `when` timestamp — so `migrate` never replays the `CREATE TABLE`s against the live DB. Do **not** hand-write an `IF NOT EXISTS` baseline (it desyncs drizzle's snapshot). - **Drift gate:** before cutting `0000`, run `drizzle-kit push` and require it to report **"no changes"** — proof the live DB already matches `schema.ts`. This is `push`'s last honest use. - **Folder + runner:** set `out: "./app/db/migrations"` (colocated with `schema.ts`); add `db:generate` (`drizzle-kit generate`) and `db:migrate` (`drizzle-kit migrate`) scripts in the existing `db:*` style. - **One DB → one command.** There is exactly one (local) database. `db:migrate` targets it directly — no env-swap, no `:local`/`:prod` split, no clone step. Manual `pnpm db:migrate` is the entire "deploy" story. - **Retire push:** delete the `db:push` script; document generate→migrate in the **README** (not CONTEXT.md). No push-blocking hook, no auto-backup — script removal + docs is enough friction for a solo, locally-run tool. - The tsvector schema change (section C) ships as **`0001`** on this new workflow. ### B. Text extraction contract (pure, TS) — [#130](https://github.com/mattpocock/personal-wiki/issues/130) A pure, tldraw-free, deterministic extractor lives at `app/lib/extract-scene-text.ts` (beside `scene-hash.ts`): ```ts extractSceneText(scene: unknown): string // { store, schema } in → joined plaintext out flattenRichText(richText: unknown): string // exported for reuse/tests ``` - **Total-tolerant:** unknown shape type / missing prop / malformed richText → `""` for that shape, **never throws** (the backfill must survive every row). - **What holds text:** `props.richText` (ProseMirror/TipTap doc JSON) on `text`, `geo` (label), `note`, `arrow` (mid-line label); the flat string `frame.props.name`. **Fallback** to `props.text` when `richText` is absent (legacy jsonb from before the `AddRichText` migration). - **Excluded:** `image`/`video` `altText` (out of v1 — cheap to add later, extractor-level, no schema impact); `bookmark`/`embed` URLs; and no-text shapes (`draw`, `line`, `highlight`, `group`). - **richText→plaintext semantics** (mirrors tldraw's own `renderPlaintextFromRichText` without importing tldraw): concatenate `{type:"text"}` leaf `.text` with **no** separator (so `Hello **world**` rejoins to `"Hello world"` and phrase search matches); insert whitespace at **block** boundaries; empty-doc guard; ignore marks/attrs/non-text leaves; collapse whitespace; trim. Shapes joined with a **single space**. - Research asset: [`docs/research/tldraw-5-searchable-text.md`](https://github.com/mattpocock/course-video-manager/blob/62efab9684dfd6519057a7523dcd22d417a269db/docs/research/tldraw-5-searchable-text.md) (CVM). ### C. Index schema: hybrid `search_text` + generated `search_vector` — [#133](https://github.com/mattpocock/personal-wiki/issues/133) The index is a **hybrid**: the app writes a plaintext column; Postgres owns a **generated** tsvector over it. - On **both** `diagram_snapshot` (`scene`) and `diagrams` (`head_scene`) — the corpus is every snapshot **plus** each diagram's live head (#131). Each table gets: - `search_text text` (**nullable**) — app writes `extractSceneText(scene)` (section D). - `search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(search_text, ''))) STORED` — always consistent with `search_text`, config lives in schema (change = migration), GIN-indexed. - **Why hybrid, not alternatives:** a *pure generated column* would need to reimplement the ProseMirror tree-walk in PL/pgSQL (brittle); an *app-populated tsvector directly* would bury the `'english'` config in app code (config change = redeploy + full re-backfill) and throw away the debuggable plaintext. - **Config `'english'` pinned literally** (not `default_text_search_config`) so index and query use the deterministically-same dictionary, matching #131's `websearch_to_tsquery('english', …)`. - Drizzle: a reusable `customType` `tsvector` (mirrors the existing `varcharCollateC` precedent), two columns + one GIN index per table. Generated columns are STORED-only, which is drizzle's `.generatedAlwaysAs` default. - Ships as migration **`0001`** (four `ADD COLUMN`s + two `CREATE INDEX … USING gin`). Adding the STORED column computes it for all existing rows at ALTER time (from `search_text = NULL` → empty vector); the backfill (section D) then fills `search_text` and the vector recomputes automatically. ### D. Backfill + keep-fresh — [#134](https://github.com/mattpocock/personal-wiki/issues/134) `search_vector` is generated from `search_text`, which the **app** must write. Three write sites, all feeding the one `extractSceneText` contract: - **createSnapshot (once, immutable):** add `searchText: extractSceneText(diagram.headScene!)` to the snapshot **insert**. The `preserved`-flip branch and the dedupe early-return **must not** touch `searchText` — a snapshot's `scene` is frozen, so its `search_text` is set once and never recomputed. - **updateDiagramHead (every change):** add `searchText: extractSceneText(headScene)` to the head `.set`, **inside** the existing "hash changed" guard, so it recomputes exactly when the scene changes and is free when unchanged. This is the only head-write path. - **One-off backfill:** a TS script `scripts/backfill-search-text.ts` (run via a new `db:backfill-search-text` package script) — **not** SQL, because extraction is TS. Standalone drizzle connection; `UPDATE … SET search_text = extractSceneText(scene/head_scene)` over every snapshot row and every diagram row. Deterministic ⇒ **idempotent / re-runnable**. Simple sequential loop for the single local DB. `search_vector` recomputes automatically on each write. Chosen a standalone script over a `cvm` CLI subcommand (CLI is out of scope for this feature). ### E. Query & result semantics — [#131](https://github.com/mattpocock/personal-wiki/issues/131) A new query, added at the highest existing seam — the `DiagramOperationsService` (Effect + Drizzle), beside `listDiagrams`: - **Multi-term = AND** via `websearch_to_tsquery('english', input)` (defaults to AND; user can type `or`). One built-in call — safe multi-word handling, no manual `:*` token parsing. - **Whole-word/stemmed, not prefix** — the box filters on *completed words* (tradeoff explicitly accepted; stemming still bridges `boundary`→"boundaries"). - **Result grain = snapshot-grain (NOT a diagram roll-up).** The query returns the **matching snapshot rows** (from `diagram_snapshot`) *and* matching **head** rows (from `diagrams`, surfaced as a "Current" entry) — corpus = every snapshot + each diagram's head. Each match is its own result; the UI renders each as its own thumbnail. - **Ordering:** groups by the grid's existing recency sort (`GREATEST(lastClipPinAt, diagrams.updatedAt)` desc). **No `ts_rank`/relevance rank in v1** (parked as fog; revisit only if the grid feels noisy). Also absorbs the existing `name` `ILIKE` filter so the one box matches name AND content. ### F. Search box UI — [#135](https://github.com/mattpocock/personal-wiki/issues/135) Locked via a live `/prototype` (three variants → converged). Folded into `DiagramSearchView` (`app/features/diagrams/diagram-search/`) on throwaway CVM branch [`wayfinder-135-search-ui-prototype`](https://github.com/mattpocock/course-video-manager/tree/wayfinder-135-search-ui-prototype) (commit `1b16c13ea97f`) — **stubbed backend** (`stub-data.ts`); swap `stubSearch` for the real query (section E) when the backend lands. See the folder's `NOTES.md`. - **One search box inline in the header**, beside the "Diagrams" title. It **absorbs** the existing name filter and searches **name AND content** in a single field. **No separate name input; no scope toggle** (the prototype's Both/Name/Content control was explicitly cut). - **Results = one flat, recency-ordered stream** of snapshot cards (no per-diagram group headers), each carrying a **diagram-name chip** and a **matched-text snippet** ("why did this match"). - **Head↔snapshot dedup:** if the live `head_scene` ("Current") is content-identical to a preserved snapshot, the head is not shown twice. **No "Current/Preserved" corner tags** on cards. - **Interaction:** live-as-you-type, **200ms debounce**, server round-trip against the tsvector/GIN index (the loader wires the debounced query to the service). - **Empty state:** `No diagrams match "<query>".` No query → the resting diagram grid. ### G. Opening a matched version: Restore to Head with silent auto-preserve — [#136](https://github.com/mattpocock/personal-wiki/issues/136) Clicking a matched snapshot opens the diagram *at that version* by loading its scene into `headScene` — a real **Restore to Head** (same effect as the timeline's existing Restore). Because this overwrites the outgoing live canvas: - **Silent auto-preserve first:** the outgoing `headScene` is captured as a **Preserved Snapshot** before the target loads — *silently, no dialog* (it's a navigation entry; an "Are you sure?" on arrival would be awkward). `preserved: true` is **required, not optional** — the timeline only shows Preserved (or clip-pinned) snapshots, so a non-preserved capture would be invisible and the safety net would silently fail. - **No duplicate snapshots:** the capture is deduped on the unique `(diagramId, contentHash)` index. If the outgoing head matches an existing snapshot, no new row; if it matches a *non*-preserved snapshot, `createSnapshot({preserved:true})` **promotes** it to Preserved (one-way, desirable). - **No-op when head already == target** (e.g. clicking the "Current" entry) — skip the whole preserve-and-load. - **Mechanics:** a new **search-entry code path** wires `createSnapshot(diagramId, { preserved: true })` (capture) + `restoreSnapshotToHead()` (load) with the no-op guard. No new persistence primitives. - **Scope guard:** this silent model is specific to the **search-entry** Restore to Head. The **existing timeline Restore button is unchanged** — it keeps its warn-then-destroy `RestoreSnapshotDialog`. Unifying both is out of scope (see below). - **Glossary (CONTEXT.md):** adds **Restore to Head** (loading an older DiagramSnapshot's scene back into `headScene`; *avoid*: Revert/Roll back/Undo) and widens **Preserved Snapshot**'s provenance (now also auto-created when a Restore to Head would overwrite an unpreserved head). (An uncommitted glossary edit already exists on CVM `main` — reconcile with it.) ## Testing Decisions **What makes a good test here:** exercise **external behavior at the highest seam**, not implementation details. CVM's test stack is **Vitest + `@effect/vitest` + PGlite** (in-process WASM Postgres; `createTestDb()` boots + pushes/snapshots the schema, `truncateAllTables` CASCADE per `beforeEach`, services injected via Effect Layers). Every `*.server.ts` has a `*.test.ts` partner. Because PGlite *is* Postgres, tsvector/`to_tsvector`/`websearch_to_tsquery`/GIN and STORED generated columns all work under test — so the query can be tested against the **real index**, not a mock. (Early check: confirm the test-DB bootstrap materializes the generated column + GIN index — if the schema-snapshot path skips them, fall back to `push` for these tables.) **Three seams, in priority order:** 1. **The search query — `DiagramOperationsService` (service Effect seam, highest).** The one seam that matters most: assert query & result semantics end-to-end against a seeded PGlite DB with real `search_text` populated — AND across terms, `or` widening, stemming (`boundary`→"boundaries"), snapshot-grain results (a diagram with N matching snapshots yields N results, not one), head surfaced as a "Current" match, head↔snapshot dedup, recency ordering, and name+content in one query. **Prior art:** the existing `listDiagrams({ nameFilter })` tests in `db-diagram-operations.test.ts` (filter + sort + archived coverage) — mirror them. 2. **The extractor — `extractSceneText` / `flattenRichText` (pure-function seam).** Unit tests over scene-JSON fixtures: richText runs rejoin without spurious separators (`Hello **world**`→`"Hello world"`), block boundaries insert whitespace, frame names included, URLs/ids excluded, `props.text` legacy fallback, and **total-tolerance** (malformed/unknown shape → `""`, never throws). **Prior art:** `scene-hash.ts` + its tests (same pure-lib shape, beside it). 3. **Restore-to-Head-with-auto-preserve — `DiagramOperationsService` (service Effect seam).** Assert the new search-entry path: outgoing head captured as Preserved before target loads; no duplicate when head matches an existing snapshot; non-preserved match promoted to Preserved; no-op when head already == target; target scene lands on head. **Prior art:** `db-diagram-snapshot-operations.test.ts` (createSnapshot dedup/preserve + restoreSnapshotToHead coverage). The **UI** (`DiagramSearchView`) is not the test target — its behavior (debounce, flat stream, empty state) is driven by the loader→service round-trip, which the seam-1 tests already cover; the React layer stays thin. ## Out of Scope - **Relevance ranking / match highlighting** — no `ts_rank`, no in-card highlight in v1. Parked; revisit only if a plain filter proves too noisy on a busy grid. - **`cvm` CLI diagram search** — the CLI doesn't need diagrams; this is app-UI only. - **Central/global search-service integration** — `db-search-operations.server.ts` (the courses/lessons/videos union) is **not** extended to diagrams; this feature is scoped to the Diagrams page. - **Unifying the timeline Restore button onto silent auto-preserve** — the existing timeline Restore keeps its warn-then-destroy `RestoreSnapshotDialog`. Harmonising both Restore-to-Head entry points on the safer silent model is a UX improvement beyond content search. - **`altText` on image/video shapes** — excluded from the v1 corpus (extractor-level; cheap to add later with a re-backfill, no migration). ## Further Notes - **Build order:** (A) migration workflow switch + `0000` baseline → (B) extractor → (C) `0001` tsvector schema/migrate → (D) backfill + keep-fresh wiring → (E) query → (F) UI (swap the stub) → (G) restore-on-click path. B is independent and can land first; C depends on A; D depends on B+C; E depends on C+D; F depends on E; G depends on E+F. - **Rejected fallback (recorded for context):** naive `scene::text ILIKE` — false positives (ids/colors/URLs), richText runs split a phrase so it won't match, and seq-scans big jsonb. Committed to proper tsvector instead. - **Storage facts:** tldraw **5.x**; scenes are jsonb (`diagrams.head_scene` live, `diagram_snapshot.scene` immutable captures cut *from* head via `createSnapshot`, deduped by `contentHash`). Snapshots are **on-demand only** (manual preserve / clip-pin), so many diagrams have few/zero snapshots and live work sits only in `head_scene` — which is exactly why the head is indexed too. - **Prototype branch is not mergeable as-is** — `stub-data.ts` fabricates matches. It's a design artifact; the UI folds in once the real query exists. - **Seams flagged for confirmation:** the three test seams above (service query, pure extractor, service restore-path) are inferred from the locked decisions + CVM's existing test patterns — the ideal count is one, but this feature genuinely spans a pure extractor + a query + an interaction, all converging on the `DiagramOperationsService` seam. Confirm this matches your expectation before the build session commits to it.