# `PhoenixKit.Migrations.Postgres.Helpers`
[🔗](https://github.com/BeamLabEU/phoenix_kit/blob/v2.13.7/lib/phoenix_kit/migrations/postgres/helpers.ex#L1)

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 on
    `CREATE INDEX` (Postgres rejects `CREATE INDEX schema.name`); only
    `DROP INDEX schema.name` accepts a qualified name.
  * `validate_prefix!/1` — rejects prefixes that can't be interpolated
    into SQL safely. Called at the `up/down` entry points and by the
    prefix-resolving tooling (`PrefixConfig.resolve_prefix/1`,
    `Install.Common`).
  * `ensure_extension!/1` — privilege-aware replacement for a bare
    `CREATE 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 checks `pg_extension` first and
    only attempts creation when the extension is genuinely missing.
  * `ensure_uuid_v7_function/1` — creates `uuid_generate_v7()` inside
    the install's schema (never wherever `search_path` happens to
    point, which pollutes `public` and fails outright on PG15+ where
    `public` isn't world-writable). Unlike `ensure_extension!/1`, this
    one does NOT pre-check existence — it queues `CREATE OR REPLACE
    FUNCTION` for every install whose role owns (or could own) the
    function. That is deliberate, not a missed optimization: an
    `unless already exists` guard 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 REPLACE` never 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 manual `DROP`.
    `CREATE OR REPLACE FUNCTION` on 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 lets
    `PhoenixKit.Migrations.Repair` self-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 REPLACE` requires ownership, and in
    migration context the statement is only QUEUED, so that failure
    arrives at flush time where no `rescue` here 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`.

# `ensure_extension!`

```elixir
@spec ensure_extension!(String.t()) :: :ok
```

Ensures a Postgres extension is available, in migration context.

* already installed → no-op (skips the `CREATE EXTENSION` privilege
  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

# `ensure_extension!`

```elixir
@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`).

# `ensure_uuid_v7_function`

```elixir
@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.

# `ensure_uuid_v7_function`

```elixir
@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`).

# `pgcrypto_call`

```elixir
@spec pgcrypto_call(String.t()) :: String.t()
```

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.

# `public_prefix?`

```elixir
@spec public_prefix?(String.t() | nil) :: boolean()
```

Whether the prefix denotes the default `public` schema (`nil` counts).

# `qualify_table`

```elixir
@spec qualify_table(String.t() | atom(), String.t() | nil) :: String.t()
```

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`.

# `uuid_v7_call`

```elixir
@spec uuid_v7_call(String.t() | nil) :: String.t()
```

Schema-qualified `uuid_generate_v7()` call for SQL interpolation.

# `validate_prefix!`

```elixir
@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.

---

*Consult [api-reference.md](api-reference.md) for complete listing*