phoenix_kit

1.7.205

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 46 Dependencies Dependants Files Activity

phoenix_kit lib phoenix_kit migrations postgres v135.ex

Raw

lib/phoenix_kit/migrations/postgres/v135.ex

defmodule PhoenixKit.Migrations.Postgres.V135 do
  @moduledoc """
  V135: Structured staff skills.

  Replaces the free-text `phoenix_kit_staff_people.skills` column with a
  first-class, translatable `Skill` entity assigned to people many-to-many,
  each assignment carrying zero or more of the skill's own proficiency levels.
  Creates:

  - `phoenix_kit_staff_skills` — translatable skill (name + description +
    `translations` JSONB), globally unique by `lower(name)`. Carries its own
    **per-skill, translatable proficiency levels** in a `levels` JSONB array
    (each `{"id", "name", "translations"}`) plus an `allow_multiple_levels`
    boolean that decides whether an assignment may hold one level or several.
  - `phoenix_kit_staff_person_skills` — person ↔ skill join whose
    `proficiency_levels` JSONB array holds the selected level `id`s into the
    parent skill's `levels` (`[]` = no level / "not set")

  Also adds a partial index on `phoenix_kit_staff_people(date_of_birth)`
  (active + non-null DOB only) so `Staff.upcoming_birthdays/1` scans a small
  index rather than the full people table.

  ## Data migration

  The free-text `skills` column (comma-separated) is split, trimmed,
  case-insensitively de-duplicated into `Skill` rows, and each person is
  linked to the skills parsed from their string (proficiency `NULL`). Then
  the column is dropped. The parse/insert runs inside a column-existence
  guard so a partial re-run (column already dropped) is a safe no-op.

  **Lossy by design (documented):** the column holds only the primary-language
  skill string. Per-locale skill overrides — `translations[locale]["skills"]`
  on the people table, a *separate* JSONB column — do **not** map cleanly to
  structured skills and are **dropped** (the orphaned `"skills"` keys are
  stripped from each person's `translations`). Structured skills carry their
  own translations going forward.
  """

  use Ecto.Migration

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

    # 1. Skills entity (translatable, flat — no parent).
    execute("""
    CREATE TABLE IF NOT EXISTS #{p}phoenix_kit_staff_skills (
      uuid UUID PRIMARY KEY DEFAULT #{prefix}.uuid_generate_v7(),
      name VARCHAR(255) NOT NULL,
      description TEXT,
      translations JSONB NOT NULL DEFAULT '{}'::jsonb,
      levels JSONB NOT NULL DEFAULT '[]'::jsonb,
      allow_multiple_levels BOOLEAN NOT NULL DEFAULT false,
      inserted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
    """)

    execute("""
    CREATE UNIQUE INDEX IF NOT EXISTS phoenix_kit_staff_skills_lower_name_index
    ON #{p}phoenix_kit_staff_skills (lower(name))
    """)

    # 2. person ↔ skill join + nullable proficiency level.
    execute("""
    CREATE TABLE IF NOT EXISTS #{p}phoenix_kit_staff_person_skills (
      uuid UUID PRIMARY KEY DEFAULT #{prefix}.uuid_generate_v7(),
      staff_person_uuid UUID NOT NULL REFERENCES #{p}phoenix_kit_staff_people(uuid) ON DELETE CASCADE,
      skill_uuid UUID NOT NULL REFERENCES #{p}phoenix_kit_staff_skills(uuid) ON DELETE CASCADE,
      proficiency_levels JSONB NOT NULL DEFAULT '[]'::jsonb,
      inserted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
    """)

    execute("""
    CREATE UNIQUE INDEX IF NOT EXISTS phoenix_kit_staff_person_skills_person_skill_index
    ON #{p}phoenix_kit_staff_person_skills (staff_person_uuid, skill_uuid)
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS phoenix_kit_staff_person_skills_skill_index
    ON #{p}phoenix_kit_staff_person_skills (skill_uuid)
    """)

    # 3. Migrate the free-text column → structured rows. Guarded on the
    #    column still existing so a retry after the DROP below is a no-op
    #    (PL/pgSQL plans the inner statements lazily, so they're never parsed
    #    when the column is gone). Prefix threaded through the dynamic SQL.
    execute("""
    DO $$
    BEGIN
      IF EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = '#{prefix}'
          AND table_name = 'phoenix_kit_staff_people'
          AND column_name = 'skills'
      ) THEN
        -- distinct skills, deterministic canonical casing. The source
        -- people.skills column is unbounded TEXT but skills.name is
        -- VARCHAR(255) (the Skill changeset's max), so cap each token at
        -- 255 before it reaches the bounded column — otherwise one long
        -- token would raise "value too long" and abort the whole migration.
        INSERT INTO #{p}phoenix_kit_staff_skills (name)
        SELECT DISTINCT ON (lower(LEFT(trim(tok), 255))) LEFT(trim(tok), 255)
        FROM #{p}phoenix_kit_staff_people pers
        CROSS JOIN LATERAL regexp_split_to_table(pers.skills, ',') AS tok
        WHERE pers.skills IS NOT NULL AND trim(tok) <> ''
        ORDER BY lower(LEFT(trim(tok), 255)), LEFT(trim(tok), 255)
        ON CONFLICT (lower(name)) DO NOTHING;

        -- link each person to the skills parsed from their string. The join
        -- uses the capped form so a token longer than 255 chars still matches
        -- the truncated skill row above (and so gets linked, not dropped).
        INSERT INTO #{p}phoenix_kit_staff_person_skills (staff_person_uuid, skill_uuid)
        SELECT DISTINCT pers.uuid, sk.uuid
        FROM #{p}phoenix_kit_staff_people pers
        CROSS JOIN LATERAL regexp_split_to_table(pers.skills, ',') AS tok
        JOIN #{p}phoenix_kit_staff_skills sk ON lower(sk.name) = lower(LEFT(trim(tok), 255))
        WHERE pers.skills IS NOT NULL AND trim(tok) <> ''
        ON CONFLICT (staff_person_uuid, skill_uuid) DO NOTHING;

        -- strip the now-orphaned per-locale "skills" overrides from the
        -- separate translations JSONB (idempotent: removing an absent key
        -- is a no-op; other translated fields are preserved)
        UPDATE #{p}phoenix_kit_staff_people pers
        SET translations = (
          SELECT COALESCE(jsonb_object_agg(lang, submap - 'skills'), '{}'::jsonb)
          FROM jsonb_each(pers.translations) AS t(lang, submap)
        )
        WHERE pers.translations <> '{}'::jsonb;
      END IF;
    END $$;
    """)

    # 4. Drop the free-text column (independently idempotent).
    execute("ALTER TABLE #{p}phoenix_kit_staff_people DROP COLUMN IF EXISTS skills")

    # 5. Partial index for `Staff.upcoming_birthdays/1`, which filters
    #    `status = 'active' AND date_of_birth IS NOT NULL` before its
    #    next-anniversary window fragment — so Postgres scans a small index
    #    instead of the full people table as the roster grows.
    execute("""
    CREATE INDEX IF NOT EXISTS phoenix_kit_staff_people_active_dob_index
    ON #{p}phoenix_kit_staff_people (date_of_birth)
    WHERE status = 'active' AND date_of_birth IS NOT NULL
    """)

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

  @doc """
  Drops the two skills tables and re-adds the free-text `skills` column.

  **Lossy rollback:** the re-added `skills` column is empty — structured
  skill rows and assignments are destroyed and the original free-text values
  are not restored.
  """
  def down(opts) do
    prefix = Map.get(opts, :prefix, "public")
    p = prefix_str(prefix)

    execute("DROP INDEX IF EXISTS #{p}phoenix_kit_staff_people_active_dob_index")

    execute("ALTER TABLE #{p}phoenix_kit_staff_people ADD COLUMN IF NOT EXISTS skills TEXT")

    execute("DROP TABLE IF EXISTS #{p}phoenix_kit_staff_person_skills")
    execute("DROP TABLE IF EXISTS #{p}phoenix_kit_staff_skills")

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

  defp prefix_str("public"), do: "public."
  defp prefix_str(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