feat: support public (secretless, PKCE-only) OAuth2 clients - #27195

Layer 1 of a multi-PR split of #27195 (public/secretless PKCE-only
OAuth2 clients), broken up for easier review: database schema, then
the oauth2provider handler logic, then API/e2e integration tests.

Add app_id to oauth2_provider_app_tokens, backfilled from the existing
app_secret_id -> app_id join, so a future public (secretless) client's
tokens can be attributed to their owning app without joining through a
secret that doesn't exist. app_id stays nullable and app_secret_id
stays required for now: no write path populates app_id yet, and no
public clients can be created until a later migration loosens
app_secret_id once the application code that relies on this column
lands.

This keeps the schema change fully backward compatible and
independently mergeable: nothing outside coderd/database changes, and
the full repo builds and tests pass unmodified.

Co-Authored-By: Claude Sonnet 5 <[email protected]>

…ema layer

Add app_id to oauth2_provider_app_tokens and make app_secret_id
nullable, so a public (secretless, PKCE-only) client's tokens can
still be attributed to their owning app for revocation and listing,
without joining through a secret that does not exist. This is a
single, final migration: app_id is populated at insert time from
day one, so there is no window where new rows are written with a
NULL app_id, and no second backfill will ever be needed.

Fixes a review finding from an earlier version of this branch: the
migration previously only added app_id as an unenforced, unpopulated
column, deferring app_secret_id's nullability and app_id's write path
to a later PR. That left a gap where any token created between this
PR merging and the next one landing would carry a NULL app_id
permanently, defeating the column's purpose. Doing the full migration
now, with the insert path updated in the same change, closes that gap
entirely.

This requires touching a few call sites outside coderd/database, but
only mechanically: revoke.go's ownership checks now compare app_id
directly instead of joining through app_secret_id (a genuine
simplification, not a shim), and tokens.go's two insert call sites
supply the new app_id column and wrap app_secret_id as a NullUUID.
No client-type branching exists yet, no public client can be
registered, and behavior for today's confidential-only clients is
unchanged. That capability (registration skipping secret generation,
the token endpoint accepting PKCE alone, discovery advertising
"none") lands in a follow-up PR.

Layer 1 of a multi-PR split of #27195 (public/secretless PKCE-only
OAuth2 clients), broken up for easier review: database schema (this
PR), then the oauth2provider handler logic, then API/e2e integration
tests.

Co-Authored-By: Claude Sonnet 5 <[email protected]>

…#27712)

Layer 1 of a multi-PR split of #27195 (public/secretless PKCE-only
OAuth2 clients), broken up for easier review: **database schema (this
PR)** → oauth2provider handler logic → API/e2e integration tests.

## Goal

Coder's OAuth2 provider only works correctly for confidential clients
today. Public clients — native apps that can't safely hold a shared
secret, such as the CLI's browser-based login flow, IDE plugins (VS
Code, JetBrains), desktop apps, and MCP clients — cannot complete a real
OAuth2 flow against Coder, even though OAuth 2.1 §2.1 explicitly defines
this client type and RFC 8252 §8.5 requires PKCE alone to be sufficient
authentication for it. Every MCP client, CLI login flow, and IDE plugin
is a public client by construction, and none of them can complete a
secretless flow against Coder today: dynamic registration always
classifies a client as confidential regardless of what it asks for, the
token endpoint unconditionally requires a `client_secret`, and discovery
metadata never advertises `"none"` as a supported auth method.

