feat(oci): container_remote_manifest_relationships schema (S16 Step 2b)

What

Adds the container_remote_manifest_relationships table — the edge table linking a cached manifest list or OCI index to the platform-specific manifests it lists — as the last of Step 2's three MRs in the S16 container remote vertical slice, in its 2a → 2c → 2b order. One goose migration, the regenerated jet types and structure.sql dump, and two integration suites asserting the schema shape and every constraint's accept and reject paths.

No Go production code and no behavior change: the table ships with no reader or writer, which is the intended state — a lazy cache fill stores the parent before any child has been fetched, so nothing links an index's children yet. Defining the mechanism that populates these rows is a spec follow-up tracked in #264.

Step 1 (container_remote_repositories, container_remote_images, container_remote_manifests), 2a (container_remote_blobs), and 2c (container_remote_tags, !1503 (merged)) are all merged, so the merge order this MR requires is already satisfied — see ## Merge order.

Spec: docs/specs/S16-container-remote.md#container_remote_manifest_relationships. Plan: docs/plans/2026-07-30-container-remote.md (Step 2, sub-MR 2b).

Schema

PARTITION BY HASH (namespace_id) × 64, PK (id, namespace_id). Five uuid columns, all NOT NULL. Four FKs across three parents — namespaces, the composite (id, namespace_id) into container_remote_images, and two composites into container_remote_manifests for the parent and child ends of the edge — the only table in the family whose FK set names one parent twice. All four are MATCH SIMPLE and ON DELETE NO ACTION, so a manifest at either end of a live edge cannot be hard-deleted, and a soft-deleted one still blocks.

Three indexes, all non-partial: the unique (namespace_id, parent_container_remote_manifest_id, child_container_remote_manifest_id), a child lookup (namespace_id, child_container_remote_manifest_id), and an image-scoped (namespace_id, container_remote_image_id). Each leads with the columns one composite FK's referential-integrity check has to descend, which is why the table carries no separate FK-coverage index the way container_remote_blobs does. The unique index is scoped to the pair rather than to the grouping image, unlike the blobs and tags unique indexes: a second edge between the same two manifests under a different image is a row nothing can resolve, since a manifest is already reachable from exactly one image through its own FK. Both directions of that inversion carry their own pinning tests.

No CHECK constraints and no timestamp columns of any kind: all five columns are uuids, so the type is the whole domain rule, and an edge is dated by the parent manifest it derives from. Each absence has its own attributable test rather than resting only on the exact-column-count assertion. A self-referencing edge is insertable by design — hosted container_manifest_relationships accepts one too.

Index and constraint names abbreviate the table to crmr: the table name alone is 39 of an identifier's 63 characters, and the unique index spelled out in full is 89. The hosted registry hits the same wall and answers it with cmr, which this schema already uses, so crmr is that initialism with the r for remote inserted.

Merge order

