feat(managementapi): serve upstream visibility, size, url, and health

Why

The monolith's virtual repository detail page shows each upstream's visibility and size, and a remote upstream's URL and connection status (fmccawley's note). The upstream summary carries only id, name, format, and kind, so the page would otherwise fetch each upstream's own detail: up to 20 extra calls, ADR-004's cap on upstreams per virtual repository.

The four fields describe the upstream itself, so the summary carries them only for a caller holding read_repository on that upstream, the check its own detail GET applies.

What's worth a look

• visibility, size_bytes, and a remote row's settings (url, last_health_status) render only when the caller holds read_repository on the upstream itself, the check the upstream's own detail GET applies. A caller who reads only the virtual repository keeps each upstream's id, name, format, kind, and position, so the contract requires those four fields alone. Each field sits at the path the repository resource gives it.
• Each format's management read LEFT JOINs its remote child table with the namespace bind in the ON clause, because in the WHERE it would drop every hosted row. We chose the join over a second batched details read, whose soft-delete gate drops a tombstoned upstream's remote row while the list still names that upstream.
• The projection fails closed. A remote row with no remote child row, a value outside its enum, or a stored URL the write gate refuses answers the logged 500, whether or not the caller can read that upstream, because every field validates before the check drops any. Every refusal names the association, the upstream id, and the upstream's name. The dissociate renders no summary, so it still removes such a row.
• The summary re-runs remote.ParseUpstreamBase on the stored URL, so a stricter gate owes the stored URLs a backfill first. A new comment beside the gate says so.
• errUpstreamRemoteRowMissing joins errRemoteDetailsMissing and errRemoteDetailsNilRow as a third "remote row without its details" sentinel, because each names the read that found the gap.
• One read renders four operations, so the list, the association GET, the associate 201, and the reposition 200 all carry the fields under the same check.
• The check is one relationships read and one GLAZ BatchCheck per response through Deps.Verdicts, the machinery include_permissions already uses. Under the opt-in the same evaluation answers both, so a row's permissions.read_repository and its four fields always agree. The associate and the reposition check before they write, the reposition on the association's current upstream, which a move never changes, so a failed check answers 503 with nothing applied, as the contract's 503 promises. The reposition reads the list once more for that check.
• ADR-021's DRI directed this per-upstream check in review. ADR-021 still says a sub-resource list evaluates none of the resources its rows name unless the caller opts in, which no longer holds for this list, so S17's Follow-ups name the handbook amendment that text owes. S09's "Opt-out is inert" criterion now names the check as the one evaluation that runs without the opt-in.

Step 42 interplay

Step 42 of the S17 Phase 6 plan adds the container family's association routes in a stack of MRs. The container store already converts into the shared listing type, so this MR extends its row and statement too. A container row compiled without the new columns would render every container upstream public. The container store suite and its plan pin landed first, in test(datastore): cover the container upstream l... (!2853 - merged) • Hayley Swimelar • 19.5, so this MR gives the pin its fourth table. It also adds the npm and Maven store suites' two summary walks to that suite. One reads every stored field back across all three visibilities, non-zero sizes, and remote rows. The other checks that a tombstoned remote keeps its stored values. The container association routes landed on main in feat(managementapi): container virtual upstream... (!2856 - merged) • Hayley Swimelar • 19.5 and serve the checked summary through the shared handlers, so this MR moves that MR's opt-out arm test from evaluating nothing to the read check and adds a mixed-readability list case. Whichever side lands second carries the other's needs. If this MR lands first, the remote fixtures fill url and health through withUpstreamSummary in test(managementapi): cover the container upstre... (!2857 - merged) • Hayley Swimelar • 19.5 and test(managementapi): cover the container upstre... (!2858 - merged) • Hayley Swimelar • 19.5, and their handler rigs need a caller who can read the upstreams, as the npm and Maven rigs now default to. The container e2e rows arrive with test(managementapi): cover the container upstre... (!2859 - merged) • Hayley Swimelar • 19.5. If those parts land first, this MR takes on the same changes.

For consumers

visibility, size_bytes, and settings are optional in the contract. They are absent on a row whose upstream the caller cannot read and on any AR older than this change, so a consumer renders such a row without them.

Test plan

• go test ./internal/datastore/ ./internal/managementapi/ ./internal/remote/
• go test -tags=integration ./internal/datastore/ ./internal/managementapi/ against ARTIFACT_REGISTRY_DATABASE_TEST_DSN
• golangci-lint run --build-tags=integration ./internal/datastore/ ./internal/managementapi/ --max-same-issues=0 --max-issues-per-linter=0 --uniq-by-line=false
• redocly lint from api/openapi

Size

File group	Files	Added	Removed
Tests	17	2,027	108
Production Go	7	282	59
Contract (api/openapi/v1.yaml)	1	49	1
Spec, sidecars, e2e catalogs, and user docs	8	178	10
Total	33	2,536	178

The diff runs past the 500-line guideline. Tests are 2,135 of its 2,714 changed lines. A split does not help. The store reads alone would ship columns nothing reads, and the contract alone would promise fields no response carries. The contract, the three statements, the projection, the read check, and the tests pin each other's values, so they review as one change.

