feat(oci): container_remote_repositories schema (S16 Step 1a)

What

Adds the container_remote_repositories table — the root cache table for kind=2 (remote) container repositories — as the first 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 an integration test suite 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 6 (remote repository finder) and Step 9 (credential and auth_status writes).

container_remote_images (1b) and container_remote_manifests (1c) follow in their own MRs. Each takes the next free timestamp after the previous 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 three 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 two non-obvious ones — child index naming and 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, unique (namespace_id, repository_id), a composite FK to repositories(id, namespace_id) ON DELETE CASCADE, and a namespace_id FK to namespaces(id) with NO ACTION. Eight CHECK constraints. All of it reads off the migration.

Three things the diff cannot show:

1. Container-only columns. auth_status memoizes the OCI auth-challenge verdict (0=unknown, 1=none, 2=bearer) and has no equivalent on the npm or Maven remote-repository tables, and no ADR-007 equivalent at all — neither the column nor either of its two CHECKs. #30 carries that amendment. There is no metadata_cache_validity_hours: container has no separate metadata tier the way Maven's maven-metadata.xml does, so cache_validity_hours is the only freshness window.
2. last_health_status gains a range bound the siblings lack. S13 defines the column; the probe writes it, so it is service-written on the same terms as auth_status. The npm and Maven tables declare it unbounded and are not backfilled here — both are empty pre-production, so the change is cheap, but those tables belong to the npm and Maven slices. #486 tracks it; until it lands the three tables disagree. The absence there traces to ADR-007 rather than to a decision, so bounding it here contradicts nothing.
3. Deliberate absences, asserted by tests. The interim tmp_plaintext_username / tmp_plaintext_password text columns stand in for ADR-007's encrypted_* bytea shape, matching what merged Maven and npm remote carry; encryption at rest is GA-blocking in #417. The 2048-character credential cap and the RFC 7617 colon rule stay Go-side per ADR-007, and a test asserts their absence from the catalog so a later DB-side cap cannot land unnoticed. The https-scheme and resolved-address rules on the discovered realm likewise stay on the outbound request path, not in the schema.

Down section drops each partition directly

No DETACH PARTITION pair. DROP TABLE auto-detaches, and IF EXISTS on every statement leaves the section replayable after a mid-sequence interruption — which a DETACH/DROP pair cannot be, since DETACH takes no IF EXISTS guard and a retry fails on the partitions already detached. The pair also holds far more FK-target locks; the migration states the shape and the plan derives the counts.

#448 tracks converting the older migrations to this shape. main already carries both, so this file starts on the one that issue moves to rather than adding another for it to fix.

Cross-cutting edits

Two, both in test helpers, no production code. They are why a schema MR touches sibling suites:

1. assertDownDropsEveryPartitionAndParent takes a down-section style. It asserted exactly 64 DETACHes, so a migration dropping them had to widen the helper in the same MR or every format's schema suite fails. The Maven remote suite passes detachThenDrop and keeps every assertion it had; the container suite passes dropWithoutDetach, which asserts zero DETACH rather than skipping the check, so a later container remote table cannot quietly regain the pair. The helper also now requires IF EXISTS on every DROP, which both Maven remote migrations already satisfy.
2. One shared readMigrationFile in schema_helpers_test.go, replacing three per-format copies — readOCIMigrationFile, readNPMMigrationFile and readMavenMigrationFile — with keysOf moving across alongside it. Raised in review: this suite's Down-walk reached into the hosted OCI suite file for its reader, which is the coupling schema_helpers_test.go's header exists to prevent. Every schema suite that walks a Down section now shares one reader, so the edit touches the hosted npm, npm remote, npm remote Step 2, hosted OCI, hosted Maven and Maven remote suites — a wider surface than a schema MR would otherwise carry, accepted deliberately rather than deferred, because 1b and 1c would each add another consumer of a helper owned by the wrong file. The Maven copy came in a second round: its bare-substring match looked like a blocking difference, but all six Maven tokens match uniquely under token + ".sql", so the shared reader is a drop-in and the stricter of the two, since it also skips directory entries.

