feat(datastore): add the maven virtual upstream list and rules reads (S30 plan: 4/25)

Adds the two datastore reads a Maven virtual resolution starts from. UpstreamsByVirtualRepository returns a virtual repository's upstream list in position order, joining repositories for kind, format, and name, maven_remote_repositories for the validity hours, the revalidate flag, health status, and URL, and maven_repositories for the hosted binding. It selects no credential column. RulesByUpstreams returns the rules for a set of associations in one statement, grouped by association. Both reads fail closed past their bound rather than serving a shortened result: the list read with ErrMavenVirtualUpstreamListTooMany above the 20-position cap the schema already enforces, the rules read with ErrMavenVirtualUpstreamRulesTooMany past its ceiling, because a dropped rule may be a deny. The plan books a clip-and-warn on the list read; it is dropped here because the position CHECK makes it unreachable. A soft-deleted upstream is returned flagged, not filtered; the caller drops it. The contract sidecar maven_virtual_resolution_contract.md states the projection and the two absence shapes the left joins report.

One defect found while writing it: the go-jet default alias for the rules read's grouping column is 65 bytes, past PostgreSQL's 63-byte identifier cap, so the column came back truncated, the row mapper matched no field, and every rule grouped under the zero UUID with no error. That is "no rules", so deny rules would have been ignored. The alias is now explicit and a test walks both row structs to keep every alias under the cap.

Tests cover the plan's acceptance criteria plus guards for defects this diff hit: a 63-byte cap on every alias tag in the package and an explicit alias tag on both resolution row structs, a transient failure surfacing as an error rather than an empty result, and the remote-position shape (every Maven repository carries a maven_repositories row whatever its kind). Review added the list read at ADR-004's cap and its indifference to a soft-deleted virtual parent, the constructor's nil-client panic and the client it keeps, the keys, joins, projection and discriminator guards on both rendered statements, and the statement count an empty rules batch issues.

No e2e catalog change and no docs/user change: nothing wires these reads yet, so no client-visible behavior moves.

Hand-written diff is about 1,570 lines and stays in one MR: the two reads are 251 lines of Go plus a 111-line contract, the rest is tests, and the list read and rules read are separate commits if a split is wanted.

Database Review Evidence

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 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
datastore.UpstreamsByVirtualRepository	Limit	unique_mvru_ns_id_repository_id_position	1 / 6	33.24	1.654ms	40 / 11	1/64, 1/64, 1/64, 1/64
datastore.RulesByUpstreams	Limit	index_mvur_on_ns_id_repository_upstream_id	1 / 60	16.88	0.026ms	3 / 0	1/64

datastore.UpstreamsByVirtualRepository

Summary: Plan matches the method's intent. The driving index scan is the positional uniqueness index, so it supplies the ORDER BY position ordering with no Sort node above it, and each of the three joins is an index probe on its own (namespace_id, ...) index, pruning all four tables to one of 64 partitions. Both child joins reach their (namespace_id, repository_id) unique index. No anomalies.

A note for reviewers, because it is easy to reproduce by accident: seeding this read with a single namespace that owns every row of its partition makes the planner pick a Hash Right Join over a Seq Scan on the maven_repositories partition instead. That is a cost tie-break, not a missing index. At one namespace per partition the namespace_id filter removes nothing, so the scan is priced at 109.54 for 5,003 rows and the whole plan at 316.65, against 342.38 for the index-probe plan — 7.7% apart. Forcing the index path there (enable_seqscan = off) executes faster anyway (0.166ms against 0.896ms). The seed below is the representative one: the target's maven_repositories partition holds 8,003 rows from 6 namespaces, of which 3 are the target's, and the planner picks the index unprompted.

Seed shape: namespaces=201, repositories=650007, maven_repositories=320003, maven_remote_repositories=320003, maven_virtual_repositories=10001, maven_virtual_repository_upstreams=200006

Target namespace: one virtual repository with six upstream positions (3 hosted, 3 remote). 200 filler namespaces with random ids carry the bulk (3,200 upstream repositories and 50 virtual repositories x 20 positions each), so every hash partition holds rows from several namespaces. Target partitions: maven_virtual_repository_upstreams_p63 5,006 rows / 6 namespaces / 6 the target's; maven_repositories_p63 8,003 / 6 / 3; maven_remote_repositories_p63 8,003 / 6 / 3; repositories_p63 16,257 / 6 / 7.

Rendered SQL:

SELECT maven_virtual_repository_upstreams.id AS "maven_virtual_repository_upstreams.id",
     maven_virtual_repository_upstreams.upstream_repository_id AS "maven_virtual_repository_upstreams.upstream_repository_id",
     maven_virtual_repository_upstreams.position AS "maven_virtual_repository_upstreams.position",
     repositories.name AS "repositories.name",
     repositories.kind AS "repositories.kind",
     repositories.format AS "repositories.format",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     maven_repositories.id AS "maven_repositories.id",
     maven_remote_repositories.id AS "maven_remote_repositories.id",
     maven_remote_repositories.repository_id AS "maven_remote_repositories.repository_id",
     maven_remote_repositories.url AS "maven_remote_repositories.url",
     maven_remote_repositories.cache_validity_hours AS "maven_remote_repositories.cache_validity_hours",
     maven_remote_repositories.metadata_cache_validity_hours AS "maven_remote_repositories.metadata_cache_validity_hours",
     maven_remote_repositories.snapshot_metadata_always_revalidate AS "maven_remote_repositories.snapshot_metadata_always_revalidate",
     maven_remote_repositories.last_health_status AS "maven_remote_repositories.last_health_status"
FROM public.maven_virtual_repository_upstreams
     INNER JOIN public.repositories ON ((repositories.namespace_id = maven_virtual_repository_upstreams.namespace_id) AND (repositories.id = maven_virtual_repository_upstreams.upstream_repository_id))
     LEFT JOIN public.maven_remote_repositories ON ((maven_remote_repositories.namespace_id = $1::uuid) AND (maven_remote_repositories.repository_id = maven_virtual_repository_upstreams.upstream_repository_id))
     LEFT JOIN public.maven_repositories ON ((maven_repositories.namespace_id = $2::uuid) AND (maven_repositories.repository_id = maven_virtual_repository_upstreams.upstream_repository_id))
WHERE ((maven_virtual_repository_upstreams.namespace_id = $3::uuid) AND (maven_virtual_repository_upstreams.maven_virtual_repository_id = $4::uuid)) AND (repositories.namespace_id = $5::uuid)
ORDER BY maven_virtual_repository_upstreams.position ASC
LIMIT $6;

Bound args: [52ed1f92-7e53-7cf6-a79e-30747fa5bab4, 52ed1f92-7e53-7cf6-a79e-30747fa5bab4, 52ed1f92-7e53-7cf6-a79e-30747fa5bab4, a6500b9b-063e-7769-8ff9-99ae9498e100, 52ed1f92-7e53-7cf6-a79e-30747fa5bab4, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=1.13..33.24 rows=1 width=166) (actual time=1.178..1.610 rows=6 loops=1)
   Buffers: shared hit=40 read=11
   ->  Nested Loop Left Join  (cost=1.13..33.24 rows=1 width=166) (actual time=1.178..1.608 rows=6 loops=1)
         Buffers: shared hit=40 read=11
         ->  Nested Loop Left Join  (cost=0.85..24.93 rows=1 width=150) (actual time=1.169..1.593 rows=6 loops=1)
               Buffers: shared hit=25 read=11
               ->  Nested Loop  (cost=0.57..16.62 rows=1 width=69) (actual time=0.710..1.123 rows=6 loops=1)
                     Buffers: shared hit=12 read=9
                     ->  Index Scan using maven_virtual_repository_ups_namespace_id_maven_virtual_r_key63 on maven_virtual_repository_upstreams_p63 maven_virtual_repository_upstreams  (cost=0.28..8.30 rows=1 width=52) (actual time=0.384..0.385 rows=6 loops=1)
                           Index Cond: ((namespace_id = '52ed1f92-7e53-7cf6-a79e-30747fa5bab4'::uuid) AND (maven_virtual_repository_id = 'a6500b9b-063e-7769-8ff9-99ae9498e100'::uuid))
                           Buffers: shared hit=1 read=2
                     ->  Index Scan using repositories_p63_pkey on repositories_p63 repositories  (cost=0.29..8.30 rows=1 width=65) (actual time=0.122..0.122 rows=1 loops=6)
                           Index Cond: ((id = maven_virtual_repository_upstreams.upstream_repository_id) AND (namespace_id = '52ed1f92-7e53-7cf6-a79e-30747fa5bab4'::uuid))
                           Buffers: shared hit=11 read=7
               ->  Index Scan using maven_remote_repositories_p63_namespace_id_repository_id_idx on maven_remote_repositories_p63 maven_remote_repositories  (cost=0.28..8.30 rows=1 width=81) (actual time=0.078..0.078 rows=0 loops=6)
                     Index Cond: ((namespace_id = '52ed1f92-7e53-7cf6-a79e-30747fa5bab4'::uuid) AND (repository_id = maven_virtual_repository_upstreams.upstream_repository_id))
                     Buffers: shared hit=13 read=2
         ->  Index Scan using maven_repositories_p63_namespace_id_repository_id_idx on maven_repositories_p63 maven_repositories  (cost=0.28..8.30 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=6)
               Index Cond: ((namespace_id = '52ed1f92-7e53-7cf6-a79e-30747fa5bab4'::uuid) AND (repository_id = maven_virtual_repository_upstreams.upstream_repository_id))
               Buffers: shared hit=15
 Planning:
   Buffers: shared hit=68 read=12
 Planning Time: 1.294 ms
 Execution Time: 1.654 ms

