# Aplicación de Búsqueda PostgreSQL - Paquete de Contexto

> Paquete de contexto copiable para una aplicación de búsqueda de texto completo y vectorial respaldada por PostgreSQL con pgvector, para que su agente de IA escriba código SQL y de migración correcto desde el principio.

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

---

Pegue esto al inicio de una tarea para que el agente entienda el esquema de Postgres,
la estrategia de búsqueda y el flujo de trabajo de migración antes de tocar cualquier código de consulta.

## Antecedentes del Proyecto

```txt
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.
```

## Stack Tecnológico

```txt
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)
```

## Estructura de Directorios

```txt
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
```

## Convenciones de Codificación

```txt
- 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.
```

## Límites de Tareas de IA

```txt
- 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.
```

## llms.txt

```txt
# 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
```