chore(datastore): add artifact tombstone marker and discovery indexes

Why

An artifact delete does bounded work in the request and leaves subtree removal to the lifecycle purger: mark the row, answer 202, reap later (Spec: Removal is deferred behind a marker). Nothing on main can carry that contract. container_images has no marker column, four of the nine bulk-delete collection tables have no created_at for the acceptance-time predicate to read, and no index finds a marked row.

This lands those three pieces and nothing else. The schema is inert: no read filters the marker and no writer sets it. The plan sequences it that way so each piece merges safely alone, with the read predicates next and the partial-unique swap last.

What

• Five columns, not the one the spec names. The spec scopes delete_all by created_at, and four backing tables never had it: container_images, maven_packages, npm_packages, and maven_files. The other five carry it already, and the plan's Research Findings records the correction.
• NOT NULL DEFAULT now() keeps the acceptance time on the database clock the spec's wording requires, rather than the application host's clock a UUIDv7-derived time would reintroduce.
• Both discovery indexes are partial on IS NOT NULL, indexing exactly the marked rows a reap scans. Five sibling marker indexes already have this shape.
• The index builds block by choice, not by constraint. CONCURRENTLY is unsupported on a partitioned parent and the tables are empty before launch, so each migration carries the instruction to switch to the per-partition path if that ever stops holding.
• The versions are 120300 through 120500 because main took 120000 through 120200 while this was in review, and goose panics on a duplicate version rather than returning an error.
• A rolling deploy is safe: each of the four tables has exactly one INSERT, each names its columns, and none names created_at. No tool enforces that, because unqueryvet does not fire on go-jet receivers, so later writers have to keep naming columns.
• 1,117 reviewable lines against the 500-line guidance in docs/dev/development-model.md. The suite is 861 of them and migration rationale comments are 206 more, leaving 28 lines of executable SQL across 14 statements. Splitting the suite off the migrations would break the test-first commit order.

Spec coverage

Schema object	Migration	Tests
container_images.soft_deleted_at, nullable timestamptz, no default	20260811120300	MarkerColumnIsNullableWithNoDefault, MarkerRoundTripsThroughItsPartition
created_at timestamptz NOT NULL DEFAULT now() on the four tables	20260811120400	TestDeleteAllCreatedAt_ColumnsAreNotNullAndDefaultToNow, TestDeleteAllCreatedAt_StampsOnInsertThroughEveryParent, one positive subtest per table plus a NOT NULL rejection
Both indexes keyed (namespace_id, soft_deleted_at DESC) with predicate soft_deleted_at IS NOT NULL	20260811120500	DiscoveryIndexesExistWithKeysAndPredicate, exact key list and exact predicate
One attached child index per partition	20260811120500	EveryPartitionCarriesADiscoveryChild, partition count from the catalog and child names through pg_inherits
A discovery scan and a trash listing both ride the index	20260811120500	DiscoveryScanIsIndexBacked, both directions over the one DESC key
All three migrations revert	all three	ObjectsAbsentAfterDown, presence asserted at head first, one DownTo walk, head restored in cleanup
Transaction directives match each migration's lock profile	all three	TransactionDirectivesMatchTheLockBudget, parses the SQL with no chain walk
The reversibility walk targets the right version	20260811120300	MarkerVersionMatchesItsFilename

The marked-subtree 404s, the delete_all predicate, and re-pushing a marked image's name need the read gates, the bulk workers, and the partial-unique swap that later steps land. Each is named in the plan rather than implied here.

Database Review Evidence

Migrations

Note

Timings are from CI (db:migrate matrix, goose verbose) against an empty database, in apply / rollback order per PG version. Production-scale validation via Database Lab is not yet available. See Database review evidence for the matrix rationale and how to read the numbers.

Migration	PG 16	PG 17	PG 18
20260812150000_add_container_images_soft_deleted_at.sql	OK (4.58ms / 7.12ms)	OK (6.12ms / 14.68ms)	OK (14.03ms / 17.99ms)
20260812150100_add_delete_all_created_at_columns.sql	OK (28.25ms / 45.61ms)	OK (46.66ms / 109.91ms)	OK (172.61ms / 151.89ms)
20260812150200_add_artifact_tombstone_discovery_indexes.sql	OK (266.24ms / 21.52ms)	OK (62.26ms / 43.81ms)	OK (140.04ms / 46.41ms)

Migration notes:

• These timings supersede the pre-merge numbers this section carried until the merge: the table now reads from the merge-train pipeline that gated the merge on 2026-08-13 (the jobs linked in the header), replacing the 2026-08-12 merged-results run (jobs 15859971545-47). Where the two runs disagree, the bullets below say how.
• The PG 18 apply gap on add_delete_all_created_at_columns reproduces and widens: 172.61ms against PG 17's 46.66ms is a factor of 3.7, up from 3.0 in the superseded run, and this job's re-up phase agrees at 3.8. The explanation stands: the migration alters four hash-partitioned parents, touching 260 relations, and PG 18's per-relation catalog work costs more.
• The PG 16 apply gap on add_artifact_tombstone_discovery_indexes now straddles the catalog's 2x threshold instead of clearing it: 266.24ms against PG 18's 140.04ms is 1.9x in this run's Up phase, and the same job's re-up phase reads 2.11x (260.13ms against 123.31ms). PG 16's absolute time is stable across every measurement to date (241.5-268.9ms over four runs). The ratio moves because PG 18's time does not hold still. The plausible cost is unchanged: the two parent-level builds recurse to 128 partition children.
• New above the threshold this run: add_container_images_soft_deleted_at, where PG 18 applies in 14.03ms against PG 17's 6.12ms, a factor of 2.3 (re-up 2.7, superseded run 1.7). The direction matches the created_at gap, consistent with the same per-relation cost at one 64-partition parent, but the absolute times are 6-16ms, at the scale of timing noise on an empty database.
• The rollback ratios remain unflagged variance: this run's widest cross-version rollback gap is 1.38x.
• Nothing here approaches the boot budget. The slowest cell is 266.24ms, no cell reaches 1s on an empty database, and no cell reports FAILED or EMPTY.
• Empty-database timings poorly predict production runtime. These tables are empty before launch, which is the assumption the migrations state and the reason they take the blocking index path.

Context for LLM agents

The schema lands across three steps because the ordering is load-bearing. Columns and indexes have no readers or writers, so they deploy alone. Every container_images predicate lands next, behavior-preserving while no writer exists. The partial-unique swap goes last, because a still-running predicate-less binary cannot use a partial arbiter. The first marker writer waits on both, since a mark written before the gates exist would be readable, and a re-push against it would either resurrect the marked row or double-list the name.

Rejected alternatives:

• Per-partition CONCURRENTLY builds, the shape a populated table needs. Rejected because 64 partitions make it 65 statements per index for no gain on empty tables.
• Deriving the acceptance time from the UUIDv7 primary key. Rejected because it reintroduces the application-host clock the database-clock wording exists to exclude.
• A static parse instead of the runtime DownTo reversibility walk. go-testing.md argues against runtime down-walks unqualified, and a static in-tree precedent exists. Kept the walk because the merged plan names this file's shape, three sibling suites in the package use it, and the suite also carries the cheap static check. Reconciling that guidance with the practice belongs in its own change.
• Backfilling created_at. Not done: pre-existing rows stamp at migration time, which is acceptable while the tables are empty.

Non-goals: no read predicate, no writer, no partial-unique swap, no purge job, and no reaping. The plan's Status table is filled once for the whole step wave, so an empty row here is expected.

Related to #313 (closed)