Seng Chhunsour

For the “did the RPC update actually commit before the agent disconnected?” problem, a read-only check from a **new connection** can remove the uncertainty about the current definition: ```sql -- Replace this with the function's schema, name, and exact input types. select pg_get_functiondef( to_regprocedure('public.my_rpc(uuid)') ) as current_definition; ``` This handles overloaded functions by specifying the input types. If that exact function does not exist, the result is NULL. Compare the returned definition with the intended migration, and test the RPC's behavior separately. This shows the currently committed definition visible to that connection; it does not reconstruct previous versions or prove which particular request committed. PostgreSQL's [`pg_proc` catalogue](https://www.postgresql.org/docs/17/catalog-pg-proc.html) does not store ordinary per-function `created_at` / `updated_at` timestamps. [`pg_get_functiondef`](https://www.postgresql.org/docs/17/functions-info.html) reconstructs the current definition. A timeline of earlier definitions needs to be recorded before changes occur; it cannot be recovered from the current function definition alone. For future agent changes, a practical workaround is to save each `CREATE OR REPLACE FUNCTION` in a versioned migration and commit that file to Git. That gives you a reviewable diff and a link between the proposed SQL and the recorded deployment. Keep raw SQL execution for inspection, and route schema changes through the migration workflow. Instructions help, but if an agent can still write arbitrary SQL, they are not an enforced access boundary. Supabase's [migration guide](/docs/guides/deployment/database-migrations#how-migration-tracking-works) explains the distinction: Git tracks the SQL files; `supabase_migrations.schema_migrations` tracks applied migrations. `supabase migration list` can compare local and remote records. A raw `execute_sql` change will not gain migration history retroactively merely by checking that table. This is a workaround for verification and future history, rather than an implementation of the proposed Dashboard timeline. *Prepared with AI assistance. The definition query was checked against created, replaced, and missing functions in an isolated PostgreSQL 17 instance; no access to your project was used.*

For the migration runner, **Supavisor session mode on the shared pooler’s port 5432 is the IPv4 alternative to a direct connection**. It supports session-level advisory locks across transactions while the connection remains open. Transaction mode on port 6543 is unsuitable for that dependency. Supabase documents this in [connection modes and session-state limitations](/docs/guides/database/connecting-to-postgres#transaction-mode-limitations). There is also a client-side requirement: reserve one Postgres.js connection and run the lock, migration statements, and unlock through that reserved client. Using the general `sql` pool can select different sessions. [`sql.reserve()`](https://github.com/porsager/postgres#reserving-connections) provides this. If the connection is lost, stop the migration attempt and reconcile what committed before retrying; a session lock does not survive reconnection. For your initial identity check with TLS verification enabled: ```js import postgres from 'postgres' // Copy the Session pooler URL from Dashboard > Connect. // Keep it server-side. Use the actual pooler hostname and role.project-ref user. // Omit URL sslmode parameters; configure TLS explicitly below. const databaseUrl = process.env.DATABASE_URL const host = new URL(databaseUrl).hostname const sql = postgres(databaseUrl, { max: 1, connect_timeout: 15, ssl: { rejectUnauthorized: true, servername: host }, }) try { const connection = await sql.reserve() try { const rows = await connection` select current_user, current_database(), pg_backend_pid() ` console.log(rows) } finally { await connection.release() } } finally { await sql.end() } ``` This uses Node's default trusted CAs and checks the pooler hostname. If a custom CA is necessary, add the CA appropriate to **that endpoint** via the TLS `ca` option. Do not disable verification to hide a certificate error. Postgres.js's `ssl: 'require'` disables certificate verification; it is insufficient for your stated requirement. See its [TLS implementation](https://github.com/porsager/postgres/blob/master/src/connection.js) and [Node TLS options](https://nodejs.org/api/tls.html#tlsconnectoptions-callback). A timeout alone does not establish a TLS failure: preserve the error code, and check DNS, TCP reachability, and the exact Connect-dialog hostname/username first. For the remaining preflight questions: - **Catalog visibility:** inspect `pg_namespace`, `pg_class`, `pg_proc`, `pg_depend`, and `pg_roles`, but do not assume a superuser-equivalent inventory. Supabase's [`postgres` role is not a superuser](/docs/guides/database/postgres/roles-superuser), and protected fields such as role password material are not an ordinary catalog-inspection entitlement. - **`pg_trgm` 1.6:** check your actual instance before pinning a version: ```sql select name, version, installed from pg_available_extension_versions where name = 'pg_trgm'; ``` Supabase [lists `pg_trgm` among its extensions](/docs/guides/database/extensions). If version 1.6 is available and the extension is not already installed, enable it through the Dashboard or a reviewed migration using `CREATE EXTENSION pg_trgm WITH SCHEMA extensions VERSION '1.6';`. `CREATE EXTENSION` does not upgrade an already-installed older version. PostgreSQL documents [`pg_trgm` as a trusted extension](https://www.postgresql.org/docs/17/pgtrgm.html), but your executing role still needs the relevant database privileges. - **Separate runtime role:** Supabase [supports custom Postgres roles](/docs/guides/database/postgres/roles). Give the runtime role only the schema/table/function privileges it needs, and keep application-object ownership and migration privileges separate. Check effective inherited privileges too; a `REVOKE` on the role does not cancel a grant inherited through `PUBLIC` or another role. For a precise hosted-role lifecycle beyond those documented capabilities, maintainers need to confirm supportability. *Prepared with AI assistance. The catalog query and reserved-connection/session-lock behavior were checked on an isolated PostgreSQL 17 instance. The hosted pooler and its certificate were not tested against your project.*