chore(datastore): add repositories and npm_packages tombstone-discovery indexes (S20-A plan: 1/21)

What this step delivers

Two tombstone-discovery indexes, one on repositories and one on npm_packages. Each index is keyed (namespace_id, soft_deleted_at DESC) and partial on soft_deleted_at IS NOT NULL. Each is built ON ONLY the partitioned parent, and Postgres attaches one child index per partition. These are the two indexes the purger's discovery scans read: a trash listing reads newest-first, and a reap reads oldest-first off the same key.

New migration internal/datastore/migrations/sql/20260817120000_add_repositories_and_npm_packages_tombstone_indexes.sql. internal/datastore/migrations/structure.sql is regenerated from it with mise run db:dump-structure. internal/datastore/migrations/migrations_checksum_test.go moves its knownHeadVersion pin to the new head.

The migration adds no column, removes no column, and adds no constraint. Both builds sit under -- +goose NO TRANSACTION, so each build takes its locks in its own implicit transaction. Both statements carry IF NOT EXISTS, and both DROP INDEX statements in the Down carry IF EXISTS, so a repeat apply and a repeat rollback are no-ops. The third discovery index this spec names, on container_images, is already on main and outside this step.

New test file internal/datastore/migrations/tombstone_discovery_schema_integration_test.go, with seven //go:build integration test functions:

Test	What it falsifies
TestTombstoneDiscoverySchema_IndexesExistWithKeysAndPredicate	Wrong parent, wrong key list, wrong predicate. Both assertions compare for equality, so an extra key or an extra conjunct fails
TestTombstoneDiscoverySchema_EveryPartitionCarriesAChild	A missed attach on the per-partition build path
TestTombstoneDiscoverySchema_ScanIsIndexBacked	Both reader directions, a post-scan Sort, and more than one partition child in the plan
TestTombstoneDiscoverySchema_MarkerColumnsKeepTheirShape	A default or a NOT NULL on soft_deleted_at
TestTombstoneDiscoverySchema_NoCheckConstraintMentionsTheMarker	A CHECK constraint that names soft_deleted_at
TestTombstoneDiscoverySchema_MigrationBuildsIndexesAndNothingElse	Statement-verb allowlist: only CREATE INDEX and DROP INDEX, both names guarded
TestTombstoneDiscoverySchema_TransactionDirectiveMatchesTheLockBudget	A missing NO TRANSACTION directive, or a DO $$ batching block

Two of the seven pass at both ends by construction, because the scope-containment claim they carry has to hold before and after: _MarkerColumnsKeepTheirShape and _NoCheckConstraintMentionsTheMarker. The falsifiable half of that claim is _MigrationBuildsIndexesAndNothingElse, which did fail before the migration landed.

Spec coverage

Spec: docs/specs/S20-a-lifecycle-closed-beta.md Plan: docs/plans/2026-08-11-s20a-lifecycle-closed-beta.md, Step 1

Acceptance criteria

