phoenix_kit

1.7.207

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

Raw

lib/phoenix_kit/migrations/postgres/v111.ex

defmodule PhoenixKit.Migrations.Postgres.V111 do
  @moduledoc """
  V111: PDF library tables for the catalogue module.

  Backs the "PDFs" subtab in `phoenix_kit_catalogue`, layered on top
  of core's `phoenix_kit_files` for binary storage / dedup / soft-delete
  / multi-bucket redundancy. Catalogue owns only the per-page text
  index and the user-facing per-upload row.

  ## Tables

  - `phoenix_kit_cat_pdfs` — thin per-upload row. One row per
    "user uploaded this name". `file_uuid` FK → `phoenix_kit_files.uuid`
    `ON DELETE RESTRICT` (catalogue manages the lifecycle; core
    prune can't remove a file referenced by a live catalogue row).
    Two uploads of identical content (different filenames) → two
    `phoenix_kit_cat_pdfs` rows, one shared `phoenix_kit_files` row,
    one shared extraction.
    Soft-delete via `status` sentinel `"active"` / `"trashed"`
    (workspace convention) plus `trashed_at` for trashed-at age UI.

  - `phoenix_kit_cat_pdf_extractions` — keyed by `file_uuid` PK
    (one row per unique PDF content). Holds the worker's state
    machine (`pending → extracting → extracted | scanned_no_text |
    failed`), `page_count`, `extracted_at`, `error_message`.
    Cascades on the file row's hard delete.

  - `phoenix_kit_cat_pdf_page_contents` — content-addressed dedup
    cache. Keyed by `content_hash` (SHA-256 hex of the page's
    normalized text). Same page text across multiple PDFs (boilerplate,
    legal disclaimers, cross-referenced product entries) is stored
    once. The GIN trigram index on `text` lives here, so the search
    index doesn't grow with duplication.

  - `phoenix_kit_cat_pdf_pages` — per-page join. Composite PK
    `(file_uuid, page_number)`. References both the file (cascade on
    file delete) and the page-content cache (restrict; orphaned content
    rows are GC'd by a catalogue-side helper, not by FK cascade, so
    the cache doesn't churn during normal upload/delete cycles).

  Enables `pg_trgm` for the trigram index.
  """

  use Ecto.Migration

  alias PhoenixKit.Migrations.Postgres.Helpers

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

    # Drop any pre-existing prototype tables — V111 is the canonical
    # shape; if an earlier (pre-rewrite) V111 left rows behind in dev
    # they get dropped here so the schema matches the new code.
    execute("DROP TABLE IF EXISTS #{p}phoenix_kit_cat_pdf_pages CASCADE")
    execute("DROP TABLE IF EXISTS #{p}phoenix_kit_cat_pdfs CASCADE")
    execute("DROP TABLE IF EXISTS #{p}phoenix_kit_cat_pdf_extractions CASCADE")
    execute("DROP TABLE IF EXISTS #{p}phoenix_kit_cat_pdf_page_contents CASCADE")

    Helpers.ensure_extension!("pg_trgm")

    # ── per-upload row ─────────────────────────────────────────────────

    create table(:phoenix_kit_cat_pdfs, primary_key: false, prefix: prefix) do
      add(:uuid, :uuid, primary_key: true, default: fragment("#{prefix}.uuid_generate_v7()"))

      add(
        :file_uuid,
        references(:phoenix_kit_files,
          column: :uuid,
          type: :uuid,
          on_delete: :restrict,
          prefix: prefix
        ),
        null: false
      )

      add(:original_filename, :string, null: false, size: 500)
      add(:byte_size, :bigint)
      add(:status, :string, null: false, default: "active", size: 20)
      add(:trashed_at, :utc_datetime)

      timestamps(type: :utc_datetime)
    end

    create(index(:phoenix_kit_cat_pdfs, [:file_uuid], prefix: prefix))
    create(index(:phoenix_kit_cat_pdfs, [:status], prefix: prefix))

    # ── extraction state (one per unique file) ────────────────────────

    create table(:phoenix_kit_cat_pdf_extractions, primary_key: false, prefix: prefix) do
      add(
        :file_uuid,
        references(:phoenix_kit_files,
          column: :uuid,
          type: :uuid,
          on_delete: :delete_all,
          prefix: prefix
        ),
        primary_key: true,
        null: false
      )

      add(:extraction_status, :string, null: false, default: "pending", size: 20)
      add(:page_count, :integer)
      add(:extracted_at, :utc_datetime)
      add(:error_message, :text)

      timestamps(type: :utc_datetime)
    end

    create(index(:phoenix_kit_cat_pdf_extractions, [:extraction_status], prefix: prefix))

    # ── content-addressed page dedup cache ────────────────────────────

    create table(:phoenix_kit_cat_pdf_page_contents,
             primary_key: false,
             prefix: prefix
           ) do
      add(:content_hash, :string, primary_key: true, null: false, size: 64)
      add(:text, :text, null: false)
      add(:inserted_at, :utc_datetime, null: false)
    end

    execute("""
    CREATE INDEX IF NOT EXISTS phoenix_kit_cat_pdf_page_contents_text_trgm_index
    ON #{p}phoenix_kit_cat_pdf_page_contents USING gin (text gin_trgm_ops)
    """)

    # ── per-page join ─────────────────────────────────────────────────

    create table(:phoenix_kit_cat_pdf_pages, primary_key: false, prefix: prefix) do
      add(
        :file_uuid,
        references(:phoenix_kit_files,
          column: :uuid,
          type: :uuid,
          on_delete: :delete_all,
          prefix: prefix
        ),
        primary_key: true,
        null: false
      )

      add(:page_number, :integer, primary_key: true, null: false)

      add(
        :content_hash,
        references(:phoenix_kit_cat_pdf_page_contents,
          column: :content_hash,
          type: :"varchar(64)",
          on_delete: :restrict,
          prefix: prefix
        ),
        null: false
      )

      add(:inserted_at, :utc_datetime, null: false)
    end

    create(index(:phoenix_kit_cat_pdf_pages, [:content_hash], prefix: prefix))

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

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

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

    drop_if_exists(table(:phoenix_kit_cat_pdf_pages, prefix: prefix))
    drop_if_exists(table(:phoenix_kit_cat_pdf_page_contents, prefix: prefix))
    drop_if_exists(table(:phoenix_kit_cat_pdf_extractions, prefix: prefix))
    drop_if_exists(table(:phoenix_kit_cat_pdfs, prefix: prefix))

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