Plan amendment

docs/plans/2026-07-30-container-remote.md described Step 1's rollback as "the symmetric DETACH/DROP down path", written before #448 was filed. That phrase is replaced with the drop-only shape and its reason, both cross-cutting edits are recorded as named accepted ones with the reader hoist called out as the wider of the two, the child-index-naming derivation moved in from this description, and Step 1's Files entry now names the mechanical companions that adding any migration forces — the two generated jet files, the table_use_schema.go line, and the knownHeadVersion bump — so 1b, 1c, and 2a–2c do not each re-litigate whether they belong.

Tests

Three files (integration tag; raw SQL is permitted in the migrations package to assert constraints): container_remote_test_helpers_test.go for the shared fixtures, container_remote_schema_integration_test.go for catalog shape, partition routing and the static Down checks, and container_remote_constraints_integration_test.go for constraint rejections. Raised in review: one file would have passed 1000 lines at this table and grown past 3000 by the third, so the split lands here rather than being promised for 1b. Every acceptance clause has a named asserting test:

Acceptance clause	Test
Applies cleanly	_TableAndPartitionsExistPostUp
Reverts and replays cleanly	package-level TestMigrations_UpDownUp, plus _DownLockBudget and _DownReversesEveryUpObject as the static per-migration half
structure.sql regenerates with no drift	CI db:structure-check
Credential CHECK all-or-none rejects a half-set pair	_CredentialsAllOrNoneCHECK (6 polarities incl. both-empty-strings)
auth_status = 2 with null auth_url rejected	_AuthStatusAuthURLCHECK (bearer_without_auth_url_rejected)
last_health_status accepts 0, 1, 2 and rejects outside	_LastHealthStatusRangeCHECK (all three defined values as positive hits, since each is a state the probe writes; 3, -1, 99 rejected)
auth_status outside 0, 1, 2 rejected	_AuthStatusRangeCHECK (3, -1, 99, each against a null auth_url so the pair CHECK admits it and only the range CHECK can reject — verified by deleting the constraint and watching all three inserts succeed)
Non-null auth_url at any other status rejected	_AuthStatusAuthURLCHECK (unknown_with_auth_url_rejected, none_with_auth_url_rejected) — both defined non-bearer statuses, so "any other" is exhausted rather than sampled
No DB-level credential validation	_CredentialColumnsCarryNoDBLevelValidation
No metadata_cache_validity_hours	_NoMetadataCacheValidityColumn
cache_validity_hours >= 0 accepts 0	_CacheValidityHoursCHECK
1024-char url / auth_url caps	_URLLengthCHECK, _AuthURLLengthCHECK
Empty auth_url rejected at auth_status = 2	_AuthURLLengthCHECK (empty_rejected; deleting the constraint makes it fail while the two cap cases still pass)
Empty url accepted, pending #486	_URLLengthCHECK (empty_accepted_no_lower_bound, pinning the deliberate asymmetry)
Partitioning, PK, unique index, routing	_PartitionsByHashOfNamespaceID, _PrimaryKey, _Columns, _ColumnDefaults, _UniqueRepositoryIDIndex, _UniqueRepositoryIDRejectsDuplicate, _PartitionRoutingByHashOfNamespaceID
Both FK directions enforce	_FKRejectsAbsentRepository, _FKRejectsAbsentNamespace, _RepositoryFKCascadesOnRepositoryDelete, _NamespaceFKIsNoAction (pins confdeltype='a')

Spec: S16 container remote. Its acceptance criteria are an auto-numbered Markdown list with no stable ids, so rows cite an item's text rather than a number the next insertion shifts.

Error cases

S16 error case	Test
Every row in S16 ## Error Cases	Out of scope for Step 1a. All of them are request-path HTTP status mappings — upstream 401/403 propagation, digest mismatch, unknown reference — and this step ships no request path. Steps 10 through 16 carry them.

Security considerations

