feat(datastore): fetch rows by id set with a batch cap part 4/4 (S09 Enforcement plan: 18/20)

Part 4 of 4 of the S09 enforcement plan — Step 18: Candidate repository enumeration.

The step is split into 4 stacked MRs to keep each within the review size limit (≈800 ideal). Each part targets the previous one (part 1 → main); review and merge bottom-up.

Stacked MRs (review/merge bottom-up)

• feat(datastore): List guard extraction, zero cu... (!2143 - merged) • David Fernandez • 19.4
• feat(datastore): repository key enumeration par... (!2144 - merged) • David Fernandez • 19.4
• test(datastore): enumeration integration suite ... (!2145 - merged) • David Fernandez • 19.4
• feat(datastore): fetch rows by id set with a ba... (!2146 - merged) • David Fernandez • 19.4 👈

This part (~485 reviewable LoC)

RepositoryStore.FindByIDs with its unit and integration tests: a by-id-set row fetch scoped to one namespace, live rows only, deduplicated to first-seen order, order-preserving, silently omitting an id with no live row. The batch is capped at repositoriesByIDsBatchLimit (100, value-matched to the management API's maxPageSize; errRepositoryTooManyIDs past it, checked on the raw submitted length) — the managementapi cap catalog and its pinning test gain the third entry. An empty id list is a caller error (errRepositoryEmptyIDs): the permission-filtered listing resolves an all-denied page to its empty 200 before calling the store.

No symbol dependency on parts 2–3 (stacked for file continuity of the shared integration-test file only).

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17.10 container (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), with synthesized seed data rolled back per query 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, methodology, and the anomalies the skill flags. Expand each row's details for the seed shape, rendered SQL, bound args, and raw plan.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
RepositoryStore.FindByIDs	Bitmap Heap Scan	repositories_p42_pkey	19 / 12	103.78	0.037ms	23 / 0	1/64

RepositoryStore.FindByIDs

Summary: Plan matches the method's intent: a primary-key id = ANY(...) bitmap lookup, scoped by the namespace_id predicate that also prunes to the namespace's single hash partition (1/64). At this selectivity (20 requested ids against 5,200 seeded rows in the partition) the planner prefers the primary key over the namespace-scoped secondary indexes; the index scan matches the 17 ids that exist in the partition (12 live + 5 soft-deleted), and the heap filter removes the 5 soft-deleted rows, leaving the 12 live rows FindByIDs returns. The 3 absent ids and the soft-deleted ids are simply absent from the result rather than causing an error, matching the method's documented contract. No anomalies.

Seed shape: namespaces=1, repositories=5200 (5000 live, 200 soft-deleted)

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE ((repositories.namespace_id = $1::uuid) AND (repositories.id IN ($2::uuid, $3::uuid, $4::uuid, $5::uuid, $6::uuid, $7::uuid, $8::uuid, $9::uuid, $10::uuid, $11::uuid, $12::uuid, $13::uuid, $14::uuid, $15::uuid, $16::uuid, $17::uuid, $18::uuid, $19::uuid, $20::uuid, $21::uuid))) AND (repositories.soft_deleted_at IS NULL);

Bound args: [345e2c40-0281-7f35-8989-b7f38daac743 (namespace_id), 001c35dc-a1f6-70c6-a940-d0e6dec7f4d3, 0024c42d-233b-78d4-bbe1-ffaf6247a7cc, 00291940-4f81-7c0f-9d56-f86a0b533350, 002d7063-45db-7c58-a344-21491c5d38c2, 0038309d-82d3-77f8-8f3a-947344f0e5f7, 00440028-40ae-787b-babc-23b35e6a1fb3, 004f0128-b0ab-7c79-85ab-6c0a7248ae34, 00605420-7267-7406-87f0-d0b5e232d674, 0065f7db-1f53-7b83-898d-404e1dcf483a, 006717e6-6cd9-76bf-8779-f19321cc8bb6, 0069e4f0-ca1c-79fb-ad07-82d5b435e1ee, 006d1fee-08f5-79f4-a23d-a2f27c93d752 (12 live ids), 033c8171-fc9d-7a38-ae44-26a95f6117f6, 0452278c-c1d3-72b1-bc17-be7da527475d, 0512768f-6e07-7744-bbef-3ae634cb83bc, 058c2314-b4b7-770c-acdb-3b8d07de4ad3, 074fee75-6c97-75bd-a01d-8ad49e41eb76 (5 soft-deleted ids), 12f5741e-0214-4b6c-8f06-78b3351004e9, 04da02f2-a879-4cff-8daf-ae98d03a7159, 39377b1e-6ca0-404b-8e42-7a3f70ccf2f2 (3 absent ids)]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Bitmap Heap Scan on repositories_p42 repositories  (cost=52.15..103.78 rows=19 width=230) (actual time=0.027..0.037 rows=12 loops=1)
  Recheck Cond: ((id = ANY ('{001c35dc-a1f6-70c6-a940-d0e6dec7f4d3,0024c42d-233b-78d4-bbe1-ffaf6247a7cc,00291940-4f81-7c0f-9d56-f86a0b533350,002d7063-45db-7c58-a344-21491c5d38c2,0038309d-82d3-77f8-8f3a-947344f0e5f7,00440028-40ae-787b-babc-23b35e6a1fb3,004f0128-b0ab-7c79-85ab-6c0a7248ae34,00605420-7267-7406-87f0-d0b5e232d674,0065f7db-1f53-7b83-898d-404e1dcf483a,006717e6-6cd9-76bf-8779-f19321cc8bb6,0069e4f0-ca1c-79fb-ad07-82d5b435e1ee,006d1fee-08f5-79f4-a23d-a2f27c93d752,033c8171-fc9d-7a38-ae44-26a95f6117f6,0452278c-c1d3-72b1-bc17-be7da527475d,0512768f-6e07-7744-bbef-3ae634cb83bc,058c2314-b4b7-770c-acdb-3b8d07de4ad3,074fee75-6c97-75bd-a01d-8ad49e41eb76,12f5741e-0214-4b6c-8f06-78b3351004e9,04da02f2-a879-4cff-8daf-ae98d03a7159,39377b1e-6ca0-404b-8e42-7a3f70ccf2f2}'::uuid[])) AND (namespace_id = '345e2c40-0281-7f35-8989-b7f38daac743'::uuid))
  Filter: (soft_deleted_at IS NULL)
  Rows Removed by Filter: 5
  Heap Blocks: exact=15
  Buffers: shared hit=23
  ->  Bitmap Index Scan on repositories_p42_pkey  (cost=0.00..52.09 rows=20 width=0) (actual time=0.022..0.022 rows=17 loops=1)
        Index Cond: ((id = ANY ('{001c35dc-a1f6-70c6-a940-d0e6dec7f4d3,0024c42d-233b-78d4-bbe1-ffaf6247a7cc,00291940-4f81-7c0f-9d56-f86a0b533350,002d7063-45db-7c58-a344-21491c5d38c2,0038309d-82d3-77f8-8f3a-947344f0e5f7,00440028-40ae-787b-babc-23b35e6a1fb3,004f0128-b0ab-7c79-85ab-6c0a7248ae34,00605420-7267-7406-87f0-d0b5e232d674,0065f7db-1f53-7b83-898d-404e1dcf483a,006717e6-6cd9-76bf-8779-f19321cc8bb6,0069e4f0-ca1c-79fb-ad07-82d5b435e1ee,006d1fee-08f5-79f4-a23d-a2f27c93d752,033c8171-fc9d-7a38-ae44-26a95f6117f6,0452278c-c1d3-72b1-bc17-be7da527475d,0512768f-6e07-7744-bbef-3ae634cb83bc,058c2314-b4b7-770c-acdb-3b8d07de4ad3,074fee75-6c97-75bd-a01d-8ad49e41eb76,12f5741e-0214-4b6c-8f06-78b3351004e9,04da02f2-a879-4cff-8daf-ae98d03a7159,39377b1e-6ca0-404b-8e42-7a3f70ccf2f2}'::uuid[])) AND (namespace_id = '345e2c40-0281-7f35-8989-b7f38daac743'::uuid))
        Buffers: shared hit=8
Planning:
  Buffers: shared hit=195
Planning Time: 0.829 ms
Execution Time: 0.064 ms

Timings: planning 0.829ms, execution 0.064ms, total 0.893ms.

References

• Plan: docs/plans/2026-08-21-s09-authorization-enforcement.md
• Spec: S09 (authorization)

No e2e scenario is added or affected: datastore-only reads with no route change; no catalog covers the management API, and the listing filter's behavior is pinned by Step 19's handler tests (plan Testing Strategy).

Related to #860 (closed)