feat(npm): carry npm_files.created_at to the virtual tarball winner

What this MR does

The hosted tarball lookup of an npm virtual repository now projects npm_files.created_at. NpmVirtualHostedHit.CreatedAt carries the value, and the assembly copies it onto the hosted VirtualPosition. The virtual tarball handler (part 3) sends this time as Last-Modified for a hosted winner, with no second query (ruling E1 of !3111 (merged)).

VirtualHostedUpstreams.lookupAnswers returns the existing Lookup answers plus the tarball times. The assembly uses it when the hosted seam supports it, and falls back to Lookup when it does not.

Tests

  • TestNpmVirtualHostedTarballLookupStmt_ProjectsCreatedAt and TestNpmVirtualHostedTarballLookup_CarriesTheFilesCreatedAt cover the datastore half.
  • The npm half has no direct test in this part. Part 3 covers it through the handler: TestVirtualTarballHandler_ServesTheWinnersBytes and TestVirtualTarballHandler_ConditionalRequestsFollowTheWinnersKind assert the hosted Last-Modified.

Split of !3111 (merged)

This MR is one part of a split of !3111 (merged) into six stacked MRs. Each part targets the branch of the part before it, so each diff shows only that part. Merge the parts in order: 1, 2, 3, 4, 5, then !3111 (merged) itself as part 6.

Part MR Contents
1 !3150 (merged) npm_files.created_at to the hosted tarball winner
2 !3147 (merged) Resolver re-entry on a virtual drain failure
3 !3148 (merged) VirtualTarballHandler, not wired
4 !3149 (merged) Copy-fault accounting, kill log, cold HEAD drain failure
5 !3151 (merged) Usage events
6 !3111 (merged) Slot wiring, run recipe, docs

The six parts together are byte-identical to the original !3111 (merged) diff (same git patch-id --stable). The spec coverage table, the rulings (E1 to E22), and the spec gaps stay in the description of !3111 (merged).

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.NpmVirtualHostedTarballLookup Unique npm_packages_p34_namespace_id_npm_repository_id_name_idx, npm_versions_p34_namespace_id_npm_package_id_id_idx, npm_files_p34_namespace_id_npm_version_id_idx 1 / 3 46.71 0.254ms 482 / 0 1/64, 1/64, 1/64

Query notes:

  • datastore.NpmVirtualHostedTarballLookup: plan vs actual rows diverge 50x on the npm_versions leg (1 estimated, 50 actual per loop). The planner uses the average version count per package, and the seed gives the target package 50 versions. ANALYZE ran.
  • datastore.NpmVirtualHostedTarballLookup: the npm_files probe uses (namespace_id, npm_version_id) and filters file_name, so it probes once per live version (150 probes for 3 hits). The work grows with the live version count of the package, multiplied by the position count.
  • datastore.NpmVirtualHostedTarballLookup: the plan starts at npm_packages. The merged Step 5 MR !1975 (merged) rejected this join order: driven from npm_packages, the read walks every live version of the package and probes npm_files once for each. ADR-004 permits 25,000 versions per package.
  • datastore.NpmVirtualHostedTarballLookup: no test pins the join order since commit 23f228ed9 ("test(datastore): delete the query-plan assertions"), which removed TestNpmVirtualHostedTarballLookup_DrivesFromNpmFiles. The database review evidence is the only record of this plan.
  • datastore.NpmVirtualHostedTarballLookup: this MR adds npm_files.created_at to the select list. It makes no query change and no index change. The plan shape, the indexes, and the estimates are the same at the merge-base 1ea37fc3.
  • The join order is tracked in the follow-up issue #1518.
datastore.NpmVirtualHostedTarballLookup

Summary: The plan returns the right rows: index scans on all three tables prune to 1 of 64 partitions each, and an Incremental Sort feeds DISTINCT ON. The plan starts at npm_packages, the order that !1975 (merged) rejected. The versions-leg estimate is 50x low, and the files probe filters file_name once per version (see Query notes).

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

Rendered SQL:

SELECT DISTINCT ON (npm_packages.npm_repository_id) npm_packages.npm_repository_id AS "npm_packages.npm_repository_id",
     npm_files.blob_sha256 AS "npm_files.blob_sha256",
     npm_files.created_at AS "npm_files.created_at"
FROM public.npm_packages
     INNER JOIN public.npm_versions ON ((npm_versions.namespace_id = npm_packages.namespace_id) AND (npm_versions.npm_package_id = npm_packages.id))
     INNER JOIN public.npm_files ON ((npm_files.namespace_id = npm_versions.namespace_id) AND (npm_files.npm_version_id = npm_versions.id))
