[FR] Add GET /api/connections admin endpoint for monitoring connection sync state

[Harsh23Kashyap]

Problem

Sourcebot's "settings → connections" page (/settings/connections) is the only place a self-hosted operator can see the per-connection sync state for the org: name, code-host type, last successful sync, latest job status, and a count of in-flight jobs. The data is loaded via a Server Action (getConnectionsWithLatestJob) that reads from Prisma directly. There is no public HTTP endpoint that exposes the same data, so:

• Operators can't run curl from a script to see which code-host connections are failing.
• The settings page can't be linked from a status dashboard.
• The data isn't available to any external monitoring tool (Prometheus exporter, Slack alert, etc.).

Motivation

Three concrete operator scenarios this would unblock:

1. Operational monitoring. A bot runs every 5 minutes and curls a connection-status endpoint. If a connection's latestJobStatus is FAILED, the bot alerts the on-call. Today the only way to do this is to scrape the Prisma DB directly, which requires DB credentials and bypasses the application layer.
2. External dashboarding. A Grafana / Datadog / New Relic panel reads the connection status alongside other Sourcebot metrics. With this endpoint, the panel can hit the same authenticated route the UI uses.
3. Self-audit. Before rolling out a config change that touches a connection, the operator curls the endpoint to confirm the connection is currently COMPLETED (not IN_PROGRESS), so the change doesn't race with an in-flight sync.

Current behavior

$ curl -fsS https://sourcebot.example.com/api/connections
404 Not Found

The data exists internally and is rendered on the settings page, but there is no public route that returns it.

Expected behavior

A paginated, auth-gated endpoint that returns the per-connection sync state in a stable, documented JSON shape.

GET /api/connections?page=1&perPage=50

{
 "connections": [
 {
 "id": 1,
 "name": "github-public",
 "connectionType": "github",
 "isDeclarative": false,
 "createdAt": "2026-06-12T08:21:00.000Z",
 "updatedAt": "2026-07-25T14:00:00.000Z",
 "syncedAt": "2026-07-25T14:00:00.000Z",
 "latestJob": {
 "id": "clx...",
 "status": "COMPLETED",
 "createdAt": "2026-07-25T13:59:30.000Z",
 "completedAt": "2026-07-25T14:00:00.000Z",
 "errorMessage": null
 },
 "repoCount": 137,
 "inFlightJobCount": 0
 }
 ]
}

Standard pagination headers (X-Total-Count, Link) follow the existing pattern used by /api/repos and /api/ee/audit.

The endpoint is auth-gated (any signed-in user) — the same access level as the settings page. Connection config (which carries tokens) is not in the response.

Proposed solution

• New route: packages/web/src/app/api/(server)/connections/route.ts. Auth: withAuth (any signed-in user in the org; same access as the existing settings page).
• Query params: page (default 1, positive int), perPage (default 50, max 100, positive int). Same Zod pattern used by /api/ee/audit.
• Action: listConnectionsApi.ts (in the same folder) uses withAuth and a sew wrapper to call prisma.connection.findMany with include: { syncJobs: { orderBy: { createdAt: 'desc' }, take: 1 }, _count: { select: { syncJobs: true, repos: true } } }. A second query (prisma.connectionSyncJob.count with status: { in: [PENDING, IN_PROGRESS] }) populates inFlightJobCount per connection.
• Response: typed via a new Zod schema listConnectionsResponseSchema in packages/web/src/lib/schemas.ts. Registered in publicApiSchemas.ts and added to the public OpenAPI document under a new "Connections" tag (or under the existing "Repositories" tag — open to maintainer preference).
• Tests: 6+ cases covering auth (anonymous → 401), pagination (page/perPage validation, X-Total-Count, Link header), the latestJob null case (connection that has never been synced), the inFlightJobCount for connections with pending jobs, and the connection-type passthrough.
• Docs: a new docs/docs/api-reference/connections.mdx page describing the response shape, the auth requirement, and a one-line curl example. The docs/docs.json is updated to add the page under the "API Reference" tab (and to remove the now-redundant OpenAPI entry for GET /api/connections if one is generated).
• CHANGELOG: [Unreleased] → Added entry linking to the PR.

Alternatives considered

• Reuse the existing Server Action as a "public" endpoint. Server Actions in Next.js are not reachable as curl-able HTTP routes; they require a serialized RPC. So this isn't an option without making the Server Action itself route-friendly.
• Add a "stats" sub-endpoint (/api/connections/:id/stats) instead of a list. Useful but doesn't address the operator dashboard use case. The list is the higher-value primitive; a per-connection detail endpoint can be a follow-on.
• Make it public (no auth). Rejected: connections are org-private data and include code-host identifiers. Any signed-in org member is the right access level.
• Make it OWNER-gated. Rejected: any signed-in org member can see the connections page in the settings UI, so gating the API stricter than the UI is inconsistent.

Scope

• 2 new files in packages/web/src/app/api/(server)/connections/: route.ts (HTTP handler) and listConnectionsApi.ts (auth + DB).
• 1 new test file: route.test.ts.
• 1 new schema entry in packages/web/src/lib/schemas.ts.
• 1 new public-API schema in packages/web/src/openapi/publicApiSchemas.ts + 1 new path registration in packages/web/src/openapi/publicApiDocument.ts.
• Regenerate docs/api-reference/sourcebot-public.openapi.json (auto-generated by yarn openapi:generate).
• 1 new MDX page (docs/docs/api-reference/connections.mdx) + 1 line in docs/docs.json to wire it.
• 1 new CHANGELOG entry under [Unreleased] → Added.

No new dependencies. No Prisma migration. No changes to backend, schemas, or shared packages.

Acceptance criteria

• GET /api/connections (with valid auth) returns 200 and the documented response shape.
• GET /api/connections (no auth) returns 401.
• GET /api/connections?perPage=0 returns 400 with a clear validation error.
• GET /api/connections?perPage=1000 returns 400 (max 100).
• The connections array is sorted by name ascending.
• X-Total-Count and Link headers are present on the response.
• latestJob is null for connections that have never been synced.
• inFlightJobCount correctly counts PENDING + IN_PROGRESS jobs per connection.
• repoCount matches prisma.repoToConnection.count({ where: { connectionId } }) for each row.
• The config field (which carries tokens) is never in the response.
• ConnectionSyncJob.errorMessage is exposed on the latest job, gated to null when null.
• Public OpenAPI doc is regenerated and the path is in the System or Repositories group.
• Tests pass; lint clean; tsc clean in the new files.

Backward compatibility

Fully additive. No existing routes, schemas, or Prisma models change. Operators who never call the endpoint see no change.

Risks

• The endpoint exposes the list of code-host names and types in the org. This is the same data the settings page already shows to any signed-in org member, so no new information leak.
• The errorMessage field on the latest job may include stack traces or internal hostnames from the worker. The endpoint should pass it through verbatim (operators need it for diagnosis) but the public OpenAPI schema should mark it as a free-form string.
• The endpoint is auth-gated but not entitlement-gated. The connection-list feature is core (not EE), so no entitlement check is needed.

[brendan-kellam]

hi @Harsh23Kashyap - is this a gap you are actually facing in your deployment?