feat(npm): npm remote cache-table schema (S15 Step 2)

📌 This part

S15 Step 2 (npm remote): the two npm-remote cache-content tables and their schema-invariant integration tests. Schema-only — no production Go logic; the read/write paths that fill these caches land in later steps.

These tables are the content cache for npm remote (proxy) repositories: rewritten upstream packument documents (one row per package per kind) and cached tarballs (one per version). They complete the remote schema started in Step 1 (npm_remote_repositories / _packages / _versions). Both reference the S06 blob-storage tables through the active composite blob-FK shape, and both add the revalidation columns (upstream_checked_at, upstream_etag) that later steps use to decide when to re-fetch from upstream — in place of the hosted npm_metadata_files.expires_at.

• npm_remote_metadata_files: cached packument documents, one row per package per kind. kind ∈ {0 full, 1 dist-tags, 2 abbreviated}; unique (namespace_id, npm_remote_package_id, kind), non-partial (it also serves as the package-FK cover). No soft_deleted_at — metadata is replaced in place. kind=2 is added by this spec pending an ADR-007 amendment to the remote block (the same divergence S11 carries for hosted npm).
• npm_remote_files: cached tarballs, one per version + file name. Partial unique (namespace_id, npm_remote_version_id, file_name) WHERE soft_deleted_at IS NULL allows re-caching a tarball after soft deletion, plus a full non-partial (namespace_id, npm_remote_version_id) FK-coverage index — the partial unique excludes soft-deleted rows and so cannot serve the RI check on an npm_remote_versions delete (mirrors the Step 1c versions FK-cover index).
• Both tables: HASH(namespace_id) × 64 partitions; PK (id, namespace_id); active composite FKs to blob_storage_attachments (id, namespace_id, sha256) and blob_storage_blobs (namespace_id, sha256) — active from creation since S06 landed, no deferral; blob_sha256 NOT NULL + 32-byte octet_length CHECK; 255-char CHECKs on the text columns; direct namespace FK ON DELETE NO ACTION.
• Idempotent Down: each Down drops its 64 partitions directly (DROP TABLE IF EXISTS partitions.<t>_pNN) then the parent, under +goose NO TRANSACTION — one relation-pair per statement to stay within max_locks_per_transaction, and re-runnable after a mid-sequence interruption. (This deviates from the DETACH+DROP shape in the earlier partitioned migrations; a family-wide follow-up to align the siblings is worth filing.)
• jet + structure.sql: regenerated for the two tables; the large structure.sql diff is churn-only — the two tables sort alphabetically before npm_remote_packages, so the dump re-emits the partition blocks in place (nothing existing is removed or reordered).
• checksum test: head-version pin bumped to the new head migration.
• spec: documents the new npm_remote_files FK-coverage index.

Testing — integration tests in internal/datastore/migrations (behind the integration build tag; run in test:integration):

Invariant	Test(s)
Both tables exist post-Up as HASH(namespace_id)×64 partitioned tables; PK (id, namespace_id)	TestNPMRemoteCacheSchema_TablesAndPartitionsExistPostUp, _PartitionByHashOfNamespaceID, _PrimaryKeys
Exact column set/type/nullability (metadata has no soft_deleted_at, files does; both carry the revalidation columns)	TestNPMRemoteCacheSchema_Columns, _UpstreamCheckedAtDefaultAndEtagNullable
Active composite attachment FK + active blob_storage_blobs FK (success w/ parent, 23503 absent)	TestNPMRemoteCacheConstraints_{Metadata,Files}ActiveCompositeFKToAttachments, _ActiveBlobStorageBlobsFKPresent
blob_sha256 NOT NULL + 32-byte CHECK; 255-char CHECKs; kind ∈ {0,1,2}	TestNPMRemoteCacheConstraints_BlobSha256NotNullAnd32ByteCHECK, _LengthCHECKsRejectOverflow, _MetadataKindRangeCHECK
Parent-FK ordering (23503 on absent package/version); namespace FK ON DELETE NO ACTION	TestNPMRemoteCacheConstraints_{MetadataFKRejectsAbsentRemotePackage,FilesFKRejectsAbsentRemoteVersion,NamespaceFKsPresent}
Unique keys: metadata non-partial per (pkg, kind); files partial per (version, file_name) with re-cache-after-soft-delete	TestNPMRemoteCacheConstraints_{MetadataUniquePackageKind,MetadataUniqueScopedPerPackage,FilesPartialUniqueNameAllowsResurrection,FilesPartialUniqueScopedPerVersion}, TestNPMRemoteCacheSchema_MetadataUniqueIndexNonPartial
Secondary + FK-coverage indexes present; partition routing agrees with satisfies_hash_partition	TestNPMRemoteCacheSchema_SecondaryIndexesExist, _PartitionRoutingByHashOfNamespaceID
Down lock-budget (NO TRANSACTION, no DO-block, one DROP per statement); apply/revert/replay clean	TestNPMRemoteCacheSchema_DownLockBudget, TestMigrations_UpDownUp (shared)

Verified clean on this branch: build, go vet (default + -tags integration), go test -race -short, goimports, and golangci-lint (v2.12.0), plus the full -tags integration suite (22 Step 2 tests + TestMigrations_UpDownUp reversibility walk) against a live PostgreSQL.

🔗 References

• Plan: docs/plans/2026-07-15-npm-remote.md — Step 2
• Spec: docs/specs/S15-npm-remote.md — Data Model (npm_remote_metadata_files, npm_remote_files)

Related to #339 (closed)