WHERE ((((((((npm_packages.namespace_id = $1::uuid) AND (npm_packages.npm_repository_id IN ($2::uuid, $3::uuid, $4::uuid))) AND (npm_packages.name = $5::text)) AND (npm_packages.soft_deleted_at IS NULL)) AND (npm_versions.namespace_id = $6::uuid)) AND (npm_versions.soft_deleted_at IS NULL)) AND (npm_files.namespace_id = $7::uuid)) AND (npm_files.file_name = $8::text)) AND (npm_files.soft_deleted_at IS NULL)
ORDER BY npm_packages.npm_repository_id ASC, npm_files.created_at DESC, npm_files.id DESC;

Bound args: [030333d2-2ab0-7cbf-9f9e-d828640fe8e9, 45c747e1-f808-73f0-9347-f02f8d9f1861, f120888d-5280-779f-a06c-8206853a076b, 4a31e261-21c4-759c-bcca-2a08deec867f, review-prep-pkg-0250, 030333d2-2ab0-7cbf-9f9e-d828640fe8e9, 030333d2-2ab0-7cbf-9f9e-d828640fe8e9, review-prep-pkg-0250-1.0.25.tgz]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Unique  (cost=23.74..46.71 rows=1 width=73) (actual time=0.252..0.254 rows=3 loops=1)
   Buffers: shared hit=482
   ->  Incremental Sort  (cost=23.74..46.71 rows=2 width=73) (actual time=0.252..0.253 rows=3 loops=1)
         Sort Key: npm_packages.npm_repository_id, npm_files.created_at DESC, npm_files.id DESC
         Presorted Key: npm_packages.npm_repository_id
         Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
         Buffers: shared hit=482
         ->  Nested Loop  (cost=0.85..46.62 rows=1 width=73) (actual time=0.094..0.240 rows=3 loops=1)
               Buffers: shared hit=482
               ->  Nested Loop  (cost=0.56..45.35 rows=3 width=48) (actual time=0.036..0.069 rows=150 loops=1)
                     Buffers: shared hit=32
                     ->  Index Scan using npm_packages_p34_namespace_id_npm_repository_id_name_idx on npm_packages_p34 npm_packages  (cost=0.28..20.41 rows=3 width=48) (actual time=0.022..0.027 rows=3 loops=1)
                           Index Cond: ((namespace_id = '030333d2-2ab0-7cbf-9f9e-d828640fe8e9'::uuid) AND (npm_repository_id = ANY ('{45c747e1-f808-73f0-9347-f02f8d9f1861,f120888d-5280-779f-a06c-8206853a076b,4a31e261-21c4-759c-bcca-2a08deec867f}'::uuid[])) AND (name = 'review-prep-pkg-0250'::text))
                           Buffers: shared hit=7
                     ->  Index Scan using npm_versions_p34_namespace_id_npm_package_id_id_idx on npm_versions_p34 npm_versions  (cost=0.28..8.30 rows=1 width=48) (actual time=0.006..0.011 rows=50 loops=3)
                           Index Cond: ((namespace_id = '030333d2-2ab0-7cbf-9f9e-d828640fe8e9'::uuid) AND (npm_package_id = npm_packages.id))
                           Filter: (soft_deleted_at IS NULL)
                           Buffers: shared hit=25
               ->  Index Scan using npm_files_p34_namespace_id_npm_version_id_idx on npm_files_p34 npm_files  (cost=0.28..0.41 rows=1 width=89) (actual time=0.001..0.001 rows=0 loops=150)
                     Index Cond: ((namespace_id = '030333d2-2ab0-7cbf-9f9e-d828640fe8e9'::uuid) AND (npm_version_id = npm_versions.id))
                     Filter: ((soft_deleted_at IS NULL) AND (file_name = 'review-prep-pkg-0250-1.0.25.tgz'::text))
                     Rows Removed by Filter: 1
                     Buffers: shared hit=450
 Planning:
   Buffers: shared hit=105
 Planning Time: 0.860 ms
 Execution Time: 0.294 ms

Timings: planning 0.860ms, execution 0.294ms, total 1.154ms.

E2E scenario catalog and user documentation

This part adds no e2e scenario and changes no file in docs/user/. Nothing wires the virtual tarball handler before part 6, so the route still answers 501 and no client-visible behavior changes.

Related to #898 (closed)

🤖 Generated with Claude Code

Edited by David Fernandez

Merge request reports

Loading
Loading