# Migrate data from the legacy mttx MySQL database into the mattraxe schema

The legacy production service (`mattrax/mttx`) stores tenants, accounts, devices, groups,
policies and APNs credentials in a MySQL schema that predates this repository. `mattraxe`
reuses the same database engine but has a substantially different schema (organisation
membership, Yielded Auth, DDM-aware devices, per-organisation APNs rows, a JWK-based SCEP
CA, and hashed platform-instance keys).

This issue defines the target mapping and the operator procedure. The companion PR adds a
best-effort, opt-in migration script and runbook; it is **not** wired into CI and must never
run automatically.

## Scope and assumptions

- Source is the current `mttx` schema, i.e. all migrations in
  `apps/mattrax/migrations` through `20260207091500_instance-id-bigint.sql` have been applied.
- Target is a freshly migrated `mattraxe` database (`pnpm db:migrate`).
- The old service is frozen while the final migration runs.
- IDs in `mttx` are CUID2 strings (12 chars) except `ca.id` and `instance.id`. IDs that are
  valid in the new `varchar(255)` primary keys are preserved, so device/profile URLs,
  enrollment links and references between migrated rows keep working.
- `mttx` has no audit log, invitation, SSO identity, user-group or subscription history to
  migrate. Those features start at cutover.

## Verified legacy facts

These were checked in the `mttx` source before designing the mapping:

- `tenant.id` **is** the owner's `account.id`. Registration inserts both rows with the same
  generated id (`crates/mx-api/src/v1/auth.rs`, `register` and `login_email_code`), and all
  tenant queries join `account a INNER JOIN tenant t ON a.id = t.id`. Therefore a tenant's
  owner is unambiguous: `tenant.id = account.id`.
- `tenant.email` is a tenant **contact** email, not the owner's login email. The login email
  is `account.email`. `tenant.email` has no column in the new schema, so it is preserved in
  `organization.metadata`.
- `account.password` is a `password_auth` Argon2 PHC hash. `mttx` sessions are opaque refresh
  tokens in `session`, encrypted with the legacy `SECRET`. Neither is portable to Yielded Auth.
- `mttx` APNs private keys are ChaCha20-Poly1305 ciphertexts (12-byte nonce prefix) keyed by
  the legacy 32-byte `SECRET`; certificates are DER. New APNs keys are AES-256-GCM payloads
  keyed by `SHA-256(AUTH_SECRET)`; certificates are PEM.
- The `mttx` SCEP CA is an RSA-4096 self-signed CA stored as DER certificate + PKCS#8 DER key,
  valid for 12 years. The new SCEP CA is a single row (`scep_ca.id = 1`) with a PEM
  certificate and a private-key JWK.
- Current `mttx` configuration kinds are already `mttx.apple.device`,
  `mttx.apple.custom.<id>`, `mttx.apple.certificate.<id>` and
  `mttx.apple.declaration.<id>`. New Windows configurations are
  `mttx.windows.custom.<id>`. Very old rows may predate this naming and will be reported, not
  silently rewritten.
- `mttx` has no `invitation` table. Pending invitations cannot be migrated because they do
  not exist in the source.

## Table-by-table mapping

