feat(npm): npm_files, npm_tags, npm_metadata_files schema (S11 Step 2)

What

Second half of the npm-local schema (S11 Step 2): three HASH(namespace_id) / 64-partition tables, mirroring the merged OCI container_blobs / container_manifests shape.

• npm_files — file rows. Active composite FK (blob_storage_attachment_id, namespace_id, blob_sha256) → blob_storage_attachments(id, namespace_id, sha256); blob_sha256 NOT NULL + 32-byte octet_length CHECK; partial-unique (namespace_id, npm_version_id, file_name) WHERE soft_deleted_at IS NULL (resurrection after soft-delete); created_at for the Step 9 Last-Modified derivation.
• npm_tags — dist-tag rows. Unique (namespace_id, npm_package_id, name).
• npm_metadata_files — precomputed packument cache. kind smallint (0 full / 1 dist-tags / 2 abbreviated) + expires_at; unique (namespace_id, npm_package_id, kind).

Both storage tables also carry the ADR 007 (namespace_id, blob_sha256) reverse-lookup index for digest-keyed cross-format checksum search.

Deferred: the blob_storage_blobs FK is omitted with an inline TODO(S06-Step-6), exactly as the merged oci_container_blobs / oci_container_manifests migrations do (the !276 (merged) precedent). Only the referential constraint is deferred — blob_sha256 already ships NOT NULL + a 32-byte CHECK.

package_json stays plain jsonb; its strict-schema allow-list validation is application-layer (the Step 5 datastore validator), not a DB CHECK — pg_jsonschema is not on CloudSQL's supported extensions (follows the merged OCI annotations precedent).

Plan / spec

• Plan: docs/plans/2026-05-11-npm-local.md — Step 2 (plan MR already merged on main).
• Spec: docs/specs/S11-npm-local.md; data model per ADR 007.

Tests

Integration tests in package migrations (//go:build integration → test:integration), consolidated into the existing npm_schema_integration_test.go and reusing the Step 1 / OCI shared helpers. The full spec-coverage map is in the test(npm): … commit body. Coverage includes: partition strategy/count, PK shape, explicit-sequence design, both active composite FKs (present → success, absent → 23503), the npm_tags → npm_versions and npm_metadata_files → npm_packages FK rejections, deferred-FK absence, blob_sha256 CHECK/NOT NULL, file_name/name/kind CHECKs (23514), dist-tag and per-kind uniqueness (23505), partial-unique resurrection, index-existence assertions (the reverse-lookup indexes plus index_npm_files_on_ns_id_bsa_id and index_npm_tags_on_ns_id_version_id), FK-ordering, apply/revert/replay-in-FK-order, and the per-migration Down lock-budget. Validated locally against PostgreSQL 16 via testcontainers.

Reviewer note — diff size

The raw diff is large (~21k lines) but ~99% is generated or mechanical:

• structure.sql (regenerated pg_dump) and the jet model/table types are generated artifacts.
• Each migration is ~90% mechanical 64-partition CREATE / DETACH / DROP expansion (the established OCI pattern); ~40 hand-authored lines of schema per file.

Hand-authored reviewable content is roughly the ~1.4k-line integration test (Step 2 tests consolidated into npm_schema_integration_test.go) plus ~120 lines of distinct SQL schema and the plan-doc edit — ~1.6k "thinking" LOC.