phoenix_kit

1.7.152

1.7.208 1.7.207 1.7.206 1.7.205 1.7.204 1.7.203 1.7.202 1.7.201 1.7.200 1.7.199 1.7.198 1.7.197 1.7.196 1.7.194 1.7.193 1.7.192 1.7.191 1.7.190 1.7.189 1.7.187 1.7.186 1.7.185 1.7.184 1.7.183 1.7.182 1.7.181 1.7.180 1.7.179 1.7.178 1.7.177 1.7.176 1.7.175 1.7.174 1.7.173 1.7.172 1.7.171 1.7.170 1.7.169 1.7.168 1.7.167 1.7.166 1.7.165 1.7.164 1.7.162 1.7.161 1.7.160 1.7.159 1.7.157 1.7.156 1.7.155 1.7.154 1.7.153 1.7.152 1.7.151 1.7.150 1.7.149 1.7.146 1.7.145 1.7.144 1.7.143 1.7.138 1.7.133 1.7.132 1.7.131 1.7.130 1.7.128 1.7.126 1.7.125 1.7.121 1.7.120 1.7.119 1.7.118 1.7.117 1.7.116 1.7.115 1.7.114 1.7.113 1.7.112 1.7.111 1.7.110 1.7.109 1.7.108 1.7.107 1.7.106 1.7.105 1.7.104 1.7.103 1.7.102 1.7.101 1.7.100 1.7.99 1.7.98 1.7.97 1.7.96 1.7.95 1.7.94 1.7.93 1.7.92 1.7.91 1.7.90 1.7.89 1.7.88 1.7.87 1.7.86 1.7.85 1.7.84 1.7.83 1.7.82 1.7.81 1.7.80 1.7.79 1.7.78 1.7.77 1.7.76 1.7.75 1.7.74 1.7.71 1.7.70 1.7.69 1.7.66 1.7.65 1.7.64 1.7.63 1.7.62 1.7.61 1.7.59 1.7.58 1.7.57 1.7.56 1.7.55 1.7.54 1.7.53 1.7.52 1.7.51 1.7.49 1.7.44 1.7.43 1.7.42 1.7.41 1.7.39 1.7.38 1.7.37 1.7.36 1.7.34 1.7.33 1.7.31 1.7.30 1.7.29 1.7.28 1.7.27 1.7.26 1.7.25 1.7.24 1.7.23 1.7.22 1.7.21 1.7.20 1.7.19 1.7.18 1.7.17 1.7.16 1.7.15 1.7.14 1.7.13 1.7.12 1.7.11 1.7.10 1.7.9 1.7.8 1.7.7 1.7.6 1.7.5 1.7.4 1.7.3 1.7.2 1.7.1 1.7.0 1.6.20 1.6.19 1.6.18 1.6.17 1.6.16 1.6.15 1.6.14 1.6.13 1.6.12 1.6.11 1.6.10 1.6.9 1.6.8 1.6.7 1.6.6 1.6.5 1.6.4 1.6.3 1.5.2 1.5.1 1.5.0 1.4.9 1.4.8 1.4.7 1.4.6 1.4.5 1.4.4 1.4.3 1.4.2 1.4.1 1.4.0 1.3.2 1.3.1 1.3.0 1.2.10 1.2.9 1.2.8 1.2.7 1.2.5 1.2.4 1.2.2 1.2.1 1.2.0 1.1.0 1.0.0

A foundation for building Elixir Phoenix apps — SaaS, social networks, ERP systems, marketplaces, and more

HexDocs

Current section

Files

Jump to

Readme 222 Versions 45 Dependencies Dependants Files Activity

phoenix_kit lib phoenix_kit migrations postgres v114.ex

Raw

lib/phoenix_kit/migrations/postgres/v114.ex

