Module-owned versioned migrations for phoenix_kit_og — the decentralized-migrations protocol that core's mix phoenix_kit.update discovers via migration_module/0. This follows the canonical shape documented in phoenix_kit_hello_world's README ("Versioned migrations", "Adopting a table core already creates") and its mix phoenix_kit_hello_world.audit_migrations task: two readers (migrated_version/1 for migration context, migrated_version_runtime/1 for Mix-task context), up/1 re-reading the version before it changes anything, and a namespaced COMMENT ON TABLE marker on one anchor table. PhoenixKitPublishing.Migrations is this chain's closest sibling in spirit — same adoption situation, same semantic-guard requirement — scaled down here to 2 tables, 1 FK, 1 named UNIQUE constraint and 3 indexes (all on the same table), and extended with a new invalid-index self-heal no sibling chain in this workspace has needed yet.

Ownership situation — read before touching

Both tables are core's baseline today, created inline by core migration V154 (shipped in core 1.7.206, per core's CHANGELOG.md). Core's ExpectedSchema manifest carries every column of both tables, and no later core migration (checked through the newest entries, well past V190) ever touches either one again — V154's shape is still exactly what a fresh install gets today. This module never shipped a migration file of its own before this one (git log --all -- '*migration*' in this repo confirms it), so there is no module-side predecessor whose shape could have been squashed incorrectly.

A dedicated research pass — core's literal V154 migration source, core's structured ExpectedSchema.objects/1 manifest, and a live, fully-migrated Postgres catalog (this repo's own phoenix_kit_og_test, migrated to core's current head) — found all three in agreement, column-for-column, index-for-index, constraint-for-constraint, for the shape of both tables. That research pass was then adversarially re-verified by a second agent: isolated Postgres schemas built from the candidate DDL, diffed against a schema built from core's literal V154 text, guards round-tripped twice for idempotence. Verdict: exact match, zero findings, idempotent. up_statements/2 is therefore CREATE TABLE IF NOT EXISTS (full final shape) + a safety-net ADD COLUMN IF NOT EXISTS (core's own belt-and-suspenders for slot_mapping, kept here for the same reason core keeps it — an install whose phoenix_kit_og_assignments predates that column) + semantically-guarded PKs, 1 named UNIQUE constraint, 3 indexes, and 1 foreign key, plus the version-marker COMMENT. No CHECK constraint exists on either table (same three-source pass), so there is no check_guard helper in this file.

Guards are semantic, not name-based — a real host discovery

A host-level rename bug already hit this exact family of guards on a real install (decor_3d_print's phoenix_kit_posts table ended up with 3 duplicate UNIQUE indexes from a name-based guard after a host-level rename), and PhoenixKitNewsletters.Migrations' first cut crashed outright on a renamed host with 42P16 multiple primary keys for the same reason. ALTER TABLE ... RENAME TO never renames a table's own constraints or indexes, and core's own V154 guards get away with by-name checks only because the baseline runs solely on an empty database; an adoption chain runs on tables with an arbitrary naming history. Every guard here is therefore semantic:

• Primary keys (both tables) — "does this table already have ANY primary key" via contype = 'p' on the table (resolved through regclass, immune to renames), never a check for a specific <table>_pkey name.
• The 1 named UNIQUE constraint (phoenix_kit_og_templates_name_uniq) — via contype = 'u' PLUS the exact column set (conkey resolved to column names through pg_attribute), not by name.
• The 1 foreign key — by source table, target table, and source column (all via regclass/pg_attribute, immune to renames on either end). The referential action (ON DELETE ...) is deliberately NOT part of the match — this is an ADOPTION guard, not a shape-repair tool; a host whose existing FK already disagrees on ON DELETE is a legitimate V2+ shape change, not something V1's adoption should silently override.
• The 3 indexes — by ordered column list, uniqueness, access method (btree for all 3), and canonical partial predicate via pg_get_expr, with indexprs IS NULL and a column-count check so an expression index can never masquerade as a match. A bare CREATE INDEX IF NOT EXISTS <name> ... is not enough — it only guards its own literal name, not a second, differently-named index with an identical definition (the phoenix_kit_posts incident above), so every CREATE INDEX/CREATE UNIQUE INDEX here still runs inside a DO $$ ... $$ guard via EXECUTE.

Invalid-index handling — new to this migration-porting series

index_guard/8's semantic check requires i.indisvalid — an invalid index (left behind by a crashed CREATE INDEX CONCURRENTLY, which never happens in this chain's own DDL but can exist on a host from unrelated tooling) never counts as "already satisfies the guard", even under a different name. indisready is deliberately not consulted: Postgres clears indisvalid on (or before) every crashed-CONCURRENTLY path that clears indisready, so indisvalid alone covers the reachable states. But a bare CREATE INDEX IF NOT EXISTS <name> is a no-op against ANY existing object of that name — including an INVALID one under the CANONICAL name — so without an extra step, an invalid canonically named index would stay broken forever: the semantic check (correctly) says "no valid match exists" and queues a CREATE INDEX IF NOT EXISTS, which then (incorrectly) no-ops against the invalid object sharing that exact name. Each index guard therefore runs a DROP-first pass: if an index named exactly like the canonical name exists on this table and is invalid, it is dropped before the semantic check runs, so the subsequent CREATE INDEX IF NOT EXISTS actually creates a fresh, valid one. Verified live (migrations_invalid_index_test.exs): a plain index and a partial UNIQUE index each recover from a simulated crashed-CONCURRENTLY state with no duplicate left behind, idempotently. No sibling chain in this workspace has this yet (grep -rl indisvalid across the phoenix_kit_* siblings returns nothing) — this is new.