| Legacy `mttx` | New `mattraxe` | ID handling | Notes |
| --- | --- | --- | --- |
| `tenant` | `organization` | **Preserve** `tenant.id` → `organization.id` | `name` → `name`; derive a unique `slug`; `email` → `organization.metadata.mttxContactEmail`; `stripe_customer_id` → `stripe_customer_id`; `created`/`modified` → timestamps. |
| `tenant` (owner) | `member` | New deterministic id | `member.organization_id = tenant.id`, `member.user_id = tenant.id`, `role = 'owner'`. The owner relationship is derived from `tenant.id = account.id`. |
| `tenant` (enrollment columns) | `enterprise_enrollment` | **Preserve** `enroll_id` → `enterprise_enrollment.id` | Keeps existing `/enroll/<id>` links working. `enroll_code` → `code`; `enroll_max_devices` → `max_devices`; `enroll_enabled_apple/windows/android/asset_tag` → matching flags. |
| `account` | `user` | **Preserve** `account.id` → `user.id` | `name`, `email`; `verified` → `email_verified`; `disabled` → `disabled`; `created`/`modified` → timestamps. `marketing_email` has no new column (dropped; see decisions). |
| `account` (login identity) | `auth_identifiers` + `auth_credentials` + `auth_emailCredentials` | New rows keyed by preserved user id | Email identifier in namespace `email`; one active email credential per user. **No `auth_passwords` row is created.** |
| `session` | — | — | Dropped. Refresh tokens are legacy-encrypted and not valid Yielded Auth sessions. Every user is signed out at cutover. |
| `email_code` | — | — | Dropped. Codes are short-lived and purpose-bound; users request a fresh code. |
| `device` | `device` | **Preserve** `device.id` | `tenant` → `organization_id`; `proto`, `status`, `name`, `model`, `serial`, `created` → `enrolled_at`, `last_seen` → `last_seen_at`, `notes`, asset/physical/vendor/OS/disk fields. `imei`, `meid`, `unenrolled` are not in the new schema (see decisions). |
| `device_apple.udid` | `device.dm_identifier` | — | Same value as legacy `device.dm_identifier`, now stored on `device`. |
| `device_apple.cert_hash` | `device.dm_cert_hash` | — | Binary SHA-256 (32 bytes) → lowercase hex `varchar`. The new server hashes the identity-certificate string it receives, so this value is informational and is recomputed on the next MDM `Authenticate`. |
| `device_apple.model_identifier` | `device.model_identifier` | — | Moved from the Apple extension table to the core device row. |
| `device_apple` | `device_apple` | **Preserve** `device.id` → `device_apple.device_id` | `enrollment_id` → `enrollment_id`; `topic`, `push_magic`, `configured`, `device_token`, `token_updated` → `token_updated_at`. Binary `token` (raw APNs push token) → **hex text**. Binary `unlock_token` and `bootstrap_token` → **base64 text** (matching the new plist string representation). |
| `device_user` | `device_apple_user` | New row keyed by (`device_id`, `user_id`) | `device` → `device_id`; `id` (Apple `UserID`) → `user_id`; `token` binary → hex text; `push_magic`, `topic`; `token_updated`/`authenticated` → `updated_at`. `enrollment_id`, names, `enabled`, `configured` have no equivalent; disabled rows are skipped with a warning. |
| `group` | `enterprise_group` | **Preserve** `group.id` | `tenant` → `organization_id`; `name`, `notes`, timestamps. |
| `group_device` | `group_device` | Composite key preserved | Rows whose group or device was skipped are skipped and reported. |
| `group_policy` | `group_policy` | Composite key preserved | Group-to-policy assignment survives, so policy rollout behaviour is preserved. |
| `policy` | `policy` | **Preserve** `policy.id` | `tenant` → `organization_id`; `name`, `notes`, timestamps. |
| `configuration` | `configuration` | (`kind`, `policy_id`) preserved | `data` copied. `hash` is **recomputed** as `SHA-256(JSON.stringify(data))` because the new server uses that definition; the legacy Rust in-memory hash is not equivalent. Unknown kinds are copied and reported. |
| `apple_push_certs` | `apple_push_certificate` | — | One row **per organisation** (legacy topic is globally unique, but two tenants can share the same Apple topic). `topic`, `email` and `updated_at` copied; certificate DER → PEM. Private key requires an operator decision below. |
| `ca` | `scep_ca` | Always `id = 1` | Optional import only. Certificate DER → PEM; PKCS#8 DER → private-key JWK. Prefer generating a new CA. |
| — | `scep_issued_certificate` | — | **No legacy source.** `mttx` did not persist issued certificate serials, so historical renewals cannot be backfilled. |
| `instance` | `platform_instance` | **Preserve** numeric `id` as text | `key` is hashed: `key_hash = SHA-256(key)` hex, `key_prefix = key.slice(0, 13)`. `issued_by` → `issued_by_user_id` (skip when the user is gone); `user_count`, `device_count`, `created`/`updated_at`, `last_seen`, `revoked`. Existing instance keys keep authenticating, but the plaintext key can never be displayed again. |
| — | `user_group`, `user_group_member`, `sso_identity`, `sso_config`, `scim_token` | — | Out of scope; no legacy source data. |
| — | `invitation` | — | Out of scope; `mttx` had no invitations. |
| — | `audit_log` | — | Out of scope; `mttx` had no audit log. History begins at cutover. |
| — | `subscription` | — | Out of scope. Billing state must be re-established/verified with Stripe; the legacy `stripe_customer_id` is preserved on the organisation. |

