Apply FTS migration 20260207200000_add_fts_search to live Postgres — searchVector columns not GENERATED, no GIN indexes #70

New issue

Open

opened 2026-09-18 23:17:46 +00:00 by Mark · 0 comments

Mark commented

2026-09-18 23:17:46 +00:00

Owner

Summary

Live Postgres (pg-nodedesk) on unstable has the four searchVector tsvector columns (Client, System, Note, WorkLog) but they are NOT declared as GENERATED ALWAYS AS (...) STORED and there are NO GIN indexes. The Prisma migration prisma/migrations/20260207200000_add_fts_search/migration.sql exists and is correct, but it was never applied to this DB because the database was bootstrapped via prisma db push (no _prisma_migrations table) instead of prisma migrate deploy.

Impact

FTS queries (Note.searchVector @@ websearch_to_tsquery(...)) currently cannot return results because:

The searchVector columns are NULL/empty — never auto-populated.
Even if manually populated, no GIN index means searches are sequential scans, not the index-driven FTS performance the migration was designed to provide.

All four tables are empty in dev (pg_stat_user_tables.n_live_tup = 0) so the bug is not yet user-visible, but any seed/import of data would silently produce rows with NULL searchVectors and zero FTS capability.

Reproduction

podman exec -e PGPASSWORD=ci pg-nodedesk psql -U ci -d nodedesk -c "
SELECT table_name, column_name, data_type, is_generated
FROM information_schema.columns
WHERE column_name='searchVector' AND table_schema='public' ORDER BY table_name;"

Returns 4 rows with data_type=tsvector, is_generated=NEVER.

podman exec -e PGPASSWORD=ci pg-nodedesk psql -U ci -d nodedesk -c "
SELECT indexname FROM pg_indexes
WHERE schemaname='public' AND indexname LIKE '%search%';"

Returns 0 rows.

podman exec -e PGPASSWORD=ci pg-nodedesk psql -U ci -d nodedesk -c "
SELECT count(*) FROM \"Note\" WHERE \"searchVector\" @@ websearch_to_tsquery('english', 'hello');"

Will be 0 (no rows) regardless of how many rows exist — but in dev the count is 0 because there are no rows at all.

Proposed fix

Apply the migration's SQL statements manually to the live DB:

-- Replace existing columns with GENERATED ALWAYS equivalents (use ALTER COLUMN DROP EXPRESSION semantics by dropping and recreating)
ALTER TABLE "Client"  DROP COLUMN IF EXISTS "searchVector";
ALTER TABLE "System"  DROP COLUMN IF EXISTS "searchVector";
ALTER TABLE "Note"    DROP COLUMN IF EXISTS "searchVector";
ALTER TABLE "WorkLog" DROP COLUMN IF EXISTS "searchVector";

ALTER TABLE "Client"
  ADD COLUMN "searchVector" tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english'::regconfig, COALESCE(name, '')), 'A')
  ) STORED;
ALTER TABLE "System"
  ADD COLUMN "searchVector" tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english'::regconfig, COALESCE(name, '')), 'A') ||
    setweight(to_tsvector('english'::regconfig, COALESCE(hostname, '')), 'B') ||
    setweight(to_tsvector('english'::regconfig, COALESCE(ip, '')), 'C')
  ) STORED;
ALTER TABLE "Note"
  ADD COLUMN "searchVector" tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english'::regconfig, COALESCE(title, '')), 'A') ||
    setweight(to_tsvector('english'::regconfig, COALESCE(body, '')), 'B')
  ) STORED;
ALTER TABLE "WorkLog"
  ADD COLUMN "searchVector" tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english'::regconfig, COALESCE(summary, '')), 'A') ||
    setweight(to_tsvector('english'::regconfig, COALESCE(details, '')), 'B')
  ) STORED;

CREATE INDEX "Client_search_vector_idx"  ON "Client"  USING GIN("searchVector");
CREATE INDEX "System_search_vector_idx"  ON "System"  USING GIN("searchVector");
CREATE INDEX "Note_search_vector_idx"    ON "Note"    USING GIN("searchVector");
CREATE INDEX "WorkLog_search_vector_idx" ON "WorkLog" USING GIN("searchVector");

This is the EXACT content of prisma/migrations/20260207200000_add_fts_search/migration.sql (UP section). Because the DB is dev-only and tables are empty, dropping and recreating the columns is safe.

Acceptance criteria

All 4 searchVector columns report is_generated=ALWAYS in information_schema.columns.
All 4 GIN indexes exist in pg_indexes.
prisma db push against the live DB still passes (prisma validate already passes; this is prisma db push --accept-data-loss).
After seeding test data, SELECT count(*) FROM "Note" WHERE "searchVector" @@ websearch_to_tsquery('english', 'hello'); returns rows containing "hello" in title or body.
The _prisma_migrations table is backfilled with this migration marked applied, so future prisma migrate deploy does not re-run it.

Mark/nodedesk#43 (parent — closed 2026-09-18T23:16:59Z; resolution noted the DB-level gap as the residual follow-up)
Mark/nodedesk#69 (closed as superseded; the nd-007-fts-search branch's content has been absorbed into main via other PRs)
prisma/migrations/20260207200000_add_fts_search/migration.sql (the migration that should be re-applied)