#	Criterion	Tests
AC-P1	Eligible repositories tombstone discovered and reaped; a fresh one is not, at zero and non-zero window	Steps 5 and 15. Not in this step
AC-P2	A soft_deleted_at IS NULL row is never discovered by any scan or walk	Partial: TestTombstoneDiscoverySchema_IndexesExistWithKeysAndPredicate pins the IS NOT NULL predicate, so a live row is not in either index. The scan and walk halves are Steps 5 to 14
AC-P3	The repositories, npm_packages and container_images scans use the added index and prune to one partition, by EXPLAIN	Partial: TestTombstoneDiscoverySchema_ScanIsIndexBacked asserts both index-backing and single-partition pruning for repositories and npm_packages. container_images is TestArtifactTombstoneSchema_DiscoveryScanIsIndexBacked, already on main. The scan code is Steps 5 and 7
AC-P4	The four version-level scans use the existing indexes and prune to one partition	Step 6. Not in this step
AC-P5	Re-running a purge on a reaped subtree is a no-op; a mid-walk abort leaves strictly fewer rows	Steps 14 and 15. Not in this step
AC-P6	The npm repository walk reaps live and already-tombstoned rows in one pass	Steps 8 and 14. Not in this step
AC-P7	Every purge transaction is bounded	Step 15. Not in this step
AC-P8	A purge job whose namespace stopped serving writes refuses retryably, per lifecycle column	Step 15a. Not in this step
AC-F1	npm version reap, including each file's attachment, on npm's own reference check	Step 8. Not in this step
AC-F2	Unpublish then republish before the reap leaves no orphaned row	Step 8. Not in this step
AC-F3	npm package reap with its tags, metadata files and attachments, with no 23503	Step 8. Not in this step
AC-F4	DeleteNpmMetadataFiles removes each attachment in the same transaction	Step 8. Not in this step
AC-F5	Maven version and package reap	Step 9. Not in this step
AC-F6	Container repository reap through the existing per-artifact deleters	Step 13. Not in this step
AC-F7	An image index and its children are reaped in one purge, parent-first	Step 13. Not in this step
AC-F8	A tombstoned container_images row under a live repository is reaped through the same walk	Step 13. Not in this step
AC-F9	The container repository walk reaps live and already-tombstoned images in one pass	Step 13. Not in this step
AC-F10	Remote-cache subtree reaped for every remote table carrying the column	Steps 10, 11 and 12. Not in this step
AC-F11	container_remote_manifests and container_remote_blobs reaped once S16 lands them	Step 12, gated on S16. Not in this step
AC-F12	After a reap every referenced blob has zero blob_storage_attachments rows	Steps 8 to 14. Not in this step
AC-R1	DELETE without destructive, or with a non-boolean value, returns 400	Step 18. Not in this step
AC-R2	destructive=false on an empty repository returns 204 and frees the name	Step 18. Not in this step
AC-R3	destructive=false on a non-empty repository returns 409 and changes nothing	Step 18. Not in this step
AC-R4	That 409's message names the remedy the caller has, per format and kind	Step 18. Not in this step
AC-R5	destructive=true on a non-empty repository returns 202, tombstones, and enqueues in the same transaction	Steps 4 and 18. Not in this step
AC-R6	destructive=true on an empty repository returns 204, with no tombstone and no job	Step 18. Not in this step
AC-R7	After the 202 every route naming the repository returns 404; a mount from= answers 202	Steps 3 and 18. Not in this step
AC-R8	A create at a tombstoned name returns 409 until the finalizer runs	Step 18. Not in this step
AC-R9	The finalizer cannot succeed early with an artifact left in place	Step 14. Not in this step
AC-R10	A finalizer reaching an empty subtree removes the row, its format child, and every collection link	Step 14. Not in this step
AC-SW1	A tombstoned repository with no live purge job is re-queued within one sweep_interval	Step 16. Not in this step
AC-SW2	The sweep reads oldest-first, from a backwards scan of the DESC index with no Sort node	Partial: TestTombstoneDiscoverySchema_ScanIsIndexBacked's reap subtest asserts the backwards scan and the absent Sort on both indexes. The sweep's page behavior is Step 16
AC-SW3	An orphaned npm_packages row is enumerated on the added index and re-queued	Partial: the index half is TestTombstoneDiscoverySchema_IndexesExistWithKeysAndPredicate and ..._ScanIsIndexBacked. Enumeration is Steps 7 and 16
AC-SW4	A repository that already has a live purge job is not re-queued	Step 16. Not in this step
AC-SW5	A terminal-state prior job is re-queued, pinning UniqueByArgsWhileLive()	Step 16. Not in this step
AC-SW6	A permanently failing purge stays counted and re-queued at the bounded cadence	Steps 16 and 17b. Not in this step
AC-SW7	The sweep registers a worker for its periodic kind	Step 16. Not in this step
AC-SW8	A frozen namespace has nothing enqueued, and returns on the first tick after it clears	Steps 15a and 16. Not in this step
AC-V1	ContainerRepositoryStore.FindByNameInNamespace is not-found for a tombstoned repository, both formats	Step 3. Not in this step
AC-V2	RepositoryStore.FindByID is not-found for a tombstoned repository	Step 3. Not in this step
AC-V3	RepositoryStore.Update is not-found for a tombstoned repository	Step 3. Not in this step
AC-V4	Delete and the counter writers still act on a tombstoned row	Step 3. Not in this step
AC-V5	parentRepositoryIsActive returns ErrParentRepositoryMissing on a concurrent tombstone	Step 4. Not in this step
AC-V6	Each of the four container_images gates hides a tombstoned image	S17 Phase 4. Not in this step
AC-V7	Every /v2/ route resolving through FindByName answers 404 NAME_UNKNOWN	S17 Phase 4. Not in this step
AC-V8	The blob mount answers 202 when every source image linking the blob is tombstoned	S17 Phase 4. Not in this step
AC-V9	A from= naming a tombstoned image still mounts and answers 201	S17 Phase 4. Not in this step
AC-V10	A mount into a tombstoned destination name answers 201	S17 Phase 4. Not in this step
AC-V11	UpsertContainerImage does not resurrect a tombstoned image	S17 Phase 4. Not in this step
AC-V12	ContainerImageStore.Delete still acts on a tombstoned row	S17 Phase 4. Not in this step
AC-A1	The destructive DELETE emits no counter delta at the tombstone write	Step 19. Not in this step
AC-A2	Δsize and Δcomponents fire at the purger, with the delta set depending on the entry point	Step 19. Not in this step
AC-A3	Every emit site commits its source rows before issuing the delta	Step 19. Not in this step
AC-A4	A purge chunk emits from its committed affected-row aggregate	Step 19. Not in this step
AC-A5	No raw foreign-key cascade emits anything	Step 19. Not in this step
AC-S1	Both index migrations apply to the parent and every partition, and roll back cleanly	TestTombstoneDiscoverySchema_IndexesExistWithKeysAndPredicate, TestTombstoneDiscoverySchema_EveryPartitionCarriesAChild, TestTombstoneDiscoverySchema_MigrationBuildsIndexesAndNothingElse, TestTombstoneDiscoverySchema_TransactionDirectiveMatchesTheLockBudget. The runtime rollback is TestMigrations_UpDownUp, this package's one sanctioned chain walk
AC-S2	The migration adds no column and drops none, and no CHECK pins any soft_deleted_at to NULL	TestTombstoneDiscoverySchema_MigrationBuildsIndexesAndNothingElse, TestTombstoneDiscoverySchema_NoCheckConstraintMentionsTheMarker, TestTombstoneDiscoverySchema_MarkerColumnsKeepTheirShape. The structure.sql no-drift half is the db:structure-check CI job
AC-S3	unique_container_images_ns_id_cr_id_name is partial on the parent and every partition	TestContainerImagesPartialUnique_*, already on main. Its own migration owns it
AC-S4	Config load accepts retention_window: 0s and rejects the documented invalid windows	Step 2. Not in this step

