Artifact Registry ADR 007: index npm_packages for tombstone discovery

What

Adds (namespace_id, soft_deleted_at DESC) WHERE soft_deleted_at IS NOT NULL to npm_packages in ADR-007.

Why

ADR-007 places this index at version level for every format — maven_versions, maven_remote_versions, npm_versions, npm_remote_versions — plus repositories, container_manifests and container_remote_manifests. No *_packages table has one, on the reasoning that a package is reachable by walking up from a tombstoned version.

That reasoning has one hole. npm's single-version unpublish soft-deletes the parent package when the last active version goes, so the package outlives its versions. Once those are purged there is no version to walk up from and no repository tombstone to walk down from, and the package row is unreachable — which is exactly the state where live npm_metadata_files rows sit under a tombstoned npm_packages row and the foreign key stalls the delete permanently.

Without the index the only ways to reach it are a sequential scan of npm_packages or one partial index per table, and the second is the alternative the S20-A design rejects on cost.

npm_packages is the only package table affected today: Maven has no delete path at any layer and the remote caches are read-only. maven_packages opens the same hole once S17 Phase 4 gives Maven a delete verb — deliberately not added here, since the writer does not exist yet. Flagging it for the reviewer rather than pre-empting it.

Consumer

S20-A's purger scans this index. See gitlab-org/ops/artifact-registry!1331 (merged).