## Authentication migration plan

### What is preserved

- User IDs and emails, so memberships, audit actor references and any external links stay
  stable.
- `email_verified` from `account.verified` and `disabled` from `account.disabled`.
- Email-code sign-in capability once the address is verified: an `auth_identifiers` row
  (`namespace = 'email'`, lowercase address), an active `auth_credentials` row, and an active
  `auth_emailCredentials` row with module id `mattrax/auth/email`.

### What is deliberately not preserved

- **Password hashes.** Legacy hashes come from the Rust `password_auth` crate (Argon2 PHC).
  Yielded Auth stores a versioned verifier in `auth_passwords` under its own contract. We have
  not proven that the Argon2 parameters, encoding and version metadata match, and importing a
  hash that later fails verification is worse than a clean reset. The migration script does
  not write `auth_passwords`.
- **Sessions.** Legacy refresh tokens in `session` are encrypted with the old `SECRET` and are
  not Yielded Auth session records. `auth_sessions` starts empty, so all users are signed out.
- **Email verification codes.** `email_code` is transient and is dropped.

### Operator flow

1. Migrate users and auth identifiers.
2. Send a password-reset / "set your password" email to every migrated user with a legacy
   password, or direct them to email-code sign-in.
3. Users who never had a password (legacy `password IS NULL`) already use email-code sign-in;
   they need no reset email.
4. Users whose legacy `verified = false` keep a NULL `verified_at`; email-code sign-in rejects
   unverified identifiers, so they must complete verification (or an operator must verify the
   address) before signing in.
5. Users with `disabled = true` remain disabled. Note the 2026 legacy migration marked every
   passwordless account as disabled, so a number of otherwise valid users will need to be
   re-enabled deliberately.

### Ownership

`tenant.id = account.id` means each legacy tenant maps to exactly one owner membership. There
are no legacy team members to migrate. Any account that exists without a tenant is migrated as
a user but left without an organisation; this is reported for manual resolution rather than
inventing a tenant.

## Device migration details

- `token`, `cert_hash` and `device_token` need explicit conversion; do not let the driver
  coerce `VARBINARY`/`BINARY` to a locale-dependent string.
  - `cert_hash BINARY(32)` → lowercase hex.
  - `token VARBINARY` (APNs push token) → lowercase hex, matching the new
    `base64ToHex` normalisation.
  - `unlock_token` / `bootstrap_token` `VARBINARY` → base64 text, matching the new plist
    string representation.
- `device_apple.device_token` is preserved because it appears in profile/asset URLs.
- `dm_cert_hash` is copied as the hex of the legacy DER hash, but the new server computes
  SHA-256 over the PEM identity certificate and overwrites the value on the next
  `Authenticate`. Treat the migrated value as informational; the device lookup itself uses
  `dm_identifier` (UDID) scoped to the organisation.
- Windows and Android device rows can be copied, but the new backend only has a Windows
  preview and no Android management backend. Decide whether to migrate or exclude them.
- Disabled `device_user` rows are skipped because the new table has no `enabled` column; a
  later check-out removes them anyway.

## Policy migration details

- `policy` and `configuration` copy one-to-one with IDs preserved, and `group_policy` links are
  retained so existing rollout targeting is unchanged.
- Current `mttx` kinds already use the `mttx.apple.*` namespace. Older databases may contain
  pre-v3 kinds; the script reports unknown kinds rather than guessing a rewrite. Any reported
  kind must be rewritten to `mttx.apple.device`, `mttx.apple.custom.<id>`,
  `mttx.apple.certificate.<id>`, `mttx.apple.declaration.<id>` or
  `mttx.windows.custom.<id>` before devices will receive that configuration.
