feat(maven): maven remote packages schema (S14 Step 2)

What

Adds the maven_remote_packages table — the package-level cache table for kind=2 (remote) Maven repositories, child of Step 1's maven_remote_repositories — as Step 2 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 minimum coordinate length the migration enforces.

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_packages 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_repository_id, namespace_id) → maven_remote_repositories(id, namespace_id) with ON DELETE NO ACTION per ADR-007's artifact-table rule (the cascade stops where user data begins); namespace_id FK to namespaces(id), also NO ACTION.
• CHECK (char_length(group_id) >= 1 AND char_length(group_id) <= 255) and CHECK (char_length(artifact_id) >= 1 AND char_length(artifact_id) <= 255) — the minimum length 1 bound the S14 data model now records.
• Partial unique (namespace_id, maven_remote_repository_id, group_id, artifact_id) WHERE soft_deleted_at IS NULL — rejects a duplicate live coordinate and admits re-caching after soft deletion.
• Full, non-partial (namespace_id, maven_remote_repository_id) index — the spec-required FK-coverage index, because the partial unique cannot back the referential-integrity check on a parent delete (a soft-deleted child still blocks).

No created_at: the spec assigns provenance timestamps to maven_remote_versions (Step 3), not to the package row.

Tests

maven_remote_packages_schema_integration_test.go (integration tag; raw SQL is permitted in the migrations package to assert constraints). 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 1, no intermediate FK error	_FKRejectsAbsentRemoteRepository; every constraint test seeds through Step 1's table
Partial unique rejects duplicate live coordinate	_PartialUniqueRejectsDuplicateLive (SQLSTATE 23505), _PartialUniqueScopedPerRepository
Admits re-caching after soft delete	_PartialUniqueAllowsRecachingAfterSoftDelete
Full non-partial FK-coverage index, spec columns	_FKCoverageIndexNonPartial; RI behavior _RemoteRepositoryDeleteBlockedByLiveChild / _...SoftDeletedChild
Partitioning, PK, column set	_PartitionsByHashOfNamespaceID, _PrimaryKey, _Columns, _PartitionRoutingByHashOfNamespaceID
255 CHECKs at/over limit	_GroupAndArtifactLengthCHECKs (SQLSTATE 23514)
Coordinate CHECKs reject empty	_GroupAndArtifactNonEmptyCHECKs (SQLSTATE 23514)
Namespace FK NO ACTION	_NamespaceFKIsNoAction

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

The suite was diffed subtest-by-subtest against the npm remote packages equivalent and Step 1's repositories suite for dropped coverage; every mirrored subtest class has a counterpart, including the at-limit boundary pin and both soft-deleted-child RI pins.

MR size

~1348 lines of reviewable code (500-line migration + 848-line test), over the 500 LOC ceiling in the development model, which asks for a split or a justification here.

Justification: as in Step 1, roughly 440 of the migration's 500 lines are the mechanical 64-partition CREATE DDL and its DETACH/DROP reverse — 64 near-identical statements, reviewable as a block once the first is read. The generated jet types and the structure.sql dump are excluded from the count as generated artifacts. The novel surface is the table body (~40 lines) and 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 Step 1: 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.
• The final commit amends the merged plan: Steps 2–4's Files entries now declare the mavenRemoteCoreMigrationTokens append to Step 1's shared static Down-test slice, which the implementation requires and the pre-push step validation surfaced as undeclared 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
20260730120000_create_maven_remote_packages.sql	OK (1.32s / 6.48s)	OK (514.77ms / 6.73s)	OK (521.76ms / 6.69s)

Migration notes:

• Up/down asymmetry: rollback is 5-13x slower than apply across all PG versions (6.48s-6.73s down vs 515ms-1.32s 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 the equivalent npm migration (4.42s-6.72s down). The Up direction - the boot-relevant phase - peaks at 1.32s, well within the 5-minute boot budget.
• PG 16 apply (1.32s) is ~2.5x the PG 17/18 applies (~520ms). The sibling maven_remote_repositories migration in the same jobs shows the same ratio (1.18s vs 482-576ms), so this reads as PG 16 runner variance rather than anything migration-specific.

Related to #286 (closed)