Decide host-cloudflare Postgres migration strategy: runtime ensure vs out-of-band drizzle-kit

[amondnet]

Summary

Decide (and optionally implement) the schema-migration strategy for
apps/host-cloudflare's opt-in Postgres seam: keep the current runtime
ensure bring-up, or adopt out-of-band drizzle-kit migrations like
apps/cloud.

Follow-up to #10, which made the host-cloudflare db seam configurable (D1
default, Neon/Postgres-over-Hyperdrive opt-in). #10 intentionally deferred this
decision and shipped with runtime ensure.

Background

The Postgres seam currently brings the schema up at runtime via
ensureDrizzleRuntimeSchemaFromTables (idempotent CREATE TABLE IF NOT EXISTS

• nullable ADD COLUMN), done once per isolate at boot. This preserves the
  template's zero-setup, self-provisioning ethos and is consistent with
  host-selfhost and apps/local. apps/cloud is the outlier: it uses generated
  drizzle-kit SQL migrations applied out-of-band (scripts/migrate.ts), because
  it is the multi-tenant production app where migration history and non-additive
  changes matter.

Decision

• Keep runtime ensure (status quo), OR
• Adopt out-of-band drizzle-kit migrations for the Postgres path, OR
• Make it configurable

Options & trade-offs

Runtime ensure (current)

• Zero deploy step, auto-provisioning; matches host-selfhost/local.
• Cannot express non-additive changes: column rename / type change / drop,
  non-null column + backfill, data transforms. No migration history.
• DDL runs at first-boot per isolate (idempotent, cheap, but implicit).

Out-of-band drizzle-kit (cloud-style)

• Full migration history; supports non-additive changes and data migrations;
  reviewable SQL diffs.
• Adds a separate db:migrate deploy step (breaks the one-command deploy), a
  static executor-schema.ts that must be hand-synced, a drizzle/ SQL folder,
  drizzle.config.ts, and scripts/migrate.ts (all cloneable from apps/cloud).

Recommendation

Keep runtime ensure for now: it fits a single-tenant, low/spiky template and
stays consistent with the other self-host apps. Revisit and adopt the
cloud-style out-of-band path the first time a non-additive schema change is
needed (the code is a near-verbatim clone of apps/cloud).

Tasks

• Make the decision above
• If adopting out-of-band: add drizzle.config.ts, static
  executor-schema.ts (pg), drizzle/ migrations, scripts/migrate.ts,
  and a deploy/CI migrate step; document it in README.md
• Either way: document the chosen strategy and its limits in the
  host-cloudflare README

Related / also deferred from #10

• Validate the Postgres path against a live Neon instance via a real
  Hyperdrive binding (current coverage uses local PGlite over a socket on the
  real workerd runtime).
• D1 -> Postgres data migration tooling for operators moving existing data
  (a fresh Postgres database starts empty; explicitly out of scope in Evaluate Neon/PlanetScale + Hyperdrive for apps/host-cloudflare db seam (vs D1) #10).

References

• Evaluate Neon/PlanetScale + Hyperdrive for apps/host-cloudflare db seam (vs D1) #10 (parent: configurable db seam)
• apps/host-cloudflare/src/db/postgres.ts, apps/host-cloudflare/src/db/index.ts
• apps/cloud/src/db/db.ts, apps/cloud/scripts/migrate.ts,
  apps/cloud/drizzle.config.ts
• packages/core/fumadb/src/adapters/drizzle/runtime.ts
  (ensureDrizzleRuntimeSchemaFromTables)

---

• To see the specific tasks where the Asana app for GitHub is being used, see below:
  • https://app.asana.com/0/0/1216053463815341