S16 security consideration	Test
Token and credential hygiene, over tmp_plaintext_username / tmp_plaintext_password	The columns ship here, the hygiene rules do not act on them until a reader exists. What this step pins: _CredentialsAllOrNoneCHECK (no half-set pair can be stored) and _CredentialColumnsCarryNoDBLevelValidation (the 2048-char cap and RFC 7617 colon rule stay Go-side, asserted absent from the catalog so a later DB-side cap cannot land unnoticed). Encryption at rest is GA-blocking in #417.
Auth-challenge trust — SSRF covers on the discovered realm	Storage-side only here: auth_url takes a database length cap at both ends, so an empty or arbitrarily long realm cannot be persisted. The scheme, port, and resolved-IP checks belong to the outbound request path (S13 covers, already shipped) and are not re-asserted by a schema test.
Auth-challenge trust — credential exfiltration to an upstream-named realm host	Not addressed by this step and not addressable in the schema: the realm-host allowlist is GA-blocking and tracked confidentially. No column this migration adds bounds it.
Cross-origin redirect credential stripping	S13-owned, in the upstream HTTP client. Not tested in this MR.
Outbound path-segment safety	Request-path behavior, Steps 10 through 16. Not tested in this MR.
No amplification via retries	Token-caching behavior, Step 9. Not tested in this MR.
Upstream auth failures do not leak into client-facing auth	Response-shaping behavior, Steps 10 through 16. Not tested in this MR.

Constraint-rejection assertions pin the exact SQLSTATE (23514, 23503, 23505) rather than "an error occurred": before the migration exists every insert fails with 42P01, which a bare error assertion would accept.

All three auth_status values and both container parent formats (docker, oci) are covered as positive hits, not one positive plus a negative. The suite was diffed subtest-by-subtest against the Maven remote equivalent for dropped coverage; every Maven subtest scoped to the repositories table has a counterpart, except SnapshotFlagAcceptsExplicitTrue (a Maven-only column) and the metadata_cache_validity_hours cases, whose column this table does not have and whose absence _NoMetadataCacheValidityColumn asserts directly.

One deliberate deviation from go-testing.md: every CHECK-constraint table is an inline literal of a named type passed to one shared runner, rather than a local slice named tests per test. Eight tables differ only in their insert statement and their bind values, so one assertion body beats eight copies of the Maven loop, and the runner's doc comment says so. Raised in review: this was originally justified as dupl avoidance, which was never a real reason — .golangci.yaml sets no build tags, so dupl never analyzes these //go:build integration files in CI at all.

MR size

Roughly 4,800 insertions across 18 files. Almost none of it is hand-written:

Insertions	File	Kind
~2,200	structure.sql	Regenerated pg_dump, one parent plus 64 partitions
~1,670	the three container remote suite files	Test
~500	the migration	Mostly mechanical partition DDL; roughly a third of the file is comment
<200	schema_helpers_test.go	Shared helper, widened
<200	jet model and table types	Generated by db:jet-gen
<200	plan and spec	Prose
<100	Maven and npm test call sites, checksum test	Consequences of the two above

The three largest rows are rounded and the rest are upper bounds, on purpose. An exact insertion count in prose is invalidated by the next commit — including the commit that corrects it, which is how this section went stale three review rounds running.

The two largest contributors are not review surface. structure.sql is a dump regenerated by db:dump-structure and verified by CI's db:structure-check; it is never edited by hand and diffing it line by line finds nothing the migration does not already say. The jet types come from db:jet-gen and are checked by jet:generate-check. Most of the migration is the 64-partition CREATE and DROP statements — near-identical, reviewable as a block once the first is read.

That leaves the table body and its comments and the test suites as the surface that actually wants reading.

Why one MR

Splitting the partition DDL from the table it partitions produces a migration that does not apply. Splitting the regenerated dump or the jet types from the migration that causes them breaks db:structure-check and jet:generate-check, which exist to keep exactly those artifacts in the same commit as their schema change. So the floor for a partitioned-table migration is the whole set.