Error cases

#	Condition	Tests
EC-1	DELETE omits destructive: 400, nothing written	Step 18. Not in this step
EC-2	destructive carries a non-boolean value: 400	Step 18. Not in this step
EC-3	destructive=false on a non-empty repository: 409, message split by remedy	Step 18. Not in this step
EC-4	A publish races the DELETE FROM repositories: 23503, classified non-empty	Step 18. Not in this step
EC-5	DELETE on a missing or already-tombstoned repository: 404	Step 18. Not in this step
EC-6	Create at a name held by a tombstoned repository: 409	Step 18. Not in this step
EC-7	Any client route naming a tombstoned repository: 404 in that route's vocabulary	Steps 3 and 18. Not in this step
EC-8	Blob mount whose from= names a tombstoned repository: 202	Step 18. Not in this step
EC-9	Purge job hits 23503 on a parent delete: job fails, sweep re-queues	Steps 14 and 16. Not in this step
EC-10	ContainerManifestDeleter refuses a referenced manifest: retried as a work-ordering error	Step 13. Not in this step
EC-11	The finalizer aborts because an artifact remains: job fails, tombstone stands	Step 14. Not in this step
EC-12	Repository already hard-deleted when the job runs: reported as success	Step 14. Not in this step
EC-13	Retention window changed between enqueue and run: predicate evaluated at run time	Step 15. Not in this step
EC-14	Sweep enumeration query fails: tick errors, River retries	Step 16. Not in this step
EC-15	Sweep interval elapses before enumeration completes: next tick re-enumerates oldest-first	Partial: TestTombstoneDiscoverySchema_ScanIsIndexBacked pins that oldest-first is an index-ordered backwards scan on both added indexes, which is what the no-starvation argument rests on. The tick behavior is Step 16
EC-16	Emit API unavailable at a purge or tombstone site: delta dropped and logged	Step 19. Not in this step
EC-17	Parent tombstoned between the gate and the child INSERT: ErrParentRepositoryMissing, unwrapped	Step 4. Not in this step
EC-18	A tombstoned coordinate reoccupied before the reap: both rows, purger keys on the tombstone	Step 8. Not in this step

Security considerations

#	Concern	Tests
SC-1	Authorization is unchanged and not weakened	Step 18. Not in this step: this step adds no request path and no query
SC-2	destructive is a confirmation, not an authorization control	Step 18. Not in this step
SC-3	Deletion is irreversible in closed beta and the API must say so	Step 18, in the OpenAPI description. Not in this step
SC-4	Input validation: destructive is a strict boolean, and no purger input is user-controlled	Steps 15 and 18. Not in this step
SC-5	No injection surface is added	Steps 5 to 15. Not in this step: this step adds no query. The test file's own two interpolations take a table name from a fixed in-test allowlist
SC-6	Error messages must not become an existence oracle	Steps 3 and 18. Not in this step
SC-7	Purge failures are operationally visible rather than silent	Steps 16, 17a and 17b. Not in this step

e2e scenario catalogs

The obligation did not fire. It covers feat and fix steps, and the plan sets this step's type to chore. The diff adds no request path and no query, so docs/testing/ gains no scenario and this step affects none. Step 18 is the one step whose Files: list names docs/testing/e2e/, for docker.md and oci.md.

Reviewable LOC

git diff --numstat "$(git merge-base origin/main HEAD)"...HEAD reports 1525 insertions and 7 deletions across five files. Measured at tip 336d1ecd, against merge base 3f940e57. The generated structure.sql accounts for 646 of those insertions and none of the deletions. Reviewable LOC is therefore 879 insertions and 7 deletions, which exceeds the 500-line threshold.

File	Insertions	Deletions	Group
internal/datastore/migrations/structure.sql	646	0	Generated by mise run db:dump-structure. 2 parent indexes, 128 child indexes, 128 attach statements. No reviewer reads it line by line, and db:structure-check asserts it
internal/datastore/migrations/tombstone_discovery_schema_integration_test.go	635	0	The test floor, authored before the implementation
internal/datastore/migrations/sql/20260817120000_add_repositories_and_npm_packages_tombstone_indexes.sql	237	0	The hand-written non-test surface. Two CREATE INDEX statements, two DROP INDEX statements, three squawk-ignore-file pragmas, and the rest is the header
internal/datastore/migrations/migrations_checksum_test.go	6	6	The knownHeadVersion pin and its doc comment
docs/plans/2026-08-11-s20a-lifecycle-closed-beta.md	1	1	The plan's Status row for Step 1, which now names this merge request

The development model asks for a split or a justification, and this paragraph is the justification rather than the number above. A split does not help, because the five groups have to land together: the migration, the dump it produces, the test floor that constrains it, the head-version pin TestHeadVersion reads, and the plan row that records this merge request.

Commit order on this branch

The branch carries 16 commits. chore(datastore): repositories and npm_packages tombstone indexes comes first. test(datastore): tombstone-discovery index schema assertions comes second. docs(plans): record the step 1 MR in the S20-A Status table comes third. The 13 commits after them correct comments in the migration and in the test suite, rename one test, and rewrite one failure message. None of them changes a statement, a key list, a predicate, or what an assertion compares.

