Design discussion: should email templates live in the database?

[pascal-klesse]

Background

This discussion grew out of the question "should email templates live in the database?" during the Hub Overhaul planning (see #83-#94). It's a design question that doesn't have an obvious right answer and benefits from input before any issue gets cut.

The three goals hiding behind the question

When someone says "templates in DB", they usually want one of three different things:

A. Marketing / Support edits copy without redeploy.
B. Per-tenant individualised templates.
C. Templates fully DB-driven, no .tsx files.

The argument shape for each is very different.

Where we land today

Templates are React-Email .tsx files under src/core/email/templates/ (core) and src/modules/email/templates/ (project copy-on-edit overlay). Each template exports a typed Vars interface and a meta factory. The Email Builder in the Hub already lets devs visually compose + save .tsx files via codegen. Sync-from-template only touches core; module overlays survive.

Arguments against moving full templates into the DB (i.e. against goal C)

1. Templates are a code contract, not content. password-reset.tsx exposes Vars { resetUrl: string }. The auth subsystem calls sendTemplate("password-reset", { resetUrl }). That contract is structurally code, not text. Putting it in DB swaps a typed interface for a string column with a convention.
2. TypeScript safety goes away. Editing a .tsx file today, the IDE flags {props.rsetUrl} instantly. A DB-stored template only fails at first send. New Vars fields can't be propagated by codemods.
3. Sandboxing raw JSX is non-trivial. Either a constrained DSL (= existing composer grammar — but invitation and new-device use raw JSX outside that grammar; they'd need rewriting or a "raw mode" fallback that reintroduces the sandbox problem), or arbitrary JSX in a string column with full AST validation + component allowlist.
4. Loss of git/PR review discipline. Changes go through Hub UI → DB row, with no git diff, no PR, no reviewer-before-prod. Audit log helps, but reviewer sees the change after it's live.
5. Sync-from-template + worst-case fallback get harder. Today: clean file sync. With DB: migration logic to distinguish "user customisation" from "old default". Plus DB outage = no mail; today the in-process templates mean mail keeps working as long as the Node process does.

Where DB-storage does make sense

Free / ad-hoc / marketing mails without a backend hook (newsletters, one-off announcements). They have no Vars contract, aren't security-critical wired, and operators want to author them anyway. That'd be its own system, sitting next to the transactional pipeline rather than replacing it.

Proposed direction (for goals A and B)

Keep transactional templates as code. Add a DB-backed override layer for i18n keys (parallel i18n integration is in flight — this layer assumes that wiring lands first).

I18nOverride {
  id          Uuid
  key         String   // e.g. "email.welcome.greeting"
  locale      String
  value       String
  updatedById Uuid?
  updatedAt   DateTime
  createdAt   DateTime
  @@unique([key, locale])
}

• Resolver chain: DB override (locale) → i18n file (locale) → i18n file (default locale) → key-as-fallback.
• Templates render normally; the i18n lookup t("email.welcome.greeting", vars) consults the chain.
• Hub editor: list keys, filter by namespace ("email.*"), edit with diff-vs-default, audit log.
• No tenant scope in v1 — global overrides only. Per-tenant overrides can be added later by extending the table with an optional tenantId column.

This addresses A (Marketing/Support edits copy without redeploy) without giving up TypeScript safety or PR-review discipline. B (per-tenant) becomes a forward-compatible follow-up.

What I'd like input on

• Are there use cases I'm missing where full DB templates would still beat the override-layer approach?
• Is the sync-from-template angle a real concern in practice for projects consuming this template, or am I overweighting it?
• Should we draw a hard line: "transactional templates always in code, DB only for non-hooked / marketing mails"? Or keep a path where DB-based transactionals are at least an opt-in?
• Anyone who has shipped a similar override-layer in production — what bit you?

Decision is not being made here yet

This is a design discussion. If consensus forms, we'll cut a focused issue ("Hub: global i18n key overrides with editor"). If a more compelling alternative emerges, we'll cut that instead.