# Prompt-to-PR: PostgreSQL-Volltextsuche hinzufügen

> SOP zum Hinzufügen einer nativen PostgreSQL-Volltextsuche mit tsvector, GIN-Index, ts_rank und einer Next.js-Such-API – kein externer Suchdienst erforderlich.

**Type:** Playbook  
**Tools:** Cursor, Claude Code, Codex, Windsurf  
**Stack:** Next.js, PostgreSQL, TypeScript  
**Difficulty:** hard  
**Updated:** 2026-06-08

---

Die integrierte Volltextsuche von PostgreSQL deckt die meisten Produktsuchanforderungen ohne Elasticsearch oder Algolia ab. Dieses Playbook fügt eine `tsvector`-Spalte, einen GIN-Index und eine bewertete Suchabfrage hinter einer Next.js-API-Route hinzu.

## 1. Anforderung

Fügen Sie eine Volltextsuche über eine `posts`-Tabelle hinzu (Spalten: `title`, `body`, `tags`). Die Suche soll Ergebnisse zurückgeben, die nach Relevanz geordnet sind und Ausschnitte hervorheben. Die Implementierung muss eine generierte `tsvector`-Spalte verwenden (nicht zur Abfragezeit berechnet), damit der GIN-Index verwendet wird.

## 2. Erster Prompt

```txt title="First 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. Erwartete Dateiänderungen

```txt
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. Überprüfungsliste

- `GENERATED ALWAYS AS ... STORED` — Die Spalte wird gespeichert (nicht virtuell), sodass der GIN-Index verwendet werden kann. Bestätigen Sie, dass das Schlüsselwort `STORED` vorhanden ist.
- `websearch_to_tsquery` wird verwendet (nicht `to_tsquery`) – verarbeitet mehrwortige und Phrasenabfragen aus nicht vertrauenswürdigen Eingaben ohne Syntaxfehler.
- Die vom Benutzer bereitgestellte Abfrage wird als SQL-Parameter übergeben, niemals interpoliert.
- `ts_rank` ordnet die Ergebnisse – nicht alphabetisch oder nach Einfügereihenfolge.
- Die Länge von `ts_headline` ist begrenzt (MaxWords=30), um zu große Ausschnitte zu vermeiden.
- Der GIN-Indexname ist explizit – einfacher zu löschen/neu zu erstellen, falls nötig.
- Die API-Route validiert die Mindestabfragelänge (2 Zeichen), um Volltabellenscans durch Abfragen wie `'a':*` zu vermeiden.
- `Cache-Control: public, max-age=60` wird in der Antwort gesetzt – Suchergebnisse können kurzzeitig zwischengespeichert werden.

## 5. Testbefehle

```bash
# 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. Häufige Fehler

- **Seq-Scan anstelle von Index-Scan** – `VIRTUAL`-Spalte (nicht `STORED`). Nur `STORED`-generierte Spalten können in PostgreSQL indiziert werden. Bestätigen Sie, dass die Migration `STORED` verwendet.
- **`to_tsquery` wirft bei mehrwortigen Eingaben einen Fehler** – `to_tsquery('english', 'quick brown')` ist ein Syntaxfehler. Verwenden Sie für benutzereingegebene Zeichenfolgen immer `websearch_to_tsquery`.
- **`ts_headline` gibt den gesamten Textkörper zurück** – `HighlightAll=true` versehentlich gesetzt oder `MaxWords` nicht gesetzt. Überprüfen Sie die Optionszeichenfolge.
- **GIN-Index wird nicht für `LIKE`-Abfragen verwendet** – stellen Sie sicher, dass die WHERE-Klausel den `@@`-Operator verwendet, nicht `LIKE` oder `ILIKE`. Volltextsuche und `LIKE` sind getrennt.
- **Migration schlägt bei vorhandenen Daten fehl** – die `GENERATED`-Spalte wird bei der Erstellung befüllt; bei großen Tabellen kann dies langsam sein. Führen Sie in einer Transaktion mit einer Fortschrittsprüfung durch.

## 7. Fehlerbehebungsprompt

```txt title="Fix Prompt"
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. PR-Beschreibung

```md title="PR description"
## 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.
```