The Postgres column Prisma cannot see

Postgres full-text search is good enough that reaching for a search service is usually premature. You need a tsvector of the searchable text, a GIN index over it, and a query that ranks results. No new infrastructure, no sync job, no second source of truth.

The clean way to build the vector is a generated column, so the database maintains it and it can never drift from the row:

ALTER TABLE "Article" ADD COLUMN "searchVector" tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(excerpt, '')), 'B') ||
    setweight(to_tsvector('english', coalesce("bodyMarkdown", '')), 'C')
  ) STORED;
 
CREATE INDEX "Article_searchVector_idx" ON "Article" USING GIN ("searchVector");

setweight is what makes results feel correct rather than merely relevant: a term in the title outranks the same term buried in the body, so ranking matches what a reader expects.

Where Prisma runs out of vocabulary

Prisma has no syntax for a generated column. The best it can do is acknowledge the column exists and refuse to interpret it:

searchVector Unsupported("tsvector")?
 
@@index([searchVector], type: Gin)

That is enough to keep the client compiling and the index in the schema. It is not enough to stop the drift.

Because Prisma cannot see the GENERATED ALWAYS AS expression, it compares your schema to the database, sees a column whose definition it does not understand, and proposes to normalise it. Every migration you generate from that point forward — regardless of what you were actually changing — arrives carrying a line like this:

ALTER TABLE "Article" ALTER COLUMN "searchVector" DROP DEFAULT;

Apply it and the column stops being generated. It does not error. It does not empty. It simply stops updating, and from then on your search index silently reflects the state of the world at the moment you ran that migration. New articles are unfindable. Edited articles return stale matches.

That is the worst category of bug: no exception, no alert, and a symptom that looks like a ranking problem rather than a schema problem.

Living with it

The rule is mechanical, so treat it mechanically. Generate migrations with --create-only, read the SQL before applying it, and delete that line every time.

Then verify, because the whole failure mode is that nothing complains:

SELECT is_generated FROM information_schema.columns
WHERE table_name = 'Article' AND column_name = 'searchVector';
-- expect: ALWAYS

ALWAYS means the database is still maintaining it. NEVER means you applied the drift and the column is now an ordinary snapshot.

Two habits make this survivable. Apply with migrate deploy rather than migrate dev — deploy applies migration files verbatim and never diffs, so it cannot reintroduce the drift. And write the gotcha into the schema itself, next to the field, where the next person will be looking when it bites them.

Querying it

Because the column is Unsupported, the client cannot read it. Search goes through raw SQL:

const results = await prisma.$queryRaw`
  SELECT id, ts_rank("searchVector", websearch_to_tsquery('english', ${term})) AS rank
  FROM "Article"
  WHERE "searchVector" @@ websearch_to_tsquery('english', ${term})
    AND status = 'PUBLISHED'
  ORDER BY rank DESC
  LIMIT 20
`;

websearch_to_tsquery is the parser to pick. It accepts what people actually type — quoted phrases, or, a leading - to exclude — and it does not raise on malformed input, which to_tsquery very much does.