feat(npm): NpmPackageByID resolver (npm hosted step 12, part 1/5)

David Fernandezrequested to merge
10io/npm-hosted-step-12-part1of5 into main

This change is split into 5 stacked MRs to keep each within review size. Each part targets the previous one (part 1 targets main); review and merge bottom-up, part 1 first.

Part 1 of 5 of docs/plans/2026-05-11-npm-hosted.md — Step 12: packument cache rebuilder (in-process).

Adds the NpmPackageByID datastore resolver: it resolves a package's name, scope, and repository identifiers by surrogate id, joining through npm_repositories to the foundation repositories row. The async rebuild job carries only (namespace_id, npm_package_id), so the rebuilder uses this to resolve the rest before rebuilding the cache.

Related to #130 (closed)

Stacked MRs (review/merge bottom-up)

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 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.NpmPackageByID Limit npm_packages_p32_pkey 1 / 1 10.35 0.021ms 5 / 0 1 per table (pruned from 64)
datastore.NpmPackageByID

Summary: Plan matches the resolver's intent. The namespace_id literal prunes each of the three hash-partitioned tables to a single partition (_p32, one of 64), and the driving row is fetched by primary-key Index Scan on npm_packages_p32_pkey (Index Cond: id = $2 AND namespace_id = $1), with soft_deleted_at IS NULL applied as a residual filter on that single PK-located row. The two joins resolve their pruned single-partition npm_repositories and repositories rows via single-row Seq Scans; the repository soft-delete gate appears as the expected residual Filter: soft_deleted_at IS NULL on the repositories partition scan (no extra index needed for a one-row partition). Planner and actual rows match (1 / 1) and execution stays at ~0.04ms over 5000 seeded packages. No anomalies.

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

Rendered SQL:

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

Bound args: [$1 = namespace_id cc90c01f-8e36-4f08-8650-b4cfb17e8530, $2 = npm_packages.id 2501, $3 = LIMIT 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..10.35 rows=1 width=88) (actual time=0.021..0.021 rows=1 loops=1)
   Buffers: shared hit=5
   ->  Nested Loop  (cost=0.28..10.35 rows=1 width=88) (actual time=0.020..0.021 rows=1 loops=1)
         Join Filter: (npm_repositories.repository_id = repositories.id)
         Buffers: shared hit=5
         ->  Nested Loop  (cost=0.28..9.33 rows=1 width=87) (actual time=0.016..0.017 rows=1 loops=1)
               Join Filter: (npm_repositories.id = npm_packages.npm_repository_id)
               Buffers: shared hit=4
               ->  Index Scan using npm_packages_p32_pkey on npm_packages_p32 npm_packages  (cost=0.28..8.30 rows=1 width=79) (actual time=0.013..0.013 rows=1 loops=1)
                     Index Cond: ((id = '2501'::bigint) AND (namespace_id = 'cc90c01f-8e36-4f08-8650-b4cfb17e8530'::uuid))
                     Filter: (soft_deleted_at IS NULL)
                     Buffers: shared hit=3
               ->  Seq Scan on npm_repositories_p32 npm_repositories  (cost=0.00..1.01 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)
                     Filter: (namespace_id = 'cc90c01f-8e36-4f08-8650-b4cfb17e8530'::uuid)
                     Buffers: shared hit=1
         ->  Seq Scan on repositories_p32 repositories  (cost=0.00..1.01 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=1)
               Filter: ((soft_deleted_at IS NULL) AND (namespace_id = 'cc90c01f-8e36-4f08-8650-b4cfb17e8530'::uuid))
               Buffers: shared hit=1
 Planning:
   Buffers: shared hit=911
 Planning Time: 1.935 ms
 Execution Time: 0.035 ms
(22 rows)

Timings: planning 1.935ms, execution 0.035ms, total 1.970ms.

Edited by David Fernandez

Merge request reports