feat(npm): npm local schema (S11 Step 1)

Dzmitry (Dima) Meshcharakourequested to merge
dm/npm-local-step-1-v3 into main

What

Step 1 of the npm-local plan: the first three npm schema tables, the regenerated go-jet types, and an integration test suite of schema invariants.

  • npm_repositories - per-format bind table over the foundation repositories table (1:1), mirroring the merged container_repositories.
  • npm_packages - buffered counter columns (versions_count, tags_count), scope, soft-delete, partial-unique on (namespace_id, npm_repository_id, name) WHERE soft_deleted_at IS NULL.
  • npm_versions - package_json jsonb NOT NULL, soft-delete, and the race-free version guard: partial-unique on (namespace_id, npm_package_id, version) WHERE soft_deleted_at IS NULL.

All three are hash-partitioned by namespace_id into 64 partitions per ADR 007, following the exact OCI migration boilerplate (goose -- +goose NO TRANSACTION, explicit per-partition CREATE/DETACH/DROP, partitions. schema, matching squawk-ignore-file headers).

Also regenerates structure.sql and the go-jet model/table types under internal/datastore/jet/..., and refactors the OCI schema test's pre-loop teardown from a brittle absolute-version pin to a token-based drainAbove(token) drain - generalized in this MR and now shared by the npm Down-walk too (the first npm migration landing on top of the container set broke the old pin).

Spec / plan

Deferred to Step 2

package_json ships as plain NOT NULL jsonb in Step 1; the allow-list CHECK and the 20 KB size cap are deferred to Step 2 (enforced at the publish handler), per the plan.

Tests

Integration suite npm_schema_integration_test.go (//go:build integration). Every Step 1 acceptance bullet is covered:

  • applies / reverts / replays cleanly; the three tables apply in FK order without intermediate FK errors;
  • two concurrent inserts of the same (namespace_id, package_id, version) resolve to one success and one unique_violation (23505) at commit time;
  • soft-delete + re-insert of the same version succeeds (partial-unique resurrection).

Plus shape guards: partition existence/strategy, PKs, columns/nullability, explicit-sequence shape, hash-partition routing, FK violations (23503), NOT NULL (23502), length CHECK violations (23514) on npm_packages and npm_versions, scope round-trip, counter defaults, and the down lock-budget invariant.

Review

Reviewed via /review-branch: APPROVE - 0 blocking, 0 warning, 6 observations (all deferred-by-design or process notes). Mechanical gates pass: build, vet (incl. -tags integration), lint, goimports, unit tests.

Reviewable size

The raw diff is large but dominated by generated content: structure.sql (pg_dump-style regen) + 7 go-jet DO NOT EDIT files + 64-partition DDL boilerplate. The human-reviewable surface is ~3,040 lines (3 migrations + the test file, incl. the review-driven length-CHECK tests, + the OCI refactor); distinct non-boilerplate migration content is ~30 lines per file.

Database Review Evidence

Note

Timings are from CI (db:migrate matrix jobs, goose verbose mode) against an empty database, in apply / rollback order per supported PostgreSQL version. Production-scale validation via Database Lab is not yet available. See Database review evidence in the dev docs for the matrix rationale, how to read the numbers, and the current limitations.

Migrations

Migration PG 16 PG 17 PG 18
20260603120000_create_npm_repositories.sql OK (550.01ms / 4.72s) OK (331.08ms / 5.74s) OK (343.87ms / 4.79s)
20260603120100_create_npm_packages.sql OK (898.72ms / 4.63s) OK (481.57ms / 5.77s) OK (478.48ms / 5s)
20260603120200_create_npm_versions.sql OK (1.22s / 4.71s) OK (592.56ms / 6.2s) OK (544.91ms / 5.05s)

Notes:

  • Rollback ≫ apply, by design. Each Down runs 64 DETACH PARTITION + 64 DROP TABLE serially, each in its own implicit transaction under +goose NO TRANSACTION (the documented max_locks_per_transaction workaround). That's ~128 catalog DDLs per table, so rollback lands at 4.6-6.2s against sub-second-to-1.2s applies (~4-17x). Partitions are empty at down-migration time, so this cost is catalog-bound and does not grow with row count.
  • PG 16 apply is ~2x the others. create_npm_versions applies in 1.22s on PG 16 vs 593ms (PG 17) / 545ms (PG 18). PG 16 is the first matrix shard (cold container/cache) and the absolute numbers are tiny - reads as warm-up variance, not a DDL/planner regression. Worth a glance on the next pipeline.
  • No version-specific rollback regression: PG 17 is slowest on Down but within ~1.3x of PG 16/18. No failed or EMPTY migrations among the three.

Merge request reports