feat(oci): container_remote_images schema (S16 Step 1b)

What

Adds the container_remote_images table — a cached container image within a remote repository — as the second of Step 1's three MRs in the S16 container remote vertical slice. 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: nothing reads or writes this table until Step 8 (cache-fill) and Step 11 (cache-hit serve path).

container_remote_repositories (1a) merged; container_remote_manifests (1c) follows in its own MR and takes the next free timestamp after this one merges — migrations.go sets goose.WithAllowOutofOrder(false), so two branches opened together would produce one that merges second carrying the earlier timestamp, which the runner then refuses on any database that already applied the later one.

Reviewing guide: the migration and the two new test files are the surface that wants reading. Every column, constraint, and index decision is documented at the statement it governs in the migration; the derivations behind the non-obvious ones — child index naming, the FK-target lock arithmetic — live in the plan's Step 1. This description covers only what the diff cannot show.

Schema

Shape: PARTITION BY HASH (namespace_id) × 64 with children in the partitions schema, PK (id, namespace_id) on application-generated UUIDv7, a composite FK to container_remote_repositories(id, namespace_id) with NO ACTION, a namespace_id FK to namespaces(id) also NO ACTION, a partial unique index on (namespace_id, container_remote_repository_id, name), and one CHECK carrying both bounds on name. All of it reads off the migration.

name takes char_length(name) >= 1 alongside the 255 upper bound. An upper bound alone admits the empty string, which passes char_length <= 255 and inserts. maven_remote_packages already carries that pair on group_id and artifact_id, the two columns it names parts with.

The FK-coverage index, and the spec change that goes with it

The spec specified this table with two secondary indexes — a narrow (namespace_id, container_remote_repository_id) for FK coverage and a last_downloaded_at retention index. This MR ships one, and amends the spec to match.

Most remote tables in the schema do carry the narrow index, because PostgreSQL does not auto-index FK columns and their partial unique index excludes soft-deleted rows, so the referential-integrity check on a parent delete cannot use it. They earn it two different ways: maven_remote_versions makes its retention index partial on soft_deleted_at IS NULL, and maven_remote_packages, npm_remote_packages, npm_remote_versions and npm_remote_files have no retention index at all. In every one of those cases the narrow index is the only full index on the parent's columns.

container_remote_images meets neither condition. Its retention index is deliberately non-partial — a soft_deleted_at IS NULL predicate would hide from the eviction sweep the rows it exists to reclaim — so it is already a full index leading with the two FK columns. The narrow index would be a strict key-column prefix of it: no access path of its own, and a set of entries to maintain on every last_downloaded_at bump, which cannot be a HOT update precisely because that column is indexed. That bump is the highest-frequency write this table will take, once per blob and manifest GET/HEAD.

This is not a new shape for the schema. npm_remote_metadata_files already skips the narrow index, reaching the same conclusion through a different index: it has no soft_deleted_at column, so its unique (namespace_id, npm_remote_package_id, kind) is non-partial and answers the RI check on the same prefix argument. And one unique plus one retention index, both non-partial, is exactly what the hosted container_images this table mirrors carries.

Measured on PostgreSQL 17 with 20,000 image rows in one partition and a childless parent — the case that forces the check to prove absence rather than stop at the first hit:

Index Scan using container_remote_images_p57_namespace_id_container_remote__idx1
  Index Cond: ((namespace_id = '...') AND (container_remote_repository_id = '...'))
  Buffers: shared hit=2
Execution Time: 0.020 ms

The retention index serves the RI check on its leading keys and never reads the third. Deleting a childless parent takes 3.9 ms; deleting one with 20,000 live children is correctly blocked with 23503 in 2.3 ms.

