feat(maven): maven remote versions schema (S14 Step 3)

What

Adds the maven_remote_versions table — the version-level cache table for kind=2 (remote) Maven repositories, child of Step 2's maven_remote_packages — as Step 3 of the S14 Maven remote vertical slice. One goose migration, the regenerated jet types and structure.sql dump, an integration test suite asserting the schema shape and every constraint's accept and reject paths, and an S14 spec amendment recording the one index the migration adds beyond ADR-007.

No Go production code and no behavior change: nothing reads or writes this table until Step 8 (remote cache store Lookup) and Step 9 (cache-fill writes).

Schema

Follows the npm_remote_versions boilerplate and Step 1's DETACH-then-DROP Down shape:

• PARTITION BY HASH (namespace_id) × 64, children in the partitions schema.
• PK (id, namespace_id); app-generated UUIDv7, no sequence or default.
• Composite FK (maven_remote_package_id, namespace_id) → maven_remote_packages(id, namespace_id) with ON DELETE NO ACTION per ADR-007's artifact-table rule; namespace_id FK to namespaces(id), also NO ACTION.
• CHECK (char_length(version) >= 1 AND char_length(version) <= 255) — the same minimum-length bound Step 2 recorded for the coordinate columns.
• size_bytes bigint NOT NULL DEFAULT 0 and created_at timestamptz NOT NULL DEFAULT NOW(); last_downloaded_at and soft_deleted_at nullable.

Six indexes:

Index	Columns	Purpose
Unique	(namespace_id, maven_remote_package_id, version) WHERE soft_deleted_at IS NULL	Cached-version lookup; admits re-caching after soft delete
Index	(namespace_id, maven_remote_package_id, size_bytes DESC) WHERE soft_deleted_at IS NULL	Size-ordered version listing (S17)
Index	(namespace_id, maven_remote_package_id)	Full, non-partial FK coverage — the partial indexes exclude soft-deleted rows, so the RI check on a parent delete cannot use them
Index	(namespace_id, soft_deleted_at DESC) WHERE soft_deleted_at IS NOT NULL	Trash listing (S20)
Index	(namespace_id, created_at DESC)	Chronological provenance scans, unconditional
Index	(namespace_id, maven_remote_package_id, last_downloaded_at NULLS FIRST) WHERE soft_deleted_at IS NULL	Cache-retention sweep evaluation

The first five come straight from ADR-007 and the merged S14 data model. The sixth is the only shape this MR adds beyond them, and it is byte-identical to hosted index_maven_versions_on_ns_id_pkg_id_last_downloaded_at, predicate and NULLS FIRST included. NULLS FIRST is load-bearing: it groups never-downloaded versions with the oldest rows, so one range scan returns both, where NULLS LAST would sort them past every dated row and hide them from the sweep.

Tests

maven_remote_versions_schema_integration_test.go (integration tag; raw SQL is permitted in the migrations package to assert constraints). 18 test functions; every acceptance clause has a named asserting test:

Acceptance clause	Test
Applies cleanly, 64 partitions	_TableAndPartitionsExistPostUp
Reverts and replays cleanly	package-level TestMigrations_UpDownUp, plus _DownLockBudget and _DownReversesEveryUpObject via the shared mavenRemoteCoreMigrationTokens extension
In order after Step 2, no intermediate FK error	_FKRejectsAbsentPackage, _FKRejectsAbsentNamespace; every constraint test seeds through Steps 1 and 2
Partial unique admits re-caching after soft delete	_PartialUniqueAllowsRecachingAfterSoftDelete
Partial unique rejects duplicate live coordinate	_PartialUniqueRejectsDuplicateLive (SQLSTATE 23505), _PartialUniqueScopedPerPackage
size_bytes defaults 0, created_at defaults NOW()	_ColumnDefaults
All six indexes, spec columns and predicates	_PartialUniqueIndexShape, _NonUniqueIndexShapes
Partitioning, PK, column set	_PartitionsByHashOfNamespaceID, _PrimaryKey, _Columns, _PartitionRoutingByHashOfNamespaceID
version length CHECK at and over limit	_VersionLengthCHECK (SQLSTATE 23514)
version CHECK rejects empty	_VersionNonEmptyCHECK (SQLSTATE 23514)
Namespace FK NO ACTION	_NamespaceFKIsNoAction
Parent delete blocked by child	_PackageDeleteBlockedByLiveChild, _PackageDeleteBlockedBySoftDeletedChild

Constraint-rejection assertions pin the exact SQLSTATE (23514, 23503, 23505) rather than "an error occurred", as in Steps 1 and 2.