Authorship was test-first, and the commit order is the reverse of it.

The reason is the embed. //go:embed sql/*.sql embeds the new migration from the filesystem, so HeadVersion() returns the new head while pre-commit stashes the unstaged knownHeadVersion pin. A test-only first commit therefore fails the go-test pre-commit hook. Every commit ran the whole hook chain, and no commit on this branch used --no-verify.

Merge-order notes

• The migration stamp, and the two open stacks that sort after it. This step's migration is stamped 20260817120000. On main at 7e63a7e4 the head migration is 20260814150911_add_repositories_remotes_index.sql, so this stamp is later than every migration on main. Two open stacks carry later stamps. !1621 adds 20260817144350_drop_namespaces_organization_id.sql. !1555 adds 20260817144214_add_container_images_bulk_scope_index.sql, and !1583 and !1627 stack above it and carry the same file. While none of the four has merged, this branch's knownHeadVersion is the lexically-last version and TestHeadVersion passes. When one of them merges first, the pin here is no longer the head, so TestHeadVersion fails on the next rebase. This file's stamp then also sorts before an applied migration, which goose.WithAllowOutofOrder(false) refuses. The remedy in that order is to restamp this migration above the merged head and to move the pin with it.
• Four open merge requests move the same constant. !1567, !1581, !1621 and !1555 each move knownHeadVersion in internal/datastore/migrations/migrations_checksum_test.go, and so does this branch. Whichever merges second resolves that constant by hand. After this merge request lands, the correct value is 20260817120000 until a later stamp merges.
• Two open merge requests each stale one enumeration in this migration's header. !1567 adds index_npm_packages_on_ns_id_repo_id_name_tombstoned, keyed (namespace_id, npm_repository_id, name) and partial on soft_deleted_at IS NOT NULL, stamped 20260814160000. The header enumerates the other index names on npm_packages and says both name soft_deleted_at only in an IS NULL predicate. While !1567 stays open that enumeration is complete, and once it merges the enumeration is one index short. What the enumeration supports holds in both cases, because the added index keys the name rather than the marker and cannot serve a marker-ordered read of one namespace. !1581 adds three more members of the discovery family, on maven_remote_packages, on npm_remote_packages and on container_remote_images, stamped 20260814200332. The same header enumerates the family the tree carries today, so that list is complete while !1581 stays open and three members short once it merges. Whichever migration file merges first is frozen by lint:migration-immutability, so a correction after that point lands in the file that merges second.
• No index count for either table. This merge request states no count of the indexes on repositories or on npm_packages, in the migration, in the test file, or in this description. Open merge requests move those numbers. !1567 adds a second tombstone-partial index on npm_packages.
• !1514 and the spec inventory. !1514 corrects the soft_deleted_at inventory row for maven_packages and the count sentence that reads off it. While !1514 stays open, main reads | maven_packages | yes | no | and "Six of the seventeen tables are already indexed for a tombstone scan and eleven are not", and the tree falsifies both, because it carries index_maven_packages_on_ns_id_soft_deleted_at. Once !1514 merges, the row reads yes and the sentence reads seven and ten. This merge request states no count and settles neither figure.
• No predicate is widened here. The diff adds two non-unique partial indexes on an existing column. It constrains no write and changes no query result, so no later merge request has to close a window this one opens.
• Position in the plan. Step 1 has no predecessor. The plan's Step 5 is its only in-plan dependent, and it reads these two indexes.

Divergences from the plan, disclosed

The per-partition assertion uses childIndexNamesOf

The plan's Tests: line names assertEveryPartitionInheritsIndexes as a reuse target. This step uses childIndexNamesOf with listPartitionsOf instead, for two reasons.

First, assertEveryPartitionInheritsIndexes compares a partition's whole index set against a caller-supplied list. On repositories the test then has to enumerate every index unrelated to this migration, and it fails whenever an unrelated index arrives or goes. The invariant under test is one attached child per partition, for the two indexes this migration builds. The file header of tombstone_discovery_schema_integration_test.go states this reason.

Second, that helper mishandles the expression index (namespace_id, COALESCE(last_updated_at, created_at) DESC, id DESC), because an expression key joins to no pg_attribute row.

internal/datastore/migrations/artifact_read_keyset_schema_integration_test.go is the file the plan tells the author to follow, and it uses childIndexNamesOf for this same assertion.

A modified file the plan's Files: list does not name

The plan's Files: list does not name internal/datastore/migrations/migrations_checksum_test.go. This step changes it anyway, and the change is mechanical rather than a scope widening.

TestHeadVersion fails without the change, because the knownHeadVersion constant pins the head migration's version_id. The pin moves in every migration merge request. At base 3f940e57 the last eight commits that touch the file are all migration merge requests: 174f9aba, e4fbf53e, 5c35f376, 2eaf7bc4, beaf09d1, ce4d668c, 21b054a0 and a7ec3bfa. The edit is +6/−6, which is the constant and its doc comment.

Three sibling subtests with no counterpart here, and why each absence is correct

