PhoenixKit.Migrations.Postgres.V135 (phoenix_kit v1.7.165)

Copy Markdown View Source

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

Summary

Functions

Drops the two skills tables and re-adds the free-text skills column.

Functions

down(opts)

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.

up(opts)