Mark/nodedesk

Fork 0

ND-007: FTS migration 20260207200000_add_fts_search fails on fresh DB — prisma migrate deploy aborts (P3018/42703) #56

New issue

Closed

opened 2026-08-09 18:49:48 +00:00 by Mark · 2 comments

Mark commented

2026-08-09 18:49:48 +00:00

Owner

Summary

The ND-007 FTS migration prisma/migrations/20260207200000_add_fts_search cannot be applied to a fresh database. npx prisma migrate deploy aborts with P3018 / Postgres error 42703 (column "searchVector" of relation "Client" does not exist). This breaks the documented deploy path — docker-compose.yml runs npx prisma migrate deploy on boot — and any fresh CI database setup.

Found by post-deploy probe of issue #11 (ND-007). Kanban card: t_c6ecfe15.

Reproduction

Start a clean Postgres (e.g. nodedesk-test-pg container) and create an empty scratch DB.

From a checkout of Mark/nodedesk at main, run:

DATABASE_URL=postgresql://... npx prisma migrate deploy

The init, email, and magic-link migrations apply, then 20260207200000_add_fts_search aborts:

Error: P3018 — A migration failed to apply.
Database error code: 42703
ERROR: column "searchVector" of relation "Client" does not exist
routine: ATPrepDropExpression

Actual behavior

Fresh-DB deploys fail at the FTS migration. The docker-compose boot command npx prisma migrate deploy and any fresh CI DB setup are broken.

Expected behavior

The migration applies cleanly on a fresh database (column created, GIN indexes built), as well as on databases that already had the earlier trigger-based columns.

Root cause

The UP section of migration.sql contains unguarded cleanup statements written for a DB that already had the searchVector column from the earlier trigger-based attempt:

ALTER TABLE "Client"  ALTER COLUMN "searchVector" DROP EXPRESSION;
ALTER TABLE "System"  ALTER COLUMN "searchVector" DROP EXPRESSION;
ALTER TABLE "Note"    ALTER COLUMN "searchVector" DROP EXPRESSION;
ALTER TABLE "WorkLog" ALTER COLUMN "searchVector" DROP EXPRESSION;

On a fresh DB the column doesn't exist, so ALTER COLUMN ... DROP EXPRESSION raises 42703. The subsequent DROP COLUMN IF EXISTS "searchVector" lines are correctly guarded; these four lines are not.

Suggested fix

Remove the four ALTER COLUMN "searchVector" DROP EXPRESSION lines (the guarded DROP COLUMN IF EXISTS already covers dropping the column), or
Guard each with a conditional block (DO $$ ... IF EXISTS ... $$).

Then re-verify a clean prisma migrate deploy against a fresh DB.

Secondary observation (performance)

