# Production PKCE Enablement Handover

Prepared: 2026-06-04

Audience: environment / infra operator

Goal: production backend accepts public OAuth authorization-code flows with PKCE, and public clients can use the read-only app-pack records scope `external.records.read`.

## End State

- NexusCRM engine is at or after the upstream PKCE runtime/schema change from NexusCRM #1735.
- The official L1 Salesforce CRM standard pack is at or after app-pack PR #79, commit `b40a89e4ea799a46205e7214d4f80c45a7db9079`.
- The downstream PingCAP final pack is at or after downstream PR #2381, commit `9f0e457337434bcb8b0c032133d5563afb0f2ac6`.
- Backend runtime has `NEXUSCRM_PUBLIC_OAUTH_PKCE_ENABLED=true`.
- Production DB passes the public OAuth schema readiness contract.
- `_System_OAuthCustomScope` has an active `external.records.read` row with public-client GET/HEAD route policy.
- Existing confidential Connected Apps continue to work as confidential apps.

## Prod Target

- TiDB Cloud display name: `NexusCRM-PingCAP-prod`
- TiDB Cloud cluster id: `10996361222721502359`
- Host: `gateway01.us-west-2.prod.aws.tidbcloud.com`
- Port: `4000`
- Database: `nexuscrm`

Do not commit or paste the production password into tracked docs, shell history, or PR comments. Use the deployment secret manager / env vault for credentials.

## Current Prod Read-Only Observation

Observed with read-only SQL on `2026-06-04 15:14:55 UTC`. No writes were made.

- TiDB version: `8.0.11-TiDB-v8.5.3-serverless`
- `_System_ConnectedApp`: 10 rows
- `_System_ConnectedAppAccess`: 0 rows
- `_System_OAuthCode`: 0 rows
- `_System_OAuthCustomScope`: 0 rows
- `_System_OAuthConsent`: table missing
- Existing Connected Apps: all 10 are `ClientType=confidential`, `RequirePkce=0`, `TokenEndpointAuthMethod=client_secret_post`, `ConsentPolicy=prompt`.
- Active installed pack:
  - name: `salesforce-crm-standard`
  - version: `0.2.0`
  - status: `active`
  - current applied hash: `nexus-pack-sha256-v1:89f5e48d8192a124ae57b99921cd2394115825091a9a26286d1ffd5d9362e1ae`
  - current source path points at the `20260531T113407Z` release, not the PR #2381 final pack.

Readiness gaps currently present:

- Missing table:
  - `_System_OAuthConsent`
- Missing `_System_OAuthCode` columns:
  - `AuthTime`
  - `ClientTypeAtIssue`
  - `CodeChallenge`
  - `CodeChallengeMethod`
  - `CodeHash`
  - `ConsentId`
  - `Issuer`
- Missing `_System_OAuthCustomScope` columns:
  - `AllowedClientTypes`
  - `AllowedMethods`
  - `ResourceLinks`
  - `RoutePatterns`
- Missing `_System_OAuthConsent` columns because the table itself is missing:
  - `ApprovedAt`
  - `ConnectedAppId`
  - `ConsentVersion`
  - `LastUsedAt`
  - `RevokedAt`
  - `RevokedBy`
  - `Scope`
  - `UserId`

Conclusion: production is not PKCE-ready by flag alone. It needs engine schema convergence plus final-pack convergence.

## Required Deployment Sequence

Use the normal production deployment pipeline where possible. Do not run `--reset-db`, `--reinstall-db`, or any destructive database reset path.

1. Prepare artifacts.

   - Engine: NexusCRM at or after upstream #1735.
   - L1 app pack: `pingcap-inc/nexuscrm-app-packs` at or after `b40a89e4ea799a46205e7214d4f80c45a7db9079`.
   - Downstream final pack: `pingcap-inc/nexuscrm-pingcap-pack` at or after PR #2381 commit `9f0e457337434bcb8b0c032133d5563afb0f2ac6`.

2. Take a TiDB backup / restore point before mutation.

3. Install the new engine/runtime into the inactive slot or controlled maintenance window.

4. Set runtime env for the candidate backend:

   ```bash
   export NEXUSCRM_PUBLIC_OAUTH_PKCE_ENABLED=true
   ```

