OAuth2 tokens issued by Coder ignore scope entirely. /oauth2/authorize parses the scope parameter and then discards it (// TODO: Ignoring scope for now), and both grant functions mint API keys with no Scopes set while performing the exchange as rbac.ScopeAll, so every token gets full API access regardless of what the client requested or what the app's allowlist permits. There is also nowhere to put a negotiated scope: nothing in the pipeline carries one from the authorize step to the token it produces.

This PR is the schema and query groundwork for that pipeline and changes no behavior on its own. Migration 000567 adds a nullable scope text to oauth2_provider_app_codes and oauth2_provider_app_tokens, so a negotiated scope can travel from a code to the token it is exchanged for, and from a token to its refreshed successor. It also adds DeleteOAuth2ProviderAppCodeByIDReturningID and DeleteAPIKeyByIDReturningID, which return sql.ErrNoRows when the row is already gone: Postgres serializes concurrent deletes on the row lock, so exactly one caller gets a row back, which is what will let the grant paths enforce single use of a code or refresh token without a read-then-write race. The existing blind :exec deletes and all of their call sites are untouched, and every insert writes NULL for the new column, which the token endpoint reads as unrestricted access, so current behavior is preserved exactly.

Phase 1 of PLAT-470, tracked as PLAT-478. Scope validation at /oauth2/authorize, applying the negotiated scope in authorizationCodeGrant, and refresh narrowing plus wiring in the two new deletes follow as separate PRs.

flowchart TD
    subgraph authorize["/oauth2/authorize"]
        AZ1["ShowAuthorizePage (GET)<br/>renders consent page"]
        AZ2["ProcessAuthorize (POST)<br/>scope parsed, then discarded"]
        Q1["InsertOAuth2ProviderAppCode<br/>gains a Scope param"]
        AZ1 --> AZ2 --> Q1
    end

    Q1 --> CODES[("oauth2_provider_app_codes<br/>new column: scope text NULL")]

    subgraph codegrant["POST /oauth2/token, grant_type=authorization_code"]
        G1["authorizationCodeGrant"]
        Q2["GetOAuth2ProviderAppCodeByPrefix<br/>now returns Scope"]
        Q4["DeleteOAuth2ProviderAppCodeByIDReturningID<br/>added, no caller yet"]
        G2["apikey.Generate + UserRBACSubject<br/>hardcoded to full access"]
        G1 --> Q2 --> G2
        G1 -.-> Q4
    end

    CODES --> G1
    G2 --> Q3

    Q3["InsertOAuth2ProviderAppToken<br/>gains a Scope param"]
    Q3 --> TOKENS[("oauth2_provider_app_tokens<br/>new column: scope text NULL")]

    subgraph refresh["POST /oauth2/token, grant_type=refresh_token"]
        G3["refreshTokenGrant"]
        Q5["GetOAuth2ProviderAppTokenByPrefix<br/>now returns Scope"]
        Q6["DeleteAPIKeyByIDReturningID<br/>added, no caller yet"]
        G3 --> Q5
        G3 -.-> Q6
    end

    TOKENS --> G3
    Q5 --> Q3

    subgraph enforce["Every authenticated API request"]
        E1["httpmw ExtractAPIKey"] --> E2["APIKey.ScopeSet()"] --> E3["UserRBACSubject"] --> E4["dbauthz authorize"]
    end

    TOKENS --> E1

    classDef changed fill:#c8e6c9,stroke:#2e7d32,stroke-width:2px,color:#1b3c1e
    classDef dormant fill:#c8e6c9,stroke:#2e7d32,stroke-width:2px,stroke-dasharray:5 3,color:#1b3c1e
    class Q1,Q2,Q3,Q5,CODES,TOKENS changed
    class Q4,Q6 dormant

How to review this PR

Most of the diff is generated. Suggested reading order:

1. coderd/database/migrations/000567_oauth2_scope_enforcement.{up,down}.sql: the schema change. Additive, nullable, no backfill, with a COMMENT ON COLUMN on each recording that NULL means unrestricted.
2. coderd/database/queries/oauth2.sql: scope added to both insert column lists, plus the new DeleteOAuth2ProviderAppCodeByIDReturningID :one alongside the untouched DeleteOAuth2ProviderAppCodeByID. The two Get...ByPrefix selects needed no edit, since they are SELECT * and return the generated model.
3. coderd/database/queries/apikeys.sql: the same atomic delete for api_keys, alongside the untouched DeleteAPIKeyByID. Its 8 existing call sites in userauth.go, apikey.go, revoke.go, provisionerdserver.go, and tokens.go are unchanged.
4. coderd/database/dbauthz/dbauthz.go: hand-written wrappers for the two new queries, each fetching via the existing Get...ByID, authorizing policy.ActionDelete against the fetched object, then delegating. The generic deleteQ helper does not fit, since it requires the delete to return only error.
5. coderd/oauth2provider/authorize.go and coderd/oauth2provider/tokens.go: the only production changes, and behavior-neutral. exhaustruct requires the new field at every Insert...Params construction site, so these three pass an explicit empty sql.NullString{} until later phases negotiate a real value.
6. coderd/database/dbgen/dbgen.go: threads Scope from the seed, matching the existing style for ResourceUri and Audience, so later phases can seed both a populated scope and a NULL one.
7. coderd/database/dbauthz/dbauthz_test.go: a case per new query. MethodTestSuite seeds its accounting map from database.Store by reflection and fails with Method never called for anything untested, so a new query on the interface requires one.

coderd/database/{dump.sql,models.go,querier.go,queries.sql.go} and the dbmock/dbmetrics packages are fully generated by make gen; no need to review them directly.

Verified locally: make gen and make lint both clean (no enterprise/audit/table.go errors, confirming neither type needs to become auditable), the coderd/database/migrations suite passes both up and down, and dbauthz's TestMethodTestSuite passes.