Full write-up:
[ENG-3029](https://linear.app/codercom/issue/ENG-3029/oauth2-support-public-client)

### Overall design (end state across the full PR stack)

`[PR2]` marks handler-layer changes landing in the next PR in this
stack. The green box is what this PR implements.

```mermaid
sequenceDiagram
    autonumber
    participant C as Public Client (CLI/MCP/IDE plugin)
    participant S as coderd (chi router)
    participant H as oauth2provider handlers
    participant DB as PostgreSQL

    Note over C,S: Discovery
    C->>S: GET /.well-known/oauth-authorization-server
    S->>H: GetAuthorizationServerMetadata()
    Note over H: [PR2] add "none" to<br/>the returned auth methods list
    H-->>C: [PR2] 200 { token_endpoint_auth_methods_supported:<br/>[..., "none"] }

    Note over C,S: Dynamic Client Registration
    C->>S: POST /oauth2/register<br/>{redirect_uris, token_endpoint_auth_method: "none"}
    S->>H: CreateDynamicClientRegistration()
    Note over H: [PR2] client type now reads<br/>the request -> "public"
    Note over H: [PR2] skip secret generation<br/>for public clients
    H->>DB: [PR2] INSERT app row<br/>(client_type = 'public')
    DB-->>H: app row
    Note over H: [PR2] skip secret insert entirely
    H-->>C: [PR2] 201 { client_id }<br/>(no client_secret field)

    Note over C,S: Authorization Code + PKCE flow
    C->>S: GET /oauth2/authorize?client_id=...&code_challenge=...
    C->>S: POST /oauth2/tokens (grant_type=authorization_code)<br/>no client_secret
    S->>H: extractTokenRequest()
    Note over H: [PR2] client_secret no longer required<br/>for public clients
    H->>H: authorizationCodeGrant()
    Note over H: [PR2] skip secret lookup for public clients
    Note over H: PKCE verification — already mandatory, unchanged
    rect rgb(198, 239, 206)
    Note over H,DB: [THIS PR] oauth2_provider_app_tokens.app_id<br/>column added (NOT NULL, populated at insert<br/>time from app.ID) and app_secret_id loosened<br/>to nullable. Revocation now checks app_id<br/>directly. Confidential-client behavior is<br/>unchanged — no public client can be created yet.
    H->>DB: [PR2] INSERT refresh token row<br/>(no secret reference, for public clients)
    end
    DB-->>H: token row
    H-->>C: 200 { access_token, refresh_token }
```

## This PR: database schema

A public client has no `client_secret`, so it has nothing to put in
`oauth2_provider_app_tokens.app_secret_id`, which was `NOT NULL`. This
PR makes that column nullable and instead attributes a token to its
owning app through a new, always-populated `app_id` column — so
ownership checks (e.g. revocation) work identically for public and
confidential clients without joining through a secret that may not
exist.

| Column | Before | After (this PR) |
|---|---|---|
| `app_secret_id` | `uuid NOT NULL` | **nullable** |
| `app_id` | — | **new**: `uuid NOT NULL`, `FOREIGN KEY →
oauth2_provider_apps(id) ON DELETE CASCADE`, backfilled for every
existing row and populated on every new insert from that point on |

This is a single, complete migration — not staged across multiple PRs.
An earlier version of this branch deferred `app_secret_id`'s nullability
and the insert-time population of `app_id` to a later PR, keeping this
PR's diff limited to `coderd/database`. [Automated
review](#27712 (comment))
correctly flagged that as unsafe: the migration would backfill existing
rows once, but nothing would populate `app_id` for rows written
afterward, so the moment this PR merged, new tokens would start
accumulating a permanently `NULL` app_id — and if a release happened to
be cut before the follow-up PR landed, that gap could ship to customers
and would need a second, later backfill to close. Doing the full
migration now avoids that: `app_id` is correct from the first row
written, and the promised `NOT NULL` constraint requires no data repair
because it's already enforced.

Closing that gap requires a few mechanical, non-branching touches
outside `coderd/database`:
- `revoke.go`'s two ownership checks now compare `dbToken.AppID`
directly instead of looking up the app through `app_secret_id` — a
genuine simplification (and slightly less code), not a temporary shim.
- `tokens.go`'s two `InsertOAuth2ProviderAppToken` call sites supply the
new `app_id` column and wrap `app_secret_id` as a `NullUUID`.
- `oauth2_test.go`'s one direct-insert test fixture does the same.

None of these introduce client-type branching or new capability — every
client today is still confidential-only, still always presents a secret,
and behavior is unchanged. The full repo builds, vets, and all existing
tests pass unmodified in behavior.

## Coming next

- **PR2 (handler layer)**: `codersdk`'s `DetermineClientType()` reading
the requested `token_endpoint_auth_method`; `registration.go` skipping
secret generation for public clients (and wrapping the app+secret insert
in a single transaction, fixing a pre-existing
orphan-row/visibility-race gap); `tokens.go` making the secret check
conditional so PKCE alone authenticates a public client; `metadata.go`
advertising `"none"` in discovery. No further migration is needed — the
schema this PR ships is already final.
- **PR3 (API/e2e layer)**: integration tests through the real HTTP API
(`coderd/oauth2_test.go`), the MCP OAuth2 e2e flow
(`coderd/mcp/mcp_e2e_test.go`), and the manual test script
(`scripts/oauth2/test-mcp-oauth2.sh`).

Depends on: #27195 (original combined PR, being superseded by this
stack)

---------

Co-authored-by: Claude Sonnet 5 <[email protected]>