# EDM migration — runbook Moving EDM templates off SendGrid's template store into GCS + `coredb`. SendGrid stays the **transport**; only the template store and the Handlebars merge move. Read this before running anything. `apps/core/.env` points at **production** (`core.db.karmagroup.com/coredb`), so every command below is explicit about what it touches. --- ## 0. What is already true Nothing in production behaves differently yet. Every switch is off by default: | Env var | Default | Effect when set | |---|---|---| | `EDM_STORAGE_MODE` | `gcs` | `sendgrid` → new/edited templates are stored in SendGrid, as before | | `EDM_STORAGE_DRIVER` | `gcs` | `local` → template bytes on the filesystem, no bucket needed | | `EDM_LOCAL_RENDER` | off | on → the SMTP worker renders locally instead of using `template_id` | | `EDM_SENDGRID_WRITES` | off | on → allows deleting SendGrid templates / sending through them | Until the migration runs, `edm_templates` does not exist and the console falls through to SendGrid for everything — which is the current behaviour. --- ## 1. Verify locally first You need a local Postgres. You do **not** need a GCS bucket. ```bash cd apps/core # Point at your local database (NOT the prod .env) export DATABASE_HOST=localhost export DATABASE_PORT=5432 export DATABASE_NAME=coredb_local export DATABASE_USER=postgres export DATABASE_PWD=postgres # Apply migrations pnpm migrate # or however migrations run in your setup # Renderer fixtures — no database, no network npx tsx src/cmd/check-edm-render.ts # Full end-to-end: rows, stored objects, publish, render EDM_STORAGE_DRIVER=local npx tsx src/cmd/smoke-edm.ts ``` `smoke-edm.ts` refuses to run unless `EDM_STORAGE_DRIVER=local` **and** `DATABASE_HOST` is a local address. It creates real rows and deletes them again. To run the console itself against local storage: ```bash EDM_STORAGE_DRIVER=local EDM_LOCAL_STORAGE_DIR=.edm-storage pnpm dev ``` Caveat: under the local driver, asset URLs are `file://` paths. Images will not render in the browser preview and would not render in an inbox — that is deliberate, so a local setup can never be mistaken for a working one. Set `EDM_ASSET_BASE_URL` if you want them served. --- ## 2. Apply the migration `20260809000000_create_edm_template_store` creates three tables and touches nothing existing: - `edm_folders` - `edm_templates` - `edm_template_versions` It is purely additive — no existing table is altered, no data is moved. `down()` drops all three (breaking the circular FK first). Run it wherever you run migrations. Confirm: ```sql select count(*) from edm_templates; -- 0 select count(*) from edm_folders; -- 0 ``` --- ## 3. Back-fill from SendGrid **Read-only against SendGrid.** The script does `GET /v3/templates` and `GET /v3/templates/{id}` and nothing else — it never creates, updates or deletes anything there. ```bash cd apps/core # Dry run. Reports what WOULD be mirrored. Writes nothing. npx tsx src/cmd/backfill-edm-to-gcs.ts # Small real run first npx tsx src/cmd/backfill-edm-to-gcs.ts --apply --limit 5 # Then the rest npx tsx src/cmd/backfill-edm-to-gcs.ts --apply ``` Re-runnable: a template whose active version already exists locally with a matching html hash is skipped, so an interrupted run resumes cleanly. **Each template keeps its existing `d-` id.** That is what makes this a zero-rewrite change — `promo_code.edm_template_id`, `signup_promo_code.verification_edm_template_id`, `curated_events.confirm_edm_template_id`, `member_offers.confirmation_edm_template_ids` and the ~70 hardcoded ids in the main core's `MAIL_TEMPLATES` all keep resolving. After it finishes, the console lists local templates first and falls through to SendGrid for anything not yet mirrored, so the list stays complete throughout. --- ## 4. Turn on local rendering (the actual cutover) This is the only step that changes what a member receives. **Prerequisites:** the SMTP worker must be able to reach console2 core, and needs `CONSOLE2_CORE_URL` + `INTERNAL_SERVER_API_KEY`. ```bash # workers/smtp CONSOLE2_CORE_URL=https:// INTERNAL_SERVER_API_KEY= EDM_LOCAL_RENDER=1 ``` The worker's fallback ladder means this degrades safely: ``` in-memory cache → console2 API → SendGrid template_id → nack to DLQ ``` A 404 from console2 means "not migrated, use SendGrid". An unreachable console2 throws and *also* falls back to SendGrid — but logs loudly, because "not ours" and "couldn't find out" must stay distinguishable. **Verify before trusting it.** Pick two or three low-traffic templates, send one test each through both paths, and diff what arrives. The fixture suite proves the renderer is self-consistent; it does **not** prove it matches SendGrid. ### Rolling back Unset `EDM_LOCAL_RENDER` and restart the worker. Every template still exists in SendGrid — the backfill only ever added to our store. There is nothing to restore. --- ## 5. Reclaiming quota (optional, later) Mirroring does **not** free SendGrid slots. What solves the cap is that *new* templates are created locally, so the count stops growing. Reclaiming the existing ~300–1000 slots means deleting SendGrid's copies, which is irreversible and currently blocked by `EDM_SENDGRID_WRITES` being off. Only consider it once local rendering has been stable for a long while, and never for the `MAIL_TEMPLATES` ids — those stay on SendGrid permanently by decision. --- ## Known gaps - **Parity with SendGrid is unproven.** `check-edm-render.ts` asserts documented behaviour, not observed. Residual risk is behavioural differences in helpers we *did* implement: loose `==`, numeric-string coercion, HTML-escape charset. - **`generate_plain_content` will drift.** SendGrid derives plain text server-side; we use `html-to-text`. Matters where the exact bytes are stored in Viewpoint logs via `retrieveTemplate`. - **Per-template SendGrid stats stop** for locally-rendered templates. Mitigated by `customArgs: { edm_template_id }` keeping attribution queryable in Activity. - **Unsubscribe tags are assumed to work** on raw HTML. `<%asm_group_unsubscribe_raw_url%>` is substituted by SendGrid's Subscription Tracking against the message body, so it should — but test it explicitly before a real send. ## Separately: a bug worth shipping on its own `workers/smtp` never called `channel.prefetch()` and did not await its handler, so RabbitMQ pushed the entire queue at once with unbounded concurrency. Fixed (`prefetch(25)`, awaited handler, failures nack to the DLQ instead of being acked). This is independent of the EDM work and can ship first. Note the DLQ change alters existing behaviour: messages that used to fail silently will now land in the dead-letter queue. That is correct, but expect traffic there that was previously invisible.