feat(npm): remote metadata cache-fill upsert (S15 Step 6, part 3/5)

📦 What this MR does

Part 3 of 5 of S15 Step 6 (datastore remote write — cache-fill): the metadata-document cache-fill upsert on npm_remote_metadata_files.

• UpsertNpmRemoteMetadataFileForBlob — mints a fresh blob_storage_attachments row for the transformed document's CAS digest, upserts the (namespace, package, kind) row to point at it (stamping upstream_etag and upstream_checked_at = NOW()), and hard-deletes the superseded attachment, all on the caller's cache-fill transaction so a truncated fill rolls back atomically (the spec's partial-fetch guarantee; the in-transaction delete is ADR-007's GC-eligibility mandate).
• The repoint opens with a SELECT ... FOR UPDATE on the parent package row, serializing concurrent fills of the same key across replicas (S13's single-flight is in-process only): the losing fill sees — and deletes — the winner's attachment instead of leaving it permanently orphaned, its blob pinned against GC. A missing parent locks nothing and falls through, so the FK classification still owns that error path. Mirrors LockActiveNpmPackageForPublish; covered by an overlapping-transaction race test and partition-pruning EXPLAIN tests.
• validUpstreamEtag — rejects an ETag the column CHECKs would refuse (over 255 chars, CR/LF) before any SQL, so the caller gets a stable sentinel instead of a CHECK violation it would have to tell apart from every other constraint on the row.
• The repoint read (existingAttachment) and the parent lock are per-store and scan into the generated models, so a column rename breaks the build. Part 4's tarball twin writes its own against model.NpmRemoteFiles — duplication over alias-tag-per-table indirection, the call npm_remote_files.go already records for the read side.

Sized ~965 reviewable LoC — over the ≈800 acceptable band but under the hard limit: the upsert-repoint feature (guard method, repoint helper, shared helpers, statement builders, and its behavior/concurrency/pruning tests) is indivisible without separating tests from their code.

⚙️ Why stacked

Step 6's full diff is ~3.4k reviewable LoC, so it ships as 5 stacked MRs to keep each within the review size limit (≈600 ideal), each part targeting the previous (part 1 → main), reviewed and merged bottom-up. This part sits on part 2 (ErrParentNpmRemotePackageMissing, FK ancestry).

🔗 References

• Plan: docs/plans/2026-07-15-npm-remote.md — Step 6
• Spec: docs/specs/S15-npm-remote.md — Cache fill, Variant selection, Freshness

🔬 e2e scenarios

No scenario added or affected: datastore write layer with no reachable request path until the proxy handlers (Steps 10–12) wire the cache fill end-to-end.

Related to #343 (closed)

📚 Stacked MRs (review/merge bottom-up)

• feat(npm): npm_remote_packages cache-fill write... (!1184 - merged) • David Fernandez • 19.3
• feat(npm): npm_remote_versions cache-fill write... (!1185 - merged) • David Fernandez • 19.3
• feat(npm): remote metadata cache-fill upsert (S... (!1186 - merged) • David Fernandez • 19.3 👈
• feat(npm): remote tarball cache-fill upsert (S1... (!1187 - merged) • David Fernandez • 19.3
• feat(npm): remote 304 revalidation bumps (S15 S... (!1188 - merged) • David Fernandez • 19.3

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 container (server version 17.10, matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), with the branch's migrations applied and synthesized seed data rolled back per query; the container was torn down at the end of the run. The SQL was extracted from the statement builders themselves via a throwaway .Sql() harness, so each plan describes the statement the store executes rather than a hand-written copy. 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.

This MR adds no migration, so there are no per-migration timings.

Statement	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
datastore.lockNpmRemotePackageForFillStmt	LockRows → Index Scan	npm_remote_packages_p62_pkey	1 / 1	8.31	0.020ms	4 / 0	1
datastore.findNpmRemoteMetadataAttachmentStmt	Limit → Index Scan	npm_remote_metadata_files_p38_namespace_id_npm_remote_packa_idx	1 / 1	8.30	0.022ms	3 / 0	1
datastore.upsertNpmRemoteMetadataFileStmt (repoint)	Insert	unique_npm_remote_metadata_files_ns_id_pkg_id_kind (conflict arbiter)	0 / 0 (1 conflicting tuple)	0.01	0.343ms	44 / 2	1

datastore.lockNpmRemotePackageForFillStmt

What it is / when it runs: step 0 of the cache fill's repoint sequence. UpsertNpmRemoteMetadataFileForBlob takes a SELECT ... FOR UPDATE row lock on the parent npm_remote_packages row before reading the old attachment, so concurrent fills of the same package serialize across replicas and the loser deletes the winner's attachment instead of orphaning it. It runs once per cache fill, inside the caller's fill transaction.

Summary: Plan matches the method's intent: LockRows over an Index Scan on the partition-local primary key, with both equality predicates in the Index Cond and the namespace_id literal pruning to exactly one of 64 partitions. No soft-delete filter appears, matching the documented contract that the lock targets whatever row the id names. Estimate matches reality (1 / 1) at 5000 rows in the partition, 4 shared buffers, no disk reads. No anomalies.

Row counts: namespaces=1, repositories=1, npm_remote_repositories=1, npm_remote_packages=5000 (all in one partition), blob_storage_blobs=1, blob_storage_attachments=1, npm_remote_metadata_files=5000 (all in one partition)

Rendered SQL:

SELECT npm_remote_packages.id AS "npm_remote_packages.id"
FROM public.npm_remote_packages
WHERE (npm_remote_packages.namespace_id = $1::uuid) AND (npm_remote_packages.id = $2::uuid)
FOR UPDATE;

Bound args: [d7a2fb9c-e28d-4b94-be87-63f4a9a7fc17, 4d271614-db3e-4079-aa8f-9772e1efc858]

Plan (EXPLAIN (ANALYZE, BUFFERS) output, inside BEGIN; ... ROLLBACK; so the row lock is released with the transaction):

 LockRows  (cost=0.28..8.31 rows=1 width=26) (actual time=0.010..0.010 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using npm_remote_packages_p62_pkey on npm_remote_packages_p62 npm_remote_packages  (cost=0.28..8.30 rows=1 width=26) (actual time=0.008..0.008 rows=1 loops=1)
         Index Cond: ((id = '4d271614-db3e-4079-aa8f-9772e1efc858'::uuid) AND (namespace_id = 'd7a2fb9c-e28d-4b94-be87-63f4a9a7fc17'::uuid))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=252
 Planning Time: 0.666 ms
 Execution Time: 0.020 ms

Timings: planning 0.666ms, execution 0.020ms, total 0.686ms. Planning dominates and is the 64-partition pruning cost (252 planning buffers), not the row lookup.

datastore.findNpmRemoteMetadataAttachmentStmt

What it is / when it runs: step 1 of the repoint sequence, behind existingAttachment. After the parent lock, the fill reads the current cache row's (blob_storage_attachment_id, blob_sha256) so the superseded attachment can be deleted after the repoint. Runs once per cache fill, on the fill transaction's handle so it shares that snapshot. A first fill matches no row, which is not an error.

Summary: Plan matches the method's intent: Limit over an Index Scan on the partition-local child of the total unique index unique_npm_remote_metadata_files_ns_id_pkg_id_kind, with all three equality predicates — including kind — pushed into Index Cond, and the namespace_id literal pruning to one of 64 partitions. Estimate matches reality (1 / 1), 3 shared buffers, no disk reads, at 5000 metadata rows in the partition. No anomalies.

Row counts: namespaces=1, repositories=1, npm_remote_repositories=1, npm_remote_packages=5000, blob_storage_blobs=1, blob_storage_attachments=1, npm_remote_metadata_files=5000 (all in one partition, kinds cycling 0/1/2)

Rendered SQL:

SELECT npm_remote_metadata_files.blob_storage_attachment_id AS "npm_remote_metadata_files.blob_storage_attachment_id",
     npm_remote_metadata_files.blob_sha256 AS "npm_remote_metadata_files.blob_sha256"
FROM public.npm_remote_metadata_files
WHERE ((npm_remote_metadata_files.namespace_id = $1::uuid) AND (npm_remote_metadata_files.npm_remote_package_id = $2::uuid)) AND (npm_remote_metadata_files.kind = $3)
LIMIT $4;

Bound args: [632f76e4-aa10-428c-b9d9-5d6a086299cd, bcd40a6b-91a7-407f-8db7-f63b539ae088, 1, 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.30 rows=1 width=41) (actual time=0.011..0.011 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using npm_remote_metadata_files_p38_namespace_id_npm_remote_packa_idx on npm_remote_metadata_files_p38 npm_remote_metadata_files  (cost=0.28..8.30 rows=1 width=41) (actual time=0.011..0.011 rows=1 loops=1)
         Index Cond: ((namespace_id = '632f76e4-aa10-428c-b9d9-5d6a086299cd'::uuid) AND (npm_remote_package_id = 'bcd40a6b-91a7-407f-8db7-f63b539ae088'::uuid) AND (kind = '1'::smallint))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=95
 Planning Time: 0.427 ms
 Execution Time: 0.022 ms

Timings: planning 0.427ms, execution 0.022ms, total 0.449ms.

datastore.upsertNpmRemoteMetadataFileStmt

What it is / when it runs: step 3 of the repoint sequence, the cache fill's INSERT ... ON CONFLICT (namespace_id, npm_remote_package_id, kind) DO UPDATE. A first fill for the (namespace, package, kind) inserts; a revalidation that returned 200 with a changed document updates blob_storage_attachment_id, blob_sha256, upstream_etag and resets upstream_checked_at, so the row repoints onto the freshly minted attachment before the old one is deleted at step 4.

Summary: Plan matches the method's intent on both paths. Postgres selects unique_npm_remote_metadata_files_ns_id_pkg_id_kind as the conflict arbiter — the total unique index the doc comment names, with no partial predicate, matching the fact that this table carries no soft_deleted_at — and the namespace_id value routes the write to exactly one of 64 partitions. First fill reports Tuples Inserted: 1, Conflicting Tuples: 0; the revalidation repoint reports Tuples Inserted: 0, Conflicting Tuples: 1. Execution on the first-fill path is dominated by the four FK-validation triggers (attachment, namespace, parent package, blob) firing into a cold partition, 0.927ms of the 1.296ms; on the repoint path all four are already warm and the whole statement finishes in 0.343ms. No anomalies.

Row counts: namespaces=1, repositories=1, npm_remote_repositories=1, npm_remote_packages=5001, blob_storage_blobs=2, blob_storage_attachments=2, npm_remote_metadata_files=5000 (all in one partition). The extra package is the first-fill target (no cache row yet); the second blob and attachment are the fresh CAS objects step 2 of the repoint mints.

Rendered SQL (non-nil upstream_etag):

INSERT INTO public.npm_remote_metadata_files (id, namespace_id, npm_remote_package_id, blob_storage_attachment_id, upstream_checked_at, kind, blob_sha256, upstream_etag)
VALUES ($1::uuid, $2::uuid, $3::uuid, $4, NOW(), $5, $6::bytea, $7::text)
ON CONFLICT (namespace_id, npm_remote_package_id, kind) DO UPDATE
       SET blob_storage_attachment_id = $8,
           blob_sha256 = $9::bytea,
           upstream_etag = $10::text,
           upstream_checked_at = NOW();

Bound args (repoint): [<gen_random_uuid()>, 46e10f80-bead-451b-95c5-e9d8a1da3cda, 3d5f6ade-a1b3-485c-a6bd-a4c06bae7309, <new attachment id>, 1, \x0000...0002, "upstream-etag-2", <new attachment id>, \x0000...0002, "upstream-etag-2"]

Plan — revalidation repoint (ON CONFLICT DO UPDATE path):

 Insert on npm_remote_metadata_files  (cost=0.00..0.01 rows=0 width=0) (actual time=0.205..0.205 rows=0 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: unique_npm_remote_metadata_files_ns_id_pkg_id_kind
   Tuples Inserted: 0
   Conflicting Tuples: 1
   Buffers: shared hit=44 read=2
   ->  Result  (cost=0.00..0.01 rows=1 width=130) (actual time=0.001..0.001 rows=1 loops=1)
 Planning Time: 0.033 ms
 Trigger for constraint fk_npm_remote_metadata_files_blob_storage_attachment_id_bsa on npm_remote_metadata_files_p59: time=0.044 calls=1
 Trigger for constraint fk_npm_remote_metadata_files_namespace_id_namespaces on npm_remote_metadata_files_p59: time=0.004 calls=1
 Trigger for constraint fk_npm_remote_metadata_files_npm_remote_package_id on npm_remote_metadata_files_p59: time=0.042 calls=1
 Trigger for constraint fk_npm_remote_metadata_files_ns_id_blob_sha256_blobs on npm_remote_metadata_files_p59: time=0.034 calls=1
 Execution Time: 0.343 ms

Plan — first fill (INSERT path, no prior row for the (package, kind)):

 Insert on npm_remote_metadata_files  (cost=0.00..0.01 rows=0 width=0) (actual time=0.347..0.347 rows=0 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: unique_npm_remote_metadata_files_ns_id_pkg_id_kind
   Tuples Inserted: 1
   Conflicting Tuples: 0
   Buffers: shared hit=116
   ->  Result  (cost=0.00..0.01 rows=1 width=130) (actual time=0.001..0.001 rows=1 loops=1)
 Planning Time: 0.058 ms
 Trigger for constraint fk_npm_remote_metadata_files_blob_storage_attachment_id_bsa on npm_remote_metadata_files_p59: time=0.365 calls=1
 Trigger for constraint fk_npm_remote_metadata_files_namespace_id_namespaces on npm_remote_metadata_files_p59: time=0.007 calls=1
 Trigger for constraint fk_npm_remote_metadata_files_npm_remote_package_id on npm_remote_metadata_files_p59: time=0.287 calls=1
 Trigger for constraint fk_npm_remote_metadata_files_ns_id_blob_sha256_blobs on npm_remote_metadata_files_p59: time=0.268 calls=1
 Execution Time: 1.296 ms

Plan — nil upstream_etag branch. nullableStringExpr renders NULL inline instead of $7::text/$10::text, so the statement carries 8 placeholders rather than 10; plan shape, arbiter and partition routing are identical, confirming the nullable-etag rendering does not change how the statement executes:

 Insert on npm_remote_metadata_files  (cost=0.00..0.01 rows=0 width=0) (actual time=0.111..0.111 rows=0 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: unique_npm_remote_metadata_files_ns_id_pkg_id_kind
   Tuples Inserted: 0
   Conflicting Tuples: 1
   Buffers: shared hit=27
   ->  Result  (cost=0.00..0.01 rows=1 width=130) (actual time=0.001..0.001 rows=1 loops=1)
 Planning Time: 0.054 ms
 Trigger for constraint fk_npm_remote_metadata_files_blob_storage_attachment_id_bsa on npm_remote_metadata_files_p59: time=0.050 calls=1
 Trigger for constraint fk_npm_remote_metadata_files_namespace_id_namespaces on npm_remote_metadata_files_p59: time=0.006 calls=1
 Trigger for constraint fk_npm_remote_metadata_files_npm_remote_package_id on npm_remote_metadata_files_p59: time=0.043 calls=1
 Trigger for constraint fk_npm_remote_metadata_files_ns_id_blob_sha256_blobs on npm_remote_metadata_files_p59: time=0.256 calls=1
 Execution Time: 0.256 ms

Timings: repoint — planning 0.033ms, execution 0.343ms, total 0.376ms. First fill — planning 0.058ms, execution 1.296ms, total 1.354ms. Nil-etag repoint — planning 0.054ms, execution 0.256ms, total 0.310ms.

Query notes: none. All three statements pin namespace_id and prune to exactly one of the 64 hash partitions, every predicate lands in an Index Cond (no Seq Scan appeared at 5000 rows in the target partition, so no larger-seed re-run was needed), and plan-versus-actual rows match exactly on both reads.