phoenix_kit

1.7.190

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

phoenix_kit lib phoenix_kit migrations postgres v137.ex

Raw

lib/phoenix_kit/migrations/postgres/v137.ex

defmodule PhoenixKit.Migrations.Postgres.V137 do
  @moduledoc """
  V137: Email event deduplication indexes + `aws_message_id` backfill, plus
  optional performance indexes for the emails module (`phoenix_kit_emails`).

  ## Event dedup

  The `PhoenixKit.Modules.Emails.Event` schema declares unique constraints that
  the database never enforced (a schema↔DB mismatch). This adds the two partial
  unique indexes that back them, deduping at the DB level — at-least-once SQS
  delivery and racing pollers could previously insert duplicate events:

    - **single-occurrence** types (delivery / bounce / complaint / send / reject /
      delivery_delay / subscription / rendering_failure / queued): one row per
      `(email_log_uuid, event_type)`.
    - **multi-occurrence** types (open / click): one row per
      `(email_log_uuid, event_type, occurred_at)`, so a recipient's repeated
      opens and distinct link clicks are kept while an exact SQS redelivery
      (identical timestamp) is collapsed.

  Pre-existing duplicates are removed first so the unique indexes can be created.
  `phoenix_kit_email_events` has no bigint `id` (dropped in V74); `uuid` is the
  UUIDv7 primary key and is time-ordered, so `MIN(uuid)` per group is the earliest
  row — it is kept, the rest deleted.

  `email_log_uuid` is the canonical FK (NOT NULL, backfilled and FK-constrained in
  V56/V70; the legacy bigint `email_log_id` was dropped in V74).

  ## aws_message_id backfill

  The hot SQS lookup now relies solely on the dedicated indexed `aws_message_id`
  column (the legacy headers-JSONB scan was removed in the app code). Backfill it
  for any legacy rows whose AWS MessageId lives only in the `headers` JSONB.
  `DISTINCT ON (aws_id)` + a `NOT EXISTS` guard keep it safe against the existing
  partial unique index on `aws_message_id`.

  ## Optional performance indexes

  pg_trgm substring-search indexes for the admin email list (`pg_trgm` is enabled
  from V111), per-template open/click analytics composites, and a partial index
  for the archiver's body-compression scan.
  """

  use Ecto.Migration

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

    # --- Event dedup: remove pre-existing duplicates, then add the unique indexes

    # single-occurrence types: keep the earliest (smallest UUIDv7) per pair
    execute("""
    DELETE FROM #{p}phoenix_kit_email_events e
    USING #{p}phoenix_kit_email_events d
    WHERE e.email_log_uuid = d.email_log_uuid
      AND e.event_type = d.event_type
      AND e.event_type NOT IN ('open', 'click')
      AND e.uuid > d.uuid
    """)

    # multi-occurrence types (open/click): dedup on (uuid, type, occurred_at)
    execute("""
    DELETE FROM #{p}phoenix_kit_email_events e
    USING #{p}phoenix_kit_email_events d
    WHERE e.email_log_uuid = d.email_log_uuid
      AND e.event_type = d.event_type
      AND e.occurred_at = d.occurred_at
      AND e.event_type IN ('open', 'click')
      AND e.uuid > d.uuid
    """)

    execute("""
    CREATE UNIQUE INDEX IF NOT EXISTS phoenix_kit_email_events_log_uuid_event_type_index
    ON #{p}phoenix_kit_email_events (email_log_uuid, event_type)
    WHERE event_type NOT IN ('open', 'click')
    """)

    execute("""
    CREATE UNIQUE INDEX IF NOT EXISTS phoenix_kit_email_events_log_uuid_type_occurred_index
    ON #{p}phoenix_kit_email_events (email_log_uuid, event_type, occurred_at)
    WHERE event_type IN ('open', 'click')
    """)

    # --- aws_message_id backfill from legacy headers JSONB (conflict-safe)
    execute("""
    UPDATE #{p}phoenix_kit_email_logs l
    SET aws_message_id = src.aws_id
    FROM (
      SELECT DISTINCT ON (aws_id) uuid, aws_id
      FROM (
        SELECT uuid,
               COALESCE(headers->>'aws_message_id',
                        headers->>'X-AWS-Message-Id',
                        headers->>'MessageId') AS aws_id
        FROM #{p}phoenix_kit_email_logs
        WHERE aws_message_id IS NULL
      ) candidates
      WHERE aws_id IS NOT NULL
      ORDER BY aws_id, uuid
    ) src
    WHERE l.uuid = src.uuid
      AND NOT EXISTS (
        SELECT 1 FROM #{p}phoenix_kit_email_logs x
        WHERE x.aws_message_id = src.aws_id
      )
    """)

    # --- Optional performance indexes

    # pg_trgm substring search for the admin email list ("to" is a reserved word)
    execute("""
    CREATE INDEX IF NOT EXISTS phoenix_kit_email_logs_to_trgm_index
    ON #{p}phoenix_kit_email_logs USING gin ("to" gin_trgm_ops)
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS phoenix_kit_email_logs_subject_trgm_index
    ON #{p}phoenix_kit_email_logs USING gin (subject gin_trgm_ops)
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS phoenix_kit_email_logs_campaign_id_trgm_index
    ON #{p}phoenix_kit_email_logs USING gin (campaign_id gin_trgm_ops)
    """)

    # Per-template open/click analytics (get_template_stats CASE WHEN ... IS NOT NULL)
    execute("""
    CREATE INDEX IF NOT EXISTS phoenix_kit_email_logs_template_opened_index
    ON #{p}phoenix_kit_email_logs (template_name, opened_at)
    WHERE opened_at IS NOT NULL
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS phoenix_kit_email_logs_template_clicked_index
    ON #{p}phoenix_kit_email_logs (template_name, clicked_at)
    WHERE clicked_at IS NOT NULL
    """)

    # Archiver body-compression scan (compress_old_bodies streams sent_at order
    # over rows with a body to compress)
    execute("""
    CREATE INDEX IF NOT EXISTS phoenix_kit_email_logs_compress_scan_index
    ON #{p}phoenix_kit_email_logs (sent_at)
    WHERE body_full IS NOT NULL
    """)

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

  @doc """
  Drops the V137 indexes. The `aws_message_id` backfill is data-only and harmless
  to keep, so it is not reversed.
  """
  def down(opts) do
    prefix = Map.get(opts, :prefix, "public")
    p = prefix_str(prefix)

    execute("DROP INDEX IF EXISTS #{p}phoenix_kit_email_logs_compress_scan_index")
    execute("DROP INDEX IF EXISTS #{p}phoenix_kit_email_logs_template_clicked_index")
    execute("DROP INDEX IF EXISTS #{p}phoenix_kit_email_logs_template_opened_index")
    execute("DROP INDEX IF EXISTS #{p}phoenix_kit_email_logs_campaign_id_trgm_index")
    execute("DROP INDEX IF EXISTS #{p}phoenix_kit_email_logs_subject_trgm_index")
    execute("DROP INDEX IF EXISTS #{p}phoenix_kit_email_logs_to_trgm_index")
    execute("DROP INDEX IF EXISTS #{p}phoenix_kit_email_events_log_uuid_type_occurred_index")
    execute("DROP INDEX IF EXISTS #{p}phoenix_kit_email_events_log_uuid_event_type_index")

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