Spec change in this MR. docs/specs/S16-container-remote.md mandated the pair, so the code cannot drop it without the spec moving too. The spec now folds the FK-coverage role into the retention index row for container_remote_images and container_remote_manifests, states why container_remote_blobs keeps a separate one — it has no retention index for the check to descend instead — and the ADR-007 reconciliation follow-up drops from five indexes to three to match. That count appears in two places in the Data Model section, and both are updated; the preamble and the ledger disagreeing is how the redundant shape gets transcribed back in.

container_remote_manifests is Step 1c's table, not this one's. Its row is corrected here because the reasoning is identical and leaving the two sibling rows disagreeing is how the redundant shape would get transcribed a third time. Step 1a set the precedent for correcting this spec's rows from a step MR, including rows for tables later steps build.

Three sibling claims corrected

Writing the above turned up three claims — in the spec, the migration comments, the test doc comments, and the plan — that were wrong or half-updated. All four files are in this MR; the corrections are prose and comments only, with no DDL, no assertion, and no generated file touched.

1. "Unlike every other remote table in the schema" was false, at three sites. npm_remote_metadata_files is the counterexample above. The correction names the five tables that do carry the narrow index and why, so the claim is one a reader can check against the migrations. It makes the change easier to defend, not harder — there is already in-repo precedent for both the omission and the resulting two-index shape.
2. The child-index truncation claim was transcribed from the wrong table. PostgreSQL auto-names each of the 64 index clones from the child table plus the column list and shortens whichever is longer — and which one loses varies per table. On container_remote_images the column list loses, so _pNN survives (container_remote_images_p57_namespace_id_container_remote__idx1), the same as maven_remote_packages. It is container_remote_repositories that loses _pNN — keeping a single digit that points at a different partition — and container_manifest_relationships loses it the same way. The migration already said this correctly at the index it governs; the test doc comment and the plan bullet said the opposite. Either way the clones on one partition differ only by a trailing counter that follows creation order, so the operator rule is unchanged: resolve a child index from pg_index, never target it by name.
3. The _and_ separator rationale was right for one index, not both. The plan said both parent index names drop the separator database.md asks for under the guide's length allowance. Unabbreviated, the retention index is 65 characters and genuinely needs it; the unique index fits at 57. It drops the separator to match unique_npm_remote_packages_ns_id_repo_id_name and unique_maven_remote_packages_ns_id_repo_id_group_artifact, not for length.

Three edits outside this step's Files entry

The plan's Step 1 names exactly two accepted cross-cutting edits, and both were 1a's. These are additional, and a reviewer will stop on them:

1. container_remote_constraints_integration_test.go is 1a's already-merged suite, and this change to it is a comment correction plus nine mechanical conversions. The comment corrects the claimed FK trigger firing order, which is alphabetical by constraint name, not declaration order. 1b found it by copying the same paragraph, then finding the copy did not describe what the database does. The conversions follow from typing the shared table-name constants as parentTable so the type reaches the one helper that interpolates a table name: countMatchingCheckConstraints binds the table as $1 instead, so it keeps a plain string parameter and each call site converts at that boundary.
2. The plan's Step 1 and Step 2c Acceptance prose. This needs the history or it reads as churn. The sentence was already rewritten on purpose on the 1a branch, dropping an !1189 link and keeping maven_remote_packages.name as "a schema fact a reader can check". It is not a checkable fact — maven_remote_packages has no name column; its both-bounds CHECKs are on group_id and artifact_id. The same paragraph also said those columns have "no lower bound" and that the spec "states the upper bound only", where the spec says "minimum length 1" at both of its name rows. This edit finishes what that rewrite was reaching for rather than reversing it.
3. Two shared test constants — pgNotNullViolation in migrations_test.go and pgMaxIdentifierLength in schema_helpers_test.go — sit beside the existing pgForeignKeyViolation they mirror. Test-only and additive.

