PhoenixKit.Migrations.Postgres.V113 (phoenix_kit v1.7.113)

Copy Markdown View Source

V113: System-managed media flag + source-parent link for Tessera tiles, plus the comments ↔ files attachment junction table.

Adds two columns to phoenix_kit_files:

  • system_managed :: boolean, default false, not null — when true, the File represents internally-generated media (DZI tile pyramids and their per-tile chunks). System-managed files are excluded from the user-facing MediaBrowser listings and skip the variant generation pipeline (no small / medium / large produced — they only get an "original" FileInstance, since they don't need quality variants).

  • parent_file_uuid :: uuid, nullable — FK to phoenix_kit_files.uuid for system-managed children, pointing at the source file the chunk was derived from. Lets us cascade-clean tiles when the source image is deleted, and lets us list all tiles for a given source for auditing / re-generation.

Index on (parent_file_uuid) for the cascade-delete + per-source queries. Partial index on system_managed = true keeps the MediaBrowser's "WHERE NOT system_managed" filter cheap as the tile catalog grows.

Also creates phoenix_kit_comment_media — a junction table that lets the comments module attach core File rows to individual comments with a caller-supplied ordering and optional caption:

  • comment_uuid FK → phoenix_kit_comments(uuid) ON DELETE :delete_all (deleting the comment removes its attachment rows).
  • file_uuid FK → phoenix_kit_files(uuid) ON DELETE :restrict (a file can't be hard-deleted while it's still attached to a comment; the comments module manages the unlink lifecycle).
  • position integer — caller-managed ordering inside a comment.
  • caption text, nullable.

Unique index on (comment_uuid, position) so each slot in a comment is occupied at most once. Per-file index on file_uuid for reverse lookup ("which comments reference this file?").

Concurrent-generation safety

Two additional safeguards keep concurrent lazy-generation requests from producing duplicate phoenix_kit_files rows or violating the "user_uuid OR parent_file_uuid" invariant:

  • phoenix_kit_files_system_dedup_index — partial unique index on (parent_file_uuid, file_name) where system_managed = true. Lets Storage.store_system_file/3 use ON CONFLICT DO NOTHING so a racing second writer for the same tile silently returns the existing row.

  • phoenix_kit_files_user_or_parent_check — DB-level CHECK constraint enforcing user_uuid IS NOT NULL OR parent_file_uuid IS NOT NULL. The schema's validate_system_managed_invariants is the user-facing check; this constraint is the safety net for raw inserts, Repo.insert_all, or external tools.

All column / FK / NOT-NULL changes use raw SQL with explicit IF NOT EXISTS / DO $$ … END $$ guards so re-running on a partially-applied schema is a no-op.

Summary

Functions

down(opts)

up(opts)