defmodule PhoenixKit.Migrations.Postgres.V114 do
  @moduledoc """
  V114: Switch integration storage rows to uuid-only keys.

  Before V113, each integration row in `phoenix_kit_settings` had a
  composite key of the shape `integration:<provider>:<name>` (e.g.
  `"integration:google:default"`). That key construction baked the
  human-chosen name into the row's identity, which forced two
  unfortunate constraints:

    * Names had to match a strict regex (`[a-zA-Z0-9][a-zA-Z0-9\\-_]*`)
      because they were path-style segments in the key column.
    * Names had to be unique per provider — the `key` column has a
      unique index, so two `integration:openrouter:work` rows would
      collide at insert time.

  Both restrictions were storage-layout artifacts, not product
  decisions. Operators wanted "My Company Drive" and a second OpenRouter
  account also called "personal" without the system pushing back.

  V114 lifts both by collapsing the storage key to just the row's UUID.
  The `module` column (already set to `"integrations"` for every
  integration row via `@settings_module`) becomes the sole row-class
  discriminator, and `provider` + `name` live purely in `value_json`.

  ## Migration steps

  1. For every row in `phoenix_kit_settings` whose `key` starts with
     `integration:`:

     a. Parse `provider` and `name` from the key (handling the legacy
        V0 shape `integration:google` without a name as
        `provider=google`, `name="default"`).
     b. Ensure `value_json` has `"provider"` and `"name"` populated
        (idempotent — leaves correct values alone).
     c. Ensure `module = 'integrations'` (was already set for
        integrations created via `add_connection/3`, but legacy rows
        pre-`@settings_module` may have it NULL).
     d. Rewrite the `key` column to the row's `uuid`.

  2. Stamp the table comment with `'114'`.

  All work happens in a single transaction (handled by the outer
  migrator). Per-row updates use only the row's `uuid` for routing —
  the new shape is `key = uuid`, so the row's PK is what we touch.

  ## Down migration

  The down path is best-effort: duplicate `(provider, name)` pairs
  cannot be represented in the old shape, so on a name collision we
  suffix `-<8-char-tail>` to keep the rewrite well-defined. The tail
  is taken from UUIDv7's random segment (`substring(uuid::text from
  25 for 8)`), **not** the leading timestamp prefix — multiple rows
  inserted in the same millisecond would otherwise produce identical
  prefixes and collide on the supposedly-unique suffixed key.
  Round-trip `down → up` therefore changes those names by a suffix.
  Acceptable for a one-shot data migration that operators rarely
  roll back.
  """

  use Ecto.Migration

  def up(opts) do
    prefix = Map.get(opts, :prefix, "public")
    p = prefix_str(prefix)

    rewrite_keys_to_uuid(p)
    execute("COMMENT ON TABLE #{p}phoenix_kit IS '114'")
  end

  def down(opts) do
    prefix = Map.get(opts, :prefix, "public")
    p = prefix_str(prefix)

    rewrite_keys_to_composite(p)
    execute("COMMENT ON TABLE #{p}phoenix_kit IS '113'")
  end

  # `up` — rewrite every `integration:%`-keyed row to `key = uuid`.
  #
  # Done in pure SQL via a single CTE so we don't load every row into
  # Elixir memory. The `substring(key from '^integration:([^:]+)')` pulls
  # the provider; the rest of the suffix (or `'default'` when absent) is
  # the name. JSONB merge guarantees we don't clobber an existing
  # `provider`/`name` field — `jsonb_strip_nulls(... || jsonb_build_object(...))`
  # uses concat semantics so the JSONB body wins where it already has a
  # value.
  #
  # The `WHERE` filter is selective: only rows whose `key` literally
  # starts with `integration:` get touched. Already-migrated rows
  # (`key` is a uuid string) silently skip.
  defp rewrite_keys_to_uuid(p) do
    execute("""
    UPDATE #{p}phoenix_kit_settings AS s
       SET key = s.uuid::text,
           module = 'integrations',
           value_json = COALESCE(s.value_json, '{}'::jsonb)
                        || jsonb_build_object(
                             'provider',
                             COALESCE(
                               NULLIF(s.value_json->>'provider', ''),
                               split_part(substring(s.key from 13), ':', 1)
                             ),
                             'name',
                             COALESCE(
                               NULLIF(s.value_json->>'name', ''),
                               NULLIF(split_part(substring(s.key from 13), ':', 2), ''),
                               'default'
                             )
                           )
     WHERE s.key LIKE 'integration:%';
    """)
  end

  # `down` — rewrite back to the composite shape, with `-<8-char-tail>`
  # suffix on name collisions. Single CTE-driven update.
  #
  # The suffix is sourced from `substring(uuid::text from 25 for 8)` —
  # 8 hex chars from the random tail of UUIDv7 (positions 25-32 of the
  # dashed text representation correspond to the post-variant random
  # segment, 32 bits of entropy). The original `from 1 for 8` extracted
  # the **timestamp prefix**, which collides on rows inserted in the
  # same millisecond — and the down path's claim of a "well-defined
  # rewrite" relies on each row landing on a distinct key.
  defp rewrite_keys_to_composite(p) do
    execute("""
    WITH ordered AS (
      SELECT
        s.uuid,
        s.value_json->>'provider' AS provider,
        s.value_json->>'name'     AS name,
        ROW_NUMBER() OVER (
          PARTITION BY s.value_json->>'provider', s.value_json->>'name'
          ORDER BY s.date_added NULLS LAST, s.uuid
        ) AS rn
      FROM #{p}phoenix_kit_settings s
      WHERE s.module = 'integrations'
        AND s.key = s.uuid::text
    )
    UPDATE #{p}phoenix_kit_settings AS s
       SET key = 'integration:' || o.provider || ':' ||
                 CASE
                   WHEN o.rn = 1 THEN o.name
                   ELSE o.name || '-' || substring(s.uuid::text from 25 for 8)
                 END
      FROM ordered o
     WHERE s.uuid = o.uuid;
    """)
  end

  defp prefix_str("public"), do: ""
  defp prefix_str(prefix) when is_binary(prefix), do: "#{prefix}."
end

Operator	Description	Example
name:	Match package name. Supports * wildcards and repo/package form	name:phx* or name:hexpm/phoenix
description:	Full-text search of package descriptions	description:auth
depends:	Packages depending on a given package. Supports repo:package form	depends:ecto or depends:hexpm:ecto
build_tool:	Filter by build tool	build_tool:mix
updated_after:	Packages updated after an ISO8601 datetime	updated_after:2025-01-01T00:00:00Z
extra:	Match custom metadata (key,value). Nested keys are separated by commas	extra:license,MIT