One more that is not an edit but bears on a rule. The migration's Down section states its shape rule — each partition dropped directly, no DETACH pair — with no #448 pointer, so the tracking pointer stays at its canonical site in schema_helpers_test.go and in the plan. CLAUDE.md asks that a tracking-work-item pointer appear once per obligation, and migrations are immutable, which cuts against a copy here rather than for one: it could never be collapsed into a reference when #448 lands, it would just go stale in place. The two copies that are already permanent — 1a's migration and create_maven_remote_versions.sql — are accepted as they stand. This holds the set at two instead of letting it grow with each further step.

Spec coverage

Spec: docs/specs/S16-container-remote.md (container_remote_images, plus the primary-key, last_downloaded_at, and index-refinement paragraphs of Data Model). Plan: docs/plans/2026-07-30-container-remote.md (Step 1, sub-MR 1b).

Scope is container_remote_images alone. container_remote_repositories (1a) already merged; container_remote_manifests (1c) and every Step 2 table are not authored here.

Schema invariants

#	Invariant	Tests
DM-1	Exists post-Up as a HASH(namespace_id) partitioned table with exactly 64 partitions named pNN	TestContainerRemoteImagesSchema_TableAndPartitionsExistPostUp, TestContainerRemoteImagesSchema_PartitionsByHashOfNamespaceID
DM-2	Primary key is exactly (id, namespace_id), in that order	TestContainerRemoteImagesSchema_PrimaryKey
DM-3	Exact column set, type, and nullability, with the count pinning no created_at, no scope, and no digest/media_type/size	TestContainerRemoteImagesSchema_Columns
DM-4	No created_at column, attributably (the sibling cache tables in this spec do carry one)	TestContainerRemoteImagesSchema_NoCreatedAtColumn
DM-5	Neither last_downloaded_at nor soft_deleted_at carries a DEFAULT: both are NULL on a fresh fill, which is what the retention index's NULLS FIRST ordering and the partial unique index's predicate each read	TestContainerRemoteImagesSchema_TimestampColumnsDefaultNull
DM-6	Partial unique (namespace_id, container_remote_repository_id, name) WHERE soft_deleted_at IS NULL — name, key order, partiality, predicate; exactly one non-primary unique index	TestContainerRemoteImagesSchema_UniqueIndexShape
DM-7	Retention index (namespace_id, container_remote_repository_id, last_downloaded_at NULLS FIRST) — that exact key order and null ordering, non-partial, so it also answers the RI check on a parent delete	TestContainerRemoteImagesSchema_SecondaryIndexShapes
DM-8	Exactly one non-unique, non-primary index: the retention index and no other. A second is the separate FK-coverage index this table deliberately omits	TestContainerRemoteImagesSchema_SecondaryIndexShapes
DM-9	Every index name this suite pins is at most 63 characters, so PostgreSQL cannot truncate it silently into a duplicate index	TestContainerRemoteImagesSchema_IndexNamesFitIdentifierLimit
DM-10	All 64 partitions carry a clone of each of the two parent indexes, matched by key columns and never by child name	TestContainerRemoteImagesSchema_EveryPartitionInheritsTheParentIndexes
DM-11	Live partition routing agrees with satisfies_hash_partition	TestContainerRemoteImagesSchema_PartitionRoutingByHashOfNamespaceID
DM-12	An image row inserts under either container format its ancestor repository carries — docker (0) and oci (3), both positive hits	TestContainerRemoteImagesSchema_AcceptsEitherContainerParentFormat (docker_ancestor, oci_ancestor)
DM-13	name carries BOTH bounds in one CHECK: 1 and 255 accepted, empty string and 256 rejected 23514 naming the constraint	TestContainerRemoteImagesConstraints_NameLengthCHECK (single_character_accepted, at_upper_boundary_accepted, empty_rejected, over_upper_boundary_rejected)
DM-14	name is NOT NULL, enforced: a NULL binds to 23502 naming that column (the length CHECK is null-tolerant and cannot cover it)	TestContainerRemoteImagesConstraints_NameNotNull
DM-15	A second live row with the same name in the same remote repository is rejected 23505	TestContainerRemoteImagesConstraints_PartialUniqueRejectsDuplicateLiveName
DM-16	A soft-deleted image can be re-created under the same name, and both rows survive the re-creation	TestContainerRemoteImagesConstraints_PartialUniqueAllowsRecreateAfterSoftDelete
DM-17	The unique index is scoped per remote repository, not per namespace	TestContainerRemoteImagesConstraints_PartialUniqueScopedPerRemoteRepository
DM-18	Composite FK to container_remote_repositories(id, namespace_id) rejects an absent parent, 23503 naming that FK	TestContainerRemoteImagesConstraints_FKRejectsAbsentRemoteRepository
DM-19	The same composite FK rejects a parent that exists in a different namespace — the tenancy half a single-column FK would admit	TestContainerRemoteImagesConstraints_FKRejectsCrossNamespaceRemoteRepository
DM-20	An absent namespace_id is rejected with 23503. Which FK raises it is not asserted and could not be — a row with an absent namespace also has an unsatisfiable composite FK, so the SQLSTATE pins only the conjunction	TestContainerRemoteImagesConstraints_FKRejectsAbsentNamespace
DM-21	Deleting a container_remote_repositories row with a live image is blocked 23503 (NO ACTION, not CASCADE)	TestContainerRemoteImagesConstraints_RemoteRepositoryDeleteBlockedByLiveChild
DM-22	The same delete is still blocked when the only child is soft-deleted: the RI check reads rows, not the partial index	TestContainerRemoteImagesConstraints_RemoteRepositoryDeleteBlockedBySoftDeletedChild
DM-23	Exactly two FKs on the parent table (conparentid = 0), both ON DELETE NO ACTION — where a namespaces cascade would hide	TestContainerRemoteImagesConstraints_AllFKsAreNoAction
DM-24	The migration applies, reverts, and replays cleanly with no orphan residue	TestMigrations_UpDownUp + assertNoApplicationSchemaResidue (shared, format-neutral, pre-existing)
DM-25	Down lock budget: -- +goose NO TRANSACTION, no DO-block batching, at most one DETACH or DROP per statement	TestContainerRemoteRepositoriesSchema_DownLockBudget/create_container_remote_images (static parse)
DM-26	Down reverses every Up object: 65 DROP TABLEs naming the parent and each pNN, every one guarded by IF EXISTS so an interrupted rollback replays, and zero DETACH	TestContainerRemoteRepositoriesSchema_DownReversesEveryUpObject/create_container_remote_images (static parse)