5. Run existing-DB control-plane convergence.

   Preferred path: use the production deployment wrapper that builds the current final pack, validates it, rebinds or reapplies the already-active source-backed `salesforce-crm-standard` pack to the new release path, and then runs the normal convergence/verify gates.

   Important: a bare `go run ./cmd/setup apply` reconciles active source-backed packs from the source identity currently stored in `_System_InstalledPack`. Prod currently stores an old release path, so the deployment must first rebind/reapply `salesforce-crm-standard` to the PR #2381 final pack path.

   If the deployment system stages phases manually, the required phases are:

   ```bash
   go run ./cmd/setup apply-metadata
   go run ./cmd/setup repair
   go run ./cmd/setup apply-packs
   go run ./cmd/setup validate-pack --source "local_path:${FINAL_PACK_PATH}"
   go run ./cmd/setup reapply-pack --source "local_path:${FINAL_PACK_PATH}" --verify-pack
   go run ./cmd/setup rebuild
   go run ./cmd/setup verify-pack salesforce-crm-standard
   ```

   `FINAL_PACK_PATH` must point to the PR #2381 generated final pack directory, normally `packs/NOT-EDIT-final-merged-packs` in the downstream release checkout.

6. Do not use `go run ./cmd/setup init-l0` as the primary prod migration command for the already-populated DB. `init-l0` is the clean L0 path and can reject existing app/business metadata as contamination. For existing prod, use `setup apply` or the equivalent existing-DB convergence phases above.

7. Start the candidate backend and run readiness verification before switching traffic.

## Readiness Verification

The PR #2381 deployment gate checks both the flag and the DB schema. If the deployment tooling exposes `verify_public_oauth_readiness`, run it before traffic cutover.

Manual SQL equivalent for the schema contract should return no rows:

```sql
WITH required_tables AS (
  SELECT '_System_ConnectedApp' AS table_name UNION ALL
  SELECT '_System_ConnectedAppAccess' UNION ALL
  SELECT '_System_OAuthCustomScope' UNION ALL
  SELECT '_System_OAuthCode' UNION ALL
  SELECT '_System_OAuthConsent'
), required_columns AS (
  SELECT '_System_ConnectedApp' AS table_name, 'ConsumerSecretHash' AS column_name UNION ALL
  SELECT '_System_ConnectedApp','RedirectUris' UNION ALL
  SELECT '_System_ConnectedApp','ClientType' UNION ALL
  SELECT '_System_ConnectedApp','RequirePkce' UNION ALL
  SELECT '_System_ConnectedApp','TokenEndpointAuthMethod' UNION ALL
  SELECT '_System_ConnectedApp','AllowedGrantTypes' UNION ALL
  SELECT '_System_ConnectedApp','ConsentPolicy' UNION ALL
  SELECT '_System_ConnectedApp','ConsentVersion' UNION ALL
  SELECT '_System_ConnectedApp','IssueRefreshTokens' UNION ALL
  SELECT '_System_ConnectedApp','PublisherName' UNION ALL
  SELECT '_System_ConnectedApp','SupportUrl' UNION ALL
  SELECT '_System_ConnectedApp','SessionIssuerClass' UNION ALL
  SELECT '_System_ConnectedAppAccess','ConnectedAppId' UNION ALL
  SELECT '_System_ConnectedAppAccess','ProfileId' UNION ALL
  SELECT '_System_ConnectedAppAccess','PermissionSetId' UNION ALL
  SELECT '_System_OAuthCustomScope','Name' UNION ALL
  SELECT '_System_OAuthCustomScope','CustomPermissionId' UNION ALL
  SELECT '_System_OAuthCustomScope','AllowedClientTypes' UNION ALL
  SELECT '_System_OAuthCustomScope','AllowedMethods' UNION ALL
  SELECT '_System_OAuthCustomScope','RoutePatterns' UNION ALL
  SELECT '_System_OAuthCustomScope','ResourceLinks' UNION ALL
  SELECT '_System_OAuthCustomScope','IsActive' UNION ALL
  SELECT '_System_OAuthCode','CodeHash' UNION ALL
  SELECT '_System_OAuthCode','CodeChallenge' UNION ALL
  SELECT '_System_OAuthCode','CodeChallengeMethod' UNION ALL
  SELECT '_System_OAuthCode','AuthTime' UNION ALL
  SELECT '_System_OAuthCode','Issuer' UNION ALL
  SELECT '_System_OAuthCode','ConsentId' UNION ALL
  SELECT '_System_OAuthCode','ClientTypeAtIssue' UNION ALL
  SELECT '_System_OAuthConsent','UserId' UNION ALL
  SELECT '_System_OAuthConsent','ConnectedAppId' UNION ALL
  SELECT '_System_OAuthConsent','Scope' UNION ALL
  SELECT '_System_OAuthConsent','ConsentVersion' UNION ALL
  SELECT '_System_OAuthConsent','ApprovedAt' UNION ALL
  SELECT '_System_OAuthConsent','LastUsedAt' UNION ALL
  SELECT '_System_OAuthConsent','RevokedAt' UNION ALL
  SELECT '_System_OAuthConsent','RevokedBy'
)
SELECT 'missing_table' AS check_name, rt.table_name, '' AS detail
FROM required_tables rt
LEFT JOIN information_schema.tables t
  ON t.table_schema = DATABASE() AND t.table_name = rt.table_name
WHERE t.table_name IS NULL
UNION ALL
SELECT 'missing_column' AS check_name, rc.table_name, rc.column_name AS detail
FROM required_columns rc
LEFT JOIN information_schema.columns c
  ON c.table_schema = DATABASE()
 AND c.table_name = rc.table_name
 AND c.column_name = rc.column_name
WHERE c.column_name IS NULL
ORDER BY check_name, table_name, detail;
```

