## Summary

This PR migrates the Mattrax Cloud API from PostgreSQL to MySQL.

The application schema, Drizzle runtime, Hyperdrive wiring, query rewrites, and migrations are implemented. The branch typechecks, tests, builds, and generates MySQL DDL.

**Important:** this PR currently depends on two local `pnpm` patches to Yielded Auth because its MySQL adapter does not yet expose the composed `AuthPersistence` facade. It has also not been run against a live MySQL/Hyperdrive instance.

## What changed

- **Schema** — `packages/enterprise-core/src/schema.ts` converted from `drizzle-orm/pg-core` to `drizzle-orm/mysql-core`:
  - indexed `text` → bounded `varchar` (MySQL cannot index `TEXT` without a prefix),
  - `jsonb` → `json`, `serial` → `AUTO_INCREMENT`,
  - `timestamp` → `datetime(3)` (avoids the 2038 limit and implicit `ON UPDATE`), with UTC pool timezone,
  - removed the `gen_random_uuid()` default (MySQL rejects non-deterministic defaults; provisioning already supplies the UUID).
- **Auth schema** — generated as MySQL tables via `AuthPersistenceConfig.managed(...)` using the patched adapter.
- **Runtime** — `drizzle-orm/effect-postgres` → `drizzle-orm/effect-mysql2`; `@effect/sql-pg`/`pg` → `@effect/sql-mysql2`/`mysql2`.
- **Hyperdrive** — `MysqlClient.layer` uses the individual `host`/`port`/`user`/`password`/`database` fields, `poolConfig: { disableEval: true, timezone: "Z" }` and `disablePreparedStatements: true`. No `url`: `@effect/sql-mysql2` ignores `poolConfig` when `url` is set, and Hyperdrive rejects `COM_STMT_PREPARE`.
- **Queries** — `onConflictDoNothing` → `insert().ignore()`, `onConflictDoUpdate` → `onDuplicateKeyUpdate`, `DELETE ... RETURNING` → tenant-scoped `findFirst` + `delete`.
- **Migrations** — Postgres migration replaced with generated MySQL DDL. No data migration.

## Validation

```text
pnpm install                                   pass
pnpm typecheck                                 pass
pnpm test                                      pass (22 files, 185 tests)
pnpm --filter @mattrax/web build               pass
pnpm --filter @mattrax/api build               pass
pnpm --filter @mattrax/api db:generate         pass
```

There was no MySQL server available in the environment, so the DDL and runtime path were **not executed**. Green checks are type/build/codegen only.

## Detailed migration notes

### Primary blocker: no composed MySQL auth adapter

`@yielded/auth-persistence-drizzle@0.1.0-beta.47` ships `/Mysql2`, but unlike `/Postgres` and `/SqliteBun` it does **not** export `AuthPersistence`:

- It exports explicit factories such as `makePasswordPersistenceServices`, `makeProofPersistenceServices`, `makeAuthenticationAuthorityServices`, `makePendingAuthenticationServices`, `makeStatefulSessionServices`, `makeSessionCleanupServices`, and identity/OAuth/passkey helpers.
- `@yielded/auth-persistence`’s composed layer only recognizes `pg` and `sqlite` in its `onDialectOrElse` check and returns “Use an explicit adapter for this SQL dialect” for MySQL.

Mattrax’s auth is written against the composed API:

```ts
AuthPersistence.make(AuthDefinition).managed(...)
AuthPersistenceConfig.layer
AuthPersistenceConfig.Config
AuthPersistenceConfig.Provisioning
```

That cannot be expressed with the explicit MySQL factories without hand-wiring the full service graph and tables those factories expect. That would mean defining MySQL physical tables for every generated auth role, building every typed mapping, reproducing transaction/coordinator ownership, and keeping subject/credential/session semantics identical to Postgres. This belongs upstream, not in Mattrax.

### Workaround in this PR

Two local `pnpm` patches make the composed path work:

1. `patches/@yielded__auth-persistence@0.1.0-beta.47.patch`
   - Adds `mysql: () => "mysql"` to the composed layer’s dialect detection.
