feat(datastore): repository List and FindByName queries (S17 Phase 1 Step 5)

What

S17 Phase 1 Step 5: the datastore read layer for the repository management API. Adds RepositoryStore.List (keyset pagination) and FindByName, drops the four superseded 2-column sort indexes, and ships integration tests.

Plan: docs/plans/2026-06-22-s17-phase1-repository-crud.md (Step 5). Depends on Step 4 (!668 (merged), merged).

Note

Stacked on !702 (merged). This MR targets the Step 7 branch (jdrpereira/s17-phase1-repository-crud-step-7), not main, so the diff shows only the Step 5 read layer on top of the shared repositories.go that Steps 6/7 introduce (merge order 6 → 7 → 5). When !702 (merged) merges, GitLab auto-retargets this MR to main. Review the diff as-is.

Changes

• List: keyset pagination over name / last_updated_at / artifacts_count / downloads_count / size_bytes (both directions), optional format/kind filters, soft_deleted_at IS NULL, scoped by namespace_id; hasMore via a limit+1 probe with a non-positive-limit floor guard.
• FindByName: active row by (namespace_id, name), else ErrNotFound.
• Drop migration for the four 2-column sort indexes the Step 4 (namespace_id, <col>, id) keyset indexes supersede; the deep-page EXPLAIN test gates that every sort stays index-backed after the drop.
• Adds a (namespace_id, format, name) index so the common format-filtered name listing stays index-only instead of filtering wrong-format rows.

last_updated_at sorts by COALESCE(last_updated_at, created_at)

A repository reads null last_updated_at until its first artifact update, so "never updated" is the common case, not an edge. Two ways to rank those rows when sorting by recency:

• NULLS LAST (the obvious one): a just-created repository sinks to the bottom of "recently updated". That is the surprising UX, and it forces the keyset bound to page across a NULLS region a row-value comparison cannot express without a NULL-region special case (an OR ... IS NULL the planner can't fold into one ordered index range).
• COALESCE(last_updated_at, created_at) (chosen): a never-updated repository ranks by its creation time, so a fresh repo appears at the top of "recently updated" (the conventional repository-list behavior). The sort key is then never null, so its keyset bound is the same plain row-value form as the counter columns, index-ordered in one scan.

This was settled with the spec author. It is both the better UX and the simpler implementation: it removes the entire NULL-region keyset machinery (a dedicated bound function plus its tests) and the *time.Time cursor nil-overload. The serialized last_updated_at field stays nullable; only the sort key coalesces. Step 5 swaps the raw last_updated_at keyset index for an expression index (namespace_id, COALESCE(last_updated_at, created_at) DESC, id DESC). The spec (Resolution 7, List) and the plan are updated to record this.

Spec coverage

#	Criterion	Tests
AC-7	Detail returns row / not-found	FindByName hit/miss (404 mapping = Step 8)
AC-8	List filters format/kind, sorts each col both ways	List_Filter, List_SortColumnsBothDirections
AC-9	Keyset: next-signal, no dups/gaps, final page omits	List_KeysetBoundaryNoGapsOrDuplicates, List_HasMoreSignal
AC-12	Cross-slug isolation	List_NamespaceIsolation, FindByName isolation
Res.7	Every sort index-backed (no post-scan Sort)	List_DeepPageIsIndexBacked (all five columns incl. COALESCE)

Keyset correctness over the trickier paths is pinned by List_KeysetTiedCounters (row-value tiebreaker over tied counter values) and List_KeysetLastUpdatedAtCoalesce (mixed updated/never-updated walk == single page). Handler-owned items (param 400, opaque cursor, Link header, status codes, limit cap) are Step 8.

Review notes

AI pre-review (/review-branch + pr-review-toolkit) caught, and fixed test-first, a keyset data-loss bug: List sorted by last_updated_at silently dropped the NULL-timestamp region once a page ended inside it. The spec author then chose the COALESCE semantics above, which removes that region (and its bug class) entirely. Also addressed: a counter-column tie-pagination coverage gap and a non-positive-Limit panic guard.

Follow-ups (not in this MR)

• ADR-007's repository index list is amended to record this MR's index changes (the COALESCE(last_updated_at, created_at) keyset index replacing the last_updated_at DESC NULLS LAST keyset, and the new (namespace_id, format, name) filter index) via handbook MR gitlab-com/content-sites/handbook!20220 (merged). This MR is marked blocked by it (ADRs are synced from the handbook, not edited in this repo).
• Promote RepositoryFormat/RepositoryKind to named int16 types for compile-time mix-safety (cross-cutting; coordinate with the Step 6/7 write path).

Testing

Integration tests (testcontainers PostgreSQL) per the table above, plus the counter-tie and COALESCE keyset walks, deep-page EXPLAIN (index-backed, no Sort, all five columns), soft-delete exclusion, non-positive-limit, and the updated Step 4 migration schema test (COALESCE expression index). golangci-lint clean; migrations up/down round-trip and structure.sql regenerated.

Related to #171 (closed)

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
20260622130000_drop_redundant_repository_sort_indexes.sql	OK (73.95ms / 777.00ms)	OK (62.96ms / 183.16ms)	OK (92.36ms / 249.98ms)
20260625120000_replace_last_updated_at_keyset_index_with_coalesce.sql	OK (219.04ms / 208.06ms)	OK (63.63ms / 235.46ms)	OK (88.41ms / 104.75ms)
20260625130000_add_repository_format_name_index.sql	OK (198.07ms / 20.14ms)	OK (46.68ms / 20.85ms)	OK (64.69ms / 26.39ms)

Migration notes:

• All three migrations are index-only and sub-second on an empty database; none approaches the 5-minute boot budget. They recurse to the 64 hash partitions of repositories (blocking parent-level DDL, safe only because the tables are empty in dev with no production deployment, per the migration headers).
• PG 16 is consistently ~3-4x slower than PG 17/18 on the index-build paths (coalesce up 219ms vs 64/88ms; format up 198ms vs 47/65ms; drop down 777ms vs 183/250ms) — PG 16 partition index-creation overhead across the 64 partitions, not a forward-path concern; the largest number is a 777ms rollback on an empty DB. No action.
• drop_redundant's down (777/183/250ms) is slower than its up (~63-92ms) because the rollback recreates four partial indexes while the forward path only drops them. Expected asymmetry.

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 container (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), with 5000 repositories rows seeded in one namespace (one hash partition) and the container torn down at the end of the run. Numbers reflect moderate cardinality and do not capture production-scale effects. See Database review evidence for seed sizing and methodology. Expand each row for the rendered SQL, bound args, and raw plan.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
datastore.FindByName	Index Scan	repositories_pNN_namespace_id_name_idx1	1 / 1	8.30	0.045ms	3 / 0	1
datastore.List (sort last_updated_at DESC, cursor)	Index Scan	repositories_pNN_namespace_id_coalesce_id_idx	21 / 21	3.54	0.334ms	3 / 0	1
datastore.List (sort name ASC, cursor)	Index Scan	repositories_pNN_namespace_id_name_idx1	21 / 21	2.62	0.021ms	4 / 0	1
datastore.List (sort artifacts_count DESC, cursor)	Index Only Scan	repositories_pNN_namespace_id_artifacts_count_id_idx	21 / 21	2.65	0.054ms	23 / 0	1
datastore.List (sort name ASC, format filter, cursor)	Index Scan	repositories_pNN_namespace_id_format_name_idx	21 / 21	5.03	0.043ms	4 / 0	1

Query notes:

• Every query prunes to a single partition via the namespace_id literal and is satisfied by an index with no post-scan Sort. The last_updated_at sort uses the new COALESCE(last_updated_at, created_at) expression index (..._coalesce_id_idx), confirming the keyset sort stays index-ordered after the redesign.
• List with a format filter and the default name sort uses the (namespace_id, format, name) index (added in this MR), so format is an index key and the scan seeks the (format, name) range index-only — no post-index Filter, no rows removed. The rarer counter/timestamp sorts keep format/kind as a post-index Filter (no dedicated composite index).

datastore.List (sort last_updated_at DESC, cursor)

Summary: Plan matches intent. Index Scan over the COALESCE expression index with the namespace_id literal pruning to one of 64 partitions; the row-value keyset bound (COALESCE(last_updated_at, created_at), id) < ($2, $3) folds into the index range, so no post-scan Sort. Actual rows match the LIMIT (21 / 21), execution under 0.4ms at 5000 seeded rows. No anomalies.

Seed shape: namespaces=1, repositories=5000 (one partition; last_updated_at NULL for every 5th row so COALESCE exercises both branches)

Rendered SQL:

SELECT ... FROM public.repositories
WHERE ((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL))
  AND ((COALESCE(repositories.last_updated_at, repositories.created_at), repositories.id) < ($2::timestamp with time zone, $3))
ORDER BY COALESCE(repositories.last_updated_at, repositories.created_at) DESC, repositories.id DESC
LIMIT $4;

Bound args: [<namespace_id>, 2026-01-02 17:40:00+00, 2500, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Limit  (cost=0.28..3.54 rows=21 width=16) (actual time=0.299..0.303 rows=21 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using repositories_p16_namespace_id_coalesce_id_idx on repositories_p16 repositories  (cost=0.28..258.94 rows=1667 width=16) (actual time=0.299..0.301 rows=21 loops=1)
         Index Cond: ((namespace_id = '<ns>'::uuid) AND (ROW(COALESCE(last_updated_at, created_at), id) < ROW('2026-01-02 17:40:00+00'::timestamp with time zone, '2500'::bigint)))
         Buffers: shared hit=3
 Planning Time: 0.211 ms
 Execution Time: 0.334 ms

datastore.List (sort artifacts_count DESC, cursor)

Summary: Index Only Scan over the (namespace_id, artifacts_count, id) keyset index, single partition, no post-scan Sort; the row-value bound folds into the range. Heap Fetches: 21 (visibility checks) keeps buffers low. 21 / 21 rows, under 0.06ms. No anomalies. The downloads_count and size_bytes sorts share this shape on their respective keyset indexes.

Seed shape: namespaces=1, repositories=5000 (one partition)

Rendered SQL:

SELECT ... FROM public.repositories
WHERE ((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL))
  AND ((repositories.artifacts_count, repositories.id) < ($2, $3))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $4;

Bound args: [<namespace_id>, 50, 2500, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Limit  (cost=0.28..2.65 rows=21 width=16) (actual time=0.021..0.045 rows=21 loops=1)
   ->  Index Only Scan using repositories_p16_namespace_id_artifacts_count_id_idx on repositories_p16 repositories  (cost=0.28..293.47 rows=2600 width=16) (actual time=0.021..0.043 rows=21 loops=1)
         Index Cond: ((namespace_id = '<ns>'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '2500'::bigint)))
         Heap Fetches: 21
         Buffers: shared hit=23
 Planning Time: 0.152 ms
 Execution Time: 0.054 ms

datastore.List (sort name ASC, cursor) / FindByName / format filter

name sort uses the unique (namespace_id, name) index with a single-column bound (name > $2), no id tiebreaker needed; Index Scan, 21 / 21, 0.021ms.

FindByName is a point lookup on the same unique index (namespace_id = $1 AND name = $2), 1 / 1, 0.045ms.

format filter seeks the (namespace_id, format, name) index range, so format is an index key, not a post-index Filter; Index Scan, 21 / 21, 0.043ms, no rows removed.

Rendered SQL (name + format filter):

SELECT ... FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL))
  AND (repositories.format = $2::smallint)) AND (repositories.name > $3::text)
ORDER BY repositories.name ASC
LIMIT $4;

Plan (format filter):

 Limit  (cost=0.28..5.03 rows=21 width=187) (actual time=0.022..0.027 rows=21 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using repositories_p16_namespace_id_format_name_idx on repositories_p16 repositories  (cost=0.28..185.10 rows=817 width=187) (actual time=0.021..0.025 rows=21 loops=1)
         Index Cond: ((namespace_id = '<ns>'::uuid) AND (format = '2'::smallint) AND (name > 'explain-2500'::text))
         Buffers: shared hit=4
 Planning Time: 1.876 ms
 Execution Time: 0.043 ms