Application de recherche PostgreSQL — Pack de contexte

A content search application that combines PostgreSQL full-text search (tsvector
+ tsquery) with vector similarity search via the pgvector extension. The backend
is a Next.js 15 App Router API. Embeddings are generated by OpenAI's
text-embedding-3-small model and stored as vector(1536) columns. The database
runs on Supabase (hosted Postgres 16) and is accessed via the Postgres.js driver.

Next.js 15 (App Router, Route Handlers for search API)
PostgreSQL 16 with extensions: pgvector, pg_trgm, unaccent
postgres (Postgres.js) driver — NOT pg/node-postgres
OpenAI SDK (text-embedding-3-small, 1536 dimensions)
TypeScript (strict)
Tailwind CSS v4
db-migrate for schema migrations (SQL files, not ORM)

src/
  app/
    api/
      search/route.ts        # Unified search endpoint (FTS + vector)
      embed/route.ts         # Embedding generation + upsert
  lib/
    db.ts                    # Postgres.js client singleton
    search.ts                # ftsSearch(), vectorSearch(), hybridSearch()
    embed.ts                 # OpenAI embedding helper
    schema/                  # TypeScript types mirroring DB tables
  components/
    SearchBar.tsx
    SearchResults.tsx
migrations/
  001_initial.sql
  002_add_pgvector.sql
  003_add_trgm_index.sql
  ...
scripts/
  seed.ts                    # Bulk embed + insert content

- All SQL lives in src/lib/search.ts or in migrations/ — never inline SQL in
  route handlers or components.
- Use tagged template literals with Postgres.js (sql`...`) for all queries.
  Never concatenate user input into SQL strings.
- Full-text search uses a generated tsvector column with a GIN index.
  Update triggers maintain the column automatically — do not compute tsvector
  in application code.
- Vector columns are vector(1536) for text-embedding-3-small. If the model
  changes, the dimension must change too — this requires a migration.
- Hybrid search combines FTS rank and cosine similarity with a weighted sum.
  The weights live in src/lib/search.ts as named constants, not magic numbers.
- Schema changes require a new numbered SQL file in migrations/ and must be
  applied with: `npm run db:migrate` (which calls db-migrate up).
- All migration files are append-only — never edit a migration that has been
  applied to production.
- Index creation uses CONCURRENTLY in production migrations to avoid table locks.

- Do not switch to an ORM (Prisma, Drizzle) without explicit instruction.
  Raw SQL with Postgres.js is intentional for fine-grained query control.
- Do not call the OpenAI API in a request that also queries the DB within a
  transaction — embeddings are generated before the transaction opens.
- Do not use cosine similarity (<->) without a vector index (ivfflat or hnsw).
  Always add the index in the same migration that adds the column.
- Do not store user-supplied text as embeddings without sanitizing PII first.
- Do not change the embedding model without a re-indexing migration that
  updates all existing vector values and the column dimension.
- pg_trgm GIN indexes are used for ILIKE fallback — do not drop them.
- Supabase Row Level Security (RLS) is enabled on all tables. Any new table
  must have RLS enabled and at least one policy added in its migration.

# PostgreSQL Search App
DB: Supabase (Postgres 16) — postgres.js driver (src/lib/db.ts)
Search: FTS (tsvector/tsquery) + vector (pgvector) hybrid (src/lib/search.ts)
Embeddings: OpenAI text-embedding-3-small, 1536 dims (src/lib/embed.ts)
Migrations: db-migrate, SQL files in migrations/ — append-only
RLS: enabled on all tables — new tables need policies
Key indexes: GIN on tsvector column, hnsw on vector column
Do NOT: inline SQL in routes, concatenate user input into SQL, skip RLS