feat(npm): NpmVersionByPackageAndVersion datastore finder (S11 Step 9, 2/6)

📦 What

The exact-version point-lookup finder for npm_versions, extracted from the tarball-download handler MR (!536 (merged)) into its own change because it is reusable read-side infrastructure: the download handler resolves a version directly from the {plain_name}-{version}.tgz file name with it, and the packument paths (Step 11) will want the same lookup.

NpmVersionByPackageAndVersion(namespace_id, npm_package_id, version) returns the single active npm_versions row, or datastore.ErrNotFound. The predicate plus soft_deleted_at IS NULL is served by the Step 1 partial unique index on (namespace_id, npm_package_id, version), so it is a single index seek.

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.
• !543 (merged) - NpmVersionByPackageAndVersion datastore finder (read-side prerequisite). <- this MR
• !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 dm/npm-local-step-9 (the !535 (merged) branch), not main.

✅ Spec coverage

Behaviour	Test (TestNpmVersionStore_NpmVersionByPackageAndVersion)
exact (package, version) match returns the active row	returns the active row for an exact (package, version) match
missing version -> ErrNotFound	returns ErrNotFound when the version does not exist
soft-deleted version -> ErrNotFound	treats a soft-deleted version as not found
version under a different package not matched	does not match a version under a different package

🧪 Testing

• go build ./internal/datastore/... - pass
• go test ./internal/datastore/... - pass (unit)
• go vet -tags integration ./internal/datastore/... - clean (the new integration test compiles)
• Integration tests 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.NpmVersionByPackageAndVersion	Limit -> Index Scan	unique_npm_versions_ns_id_pkg_id_version (partition child npm_versions_p56_namespace_id_npm_package_id_version_idx)	1 / 1	8.30	0.016ms	3 / 0	1

datastore.NpmVersionByPackageAndVersion

Summary: Plan matches the method's intent: a single index seek over the partial unique index unique_npm_versions_ns_id_pkg_id_version ((namespace_id, npm_package_id, version) WHERE soft_deleted_at IS NULL), with the namespace_id literal pruning to one of 64 hash partitions. The Index Cond covers all three predicate columns and returns at most one row (plan 1 / actual 1); the LIMIT 1 is belt-and-suspenders. No anomalies.

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

Rendered SQL:

SELECT npm_versions.namespace_id AS "npm_versions.namespace_id",
     npm_versions.id AS "npm_versions.id",
     npm_versions.npm_package_id AS "npm_versions.npm_package_id",
     npm_versions.last_downloaded_at AS "npm_versions.last_downloaded_at",
     npm_versions.soft_deleted_at AS "npm_versions.soft_deleted_at",
     npm_versions.created_at AS "npm_versions.created_at",
     npm_versions.version AS "npm_versions.version",
     npm_versions.package_json AS "npm_versions.package_json",
     npm_versions.gitlab_user_id AS "npm_versions.gitlab_user_id",
     npm_versions.gitlab_project_id AS "npm_versions.gitlab_project_id",
     npm_versions.gitlab_git_commit_sha AS "npm_versions.gitlab_git_commit_sha"
FROM public.npm_versions
WHERE (((npm_versions.namespace_id = $1::uuid) AND (npm_versions.npm_package_id = $2)) AND (npm_versions.version = $3::text)) AND (npm_versions.soft_deleted_at IS NULL)
LIMIT $4;

Bound args: [<seeded namespace_id uuid>, 1, 'review-prep-ver-002500', 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.30 rows=1 width=180) (actual time=0.007..0.007 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using npm_versions_p56_namespace_id_npm_package_id_version_idx on npm_versions_p56 npm_versions  (cost=0.28..8.30 rows=1 width=180) (actual time=0.007..0.007 rows=1 loops=1)
         Index Cond: ((namespace_id = '1ecb488f-bdab-4f4d-87fd-acb6304e2bac'::uuid) AND (npm_package_id = '1'::bigint) AND (version = 'review-prep-ver-002500'::text))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=415 read=1
 Planning Time: 0.816 ms
 Execution Time: 0.016 ms

Timings: planning 0.816ms, execution 0.016ms, total 0.832ms.