phoenix_kit

1.7.114

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

Raw

lib/phoenix_kit/migrations/postgres/v79.ex

defmodule PhoenixKit.Migrations.Postgres.V79 do
  @moduledoc """
  V79: Newsletters Module — Database Tables

  Creates 4 tables for the newsletter broadcast system:
  - `phoenix_kit_newsletters_lists` — Named newsletter lists for segmentation
  - `phoenix_kit_newsletters_list_members` — User membership in lists
  - `phoenix_kit_newsletters_broadcasts` — Email broadcasts (draft/sent/scheduled)
  - `phoenix_kit_newsletters_deliveries` — Per-recipient delivery tracking

  All UUIDs use `uuid_generate_v7()`. All operations are idempotent.
  """

  use Ecto.Migration

  def up(%{prefix: prefix} = _opts) do
    p = prefix_str(prefix)

    # =========================================================================
    # Table 1: phoenix_kit_newsletters_lists
    # =========================================================================

    execute("""
    CREATE TABLE IF NOT EXISTS #{p}phoenix_kit_newsletters_lists (
      uuid UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
      name VARCHAR(255) NOT NULL,
      slug VARCHAR(255) NOT NULL,
      description TEXT,
      status VARCHAR(20) NOT NULL DEFAULT 'active',
      is_default BOOLEAN NOT NULL DEFAULT false,
      subscriber_count INTEGER NOT NULL DEFAULT 0,
      inserted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
    """)

    execute("""
    CREATE UNIQUE INDEX IF NOT EXISTS idx_newsletters_lists_slug
    ON #{p}phoenix_kit_newsletters_lists (slug)
    """)

    # =========================================================================
    # Table 2: phoenix_kit_newsletters_list_members
    # =========================================================================

    execute("""
    CREATE TABLE IF NOT EXISTS #{p}phoenix_kit_newsletters_list_members (
      uuid UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
      user_uuid UUID NOT NULL,
      list_uuid UUID NOT NULL,
      status VARCHAR(20) NOT NULL DEFAULT 'active',
      subscribed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      unsubscribed_at TIMESTAMPTZ,
      CONSTRAINT fk_newsletters_list_members_user
        FOREIGN KEY (user_uuid)
        REFERENCES #{p}phoenix_kit_users(uuid)
        ON DELETE CASCADE,
      CONSTRAINT fk_newsletters_list_members_list
        FOREIGN KEY (list_uuid)
        REFERENCES #{p}phoenix_kit_newsletters_lists(uuid)
        ON DELETE CASCADE
    )
    """)

    execute("""
    CREATE UNIQUE INDEX IF NOT EXISTS idx_newsletters_list_members_user_list
    ON #{p}phoenix_kit_newsletters_list_members (user_uuid, list_uuid)
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS idx_newsletters_list_members_list
    ON #{p}phoenix_kit_newsletters_list_members (list_uuid)
    """)

    # =========================================================================
    # Table 3: phoenix_kit_newsletters_broadcasts
    # =========================================================================

    execute("""
    CREATE TABLE IF NOT EXISTS #{p}phoenix_kit_newsletters_broadcasts (
      uuid UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
      subject VARCHAR(998) NOT NULL,
      markdown_body TEXT,
      html_body TEXT,
      text_body TEXT,
      template_uuid UUID,
      list_uuid UUID NOT NULL,
      status VARCHAR(20) NOT NULL DEFAULT 'draft',
      scheduled_at TIMESTAMPTZ,
      sent_at TIMESTAMPTZ,
      total_recipients INTEGER NOT NULL DEFAULT 0,
      sent_count INTEGER NOT NULL DEFAULT 0,
      delivered_count INTEGER NOT NULL DEFAULT 0,
      opened_count INTEGER NOT NULL DEFAULT 0,
      bounced_count INTEGER NOT NULL DEFAULT 0,
      created_by_user_uuid UUID,
      inserted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      CONSTRAINT fk_newsletters_broadcasts_template
        FOREIGN KEY (template_uuid)
        REFERENCES #{p}phoenix_kit_email_templates(uuid)
        ON DELETE SET NULL,
      CONSTRAINT fk_newsletters_broadcasts_list
        FOREIGN KEY (list_uuid)
        REFERENCES #{p}phoenix_kit_newsletters_lists(uuid)
        ON DELETE RESTRICT,
      CONSTRAINT fk_newsletters_broadcasts_created_by
        FOREIGN KEY (created_by_user_uuid)
        REFERENCES #{p}phoenix_kit_users(uuid)
        ON DELETE SET NULL
    )
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS idx_newsletters_broadcasts_list
    ON #{p}phoenix_kit_newsletters_broadcasts (list_uuid)
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS idx_newsletters_broadcasts_status
    ON #{p}phoenix_kit_newsletters_broadcasts (status)
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS idx_newsletters_broadcasts_scheduled_at
    ON #{p}phoenix_kit_newsletters_broadcasts (scheduled_at)
    WHERE scheduled_at IS NOT NULL
    """)

    # =========================================================================
    # Table 4: phoenix_kit_newsletters_deliveries
    # =========================================================================

    execute("""
    CREATE TABLE IF NOT EXISTS #{p}phoenix_kit_newsletters_deliveries (
      uuid UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
      broadcast_uuid UUID NOT NULL,
      user_uuid UUID NOT NULL,
      status VARCHAR(20) NOT NULL DEFAULT 'pending',
      sent_at TIMESTAMPTZ,
      delivered_at TIMESTAMPTZ,
      opened_at TIMESTAMPTZ,
      error TEXT,
      message_id VARCHAR(255),
      inserted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      CONSTRAINT fk_newsletters_deliveries_broadcast
        FOREIGN KEY (broadcast_uuid)
        REFERENCES #{p}phoenix_kit_newsletters_broadcasts(uuid)
        ON DELETE CASCADE,
      CONSTRAINT fk_newsletters_deliveries_user
        FOREIGN KEY (user_uuid)
        REFERENCES #{p}phoenix_kit_users(uuid)
        ON DELETE CASCADE
    )
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS idx_newsletters_deliveries_broadcast
    ON #{p}phoenix_kit_newsletters_deliveries (broadcast_uuid)
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS idx_newsletters_deliveries_user
    ON #{p}phoenix_kit_newsletters_deliveries (user_uuid)
    """)

    execute("""
    CREATE UNIQUE INDEX IF NOT EXISTS idx_newsletters_deliveries_message_id
    ON #{p}phoenix_kit_newsletters_deliveries (message_id)
    WHERE message_id IS NOT NULL
    """)

    execute("""
    CREATE INDEX IF NOT EXISTS idx_newsletters_deliveries_status
    ON #{p}phoenix_kit_newsletters_deliveries (status)
    """)

    # Version marker
    execute("COMMENT ON TABLE #{p}phoenix_kit IS '79'")
  end

  def down(%{prefix: prefix} = _opts) do
    p = prefix_str(prefix)

    execute("DROP TABLE IF EXISTS #{p}phoenix_kit_newsletters_deliveries CASCADE")
    execute("DROP TABLE IF EXISTS #{p}phoenix_kit_newsletters_broadcasts CASCADE")
    execute("DROP TABLE IF EXISTS #{p}phoenix_kit_newsletters_list_members CASCADE")
    execute("DROP TABLE IF EXISTS #{p}phoenix_kit_newsletters_lists CASCADE")

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

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