Shared SQL helpers for the versioned Postgres migration chain.
Centralizes the prefix-sensitive patterns that individual version modules used to hand-roll (and get subtly wrong in independent ways):
qualify_table/2— schema-qualified table reference for raw SQL in NEW migration code (existing versions keep their local helpers; migrating them is opportunistic). Index names must stay bare onCREATE INDEX(Postgres rejectsCREATE INDEX schema.name); onlyDROP INDEX schema.nameaccepts a qualified name.validate_prefix!/1— rejects prefixes that can't be interpolated into SQL safely. Called at theup/downentry points and by the prefix-resolving tooling (PrefixConfig.resolve_prefix/1,Install.Common).ensure_extension!/1— privilege-aware replacement for a bareCREATE EXTENSION IF NOT EXISTS. Postgres checks the CREATE privilege before the IF-NOT-EXISTS short-circuit, so the bare statement fails for low-privilege roles even when the extension is already installed. This helper checkspg_extensionfirst and only attempts creation when the extension is genuinely missing.ensure_uuid_v7_function/1— createsuuid_generate_v7()inside the install's schema (never whereversearch_pathhappens to point, which pollutespublicand fails outright on PG15+ wherepublicisn't world-writable). Unlikeensure_extension!/1, this one does NOT pre-check existence — it queuesCREATE OR REPLACE FUNCTIONfor every install whose role owns (or could own) the function. That is deliberate, not a missed optimization: anunless already existsguard here (the original shape) meant a function created before some later fix to this same body (e.g. the pgcrypto schema-qualification change) stayed on the OLD body forever —CREATE OR REPLACEnever got a chance to run because the guard always saw "exists" and skipped it, and there was no other path back to a correct body short of a manualDROP.CREATE OR REPLACE FUNCTIONon an unchanged signature is additive and idempotent (no dependent objects break, nothing is dropped), so the guard bought nothing but a stuck body — removing it is what letsPhoenixKit.Migrations.Repairself-heal a drifted body via this same helper instead of reporting a permanent, un-fixable finding. The one case still checked first is an existing function owned by another role:CREATE OR REPLACErequires ownership, and in migration context the statement is only QUEUED, so that failure arrives at flush time where norescuehere can reach it and the migration aborts. That topology is documented and supported (PhoenixKit.Migration's moduledoc tells a DBA to pre-create the function), so it is excluded before the statement is queued.
Functions without a repo argument run in Ecto.Migration context
(immediate existence checks via repo().query/3, DDL queued via
execute/1). The repo-taking variants are for runtime callers outside
a migration context, e.g. mix phoenix_kit.repair.
Summary
Functions
Ensures a Postgres extension is available, in migration context.
Runtime variant of ensure_extension!/1 for callers outside migration
context (statements run immediately on repo).
Ensures uuid_generate_v7() exists in the install's schema, in
migration context.
Runtime variant of ensure_uuid_v7_function/1 (statements run
immediately on repo; raises on database errors via repo.query!/3).
Schema-qualified pgcrypto function reference for SQL interpolation in
migration context, e.g. "#{Helpers.pgcrypto_call("digest")}(...)".
Whether the prefix denotes the default public schema (nil counts).
Schema-qualified table reference for raw SQL interpolation.
Schema-qualified uuid_generate_v7() call for SQL interpolation.
Validates a schema prefix before it is interpolated into SQL.
Functions
@spec ensure_extension!(String.t()) :: :ok
Ensures a Postgres extension is available, in migration context.
- already installed → no-op (skips the
CREATE EXTENSIONprivilege check entirely, so pre-provisioned low-privilege setups pass) - missing + role can create → queues
CREATE EXTENSION IF NOT EXISTS - missing + role cannot create → raises an operator-facing error listing the extensions to pre-create as a privileged role
@spec ensure_extension!(Ecto.Repo.t(), String.t()) :: :ok
Runtime variant of ensure_extension!/1 for callers outside migration
context (statements run immediately on repo).
@spec ensure_uuid_v7_function(String.t() | nil) :: :ok
Ensures uuid_generate_v7() exists in the install's schema, in
migration context.
Queues a schema-qualified CREATE OR REPLACE FUNCTION whenever this role
could actually run it — no "does it exist" pre-check, on purpose (see
moduledoc "ensure_uuid_v7_function/1"); the only thing checked first is
whether an existing function is owned by another role, which no rescue
in migration context could recover from (the DDL is queued, so the error
arrives at flush time). The schema must exist when this runs; V01 owns
schema creation, so callers on the upgrade path (installed version > 0)
are always safe.
@spec ensure_uuid_v7_function(Ecto.Repo.t(), String.t() | nil) :: :ok
Runtime variant of ensure_uuid_v7_function/1 (statements run
immediately on repo; raises on database errors via repo.query!/3).
Schema-qualified pgcrypto function reference for SQL interpolation in
migration context, e.g. "#{Helpers.pgcrypto_call("digest")}(...)".
Resolves pgcrypto's actual installation schema (same lookup used by
ensure_uuid_v7_function/1) so the call works regardless of the
connecting role's search_path — required whenever pgcrypto was
installed outside the default search path (e.g. alongside a custom
prefix schema in a hardened multi-schema install). Call after
ensure_extension!("pgcrypto") so the extension is already visible.
Whether the prefix denotes the default public schema (nil counts).
Schema-qualified table reference for raw SQL interpolation.
nil and "public" both qualify explicitly as public. — an
explicit schema never depends on the connection's search_path.
Schema-qualified uuid_generate_v7() call for SQL interpolation.
@spec validate_prefix!(term()) :: :ok
Validates a schema prefix before it is interpolated into SQL.
The migration chain interpolates the prefix into hundreds of
statements, mostly unquoted, so only conventional lower-case
identifiers are supported: "^[a-z_][a-z0-9_]*$".
Raises ArgumentError for anything else (including uppercase or
dashed names, which only ever half-worked) — including a prefix over
20 bytes, which fits every conventional prefix a real
install uses but rejects the ones that would silently truncate a
prefix-embedded object name (see @longest_embedded_object_name above)
past Postgres's 63-byte NAMEDATALEN.