feat(npm): two-id Resolution for npm read handlers (S11 Step 9, 1/6)

📦 What

S11 Step 9 (tarball download) is a six-MR stack, in dependency order (each targets the one above; GitLab auto-retargets to main as they merge):

• !535 (merged) - two-id Resolution for npm read handlers. <- this MR
• !543 (merged) - NpmVersionByPackageAndVersion datastore finder (read-side prerequisite).
• !536 (merged) - tarball download handler: serve + Cache-Control.
• !562 (merged) - last_downloaded_at bump on a served download.
• !563 (merged) - perf: resolve the version by an indexed point lookup.
• !537 (merged) - conditional GET + strong validators.

This MR targets main.

Heads-up: the tarball handler that slices 3-4 build ships unwired - handler.go still serves the 501 placeholder. This is a deliberate, documented plan-level wiring gap (see the Known gap section in !536 (merged)), to be closed by a plan amendment before Step 23 conformance, not in this stack.

npm read handlers key npm_packages lookups on npm_repositories.id, while the tarball response's visibility-driven Cache-Control needs repositories.id. Step 6 shipped a single-id Resolution carrying only repositories.id; this extends it to the (npm_repositories.id, repositories.id) pair via an npm_repositories JOIN repositories, mirroring OCI's two-id Resolution (ContainerRepositoryID, RepositoryID).

Follow-up issue #153 extracts the shared namespace-tier resolution to remove the cross-format finder duplication.

✅ Spec coverage

Internal plumbing (no new endpoint); covered by:

• internal/datastore/npm_repositories_integration_test.go - the two-id JOIN returns (npm_repository_id, repository_id); ErrNotFound for wrong-format/kind or soft-deleted rows.
• internal/format/npm/resolver_test.go - Resolution carries both ids.
• cmd/artifact-registry/wire_npm_test.go - the finder adapter threads both ids.

🧪 Testing

• go test ./internal/format/npm/... - pass
• go vet -tags integration ./internal/format/npm/... ./internal/datastore/... ./cmd/artifact-registry/... - clean
• Integration tests (//go:build integration) run in the merged-results pipeline.

Related to #127 (closed)

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17.10 container (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), with synthesized seed data rolled back per query and the container torn down at the end of the run. Numbers reflect moderate cardinality and do not capture production-scale effects. See Database review evidence for seed sizing, methodology, and the anomalies the skill flags. Expand each row's details for the seed shape, rendered SQL, bound args, and raw plan.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
datastore.FindHostedByNameInNamespace	Limit -> Nested Loop	repositories_pNN_namespace_id_name_idx1, npm_repositories_pNN_namespace_id_repository_id_idx	1 / 1	16.62	0.021ms	6 / 0	1 per side

datastore.FindHostedByNameInNamespace

Summary: Plan matches the method's intent. The repositories side is an Index Scan over the (namespace_id, name) active partial index (soft_deleted_at IS NULL is satisfied by the partial predicate, so it drops out of the Index Cond; format/kind apply as a cheap filter), and the namespace_id literal prunes to a single partition. The Nested Loop then drives one Index Scan into npm_repositories via its (namespace_id, repository_id) unique index, also pruned to one partition. Estimate matches reality exactly (1 / 1) at 5000 seeded rows per side, all buffers are cache hits (6 / 0), and execution stays at ~21us. No anomalies.

Seed shape: namespaces=1, repositories=5000, npm_repositories=5000

Rendered SQL:

SELECT npm_repositories.id AS "npm_repositories.id",
     npm_repositories.repository_id AS "npm_repositories.repository_id"
FROM public.npm_repositories
     INNER JOIN public.repositories ON ((repositories.id = npm_repositories.repository_id) AND (repositories.namespace_id = npm_repositories.namespace_id))
WHERE ((((npm_repositories.namespace_id = $1::uuid) AND (repositories.name = $2::text)) AND (repositories.format = $3)) AND (repositories.kind = $4)) AND (repositories.soft_deleted_at IS NULL)
LIMIT $5;

Bound args: [<namespace uuid>, 'review-prep-repo-002500', 2, 0, 1] ($3=RepositoryFormatNpm, $4=RepositoryKindHosted, $5=LIMIT)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.56..16.62 rows=1 width=16) (actual time=0.011..0.012 rows=1 loops=1)
  Buffers: shared hit=6
  ->  Nested Loop  (cost=0.56..16.62 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=1)
        Buffers: shared hit=6
        ->  Index Scan using repositories_p50_namespace_id_name_idx1 on repositories_p50 repositories  (cost=0.28..8.31 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=1)
              Index Cond: ((namespace_id = '9aaac9e1-022a-411d-b529-fcab8c6e9c73'::uuid) AND (name = 'review-prep-repo-002500'::text))
              Filter: ((format = '2'::smallint) AND (kind = '0'::smallint))
              Buffers: shared hit=3
        ->  Index Scan using npm_repositories_p50_namespace_id_repository_id_idx on npm_repositories_p50 npm_repositories  (cost=0.28..8.30 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=1)
              Index Cond: ((namespace_id = '9aaac9e1-022a-411d-b529-fcab8c6e9c73'::uuid) AND (repository_id = repositories.id))
              Buffers: shared hit=3
Planning:
  Buffers: shared hit=297
Planning Time: 0.728 ms
Execution Time: 0.021 ms

Timings: planning 0.728ms, execution 0.021ms, total 0.749ms.