This is the shape every sibling schema MR took, and this one sits at the top of their range rather than outside it:

MR	Files	Insertions
!978 (merged) npm remote packages (S15 Step 1b)	7	2949
!977 (merged) npm remote repositories (S15 Step 1a)	7	3135
!1189 (merged) maven remote packages (S14 Step 2)	10	3377
!1120 (merged) maven remote repositories (S14 Step 1)	9	3759
!979 (merged) npm remote versions (S15 Step 1c)	8	4393
This MR	18	~4,800
!1044 (merged) npm remote cache tables (S15 Step 2)	12	28515

That makes this the second-largest of the seven, above !979 (merged). The per-file reasoning above does not depend on the ranking: each of these MRs is dominated by its own regenerated structure.sql for the same reason. Touching the plan and the spec alongside the migration also follows them: four of those six amended one or both in flight, and !1189 (merged) amended both, as this MR does.

The plan originally budgeted far less test than the ~1,670 here. The gap is the container-only surface Maven's 1a had no equivalent for — auth_status needs six accept/reject polarities against auth_url plus its own range cases, last_health_status needs all three defined values as positive hits, both parent formats need positive-hit coverage, and the absent metadata_cache_validity_hours needs an asserted absence — plus doc comments carrying the reasoning for each. The estimate was low; the coverage is not padding.

End-to-end scenario catalogs

No scenario added or invalidated. This step ships no request path, so there is no observable behavior to cover. Step 18's hermetic proxy harness is the automated coverage for the S16 read paths; when it lands it drops the "Virtual and remote (proxy/cache) repositories" bullet from the "Out of scope until the capability ships" list in both docs/testing/e2e/oci.md and docs/testing/e2e/docker.md.

Conformance

Not applicable: this step implements no Container/OCI protocol behavior. The conformance suite runs against the read and write paths that Steps 10 through 16 build.

Database Review Evidence

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.

Collected from pipeline 2732538482 run on the merge result of branch head 830c105d (a merge-result pipeline reports its own ephemeral merge commit, so its page shows a different SHA). A dated record, not the head pipeline: merge-request changes: rules match the whole MR diff, so db:migrate re-runs on every pipeline here even for a docs-only push. No commit since has touched the migration, so these are the timings for the SQL under review.

Migration	PG 16	PG 17	PG 18
20260804120000_create_container_remote_repositories.sql	OK (1.09s / 456.28ms)	OK (766.63ms / 522.53ms)	OK (280.86ms / 197.37ms)

Migration notes:

• The migration stays in boot rather than going out-of-band. Apply straddles the 1-second empty-database threshold on the slowest version from one run to the next — 1.09s here, 726ms on the previous run of the same DDL — so the call is made on what the cost is rather than on where a sample lands: it is fixed DDL — 65 CREATE TABLE statements plus one unique index recursing into 64 empty partitions — on a table that does not exist yet, so none of it scales with production row counts the way a backfill would. Against the 5-minute boot cap (lock-wait plus apply, see Time budget) that leaves effectively the whole window for lock-wait on the FK targets — namespaces, repositories, and each of repositories' 64 partitions, since that table is itself hash-partitioned and the composite FK reaches all of it. The migration documents that exposure at the statement that takes it.
• Rollback comes in under apply on every version, as expected: the Down section is 65 DROP TABLE statements with no index rebuild.
• The spread between PG versions is runner noise and is not worth reading as a version difference. Two pipelines have now run this identical DDL — the later one differs only in comment text — and the ordering reversed between them. PG 18 was the slowest on both phases in the first run and is the fastest in this one; PG 17 apply moved from 277.51ms to 766.63ms. Whole-suite rollback moved the same way, PG 18 from 189s down to 77s while PG 16 went from 53s up to 105s. Empty-database timings on shared runners carry that much run-to-run variance, so they are evidence that the migration applies and rolls back, not evidence about which version handles it better.
• Re-Up is OK on all three versions, so the Down section leaves a state the Up section re-applies cleanly.

Related to #288