2. `patches/@yielded__auth-persistence-drizzle@0.1.0-beta.47.patch`
   - Adds a MySQL `Backend` for `createPersistence(...)`.
   - Exports `AuthPersistence` from `/Mysql2`, including `migrationsLayer` using `drizzle-orm/effect-mysql2/migrator`.
   - Maps generated storage columns:
     - keyed `text` → `varchar` (`value` = 320, other ids = 191) under the 3072-byte InnoDB key limit,
     - non-keyed `text` → `text`,
     - managed `integer` → `bigint` (`mode: "number"`) because auth stores epoch milliseconds,
     - `boolean` → `boolean` (`tinyint(1)`).

These patches are a proposal for the upstream change, not a final solution. They still need upstream review for:

- **Transaction/isolation ownership.** The composed layer falls back to `sql.withTransaction` (MySQL default `REPEATABLE READ`), while the explicit MySQL adapters use `mysqlNativeCommit` (reserved connection, one-shot `READ COMMITTED`). This needs concurrency validation or a native-commit hook.
- **Runtime storage validation.** `validateStorageBatch("mysql", ...)` must pass against a live MySQL 8 schema, including index lengths/types.
- **Future strategy key lengths.** OAuth/passkey composite keys can exceed MySQL’s 3072-byte limit. Those strategies are not generated today but need a key-hashing design before MySQL can be a general adapter.

### MySQL-specific caveats

- `text` primary/unique/index keys → `varchar` (MySQL cannot index `TEXT` without a prefix). Application tables use `varchar(255)`; emails use `varchar(320)`; auth generated tables use 191/320.
- `timestamp(..., { mode: "date" })` → `datetime(..., { mode: "date", fsp: 3 })`. MySQL `TIMESTAMP` is limited to 1970–2038 and has implicit auto-update semantics; `DATETIME(3)` preserves milliseconds and avoids both. `timezone: "Z"` keeps reads/writes UTC.
- `jsonb` → `json` (`configuration.data`, `scep_ca.private_key_jwk`).
- `serial` → `int().autoincrement()` (`scep_ca.id`). An explicit `id: 1` insert still works.
- `gen_random_uuid()::text` removed from `user.auth_revision`; provisioning supplies the UUID. Future direct `user` inserts must set `authRevision`.
- `onConflictDoNothing()` → `.ignore()` **before** `.values()`. `INSERT IGNORE` also suppresses other ignorable errors, not just duplicate keys.
- `onConflictDoUpdate({ target, set })` → `.onDuplicateKeyUpdate({ set })`; MySQL infers the conflict target.
- `DELETE ... RETURNING` → tenant-scoped `findFirst` then `delete`; this adds a read and is not atomic. If atomicity matters, use the mysql2 affected-row count.
- `url` must not be used for the MySQL client: `@effect/sql-mysql2` ignores `poolConfig` when `url` is set, silently dropping `disableEval`. `disablePreparedStatements` is required because Hyperdrive rejects `COM_STMT_PREPARE`.

### Files affected

- `packages/enterprise-core/src/schema.ts` — MySQL schema, generated auth tables
- `packages/enterprise-core/src/db.ts` — `drizzle-orm/effect-mysql2`
- `packages/enterprise-core/package.json`, `packages/enterprise-backend/package.json`, `apps/api/package.json`, `pnpm-workspace.yaml` — driver dependencies
- `packages/enterprise-backend/src/enterpriseHandlers.ts` — conflict/returning rewrites
- `packages/management-backend-enterprise/src/index.ts`, `packages/management-backend-enterprise/src/scep.ts` — upsert rewrites
- `packages/enterprise-backend/tests/helpers.ts` — fake DB supports `.ignore()`
- `apps/api/src/index.ts` — MySQL connection layer
- `apps/api/cloudflare.config.ts` — local Hyperdrive dev URL
- `apps/api/drizzle.config.ts` — MySQL dialect/credentials
- `apps/api/migrations/20261109090419_mushy_vengeance/` — generated MySQL DDL
- `patches/` — the two upstream patches described above
- `README.md` — migration command

### Effort estimate