Phase 0 — this V1 adopts, and changes NOTHING

CREATE TABLE IF NOT EXISTS shape-identical to core's V154 baseline, under core's exact object names, then a namespaced marker stamp on the anchor table (pkog_schema:1 — an adopted table may already carry a foreign comment, so the reader must treat prose as version 0, never crash on it, never assume it means V1). Because the shape is unchanged, core's ExpectedSchema manifest stays accurate for every column of both tables: no core release is required and there is no release-ordering hazard. This package releases alone.

Phase 1 — the first real shape change (V2+) is when core must move too

Before shipping a version that changes either table's shape:

1. add the objects that version alters to core's manifest generator's @excluded_exact (dev_docs/squash/generate_baseline.exs) and regenerate ExpectedSchema;
2. raise this package's :phoenix_kit floor to the release that ships that regenerated manifest.

Skipping step 1 means mix phoenix_kit.repair restores the old shape after every run, silently undoing the new version.

Phase 2 — creation leaves core's baseline at the next squash cycle

When core cuts its next baseline, module-owned tables are simply not included: fresh installs from then on get both phoenix_kit_og_* tables from THIS chain's V1 — which is why V1's up/1 ensures the uuid_generate_v7() function (and its pgcrypto extension) exist rather than assuming core's chain already provided them, and why the CREATE TABLE statements here are already the full, correct definitions on their own, not merely shape-matching no-ops for already-existing tables. Existing installs are untouched — a baseline squash only affects fresh installs and below-floor bridging.

What must NEVER happen

No conditional core migration of the form "module absent → drop the tables" — that is nondeterministic (depends on which packages are compiled in) and destroys data on a host that merely removed the package. Removing this module's data is a human, manual step — see README.md "Removing this module" for the operator SQL (2 DROP TABLEs in FK-safe order). There is deliberately no automated uninstall path, and down/1 NEVER drops either table for ANY target version, including 0 — it only unstamps (or re-stamps) the marker on the anchor table. The rows are every host's real OG templates and their per-scope assignments; rolling back this module's chain must not destroy any of them.

The migrated version is tracked as a pkog_schema:<N> COMMENT on phoenix_kit_og_templates — the root of this chain's FK tree (phoenix_kit_og_assignments.template_uuid points at it, and nothing in this chain points OUT of it), so it is the one table whose independent loss would strand the other table's foreign key. A marker-less table, or one carrying a foreign (non-pkog_schema:) comment, reads as version 0 — the core-baseline shape before this chain existed.