Scenarios and docs

• e2e: this MR adds e2e.npm.upstreams.summary-fields and e2e.maven.upstreams.summary-fields, each covering a reader of the upstreams and a reader of the virtual repository alone. The container rows are Step 42's, whose part 14 creates the container upstream groups.
• docs/user/repositories/_index.md now says who can see an upstream URL, to keep credentials out of it, and that the upstream list shows an upstream's URL, health, visibility, and size only to a caller with read access to that upstream.
• Bruno is unchanged, because this MR adds, renames, or removes no operation.
• The configuration reference and the usage-data catalog stay as they are, because this MR adds no configuration and no usage event.
• The monolith specs S05, S07, and S15 under docs/specs/monolith/ still describe a four-field summary. The follow-up that records those passages is Monolith S05, S07, and S15 still describe a fou... (#1461 - closed) • Fiona McCawley. The monolith's upstream-table work item carries a note on the new fields: Virtual repository detail: upstream-list read, ... (gitlab-org/gitlab#628475 - closed) • Fiona McCawley • 19.5.

Plan and spec amendments

This MR amends S17: the summary text and a second example under Virtual upstream associations, the paragraph on how the four responses evaluate the check, acceptance criterion 152, an error-table row for the check's 503, the Security Considerations bullet on what the upstream summary discloses, two Resolutions, and two Follow-ups items, one for ADR-009 and one for the ADR-021 amendment. The first Resolution records the per-upstream check ADR-021's DRI directed in review. The MR also amends S09's "Opt-out is inert" criterion to name that check. Approving this MR approves that spec text, with S17's DRI signing off in the review thread.

Spec coverage

Row 1 is S17 criterion 152, which this MR appends. Rows 2 to 17 break out what it and the new Security Considerations bullet promise. The two S17 rows at the end are existing criteria that gain assertions. The Maven handler suite asserts its list and associate bodies. Its GET and PATCH share the format-blind projection the npm suite pins per operation, so the Maven suite does not assert them again. The container arm gets statement tests, store walks, its plan pin, and the read check on its list through its arm tests.

Acceptance criteria

#	Criterion	Tests
1 (S17 152)	Every summary on the list, GET, associate 201, and reposition 200 whose upstream the caller holds read_repository on carries visibility and size_bytes equal to the upstream's detail, 0 included. Such a remote's settings carries url and last_health_status equal to its detail's, unknown before a probe. A hosted summary has no settings. A summary whose upstream the caller cannot read carries id, name, format, and kind alone, and one list mixes both. Each response checks once, opted in or not, the associate and the reposition before they write, a failed check answers 503 with nothing applied, and a tombstoned upstream carries its stored values to a caller who can read it	TestNpmUpstreams_EveryRenderingRoute_CarriesTheSummaryFields (four operations, hosted and remote), TestNpmUpstreams_EveryRenderingRoute_OmitsAnUnreadableUpstreamsFields, TestNpmUpstreams_List_MixesReadableAndUnreadableRows, TestNpmUpstreams_Get_FollowsTheAddressedUpstreamsReadVerdict, TestNpmUpstreams_EveryRenderingRoute_ChecksTheRowsItRendersOnce, TestNpmUpstreams_List_Empty_HandsTheEvaluatorOneEmptyBatch, TestNpmUpstreams_EveryRenderingRoute_CheckFailure_Returns503, TestNpmUpstreams_List_ZeroSizeIsServed, TestNpmUpstreams_List_FieldsFollowTheUpstreamsReadVerdict, Test{Npm,Maven,Container}VirtualUpstreamStore_Upstreams_KeepsATombstonedUpstream, and against the upstream's own detail TestNpmVirtualUpstreamsIntegration_AssociateReorderDissociateWalk, TestNpmVirtualUpstreamsIntegration_ListKeepsATombstonedUpstream, and TestMavenVirtualUpstreamsIntegration_AssociateListDissociateWalk
2	Each of public, private, and internal renders as its own token, on the npm, Maven, and container reads against Postgres and through the npm list	Test{Npm,Maven,Container}VirtualUpstreamStore_Upstreams_ServesEveryStoredField, TestNpmUpstreams_List_RendersEveryVisibility
3	Each of unknown, healthy, and unhealthy renders as its own token, on the same reads	Test{Npm,Maven,Container}VirtualUpstreamStore_Upstreams_ServesEveryStoredField, TestNpmUpstreams_List_RendersEveryHealthStatus
4	Each of hosted and remote renders as its own kind, on every rendering route and on the npm, Maven, and container reads against Postgres	TestNpmUpstreams_EveryRenderingRoute_CarriesTheSummaryFields, Test{Npm,Maven,Container}VirtualUpstreamStore_Upstreams_ServesEveryStoredField
5	Each store serves its own format, and each arm renders it	Test{Npm,Maven,Container}VirtualUpstreamStore_Upstreams_ServesEveryStoredField, Test{Npm,Maven}Upstreams_List_ReturnsThePositionOrderedSet through assertUpstreamResource
6	A hosted summary carries no settings key, even when its listing carries a remote child row	TestNpmUpstreams_List_HostedRowWithAStrayRemoteRow_OmitsSettings, every hosted row through assertUpstreamResource, Test{Npm,Maven,Container}VirtualUpstreamStore_Upstreams_ServesEveryStoredField (pair nil on hosted rows), the walks' hosted rows through assertSummaryMatchesDetail
7	A format, kind, or visibility outside its enum, a health value outside the taxonomy, a remote listing missing its URL or health, and a stored URL remote.ParseUpstreamBase refuses each answer the logged 500 with the internal envelope, whether or not the caller can read the upstream, and every refusal names the association, the upstream id, and the upstream's name	TestNpmUpstreams_UnrenderableSummaryField_Returns500 (status, envelope, no stored URL in the body, the association in the log, no credential in the log), TestNpmUpstreams_UnrenderableSummaryField_Returns500ToAnUnreadableCaller, TestUpstreamAssociationFromRow_FailsClosed (the sentinel per arm under both readabilities, the association, upstream id, and name in the text, no credential in it)
8	The render-time URL check is the whole write gate: an opaque base carrying a credential and a javascript: scheme fail closed, which a userinfo, query, and fragment check alone would pass	The opaque-base and non-http scheme rows of TestUpstreamAssociationFromRow_FailsClosed and TestNpmUpstreams_UnrenderableSummaryField_Returns500
9	A stored URL the gate accepts renders in its stored spelling, the value the detail serves, never as its parse	TestUpstreamAssociationFromRow_RendersTheStoredURLSpelling
10	The dissociate removes an association whose summary cannot render: the list answers 500, the DELETE answers 204 and emits one audit event	TestNpmUpstreams_Dissociate_RemovesAnUnrenderableRow
11	An all-deny read_repository verdict on the upstream drops visibility, size_bytes, and settings, and an allow keeps them, opted in or not, and under the opt-in the fields follow the set's read_repository alone	TestNpmUpstreams_List_FieldsFollowTheUpstreamsReadVerdict, TestNpmUpstreams_List_OptIn_ReadsTheCheckFromReadRepository, TestUpstreamAssociationFromRow_UnreadableOmitsTheStoredFields, TestMavenUpstreams_ListAndGet_OmitAnUnreadableUpstreamsFields, TestContainerUpstreamArms_List_MixesReadableAndUnreadableRows
12	Each management statement LEFT JOINs its remote child table with the namespace bind in the ON clause, projects the four columns, binds the namespace four times and the repository once, names no remote table after its last WHERE, has no soft_deleted_at filter and no LIMIT, and projects no credential column	Test{Npm,Maven,Container}VirtualUpstreamManagementListStmt_JoinsTheSummaryAndKeepsEveryRow, Test{Npm,Maven,Container}VirtualUpstreamManagementListStmt_ProjectsNoCredential
13	Every field of the three management rows carries an alias tag its statement renders	TestVirtualUpstreamManagementRows_AliasEveryFieldInTheStatement
14	The npm, Maven, and container management reads plan over exactly four partitioned tables, each pruned to one partition	Test{Npm,Maven,Container}VirtualUpstreamManagementList_PlanShape
15	A tombstoned remote upstream still listed carries its stored URL and health, on the npm, Maven, and container reads	Test{Npm,Maven,Container}VirtualUpstreamStore_Upstreams_KeepsATombstonedUpstream
16	The contract requires id, name, format, and kind alone, declares visibility ($ref: Visibility) and size_bytes (int64) optional, and declares settings optional as UpstreamRepositorySummarySettings, an open schema of exactly url (string, maxLength: 1024) and last_health_status ($ref: HealthStatus), both required. Every handler response validates, a zero size included	TestContract_UpstreamResources_KeepTheirShape, TestContract_UpstreamRepositorySummarySettings_KeepsItsFields, assertMatchesContract in every handler test above, TestNpmUpstreams_List_ZeroSizeIsServed
17	End to end, for a caller who can read every upstream, a remote's settings.url equals its detail's and reads unknown before a probe, on the npm and Maven walks. A hosted upstream created internal lists as internal with no settings, and a fresh upstream carries size_bytes: 0	TestNpmVirtualUpstreamsIntegration_AssociateReorderDissociateWalk, TestNpmVirtualUpstreamsIntegration_ListKeepsATombstonedUpstream, TestMavenVirtualUpstreamsIntegration_AssociateListDissociateWalk
S17 89	Associate a hosted and a remote upstream (201, Location, body), and the list returns the ordered set	The fields through assertUpstreamResource: TestNpmUpstreams_List_ReturnsThePositionOrderedSet, TestNpmUpstreams_Associate_AppendsWhenPositionIsOmitted, TestNpmUpstreams_Get_ReturnsTheAddressedAssociation, TestMavenUpstreams_List_ReturnsThePositionOrderedSet, TestMavenUpstreams_Associate_LocationCarriesTheMavenSegment, and the npm and Maven walks
S17 90	PATCH moves an association, DELETE dissociates	TestNpmUpstreams_Update_MovesAndKeepsTheRestContiguous (the 200 body through assertUpstreamResource, hosted and remote moves), TestNpmUpstreams_Dissociate_RemovesAnUnrenderableRow, the npm walk's move body

Error cases

#	Condition	Tests
E-503	The per-upstream check cannot evaluate on the list, GET, associate, or reposition, and both mutations answer before they write, so nothing is applied	TestNpmUpstreams_EveryRenderingRoute_CheckFailure_Returns503 (no store write, no audit event, no Location), TestNpmUpstreams_List_VerdictFailure_FailsClosed, TestMavenUpstreams_List_VerdictFailure_FailsClosed
E-abandon	A request whose context ends during the check writes no response and applies nothing	TestNpmUpstreams_EveryRenderingRoute_AbandonedCheck_WritesNothing
E-500	The summary's fail-closed arms, and a check batch that does not answer one set per rendered upstream, answer 500 internal_server_error	TestNpmUpstreams_UnrenderableSummaryField_Returns500, TestNpmUpstreams_UnrenderableSummaryField_Returns500ToAnUnreadableCaller, TestUpstreamAssociationFromRow_FailsClosed, TestNpmUpstreams_EveryRenderingRoute_MalformedCheckBatch_Returns500 (nothing applied), the list half of TestNpmUpstreams_Dissociate_RemovesAnUnrenderableRow, and the unchanged TestNpmUpstreams_UnrenderableUpstreamEnum_Returns500

Security considerations

#	Concern	Tests
S-1	What the upstream summary discloses	Fields withheld from a caller who cannot read the upstream, on every rendering route and in a mixed list: TestNpmUpstreams_EveryRenderingRoute_OmitsAnUnreadableUpstreamsFields, TestNpmUpstreams_List_MixesReadableAndUnreadableRows, TestNpmUpstreams_Get_FollowsTheAddressedUpstreamsReadVerdict, TestNpmUpstreams_List_FieldsFollowTheUpstreamsReadVerdict, TestMavenUpstreams_ListAndGet_OmitAnUnreadableUpstreamsFields, TestContainerUpstreamArms_List_MixesReadableAndUnreadableRows. The check keys on each upstream's own id under its namespace and organization, in list order, once per response, sharing the opt-in's evaluation: TestNpmUpstreams_EveryRenderingRoute_ChecksTheRowsItRendersOnce, TestNpmUpstreams_List_ChecksEachUpstreamUnderItsOrganizationInListOrder, TestNpmUpstreams_List_OptIn_ReadsTheCheckFromReadRepository, Test{Npm,Maven}Upstreams_List_OptOutOmitsPermissions. A failed check answers 503, an abandoned one writes nothing, and neither applies a write: TestNpmUpstreams_EveryRenderingRoute_CheckFailure_Returns503, TestNpmUpstreams_EveryRenderingRoute_AbandonedCheck_WritesNothing. No credential column rides the join: Test{Npm,Maven,Container}VirtualUpstreamManagementListStmt_ProjectsNoCredential. No has_credentials and exactly two settings fields: TestContract_UpstreamRepositorySummarySettings_KeepsItsFields. A stored URL the write gate refuses never renders, and its credential reaches neither body, log, nor error: TestNpmUpstreams_UnrenderableSummaryField_Returns500, TestUpstreamAssociationFromRow_FailsClosed. A token in a path segment or hostname label reaches only a caller who can read the upstream, as its own detail shows it, and has no test. The role requirement is the routes' existing authorization, unchanged here

Database Review Evidence

No file under internal/datastore/migrations/sql/ changed, so migration mode did not run.

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against PostgreSQL 17.10 (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), started with CI's .pg-service-options flags, in a dedicated database dropped at the end of the run, with synthesized seed data rolled back per query. 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. The Index column names each declared index, and the plans name the partition's copy of it. 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.containerVirtualUpstreamManagementListStmt	Sort	unique_cvr_namespace_id_and_repository_id, unique_cvru_ns_id_repository_id_upstream_repository_id, pk_repositories, unique_container_remote_repositories_namespace_id_repository_id	5 / 20	31.88	0.119ms	116 / 0	1/64, 1/64, 1/64, 1/64
datastore.mavenVirtualUpstreamManagementListStmt	Sort	unique_maven_virtual_repositories_namespace_id_repository_id, unique_mvru_ns_id_repository_id_upstream_repository_id, pk_repositories, unique_maven_remote_repositories_namespace_id_and_repository_id	5 / 20	30.80	0.148ms	119 / 0	1/64, 1/64, 1/64, 1/64
datastore.npmVirtualUpstreamManagementListStmt	Sort	unique_nvr_ns_id_repository_id, unique_nvru_ns_id_repository_id_upstream_repository_id, pk_repositories, unique_npm_remote_repositories_namespace_id_and_repository_id	5 / 20	29.71	0.115ms	119 / 0	1/64, 1/64, 1/64, 1/64

Partition routing: each read's four tables hold every row of the target namespace in their _p32 partition, and each table also holds rows in _p01, _p05, and _p06, so the one partition each plan scans per table is pruning, not an empty table.

Against the merge-base statement, the outer join adds 50 buffer hits per read, one probe of the remote child's unique key per junction row. Over seven warm literal-SQL runs per read, median planning rises from 0.31-0.33 ms to 0.51-0.52 ms and median execution from 0.09 ms to 0.13-0.14 ms. Each read still returns its 20 junction rows, 10 with the remote pair and 10 hosted with it NULL. The root Sort predates this MR and has no index-order alternative, since with enable_sort off each planner still sorts, and each root's 5-row estimate against 20 actual rows is the seed's five-per-list average.

Query notes:

• datastore.containerVirtualUpstreamManagementListStmt and datastore.npmVirtualUpstreamManagementListStmt: neither read carries a LIMIT, which deviates from "Bounding result sets" in docs/dev/database-query-patterns.md. The deviation predates this MR, since the merge-base statements carry none either, and container_virtual_upstreams.md and npm_virtual_upstreams.md record why the bound is refused: the store holds each list it writes to 20 rows, and a truncating bound would hide the rows past it from the caller that lists them to remove them. The npm read shipped with the same note on feat(managementapi): npm virtual upstream assoc... (!2735 - merged) • Hayley Swimelar • 19.5. This MR adds no row, because each outer join matches on its remote table's unique (namespace_id, repository_id) key. The Maven read needs no note, since check_mvru_position_range and unique_mvru_ns_id_repository_id_position hold its lists to 20 rows in the schema.

datastore.containerVirtualUpstreamManagementListStmt

Summary: Plan matches the read's intent: the virtual repository resolves on unique_cvr_namespace_id_and_repository_id, its junction rows on unique_cvru_ns_id_repository_id_upstream_repository_id, and each upstream on pk_repositories, while the new outer join probes unique_container_remote_repositories_namespace_id_repository_id once per row, with all four tables pruned to one partition of 64. The plan's container_remote_repositories__namespace_id_repository_id_idx29 is that index's copy on _p32, whose suffix counts name collisions rather than naming the partition. The one anomaly is the missing LIMIT in the Query notes.

Seed shape: namespaces=4 (the target plus three neighbors in other partitions), repositories=24000 (15000 in the target namespace: per family 1000 virtual, 2000 hosted, and 2000 remote), container_virtual_repositories=1600 (1000 in the target), container_remote_repositories=3200 (2000 in the target), container_virtual_repository_upstreams=8015 (5015 in the target: five per virtual repository, odd positions hosted and even positions remote, the read's own list topped up to the cap of 20). The npm and Maven tables hold the same counts.

Rendered SQL:

SELECT container_virtual_repository_upstreams.id AS "container_virtual_repository_upstreams.id",
     container_virtual_repository_upstreams.position AS "container_virtual_repository_upstreams.position",
     container_virtual_repository_upstreams.upstream_repository_id AS "container_virtual_repository_upstreams.upstream_repository_id",
     repositories.name AS "repositories.name",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.size_bytes AS "repositories.size_bytes",
     container_remote_repositories.url AS "container_remote_repositories.url",
     container_remote_repositories.last_health_status AS "container_remote_repositories.last_health_status"
FROM public.container_virtual_repository_upstreams
     INNER JOIN public.container_virtual_repositories ON ((container_virtual_repositories.namespace_id = container_virtual_repository_upstreams.namespace_id) AND (container_virtual_repositories.id = container_virtual_repository_upstreams.container_virtual_repository_id))
     INNER JOIN public.repositories ON ((repositories.namespace_id = container_virtual_repository_upstreams.namespace_id) AND (repositories.id = container_virtual_repository_upstreams.upstream_repository_id))
     LEFT JOIN public.container_remote_repositories ON ((container_remote_repositories.namespace_id = $1::uuid) AND (container_remote_repositories.repository_id = container_virtual_repository_upstreams.upstream_repository_id))
WHERE (((container_virtual_repository_upstreams.namespace_id = $2::uuid) AND (container_virtual_repositories.namespace_id = $3::uuid)) AND (container_virtual_repositories.repository_id = $4::uuid)) AND (repositories.namespace_id = $5::uuid)
ORDER BY container_virtual_repository_upstreams.position ASC;

Bound args: [01930000-0000-7000-8000-000000000001, 01930000-0000-7000-8000-000000000001, 01930000-0000-7000-8000-000000000001, 00000001-0002-7001-8000-000000000001, 01930000-0000-7000-8000-000000000001]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Sort  (cost=31.87..31.88 rows=5 width=108) (actual time=0.117..0.119 rows=20 loops=1)
  Sort Key: container_virtual_repository_upstreams."position"
  Sort Method: quicksort  Memory: 27kB
  Buffers: shared hit=116
  ->  Nested Loop Left Join  (cost=1.12..31.81 rows=5 width=108) (actual time=0.046..0.102 rows=20 loops=1)
        Buffers: shared hit=116
        ->  Nested Loop  (cost=0.84..29.67 rows=5 width=60) (actual time=0.037..0.072 rows=20 loops=1)
              Buffers: shared hit=66
              ->  Nested Loop  (cost=0.56..23.73 rows=5 width=52) (actual time=0.026..0.031 rows=20 loops=1)
                    Buffers: shared hit=6
                    ->  Index Scan using container_virtual_repositories_namespace_id_repository_id_idx32 on container_virtual_repositories_p32 container_virtual_repositories  (cost=0.28..8.29 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=1)
                          Index Cond: ((namespace_id = '01930000-0000-7000-8000-000000000001'::uuid) AND (repository_id = '00000001-0002-7001-8000-000000000001'::uuid))
                          Buffers: shared hit=3
                    ->  Index Scan using container_virtual_repository_namespace_id_container_virtu_idx32 on container_virtual_repository_upstreams_p32 container_virtual_repository_upstreams  (cost=0.28..15.38 rows=5 width=68) (actual time=0.013..0.016 rows=20 loops=1)
                          Index Cond: ((namespace_id = '01930000-0000-7000-8000-000000000001'::uuid) AND (container_virtual_repository_id = container_virtual_repositories.id))
                          Buffers: shared hit=3
              ->  Index Scan using repositories_p32_pkey on repositories_p32 repositories  (cost=0.29..1.18 rows=1 width=56) (actual time=0.002..0.002 rows=1 loops=20)
                    Index Cond: ((id = container_virtual_repository_upstreams.upstream_repository_id) AND (namespace_id = '01930000-0000-7000-8000-000000000001'::uuid))
                    Buffers: shared hit=60
        ->  Index Scan using container_remote_repositories__namespace_id_repository_id_idx29 on container_remote_repositories_p32 container_remote_repositories  (cost=0.28..0.42 rows=1 width=64) (actual time=0.001..0.001 rows=0 loops=20)
              Index Cond: ((namespace_id = '01930000-0000-7000-8000-000000000001'::uuid) AND (repository_id = container_virtual_repository_upstreams.upstream_repository_id))
              Buffers: shared hit=50
Planning:
  Buffers: shared hit=108
Planning Time: 1.025 ms
Execution Time: 0.167 ms

Timings: planning 1.025ms, execution 0.167ms, total 1.192ms. The planning figure is the session's first plan of the statement, and the warm medians are 0.514ms planning and 0.135ms execution.

datastore.mavenVirtualUpstreamManagementListStmt

Summary: Plan matches the read's intent: the virtual repository resolves on unique_maven_virtual_repositories_namespace_id_repository_id, its junction rows on unique_mvru_ns_id_repository_id_upstream_repository_id, and each upstream on pk_repositories, while the new outer join probes unique_maven_remote_repositories_namespace_id_and_repository_id once per row, with all four tables pruned to one partition of 64. No anomalies.

Seed shape: namespaces=4 (the target plus three neighbors in other partitions), repositories=24000 (15000 in the target namespace: per family 1000 virtual, 2000 hosted, and 2000 remote), maven_virtual_repositories=1600 (1000 in the target), maven_remote_repositories=3200 (2000 in the target), maven_virtual_repository_upstreams=8015 (5015 in the target: five per virtual repository, odd positions hosted and even positions remote, the read's own list topped up to the cap of 20). The npm and container tables hold the same counts.

Rendered SQL:

SELECT maven_virtual_repository_upstreams.id AS "maven_virtual_repository_upstreams.id",
     maven_virtual_repository_upstreams.position AS "maven_virtual_repository_upstreams.position",
     maven_virtual_repository_upstreams.upstream_repository_id AS "maven_virtual_repository_upstreams.upstream_repository_id",
     repositories.name AS "repositories.name",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.size_bytes AS "repositories.size_bytes",
     maven_remote_repositories.url AS "maven_remote_repositories.url",
     maven_remote_repositories.last_health_status AS "maven_remote_repositories.last_health_status"
FROM public.maven_virtual_repository_upstreams
     INNER JOIN public.maven_virtual_repositories ON ((maven_virtual_repositories.namespace_id = maven_virtual_repository_upstreams.namespace_id) AND (maven_virtual_repositories.id = maven_virtual_repository_upstreams.maven_virtual_repository_id))
     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))
WHERE (((maven_virtual_repository_upstreams.namespace_id = $2::uuid) AND (maven_virtual_repositories.namespace_id = $3::uuid)) AND (maven_virtual_repositories.repository_id = $4::uuid)) AND (repositories.namespace_id = $5::uuid)
ORDER BY maven_virtual_repository_upstreams.position ASC;

Bound args: [01930000-0000-7000-8000-000000000001, 01930000-0000-7000-8000-000000000001, 01930000-0000-7000-8000-000000000001, 00000001-0001-7001-8000-000000000001, 01930000-0000-7000-8000-000000000001]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Sort  (cost=30.79..30.80 rows=5 width=104) (actual time=0.146..0.148 rows=20 loops=1)
  Sort Key: maven_virtual_repository_upstreams."position"
  Sort Method: quicksort  Memory: 27kB
  Buffers: shared hit=119
  ->  Nested Loop Left Join  (cost=1.12..30.73 rows=5 width=104) (actual time=0.054..0.128 rows=20 loops=1)
        Buffers: shared hit=119
        ->  Nested Loop  (cost=0.84..28.72 rows=5 width=60) (actual time=0.043..0.086 rows=20 loops=1)
              Buffers: shared hit=69
              ->  Nested Loop  (cost=0.56..23.73 rows=5 width=52) (actual time=0.029..0.037 rows=20 loops=1)
                    Buffers: shared hit=9
                    ->  Index Scan using maven_virtual_repositories_p32_namespace_id_repository_id_idx on maven_virtual_repositories_p32 maven_virtual_repositories  (cost=0.28..8.29 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=1)
                          Index Cond: ((namespace_id = '01930000-0000-7000-8000-000000000001'::uuid) AND (repository_id = '00000001-0001-7001-8000-000000000001'::uuid))
                          Buffers: shared hit=3
                    ->  Index Scan using maven_virtual_repository_ups_namespace_id_maven_virtual_r_idx32 on maven_virtual_repository_upstreams_p32 maven_virtual_repository_upstreams  (cost=0.28..15.38 rows=5 width=68) (actual time=0.013..0.019 rows=20 loops=1)
                          Index Cond: ((namespace_id = '01930000-0000-7000-8000-000000000001'::uuid) AND (maven_virtual_repository_id = maven_virtual_repositories.id))
                          Buffers: shared hit=6
              ->  Index Scan using repositories_p32_pkey on repositories_p32 repositories  (cost=0.29..0.99 rows=1 width=56) (actual time=0.002..0.002 rows=1 loops=20)
                    Index Cond: ((id = maven_virtual_repository_upstreams.upstream_repository_id) AND (namespace_id = '01930000-0000-7000-8000-000000000001'::uuid))
                    Buffers: shared hit=60
        ->  Index Scan using maven_remote_repositories_p32_namespace_id_repository_id_idx on maven_remote_repositories_p32 maven_remote_repositories  (cost=0.28..0.39 rows=1 width=60) (actual time=0.002..0.002 rows=0 loops=20)
              Index Cond: ((namespace_id = '01930000-0000-7000-8000-000000000001'::uuid) AND (repository_id = maven_virtual_repository_upstreams.upstream_repository_id))
              Buffers: shared hit=50
Planning:
  Buffers: shared hit=108
Planning Time: 1.124 ms
Execution Time: 0.201 ms

Timings: planning 1.124ms, execution 0.201ms, total 1.325ms. The planning figure is the session's first plan of the statement, and the warm medians are 0.509ms planning and 0.133ms execution.

datastore.npmVirtualUpstreamManagementListStmt

Summary: Plan matches the read's intent: the virtual repository resolves on unique_nvr_ns_id_repository_id, its junction rows on unique_nvru_ns_id_repository_id_upstream_repository_id, and each upstream on pk_repositories, while the new outer join probes unique_npm_remote_repositories_namespace_id_and_repository_id once per row, with all four tables pruned to one partition of 64. The one anomaly is the missing LIMIT in the Query notes.

Seed shape: namespaces=4 (the target plus three neighbors in other partitions), repositories=24000 (15000 in the target namespace: per family 1000 virtual, 2000 hosted, and 2000 remote), npm_virtual_repositories=1600 (1000 in the target), npm_remote_repositories=3200 (2000 in the target), npm_virtual_repository_upstreams=8015 (5015 in the target: five per virtual repository, odd positions hosted and even positions remote, the read's own list topped up to the cap of 20). The Maven and container tables hold the same counts.

Rendered SQL:

SELECT npm_virtual_repository_upstreams.id AS "npm_virtual_repository_upstreams.id",
     npm_virtual_repository_upstreams.position AS "npm_virtual_repository_upstreams.position",
     npm_virtual_repository_upstreams.upstream_repository_id AS "npm_virtual_repository_upstreams.upstream_repository_id",
     repositories.name AS "repositories.name",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.size_bytes AS "repositories.size_bytes",
     npm_remote_repositories.url AS "npm_remote_repositories.url",
     npm_remote_repositories.last_health_status AS "npm_remote_repositories.last_health_status"
FROM public.npm_virtual_repository_upstreams
     INNER JOIN public.npm_virtual_repositories ON ((npm_virtual_repositories.namespace_id = npm_virtual_repository_upstreams.namespace_id) AND (npm_virtual_repositories.id = npm_virtual_repository_upstreams.npm_virtual_repository_id))
     INNER JOIN public.repositories ON ((repositories.namespace_id = npm_virtual_repository_upstreams.namespace_id) AND (repositories.id = npm_virtual_repository_upstreams.upstream_repository_id))
     LEFT JOIN public.npm_remote_repositories ON ((npm_remote_repositories.namespace_id = $1::uuid) AND (npm_remote_repositories.repository_id = npm_virtual_repository_upstreams.upstream_repository_id))
WHERE (((npm_virtual_repository_upstreams.namespace_id = $2::uuid) AND (npm_virtual_repositories.namespace_id = $3::uuid)) AND (npm_virtual_repositories.repository_id = $4::uuid)) AND (repositories.namespace_id = $5::uuid)
ORDER BY npm_virtual_repository_upstreams.position ASC;

Bound args: [01930000-0000-7000-8000-000000000001, 01930000-0000-7000-8000-000000000001, 01930000-0000-7000-8000-000000000001, 00000001-0000-7001-8000-000000000001, 01930000-0000-7000-8000-000000000001]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Sort  (cost=29.69..29.71 rows=5 width=102) (actual time=0.114..0.115 rows=20 loops=1)
  Sort Key: npm_virtual_repository_upstreams."position"
  Sort Method: quicksort  Memory: 27kB
  Buffers: shared hit=119
  ->  Nested Loop Left Join  (cost=1.12..29.64 rows=5 width=102) (actual time=0.047..0.098 rows=20 loops=1)
        Buffers: shared hit=119
        ->  Nested Loop  (cost=0.84..27.78 rows=5 width=60) (actual time=0.033..0.064 rows=20 loops=1)
              Buffers: shared hit=69
              ->  Nested Loop  (cost=0.56..23.73 rows=5 width=52) (actual time=0.023..0.030 rows=20 loops=1)
                    Buffers: shared hit=9
                    ->  Index Scan using npm_virtual_repositories_p32_namespace_id_repository_id_idx on npm_virtual_repositories_p32 npm_virtual_repositories  (cost=0.28..8.29 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=1)
                          Index Cond: ((namespace_id = '01930000-0000-7000-8000-000000000001'::uuid) AND (repository_id = '00000001-0000-7001-8000-000000000001'::uuid))
                          Buffers: shared hit=3
                    ->  Index Scan using npm_virtual_repository_upstr_namespace_id_npm_virtual_rep_idx32 on npm_virtual_repository_upstreams_p32 npm_virtual_repository_upstreams  (cost=0.28..15.38 rows=5 width=68) (actual time=0.010..0.015 rows=20 loops=1)
                          Index Cond: ((namespace_id = '01930000-0000-7000-8000-000000000001'::uuid) AND (npm_virtual_repository_id = npm_virtual_repositories.id))
                          Buffers: shared hit=6
              ->  Index Scan using repositories_p32_pkey on repositories_p32 repositories  (cost=0.29..0.80 rows=1 width=56) (actual time=0.001..0.001 rows=1 loops=20)
                    Index Cond: ((id = npm_virtual_repository_upstreams.upstream_repository_id) AND (namespace_id = '01930000-0000-7000-8000-000000000001'::uuid))
                    Buffers: shared hit=60
        ->  Index Scan using npm_remote_repositories_p32_namespace_id_repository_id_idx on npm_remote_repositories_p32 npm_remote_repositories  (cost=0.28..0.36 rows=1 width=58) (actual time=0.001..0.001 rows=0 loops=20)
              Index Cond: ((namespace_id = '01930000-0000-7000-8000-000000000001'::uuid) AND (repository_id = npm_virtual_repository_upstreams.upstream_repository_id))
              Buffers: shared hit=50
Planning:
  Buffers: shared hit=107 read=1
Planning Time: 1.117 ms
Execution Time: 0.173 ms

Timings: planning 1.117ms, execution 0.173ms, total 1.290ms. The planning figure is the session's first plan of the statement, and the warm medians are 0.521ms planning and 0.140ms execution.

Context for LLM agents

Rationale

• Layout: settings on remote rows, a subset of the upstream's own, so each field keeps the repository resource's path. Rejected alternatives:
  • A oneOf on kind, which repeats permissions in both branches for a discrimination kind already carries.
  • Flat optional url and last_health_status, which sit at paths the repository resource does not use.
  • null on hosted rows, which needs a nullable HealthStatus beside the $ref every other last_health_status uses.
  • The whole Settings union, which serves has_credentials and the cache windows to every reader of the list.
• Disclosure: the four fields follow read_repository on each upstream, the ADR-021 DRI's direction in review, because a role on the virtual repository grants the content served through it and the upstream names (docs/adr/021_authorization.md:196 and :289), not facts about the upstreams. It costs one relationships read and one BatchCheck per response and a 503 on four responses. Rejected: serving the fields to every reader of the list, the original choice, which needs an ADR-021 amendment extending the names rule to these fields and is one-way inside v1. Also rejected: gating only url and last_health_status, which pays the same evaluation on every remote row.
• Check placement: before the write on the associate and the reposition, because the contract's 503 promises nothing was applied. Rejected: checking the settled row after the write, which answers 503 for a committed write. The reposition checks the association's current upstream from one more list read, because a move never changes it. Under the opt-in the check reads the opt-in's own verdict sets. Rejected: a separate read-only batch, which evaluates twice for one answer.
• Read: one LEFT JOIN per format family. Rejected: the batched FindRemoteDetailsByRepositoryIDs read, which drops a tombstoned upstream's remote row, adds a round trip to four responses, and refuses sets past its page bound. One detail read per row is the N+1 ADR-014 forbids.
• Render-time URL check: the whole write gate. Rejected: checking only the credential-bearing components, which keeps lists up through a gate change but stops tracking the refusals the gate adds.

Non-goals

• Permission verdicts on the summary: Embed upstream repository verdicts in listUpstr... (#1174 - closed) • David Fernandez • 19.5, merged for npm as feat(managementapi): embed upstream repository ... (!2888 - merged) • David Fernandez • 19.5.
• last_health_checked_at on the summary: not requested, and additive later inside the open settings schema.
• The container arm's remote fixtures and e2e rows: Step 42's remaining parts.
• The ADR-021 handbook amendment: S17's Follow-ups track it.
• A stricter URL gate, such as one refusing a token in a path: the stored URLs need a backfill first, or every list naming an affected row answers 500.

🤖 Generated with Claude Code

Closes #1438 (closed)