A DB provisioned via prisma db push (as was done for the current #52 work) gets the searchVector columns but no GIN indexes — db push cannot create GIN indexes on Unsupported("tsvector") columns. Such a DB silently runs FTS with sequential scans, defeating the performance point of ND-007. Consider documenting that db push is not a substitute for migrate deploy for this migration, or adding a post-push index step.

References

Kanban card: t_c6ecfe15 (post-deploy probe of Mark/nodedesk#11)
Migration: prisma/migrations/20260207200000_add_fts_search/migration.sql
Related (not duplicate): #43 — ND-007 fix closed but never applied to running deployment

## Summary The ND-007 FTS migration `prisma/migrations/20260207200000_add_fts_search` **cannot be applied to a fresh database**. `npx prisma migrate deploy` aborts with `P3018` / Postgres error `42703` (`column "searchVector" of relation "Client" does not exist`). This breaks the documented deploy path — `docker-compose.yml` runs `npx prisma migrate deploy` on boot — and any fresh CI database setup. Found by post-deploy probe of issue #11 (ND-007). Kanban card: `t_c6ecfe15`. ## Reproduction 1. Start a clean Postgres (e.g. `nodedesk-test-pg` container) and create an empty scratch DB. 2. From a checkout of `Mark/nodedesk` at `main`, run: ```bash DATABASE_URL=postgresql://... npx prisma migrate deploy ``` 3. The `init`, `email`, and `magic-link` migrations apply, then `20260207200000_add_fts_search` aborts: ``` Error: P3018 — A migration failed to apply. Database error code: 42703 ERROR: column "searchVector" of relation "Client" does not exist routine: ATPrepDropExpression ``` ## Actual behavior Fresh-DB deploys fail at the FTS migration. The docker-compose boot command `npx prisma migrate deploy` and any fresh CI DB setup are broken. ## Expected behavior The migration applies cleanly on a fresh database (column created, GIN indexes built), as well as on databases that already had the earlier trigger-based columns. ## Root cause The UP section of `migration.sql` contains **unguarded** cleanup statements written for a DB that already had the `searchVector` column from the earlier trigger-based attempt: ```sql ALTER TABLE "Client" ALTER COLUMN "searchVector" DROP EXPRESSION; ALTER TABLE "System" ALTER COLUMN "searchVector" DROP EXPRESSION; ALTER TABLE "Note" ALTER COLUMN "searchVector" DROP EXPRESSION; ALTER TABLE "WorkLog" ALTER COLUMN "searchVector" DROP EXPRESSION; ``` On a fresh DB the column doesn't exist, so `ALTER COLUMN ... DROP EXPRESSION` raises `42703`. The subsequent `DROP COLUMN IF EXISTS "searchVector"` lines are correctly guarded; these four lines are not. ## Suggested fix - Remove the four `ALTER COLUMN "searchVector" DROP EXPRESSION` lines (the guarded `DROP COLUMN IF EXISTS` already covers dropping the column), or - Guard each with a conditional block (`DO $$ ... IF EXISTS ... $$`). Then re-verify a clean `prisma migrate deploy` against a fresh DB. ## Secondary observation (performance) A DB provisioned via `prisma db push` (as was done for the current #52 work) gets the `searchVector` columns but **no GIN indexes** — `db push` cannot create GIN indexes on `Unsupported("tsvector")` columns. Such a DB silently runs FTS with sequential scans, defeating the performance point of ND-007. Consider documenting that `db push` is not a substitute for `migrate deploy` for this migration, or adding a post-push index step. ## References - Kanban card: `t_c6ecfe15` (post-deploy probe of Mark/nodedesk#11) - Migration: `prisma/migrations/20260207200000_add_fts_search/migration.sql` - Related (not duplicate): #43 — ND-007 fix closed but never applied to running deployment

Mark added the

bug

label

2026-08-09 18:49:48 +00:00

Mark referenced this issue from a commit

2026-08-11 05:17:26 +00:00

fix(#56): FTS migration fresh-DB abort; legacy .gitea workflow SHAs; drop .eslintignore

Mark commented

2026-08-11 05:18:04 +00:00

Author

Owner

Fixed by commit d618eb0 (pushed to main 2026-08-10). The four unguarded ALTER COLUMN ... DROP EXPRESSION cleanup statements were removed — on a fresh DB the searchVector columns do not exist, so they aborted prisma migrate deploy with P3018/42703. The DROP COLUMN IF EXISTS statements that followed already handle both fresh DBs (no-op) and databases carrying the earlier trigger-era columns (verified DROP COLUMN works directly on a generated column). Verified end-to-end: fresh scratch DB + npx prisma migrate deploy → all 6 migrations apply cleanly. This also unblocks the docker-compose boot deploy path and the #43 deployment concern.

Fixed by commit d618eb0 (pushed to main 2026-08-10). The four unguarded `ALTER COLUMN ... DROP EXPRESSION` cleanup statements were removed — on a fresh DB the searchVector columns do not exist, so they aborted `prisma migrate deploy` with P3018/42703. The `DROP COLUMN IF EXISTS` statements that followed already handle both fresh DBs (no-op) and databases carrying the earlier trigger-era columns (verified DROP COLUMN works directly on a generated column). Verified end-to-end: fresh scratch DB + `npx prisma migrate deploy` → all 6 migrations apply cleanly. This also unblocks the docker-compose boot deploy path and the #43 deployment concern.

Mark added

triage-approved

and removed

bug

labels

2026-08-13 00:12:44 +00:00

Mark commented

2026-08-13 07:07:59 +00:00

Author

Owner

Fix landed + verified (fixup lane)

The unguarded ALTER COLUMN ... DROP EXPRESSION statements were removed in commit d618eb0 (fix(#56): FTS migration fresh-DB abort; legacy .gitea workflow SHAs; drop .eslintignore), already on origin/main. The migration now uses only guarded DROP COLUMN IF EXISTS for cleanup.

Verified fresh-DB acceptance test (2026-08-13):

Clean postgres:16 container (nd-test-pg), fresh empty DB scratch56
DATABASE_URL=postgresql://test:test@127.0.0.1:5544/scratch56 npx prisma migrate deploy → exit 0, all 6 migrations applied including 20260207200000_add_fts_search
searchVector tsvector columns present on Client/System/Note/WorkLog; all 4 GIN indexes created

Closing as resolved.

## Fix landed + verified (fixup lane) The unguarded `ALTER COLUMN ... DROP EXPRESSION` statements were removed in commit `d618eb0` (`fix(#56): FTS migration fresh-DB abort; legacy .gitea workflow SHAs; drop .eslintignore`), already on `origin/main`. The migration now uses only guarded `DROP COLUMN IF EXISTS` for cleanup. Verified fresh-DB acceptance test (2026-08-13): - Clean postgres:16 container (`nd-test-pg`), fresh empty DB `scratch56` - `DATABASE_URL=postgresql://test:test@127.0.0.1:5544/scratch56 npx prisma migrate deploy` → exit 0, all 6 migrations applied including `20260207200000_add_fts_search` - `searchVector` tsvector columns present on Client/System/Note/WorkLog; all 4 GIN indexes created Closing as resolved.

Mark

2026-08-13 07:08:04 +00:00