feat(npm): npm hosted step 20, part 1 of 3 — unpublish datastore layer + deleter

Part 1 of 3 of the npm hosted plan — Step 20: whole-package unpublish + inline cascade.

🗂️ Stacked MRs

Split into 3 stacked MRs to keep each within the review size limit (~600 ideal reviewable LoC). Each part targets the previous one (part 1 → main); review and merge bottom-up.

• feat(npm): npm hosted step 20, part 1 of 3 — un... (!699 - merged) • David Fernandez • 19.2 👈
• feat(npm): npm hosted step 20, part 2 of 3 — un... (!700 - merged) • David Fernandez • 19.2
• test(npm): npm hosted step 20, part 3 of 3 — un... (!701 - merged) • David Fernandez • 19.2

📦 What this part adds

The datastore foundation for the whole-package unpublish endpoint DELETE /{slug}/npm/{repo}/{package}/-rev/{rev}:

• Query / DML methods the cascade drives: LatestNpmVersionIDByPackage (the {rev} token source), SumNpmFileSizesByPackage, DeleteNpmMetadataFiles, DecrementRepositoryArtifactsCount, DecrementRepositorySizeBytes.
• NpmPackageUnpublishDeleter — the transaction envelope that runs the cascade atomically: soft-delete the npm_packages row, cascade-soft-delete its npm_versions and npm_files, hard-delete its npm_tags and npm_metadata_files, all in one transaction, rolling back on any error. Mirrors the OCI ContainerManifestDeleter pattern, keeping database/sql out of the format layer per ADR 023.
• transport.go — rev_mismatch (409) and version_not_found error codes and safe messages.
• Docs — reconciles S11 with the interim inline-cascade contract (the spec's River-job design is the S27 end-state) and back-fills the Step 20 plan Files entry.

Test coverage for these methods lands via the integration tests in parts 2 and 3, which exercise the handler end-to-end and so drive this layer.

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.LatestNpmVersionIDByPackage	Limit → Index Scan Backward	npm_versions_p25_pkey	1 / 1	0.28..0.35	0.021ms	3 / 0	1
datastore.SumNpmFileSizesByPackage	Aggregate → Hash Join	n/a (Append over 64 blob partitions)	1 / 1	63.03..63.04	0.394ms	55 / 0	1 (npm_files/npm_versions) + 64 (blob_storage_blobs)
datastore.DeleteNpmMetadataFiles	Delete → Seq Scan	n/a	3 / 3	0.00..1.04	0.137ms	4 / 0	1
datastore.DecrementRepositoryArtifactsCount	Update → Seq Scan	n/a	1 / 1	0.00..1.75	0.374ms	48 / 2	1
datastore.DecrementRepositorySizeBytes	Update → Seq Scan	n/a	1 / 1	0.00..1.75	0.402ms	50 / 0	1

datastore.LatestNpmVersionIDByPackage

Summary: Plan matches the method's intent: Limit drives an Index Scan Backward on the primary key of the namespace partition, with the namespace_id literal pruning to one of 64 partitions. The npm_package_id and soft_deleted_at IS NULL are post-index filters applied during the scan. Single buffer hit for the seek; execution 0.021ms at 5000 seeded versions. No anomalies.

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

Rendered SQL:

SELECT npm_versions.id AS "npm_versions.id"
FROM public.npm_versions
WHERE ((npm_versions.namespace_id = $1::uuid) AND (npm_versions.npm_package_id = $2)) AND (npm_versions.soft_deleted_at IS NULL)
ORDER BY npm_versions.id DESC
LIMIT $3;

Bound args: [<namespace-uuid>, <pkg-id>, 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..0.35 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan Backward using npm_versions_p25_pkey on npm_versions_p25 npm_versions  (cost=0.28..353.35 rows=5000 width=8) (actual time=0.009..0.009 rows=1 loops=1)
         Index Cond: (namespace_id = '002e8fff-6a35-4909-b089-3b4d777400ef'::uuid)
         Filter: ((soft_deleted_at IS NULL) AND (npm_package_id = '1'::bigint))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=406 read=1
 Planning Time: 1.571 ms
 Execution Time: 0.021 ms

Timings: planning 1.571ms, execution 0.021ms, total 1.592ms.

datastore.SumNpmFileSizesByPackage

Summary: Plan matches the method's intent — aggregate sum of blob sizes across all files for a package — via two Hash Joins: npm_files inner-joined with npm_versions (namespace-pruned to one partition each), then inner-joined with blob_storage_blobs via an Append over all 64 hash partitions. The 64-partition scan on blob_storage_blobs is a known sub-optimal characteristic (the join key is sha256, the partition column, but only namespace_id is a plan-time literal, so no partition pruning is possible). This is the storage-accounting trade-off documented in ADR 007 and in the method's comment; the long-term mitigation is a delayed-increment rollup table. The method has no soft_deleted_at filter, which is assessed correct (whole-package unpublish is the sole size_bytes decrement path today; a caveat comment flags it must be revisited if single-version unpublish adds size accounting). Execution 0.394ms at 100 seeded files (scattered across 64 blob partitions). Two known flags; both documented in code and accepted as-is.

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

Rendered SQL:

SELECT COALESCE(SUM(blob_storage_blobs.size), $1) AS "total"
FROM public.npm_files
     INNER JOIN public.npm_versions ON ((npm_versions.namespace_id = npm_files.namespace_id) AND (npm_versions.id = npm_files.npm_version_id))
     INNER JOIN public.blob_storage_blobs ON ((blob_storage_blobs.namespace_id = npm_files.namespace_id) AND (blob_storage_blobs.sha256 = npm_files.blob_sha256))
WHERE (npm_files.namespace_id = $2::uuid) AND (npm_versions.npm_package_id = $3);

Bound args: [0, <namespace-uuid>, <pkg-id>]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Aggregate  (cost=63.03..63.04 rows=1 width=32) (actual time=0.200..0.205 rows=1 loops=1)
   Buffers: shared hit=55
   ->  Hash Join  (cost=58.98..62.77 rows=100 width=8) (actual time=0.164..0.198 rows=100 loops=1)
         Hash Cond: (npm_files.blob_sha256 = blob_storage_blobs.sha256)
         Buffers: shared hit=55
         ->  Hash Join  (cost=4.75..8.27 rows=100 width=49) (actual time=0.040..0.058 rows=100 loops=1)
               Hash Cond: (npm_files.npm_version_id = npm_versions.id)
               Buffers: shared hit=4
               ->  Seq Scan on npm_files_p14 npm_files  (cost=0.00..3.25 rows=100 width=57) (actual time=0.005..0.013 rows=100 loops=1)
                     Filter: (namespace_id = '36cdc115-3ad6-4e60-b98b-d875648607e6'::uuid)
                     Buffers: shared hit=2
               ->  Hash  (cost=3.50..3.50 rows=100 width=24) (actual time=0.026..0.026 rows=100 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 14kB
                     Buffers: shared hit=2
                     ->  Seq Scan on npm_versions_p14 npm_versions  (cost=0.00..3.50 rows=100 width=24) (actual time=0.003..0.010 rows=100 loops=1)
                           Filter: ((namespace_id = '36cdc115-3ad6-4e60-b98b-d875648607e6'::uuid) AND (npm_package_id = '3'::bigint))
                           Buffers: shared hit=2
         ->  Hash  (cost=52.82..52.82 rows=113 width=57) (actual time=0.120..0.125 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 17kB
               Buffers: shared hit=51
               ->  Append  (cost=0.00..52.82 rows=113 width=57) (actual time=0.002..0.107 rows=100 loops=1)
                     Buffers: shared hit=51
                     ->  Seq Scan on blob_storage_blobs_p00 blob_storage_blobs_1  (cost=0.00..1.01 rows=1 width=57) (actual time=0.002..0.002 rows=1 loops=1)
                           Filter: (namespace_id = '36cdc115-3ad6-4e60-b98b-d875648607e6'::uuid)
                           Buffers: shared hit=1
                     ->  Seq Scan on blob_storage_blobs_p01 blob_storage_blobs_2  [... 62 more partition scans identical in structure ...]
 Planning:
   Buffers: shared hit=3429 read=1
 Planning Time: 8.546 ms
 Execution Time: 0.394 ms

Timings: planning 8.546ms, execution 0.394ms, total 8.940ms.

datastore.DeleteNpmMetadataFiles

Summary: Plan matches the method's intent: Delete targeting a single namespace partition (npm_metadata_files_p30) identified by a Seq Scan with namespace_id + npm_package_id filter. Removes all 3 cache rows (kinds 0, 1, 2) in one statement. Execution 0.137ms at 3 seeded rows. No anomalies.

Seed shape: namespaces=1, repositories=1, npm_repositories=1, npm_packages=1, blob_storage_blobs=1, blob_storage_attachments=1, npm_metadata_files=3

Rendered SQL:

DELETE FROM public.npm_metadata_files
WHERE (npm_metadata_files.namespace_id = $1::uuid) AND (npm_metadata_files.npm_package_id = $2);

Bound args: [<namespace-uuid>, <pkg-id>]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on npm_metadata_files  (cost=0.00..1.04 rows=0 width=0) (actual time=0.007..0.007 rows=0 loops=1)
   Delete on npm_metadata_files_p30 npm_metadata_files_1
   Buffers: shared hit=4
   ->  Seq Scan on npm_metadata_files_p30 npm_metadata_files_1  (cost=0.00..1.04 rows=3 width=10) (actual time=0.003..0.003 rows=3 loops=1)
         Filter: ((namespace_id = 'cd77d6dc-bd69-4bf5-a66c-78816ce2a59d'::uuid) AND (npm_package_id = '4'::bigint))
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=329
 Planning Time: 0.739 ms
 Execution Time: 0.137 ms

Timings: planning 0.739ms, execution 0.137ms, total 0.876ms.

datastore.DecrementRepositoryArtifactsCount

Summary: Plan matches the method's intent: Update targeting a single namespace partition (repositories_p23) via Seq Scan with namespace_id + id filter. 50 rows scanned (50 seed rows in partition), 1 updated. The GREATEST(artifacts_count - $1, $2) expression clamps the decrement at zero. Execution 0.374ms at 50 seeded rows. No anomalies.

Seed shape: namespaces=1, repositories=50

Rendered SQL:

UPDATE public.repositories
SET artifacts_count = GREATEST(repositories.artifacts_count - $1, $2)
WHERE (repositories.namespace_id = $3::uuid) AND (repositories.id = $4);

Bound args: [5, 0, <namespace-uuid>, <repo-id>]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Update on repositories  (cost=0.00..1.75 rows=0 width=0) (actual time=0.218..0.218 rows=0 loops=1)
   Update on repositories_p23 repositories_1
   Buffers: shared hit=48 read=2
   ->  Seq Scan on repositories_p23 repositories_1  (cost=0.00..1.75 rows=1 width=18) (actual time=0.005..0.007 rows=1 loops=1)
         Filter: ((namespace_id = '90124a44-5f30-45c9-856a-fbe981cc9d43'::uuid) AND (id = '55'::bigint))
         Rows Removed by Filter: 49
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=370
 Planning Time: 1.183 ms
 Trigger for constraint fk_repositories_namespace_id_namespaces on repositories_p23: time=0.025 calls=1
 Execution Time: 0.374 ms

Timings: planning 1.183ms, execution 0.374ms, total 1.557ms.

datastore.DecrementRepositorySizeBytes

Summary: Plan matches the method's intent: Update targeting a single namespace partition (repositories_p46) via Seq Scan with namespace_id + id filter, mirroring DecrementRepositoryArtifactsCount. 50 rows scanned, 1 updated. GREATEST(size_bytes - $1, $2) clamps at zero. Execution 0.402ms at 50 seeded rows. No anomalies.

Seed shape: namespaces=1, repositories=50

Rendered SQL:

UPDATE public.repositories
SET size_bytes = GREATEST(repositories.size_bytes - $1, $2)
WHERE (repositories.namespace_id = $3::uuid) AND (repositories.id = $4);

Bound args: [512000, 0, <namespace-uuid>, <repo-id>]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Update on repositories  (cost=0.00..1.75 rows=0 width=0) (actual time=0.214..0.215 rows=0 loops=1)
   Update on repositories_p46 repositories_1
   Buffers: shared hit=50
   ->  Seq Scan on repositories_p46 repositories_1  (cost=0.00..1.75 rows=1 width=18) (actual time=0.005..0.008 rows=1 loops=1)
         Filter: ((namespace_id = '7a7c1cc0-dfeb-41b3-b6ac-a494de3244d0'::uuid) AND (id = '105'::bigint))
         Rows Removed by Filter: 49
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=158
 Planning Time: 0.737 ms
 Trigger for constraint fk_repositories_namespace_id_namespaces on repositories_p46: time=0.027 calls=1
 Execution Time: 0.402 ms

Timings: planning 0.737ms, execution 0.402ms, total 1.139ms.

Query notes:

• datastore.SumNpmFileSizesByPackage (sub-optimal, known): scans all 64 blob_storage_blobs partitions. The blob_storage_blobs table is hash-partitioned on sha256; the join key is blob_storage_blobs.sha256 = npm_files.blob_sha256, and sha256 is not a plan-time literal, so no partition pruning is possible. Only namespace_id is bound as a literal at plan time, but blob_storage_blobs is partitioned on sha256, not namespace_id. This is the storage-accounting trade-off documented in ADR 007 (long-term mitigation: delayed-increment rollup tables). Accepted as-is; single pre-transaction read, off the lock path.
• datastore.SumNpmFileSizesByPackage (no soft_deleted_at filter, assessed correct): sums active and soft-deleted files together. Correct because whole-package unpublish is the sole repositories.size_bytes decrement path today; a caveat in the method comment flags it must be revisited if single-version unpublish adds size accounting.

📚 References

• Plan: https://gitlab.com/gitlab-org/ops/artifact-registry/-/blob/main/docs/plans/2026-05-11-npm-hosted.md
• Spec: https://gitlab.com/gitlab-org/ops/artifact-registry/-/blob/main/docs/specs/S11-npm-hosted.md

Related to #138 (closed)