The OAuth custom scope row should exist after final-pack convergence:

```sql
SELECT
  Name,
  IsActive,
  AllowedClientTypes,
  AllowedMethods,
  RoutePatterns,
  ResourceLinks
FROM _System_OAuthCustomScope
WHERE Name = 'external.records.read';
```

Expected row:

- `Name = external.records.read`
- `IsActive = 1`
- `AllowedClientTypes` includes `public`
- `AllowedMethods` includes `GET` and `HEAD`
- `RoutePatterns` includes `/services/data/:version/query`, `/services/data/:version/queryAll`, sobject describe/read routes, and identity routes
- `ResourceLinks` includes `sobjects`, `query`, `queryAll`, `identity`

Pack verification should pass:

```bash
go run ./cmd/setup validate-pack --source "local_path:${FINAL_PACK_PATH}"
go run ./cmd/setup verify-pack salesforce-crm-standard
```

## Functional Smoke Test

After schema, pack, and env flag are converged, perform one public-client PKCE smoke. This requires an admin/operator credential and should be run against the candidate backend before traffic cutover if possible.

1. Create a public Connected App with:

   - `ClientType=public`
   - `RequirePkce=true` or omitted if the current engine auto-defaults public apps to PKCE
   - redirect URI owned by the test client
   - scope `external.records.read`
   - active

2. Start `/services/oauth2/authorize` with:

   - `response_type=code`
   - `code_challenge_method=S256`
   - a valid `code_challenge`
   - `scope=external.records.read`

3. Exchange the code at `/services/oauth2/token` with:

   - `grant_type=authorization_code`
   - `client_id`
   - `redirect_uri`
   - `code`
   - `code_verifier`
   - no `client_secret`

4. Expected:

   - token exchange returns `200`
   - token scope is `external.records.read`
   - `GET /services/data/v63.0/query?q=SELECT+Id+FROM+Account+LIMIT+1` returns `200`
   - `POST /services/data/v63.0/sobjects/Account` returns `403` with `INSUFFICIENT_SCOPE`
   - repeating token exchange without `code_verifier` returns `400 invalid_grant`

## Rollback

- If readiness fails before traffic switch, keep the candidate slot out of rotation and keep `NEXUSCRM_PUBLIC_OAUTH_PKCE_ENABLED` disabled for any active old runtime.
- If traffic was switched and OAuth regressions appear, roll back backend runtime and final-pack release together.
- Do not drop the newly added OAuth tables/columns during rollback. They are forward-compatible substrate and should remain unless the DB restore point is explicitly used.
- Existing confidential Connected Apps should remain confidential and do not need to be mass-edited for rollback.

## Acceptance Checklist

- [ ] Engine runtime includes upstream PKCE code/schema.
- [ ] Final pack includes `oauth_custom_scope external.records.read`.
- [ ] Prod DB schema readiness query returns no rows.
- [ ] `NEXUSCRM_PUBLIC_OAUTH_PKCE_ENABLED=true` is present in candidate backend env.
- [ ] `_System_OAuthCustomScope.external.records.read` exists and is active.
- [ ] `verify-pack salesforce-crm-standard` passes.
- [ ] Public-client PKCE smoke passes without `client_secret`.
- [ ] Scoped read works and scoped write is blocked.
- [ ] Traffic switched only after readiness and smoke pass.