| Work | Estimate |
| --- | --- |
| Upstream: add composed MySQL `AuthPersistence` + dialect gate + tests | 1–2 days |
| Upstream: decide/implement MySQL transaction ownership and key-length strategy | 1–2 days |
| This repo: drop patches, regenerate, validate on MySQL 8 + Hyperdrive | 0.5–1 day |
| Hand-wired alternative (explicit MySQL factories, no upstream change) | 3–5 days, high risk |
| Data migration Postgres → MySQL | 1–3 days depending on volume |
| Concurrency/auth regression suite on MySQL | 1–2 days |

### What would make this mergeable

1. `@yielded/auth-persistence-drizzle` publishes `/Mysql2` with the composed `AuthPersistence` (or Mattrax pins a reviewed fork).
2. The composed path uses the same MySQL transaction/isolation owner as the explicit adapters, or concurrency tests prove the `withTransaction` fallback is safe.
3. `validateStorageBatch("mysql", ...)` passes against the generated schema on a live MySQL 8 database, including index-length checks.
4. A real Postgres → MySQL data migration exists, or the rollout is explicitly a greenfield/parallel-run cutover.
5. Hyperdrive MySQL is exercised in a deployed preview (text protocol, no prepared statements, `disableEval`).

## Test plan

- [x] `pnpm typecheck`
- [x] `pnpm test`
- [x] `pnpm --filter @mattrax/web build`
- [x] `pnpm --filter @mattrax/api build`
- [x] `pnpm --filter @mattrax/api db:generate`
- [ ] Apply the generated migration to MySQL 8
- [ ] Register, sign in, change password, and exercise session revocation against MySQL
- [ ] Deploy a preview against Hyperdrive MySQL with `disableEval` and text-protocol queries
- [ ] Decide whether to upstream the composed MySQL adapter or keep Postgres


## Local MySQL validation

Tested locally against **MySQL 9.7.2** using a `mattraxe` database and the `mattrax`/`mattrax` user. Applied the generated migration with `pnpm api db:migrate` and ran the dev server.

Verified through the web Worker proxy:

- register → `RegistrationAccepted`
- sign in → session cookie issued, Argon2id verifier stored
- `getSession` and `/me`
- create and list a tenant
- change password, old password rejected, new password accepted
- sign out, then `getSession` returns `null`
- duplicate registration does not create a second user or password
- API token creation and Bearer-token access to `/me`
- group and policy create/patch/delete, including MySQL upsert and delete paths
- password reset end-to-end:
  - request reset creates an `auth_proofs` row
  - the local `send_email` capture contains the reset link
  - the link fragment parses correctly
  - completing the reset replaces the password and invalidates sessions
  - signing in with the reset password works
- email change end-to-end:
  - request change sends the confirmation email
  - extracted token updates `user.email` and `auth_identifiers`
  - old email no longer signs in, new email does

Local email is captured in the Cloudflare Local Explorer under Email → Sending (`dev: { remote: false }` on the `EMAIL` binding).

### Regressions found and fixed in this PR

1. The web auth client sent raw action inputs. Yielded HTTP actions expect `{ payload: ... }`; login/register/password-reset requests returned `InvalidOperationInput`. Fixed in `apps/web/src/lib/auth.ts`.
2. `EnterpriseAuth` called `appAuth.verifySession` on the service class instead of the yielded service instance, so `/me` and tenant endpoints returned 500. Fixed by yielding `options.appAuth` in `packages/enterprise-backend/src/enterpriseAuth.ts`.
3. Local dev database defaults pointed at `mattrax`; the dev config and Drizzle defaults now use `mattraxe`.
4. Auth proof delivery used the request-scoped default scheduler, so reset/email-change mail could be interrupted when the per-request Auth layer closed. The API now supplies a `ProofDispatchScheduler` backed by `waitUntil`, keeping the Worker alive until the email is accepted.
5. The reset page stripped the leading `#` from the fragment, but `parseLinkFragment` requires it. Fixed in `apps/web/src/routes/reset-password.tsx`.

### Not covered locally

- Deployed Hyperdrive MySQL (text protocol, no prepared statements, `disableEval`) and MySQL 8-specific behaviour.
- A real Postgres → MySQL data migration.