TOCTOU Claude Code audit

This is a meta-issue tracking findings from a systematic audit of Omicron's database layer for time-of-check-to-time-of-use (TOCTOU) races — sequences of database operations where correctness depends on assumptions about database state that can be invalidated by concurrent operations between separate transactions. The audit was motivated by issues #10301 (missing rcgen bumps in allocate_floating_ip and silo_image_demote) and #8992 (IP pool unlink TOCTOU), both of which turned out to be instances of broader classes of bugs.

Audit conducted against main at 8061ac9fb.

[!WARNING]
Claude Code (Opus 4.7) performed this audit and wrote up this issue. I (@mergeconflict) haven't manually verified its findings or edited the issue description at all except to add this admonition. Treat everything here as innocent until proven guilty.

Summary

• Total findings: 33 distinct issues.
• By severity:
  • P1 (structural inconsistency, rcgen contract broken): 3
  • P2 (incorrect behavior, recoverable): 15
  • P3 (theoretical / narrow / cosmetic): 15
• Already tracked elsewhere: 7 findings are covered by #8992, #9340, or #10301.
• Novel (not filed elsewhere): 26 findings. Most cluster around two related patterns: missing parent-liveness checks on silo-child creates, and missing silo_delete sweeps. Individually novel but systemically one class.

Top 3 highest-impact findings

1. P1: vpc_create_subnet_raw bypasses vpc.subnet_gen. The only producer of vpc_subnet rows does not bump the rcgen that project_delete_vpc relies on. A concurrent vpc_create_subnet during project_delete_vpc can tombstone the VPC with a live subnet still pointing at it. Novel — not covered by any existing issue.
2. P2: nine distinct silo / silo_user child tables can be orphaned after silo_delete / silo_user_delete, because those deletes run a non-transactional post-commit sweep and the child creates don't check parent liveness. Includes scim_client_bearer_token, which is security-relevant — an orphaned token still authenticates against a tombstoned silo.
3. P2: ephemeral-IP-vs-unlink race. The unlink_ip_pool_from_external_silo_query CTE added to fix #8992 can't see a freshly-allocated ephemeral IP whose parent_id and project_id are both still NULL. The operator flow from #8992 is fixed; this variant is not.

The codebase is generally disciplined on the concurrency patterns that matter most (saga state CAS, state-generation ordering, collection-insert CTE, attach/detach CTE). The systemic gap is the boundary between the rcgen-gated transactional "core" of a delete and the non-transactional "cleanup" that follows — that boundary has not been fully formalized, and it keeps leaking.

Scope

What was audited

