Prompt-to-PR: Añadir búsqueda de texto completo en PostgreSQL

Procedimiento operativo estándar para añadir búsqueda de texto completo nativa de PostgreSQL con tsvector, índice GIN, ts_rank y una API de búsqueda de Next.js — sin necesidad de un servicio de búsqueda de terceros.

La búsqueda de texto completo integrada de PostgreSQL maneja la mayoría de las necesidades de búsqueda de productos sin Elasticsearch ni Algolia. Este manual añade una columna tsvector, un índice GIN y una consulta de búsqueda clasificada detrás de una ruta API de Next.js.

1. Requisito

Añadir búsqueda de texto completo sobre una tabla posts (columnas: title, body, tags). La búsqueda debe devolver resultados clasificados por relevancia con resaltados de fragmentos. La implementación debe usar una columna tsvector generada (no calculada al momento de la consulta) para que se utilice el índice GIN.

2. Primer Prompt

Add native PostgreSQL full-text search to the posts table in this project.

Database: PostgreSQL. ORM/query builder: [Drizzle / postgres.js — use whichever
is already in src/db/].

Step 1 — migration:
  Create a migration file (or Drizzle schema change) that:
  a. Adds a generated column:
       search_vector tsvector GENERATED ALWAYS AS (
         setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
         setweight(to_tsvector('english', coalesce(body, '')), 'B') ||
         setweight(to_tsvector('english', coalesce(tags, '')), 'C')
       ) STORED;
  b. Creates a GIN index on search_vector:
       CREATE INDEX posts_search_idx ON posts USING gin(search_vector);

Step 2 — query function:
  Create `src/lib/queries/search.ts` exporting `searchPosts(q: string, limit = 10)`.
  The query must:
  - Convert the user query to a tsquery: `websearch_to_tsquery('english', $1)`.
  - Filter: `search_vector @@ query`.
  - Rank: `ts_rank(search_vector, query) DESC`.
  - Return: id, title, ts_headline('english', body, query,
      'MaxWords=30, MinWords=15, ShortWord=3, HighlightAll=false') AS snippet.
  - Use parameterized query (no string interpolation of the user input).

Step 3 — API route:
  Create `src/app/api/search/route.ts` (GET). Read `q` from URL search params.
  Return 400 if q is empty or shorter than 2 chars. Return JSON array of results.
  Add cache-control: public, max-age=60.

Step 4 — do not touch any UI components.

3. Cambios de archivo esperados

src/db/migrations/<timestamp>_add_search_vector.sql   (new — or Drizzle migration)
src/db/schema.ts                                       (search_vector column if Drizzle)
src/lib/queries/search.ts                              (new — searchPosts function)
src/app/api/search/route.ts                            (new — GET endpoint)

4. Lista de verificación

GENERATED ALWAYS AS ... STORED — la columna se almacena (no es virtual), por lo que se puede usar el índice GIN. Confirma que la palabra clave STORED está presente.
Se usa websearch_to_tsquery (no to_tsquery) — maneja consultas de varias palabras y frases desde entradas no confiables sin errores de sintaxis.
La consulta proporcionada por el usuario se pasa como un parámetro SQL, nunca se interpola.
ts_rank ordena los resultados — no alfabéticamente ni por orden de inserción.
La longitud de ts_headline está limitada (MaxWords=30) para evitar devolver fragmentos enormes.
El nombre del índice GIN es explícito — más fácil de eliminar/volver a crear si es necesario.
La ruta API valida una longitud mínima de consulta (2 caracteres) para evitar tsqueries que escaneen toda la tabla como 'a':*.
Se establece Cache-Control: public, max-age=60 en la respuesta — los resultados de búsqueda se pueden almacenar en caché de corta duración.

5. Comandos de prueba

# Run the migration
npx drizzle-kit migrate
# or: psql $DATABASE_URL < migration.sql

# Verify the generated column and index exist
psql $DATABASE_URL -c "\d posts" | grep search_vector
psql $DATABASE_URL -c "\di posts_search_idx"

# Query performance — confirm index scan, not seq scan
psql $DATABASE_URL -c "
  EXPLAIN ANALYZE
  SELECT id FROM posts
  WHERE search_vector @@ websearch_to_tsquery('english', 'your test query')
  LIMIT 10;
" | grep "Index Scan"

# Test the API endpoint
bun dev &
curl "http://localhost:3000/api/search?q=your+test+query" | jq .

# Confirm empty query returns 400
curl -o /dev/null -w "%{http_code}" "http://localhost:3000/api/search?q="
# Expect: 400

6. Fallos comunes

Escaneo secuencial en lugar de escaneo por índice — columna VIRTUAL (no STORED). Solo las columnas generadas STORED se pueden indexar en PostgreSQL. Confirma que la migración usa STORED.
to_tsquery lanza error con entrada de varias palabras — to_tsquery('english', 'quick brown') es un error de sintaxis. Siempre usa websearch_to_tsquery para cadenas proporcionadas por el usuario.
ts_headline devuelve todo el cuerpo — HighlightAll=true accidentalmente establecido, o MaxWords no establecido. Verifica la cadena de opciones.
El índice GIN no se usa para consultas LIKE — asegúrate de que la cláusula WHERE usa el operador @@, no LIKE o ILIKE. La búsqueda de texto completo y LIKE son independientes.
La migración falla en datos existentes — la columna GENERATED se puebla al crearla; en tablas grandes esto puede ser lento. Ejecuta en una transacción con una verificación de progreso.

7. Prompt de corrección

EXPLAIN ANALYZE shows a sequential scan instead of an index scan on the
posts table. The search_vector column was added as VIRTUAL (or without
the STORED keyword) so PostgreSQL cannot create a GIN index on it.

Fix the migration:
  ALTER TABLE posts DROP COLUMN search_vector;
  ALTER TABLE posts ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
      setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
      setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED;
  CREATE INDEX posts_search_idx ON posts USING gin(search_vector);

Confirm the word STORED appears in the column definition.

8. Descripción del PR

## Feature: PostgreSQL native full-text search on posts

- Generated `tsvector` column (`STORED`) with weighted fields:
  title (A), body (B), tags (C)
- GIN index `posts_search_idx` — index scans confirmed via `EXPLAIN ANALYZE`
- `searchPosts(q, limit)` uses `websearch_to_tsquery` (safe for user input)
  and `ts_rank` for relevance ordering
- `ts_headline` snippets (max 30 words) with matched terms highlighted
- GET `/api/search?q=` — validates query length, returns ranked JSON,
  `Cache-Control: public, max-age=60`

No third-party search dependency added.