# `PhoenixKit.Migrations.Postgres.V113`
[🔗](https://github.com/BeamLabEU/phoenix_kit/blob/v1.7.165/lib/phoenix_kit/migrations/postgres/v113.ex#L1)

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.

# `down`

# `up`

---

*Consult [api-reference.md](api-reference.md) for complete listing*