chore(datastore): maven metadata tombstone discovery index (S20-A Maven metadata purge plan: 1/5)

What

Adds the metadata-only discovery index for hosted Maven package-metadata tombstones on maven_files:

(namespace_id, soft_deleted_at DESC) WHERE soft_deleted_at IS NOT NULL AND maven_version_id IS NULL AND file_name = 'maven-metadata.xml'

The parent is created ON ONLY; one child per HASH(namespace_id) partition (64) is built CONCURRENTLY and then attached under a bounded lock. The Down drops the parent under the same bound. Schema only — discovery stays unactivated. The reaper, discovery queries, and activation land in later steps of the plan.

Size justification

This MR is intentionally over the 500-reviewable-LOC guideline. The plan's Step 1 Size forecast accepts one over-ceiling MR for the complete index family.

Group	Measured	Forecast
Migration SQL (20260916092249_add_maven_metadata_tombstone_index.sql)	631	600–700
Integration test (maven_metadata_tombstone_index_integration_test.go)	538	100–200
Checksum entry (migrations_checksum_test.go)	1 (±1)	≤ 20
Generated (structure.sql)	323	excluded

Splitting does not help: the index family exposes no reusable code seam to extract, and separating child builds from attachment would add partially deployed index and rollback states. The test file runs long because it pins the shape and predicate of 64 per-partition children plus the concurrent-build ordering and the Up/Down lock bounds; the forecast undercounted the partition enumeration, not the design.

Dependencies

• The ADR-007 handbook amendment (adding this index to the maven_files index list) is merged before this MR: handbook !21121. docs/adr/ is a synced mirror and is not edited here; the mirror picks the amendment up on its next daily sync.
• No dependency on other step MRs; this is the plan's first step.

Testing

TestMavenMetadataTombstoneIndex_* in internal/datastore/migrations/maven_metadata_tombstone_index_integration_test.go cover parent and per-partition shape and predicate, apply / rollback / re-apply, concurrent child builds before attachment, and the Up and Down lock bounds. Migration evidence is collected with the database review workflow.

Spec coverage

Mirrored from 12e29217d so it survives the squash-on-merge:

#	Criterion	Tests
S20-A §Hosted Maven package metadata tombstones	The index is a partial btree on (namespace_id, soft_deleted_at DESC) with predicate soft_deleted_at IS NOT NULL AND maven_version_id IS NULL AND file_name = 'maven-metadata.xml'.	TestMavenMetadataTombstoneIndex_ParentShape (exact key list and predicate, non-unique, no INCLUDE), TestMavenMetadataTombstoneIndex_EveryPartitionCarriesThePinnedChild (the same pair on every attached child).
S20-A §Hosted Maven package metadata tombstones	"The index needs an ADR-007 amendment in the handbook before the schema migration lands."	Not a test. Merge gate: the handbook amendment is owed before this step merges (plan Dependencies); the local docs/adr/ mirror is not edited here.
S20-A §The eligibility predicate and the discovery path, maven_files row	The metadata-only tombstone scan is namespace-scoped, bounded, and oldest-first; the index leads with namespace_id and the table is HASH (namespace_id), so pruning to one partition needs the namespace equality.	Index half: TestMavenMetadataTombstoneIndex_ParentShape (namespace_id leading, soft_deleted_at DESC), TestMavenMetadataTombstoneIndex_EveryPartitionCarriesThePinnedChild (one valid child per partition). The scan and its EXPLAIN are Step 3-owned.
S20-A §Hosted Maven metadata purge	"The discovery query uses the metadata-only partial index and prunes to one partition, verified with EXPLAIN over representative live and deleted rows."	Index half: the two shape tests above. The EXPLAIN half is Step 3-owned.
S20-A §Hosted Maven metadata purge	"Index migration apply, rollback, and re-apply leave the expected parent and partition indexes."	TestMavenMetadataTombstoneIndex_AppliesRollsBackAndReapplies (presence at head, parent and all 64 pinned children absent after DownTo(version-1), parent valid and children attached again after the Up), plus TestMavenMetadataTombstoneIndex_UpBuildsEveryChildBeforeAttaching.
S20-A §Schema and configuration	"No migration in this spec adds a column or drops one, and no CHECK constraint pins any soft_deleted_at to NULL."	TestMavenMetadataTombstoneIndex_MigrationTouchesOnlyIndexes (statement allowlist over both sections).
S10 §Reconciliation on deletion, step 3	The reconciler tombstones the package-level metadata row when the live version set is empty; S20-A removes the tombstone and its unused attachment after the retention window.	Tombstone writer is pre-existing (MavenFileStore.SoftDeletePackageMetadataFile); the purge that reads this index is Steps 2-5-owned. No new assertion here.

Clause	Tests
Index apply, rollback, and re-apply succeed, with the required predicate on the parent and every partition.	TestMavenMetadataTombstoneIndex_AppliesRollsBackAndReapplies, TestMavenMetadataTombstoneIndex_ParentShape, TestMavenMetadataTombstoneIndex_EveryPartitionCarriesThePinnedChild.
Follow the populated-partition path: parent ON ONLY build, one CREATE INDEX CONCURRENTLY per partition, then one ALTER INDEX ... ATTACH PARTITION per partition.	TestMavenMetadataTombstoneIndex_UpBuildsEveryChildBeforeAttaching (one guarded ON ONLY parent build, one guarded CONCURRENTLY build per partition, one attach per build).
Build children concurrently before attachment.	Same test: every build index sits below every attach index, and each build is preceded by a drop-before-create guard naming that same child.
Bound attach and rollback locks.	Same test plus TestMavenMetadataTombstoneIndex_DownDropsTheParentUnderItsLockBound: exactly one SET lock_timeout = '5s' before the first attach and one RESET after the last, with the same bracketed pair around the Down's DROP.
Check Jet regeneration for index-only type stability.	Not a Go test: the implementation regenerates the Jet types and the structure.sql snapshot, and the schema-drift CI check plus TestMigrations_UpDownUp are what fail if either moves.
sql/<next>_add_maven_metadata_tombstone_index.sql and the checksum entries.	mavenMetadataTombstoneIndexMigrationToken selects exactly that file through readMigrationFile; mavenMetadataTombstoneMigrationVersion parses its timestamp; knownHeadVersion in migrations_checksum_test.go moves to the same timestamp.

#	Condition	Tests
S20-A §Error Cases, purge job rows	Retention window changed between enqueue and run, foreign-key stall, duplicate fire, frozen namespace.	Steps 2-5-owned; Step 1 changes no runtime behavior.
S20-A §Error Cases, repository DELETE rows	destructive validation, the two 409s, tombstoned-repository 404s.	Not this step (repository DELETE) and unaffected by it.

#	Concern	Tests
S20-A §Security, "No injection surface is added."	Purger SQL lives in internal/datastore per ADR-023, built through jet or parameterized raw SQL.	Step 1 adds DDL only: the predicate is a literal, and TestMavenMetadataTombstoneIndex_MigrationTouchesOnlyIndexes pins the statement classes to index DDL and the lock bound.
S20-A §Security, "Purge failures are operationally visible rather than silent."	The tombstone-count gauge is the control; a purge that cannot complete must never report success.	Step 5-owned. The index landed here is what makes the metadata count a bounded scan.
S10 §Reconciliation on deletion	The metadata and its synthesized sidecars answer 404 once the erasure commits.	Step 4-owned; this step touches no read or write path.

Related to #1167

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
20260916092249_add_maven_metadata_tombstone_index.sql	OK (549.52ms / 26.4ms)	OK (178.14ms / 9.67ms)	OK (334.75ms / 19.51ms)