• All 16 rcgen columns in nexus/db-schema/src/schema.rs, all 27 DatastoreCollectionConfig impls in nexus/db-model/src/, and every rcgen-gated delete operation.
• Every child-creation path for every rcgen-gated collection, classified as SAFE (via collection-insert CTE or explicit rcgen-bumping custom CTE) or BUG.
• All 4 DatastoreAttachTargetConfig impls and every writer of the backing FK columns.
• Non-rcgen multi-step datastore methods in nexus/db-queries/src/db/datastore/ and nexus/src/app/*.rs, with emphasis on mutating methods.
• Cross-layer app→datastore races: all ~96 fetch_for/lookup_for occurrences in nexus/src/app/*.rs plus action-by-action review of ~15 saga files.
• IP pool operations in depth (follow-up to #8992), with adjacent floating-IP, ephemeral-IP, multicast-group, and internet-gateway operations.
• FK-bearing columns pointing at soft-deletable parents; each write path checked for parent-liveness assertion.
• All ~45 background tasks, classified by concurrency story.
• All ordering-style generation columns (state_generation, *_gen) and their update paths.

What was NOT audited

• Deployment / blueprint execution / inventory collection. These have their own concurrency story (target-generation CAS, blueprint rendezvous) and deserve a separate audit.
• Region-replacement / volume state-machine internals. Spot-checked only; defensively coded with explicit CAS, but a deep audit would be warranted.
• Saga framework internals (saga_state, saga_node_event).
• Service-networking tables (bgp_*, switch_port_*, bootstore_*, loopback_address). Rack-setup data, different concurrency model.
• Audit log. Append-only; FK columns informational.

Known blind spots

• The audit did not attempt to reproduce any bug with a concrete failing test. Findings are by code inspection only.
• Individual saga internals of region_replacement, volume, and migration beyond entry points.
• Concurrency-timing tests are not part of this audit.

P1 findings (structural inconsistency)

All three P1 findings share a shape: there is a declared DatastoreCollectionConfig (rcgen) contract between a parent and a child, the parent's delete relies on rcgen to gate tombstoning, but at least one producer of the child bypasses the collection-insert CTE and fails to bump the parent's generation. The result is that a concurrent child create can race through the rcgen guard, leaving an orphan or a tombstoned parent with a live child.

P1-1: vpc_create_subnet_raw doesn't bump vpc.subnet_gen (novel)

• Locations:
  • Producer: nexus/db-queries/src/db/datastore/vpc.rs:989 (the call to InsertVpcSubnetQuery inside vpc_create_subnet_raw).
  • The CTE itself: nexus/db-queries/src/db/queries/vpc_subnet.rs (InsertVpcSubnetQuery) — does a plain INSERT INTO vpc_subnet without touching vpc at all.
  • Consumer: nexus/db-queries/src/db/datastore/vpc.rs:602 (project_delete_vpc UPDATE gated on vpc.subnet_gen).
  • DatastoreCollectionConfig<VpcSubnet> for Vpc impl: nexus/db-model/src/vpc.rs:120.
• Race: T1 project_delete_vpc passes subnet-emptiness check, then T2 vpc_create_subnet inserts a new subnet without bumping subnet_gen, T1's UPDATE WHERE subnet_gen = old passes, VPC is tombstoned with a live subnet still pointing at it.
• Fix proposal: Route vpc_create_subnet_raw through Vpc::insert_resource(...) machinery (the collection-insert CTE already knows how to bump subnet_gen and assert vpc.time_deleted IS NULL). This requires making InsertVpcSubnetQuery the "insert query" input to the collection-insert CTE. The existing pattern is well-illustrated by create_network_interface_raw_conn at nexus/db-queries/src/db/datastore/network_interface.rs:393, which wraps the InsertQuery CTE inside VpcSubnet::insert_resource.
• Existing issue: none. Needs filing.

P1-2: allocate_floating_ip doesn't bump project.rcgen (known: #10301)

• Locations:
  • Producer: nexus/db-queries/src/db/datastore/external_ip.rs:281 (allocate_floating_ip via NextExternalIp CTE at nexus/db-queries/src/db/queries/external_ip.rs:654-680).
  • Consumer: nexus/db-queries/src/db/datastore/project.rs:264 (project_delete UPDATE gated on project.rcgen).
  • project_delete check: ensure_no_floating_ips_in_project at nexus/db-queries/src/db/datastore/project.rs:243.
• Race: T1 project_delete passes the floating-IP-emptiness check, T2 allocate_floating_ip inserts with project_id=X but only bumps ip_pool_range.rcgen, T1's UPDATE passes because project.rcgen isn't bumped.
• Fix proposal: The NextExternalIp CTE needs to additionally update project SET rcgen = rcgen + 1 WHERE id = X AND time_deleted IS NULL, with a sentinel-cast failure mode if project is gone. Alternatively, wrap the floating-IP allocation CTE in a larger CTE that performs the project-rcgen-bump as a prerequisite (similar to how link_ip_pool_to_external_silo_query uses sentinel casts).
• Existing issue: #10301.

P1-3: silo_image_demote doesn't bump project.rcgen (known: #10301)

• Locations:
  • Producer: nexus/db-queries/src/db/datastore/image.rs:214 (silo_image_demote does plain diesel::update(dsl::image).set(...) to set project_id from NULL to X).
  • Consumer: nexus/db-queries/src/db/datastore/project.rs:264 (project_delete UPDATE gated on project.rcgen).
  • project_delete check: ensure_no_project_images_in_project at nexus/db-queries/src/db/datastore/project.rs:244.
• Race: T1 project_delete passes the project-image-emptiness check, T2 silo_image_demote sets image.project_id = X without bumping anything, T1's UPDATE passes because nothing bumped project.rcgen.
• Fix proposal: This is a plain UPDATE that reassigns an FK column. Two viable fixes:
  1. Add a "move-into-collection" operation to DatastoreCollectionConfig. A new method that atomically bumps parent rcgen, asserts parent.time_deleted IS NULL, and updates the child's FK column. This would also fix the symmetric project_image_promote case. See Systemic recommendations.
  2. Fold the demote into a CTE that atomically checks project.time_deleted IS NULL, bumps project.rcgen, and updates the image FK — all in a single statement with sentinel casts.
• Existing issue: #10301.

P2 findings (incorrect behavior, recoverable)

The P2 population splits into three thematic clusters, plus a handful of standalone items.

Missing parent-liveness check on silo / silo_user children

The silo_delete and silo_user_delete datastore methods tombstone the parent in a transaction, then run a non-transactional sweep of child tables as a separate statement sequence. Any child whose create path does not assert parent.time_deleted IS NULL atomically — and most do not — can insert an orphan row after the sweep completes.

The child tables affected (all paths under nexus/db-queries/src/db/datastore/):

#	Child table	Entry point	Create path	Notes
P2-1	silo_user	local_idp_create_user	silo_user.rs:416-453
P2-2	silo_group	JIT login, SCIM	silo_group.rs:369	Race self-documented in source, consequence not addressed
P2-3	ssh_key	POST /v1/me/ssh-keys	ssh_key.rs:250
P2-4	device_access_token	OAuth device grant	device_auth.rs:88	Security-relevant
P2-5	console_session	Console login	console_session.rs:86
P2-6	silo_group_membership	JIT / SCIM	silo_group.rs:624
P2-7	certificate	POST /v1/certificates	certificate.rs:30
P2-8	identity_provider / saml_identity_provider	POST /v1/system/identity-providers/saml	identity_provider.rs:103
P2-9	scim_client_bearer_token	SCIM token create	scim.rs:64-98	Security-relevant — orphaned tokens still authenticate against tombstoned silo because scim_lookup_token_by_bearer doesn't join silo.time_deleted IS NULL

Fix directions (any of the following, listed from local to systemic):

1. Per-site local fix: wrap each child-create in a transaction that first does SELECT ... FROM silo WHERE id = ? AND time_deleted IS NULL FOR SHARE before the insert. This is straightforward but each site needs individual attention.

2. Promote silo and silo_user to rcgen collections. Add an rcgen column to both, implement DatastoreCollectionConfig<Child> for Silo (and for SiloUser) for every child table, and route each child insert through insert_resource. This is cleaner but invasive — many tables, schema change required.

3. Move the silo_delete post-commit sweep into a transaction or a saga (this single change eliminates all P2-1 through P2-9 findings by collapsing the sweep and the parent tombstone into one atomic step). The existing comment at silo.rs:523-525 explicitly asks for this. This is the recommended fix — see Systemic recommendations.

Missing silo_delete sweep (flip side of the above)

The case where silo_delete not only doesn't run transactionally but also doesn't clean up a given child table at all. The rows persist indefinitely, orphaned and unreferenced.

• P2-10: silo_delete does not sweep silo_image. Compound with project_image_promote bypassing silo.rcgen; even if promote bumped silo.rcgen, silo_delete never checks for silo_images. Orphaned silo_image rows persist after silo tombstone. Fix: tombstone image rows WHERE silo_id = X AND project_id IS NULL inside the silo_delete transaction (and reject the delete if any exist, matching the projects check).

• P2-11: silo_delete does not sweep scim_client_bearer_token. Compounds with P2-9 above: silo_delete does not tombstone the tokens, AND the create does not check silo liveness, AND scim_lookup_token_by_bearer does not join silo.time_deleted IS NULL. Fix: add the sweep + add a silo-liveness join in the lookup.

Non-transactional cleanup after tombstone (non-silo instances)

• P2-12: Ephemeral IP allocation races with silo-unlink (partial #8992). Producer: nexus/db-queries/src/db/datastore/external_ip.rs:209-213 (allocate_instance_ephemeral_ip via NextExternalIp). Consumer: unlink_ip_pool_from_external_silo_query at nexus/db-queries/src/db/datastore/ip_pool.rs:2202-2330. The unlink CTE's instance_ips/floating_ips sub-CTEs filter on parent_id IS NOT NULL and project_id IS NOT NULL respectively, but a freshly-allocated ephemeral IP has both columns NULL until begin_attach_ip runs. Allows operator to move a pool to OxideInternal with a live customer ephemeral IP attached. Fix direction: write silo_id directly to external_ip at allocation time so guards can filter by silo without traversing the back-reference.

• P2-13 (novel): unlink_ip_pool_from_external_silo_query does not check multicast_group for outstanding rows. Purely missing from the guard CTE — not a race. A non-racy DELETE /v1/system/ip-pools/{pool}/silos/{silo} can unlink a silo's multicast pool even when the silo has live multicast groups allocated. Predates the multicast feature, so #8992 doesn't cover it. Fix: add a multicast_groups sub-CTE mirroring the existing instance_ips/floating_ips branches.

Other P2 findings

• P2-14: probe_create not transactional — #9340.
• P2-15: probe_delete not transactional — #9340.
• Advisory (downgraded from P2): zpool_delete_self_and_all_datasets children check outside a transaction. Region allocation is already fenced by physical_disk.disk_policy = in_service AND disk_state = active, so no active exploit. Worth a transactional wrap preemptively, but not P2.

P3 findings (theoretical / low consequence)

• ip_pool_is_internal vs. concurrent ip_pool_reserve. Classification race. Currently unexploitable — ip_pool_reserve has no callers.
• ip_pool_unlink_silo second-step (gateway) cleanup non-transactional. #8992.
• ip_pool_delete's ip_pool_resource cleanup window. Analyzed safe.
• vpc_subnet_unset_custom_router detaches via plain UPDATE. Asymmetric with attach CTE.
• vpc_delete_router bulk child FK-clear. Self-documented as tolerated.
• ip_pool_link_silo default-gateway nested loops. #8992 (explicit TODO).
• ip_pool_unlink_silo gateway-teardown nested loops. #8992 (explicit TODO).
• physical_disk_delete no rcgen — only reached from tests today.
• region_allocation doesn't check volume.time_deleted in replacement sagas.
• ssh_keys_batch_assign can orphan instance_ssh_key.
• MetricProducerGc list/delete race.
• PhysicalDiskAdoption bails on first racing-conflict.
• migration_mark_failed unconditionally bumps both generations. Structurally fragile but documented-safe.
• image_delete unconditional time_deleted overwrite. Cosmetic idempotency issue.
• Vestigial rcgen columns: disk.rcgen, switch.rcgen, crucible_dataset.rcgen, volume.rcgen, vpc_router.rcgen, internet_gateway.rcgen — no active producer or consumer.

Patterns observed

These are the recurring structural shapes observed across the codebase.

Custom CTE bypasses collection-insert

A custom Diesel CTE that writes to a child table of an rcgen-governed collection but does not bump the parent's generation column. The consumer (the parent delete) relies on the rcgen guard that the producer silently bypasses.

Representatives: InsertVpcSubnetQuery (P1-1), NextExternalIp for floating IPs (P1-2), link_ip_pool_to_external_silo_query (no live contract violated because ip_pool_resource isn't in a DatastoreCollectionConfig), RegionAllocate (vestigial).

Recommendation: any custom CTE writing to a child of an existing DatastoreCollectionConfig parent should either (a) compose with insert_resource (as create_network_interface_raw_conn does — it wraps InsertQuery inside VpcSubnet::insert_resource), or (b) explicitly bump the parent generation and assert parent liveness via sentinel casts.

FK reassignment by plain UPDATE

A bare diesel::update(...).set(fk_column.eq(new_value)) that moves a child between collections without touching any generation column on either the old or new parent.

Representatives: silo_image_demote (P1-3), project_image_promote. Only two instances exist today. Both should be routed through a new "move into collection" operation on DatastoreCollectionConfig (see Systemic recommendations).

Multiple declared parents, only one enforced

A DatastoreCollectionConfig where a child has more than one declared parent but the child's insert path only goes through one of them.

Representative: Zpool has both Sled and PhysicalDisk as parents; zpool_insert_on_connection only goes through Sled::insert_resource. Currently benign.

Recommendation: either drop the unused parent declaration or enforce both.

Vestigial generation column

An rcgen column in the schema that has neither a producer (nothing bumps it) nor a consumer (no delete guard reads it).

Representatives: disk.rcgen, switch.rcgen, crucible_dataset.rcgen, volume.rcgen, vpc_router.rcgen, internet_gateway.rcgen.

Recommendation: deprecate and eventually drop.

Unbumped generation column

An rcgen column that a delete guard reads but that no producer actually bumps. This is the most dangerous case — it looks like there is a contract (consumer reads it, DatastoreCollectionConfig declares it) but there isn't one.

Representative: DatastoreCollectionConfig<VpcSubnet> for Vpc (P1-1). Every producer bypasses the CTE, but project_delete_vpc relies on vpc.subnet_gen as a guard.

Non-transactional cleanup after tombstone

A parent-delete that runs a transactional tombstone with an rcgen guard, followed by a non-transactional post-commit sweep of child tables as a separate sequence of statements.

Representatives: silo_delete child sweeps (which compound with the "missing parent-liveness check" pattern below), zpool_delete_self_and_all_datasets, ip_pool_unlink_silo second step, vpc_delete_router subnet FK clear.

The boundary between the transactional core of a delete and the non-transactional cleanup is the main systemic source of remaining bugs in this codebase.

Mutable-classification check

A read of a mutable classification column followed by an action that depends on that classification, without holding the row between the read and the action.

Representative: ip_pool_is_internal read by app-layer code, followed by a mutation; concurrent ip_pool_reserve could flip the classification between the two. Currently unexploitable because ip_pool_reserve has no callers.

Asymmetric attach / detach

Attach goes through a formal CTE (attach_resource) but detach uses a plain UPDATE, losing the "which parent am I detaching from?" invariant and bypassing any generation bump.

Representatives: vpc_subnet_unset_custom_router, vpc_delete_router's bulk FK clear. Tolerated by the reading RPW's time_deleted IS NULL join; currently no active bug but structurally inconsistent.

Missing parent-liveness check

A child-create path that inserts a row referencing a parent by ID without asserting the parent's time_deleted IS NULL atomically with the insert. Benign in isolation, but combined with a non-transactional cleanup sweep on the parent-delete side (above), the child can be inserted after the sweep completes.

Representatives: all nine findings P2-1 through P2-9. The root cause is the post-commit non-transactional sweep in silo_delete / silo_user_delete, paired with child-create sites that don't join silo.time_deleted IS NULL.

Missing delete-side sweep

A child table with a FK to a soft-deletable parent, but the parent's delete implementation never tombstones the child. Orphans are permanent. Orthogonal to "missing parent-liveness check": even if every child-create had a liveness check, the parent-delete would still need to know about the child table.

Representatives: silo_image (P2-10), scim_client_bearer_token (P2-11).

Recommendation: a compile-time or test-time check that every table with silo_id appears in silo_delete's sweep; same for project_id in project_delete, vpc_id in project_delete_vpc, etc.

Systemic recommendations

Forest-level recommendations that would prevent whole classes of bugs. Ordered by estimated impact.

1. Move silo_delete's post-commit sweep into a transaction or a saga

This single change eliminates all 9 findings P2-1 through P2-9. The current silo_delete at nexus/db-queries/src/db/datastore/silo.rs:432 has two phases: a transactional tombstone with rcgen guard, and a non-transactional post-commit sweep at lines 527-614 (password hashes, silo_users, silo_group_memberships, silo_groups, identity_providers, saml_identity_providers, certificates, ip_pool_resource links, etc.). The code comment at lines 523-525 explicitly asks for this to move to a saga.

Trade-offs:

• A single mega-transaction may be too large for CRDB to handle efficiently on a silo with many users/groups.
• A saga introduces more machinery but can handle arbitrarily large cleanups idempotently.
• An intermediate: keep the sweep non-transactional but add a SELECT 1 FROM silo WHERE id = ? AND time_deleted IS NULL FOR SHARE as the first statement of every child-create datastore method. This is a much smaller change at the cost of per-site discipline.

Recommendation: saga-ify silo_delete, with each sweep step as its own saga action that is idempotent on re-run. This matches how other long-running deletes (instance_delete) already work.

2. Add a "move into collection" operation to DatastoreCollectionConfig

Today the trait handles "create child in collection" (via insert_resource) and "delete child" (via tombstone). It does not handle "change which collection a child belongs to" — which is what silo_image_demote and project_image_promote need. Both are currently broken (P1-3 and compound with P2-10).

A proposed signature:

fn move_resource<Parent, Child>(
    old_parent_id: Parent::CollectionId,
    new_parent_id: Parent::CollectionId,
    child_id: Child::ResourceId,
) -> CteQuery;

The generated CTE would:

1. Assert old_parent.time_deleted IS NULL AND new_parent.time_deleted IS NULL.
2. Bump both old_parent.rcgen and new_parent.rcgen.
3. Update child.<fk_column> = new_parent_id.

All in one statement, with sentinel-cast failure for either parent being gone. This fixes P1-3 and both image-promote/demote paths.

3. Test-level check: every FK to silo has a corresponding sweep in silo_delete

A test or build-time assertion that walks nexus/db-schema/src/schema.rs, finds every table with a silo_id column, and verifies that silo_delete either (a) tombstones rows in that table or (b) rejects the delete if such rows exist.

Implementation sketch: a #[test] in nexus/db-queries/tests/ that parses the schema and greps silo_delete for references. Same for project_id in project_delete, vpc_id in project_delete_vpc, etc.

This would have caught P2-10 (silo_image) and P2-11 (scim_client_bearer_token) mechanically. Worth implementing.

4. Clean up vestigial rcgen columns

disk.rcgen, switch.rcgen, crucible_dataset.rcgen, volume.rcgen, vpc_router.rcgen, internet_gateway.rcgen are all vestigial (no producer or consumer). Options:

• Drop the schema columns in a migration, simplify the DatastoreCollectionConfig impls to not reference them.
• Or, wire up the delete-side guard (but this is only worth it if the guard would catch a real race — today none of them would).

Recommend: deprecate and eventually drop. At minimum, file issues for each so they are discoverable.

5. Use of transaction_retry_wrapper given CRDB's implicit FOR UPDATE behavior

CockroachDB's SELECT acquires an implicit row lock under SERIALIZABLE isolation, so many check-and-act patterns that would be unsafe under READ COMMITTED are actually safe under SERIALIZABLE. This audit didn't find a case where transaction_retry_wrapper was incorrectly absent (in the sense of the isolation guarantee), but it did find cases where the absence of a txn boundary crossed a commit point (e.g. silo post-delete sweep). Those are not isolation issues — they are transactional-atomicity issues.

Conclusion: transaction_retry_wrapper is used appropriately. The issue is not whether to use it, but whether the cleanup that follows a transaction needs to be inside a bigger transaction / saga.

6. Consider updating RFD 192

RFD 192 (original rcgen/collection-insert CTE design) predates several of the patterns observed here. Recommended additions:

• Document the "custom-CTE-bypass" failure mode explicitly: custom CTEs that write to children of rcgen collections must either compose with insert_resource or manually bump the parent generation. Today this is only in code comments at collection_insert.rs.
• Document the "move into collection" gap (P1-3).
• Document the "non-transactional cleanup after tombstone" concern: post-transactional cleanup is a common source of orphans; if you have cleanup, it needs to be in a saga or a larger transaction.

7. Store silo_id directly on external_ip

P2-12's root cause is that the unlink CTE traverses parent_id -> instance -> project -> silo to attribute an ephemeral IP to a silo. A freshly-allocated ephemeral has parent_id = NULL, so the traversal misses it.

If external_ip.silo_id were populated at allocation time by NextExternalIp, the unlink CTE's guards would not need traversal. This would likely fix P2-12 and prevent future additions of this class.

8. Dedicated DatastoreCollectionConfig for silo + silo_user

This is an alternative to Recommendation #1. If silo had a formal collection relationship with each child table, each child insert would go through insert_resource (atomic parent-liveness + rcgen bump) and the post-delete sweep would have a natural "walk collection" structure.

Invasive; requires either per-child-table impls or a macro/derive. Less attractive than saga-ifying silo_delete (Recommendation #1).

Sub-issues to file

The findings above split into discrete filing units. Check each off as the corresponding issue is filed.

P1 (must file)

• vpc_create_subnet_raw does not bump vpc.subnet_gen, races with project_delete_vpc. Covers finding P1-1. New issue.
• silo_image_demote / project_image_promote should route through DatastoreCollectionConfig to bump silo/project rcgen. Covers P1-3. Could be added to #10301 since both touch the image path.

P2 groups (should file)

• silo_delete and silo_user_delete have non-transactional post-commit sweeps that race with concurrent child creates. Covers findings P2-1 through P2-9 as one systemic fix. Recommended implementation: saga-ify silo_delete.
• silo_delete does not tombstone silo_image or scim_client_bearer_token rows; add lint to catch future child tables. Covers P2-10 and P2-11. Should include the build-time check that every silo_id-bearing table is swept.
• Ephemeral IPs invisible to ip_pool unlink guard; populate external_ip.silo_id at allocation. Covers P2-12. Partial of #8992; could be updated to #8992 instead of filed new.
• ip_pool_unlink_silo does not check outstanding multicast_group rows. Covers P2-13. Simple CTE change.

P3 cleanup (nice to file)

• Clean up vestigial rcgen columns (disk, switch, crucible_dataset, volume, vpc_router, internet_gateway).
• vpc_subnet.custom_router_id is updated via plain UPDATE instead of VpcRouter::detach_resource. Covers the vpc_subnet_unset_custom_router and vpc_delete_router bulk FK clear findings from the asymmetric attach/detach pattern.
• MetricProducerGc list/delete race. Trivial fix.
• PhysicalDiskAdoption early-exit on conflict.
• region_allocation doesn't check volume.time_deleted in replacement sagas.
• migration_mark_failed fragile generation bump.
• Add move_resource operation to DatastoreCollectionConfig (systemic). Covers P1-3 and any future move-child-between-parents operations.
• Update RFD 192 with post-audit patterns (documentation).

Covered by existing issues (no new filing required)

• #10301 — allocate_floating_ip and silo_image_demote rcgen gaps (P1-2, P1-3).
• #9340 — probe_create and probe_delete non-transactional.
• #8992 — ip_pool_unlink_silo TOCTOU and related IP pool ops (covers the original operator race and the second-step gateway cleanup).

References

• RFD 192 — original rcgen / collection-insert CTE design. If you update it, consider citing this audit.
• #10301 — floating_ip and silo_image_demote rcgen gaps (covers P1-2, P1-3).
• #9340 — probe_create / probe_delete non-transactional.
• #8992 — ip_pool_unlink_silo TOCTOU and related IP pool ops (partially covers P2-12, plus the related IP pool races and the gateway-cleanup P3).

Start by reading the audit locations in nexus/db-queries/src/db/datastore/ and the DatastoreCollectionConfig implementations in nexus/db-model/src/, beginning with the P1 findings. Validate one race against the cited producer and consumer code; the audit names no reproducing tests. Done requires independently verifying and tracking the findings as focused issues or fixes, rather than treating this broad meta-issue as one change.