# MySQL Schema (ported)

Mirrors `src/lib/db/schema.ts` (SQLite source-of-truth). Used by:

- `npm run db:generate:mysql` — emits `drizzle/mysql/*.sql` migrations
- `npm run db:export-ddl` — concatenates migrations into `docs/ddl/mysql.sql`
- `buildMysqlClient()` in `../../client.mysql.ts` — runtime connection factory

## Why two schemas?

Drizzle ORM has dialect-specific table definitions. `sqliteTable` and
`mysqlTable` are not interchangeable because the column types differ:

| Drizzle SQLite | Drizzle MySQL |
|---|---|
| `text('email')` | `varchar('email', { length: 255 })` |
| `integer('age')` | `int('age')` |
| `integer('ts', { mode: 'timestamp_ms' })` | `bigint('ts', { mode: 'number' })` |
| `integer('flag', { mode: 'boolean' })` | `boolean('flag')` |
| `text('json_col', { mode: 'json' })` | `json('json_col')` |

A single shared schema isn't possible without either:

1. A code-generator that produces both from a single source (overkill for
   this scale)
2. A dialect-agnostic abstraction layer (would defeat Drizzle's type
   inference advantages)

So we keep both. Whenever the SQLite schema changes:

1. Mirror the change here.
2. `npm run db:generate:mysql` to emit the migration.
3. `npm run db:export-ddl` to refresh `docs/ddl/mysql.sql`.

## Length conventions used here

| Column kind | Length |
|---|---|
| UUID id columns | `varchar(36)` |
| email | `varchar(255)` |
| slug / key / enum-as-text | `varchar(64)` |
| Generic short labels (name, label, kind) | `varchar(16-255)` (case-by-case) |
| hash / token (sha256 hex) | `varchar(128)` |
| hex color | `varchar(16)` |
| URLs | `varchar(1024-2048)` |
| Long content (MJML / HTML / footer text) | `text` |
| JSON blobs | `json` (MySQL 5.7+) |

## Runtime-Switch Status

The Drizzle schema and the MySQL connection factory are both production-ready
today. `drizzle-kit generate/migrate` against MySQL work. What's NOT yet
ready is the **repository layer** (`src/lib/db/repositories.ts`,
`campaign-repo.ts`, …) — it contains a handful of SQLite-specific raw SQL
expressions (`unixepoch()`, JSON-extract syntax).

Until that layer is refactored to be driver-agnostic, `getDb()` only returns
the SQLite client. Production-MySQL switch requires:

1. Replace the few raw-SQL spots in the repos with Drizzle expression-builders
   (or driver-conditional helpers).
2. Set `DB_DRIVER=mysql` and `DATABASE_URL=mysql://…`.
3. Remove the throw in `client.ts` `buildClient()` for the mysql branch.

That refactor is tracked separately as "Repo-Layer dual-driver support".
