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 tophoenix_kit_files.uuidfor 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_uuidFK →phoenix_kit_comments(uuid)ON DELETE :delete_all(deleting the comment removes its attachment rows).file_uuidFK →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).positioninteger — caller-managed ordering inside a comment.captiontext, 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)wheresystem_managed = true. LetsStorage.store_system_file/3useON CONFLICT DO NOTHINGso a racing second writer for the same tile silently returns the existing row.phoenix_kit_files_user_or_parent_check— DB-level CHECK constraint enforcinguser_uuid IS NOT NULL OR parent_file_uuid IS NOT NULL. The schema'svalidate_system_managed_invariantsis 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.