feat(npm): file datastore read query (S11 Step 4b)

📦 What

Step 4b of the npm local plan — the content-addressed file read: NpmFilesByVersion (tarball-download path).

Re-sliced from the original file+metadata Step 4b by storage concern to keep each MR under 500 LOC. Step 4a (!470 (merged)) has merged to main, so this MR now targets main directly. The metadata-file read (NpmMetadataFileByPackageAndKind) lives in !502 (merged) (Step 4c), stacked on this branch.

Store file	Read method
npm_files.go	NpmFilesByVersion

NpmFilesByVersion returns one bounded keyset page of active npm_files rows for a version, filtering soft_deleted_at IS NULL (so files of an unpublished version, or removed by single-version unpublish, are not served), ordered by file_name, leading with the namespace_id partition key (ADR 022). It takes NpmFilesByVersionParams with a validated Limit in (0, maxNpmFilesPageSize=1000] and a single-column file_name keyset (AfterFileName), mirroring NpmVersionsByPackage / NpmTagsByPackage — npm stores one tarball per version, but the read is bounded so an append/overwrite anomaly can't trigger an unbounded materialization. Argument guards reject a nil context, the zero-UUID namespace, a non-positive npmVersionID (AppSec Finding 1, errNpmFileNonPositiveVersionID), and an out-of-range Limit before the SQL build, matching the sibling finders.

This slice also carries the shared content-addressed integration fixtures that both reads use: randNpmSHA256, assertDistinctDigests, and seedAttachment. seedAttachment seeds a parent blob_storage_blobs row before the blob_storage_attachments row to satisfy the (namespace_id, sha256) FK that landed on main in S06 — the old fixtures predated it.

✅ Testing

• internal/datastore/npm_read_integration_test.go (integration, testcontainers PostgreSQL): the file query suite — success, soft-delete exclusion, version/namespace isolation, empty result.
• internal/datastore/npm_read_test.go (unit): argument-guard rejections (nil ctx, zero namespace, non-positive npmVersionID, invalid Limit) + constructor nil-client panic.
• Full npm integration suite + unit + pre-commit (gofmt, goimports, golangci-lint, go-test, gitlint) green.

MR size: ~485 LOC.

🔁 Rebase + re-slice note

Re-sliced by store from the original file+metadata Step 4b, then rebased onto main after Step 4a (!470 (merged)) merged. The branch carries three feat commits: the original file read, bc69f2d (bounded keyset pagination on NpmFilesByVersion - the Limit cap + ORDER BY file_name / keyset tests), and 40b2e4c (the npmVersionID positivity guard, AppSec Finding 1). The rebase conflicts came only from 4a's stores landing on main and the shared test file gaining 4a's seedNpmVersionAt / keyset additions, resolved additively. Adapted to 4a's per-store argument-guard sentinels and inlined unit-guard convention, and fixed the blob_storage_blobs FK seed from main's S06 drift. AppSec Finding 1 (npmVersionID positivity guard) is addressed in this MR (40b2e4c9); Findings 2-3 (npmPackageID / kind guards on NpmMetadataFileByPackageAndKind) move with that metadata read to !502 (merged) / work_items/176; Finding 4 (expiry predicate) likewise moves to !502 (merged).

Related to #122 (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.NpmFilesByVersion.AfterFileNameEmpty	Limit	unique_npm_files_ns_id_version_id_file_name	50 / 50	5.07	0.012ms	3 / 0	1
datastore.NpmFilesByVersion.AfterFileNameSet	Limit	unique_npm_files_ns_id_version_id_file_name	50 / 50	6.74	0.013ms	4 / 0	1

datastore.NpmFilesByVersion.AfterFileNameEmpty (first page)

