npm virtual: the association write path must refuse a self-referencing and a soft-deleted upstream

The npm_virtual_repository_upstreams schema deliberately admits two row shapes that no constraint can express and that the association write path has to refuse for itself. This issue is the one place that obligation is tracked. Until a route writes to this table the shapes are unreachable, so nothing is broken today; the change that first admits a write is the change that has to carry both refusals.

The two shapes

A virtual repository listing its own parent repositories row. A CHECK cannot compare the row's upstream_repository_id against the repository_id on its npm_virtual_repositories parent, because that value lives in another table. Admitted, the row makes the repository undeletable while empty: fk_nvru_upstream_repository_id_repositories is NO ACTION, so it refuses the delete of that repositories row even though the same statement's cascade would clear the association, and the reap finalizer retries it forever.

An upstream whose repositories row is already soft-deleted. This table carries no soft_deleted_at and no trigger reads the parent's, so the row inserts and the read path is what skips it. Admitted, the tombstone is never reaped and its storage is never reclaimed: RepositoryReaper.finalize issues its DELETE with no in-use pre-check, and the 23503 it gets back maps to ErrRepositoryNotEmpty on every attempt. The delete-side guard the management API will grow does not close this, because it refuses the delete rather than the association.

Why the schema does not close them

Both are recorded as deliberate in the migration's own catalog of admitted shapes, in internal/datastore/migrations/sql/20260828102654_create_npm_virtual_repository_upstreams.sql, which names the self-reference "the one admitted shape that is not merely odd but harmful" and puts the soft-deleted upstream on the same footing. The comment on fk_nvru_upstream_repository_id_repositories carries the measurement.

Both are also pinned as DB-legal by TestNPMVirtualUpstreamsConstraints_AcceptsSelfReferencingUpstream and TestNPMVirtualUpstreamsConstraints_AcceptsSoftDeletedUpstream in internal/datastore/migrations/npm_virtual_repository_upstreams_rowshapes_integration_test.go. Each test's message states the write-path obligation, so a reader of a green Accepts... test reaches this issue rather than concluding the shape is harmless.

What closes this

The change that first admits a write to npm_virtual_repository_upstreams rejects both shapes at the association route, with a test per shape. A rejection at the route is what is wanted, not a new constraint: neither shape is expressible as a CHECK, and a trigger would be caught by TestNPMVirtualUpstreamsSchema_NoTriggers, which asserts the table carries none.

Related to the npm_virtual_repository_upstreams schema step, #883 (closed).