The DM-20 row is narrower than it looks like it should be. The suite asserts the SQLSTATE but not the constraint name, because fk_container_remote_images_container_remote_repository_id sorts ahead of fk_container_remote_images_namespace_id_namespaces and so is always the one reported. The namespaces FK's presence and its NO ACTION are covered by DM-23 instead.

Acceptance criteria

Only one of the spec's 111 criteria is Step 1's, per the plan's acceptance-criterion ownership table; every other group is behavior owned by a later step.

#	Criterion	Tests
Download signals 5	The retention scan has an index to use: container_remote_images carries (namespace_id, container_remote_repository_id, last_downloaded_at NULLS FIRST)	DM-7. The criterion's container_remote_manifests half is 1c's.

Group	Criteria	Owner	This MR's contribution
Auth-challenge and token-exchange	1-27	Steps 4, 5, 9, 12, 13, 17	Not this MR. Its columns are all on container_remote_repositories (1a).
Token caching	1-8	Steps 5, 12	Not this MR. Nothing in this spec persists a token or a scope.
Manifest and blob proxy	1-42	Steps 3, 8, 10, 11, 13, 14, 15, 16 (15, 30: S12)	Schema precondition only: criterion 42 commits a container_remote_images parent in the fill transaction, and DM-6/DM-16 are the index rules that let a first-time and a re-cached image both get one.
Tag listing and referrers	1-9	Steps 3, 16	Not this MR. Criterion 9 asserts these routes write no container_remote_images row.
Download signals	1-4	Steps 11, 14, 15 (all gated on S18 for the counter)	Not this MR (handler-side bumps). DM-5 is what keeps their max(existing, NOW()) writes meaningful: a DEFAULT NOW() column would make every row look freshly pulled.
Health probe	1-2	Step 6	Not this MR (columns on 1a's table).
Cache integrity and body caps	1-8	Steps 8, 12, 14, 15, 16	Not this MR (fill and cap behavior). Criteria 1 and 2 assert no container_remote_images parent is written on a discarded fill.
Error mapping	1-10	Steps 5, 13	Not this MR (status and envelope mapping).

Error cases

Condition group	Owner	Tests
Every row of the spec's Error Cases table — 405 on write verbs and upload routes, challenge/realm faults, token-exchange transport and status outcomes, upstream 401/403/404/429/5xx, digest mismatch (envelope and streamed), unclassifiable manifest payload, DIGEST_INVALID, tag-grammar 404, content-negotiation 404, transport failure with and without a cache entry, body-cap overrun, Content-Encoding despite identity, NAME_INVALID, NAME_UNKNOWN, unsatisfiable Range	Steps 3, 5, 8, 10, 12-16 / S12 / S13	Not this MR. Every row is handler or fetch behavior; none is a schema constraint.

Security considerations

#	Concern	Owner	Tests
S-1	Token and credential hygiene	S13 / Steps 5, 9, 12, 17	Not this MR. container_remote_images carries no credential column.
S-2	Auth-challenge trust (realm/service from upstream)	S13 security covers / Steps 4, 12	Not this MR.
S-3	Cross-origin redirect credential stripping	S13 upstream HTTP client	Not this MR.
S-4	Outbound path-segment safety	Step 3 / Steps 14-16	Not this MR. name takes length bounds here (DM-13, DM-14); the OCI <name> grammar is enforced in Go before a name reaches this column.
S-5	No amplification via retries	Step 12	Not this MR.
S-6	Upstream auth failures do not leak into client auth	Step 13	Not this MR.

On the size of this MR

This MR is roughly 2,100 reviewable LOC against the 500 LOC guideline in docs/dev/development-model.md. The plan anticipated this shape and its Seam interrogation section budgets about 500 lines per migration for the remote schema steps.

Where the lines are:

• The migration is about a quarter of the reviewable diff, and roughly 130 of its lines are the mechanical 64 CREATE TABLE ... PARTITION OF and 65 DROP TABLE statements. CONCURRENTLY is not available on a partitioned parent and a DO $$ ... LOOP block would put all 65 statements in one transaction, which is the lock accumulation the layout avoids — so the statements are written out.
• structure.sql and the two jet files are generated and excluded from the count already; they add roughly 1,900 further lines to the raw diff.
• The two new suites are the bulk of the rest, and are heavily doc-commented by the conventions this package already follows.

Splitting further would mean splitting one table's schema from its own tests, or splitting the partitions from the parent. The plan's shape is one table per MR, and this is that table.

End-to-end scenario catalogs

No scenario added or invalidated. This step ships schema only: nothing reads or writes container_remote_images until Step 8 and Step 11, so there is no observable client behavior for a scenario to cover.

Both container catalogs — docs/testing/e2e/oci.md and docs/testing/e2e/docker.md — already list "Virtual and remote (proxy/cache) repositories" under "Out of scope until the capability ships", and that stays accurate until the read paths land. The plan assigns the catalog change to Step 18, the hermetic proxy harness: it moves remote out of that list and adds pull-through scenarios for a cache miss, a cache hit, a stale-tag revalidation, and a token-auth upstream, in both catalogs. A table with no reader does not change what a client can observe.

Related to #288