Labels

bug, ops-task

## Summary Live Postgres (`pg-nodedesk`) on `unstable` has the four `searchVector` tsvector columns (`Client`, `System`, `Note`, `WorkLog`) but they are NOT declared as `GENERATED ALWAYS AS (...) STORED` and there are NO GIN indexes. The Prisma migration `prisma/migrations/20260207200000_add_fts_search/migration.sql` exists and is correct, but it was never applied to this DB because the database was bootstrapped via `prisma db push` (no `_prisma_migrations` table) instead of `prisma migrate deploy`. ## Impact FTS queries (`Note.searchVector @@ websearch_to_tsquery(...)`) currently cannot return results because: 1. The `searchVector` columns are NULL/empty — never auto-populated. 2. Even if manually populated, no GIN index means searches are sequential scans, not the index-driven FTS performance the migration was designed to provide. All four tables are empty in dev (`pg_stat_user_tables.n_live_tup = 0`) so the bug is not yet user-visible, but any seed/import of data would silently produce rows with NULL searchVectors and zero FTS capability. ## Reproduction ```bash podman exec -e PGPASSWORD=ci pg-nodedesk psql -U ci -d nodedesk -c " SELECT table_name, column_name, data_type, is_generated FROM information_schema.columns WHERE column_name='searchVector' AND table_schema='public' ORDER BY table_name;" ``` Returns 4 rows with `data_type=tsvector, is_generated=NEVER`. ```bash podman exec -e PGPASSWORD=ci pg-nodedesk psql -U ci -d nodedesk -c " SELECT indexname FROM pg_indexes WHERE schemaname='public' AND indexname LIKE '%search%';" ``` Returns 0 rows. ```bash podman exec -e PGPASSWORD=ci pg-nodedesk psql -U ci -d nodedesk -c " SELECT count(*) FROM \"Note\" WHERE \"searchVector\" @@ websearch_to_tsquery('english', 'hello');" ``` Will be 0 (no rows) regardless of how many rows exist — but in dev the count is 0 because there are no rows at all. ## Proposed fix Apply the migration's SQL statements manually to the live DB: ```sql -- Replace existing columns with GENERATED ALWAYS equivalents (use ALTER COLUMN DROP EXPRESSION semantics by dropping and recreating) ALTER TABLE "Client" DROP COLUMN IF EXISTS "searchVector"; ALTER TABLE "System" DROP COLUMN IF EXISTS "searchVector"; ALTER TABLE "Note" DROP COLUMN IF EXISTS "searchVector"; ALTER TABLE "WorkLog" DROP COLUMN IF EXISTS "searchVector"; ALTER TABLE "Client" ADD COLUMN "searchVector" tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('english'::regconfig, COALESCE(name, '')), 'A') ) STORED; ALTER TABLE "System" ADD COLUMN "searchVector" tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('english'::regconfig, COALESCE(name, '')), 'A') || setweight(to_tsvector('english'::regconfig, COALESCE(hostname, '')), 'B') || setweight(to_tsvector('english'::regconfig, COALESCE(ip, '')), 'C') ) STORED; ALTER TABLE "Note" ADD COLUMN "searchVector" tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('english'::regconfig, COALESCE(title, '')), 'A') || setweight(to_tsvector('english'::regconfig, COALESCE(body, '')), 'B') ) STORED; ALTER TABLE "WorkLog" ADD COLUMN "searchVector" tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('english'::regconfig, COALESCE(summary, '')), 'A') || setweight(to_tsvector('english'::regconfig, COALESCE(details, '')), 'B') ) STORED; CREATE INDEX "Client_search_vector_idx" ON "Client" USING GIN("searchVector"); CREATE INDEX "System_search_vector_idx" ON "System" USING GIN("searchVector"); CREATE INDEX "Note_search_vector_idx" ON "Note" USING GIN("searchVector"); CREATE INDEX "WorkLog_search_vector_idx" ON "WorkLog" USING GIN("searchVector"); ``` This is the EXACT content of `prisma/migrations/20260207200000_add_fts_search/migration.sql` (UP section). Because the DB is dev-only and tables are empty, dropping and recreating the columns is safe. ## Acceptance criteria - [ ] All 4 `searchVector` columns report `is_generated=ALWAYS` in `information_schema.columns`. - [ ] All 4 GIN indexes exist in `pg_indexes`. - [ ] `prisma db push` against the live DB still passes (`prisma validate` already passes; this is `prisma db push --accept-data-loss`). - [ ] After seeding test data, `SELECT count(*) FROM "Note" WHERE "searchVector" @@ websearch_to_tsquery('english', 'hello');` returns rows containing "hello" in title or body. - [ ] The `_prisma_migrations` table is backfilled with this migration marked applied, so future `prisma migrate deploy` does not re-run it. ## Related - Mark/nodedesk#43 (parent — closed 2026-09-18T23:16:59Z; resolution noted the DB-level gap as the residual follow-up) - Mark/nodedesk#69 (closed as superseded; the `nd-007-fts-search` branch's content has been absorbed into main via other PRs) - `prisma/migrations/20260207200000_add_fts_search/migration.sql` (the migration that should be re-applied) ## Labels bug, ops-task