feat(npm): transactional cache upsert (npm hosted step 12, part 3/5)

David Fernandezrequested to merge
10io/npm-hosted-step-12-part3of5 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 3 of 5 of docs/plans/2026-05-11-npm-hosted.md — Step 12: packument cache rebuilder (in-process).

Adds UpsertNpmMetadataFileForBlob: it mints a fresh blob_storage_attachments row, repoints the npm_metadata_files cache row at it, and deletes the prior attachment — all in one transaction (ADR 007), mirroring ContainerBlobLinker.LinkBlob. The store now composes a BlobStorageAttachmentStore. The async rebuild has no request transaction to mint an attachment id in, so it calls this combined method per kind. Helpers repointMetadataAttachment and existingAttachment carry the in-transaction statement sequence.

Related to #130 (closed)

Stacked MRs (review/merge bottom-up)

Database evidence

Static index analysis (not EXPLAIN-verified). Docker/Colima is unavailable in this environment, so there is no live database and no executed plan. Each query's WHERE / conflict-target / DELETE predicate columns are matched against the declared indexes, primary keys, and partition keys in internal/datastore/migrations/structure.sql and ADR 007. No migration ships on this branch (npm_metadata_files and blob_storage_attachments exist in pre-existing migrations), so migration timing analysis is n/a.

Relevant schema facts:

  • npm_metadata_files is PARTITION BY HASH (namespace_id); unique_npm_metadata_files_ns_id_pkg_id_kind is a unique btree index on (namespace_id, npm_package_id, kind).
  • blob_storage_attachments is PARTITION BY HASH (sha256) with composite primary key pk_blob_storage_attachments (id, namespace_id, sha256).

This MR adds UpsertNpmMetadataFileForBlob, whose work runs in repointMetadataAttachment as a four-statement transaction. The new queries to analyze are the existingAttachment SELECT and the blob_storage_attachments DELETE; the INSERT ... ON CONFLICT upsert is the shared upsertNpmMetadataFileStmt already analyzed in !713 (merged) (same conflict target, same pruning).

NpmMetadataFileStore.existingAttachment (SELECT)

SELECT blob_storage_attachment_id, blob_sha256 FROM npm_metadata_files WHERE namespace_id = $1 AND npm_package_id = $2 AND kind = $3 LIMIT 1. The (namespace_id, npm_package_id, kind) predicate matches unique_npm_metadata_files_ns_id_pkg_id_kind column-for-column, so the lookup is a unique-index probe returning at most one row. namespace_id is the leading index column and the partition key, so the planner prunes to the single namespace hash partition. No anomalies under static analysis.

blob_storage_attachments DELETE (via repointMetadataAttachment -> BlobStorageAttachmentStore.Delete)

DELETE FROM blob_storage_attachments WHERE namespace_id = $1 AND id = $2 AND sha256 = $3. The (namespace_id, id, sha256) predicate covers all three columns of pk_blob_storage_attachments (id, namespace_id, sha256), so the row is located through the full primary key. Because this table is hash-partitioned on sha256 (not namespace_id), the presence of sha256 in the predicate is what lets the planner prune to the single partition; namespace_id and id then complete the PK match within it. No anomalies under static analysis.

Edited by David Fernandez

Merge request reports