phoenix_kit

1.7.183

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 v136.ex

Raw

lib/phoenix_kit/migrations/postgres/v136.ex

defmodule PhoenixKit.Migrations.Postgres.V136 do
  @moduledoc """
  V136: Employment history for `phoenix_kit_staff`.

  Replaces the single flat employment span on `phoenix_kit_staff_people`
  (`employment_type` / `employment_start_date` / `employment_end_date` /
  `job_title` / `work_location`) with a first-class **history** of employment
  spans, surfaced as a dedicated tab on the person profile.

  Creates `phoenix_kit_staff_employments` — one row per span, carrying the
  employment type, a translatable `job_title`, the org placement at the time
  (`primary_department_uuid` + a `primary_team_uuid` snapshot), the date range
  (`employment_end_date IS NULL` = the current/open span), `work_location`, and
  free-text `notes`.

  ## Single open span per person

  A partial unique index enforces **at most one open span** (`employment_end_date
  IS NULL`) per person — the "current" employment. The app context closes the
  prior open span when a new one starts.

  ## Denormalized "current" mirror

  The matching columns already on `phoenix_kit_staff_people` are kept as a
  denormalized mirror of the current (open) span — the app's `sync_current/1`
  writes them in the same transaction as any span change, so existing readers
  (overview org tree, people list) need no join. This migration does NOT drop
  those columns.

  ## Backfill

  One open span per existing person is seeded from their current columns
  (`employment_type` / `job_title` / dates / `primary_department_uuid` /
  `work_location`), copying any per-locale `job_title` overrides out of the
  person's `translations` JSONB into the span's `translations`. Guarded by a
  `NOT EXISTS` check on the span table so a re-run is a safe no-op; people with
  no employment data at all are skipped (they start with an empty history).
  `primary_team_uuid` is left null on backfill — the person's team comes from the
  many-to-many `team_memberships`, which has no single "primary" to copy.
  """

  use Ecto.Migration

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

    # 1. Employment spans — a person's employment history.
    execute("""
    CREATE TABLE IF NOT EXISTS #{p}phoenix_kit_staff_employments (
      uuid UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
      staff_person_uuid UUID NOT NULL REFERENCES #{p}phoenix_kit_staff_people(uuid) ON DELETE CASCADE,
      employment_type VARCHAR(50),
      job_title VARCHAR(255),
      translations JSONB NOT NULL DEFAULT '{}'::jsonb,
      primary_department_uuid UUID REFERENCES #{p}phoenix_kit_staff_departments(uuid) ON DELETE SET NULL,
      primary_team_uuid UUID REFERENCES #{p}phoenix_kit_staff_teams(uuid) ON DELETE SET NULL,
      employment_start_date DATE,
      employment_end_date DATE,
      work_location VARCHAR(255),
      notes TEXT,
      inserted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS phoenix_kit_staff_employments_person_index
    ON #{p}phoenix_kit_staff_employments (staff_person_uuid)
    """)

    # At most one OPEN span (current employment) per person.
    execute("""
    CREATE UNIQUE INDEX IF NOT EXISTS phoenix_kit_staff_employments_one_open_index
    ON #{p}phoenix_kit_staff_employments (staff_person_uuid)
    WHERE employment_end_date IS NULL
    """)

    # 2. Backfill one span per existing person from the current columns. Guarded
    #    by NOT EXISTS so a partial re-run is a no-op; people with no employment
    #    data are skipped. job_title per-locale overrides are lifted out of the
    #    person's translations JSONB into the span's translations (job_title only).
    execute("""
    INSERT INTO #{p}phoenix_kit_staff_employments
      (staff_person_uuid, employment_type, job_title, translations,
       primary_department_uuid, employment_start_date, employment_end_date, work_location)
    SELECT
      pers.uuid,
      pers.employment_type,
      pers.job_title,
      COALESCE(
        (SELECT jsonb_object_agg(lang, jsonb_build_object('job_title', submap->'job_title'))
         FROM jsonb_each(pers.translations) AS t(lang, submap)
         WHERE submap ? 'job_title'),
        '{}'::jsonb
      ),
      pers.primary_department_uuid,
      pers.employment_start_date,
      pers.employment_end_date,
      pers.work_location
    FROM #{p}phoenix_kit_staff_people pers
    WHERE (
        pers.employment_type IS NOT NULL
        OR pers.job_title IS NOT NULL
        OR pers.employment_start_date IS NOT NULL
        OR pers.employment_end_date IS NOT NULL
        OR pers.primary_department_uuid IS NOT NULL
        OR pers.work_location IS NOT NULL
      )
      AND NOT EXISTS (
        SELECT 1 FROM #{p}phoenix_kit_staff_employments e
        WHERE e.staff_person_uuid = pers.uuid
      )
    """)

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

  @doc """
  Drops the employments table.

  The denormalized `employment_*` / `job_title` / `work_location` columns on
  `phoenix_kit_staff_people` are left intact, so a rollback keeps each person's
  current employment data — only the history is lost.
  """
  def down(opts) do
    prefix = Map.get(opts, :prefix, "public")
    p = prefix_str(prefix)

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

    execute("COMMENT ON TABLE #{p}phoenix_kit IS '135'")
  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