_ColumnDefaults asserts created_at.After(time.Now().Add(-time.Minute)) rather than just non-NULL, so a fixed-past-timestamp default would fail. _NonUniqueIndexShapes pins the non-unique count at five, making any added or dropped index fail the suite alongside the unique-shape test.

The suite was diffed subtest-by-subtest against the npm remote versions equivalent and Step 2's packages suite for dropped coverage. Every mirrored subtest class has a counterpart; Step 2's _FKCoverageIndexNonPartial is folded into _NonUniqueIndexShapes with all three of its assertions preserved, and _ColumnDefaults is net-new for this table's two defaults.

MR size

~1,431 lines of reviewable code (522-line migration + 906-line test + 3 lines across two existing test files), over the 500 LOC ceiling in the development model, which asks for a split or a justification here.

Justification: as in Steps 1 and 2, roughly 450 of the migration's 522 lines are the mechanical 64-partition CREATE DDL and its DETACH/DROP reverse — 128 near-identical statements, reviewable as a block once the first is read. The generated jet types and the structure.sql dump are excluded as generated artifacts. The novel surface is the table body and its six indexes (~72 lines) plus the test suite. Splitting the partition DDL from the table it partitions would produce a non-applying migration, so the step is already at its minimum reviewable size.

End-to-end scenario catalogs

No scenario added or invalidated. docs/testing/ holds no Maven catalog yet — authoring the first one is a separate docs concern — and 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 S14 read paths.

Conformance

Not applicable: this step implements no Maven protocol behavior. Conformance runs against the read paths landing in Steps 11 and 14 through 16.

Notes for reviewers

• The migration declares -- +goose NO TRANSACTION for the same reason as Steps 1 and 2: a single transaction would hold all 128 AccessExclusiveLocks until commit; the per-statement layout releases them as it goes. The accepted cost — a partway-interrupted Down is not replayable — is documented in the migration header and tracked in #448.
• The final commit corrects the two scan indexes. They were first written as (namespace_id, soft_deleted_at DESC, id) and (namespace_id, created_at DESC, id), adding a trailing id keyset tiebreaker. That makes both a mixed (sort_col DESC, id ASC) shape, and 20260730170000_add_artifact_read_keyset_indexes.sql records the rule: ROW(sort_col, id) stays a single index range only when both keys share a direction, so a mixed index forces the expanded OR form and a post-scan filter. The tiebreaker therefore did not deliver the stable cursor it was added for. Both indexes now match hosted maven_versions exactly.
• The cursor need is real but deferred. A paginated namespace-wide trash list does want a tiebreaker, because the cache soft-delete cascade stamps one NOW() across a package and ties are the common case. S20 owns that query and should add the uniform (namespace_id, soft_deleted_at, id) WHERE soft_deleted_at IS NOT NULL index with EXPLAIN evidence — the way index_maven_versions_on_ns_id_pkg_id_id arrived for ListLiveVersions and the S17 Phase 3 indexes arrived in 20260730170000. Nothing queries this table yet, so there is no query to regress in the meantime.
• The spec amendment restates the divergence preamble from two to five. Only the retention-sweep index row is a new schema divergence; the minimum-length bound and the non-partial FK-coverage index were already in the merged spec's tables but uncounted by its preamble, so that part of the edit is a docs-accuracy correction rather than new scope.

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.

Migration	PG 16	PG 17	PG 18
20260731130000_create_maven_remote_versions.sql	OK (1.72s / 6.27s)	OK (940.31ms / 6.94s)	OK (996.52ms / 6.91s)

Migration notes:

• Up/down asymmetry: rollback is 3.6-7.4x slower than apply across all PG versions (6.27s-6.94s down vs 940ms-1.72s up). This is expected for the 64-partition DETACH-then-DROP Down shape and is consistent with Step 1's maven_remote_repositories migration (4.36s-7.17s down) and Step 2's maven_remote_packages (6.48s-6.73s down). The Up direction — the boot-relevant phase — peaks at 1.72s, well within the 5-minute boot budget.
• Apply is roughly 1.8x Step 2's on PG 17 and 18 (940ms and 997ms against 515ms and 522ms). This table builds six parent-level indexes where maven_remote_packages builds two, and each recurses into all 64 partitions, so the extra time is index builds rather than the table body.
• PG 16 apply (1.72s) is ~1.8x the PG 17/18 applies (~940ms-997ms). Steps 1 and 2 show the same ratio in their own jobs, so this reads as PG 16 runner variance rather than anything migration-specific.

Related to #286 (closed)