Summary: Limit over an Index Scan on the partial unique index unique_npm_files_ns_id_version_id_file_name ((namespace_id, npm_version_id, file_name) WHERE soft_deleted_at IS NULL). The namespace_id literal prunes to one of 64 partitions (npm_files_p61); the index supplies the file_name ORDER BY (no Sort) and its partial predicate absorbs soft_deleted_at IS NULL (no Filter), so the Limit returns the first 50 of 5000 files seeded for the version. 3 buffer hits, no reads. No anomalies.

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

Rendered SQL:

SELECT npm_files.namespace_id AS "npm_files.namespace_id",
     npm_files.id AS "npm_files.id",
     npm_files.npm_version_id AS "npm_files.npm_version_id",
     npm_files.blob_storage_attachment_id AS "npm_files.blob_storage_attachment_id",
     npm_files.soft_deleted_at AS "npm_files.soft_deleted_at",
     npm_files.created_at AS "npm_files.created_at",
     npm_files.file_name AS "npm_files.file_name",
     npm_files.blob_sha256 AS "npm_files.blob_sha256"
FROM public.npm_files
WHERE ((npm_files.namespace_id = $1::uuid) AND (npm_files.npm_version_id = $2)) AND (npm_files.soft_deleted_at IS NULL)
ORDER BY npm_files.file_name ASC
LIMIT $3;

Bound args: [24c6065e-0cd0-48f3-9070-fce0f1eb74cc, 1, 50]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..5.07 rows=50 width=113) (actual time=0.008..0.012 rows=50 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using npm_files_p61_namespace_id_npm_version_id_file_name_idx on npm_files_p61 npm_files  (cost=0.28..479.16 rows=5000 width=113) (actual time=0.008..0.010 rows=50 loops=1)
        Index Cond: ((namespace_id = '24c6065e-0cd0-48f3-9070-fce0f1eb74cc'::uuid) AND (npm_version_id = '1'::bigint))
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=351
Planning Time: 0.600 ms
Execution Time: 0.021 ms

Timings: planning 0.600ms, execution 0.021ms, total 0.621ms.

datastore.NpmFilesByVersion.AfterFileNameSet (keyset page)

Summary: Same Index Scan + Limit shape with the keyset predicate file_name > $3 folded into the Index Cond as a range bound, so the scan seeks straight to the cursor and the index still supplies the order — no Sort, no Filter. One partition (npm_files_p61); 50 of the ~2500 post-cursor rows returned, 4 buffer hits, no reads. No anomalies.

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

Rendered SQL:

SELECT npm_files.namespace_id AS "npm_files.namespace_id",
     npm_files.id AS "npm_files.id",
     npm_files.npm_version_id AS "npm_files.npm_version_id",
     npm_files.blob_storage_attachment_id AS "npm_files.blob_storage_attachment_id",
     npm_files.soft_deleted_at AS "npm_files.soft_deleted_at",
     npm_files.created_at AS "npm_files.created_at",
     npm_files.file_name AS "npm_files.file_name",
     npm_files.blob_sha256 AS "npm_files.blob_sha256"
FROM public.npm_files
WHERE (((npm_files.namespace_id = $1::uuid) AND (npm_files.npm_version_id = $2)) AND (npm_files.soft_deleted_at IS NULL)) AND (npm_files.file_name > $3::text)
ORDER BY npm_files.file_name ASC
LIMIT $4;

Bound args: [24c6065e-0cd0-48f3-9070-fce0f1eb74cc, 1, review-prep-file-002500, 50]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..6.74 rows=50 width=113) (actual time=0.007..0.013 rows=50 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using npm_files_p61_namespace_id_npm_version_id_file_name_idx on npm_files_p61 npm_files  (cost=0.28..323.22 rows=2500 width=113) (actual time=0.007..0.011 rows=50 loops=1)
        Index Cond: ((namespace_id = '24c6065e-0cd0-48f3-9070-fce0f1eb74cc'::uuid) AND (npm_version_id = '1'::bigint) AND (file_name > 'review-prep-file-002500'::text))
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=3
Planning Time: 0.103 ms
Execution Time: 0.020 ms

Timings: planning 0.103ms, execution 0.020ms, total 0.123ms.