De l'invite à la PR : Ajouter la recherche plein texte PostgreSQL

Procédure opérationnelle standard (SOP) pour ajouter la recherche plein texte native PostgreSQL avec tsvector, index GIN, ts_rank et une API de recherche Next.js — aucun service de recherche tiers nécessaire.

La recherche plein texte intégrée de PostgreSQL répond à la plupart des besoins de recherche de produits sans Elasticsearch ou Algolia. Ce guide ajoute une colonne tsvector, un index GIN et une requête de recherche classée derrière une route API Next.js.

1. Exigence

Ajouter la recherche plein texte sur une table posts (colonnes : title, body, tags). La recherche doit renvoyer les résultats classés par pertinence avec des extraits mis en évidence. L’implémentation doit utiliser une colonne tsvector générée (non calculée au moment de la requête) afin que l’index GIN soit utilisé.

2. Première invite

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. Modifications de fichiers attendues

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. Liste de vérification

GENERATED ALWAYS AS ... STORED — la colonne est stockée (non virtuelle), donc l’index GIN peut être utilisé. Confirmez la présence du mot-clé STORED.
websearch_to_tsquery est utilisé (pas to_tsquery) — gère les requêtes multi-mots et les phrases à partir d’une entrée non fiable sans erreurs de syntaxe.
La requête fournie par l’utilisateur est passée comme paramètre SQL, jamais interpolée.
ts_rank ordonne les résultats — pas par ordre alphabétique ou d’insertion.
La longueur de ts_headline est limitée (MaxWords=30) pour éviter de renvoyer des extraits énormes.
Le nom de l’index GIN est explicite — plus facile à supprimer/recréer si nécessaire.
La route API valide une longueur minimale de requête (2 caractères) pour éviter les tsqueries de parcours complet de table comme 'a':*.
Cache-Control: public, max-age=60 est défini sur la réponse — les résultats de recherche peuvent être mis en cache de courte durée.

5. Commandes de test

# 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. Échecs courants

Analyse séquentielle au lieu d’analyse d’index — colonne VIRTUAL (pas STORED). Seules les colonnes générées STORED peuvent être indexées dans PostgreSQL. Confirmez que la migration utilise STORED.
to_tsquery échoue sur une entrée multi-mots — to_tsquery('english', 'quick brown') est une erreur de syntaxe. Utilisez toujours websearch_to_tsquery pour les chaînes fournies par l’utilisateur.
ts_headline renvoie le corps entier — HighlightAll=true défini par erreur, ou MaxWords non défini. Vérifiez la chaîne d’options.
L’index GIN n’est pas utilisé pour les requêtes LIKE — assurez-vous que la clause WHERE utilise l’opérateur @@, pas LIKE ou ILIKE. La recherche plein texte et LIKE sont distincts.
La migration échoue sur les données existantes — la colonne GENERATED se remplit lors de la création ; sur les grandes tables, cela peut être lent. Exécutez dans une transaction avec une vérification de progression.

7. Invite de correction

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. Description de la 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.