# FTS5 Full-Text Search — Execution Board

**Feature:** Add true full-text search (FTS5) to Recipe Manager  
**Created:** 2026-03-30  
**Status:** Planning → In Progress  
**Orchestrator:** Cleo (main agent)  
**Target Model:** stepfun/step-3.5-flash (where applicable)

---

## 🎯 Goal

Replace the current `LIKE`-based search with a high-performance, relevant FTS5 virtual table that indexes:
- Recipe titles and descriptions
- Ingredient items and notes
- Step instructions
- Tag names (via recipe_tags join)

Provide a single backend search endpoint that:
- Supports phrase queries, prefix searches, AND/OR logic
- Returns ranked results by relevance
- Preserves pagination metadata
- Falls back gracefully if FTS fails

---

## 📦 Deliverables

1. **Schema & Migration**
   - Add `recipes_fts` virtual table (FTS5) with `title`, `description`, `ingredient_item`, `ingredient_notes`, `step_instruction`, `tag_name` columns
   - Create triggers to keep FTS in sync on INSERT/UPDATE/DELETE of recipes, ingredients, steps, tags, recipe_tags
   - Backfill existing data in migration

2. **Backend Search Endpoint**
   - New route: `GET /api/recipes/search`
   - Params: `q` (query string), `page`, `limit`
   - Uses `MATCH` with `bm25()` ranking
   - Returns `{ results, total, page, limit, hasMore }` consistent with existing list endpoint

3. **Frontend Integration**
   - Update search input to call new endpoint instead of filter-based search
   - Show ranked order (highest relevance first)
   - Preserve existing pagination UI

4. **Tests**
   - Unit tests for FTS trigger logic (insert/update/delete sync)
   - Backend integration tests: search relevance, phrase queries, no-results, pagination
   - E2E test: search returns expected recipes by title/ingredient

5. **Migration & Rollback**
   - Ensure `schemaVersion` increment and migration applied
   - Document rollback steps (drop virtual table + triggers)

---

## 🔢 Task Breakdown

| Task | Owner | Est. (hrs) | Status |
|------|-------|------------|--------|
| T1: Design FTS5 virtual table schema + trigger plan | Sub-agent 1 | 1.5 | ⏳ |
| T2: Implement migration (create FTS, triggers, backfill) | Sub-agent 2 | 2 | ⏳ |
| T3: Build `/api/recipes/search` endpoint with ranking | Sub-agent 3 | 2 | ⏳ |
| T4: Write backend unit/integration tests for FTS/sync | Sub-agent 4 | 2 | ⏳ |
| T5: Update frontend to use new endpoint + pagination | Sub-agent 5 | 1.5 | ⏳ |
| T6: E2E verification: search by title, ingredient, tags | Sub-agent 6 | 1 | ⏳ |
| T7: Rollback plan, doc updates, final review | Sub-agent 7 | 1 | ⏳ |

---

## 🧩 Dependencies

- T3 depends on T2 (endpoint requires schema)
- T4 depends on T2 (tests need DB structure)
- T5 depends on T3 (frontend calls endpoint)
- T6 depends on T5
- T7 depends on all

Tasks T1 and T2 can run in parallel (T1 designs, T2 starts once design is signed).

---

## 📐 Acceptance Criteria

- ✅ FTS5 virtual table present and populated with all existing recipes
- ✅ Search returns recipes ranked by BM25 (most relevant first)
- ✅ Phrase queries ("chocolate chip cookies") match correctly
- ✅ Prefix searches ("choc") work
- ✅ Ingredient-only searches return correct recipes
- ✅ Pagination metadata consistent with list endpoint
- ✅ All tests pass (including new FTS tests)
- ✅ Rollback documented and tested (optional)

---

## 🛠️ Technical Notes

- Use `CREATE VIRTUAL TABLE recipes_fts USING fts5(...)`
- Triggers: `AFTER INSERT/UPDATE/DELETE` on recipes, ingredients, steps, tags, recipe_tags
- Use `content=''` and `content_triggers` to auto-sync if desired; but explicit triggers may be simpler
- Migration must be idempotent (check if FTS exists before creating)
- Backend: parameterized query `SELECT ... FROM recipes_fts WHERE recipes_fts MATCH ?`
- Rank: `bm25(recipes_fts)` ascending (lower score = better)
- Join back to `recipes` table to fetch full recipe objects

---

## 🧠 Success Metric

Search accuracy > current `LIKE` filter, no regressions in performance, tests green.

---

**Orchestration Plan:**  
Main agent will spawn seven independent sub-agents (one per task) with clear primitives. Sub-agents will report back with status updates. Main agent will monitor progress and roll up into a final status. Use `stepfun/step-3.5-flash` for all sub-agents where model choice applies (per instruction).