Database

Nuxfire uses Postgres as its primary database, accessed from Cloudflare Workers through Hyperdrive, with Drizzle ORM.

Database Architecture

1. Schema Definition

Table definitions are split across apps/functions/src/database/core-schema.ts and each layer's own server/database/schema.ts, all using Drizzle's Postgres dialect (pgTable, pgEnum). apps/functions/src/database/schema.ts is not where tables are defined — it's a hand-maintained aggregator that re-exports all of them (export * from "./core-schema" plus one line per layer) behind the single import path @nuxfire/functions/schema:

• core-schema.ts — Users, Jobs, Logs, Dead Letter Records, Waitlist Emails.
• layers/teams/server/database/schema.ts — Teams, Team Memberships, Team Invites, Roles, Permissions.
• layers/billing/server/database/schema.ts — Plans, Benefits, BenefitsOnPlan, Subscriptions.
• layers/notifications/server/database/schema.ts — Notifications.
• layers/admin/server/database/schema.ts — Platform Admins, Audit Logs.

Turning a feature layer off (config.ts's layers.features) removes its pages/components from the Nuxt bundle, never its tables — schema.ts includes every layer's schema unconditionally. The four core layers (teams, billing, notifications, admin) aren't toggleable at all. Several of these files import back and forth across the aggregator/layer boundary (e.g. core-schema.ts imports teams from layers/teams for Job's foreign key, while layers/teams imports users/jobs back from core-schema.ts) — always through a package specifier (@nuxfire/layer-teams/...), never a relative path, since SST's own Worker bundler (used to deploy cron.ts/jobs-consumer.ts/dlq-consumer.ts) fails to resolve a relative path reaching outside its package.

2. Infrastructure Setup

Workers can't hold a direct TCP connection to Postgres, so the database is reached through Hyperdrive, configured in infra/database.ts:

export const database = new sst.cloudflare.Hyperdrive("AppDB", {
  origin: {
    scheme: "postgres",
    host: secret.DatabaseHost.value,
    port: secret.DatabasePort.value.apply(Number),
    database: secret.DatabaseName.value,
    user: secret.DatabaseUser.value,
    password: secret.DatabasePassword.value,
  },
});

Hyperdrive pools and caches the connection; the app always speaks plain Postgres wire protocol, so the origin is swappable — Supabase, RDS, Neon, self-hosted — without touching application code.

Supabase connection mode: Cloudflare's official Hyperdrive documentation explicitly recommends using Supabase's Direct connection instead of pooled connection strings, because Hyperdrive already performs connection pooling at Cloudflare's edge (avoiding double-pooling). See Environment Variables — Use the Direct connection for host/user formatting and local migration IPv6 notes.

3. Database Connection

useDB() (apps/app/server/utils/db.ts) builds a drizzle-orm/postgres-js client from the Hyperdrive binding, scoped to the current request and never cached globally:

function createDB() {
  const sql = postgres(binding.connectionString, { max: 5, fetch_types: false });
  return drizzle(sql, { schema });
}

export function useDB() {
  const event = useEvent();
  const ctx = event.context as { _db?: DB };
  if (!ctx._db) ctx._db = createDB();
  return ctx._db;
}

This matters because a postgres.js client is an I/O object, and Workers tears down a request's I/O once that request ends — reusing a client built during one request inside a later one fails intermittently (Cloudflare documents this under "Cannot perform I/O on behalf of a different request"). Hyperdrive's own connection pooling already removes the cost of opening a fresh client per request, so there's no benefit to caching one across requests, only risk.

Database Usage

Example query using Drizzle's relational API:

const team = await useDB().query.teams.findFirst({
  where: or(eq(teams.id, teamIdentifier), eq(teams.slug, teamIdentifier)),
  with: {
    memberships: {
      with: {
        user: { columns: { id: true, name: true, email: true, image: true } },
        role: { columns: { id: true, name: true } },
      },
    },
  },
});

Database Migrations

Migrations are generated with Drizzle Kit from core-schema.ts and every layer's server/database/schema.ts — the exact list apps/functions/drizzle.config.ts declares under schema: [...] (dialect: "postgresql") — and committed to apps/functions/src/database/migrations/:

cd apps/functions
DATABASE_HOST=... DATABASE_PORT=5432 DATABASE_USER=... DATABASE_PASSWORD=... DATABASE_NAME=... \
  bunx drizzle-kit generate

Review the generated SQL before committing — Drizzle Kit diffs the schema files above against the last snapshot in migrations/meta/, but can't know intent (e.g. a column rename vs. a drop-and-add), so double-check anything destructive.

Deployment

Every deploy of the App Worker runs drizzle-kit migrate automatically, right after the Worker is built (see infra/utils/nuxt.ts), using the DatabaseHost/DatabasePort/DatabaseUser/DatabasePassword/DatabaseName secrets. There's no separate migration step to run by hand.

Rollback strategy

Drizzle Kit doesn't generate down-migrations — Postgres migrations here are append-only. The short version (full detail in apps/functions/src/database/MIGRATIONS.md):

• Additive changes (new table, nullable column, index) are safe to leave in place even if the deploy is rolled back.
• Destructive changes (drop column/table, new NOT NULL, type changes) ship in their own migration, deployed only after the application code that depended on the old shape has been live a while — the standard expand/contract pattern.
• A bad migration that reaches production gets undone with a new forward migration, not a revert.
• Take a manual backup before any migration that drops or alters data-bearing columns.