Prompt to Add PostgreSQL Full-Text Search

Copy-paste AI prompt to add native PostgreSQL full-text search with tsvector, GIN index, and ranked results to an existing Next.js app.

Use this prompt to add real PostgreSQL full-text search to an existing app — using tsvector, a GIN index, and ts_rank — without the agent reaching for Elasticsearch, Algolia, or a third-party search API.

Main Prompt

You are working in a Next.js App Router project with TypeScript and PostgreSQL (using the
`postgres` npm package, not Prisma or Drizzle).

Task: add full-text search over the `posts` table (columns: id, title, body, created_at).

Database changes:
1. Write a migration file `migrations/0010_add_fts.sql` that:
   - Adds a generated column: `search_vector tsvector GENERATED ALWAYS AS
     (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;`
   - Creates a GIN index: `CREATE INDEX posts_search_idx ON posts USING GIN (search_vector);`
2. Do NOT use `pg_trgm` or LIKE queries — use `@@` with `to_tsquery` or `websearch_to_tsquery`.

Application changes:
- Create `src/lib/search.ts` with a `searchPosts(query: string, limit = 20)` function that:
  - Uses `websearch_to_tsquery('english', $1)` to parse the query safely.
  - Returns rows ordered by `ts_rank(search_vector, query_vector) DESC`.
  - Returns `{ id, title, body_excerpt, rank }` — body_excerpt via `ts_headline`.
  - Uses parameterized queries only — no string interpolation.
- Create a Server Action `src/lib/actions/search.ts` that calls `searchPosts` and returns results.
- Create `src/components/SearchBox.tsx` (Client Component) with a debounced input (300 ms) that
  calls the Server Action via `useTransition` and renders results.

Do not install pg_search, Meilisearch, or any search service. Stop and list files before coding.

Implementation Notes

websearch_to_tsquery is safer than to_tsquery for user input because it tolerates malformed queries (missing operators, special characters) without throwing a PostgreSQL error.
The generated tsvector column is automatically updated on INSERT/UPDATE — no triggers needed.
ts_headline requires the original column text and the query vector; pass both from the SELECT.
The GIN index makes search fast on large tables; without it queries will sequential-scan.

Expected File Changes

migrations/0010_add_fts.sql              (new)
src/lib/search.ts                        (new)
src/lib/actions/search.ts               (new — Server Action)
src/components/SearchBox.tsx            (new — Client Component)

Acceptance Criteria

psql -f migrations/0010_add_fts.sql runs without error on the existing schema.
Searching for a word that appears only in title returns the correct post.
Searching for a phrase with a typo (e.g., “postgress”) returns no results without crashing.
Results are ordered with higher-relevance posts first.

Test Commands

psql "$DATABASE_URL" -f migrations/0010_add_fts.sql
bun run typecheck
bun run dev
# type a search term in SearchBox and verify ranked results appear
psql "$DATABASE_URL" -c "EXPLAIN ANALYZE SELECT * FROM posts WHERE search_vector @@ websearch_to_tsquery('english','test');"
# confirm "Index Scan using posts_search_idx" appears in output

Common AI Mistakes

Using LIKE '%query%' instead of the @@ full-text operator.
Using to_tsquery('english', $1) with raw user input — breaks on queries like "foo bar".
Forgetting the GIN index, leaving the search as a sequential scan.
Calling searchPosts in a Client Component instead of wrapping it in a Server Action.

Fix Prompt

The search is using LIKE or crashing on special characters. Fix in order:
1. Replace any LIKE clause with `search_vector @@ websearch_to_tsquery('english', $1)`.
2. If `to_tsquery` is used directly with user input, replace it with `websearch_to_tsquery`.
3. Confirm the GIN index exists: `CREATE INDEX IF NOT EXISTS posts_search_idx ON posts USING GIN (search_vector);`
Show only the corrected diff. Do not modify unrelated files.