feat(npm): whole-package unpublish cascade delete queries (Step 18 part 2/2)

📦 Summary

Part 2 of 2 of Step 18 — datastore npm write queries (delete-side) from docs/plans/2026-05-11-npm-hosted.md, spec docs/specs/S11-npm-hosted.md.

Step 18 is shipped as 2 stacked MRs to keep each within the review-size limit (~600 LoC ideal). This part stacks on part 1 and must merge after it.

✂️ This part — whole-package unpublish primitives

Adds the two delete-side datastore methods the whole-package unpublish flow (Step 20) composes. Both take the same jet transaction handle (qrm.DB) as the Step 5 write methods and are idempotent.

• SoftDeleteNpmPackage (internal/datastore/npm_packages.go) — sets soft_deleted_at on a package row; does not touch child rows.
• CascadeSoftDeleteNpmPackage (internal/datastore/npm_packages.go) — propagates soft_deleted_at to a package's npm_versions and npm_files, and hard-deletes its npm_tags, in batches of 1000 rows per statement.

Scope boundary: the cascade covers the version / file / tag legs only. The npm_metadata_files hard-deletion and the repositories.size_bytes counter are deliberately owned by Step 20's inline handler (per the plan's decomposition), not this datastore primitive. Per-batch transaction commit — and thus releasing locks between batches — is the caller's responsibility (the Step 20 job opens a fresh transaction per batch); the method's doc comment states this contract.

✅ Test coverage

Integration tests (internal/datastore/npm_write_integration_test.go) for these methods cover:

• Soft-delete behavior, idempotency, and namespace isolation for SoftDeleteNpmPackage.
• CascadeSoftDeleteNpmPackage correctness on a 5000-row fixture crossing the 1000-row batch boundary (asserts all versions/files soft-deleted and tags removed after cascade), plus idempotency.
• Argument guards for SoftDeleteNpmPackage and CascadeSoftDeleteNpmPackage (added to the shared guard test, which here covers all five delete-side methods).

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 run and the container torn down at the end. To exercise the version-page walk's index discrimination, the fixture is multi-package: one namespace with 5 packages × 5000 versions sharing an interleaved created_at range (25000 versions total); the target package also carries 5000 npm_files and 5000 npm_tags. SoftDeleteNpmPackage adds 49 sibling packages for write-target discrimination. Each CascadeSoftDeleteNpmPackage statement reflects one 1000-row batch; the files leg binds one 1000-version cursor page. See Database review evidence for methodology.

Method	Plan node	Index (row selection)	Rows (plan / actual)	Time	Buffers (hit / read)	Partitions
datastore.SoftDeleteNpmPackage	Update → Seq Scan	n/a (Seq Scan at 50-row seed; pk_npm_packages at scale)	1 / 1	0.446ms	24 / 0	1
datastore.CascadeSoftDeleteNpmPackage.files.versionPage (first page)	Limit → Index Only Scan	…_ns_id_npm_package_id_created_at_id (partial, WHERE soft_deleted_at IS NULL)	1000 / 1000	0.209ms	21 / 0	1
datastore.CascadeSoftDeleteNpmPackage.files.versionPage (keyset page)	Limit → Index Only Scan	…_ns_id_npm_package_id_created_at_id (partial); (ns, pkg, (created_at,id)) in Index Cond	1000 / 1000	0.278ms	21 / 0	1
datastore.CascadeSoftDeleteNpmPackage.files.update	Update → Hash Semi Join	inner: Bitmap Index Scan on …_ns_id_npm_version_id_file_name partial index	1000 / 1000	57.131ms	11236 / 0	1
datastore.CascadeSoftDeleteNpmPackage.versions	Update → Hash Semi Join	inner: Seq Scan + Limit on the namespace partition	1000 / 1000	38.862ms	10518 / 0	1
datastore.CascadeSoftDeleteNpmPackage.tags	Delete → Hash Semi Join	inner: Seq Scan + Limit on the namespace partition	1000 / 1000	1.343ms	1058 / 0	1

Query notes:

• The files-leg version-page walk is index-covered. It keysets on (created_at, id) via a row-value comparison and filters soft_deleted_at IS NULL, so the planner does an Index Only Scan on the partial composite index index_npm_versions_on_ns_id_pkg_id_created_at_id with (namespace_id, npm_package_id) equality and the (created_at, id) keyset all in the Index Cond — 21 buffers on a 25000-version / 5-package namespace, no npm_package_id heap filter, no sort. The soft_deleted_at IS NULL predicate is required: the index is partial on it, and without it the planner falls back to the (namespace_id, created_at) index with an npm_package_id heap filter that reads ~5× the rows it returns. Mirrors NpmVersionsByPackage.
• All three cascade modify legs prune to one partition (namespace_id on the outer modify). The outer modify Seq-Scans the namespace's single partition and Hash-Semi-Joins the ≤1000-id batch; that scan is bounded by the namespace's rows in the one partition (here 25000 npm_versions across 5 packages), not by the package — inherent to the batched WHERE id IN (batch) modify shape.
• FK-trigger dispatch dominates the soft-delete UPDATE cost (informational). The files-leg UPDATE spends ~51ms of its 57ms in RI-trigger invocations (fk_npm_files_npm_version_id_npm_versions 27.2ms, fk_npm_files_blob_storage_attachment_id_bsa 21.3ms, fk_npm_files_namespace_id_namespaces 2.7ms — 1000 calls each); the versions leg ~27ms of 39ms. Each call short-circuits (the UPDATE touches no FK column) but PostgreSQL dispatches one per row. Inherent to per-row FK triggers on bulk soft-delete; the cascade is an off-request background job (Step 20), so it is latency-tolerant. The tags DELETE has no outgoing-FK re-check and stays at 1.3ms.

datastore.SoftDeleteNpmPackage

Summary: namespace_id prunes to a single partition; the filter discriminates correctly (1 matched, 49 removed). Seq Scan reflects the 50-row write-target seed; at production scale (namespace_id, id) is served by the primary key. No anomalies.

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

Rendered SQL:

UPDATE public.npm_packages
SET soft_deleted_at = NOW()
WHERE ((npm_packages.namespace_id = $1::uuid) AND (npm_packages.id = $2)) AND (npm_packages.soft_deleted_at IS NULL);

Bound args: [<ns>, <target npm_packages.id>]

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Update on npm_packages  (cost=0.00..1.75 rows=0 width=0) (actual time=0.100..0.100 rows=0 loops=1)
   Update on npm_packages_p57 npm_packages_1
   Buffers: shared hit=24
   ->  Seq Scan on npm_packages_p57 npm_packages_1  (cost=0.00..1.75 rows=1 width=18) (actual time=0.004..0.006 rows=1 loops=1)
         Filter: ((soft_deleted_at IS NULL) AND (namespace_id = <ns>) AND (id = <pkg>))
         Rows Removed by Filter: 49
 Planning Time: 0.212 ms
 Trigger for constraint fk_npm_packages_namespace_id_namespaces on npm_packages_p57: time=0.015 calls=1
 Trigger for constraint fk_npm_packages_npm_repository_id_npm_repositories on npm_packages_p57: time=0.141 calls=1
 Execution Time: 0.446 ms

Timings: planning 0.212ms, execution 0.446ms.

datastore.CascadeSoftDeleteNpmPackage.files.versionPage (keyset page, multi-package fixture)

Summary: The files leg's per-page version read. Index Only Scan on the partial composite index index_npm_versions_on_ns_id_pkg_id_created_at_id, with (namespace_id, npm_package_id) equality and the (created_at, id) row-value keyset all pushed into the Index Cond. On a 25000-version / 5-package namespace it touches only the target package's versions — 21 buffers, no npm_package_id heap filter, no sort. The first-page form (no keyset bound) is identical minus the ROW(...) > ROW(...) condition.

Seed shape: npm_versions=25000 (5 packages × 5000, interleaved created_at)

Rendered SQL (keyset page; first page omits the ROW(...) > ROW(...) term):

SELECT npm_versions.id, npm_versions.created_at
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))
  AND (ROW(npm_versions.created_at, npm_versions.id) > ROW($3::timestamp with time zone, $4))
ORDER BY npm_versions.created_at ASC, npm_versions.id ASC
LIMIT $5;

Bound args: [<ns>, <package id>, <cursor created_at>, <cursor id>, 1000]

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Limit  (cost=0.29..183.37 rows=1000 width=16) (actual time=0.017..0.221 rows=1000 loops=1)
   Buffers: shared hit=21
   ->  Index Only Scan using npm_versions_p57_namespace_id_npm_package_id_created_at_id_idx on npm_versions_p57 npm_versions  (cost=0.29..732.60 rows=4000 width=16) (actual time=0.017..0.170 rows=1000 loops=1)
         Index Cond: ((namespace_id = <ns>) AND (npm_package_id = <pkg>) AND (ROW(created_at, id) > ROW('<cursor created_at>'::timestamp with time zone, <cursor id>)))
         Heap Fetches: 1000
         Buffers: shared hit=21
 Planning Time: 0.124 ms
 Execution Time: 0.278 ms

