[FR] Add GET /api/connections/:id detail endpoint for connection drill-down

[Harsh23Kashyap]

Problem

PR #1517 added GET /api/connections — a paginated, auth-gated public endpoint that lists code-host connections in the org with per-connection sync state (last successful sync, latest job status, in-flight job count, repo count). The list works for "which connections exist and what state are they in", but it intentionally keeps the per-connection payload small (just the most recent sync job). Operators who want to debug a specific failing connection have to choose between:

1. Hitting the list endpoint and reading the latest job's errorMessage — fine for "what went wrong on the most recent run" but blind to recurring flakiness across the last N runs, to error patterns, and to whether the connection is in a transient state.
2. Scraping the Prisma DB directly — the same anti-pattern the list endpoint was supposed to remove.
3. Hitting the sourcebot UI by hand — manual and not scriptable.

The right primitive is a per-connection detail endpoint that returns the full sync state for one connection: identity, declarative-config source, the most recent N sync jobs (with status, duration, error message), the in-flight job count, the repo count, and a list of error/warning message tails from the recent runs.

Motivation

Three concrete operator scenarios this unblocks:

1. Recurring-failure detection. A connection has been FAILING for 3 days straight, with the same errorMessage. The list endpoint shows the most recent state ("FAILED") but not the pattern. The detail endpoint, by surfacing the recent job history, lets the operator's monitoring script detect "this connection has failed N times in the last 24h" — a much higher-quality alert than a single point-in-time FAILED.
2. Token rotation verification. An operator rotates the code-host token. The next sync should succeed; the one after that should also succeed. The detail endpoint's recent-job history lets a script assert "no FAILED jobs in the last 2 hours" right after the rotation.
3. Pre-change audit. Before pointing a connection at a new org/repo, the operator wants to know "how often does this sync, and what's its recent error rate". Today this requires a DB query; with the detail endpoint it's a single curl.

Current behavior

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

The data exists internally; the Connection and ConnectionSyncJob tables are well-modeled. There is no public route that returns it for one connection.

Expected behavior

A paginated, auth-gated endpoint that returns the per-connection identity, sync state, and recent sync history.

GET /api/connections/1

{
  "connection": {
    "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",
    "repoCount": 137,
    "inFlightJobCount": 0
  },
  "recentJobs": [
    {
      "id": "clx_job_42",
      "status": "COMPLETED",
      "createdAt": "2026-07-25T13:59:30.000Z",
      "completedAt": "2026-07-25T14:00:00.000Z",
      "durationMs": 30147,
      "errorMessage": null,
      "warningMessages": []
    },
    {
      "id": "clx_job_41",
      "status": "FAILED",
      "createdAt": "2026-07-24T13:59:30.000Z",
      "completedAt": "2026-07-24T14:00:01.000Z",
      "durationMs": 31102,
      "errorMessage": "connection refused: host=github.example.com:443",
      "warningMessages": ["8 repos were skipped: 404 not found"]
    }
  ]
}

recentJobs is the most recent N sync jobs for the connection, where N is bounded by a new ?jobLimit query parameter (default 10, max 50). The connection.config field is never in the response (carries tokens).

The durationMs is computed at response time as completedAt - createdAt. The warningMessages is the per-job array from the Prisma model. The 404 happens only for connections in other orgs (the route scopes by orgId from the auth context).

Proposed solution

• New route: packages/web/src/app/api/(server)/connections/[id]/route.ts. Auth: withAuth. Scope: prisma.connection.findFirst({ where: { id, orgId: org.id } }) so cross-org access returns 404, never 403 (avoid leaking the existence of connections in other orgs).
• Query params: ?jobLimit=N (default 10, max 50). Same Zod pattern as page/perPage.
• Action: getConnectionApi.ts (in the same folder) does two queries: one for the connection (with _count: { repos: true } and the in-flight job count via the same groupBy pattern as listConnectionsApi.ts), and one for the recent sync jobs (prisma.connectionSyncJob.findMany({ where: { connectionId }, orderBy: { createdAt: 'desc' }, take: jobLimit })).
• Response: typed via a new Zod schema getConnectionResponseSchema in packages/web/src/lib/schemas.ts. Registered in publicApiSchemas.ts and added to the public OpenAPI document under the System tag.
• Tests: 8+ cases covering: 404 for missing connection, 404 for cross-org connection (the cross-org scope test is the most important security test), 200 with the documented shape, the recentJobs ordering and jobLimit clamp, inFlightJobCount populated from groupBy, the errorMessage and warningMessages exposed verbatim, no config in the response, and jobLimit=0 / jobLimit=1000 validation errors.
• Docs: add a line to docs/docs.json to wire the new page (Mintlify auto-generates the page from the OpenAPI doc).
• CHANGELOG: [Unreleased] → Added entry linking to the PR.

Alternatives considered

• Reuse the list endpoint with a ?include=jobs filter. Rejected: lists and detail endpoints have different shapes and different access-control semantics (list can be sorted/paginated, detail is single-row). Keeping them separate is clearer.
• Add ?include=jobs to the existing list endpoint. Rejected: response shape gets unwieldy (per-row arrays of recent jobs), and it forces the consumer to parse the same shape for two different views.
• Stream the recent jobs via Server-Sent Events. Out of scope. Webhooks (push) and a detail endpoint (pull) are both reasonable, but the detail endpoint is the lower-risk primitive.
• Include the full error stack trace on FAILED jobs. Rejected. The error message is enough for operator diagnosis; the stack trace would leak internal worker details on an authenticated-but-not-Owner endpoint.

Scope

• 2 new files in packages/web/src/app/api/(server)/connections/[id]/: route.ts (HTTP handler) and getConnectionApi.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.
• 1 line in docs/docs.json to wire the auto-generated page.
• 1 new CHANGELOG entry under [Unreleased] → Added.

No new dependencies. No Prisma migration. No changes to backend, schemas, or shared packages. Composes with the list endpoint from PR #1517 (same action module pattern, same auth, same pagination helpers).

Acceptance criteria

• GET /api/connections/:id (with valid auth + matching org) returns 200 and the documented response shape.
• GET /api/connections/:id (with valid auth + other-org connection) returns 404 (not 403 — avoid leaking cross-org existence).
• GET /api/connections/:id (no auth) returns 401.
• GET /api/connections/999999 (valid auth, missing id) returns 404.
• recentJobs is ordered createdAt descending, length bounded by jobLimit (default 10, max 50).
• ?jobLimit=0 returns 400; ?jobLimit=51 returns 400.
• durationMs is positive and bounded by completedAt - createdAt.
• inFlightJobCount correctly counts PENDING + IN_PROGRESS jobs for the connection.
• repoCount matches the count of RepoToConnection rows for the connection.
• The config field (which carries tokens) is never in the response.
• errorMessage and warningMessages are exposed verbatim on FAILED jobs.
• Public OpenAPI doc is regenerated and the path is in the System 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 recent jobs (with error messages) for one connection. This is the same data the settings page already shows for one connection (when you click into a connection), so no new information leak.
• A bad actor who can authenticate as a member of an org could enumerate connection IDs and see error messages for any connection in the org. This is the same trust model as the existing settings page. Connection config is never returned, so the credentials are not exposed.