## 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