Internal migrations table is not schema-qualified — breaks when a migration sets search_path

[scott-dn]

Summary

pgroll references its internal bookkeeping table by the unqualified name
migrations in every query. Postgres resolves unqualified names against the
session's search_path, which pgroll never controls. As a result the table's
effective location is non-deterministic, and a user migration that changes
search_path (e.g. to target a table in its own schema like "test"."table")
causes pgroll's own bookkeeping writes to fail or silently drift.

Affected code

All references to the bookkeeping table are unqualified (src/index.ts):

• ensureMigrationTable — CREATE TABLE IF NOT EXISTS migrations(...)
• INSERT INTO migrations(...) (in migrate() and go())
• DELETE FROM migrations WHERE ... (in migrate() and go())
• SELECT version FROM migrations ... (getCurrentVersion / getCurrentVersionWithTx)

Root cause

migrations is unqualified, so it resolves against whatever search_path is at
execution time. pgroll never sets search_path, and worse, the bookkeeping
INSERT/DELETE runs in the same transaction, after the user's migration
file:

await Promise.all([
  tx.file(path.join(this.migrationsDir, file)).execute(),
  tx`INSERT INTO migrations(name, version) VALUES (${file}, ${i} + 1)`
]);

If the user's migration file ran SET search_path TO <schema>, the unqualified
INSERT INTO migrations now resolves to <schema>.migrations instead of where
ensureMigrationTable created it.

Steps to reproduce

1. Start the DB: docker compose up -d

2. Create an up migration that targets a custom schema by switching search_path:

   migrations/0001_init_up.sql

   CREATE SCHEMA IF NOT EXISTS test;
   SET search_path TO test;        -- unqualified names now resolve to `test`
   CREATE TABLE "table" (id serial PRIMARY KEY);

3. Run pgroll up.

Observed

The migration fails and rolls back:

relation "migrations" does not exist

Walking through migrate() inside the single reserved transaction:

• ensureMigrationTable runs first (default search_path) → creates public.migrations ✅
• The migration file runs SET search_path TO test
• The unqualified INSERT INTO migrations now resolves to test.migrations,
  which doesn't exist → error → whole transaction rolls back ❌

Expected

pgroll's bookkeeping table location should be deterministic and independent of
whatever search_path a user migration sets.

Second failure mode (silent state drift)

If test.migrations happens to already exist, there's no error — instead the
INSERT lands in test.migrations while getCurrentVersion() (run on a fresh
connection with the default search_path) reads public.migrations. The two
disagree, so pgroll re-applies or skips migrations incorrectly.

Proposed fix

1. Schema-qualify every reference to the bookkeeping table so it never
   depends on search_path — e.g. "<schema>".migrations, with the schema
   configurable (default public) via constructor arg + a --schema CLI flag.
2. (Optional, defense in depth) Pin pgroll's own search_path on the reserved
   connection before its bookkeeping statements so a user migration's
   SET search_path can't leak into pgroll's logic.

Additional note

Bundling tx.file(...).execute() and the bookkeeping INSERT in a single
Promise.all couples the write to whatever session state (e.g. search_path)
the migration file left behind, and doesn't guarantee the bookkeeping write
happens after the migration succeeds. Worth revisiting alongside this fix.

Environment

• pgroll v0.0.9
• PostgreSQL 18.4
• PostgresJS client