feat(npm): single-version unpublish delete-side queries (Step 18 part 1/2)

📦 Summary

Part 1 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). Each part targets the previous one (part 1 targets main); review and merge bottom-up, part 1 first. The split is by entity, so each MR ships its features together with their tests.

✂️ This part — single-version unpublish primitives

Adds the three delete-side datastore methods the single-version unpublish flow (Step 19) composes in one transaction. Each takes the same jet transaction handle (qrm.DB) as the Step 5 write methods, and each is idempotent (re-running on an already-deleted or absent row is a no-op).

• SoftDeleteNpmVersion (internal/datastore/npm_versions.go) — sets soft_deleted_at on a version row.
• SoftDeleteNpmFilesByVersion (internal/datastore/npm_files.go) — soft-deletes a version's file rows.
• DeleteNpmTagsForVersion (internal/datastore/npm_tags.go) — hard-deletes a version's tag rows (npm_tags has no soft_deleted_at column, per ADR 007).

✅ Test coverage

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

• Soft-delete / hard-delete behavior against seeded rows.
• Idempotency — a second invocation is a no-op (acceptance criterion for Step 18).
• Namespace isolation — a row in another namespace is untouched.
• Argument guards — nil context, nil db, zero namespace, non-positive id each return the method's sentinel error.

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. These are write-target queries, so each is seeded with 1 matching row plus 49 sibling rows (per the skill's write-target sizing) to confirm the WHERE clause discriminates and the partition key prunes; at that cardinality the planner correctly prefers a Seq Scan over the single pruned partition. Numbers do not capture production-scale effects. See Database review evidence for methodology. Expand each row 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.SoftDeleteNpmVersion	Update → Seq Scan	n/a (Seq Scan at 50-row seed)	1 / 1	1.75	1.173ms	25 / 2	1
datastore.SoftDeleteNpmFilesByVersion	Update → Seq Scan	n/a (Seq Scan at 50-row seed)	1 / 1	1.75	1.472ms	14 / 0	1
datastore.DeleteNpmTagsForVersion	Delete → Seq Scan	n/a (Seq Scan at 50-row seed)	1 / 1	1.75	0.122ms	2 / 0	1

datastore.SoftDeleteNpmVersion

Summary: Plan matches intent — the namespace_id literal prunes to a single partition (npm_versions_p46) and the filter discriminates correctly (1 matched, 49 removed by filter). The node is a Seq Scan because the seeded partition holds only 50 rows; at production cardinality the (namespace_id, id) lookup is served by pk_npm_versions (id, namespace_id). No anomalies.

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

Rendered SQL:

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

Bound args: [<seeded namespace uuid>, <target npm_versions.id>]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Update on npm_versions  (cost=0.00..1.75 rows=0 width=0) (actual time=0.170..0.170 rows=0 loops=1)
   Update on npm_versions_p46 npm_versions_1
   Buffers: shared hit=25 read=2 dirtied=2 written=1
   ->  Seq Scan on npm_versions_p46 npm_versions_1  (cost=0.00..1.75 rows=1 width=18) (actual time=0.003..0.006 rows=1 loops=1)
         Filter: ((soft_deleted_at IS NULL) AND (namespace_id = '<ns>'::uuid) AND (id = '1'::bigint))
         Rows Removed by Filter: 49
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=281
 Planning Time: 0.720 ms
 Trigger for constraint fk_npm_versions_namespace_id_namespaces on npm_versions_p46: time=0.231 calls=1
 Trigger for constraint fk_npm_versions_npm_package_id_npm_packages on npm_versions_p46: time=0.658 calls=1
 Execution Time: 1.173 ms

Timings: planning 0.720ms, execution 1.173ms, total 1.893ms.

datastore.SoftDeleteNpmFilesByVersion

Summary: Plan matches intent — pruned to a single partition (npm_files_p45); filter matched the one file on the target version and removed the 49 siblings on another version. Seq Scan reflects the 50-row seed; at production scale the predicate (namespace_id, npm_version_id) with soft_deleted_at IS NULL is served exactly by the partial index unique_npm_files_ns_id_version_id_file_name (namespace_id, npm_version_id, file_name) WHERE soft_deleted_at IS NULL. No anomalies.

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

Rendered SQL:

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

Bound args: [<seeded namespace uuid>, <target npm_versions.id>]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Update on npm_files  (cost=0.00..1.75 rows=0 width=0) (actual time=0.062..0.063 rows=0 loops=1)
   Update on npm_files_p45 npm_files_1
   Buffers: shared hit=14
   ->  Seq Scan on npm_files_p45 npm_files_1  (cost=0.00..1.75 rows=1 width=18) (actual time=0.003..0.005 rows=1 loops=1)
         Filter: ((soft_deleted_at IS NULL) AND (namespace_id = '<ns>'::uuid) AND (npm_version_id = '53'::bigint))
         Rows Removed by Filter: 49
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=330
 Planning Time: 0.878 ms
 Trigger for constraint fk_npm_files_blob_storage_attachment_id_bsa on npm_files_p45: time=0.826 calls=1
 Trigger for constraint fk_npm_files_namespace_id_namespaces on npm_files_p45: time=0.010 calls=1
 Trigger for constraint fk_npm_files_npm_version_id_npm_versions on npm_files_p45: time=0.431 calls=1
 Execution Time: 1.472 ms

Timings: planning 0.878ms, execution 1.472ms, total 2.350ms.

datastore.DeleteNpmTagsForVersion

Summary: Plan matches intent — pruned to a single partition (npm_tags_p08); filter matched the one tag on the target version and removed the 49 siblings. Hard DELETE (no soft_deleted_at on npm_tags, per ADR 007). Seq Scan reflects the 50-row seed; at production scale the predicate (namespace_id, npm_version_id) is served exactly by index_npm_tags_on_ns_id_version_id (namespace_id, npm_version_id). No anomalies.

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

Rendered SQL:

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

Bound args: [<seeded namespace uuid>, <target npm_versions.id>]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Delete on npm_tags  (cost=0.00..1.75 rows=0 width=0) (actual time=0.007..0.007 rows=0 loops=1)
   Delete on npm_tags_p08 npm_tags_1
   Buffers: shared hit=2
   ->  Seq Scan on npm_tags_p08 npm_tags_1  (cost=0.00..1.75 rows=1 width=10) (actual time=0.002..0.004 rows=1 loops=1)
         Filter: ((namespace_id = '<ns>'::uuid) AND (npm_version_id = '55'::bigint))
         Rows Removed by Filter: 49
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=303
 Planning Time: 0.680 ms
 Execution Time: 0.122 ms

Timings: planning 0.680ms, execution 0.122ms, total 0.802ms.

🔗 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)