The project rules ask an MR that mirrors a sibling suite to "diff its test suite against the mirrored suite for dropped subtests, not only for added ones". This suite mirrors internal/datastore/migrations/container_tombstone_schema_integration_test.go and internal/datastore/migrations/artifact_read_keyset_schema_integration_test.go. Three of their subtests have no counterpart here.

No runtime Down assertion. Both mirrored suites carry one: TestArtifactTombstoneSchema_ObjectsAbsentAfterDown and TestArtifactReadKeysetSchema_IndexesAbsentAfterDown. docs/dev/go-testing.md names TestMigrations_UpDownUp as this package's one sanctioned runtime reversibility walk, and it rules against per-migration down-walks. Both siblings keep their own walk under a comment that says the same thing. A scratch migration confirmed that TestMigrations_UpDownUp does exercise this file's Down. The omission is documented in the file, on TestTombstoneDiscoverySchema_TransactionDirectiveMatchesTheLockBudget.

TestArtifactTombstoneSchema_MarkerRoundTripsThroughItsPartition. That subtest exists because its migration adds a column, so it has to prove the ALTER TABLE recursed into the child a row routes to. This migration adds no column and alters none, so there is no recursion to prove.

TestArtifactTombstoneSchema_MarkerVersionMatchesItsFilename. That subtest pins the DownTo target constant its suite's runtime down-walk needs. This suite declares no such constant, because its rollback assertion is static.

The file header of tombstone_discovery_schema_integration_test.go carries the second and third reasons as well. With the three absences named, the dropped-subtest diff is complete, so a reviewer reading the diff on its own does not have to record any of them as a dropped subtest.

Index naming: one accepted deviation

Both new index names abbreviate namespace_id to ns_id. That is the dominant spelling in this schema. structure.sql carries 137 authored index names at base 3f940e57, and 96 of them abbreviate; at tip 336d1ecd it carries 139, and 98 of them abbreviate.

index_npm_packages_on_ns_id_soft_deleted_at departs from nothing. The two other authored index names on its own table abbreviate as well, index_npm_packages_on_ns_id_repo_id_last_downloaded_at and unique_npm_packages_ns_id_repo_id_name, and index_maven_packages_on_ns_id_soft_deleted_at is the same name one format over.

index_repositories_on_ns_id_soft_deleted_at does depart, and the deviation is accepted rather than an oversight. It is the first index name on repositories to abbreviate the column, against 11 other names on that table that spell namespace_id out. Length is not the reason: index_repositories_on_namespace_id_and_soft_deleted_at is 54 characters against the 63-character identifier limit, so it fits, and docs/dev/database.md allows a column name to be abbreviated to stay inside that limit, which this name does not need. The reason is one spelling across the discovery family, so a reader who follows the family from maven_versions through container_images to here meets one shape at every level. The cost is this one table's uniformity: repositories now holds index names in two schemes, which is the outcome docs/dev/database.md names as the one to avoid. The migration header carries the same trade-off, and the suite's constants comment points at it.

The two counts above come from this extraction, which separates authored names from Postgres-generated partition children:

grep -oE '^CREATE (UNIQUE )?INDEX [a-z0-9_]+ ON (ONLY )?public\.[a-z0-9_]+' \
  internal/datastore/migrations/structure.sql |
  sed -E 's/^CREATE (UNIQUE )?INDEX //; s/ ON (ONLY )?public\./ /' |
  grep -vE ' [a-z0-9_]+_p[0-9]+$' | sort -u

Text this MR does not change

The build direction: a spec sentence, and a merged migration that reads the other way

This section quotes both texts. It says of neither one that it is the correct one, and it measures no row count of its own.

At main 45b19f60, docs/specs/S20-a-lifecycle-closed-beta.md:119 licenses the blocking parent-level build:

It is unnecessary, for the reason that migration gives for its own blocking builds: the tables are empty because closed beta has not launched and there is no production deployment.

Line 121 of the same file states the condition that ends that license:

That is the same reason The FK stall gives for specifying no backfill, and it stops holding the moment any of the three tables carries production rows.

At the same SHA, internal/datastore/migrations/sql/20260814150911_add_repositories_remotes_index.sql:26-28 takes the other path:

-- This is the per-partition path docs/dev/database-migrations.md prescribes
-- for a populated partitioned table, because repositories holds production
-- data: the blocking parent-level build would recurse into all 64 partitions

Lines 11-12 of that same migration carry the measurement its choice cites:

-- parallel seq scan whose cost tracked the whole table: measured at 306,000
-- repositories of which 6,000 are container remotes, 4,178 shared buffers

repositories is one of the two tables this step indexes. The two readings cannot both hold, and this MR takes no position on which one does.

What this step's migration does with the disagreement. The migration does not claim that either table is empty. Its header states both readings at sql/20260817120000_add_repositories_and_npm_packages_tombstone_indexes.sql:93-105, and it substitutes a measurement for the argument at :107-114. That measurement is a row count in repositories and in npm_packages in the target database, taken before the file is applied anywhere. Lines :116-123 name the window for the remedy: while this MR is open the file can be rewritten in place, and after it merges the rewrite is a separate forward migration, because lint:migration-immutability fails a merge request that modifies a migration file that already exists on the target branch.

What the migration reports about its siblings. Three blocking parent-level builds already in this directory justify themselves on their own tables being empty before closed beta launches, and the migration reports exactly that claim. It does not repeat the second half of that premise, which is that no production deployment exists. That half is false: .runway/deployment.yaml declares a production CloudSQL instance, and docs/dev/gitlab-com-infrastructure.md:26 documents the production Runway environment. "Closed beta has not launched" is a claim about product state rather than a row count, and this MR states no row count for either table.

