Add Persistent Full‑Text Search to Existing App

The route

5 steps to Done

1. 01

   Determine searchable entities and choose engine

   Identify which tables/fields need full‑text search and select a compatible indexing solution.

   Preview prompt + verify gate ▾ Hide ▴

   1. Examine the existing PostgreSQL schema (list of tables and column types). 2. Identify which tables and specific text columns should be searchable (e.g., articles.title, articles.body, users.bio). 3. Choose between PostgreSQL native full‑text search (using tsvector columns and GIN indexes) or a lightweight embedded engine like SQLite FTS5 if the project already uses SQLite for some components. 4. Justify the choice in one sentence referencing performance, ease of migration, and existing stack. 5. Output the final decision in JSON: {"engine":"postgres","tables":[{"name":"articles","columns":["title","body"]},{"name":"users","columns":["bio"]}]}

   • ✓ JSON lists all tables that need search
   • ✓ Each table entry includes the exact column names
   • ✓ Engine is set to "postgres"
   • ✓ Justification sentence is present
   • ✓ Self‑audit shows PASS
2. 02

   Create migration to add tsvector columns and GIN indexes

   Persist searchable data in the database and keep it up‑to‑date.

   Preview prompt + verify gate ▾ Hide ▴

   Based on the JSON from step 1, generate a SQL migration script (or Knex/Sequelize equivalent) that for each table: 1. Adds a new column named `search_vector` of type `tsvector`. 2. Populates `search_vector` for existing rows using `to_tsvector('english', col1 || ' ' || col2 ...)` covering all listed columns. 3. Creates a GIN index on `search_vector`. 4. Adds a BEFORE INSERT OR UPDATE trigger function that recomputes `search_vector` from the listed columns. 5. Ensures the migration is idempotent (checks existence before creating). Output the full migration file content with a clear filename (e.g., `20240901_add_fulltext_search.sql`). Include comments explaining each section.

   • ✓ Migration adds `search_vector` column to every target table
   • ✓ GIN index on `search_vector` exists
   • ✓ Trigger function updates `search_vector` on INSERT/UPDATE
   • ✓ Existing rows are backfilled
   • ✓ Self‑audit shows PASS
3. 03

   Implement backend search endpoint

   Expose a HTTP API that queries the full‑text index and returns matching records.

   Preview prompt + verify gate ▾ Hide ▴

   Create a new Express route `GET /api/search` that: 1. Reads `q` (required), `limit` (default 20), and `offset` (default 0) from the query string. 2. Sanitizes `q` using `pg_escape_literal` or parameterized queries. 3. Constructs a `to_tsquery('english', $1)` statement. 4. Executes a UNION ALL query that searches each indexed table, selects the primary key, a relevance rank (`ts_rank_cd(search_vector, query)`), and any display fields (e.g., article.title, user.username). 5. Orders results by rank descending, applies limit/offset. 6. Returns a JSON array of objects `{type:'article'|'user', id, title/username, snippet, rank}`. 7. Handles errors gracefully with a 400 for missing `q` and 500 for server errors. Provide the full route handler code, including required imports and export for inclusion in the server. Add a comment at the top explaining the endpoint purpose.

   • ✓ Endpoint validates presence of `q`
   • ✓ Uses parameterized query with to_tsquery
   • ✓ Searches all tables defined in step 1
   • ✓ Results include type, id, title/username, snippet, rank
   • ✓ Returns ordered, paginated JSON array
   • ✓ Self‑audit shows PASS
4. 04

   Build front‑end search component

   Give users a live search box that calls the new API and displays results.

   Preview prompt + verify gate ▾ Hide ▴

   Implement a functional React component `FullTextSearch` that: 1. Renders an `<input>` field with placeholder "Search...". 2. Uses `useState` for the query string, results array, loading boolean, and error message. 3. Applies a 300 ms debounce (e.g., using `useEffect` with `setTimeout`) before invoking the API. 4. Calls `GET /api/search?q={encodedQuery}` via `fetch`. 5. Parses JSON response and stores it in `results`. 6. Shows a spinner when `loading` is true. 7. Renders a list of results showing `type`, `title`/`username`, and a short snippet. Highlight matching terms if possible. 8. Handles and displays errors (e.g., network failure or 400). Export the component as default. Include any necessary CSS classes (no external UI library). Ensure the component can be imported and placed anywhere in the existing app. Add a comment at top describing its purpose.

   • ✓ Input field with 300 ms debounce
   • ✓ Fetches `/api/search` with query string
   • ✓ Displays loading spinner
   • ✓ Renders result list with type and title/username
   • ✓ Error handling visible
   • ✓ Self‑audit shows PASS
5. 05

   Verify end‑to‑end functionality and persistency

   Confirm that new records are searchable and that the index updates automatically.

   Preview prompt + verify gate ▾ Hide ▴

   Perform the following manual verification steps and document results: 1. Using the existing app UI or API, create a new Article with title "Quantum Leap" and body containing the unique word "quasidiscrete". 2. Refresh the page containing `FullTextSearch` and type "quasidiscrete". Verify that the article appears in results with correct type and snippet. 3. Edit the same article, changing the body to replace "quasidiscrete" with "hyperbolic". 4. Search for "quasidiscrete" again; ensure the article no longer appears. 5. Search for "hyperbolic"; verify the article now appears. 6. Delete the article. 7. Search for "hyperbolic"; confirm the article is absent. 8. Record a brief note for each step: PASS or FAIL. Submit a summary table of the eight checks. No code needed, just verification notes.

   • ✓ Created record appears when searchable term is entered
   • ✓ Edited record reflects new term and drops old term
   • ✓ Deleted record disappears from results
   • ✓ All eight steps are marked PASS in the table