- Configuration hashes are recomputed. Declaration identifiers also changed (`policyId:kind`
  instead of `policyId.kind`), so the first DDM sync after cutover may re-issue declarations
  even when payloads are unchanged.

## APNs certificate migration

Keys and certificates cannot be copied as-is:

- Legacy: certificate DER, private key PKCS#8 DER encrypted with ChaCha20-Poly1305 using the
  old 32-byte `SECRET` (payload is `nonce || ciphertext || tag`).
- New: certificate PEM, private key JWK sealed with AES-256-GCM under
  `SHA-256(AUTH_SECRET)` (payload is base64 of `iv || ciphertext || tag`).

Recommended options, in order:

1. **Re-upload the `.p12`/certificate and key through the new UI.** This is the simplest and
   safest path; the Apple topic must stay the same or devices must re-enroll.
2. **Controlled offline re-seal.** With the old `SECRET` and the new `AUTH_SECRET` available,
   decrypt each legacy key, convert PKCS#8 DER to JWK, and re-seal with the new scheme. The
   script exposes this as an explicit opt-in and refuses to run unless both secrets are set.
   The re-seal must happen on a trusted operator machine; secrets must never be committed or
   pasted into tickets.

`topic` and `email` can always be copied. If two legacy tenants shared one Apple topic, create
one `apple_push_certificate` row per organisation.

## SCEP CA migration

- **Recommended: generate a new CA and re-enroll devices.** New devices get certificates from
  the new CA. This avoids importing long-lived key material and avoids depending on the old
  CA's 12-year validity.
- **Only if device continuity is mandatory: import the legacy CA.** Convert the DER
  certificate to PEM and the PKCS#8 DER key to a JWK, then insert `scep_ca` row `id = 1`.
  Risks:
  - The new renewal path checks `scep_issued_certificate`, which has no legacy history. Renewals
    for certificates issued by the old server will fail until devices re-enroll or records are
    backfilled manually.
  - Any change to the CA chain requires a new enrollment profile; devices cannot silently trust
    a different CA.
  - Importing the private key increases blast radius: it is the same key the legacy service
    held and may have been handled outside current secret-management controls.

## Enrollment links, instances and invitations

- **Enrollment links/passwords:** `enterprise_enrollment.id` is set from `tenant.enroll_id`, so
  existing `/enroll/<id>` links keep resolving. `enroll_code` becomes the new plaintext `code`.
  Resetting the enrollment link in the new UI generates a new id, which is expected.
- **Instances/product keys:** `instance.key` is hashed into `platform_instance.key_hash`;
  existing self-hosted installations continue to authenticate. The legacy plaintext key is not
  recoverable from the new database, so operators must keep their existing copies.
- **Invitations:** none exist in `mttx`. Any outstanding invitation must be re-created in the
  new UI after cutover.

## Cutover procedure

1. **Freeze writes.** Put the legacy service into maintenance mode (or stop it). Do not run the
   migration against a live writer.
2. **Back up.** Snapshot the legacy database and take a fresh target backup. Record row counts
   and checksums for every table.
3. **Dry run.** Restore both databases into staging and run the script with `--dry-run`.
   Resolve every skipped row and unknown policy kind; a dry run executes all writes inside a
   transaction and rolls back.
4. **Rehearse.** Time the run against a production-sized copy, including APNs/SCEP decisions.
5. **Final run.** Run with `--yes` against the real target. The script wraps all writes in a
   single transaction and refuses to run without the confirmation flag.
6. **Validate.** Compare per-table counts and per-organisation device counts, spot-check
   memberships, devices, group links, policies and enrollment links. Run the validation queries
   in the runbook.
7. **Decide APNs/SCEP.** Re-upload APNs credentials or complete the controlled re-seal; generate
   a new SCEP CA or import the old one. Do this *before* switching traffic so pushes and
   enrollment work immediately.