The two texts have different ages. The spec last changed in commit ee2a2b82, at 20:03 UTC on 2026-08-14. The remotes-index migration merged in commit 174f9aba, at 21:49 UTC on the same day. The spec text is therefore older than the migration it disagrees with.

Nothing open reconciles the two. Four open merge requests touch the S20-A spec: !1600, !1518, !1514 and !1513. Zero of the four has a hunk in spec lines 106-146. Issue #587 is open, and it audits this class of claim across 18 merged migrations. It names the S20-A spec directly, and it points its fix at #585 (closed). Issue #585 (closed) closed on 2026-08-13 without that fix landing.

The spec is outside this step's Files: list, so this MR does not change it.

Stale spec text on the ADR-007 coverage of these indexes

docs/specs/S20-a-lifecycle-closed-beta.md says the npm_packages and container_images discovery indexes are additions to ADR-007. That research is stale in this step's favor, because ADR-007 specifies both indexes this step builds. For repositories it gives a reap's own reason, "every other partial on this table excludes the trash, and the full (namespace_id, name) unique index does not key soft_deleted_at". For npm_packages it gives the index it calls "not primarily a trash-listing index". Both clauses were read in the upstream handbook copies as well as in the in-tree mirror, because the mirror is stale at this tip. The ./scripts/adr-freshness.sh row of the Verification table carries that result.

The spec carries five sentences of this class, found by grepping it for ADR-007. They are quoted here, because the line numbers move whenever the spec gains a line. Line numbers are as of base 3f940e57.

• Line 95: "On npm_packages it is this spec's own decision. ADR-007 places the trash index at version level for every format and gives npm_packages none, so this one is an addition to the ADR rather than an implementation of it".
• Line 102: "ADR-007 specifies container_images.soft_deleted_at but places the container trash index one level down, on container_manifests, so there is no image-level index to implement and this is an addition to the ADR on the npm_packages pattern."
• Line 950: "ADR-007's amendment for this spec's container_images discovery index is handbook!20708", which merged on 2026-08-10 and reached the mirror.
• Line 975: "The purger's predicate and its repositories index come from ADR-007; its npm_packages and container_images indexes do not."
• Line 977: "Both are additions to ADR-007 and each has its own handbook amendment".

The same text counts the partial indexes on repositories in two places, and the count is short by one against the tree at base 3f940e57: line 86, "repositories carries nine partial indexes and every one of them is WHERE soft_deleted_at IS NULL", and line 975, "repositories carries nine partial indexes and all nine are WHERE soft_deleted_at IS NULL". A correction guided by one site alone leaves the other. This merge request states no count of either table's indexes.

Open !1514 corrects every sentence above, and it puts the count at ten. While !1514 stays open, main keeps the stale text. Once !1514 merges, the spec reads the corrected way and this disclosure is spent. The spec is outside this step's Files: list, so this MR changes no spec text.

The maven_packages sentences the tree already falsifies

At base 3f940e57, and unchanged on main at 7e63a7e4, three lines of the spec read:

:59 — | maven_packages | yes | no |

:98 — npm_packages is the only table that needs it today, because it is the only *_packages table any writer tombstones: Maven has no delete path at any layer, and the remote caches are read-only.

:99 — The same hole opens for maven_packages when #313 (closed) gives Maven a delete verb, and the fix is the same one index.

internal/datastore/migrations/structure.sql carries index_maven_packages_on_ns_id_soft_deleted_at, on main and on this branch. Merged commit ce4d668c added it, through sql/20260812150200_add_artifact_tombstone_discovery_indexes.sql. The three sentences above therefore read as future work for a table the schema already indexes.

Open !1514 corrects the inventory row. Neither !1514 nor !1513 has a hunk on the two sentences at :98 and :99, so those two travel with this disclosure until some later merge request takes them.

An ADR-007 divergence this MR neither creates nor changes

The migration cites index_maven_packages_on_ns_id_soft_deleted_at as family precedent. A reviewer who follows that citation into ADR-007 meets two statements the tree contradicts. Both are quoted rather than cited by line, because the mirror re-numbers on every sync.

• The npm_packages table entry in docs/adr/007_database_schema.md says that table carries "a tombstone index no other *_packages table needs".
• The maven_packages index list in the same file specifies no tombstone index among the indexes it names.
• internal/datastore/migrations/structure.sql carries index_maven_packages_on_ns_id_soft_deleted_at with the same key tuple and the same predicate as the family.

Merged commit ce4d668c created the divergence, and it exists on main with or without this step. This branch neither creates nor changes that index. Issue #649 is open, and it tracks the correction.

Two handbook merge requests touched ADR-007 on 2026-08-17, and both merged: handbook!20788 at 08:59 UTC and handbook!20767 at 10:13 UTC. Neither reaches the two statements above. handbook!20788 adds a second, name-keyed npm_packages index clause for the version-cap look-through, which open !1567 builds and this step does not. Neither merge request has reached the in-tree mirror yet, and the ./scripts/adr-freshness.sh row of the Verification table reports both as unsynced.

This MR opens no handbook merge request, because the change deviates from no ADR clause. ADR-007 specifies both indexes this step builds.