Depended on !1503 (merged) (2c), which has merged. A blocking-MR relationship on !1503 (merged) is set on this MR, and Step 2's order is 2a → 2c → 2b. 2c took 20260812150300 and merged, which settled the half that goose.WithAllowOutofOrder(false) makes hard: had 2b merged first, every database that applied 2b's migration would afterwards refuse 2c (the deploy provider reports detected 1 missing (out-of-order) migration lower than database version (<2b's version>)), and no later migration would apply until 2c was renumbered by hand. The plan's Step 2 section records the reservation and the failure in both directions.

This migration is stamped 20260814150300, and that number is not stable while the MR is open. lint:migration-ordering enforces a floor against the target branch, so each time main takes a migration sorting above this file it is re-stamped above the new floor — currently above main's 20260814131541_make_container_images_name_unique_index_partial.sql. Only internal/datastore/migrations/migrations_checksum_test.go (the comment plus knownHeadVersion) moves with it; structure.sql carries no version list, so a re-stamp needs no re-dump.

Spec and plan contradictions surfaced while authoring

Two points where the documents disagree at something this MR touches; per the plan's convention both are recorded rather than silently resolved:

• The spec's Naming Conventions index-name pattern is arithmetically impossible for this table — spelled out in full, the unique index is 89 characters against PostgreSQL's 63. The migration follows the hosted precedent (cmr there, crmr here) and documents the arithmetic at each renamed identifier.
• The spec's acceptance-criterion numbering moved after the plan was written: the criterion the plan's ownership row calls 41 is 43 in the spec as merged (and 42 is 44). The criterion itself — caching a manifest list writes zero relationship rows — is owned by Step 8, and its only schema half is this table existing to be left empty.

Database Review Evidence

The table and its 64 partitions are created empty, so no phase touches existing rows. Lock behavior was measured on PostgreSQL 17.10 against this table rather than a stand-in, and the numbers live at the statements they govern: each partition CREATE takes ShareRowExclusive on 131 relations across the three FK targets (namespaces, plus 65 each for the two hash-partitioned parents — declaring container_remote_manifests twice widens neither the relation set nor the mode), a bounded write stall that admits reads. The 64 partition DROPs in Down take no lock on any FK target; the final parent DROP reaches the same 131 relations at ACCESS EXCLUSIVE, which blocks reads too — plan a rollback as one brief stall across those parents, not 65 of them. No migration in this repo sets lock_timeout; that schema-wide decision is tracked in #548 and this file does not re-open it.

Migrations

Note

Timings are from CI (db:migrate matrix, goose verbose) against an empty database, in apply / rollback order per PG version. Production-scale validation via Database Lab is not yet available. See Database review evidence for the matrix rationale and how to read the numbers.

Migration	PG 16	PG 17	PG 18
20260814150300_create_container_remote_manifest_relationships.sql	OK (765.93ms / 391.39ms)	OK (385.69ms / 369.45ms)	OK (868.9ms / 788.26ms)

Migration notes:

• The spread across PG versions is run-to-run noise rather than a property of any one version, so the row is not a version comparison. This pipeline has PG 18 rolling back in 788.26ms against PG 16's 391.39ms, a ratio just past 2x. The previous pipeline on this branch (2755758420) ran the same DDL at 1.38s / 547.83ms on PG 18 and 653.37ms / 371.53ms on PG 16 — apply on PG 18 moved by more than 500ms between two runs of identical DDL, and the rollback ratio fell to 1.4x. Neither the 2x ratio here nor the 1.38s apply there reproduces.
• Every measured apply and rollback sits far inside the 5-minute per-migration boot budget, the slowest observation across both pipelines being 1.38s on an empty database. Apply costing more than rollback is the expected direction for this migration: Up creates the parent, 64 hash partitions, and their index clones, while Down drops them.

No EXPLAIN evidence ships in the suites: no store or query-layer code queries this table yet. The schema suite's header says so and names where that evidence belongs — the MR that adds the first real query against the table.

On the size of this MR

Above the 500 LOC guideline in docs/dev/development-model.md, matching the merged Step 1 and 2a MRs and the open 2c. Measured with the plan's counting rule (added lines in hand-written .go and .sql files, blanks and comment-only lines dropped, -- +goose directives kept):

Part	LOC
Migration SQL	213
— of which mechanical 64-partition DDL	192
Integration suites (schema, constraints, fixtures) + token and checksum bumps	668
Total	881

structure.sql (+1,822) and the go-jet artifacts (+111) are generated and excluded; the small plan edits ride along. The novel hand-written DDL is ~21 lines once the partition statements come out (CONCURRENTLY is unavailable on a partitioned parent, and batching the partitions into a DO $$ ... LOOP would accumulate all the locks in one transaction, which the per-statement layout avoids). The bulk is the two integration suites, and splitting further would mean splitting one table's schema from its own tests; the plan's shape is one table per MR, and this is that table.

Suggested reading order

By file, since the branch squashes on merge:

1. internal/datastore/migrations/sql/20260814150300_create_container_remote_manifest_relationships.sql — the table, with every column, constraint, index, and lock decision documented at the statement it governs
2. container_remote_manifest_relationships_schema_integration_test.go — catalog shape, the named column-absence groups, all three index shapes, partition inheritance and routing
3. container_remote_manifest_relationships_constraints_integration_test.go — NOT NULL backstops, the unique pair index in all four of its directions, all four FKs including both cross-namespace halves of the manifest FKs, and delete blocks from either end of an edge and from the grouping image
4. container_remote_test_helpers_test.go plus the one-line containerRemoteMigrationTokens addition — the shared fixtures, and the token that folds this migration into the existing static Down-walk tests

End-to-end scenario catalogs

No scenario added or invalidated. This step ships schema only: nothing reads or writes container_remote_manifest_relationships until later steps land, so there is no observable client behavior for a scenario to cover. Both container catalogs (docs/testing/e2e/oci.md, docs/testing/e2e/docker.md) already list remote repositories as out of scope until the read paths ship.

Related to #288