Timings: planning 1.294ms, execution 1.654ms, total 2.948ms.

datastore.RulesByUpstreams

Summary: Plan matches the method's intent: a single Index Scan on index_mvur_on_ns_id_repository_upstream_id, with both the namespace equality and the association list inside the Index Cond, pruning to one of 64 partitions. The estimate is 1 row against 60 actual, a 60x divergence that comes from the per-value selectivity of the six-element IN list rather than from the plan shape; the Index Cond is fully bound either way and the LIMIT is the method's constant ceiling. No index or predicate change is indicated.

Seed shape: namespaces=201, repositories=4207, maven_virtual_repositories=201, maven_virtual_repository_upstreams=4006, maven_virtual_upstream_rules=200060

Target namespace: one virtual repository, six positions, 10 rules each. 200 filler namespaces with random ids: one virtual repository, 20 positions, 50 rules each. Target partition maven_virtual_upstream_rules_p05 holds 3,060 rows from 4 namespaces, of which 60 are the target's.

Rendered SQL (six-element association list, matching the six-position upstream list the paired read returns):

SELECT maven_virtual_upstream_rules.maven_virtual_repository_upstream_id AS "rule_upstream.id",
     maven_virtual_upstream_rules.rule_type AS "maven_virtual_upstream_rules.rule_type",
     maven_virtual_upstream_rules.target_field AS "maven_virtual_upstream_rules.target_field",
     maven_virtual_upstream_rules.pattern AS "maven_virtual_upstream_rules.pattern"
FROM public.maven_virtual_upstream_rules
WHERE (maven_virtual_upstream_rules.namespace_id = $1::uuid) AND (maven_virtual_upstream_rules.maven_virtual_repository_upstream_id IN ($2::uuid, $3::uuid, $4::uuid, $5::uuid, $6::uuid, $7::uuid))
LIMIT $8;

Bound args: [10b87903-6dbe-7f0b-81a0-aad4ce498c02, 4debcb35-60bb-7af9-aba8-776a524d48ba, ef804ac6-c2c7-742a-a953-61ca9811f343, 45cf6afb-a8d5-71ab-b8dc-cce684fa3c3c, 07d2840f-1a02-77fa-85c5-b40bd9ccd734, 73a51992-fa69-7d15-8abe-dd086bc84976, 71600a03-74c9-74f0-9e11-1e28ef2e086a, 20001]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..16.88 rows=1 width=38) (actual time=0.010..0.019 rows=60 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using maven_virtual_upstream_rules__namespace_id_maven_virtual_r_idx5 on maven_virtual_upstream_rules_p05 maven_virtual_upstream_rules  (cost=0.28..16.88 rows=1 width=38) (actual time=0.009..0.014 rows=60 loops=1)
         Index Cond: ((namespace_id = '10b87903-6dbe-7f0b-81a0-aad4ce498c02'::uuid) AND (maven_virtual_repository_upstream_id = ANY ('{4debcb35-60bb-7af9-aba8-776a524d48ba,ef804ac6-c2c7-742a-a953-61ca9811f343,45cf6afb-a8d5-71ab-b8dc-cce684fa3c3c,07d2840f-1a02-77fa-85c5-b40bd9ccd734,73a51992-fa69-7d15-8abe-dd086bc84976,71600a03-74c9-74f0-9e11-1e28ef2e086a}'::uuid[])))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=12
 Planning Time: 0.233 ms
 Execution Time: 0.026 ms

Timings: planning 0.233ms, execution 0.026ms, total 0.259ms.

Query notes:

• datastore.RulesByUpstreams: plan rows 1 against 60 actual, a 60x divergence. It comes from the per-value selectivity the planner assigns each element of the six-element IN list, not from the plan shape: the Index Cond binds namespace_id and the whole list, one partition is scanned, and the LIMIT is the method's own constant ceiling. No change indicated.

Related to #289 (closed)