A stale helper doc this MR does not edit

internal/datastore/migrations/schema_helpers_test.go:702-705 documents assertNoTransactionDirectivePresent this way:

// assertNoTransactionDirectivePresent asserts the Down section carries
// `-- +goose NO TRANSACTION`. Without that directive, goose wraps every
// statement in a single transaction and the per-statement implicit-
// transaction model the lock budget relies on is broken.

The pinned parser falsifies that reading. In github.com/pressly/goose/v3@v3.27.3/internal/sqlparser/parser.go, ParseSQLMigration declares one useTx variable at line 103 and sets it to true at line 112. Line 182 sets it to false on the NO TRANSACTION annotation, and line 286 returns it. One variable covers the whole file, so the directive is file-level rather than per-section.

This diff carries the correct claim, in the migration's transaction-directive paragraph: "goose reads the directive as file-level, so the single line below the Up marker disables the transaction for the Down statements too, and the copy under the Down marker is this package's convention rather than the mechanism."

schema_helpers_test.go is outside this step's Files: list, and this MR does not edit it. A sweep of every open merge request's branch on 2026-08-17 found no change to the file outside main.

Verification

Check	Result
go test ./...	Pass, exit 0. GOFLAGS carries -tags=integration,development_stubs, so the integration suite runs
The seven TestTombstoneDiscoverySchema_* tests plus TestHeadVersion	Pass. The five falsifiable ones failed before the migration landed, which is the test-first signal
internal/datastore/migrations, whole package	Pass, 261s, TestMigrations_UpDownUp included
mise run db:format-migrations (pg_format)	Clean. It rewrote nothing, and the pre-commit hook ran the same formatter on every commit that touches the migration
mise run db:lint (squawk 2.61.0)	Found 0 issues in 76 files, re-run at 336d1ecd
Squawk pragmas re-measured	With the three squawk-ignore-file pragmas stripped from a copy, squawk reports 6 findings: require-lock-timeout 1, require-statement-timeout 1, require-concurrent-index-creation 2, require-concurrent-index-deletion 2. Each pragma suppresses at least one finding, so none is decorative
mise run db:dump-structure	The regenerated structure.sql is byte-identical to the committed file, sha256 27ad7dec…. This is what db:structure-check asserts
goose up on a clean database	Head reaches 20260817120000. Both parent indexes exist on the partitioned parents with the exact key list and predicate
Attach fan-out, through pg_inherits	64 valid children per parent index and 64 distinct partitions per table, which is 128 children across the two tables
Rollback and re-apply	goose down leaves 0 parents and 0 children. A re-apply returns 2 parents and 128 children. A repeat apply and a repeat rollback are no-ops and exit 0
EXPLAIN, both tables and both readers	Four plans, carried in The four EXPLAIN plans below with one plan text in full. Each DESC reader gives Index Scan using and each ASC reader gives Index Scan Backward using, on one child index of 64, with no Sort node
driver.sh smoke	22 passed, 0 failed. The driver's database came out at head 20260817120000 with 64 children per parent index, so the migration also applies through the service's own boot path
golangci-lint 2.12.2 --build-tags=integration --max-same-issues=0 --max-issues-per-linter=0 ./internal/datastore/migrations/	Zero findings in the new and edited files, re-run at 5d56513f, the last commit on this branch that changes Go. The package's 128 findings are a pre-existing backlog in other files, which CI lint never compiles
gofmt -l and goimports -l	No output
./scripts/adr-freshness.sh	Exit 1, run at 336d1ecd on 2026-08-17. It reported mirror STALE: 4 upstream commit(s) not yet synced, with the local docs/adr/ last changed 2026-08-13. Two of the four commits touch the database-schema record this step conforms to, and the other two touch two more records. ADR conformance was therefore checked against the upstream handbook copies of those files, which is what the script's closing line asks for, and the conformance conclusion is unchanged. The script also warns that internal decision records exist and cannot be counted with the available token, so this change is unverified against that set

Every database above was empty or freshly seeded. No timing above says anything about a production apply, and no production row count was taken.

Except where a row names its own commit, the runs above were taken on this branch at ec25adf7, the pre-rebase tip. Since ec25adf7 the migration and its suite changed only in comments, one test name, and one failure message, so nothing the rows above measure differs at 336d1ecd.

The four EXPLAIN plans

Method: EXPLAIN (ANALYZE, BUFFERS) with enable_seqscan and enable_bitmapscan off, which is what the suite's explainKeysetPlan helper does. Fixture: one namespace per table, 5000 rows, every tenth row marked, then ANALYZE.

Table	Reader	ORDER BY	Node	Child index scanned	Children touched	Sort node
repositories	trash listing	DESC	Index Scan using	repositories_p16_namespace_id_soft_deleted_at_idx	1 of 64	none
repositories	reap	ASC	Index Scan Backward using	repositories_p16_namespace_id_soft_deleted_at_idx	1 of 64	none
npm_packages	trash listing	DESC	Index Scan using	npm_packages_p09_namespace_id_soft_deleted_at_idx	1 of 64	none
npm_packages	reap	ASC	Index Scan Backward using	npm_packages_p09_namespace_id_soft_deleted_at_idx	1 of 64	none

The repositories reap plan in full, as the representative one:

Limit  (cost=0.27..34.21 rows=100 width=24) (actual time=0.021..0.117 rows=100 loops=1)
  Buffers: shared hit=20
  ->  Index Scan Backward using repositories_p16_namespace_id_soft_deleted_at_idx
        on repositories_p16 repositories  (cost=0.27..169.96 rows=500 width=24)
        (actual time=0.021..0.113 rows=100 loops=1)
        Index Cond: (namespace_id = '11111111-1111-7111-8111-111111111111'::uuid)

No plan carries a Sort Key: line or a Sort Method: line, and no plan carries a Filter: (soft_deleted_at IS NOT NULL) line. The order and the predicate both come out of the index. Both scanned children resolve through pg_inherits to this migration's parent indexes rather than to another index with a colliding generated name.

With the two planner settings back on, all four queries chose the same index in the same direction, again with no Sort. That run was made by hand rather than in CI, and it is what closes the gap the forced settings leave: with the settings off, the suite proves that this query shape can use the index in this direction, not that the planner picks it. Both mirrored sibling suites call the same explainKeysetPlan helper, which is where the two settings are forced, so this is the package's convention rather than something this branch introduces.

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
20260817120000_add_repositories_and_npm_packages_tombstone_indexes.sql	OK (234.91ms / 21.15ms)	OK (63.9ms / 49.3ms)	OK (127.93ms / 47.93ms)

These timings come from the db:migrate matrix on this branch's pipeline at commit 55a38a2e, before the rebase. They still describe the file that ships: from 55a38a2e to 336d1ecd every changed line in the migration is a comment line, and no CREATE INDEX, DROP INDEX, or goose directive differs. The table names the commit rather than the three job ids, because a job id moves with every push and the byte comparison does not. All three matrix jobs passed, and no log carries a FAILED line. The re-apply phase is for idempotency only, so the table excludes it. Its timings are 315.79ms on PG 16, 64.74ms on PG 17, and 129.08ms on PG 18.

Migration notes:

• The version spread is under the flag threshold. PG 16 apply (234.91ms) is 1.8x the second-slowest version, PG 18 (127.93ms). The flag for a version-specific regression starts at 2x. No version exceeds 1s, so the 5-minute boot budget is not at risk on an empty database.
• Apply exceeds rollback on every version, and that is the normal shape here. The largest ratio is 11x, on PG 16. Two sibling parent-level index migrations in the same PG 16 log show the same shape or a larger one: 20260812150200_add_artifact_tombstone_discovery_indexes.sql at 270.3ms / 21.28ms (12.7x), and 20260814150911_add_repositories_remotes_index.sql at 301.33ms / 10.71ms (28.1x). A build reads rows, and a parent-level DROP INDEX is metadata work. The asymmetry follows from that difference, so it is not an anomaly.
• The pre-apply check the file asks for is still open. The file header asks for a row count on repositories and on npm_packages in the target database, before the file is applied there. The counts below come from a scratch database that this run created and then removed, so they carry no information about any other database. Nothing in this evidence discharges that check.

Local scratch-database run

This is an independent apply and rollback outside CI, on the same PostgreSQL major version as GL_PG_CURR_VERSION. It answers the questions the CI matrix does not: what each build locks, what it scans, and what the two statements leave behind.

Caution

These numbers come from empty tables in a throwaway database. They are not a production estimate, and the row counts below describe that scratch database alone.

Database: ar_s20a_dbreview, on postgres:17-alpine, PostgreSQL 17.10. The run created this database, applied every migration to head, took the measurements, and then removed the database.

Measurement	Result
Pre-apply row count, repositories (scratch database)	0
Pre-apply row count, npm_packages (scratch database)	0
Partitions per table	64
goose apply	124.27ms
goose rollback	34.20ms
goose second apply	130.14ms

What the resulting state confirms:

• Both indexes land in the shape the file writes. After the apply, each parent index exists in the ON ONLY form, keyed (namespace_id, soft_deleted_at DESC) with the predicate soft_deleted_at IS NOT NULL, and each has 64 attached child indexes.
• No generated child name reaches the identifier limit. The longest one is 49 characters, against the 63-character limit the header warns about.
• The rollback removes both families whole. No parent index and no child index of either family remains. The six sibling (namespace_id, soft_deleted_at DESC) families keep all 384 of their children, so neither DROP statement reaches anything else.
• IF NOT EXISTS is a no-op on an index that already exists. A repeat of each CREATE INDEX returns NOTICE: relation "..." already exists, skipping and no error.
• The lock footprint matches the header. Read from pg_locks inside an open transaction, each build holds ShareLock on 65 relations (the parent and its 64 partitions) and AccessExclusiveLock on 65 relations (the new parent index and its 64 children). The result is the same for both tables.
• The build scans the table, not the marked rows. Each build does one sequential scan of every partition: 128 scans across the 128 partitions of the two tables. Total row count governs the stall, as the header states.

Not measured: the ShareLock conflict itself. A second session is needed to show that writes wait while reads continue. The PostgreSQL lock-conflict table gives that result, and the header states it correctly.

The pipeline at 55a38a2e also passed db:structure-check, lint:migrations (squawk), lint:sql-format, lint:migration-immutability, and lint:migration-ordering.

Query mode did not run. The MR changes two Go files, and both are test files, so no query-producing method changed.

This is a bot message 🤖 — /smurfit