8. **Switch DNS.** Lower TTLs beforehand, then point the hostname at the new service.
9. **Monitor.** Watch sign-in failures, MDM check-ins, push errors and enrollment attempts. Send
   the password-reset communication.
10. **Rollback.** If validation fails before DNS switch, roll back the target transaction (or
    restore the target backup) and keep the legacy service frozen. If failure happens after DNS
    switch, revert DNS and keep the legacy database and its backups untouched; any writes made
    in the new system after cutover would need reconciliation. Do not delete the legacy database
    until the agreed retention window ends.

## Security and communications

- Never log or commit `MTTX_DATABASE_URL`, `TARGET_DATABASE_URL`, `MTTX_SECRET`,
  `AUTH_SECRET`, APNs keys/certificates, SCEP keys, password hashes, legacy sessions or
  instance keys. The script redacts DSNs and reports counts only.
- Restrict access to backups and the migration host. Use TLS for remote database connections.
- Password reset emails must come from the new domain with SPF/DKIM/DMARC configured; expect
  support load and phishing suspicion after the domain change.
- Do not present `audit_log` as complete history: it starts at cutover. If compliance requires
  pre-cutover history, export the legacy database records separately before decommissioning.
- Migrated users keep their old IDs; make sure any external systems that reference them are
  aware that sessions and passwords changed.

## Known unknowns / decisions needed

1. **Password compatibility:** do we require a reset for everyone (recommended) or fund a spike
   to prove `password_auth` Argon2 output can be re-encoded into Yielded Auth verifiers without
   weakening policy? Until proven, no `auth_passwords` rows are written.
2. **APNs key path:** re-upload (recommended) or controlled re-seal? The latter requires access
   to the retired `SECRET`; confirm it is still available and handled securely.
3. **SCEP continuity:** new CA + re-enrollment (recommended) or import the legacy CA and accept
   renewal failures until devices re-enroll? This is a product/support decision, not just a
   technical one.
4. **Unverified legacy accounts:** leave them unverified (email-code sign-in blocked until
   verification) or have support verify them manually?
5. **Disabled accounts:** the legacy 2026 migration disabled passwordless accounts. Do we
   re-enable the ones that were only disabled for that reason, and how do we identify them?
6. **Orphan accounts/tenants:** how should an account without a tenant, or a tenant without an
   account, be resolved? The script reports but does not invent ownership.
7. **Slug policy:** slugs are derived from tenant names with a collision suffix. Do we need a
   mapping review/approval, or reserved slugs for existing customers?
8. **Windows/Android devices:** migrate them as inert rows or exclude them until those
   backends are usable?
9. **Legacy fields without a home:** `account.marketing_email`, `tenant.android_enterprise_id`,
   `device.imei`, `device.meid`, `device.unenrolled`, `device_user` names/enabled flags. The
   script preserves contact email and Android enterprise id in organisation metadata; the rest
   are dropped and should be exported if needed.
10. **Shared APNs topics:** confirm whether multiple tenants used the same Apple topic; each
    organisation needs its own row.
11. **Timezone assumptions:** legacy `DATETIME` values are treated as UTC. Confirm the legacy
    server wrote UTC (sqlx `NaiveDateTime`) and that the target connection uses UTC.
12. **Rerun policy:** the script is additive/upsert-only and does not delete target rows. Decide
    whether reruns after cutover writes begin are allowed (they can restore stale source values).

## Out of scope

Everything below is intentionally out of scope for the first migration and starts fresh in
`mattraxe`:

- `user_group`, `user_group_member`, `sso_identity`, `sso_config`, `scim_token`
- `invitation`
- `audit_log` history
- `subscription` state and Stripe reconciliation
- `device_declaration`, `device_command` operational state (devices will re-report)
- `auth_sessions`, `auth_pending`, `auth_proofs`
- DDM declaration server tokens (recomputed by the new service)

<sub>Generated on _MrBeast_ using _opencode-go/deepseek-v4.1-flash_ in _ses_e3fe30b56ffdLHOi1nbsDAq8jw_</sub>