Timings: planning 0.124ms, execution 0.278ms (first page: 0.209ms).

datastore.CascadeSoftDeleteNpmPackage.files.update (one 1000-row batch)

Summary: The inner row-selection is index-served and namespace-pruned — a Bitmap Index Scan on the (namespace_id, npm_version_id, file_name) partial index with the bounded version-id page folded into npm_version_id = ANY(...), capped at LIMIT 1000. The outer modify carries namespace_id, so it prunes to the single namespace partition (npm_files_p57). Execution (57ms) is dominated by FK-trigger dispatch (~51ms) on the 1000 updated rows. See Query notes.

Seed shape: … blob_storage_blobs=1, blob_storage_attachments=1, npm_files=5000 (target package)

Rendered SQL (inner version-id list elided — one cursor page of up to npmCascadeBatchSize = 1000 bound version IDs):

UPDATE public.npm_files SET soft_deleted_at = NOW()
WHERE (npm_files.namespace_id = $1::uuid) AND (npm_files.id IN ((
  SELECT npm_files.id FROM public.npm_files
  WHERE ((npm_files.namespace_id = $2::uuid) AND (npm_files.npm_version_id IN ($3, $4, … $1002)))
    AND (npm_files.soft_deleted_at IS NULL)
  LIMIT $1003)));

Bound args: [<ns>, <ns>, <1000 version IDs>, 1000]

Plan (EXPLAIN (ANALYZE, BUFFERS), = ANY array elided):

 Update on npm_files  (cost=172.87..339.12 rows=0 width=0) (actual time=5.658..5.660 rows=0 loops=1)
   Update on npm_files_p57 npm_files_1
   Buffers: shared hit=11236 dirtied=23 written=23
   ->  Hash Semi Join  (cost=172.87..339.12 rows=1000 width=50) (actual time=0.344..1.056 rows=1000 loops=1)
         Hash Cond: (npm_files_1.id = "ANY_subquery".id)
         ->  Seq Scan on npm_files_p57 npm_files_1  (cost=0.00..139.50 rows=5000 width=18) (actual time=0.003..0.375 rows=5000 loops=1)
               Filter: (namespace_id = <ns>)
         ->  Hash  (actual time=0.336..0.336 rows=1000 loops=1)
               ->  Subquery Scan on "ANY_subquery"  (actual time=0.071..0.264 rows=1000 loops=1)
                     ->  Limit  (actual time=0.069..0.183 rows=1000 loops=1)
                           ->  Bitmap Heap Scan on npm_files_p57 npm_files_2  (actual time=0.069..0.133 rows=1000 loops=1)
                                 Recheck Cond: ((namespace_id = <ns>) AND (npm_version_id = ANY ('{1..1000}'::bigint[])) AND (soft_deleted_at IS NULL))
                                 Heap Blocks: exact=16
                                 ->  Bitmap Index Scan on npm_files_p57_namespace_id_npm_version_id_file_name_idx  (actual time=0.064..0.064 rows=1000 loops=1)
                                       Index Cond: ((namespace_id = <ns>) AND (npm_version_id = ANY ('{1..1000}'::bigint[])))
 Planning Time: 1.125 ms
 Trigger for constraint fk_npm_files_blob_storage_attachment_id_bsa on npm_files_p57: time=21.327 calls=1000
 Trigger for constraint fk_npm_files_namespace_id_namespaces on npm_files_p57: time=2.714 calls=1000
 Trigger for constraint fk_npm_files_npm_version_id_npm_versions on npm_files_p57: time=27.185 calls=1000
 Execution Time: 57.131 ms

Timings: planning 1.125ms, execution 57.131ms (≈51ms in FK triggers).

datastore.CascadeSoftDeleteNpmPackage.versions (one 1000-row batch)

Summary: Inner selection is a Seq Scan + Limit over the namespace partition (stops at 1000). The outer modify carries namespace_id, pruning to the single partition (npm_versions_p57); its Seq Scan covers the namespace's 25000 versions (5 packages) and Hash-Semi-Joins the 1000-id batch. Execution (39ms) is ~27ms FK-trigger dispatch on 1000 updated rows. See Query notes.

Seed shape: npm_versions=25000 (namespace total)

Rendered SQL:

UPDATE public.npm_versions SET soft_deleted_at = NOW()
WHERE (npm_versions.namespace_id = $1::uuid) AND (npm_versions.id IN ((
  SELECT npm_versions.id FROM public.npm_versions
  WHERE ((npm_versions.namespace_id = $2::uuid) AND (npm_versions.npm_package_id = $3)) AND (npm_versions.soft_deleted_at IS NULL)
  LIMIT $4)));

Bound args: [<ns>, <ns>, <package id>, 1000]

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Update on npm_versions  (cost=154.50..831.25 rows=0 width=0) (actual time=11.998..12.000 rows=0 loops=1)
   Update on npm_versions_p57 npm_versions_1
   Buffers: shared hit=10518 dirtied=28 written=27
   ->  Hash Semi Join  (cost=154.50..831.25 rows=1000 width=50) (actual time=0.292..3.693 rows=1000 loops=1)
         Hash Cond: (npm_versions_1.id = "ANY_subquery".id)
         ->  Seq Scan on npm_versions_p57 npm_versions_1  (cost=0.00..597.50 rows=25000 width=18) (actual time=0.003..2.021 rows=25000 loops=1)
               Filter: (namespace_id = <ns>)
         ->  Hash  (actual time=0.286..0.287 rows=1000 loops=1)
               ->  Subquery Scan on "ANY_subquery"  (actual time=0.003..0.208 rows=1000 loops=1)
                     ->  Limit  (actual time=0.002..0.127 rows=1000 loops=1)
                           ->  Seq Scan on npm_versions_p57 npm_versions_2  (actual time=0.001..0.074 rows=1000 loops=1)
                                 Filter: ((soft_deleted_at IS NULL) AND (namespace_id = <ns>) AND (npm_package_id = <pkg>))
 Planning Time: 0.141 ms
 Trigger for constraint fk_npm_versions_namespace_id_namespaces on npm_versions_p57: time=3.154 calls=1000
 Trigger for constraint fk_npm_versions_npm_package_id_npm_packages on npm_versions_p57: time=23.469 calls=1000
 Execution Time: 38.862 ms

Timings: planning 0.141ms, execution 38.862ms (≈27ms in FK triggers).

datastore.CascadeSoftDeleteNpmPackage.tags (one 1000-row batch)

Summary: Hard DELETE (npm_tags has no soft_deleted_at, per ADR 007). Inner selection is a Seq Scan + Limit over the namespace partition; the outer Delete carries namespace_id, pruning to the single partition (npm_tags_p57). No FK re-check triggers (nothing references npm_tags), so execution is fast at 1.3ms.

Seed shape: npm_tags=5000 (target package)

Rendered SQL:

DELETE FROM public.npm_tags
WHERE (npm_tags.namespace_id = $1::uuid) AND (npm_tags.id IN ((
  SELECT npm_tags.id FROM public.npm_tags
  WHERE (npm_tags.namespace_id = $2::uuid) AND (npm_tags.npm_package_id = $3)
  LIMIT $4)));

Bound args: [<ns>, <ns>, <package id>, 1000]

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Delete on npm_tags  (cost=46.90..180.65 rows=0 width=0) (actual time=1.276..1.278 rows=0 loops=1)
   Delete on npm_tags_p57 npm_tags_1
   Buffers: shared hit=1058
   ->  Hash Semi Join  (cost=46.90..180.65 rows=1000 width=42) (actual time=0.318..1.042 rows=1000 loops=1)
         Hash Cond: (npm_tags_1.id = "ANY_subquery".id)
         ->  Seq Scan on npm_tags_p57 npm_tags_1  (cost=0.00..109.50 rows=5000 width=18) (actual time=0.003..0.388 rows=5000 loops=1)
               Filter: (namespace_id = <ns>)
         ->  Hash  (actual time=0.312..0.313 rows=1000 loops=1)
               ->  Subquery Scan on "ANY_subquery"  (actual time=0.004..0.213 rows=1000 loops=1)
                     ->  Limit  (actual time=0.002..0.125 rows=1000 loops=1)
                           ->  Seq Scan on npm_tags_p57 npm_tags_2  (actual time=0.001..0.073 rows=1000 loops=1)
                                 Filter: ((namespace_id = <ns>) AND (npm_package_id = <pkg>))
 Planning Time: 0.452 ms
 Execution Time: 1.343 ms

Timings: planning 0.452ms, execution 1.343ms.

🔗 Stacked MRs (review/merge bottom-up)

• feat(npm): single-version unpublish delete-side... (!669 - merged) • David Fernandez • 19.2
• feat(npm): whole-package unpublish cascade dele... (!670 - merged) • David Fernandez • 19.2 👈

Related to #136 (closed)