---
name: rebase-backend-postgres
description: Guide for setting up and managing the Rebase PostgreSQL backend with Drizzle ORM. Use this skill when the user needs help with database setup, schema generation, migrations, connection pooling, read replicas, direct connections, Drizzle configuration, or backend initialization.
---

# Rebase PostgreSQL Backend

> **WARNING FOR AGENTS**: If you are writing a script or performing data tasks (e.g., seeding, migrating content), **default to using the Rebase SDK** (`@rebasepro/client` or `@rebasepro/server`). **NEVER** use `psql` or owner-level SQL (`rebase.sql()`) to manipulate application data unless specifically instructed to do so for low-level debugging: it circumvents schema validation, access controls, and lifecycle hooks. When the SDK cannot express a query, run it as the caller with `getSql(c)`, which keeps row-level security (see `rebase-custom-functions`).

Rebase uses PostgreSQL as its primary database, with Drizzle ORM for type-safe schema management and migrations.

## Architecture

```
Tables (backend/schema, TypeScript) → Drizzle Schema (generated) → PostgreSQL (database)
```

The backend uses a **two-step process**:
1. **`rebase schema generate`** compiles the tables in `backend/schema/` (`defineTable`, see `rebase-tables`) and generates a Drizzle ORM schema file (`schema.generated.ts`)
2. **`rebase db push`** or **`rebase db generate` + `rebase db migrate`** applies the schema to the database

The backend also applies the additive part at every boot, on any database: missing
tables, columns and enum types, and the tables' RLS policies
(`REBASE_MIGRATE_ON_BOOT=ensure`, the default; `none` opts out). It never drops,
renames or narrows — that is what step 2 is for.

On the way, `db push` and `db generate` render the tables to SQL in `.rebase/sql/`
beside the backend (`backend/.rebase/sql/` in a scaffolded project): `schema.sql` is
Atlas's desired state, and `policies.sql`, `search.sql`, `vector.sql` and `triggers.sql`
are applied by Rebase itself. Both commands rewrite all five from the tables on
every run, and the directory ignores itself — never edit or commit them. Migrations
are what you commit, in `drizzle/migrations/`.

## Prerequisites

- A PostgreSQL to run against — or none: with `DATABASE_URL` unset (how `rebase init`
  leaves `.env`), `rebase dev` starts a managed PostgreSQL (PGlite) under `.rebase/`,
  and every command below but `db branch` finds it. `rebase db url` prints whichever one is in use
- For your own PostgreSQL (local, Docker, or a cloud one): PostgreSQL 14+, and
  `DATABASE_URL` set in the project root's `.env` file
- pnpm installed

## Quick Start (Development)

```bash
# From the project root directory:

# 1. Generate Drizzle schema from the tables in backend/schema
rebase schema generate

# 2. Push changes directly to database (no migration files)
rebase db push
```

## Production Workflow (With Migrations)

```bash
# 1. Generate Drizzle schema
rebase schema generate

# 2. Generate SQL migration files (creates timestamped .sql in ./drizzle/migrations/)
rebase db generate

# 3. Review the generated SQL before applying!

# 4. Apply migrations
rebase db migrate
```

## Command Reference

| Command | Description | When to Use |
|---------|-------------|-------------|
| `rebase schema generate` | `backend/schema` → Drizzle schema | Always first step |
| `rebase schema introspect` | DB → `defineTable` files in `backend/schema` | Adopting an existing database (Preferred) |
| `rebase db push` | Apply schema directly to DB | Development |
| `rebase db generate` | Generate schema + create SQL migration files | Production prep |
| `rebase db migrate` | Run pending migrations | Production deploy |
| `rebase db branch` | Create / list / inspect / diff / switch / delete / prune a database branch | Feature work (needs a real PostgreSQL) |
| `rebase db backup` | Write a backup of the current database (default `./backups` where you run `rebase`) | Before a risky change |
| `rebase db backups list` | List the backups taken so far | Picking one to restore |
| `rebase db restore` | Restore the database from a backup | Recovery |
| `rebase db pull` | Copy another database into local dev (`--from <url>`, `--anonymize`, all-or-nothing); never writes to the source | Working against real data locally |
| `rebase db url` | Print the connection string this project uses | Piping into `psql` |
| `rebase db stop` | Stop the managed local database. It comes back on the same port, and a running `rebase dev` reconnects once any command starts it again | Freeing the port |
| `rebase db reset` | Delete and recreate the managed local database | A clean slate in dev |
| `rebase db request-role migrate --to <role>` | Move the database to new request role(s), in this database only | Giving a project on a shared cluster a role of its own |
| `rebase db provision --print` | Print the request-role setup SQL | A DBA creates roles, not Rebase |

<!-- docs-verify: ignore -->
> **IMPORTANT FOR AGENTS:** `push`, `generate`, `migrate`, `branch`, `backup`, `backups`,
> `restore`, `request-role`, `provision`, `pull`, `url`, `stop` and `reset` are the whole list — anything else exits 1.
> In particular there is no `rebase db studio`.
>
> `rebase db push` refuses a destructive change on a non-TTY (dropped column, dropped
> table, a column type change that can lose values, such as `timestamptz` → `date` or
> `numeric` → `integer`, or a primary key change, which gives every row a new id). In CI or from an agent, pass `--allow-destructive`
> once you have confirmed the data loss is intended. From 0.25 the same check stops the drop
> of a CHECK or EXCLUDE constraint the database holds, and `db push` leaves those
> constraints out of its plan, so it only fires when one was missed.

## Key Backend Packages

| Package | Purpose |
|---------|---------|
| `@rebasepro/server` | Hono server coordinator, API generation, auth, storage, env validation |
| `@rebasepro/server-postgres` | PostgreSQL bootstrapper, data driver, connection helpers, realtime (LISTEN/NOTIFY) |
| `@rebasepro/types` | Shared TypeScript type definitions (`TableDef`, `TableDescriptor`, `SecurityRule`, etc.) |
| `@rebasepro/server/schema` | Table authoring: `defineTable`, `defineAuthTable`, `defineView`, `col`, `rel`, `json` — loads no server code |

## Connection Functions

All connection functions are exported from `@rebasepro/server-postgres`.

### createPostgresDatabaseConnection()

The primary connection factory. Creates a Drizzle-backed Postgres connection with a production-grade pool.

```typescript
import { createPostgresDatabaseConnection } from "@rebasepro/server-postgres";

const { db, pool, connectionString } = createPostgresDatabaseConnection(
    process.env.DATABASE_URL!,
    undefined,               // optional: Drizzle schema for relational API
    {                        // optional: PostgresPoolConfig overrides
        max: 20,
        idleTimeoutMillis: 30_000,
        connectionTimeoutMillis: 10_000,
    }
);
```

**Signature:**

```typescript no-verify
function createPostgresDatabaseConnection(
    connectionString: string,
    schema?: Record<string, unknown>,
    poolConfig?: PostgresPoolConfig
): { db: NodePgDatabase; pool: Pool; connectionString: string }
```

**Returns** `{ db, pool, connectionString }` — the `pool` is exposed so callers can register shutdown hooks (`pool.end()`) or monitor pool metrics.

### createReadReplicaConnection()

Creates a connection to a read replica for distributing read queries. Uses a default pool max of **10**.

```typescript
import { createReadReplicaConnection } from "@rebasepro/server-postgres";

const readResources = createReadReplicaConnection(
    process.env.DATABASE_READ_URL!,
    mergedSchema   // optional: same Drizzle schema for relational API
);
```

**Signature:**

```typescript no-verify
function createReadReplicaConnection(
    connectionString: string,
    schema?: Record<string, unknown>,
    poolConfig?: PostgresPoolConfig
): { db: NodePgDatabase; pool: Pool; connectionString: string }
```

> **IMPORTANT FOR AGENTS:** The bootstrapper automatically creates a read replica connection when `DATABASE_READ_URL` (or, from 0.25, the adapter's `readConnectionString`) is set and differs from the primary `connectionString`. You do NOT need to call this manually in most cases.

### createDirectDatabaseConnection()

Creates a direct (non-pooled) connection for session-level features incompatible with PgBouncer transaction mode: **LISTEN/NOTIFY**, prepared statements, advisory locks. Uses a smaller default pool max of **5**.

```typescript
import { createDirectDatabaseConnection } from "@rebasepro/server-postgres";

const directResources = createDirectDatabaseConnection(
    process.env.DATABASE_DIRECT_URL!,
    mergedSchema
);
```

**Signature:**

```typescript no-verify
function createDirectDatabaseConnection(
    connectionString: string,
    schema?: Record<string, unknown>,
    poolConfig?: PostgresPoolConfig
): { db: NodePgDatabase; pool: Pool; connectionString: string }
```

> **IMPORTANT FOR AGENTS:** The bootstrapper uses `DATABASE_DIRECT_URL` (or, from 0.25, the adapter's `directConnectionString`) automatically to bypass PgBouncer for LISTEN/NOTIFY realtime. If neither is set, it falls back to the primary `connectionString`.

### PostgresPoolConfig

All three connection functions accept an optional `PostgresPoolConfig` for programmatic pool tuning:

| Property | Type | Default | Description |
|----------|------|---------|-------------|
| `max` | `number` | `20` (`10` for replica, `5` for direct) | Maximum connections in the pool |
| `idleTimeoutMillis` | `number` | `30000` | Close idle connections after this many ms |
| `connectionTimeoutMillis` | `number` | `10000` | Abort connection attempts after this many ms |
| `queryTimeout` | `number` | `60000` | Client-side read timeout in ms — kept above `statementTimeout`, as a backstop for a dead network |
| `statementTimeout` | `number` | `30000` | Server-side `statement_timeout` in ms |
| `keepAlive` | `boolean` | `true` | Enable TCP keep-alive |
| `searchPath` | `string \| false` | `"public"` | `search_path` pinned on every connection; `false` sends none |

`REBASE_DB_POOL_MAX`, when set, is a ceiling on every pool's `max` in the process.

## DatabasePoolManager

The `DatabasePoolManager` manages multiple database connection pools dynamically. Used internally by the bootstrapper when `adminConnectionString` is configured — enables cross-database operations like branching and SQL execution against arbitrary databases.

```typescript
import { DatabasePoolManager } from "@rebasepro/server-postgres";

const poolManager = new DatabasePoolManager(process.env.ADMIN_CONNECTION_STRING!);

// Get a Drizzle instance for a specific database (creates pool on demand)
const db = poolManager.getDrizzle("my_branch_db");

// Get the raw pg.Pool
const pool = poolManager.getPool("my_branch_db");

// Check if a pool exists
poolManager.hasPool("my_branch_db"); // boolean

// Disconnect a specific database (required before DROP DATABASE / CREATE DATABASE ... TEMPLATE)
await poolManager.disconnectDatabase("my_branch_db");

// Shut down all pools
await poolManager.shutdown();
```

**API Reference:**

| Method | Signature | Description |
|--------|-----------|-------------|
| `constructor` | `new DatabasePoolManager(adminConnectionString: string)` | Parses the database name from the URL |
| `getDrizzle` | `(databaseName: string) => NodePgDatabase` | Returns (or creates) a Drizzle instance for the database |
| `getPool` | `(databaseName: string) => Pool` | Returns (or creates) a raw pg Pool for the database |
| `hasPool` | `(databaseName: string) => boolean` | Checks if a pool exists |
| `disconnectDatabase` | `(databaseName: string) => Promise<void>` | Ends pool + removes cached instances |
| `shutdown` | `() => Promise<void>` | Ends all pools |
| `defaultDatabaseName` | `string` (readonly) | The database name extracted from the admin connection string |

**Dynamic pool settings:** Each dynamically created pool uses `max: 10`, `idleTimeoutMillis: 10000`, `allowExitOnIdle: true`.

## Backend Initialization

Rebase supports two equivalent APIs for configuring the PostgreSQL backend:

### Option A: createPostgresAdapter() (Recommended)

The `DatabaseAdapter` API — a simpler, flattened interface that wraps the bootstrapper internally:

```typescript
import { Hono } from "hono";
import type { HonoEnv } from "@rebasepro/server";
import { getRequestListener } from "@hono/node-server";
import { createServer } from "http";
import path from "path";
import { initializeRebaseBackend } from "@rebasepro/server";
import { createPostgresDatabaseConnection, createPostgresAdapter } from "@rebasepro/server-postgres";
import { tables, enums, relations } from "./schema.generated.js";
// backend/schema/index.ts: every table, and the defaultRls of its "inherit" tables.
import { users, posts, tags, defaultRls } from "../schema/index.js";

const app = new Hono<HonoEnv>();
const server = createServer(getRequestListener(app.fetch));

const { db, pool, connectionString } = createPostgresDatabaseConnection(
    process.env.DATABASE_URL!,
    undefined,
    { max: env.DB_POOL_MAX }
);

const backend = await initializeRebaseBackend({
    tables: [users, posts, tags],
    defaultRls,
    schemaDir: path.resolve(__dirname, "../schema"),
    functionsDir: path.resolve(__dirname, "../functions"),
    cronsDir: path.resolve(__dirname, "../crons"),
    server,
    app,
    database: createPostgresAdapter({
        connection: db,
        schema: { tables, enums, relations },
        adminConnectionString: process.env.ADMIN_CONNECTION_STRING || process.env.DATABASE_URL,
        connectionString  // enables cross-instance realtime via LISTEN/NOTIFY
    }),
    auth: {
        jwtSecret: process.env.JWT_SECRET!,
        accessExpiresIn: "1h",
        refreshExpiresIn: "30d",
        allowRegistration: false,
    },
    storage: { type: "local", basePath: "./uploads" },
    history: true,
});

server.listen(3001);
```

`initializeRebaseBackend` builds, mounts and starts in one call. One step under
it, `createRebaseBackend` (new in 0.25) takes the same options plus a required
`provision` and mounts and starts nothing — see
`../rebase-basics/references/backend-configuration.md`.

### Option B: createPostgresBootstrapper()

The bootstrapper protocol — database-specific logic is encapsulated in bootstrapper objects:

```typescript no-verify
import { createPostgresDatabaseConnection, createPostgresBootstrapper } from "@rebasepro/server-postgres";
import { tables, enums, relations } from "./schema.generated.js";

const { db, pool, connectionString } = createPostgresDatabaseConnection(process.env.DATABASE_URL!);

const backend = await initializeRebaseBackend({
    // ...same config as above...
    bootstrappers: [
        createPostgresBootstrapper({
            connection: db,
            schema: { tables, enums, relations },
            adminConnectionString: process.env.DATABASE_URL,
            connectionString
        })
    ],
    // ...auth, storage, etc...
});
```

> **IMPORTANT FOR AGENTS:** When both `database` and `bootstrappers` are provided, `database` takes precedence and `bootstrappers` is ignored.

### PostgresDriverConfig Properties

The config object passed to both `createPostgresAdapter()` and `createPostgresBootstrapper()`:

| Property | Type | Required | Description |
|----------|------|----------|-------------|
| `connection` | `NodePgDatabase` | ✅ | Drizzle database connection from `createPostgresDatabaseConnection()` |
| `schema` | `{ tables, enums, relations }` | No | The generated Drizzle schema — for diagnostics only: the driver reads the tables it serves from the database, and this only lets it warn when `schema.generated.ts` no longer describes the database |
| `introspectionSchema` | `string` | No | The Postgres schema read when the tables are read from the database (no `backend/schema`). Default `public` |
| `unknownFilterFields` | `"error" \| "warn"` | No | A filter field that resolves to no column. Default `"error"`; `"warn"` drops it |
| `connectionString` | `string` | No | Primary connection URL — enables LISTEN/NOTIFY realtime |
| `adminConnectionString` | `string` | No | Admin-level connection — enables `DatabasePoolManager`, SQL editor, branching |
| `readConnectionString` | `string` | No | A read replica: reads go there, writes stay on the primary. At boot `DATABASE_READ_URL` fills it when unset. Honoured since 0.25 — before, only the variable worked |
| `directConnectionString` | `string` | No | New in 0.25. A direct (non-pooled) URL for the `LISTEN` connections realtime holds — PgBouncer in transaction mode keeps none. Defaults to `connectionString`; at boot `DATABASE_DIRECT_URL` fills it when unset |
| `realtime` | `RealtimeChannelsConfig` | No | Realtime options: `bus` (cross-instance channels/presence), `channels` (history retention), `cdc` (`"auto" \| "wal" \| "trigger" \| "off"`, new in 0.25 — `REALTIME_CDC` wins over it), `path` (new in 0.25 — the one URL path the socket answers upgrades on; defaults to the one the server derives, `/` for an API at `/api`), and the `max*`/`refetch*`/`recount*` limits, each with a `REALTIME_*` variable that wins over it |

**Where the environment comes in** (0.25+): `createPostgresAdapter` and
`createPostgresBootstrapper` read the `DATABASE_*_URL` and `REALTIME_*`
variables once at boot, through `postgresDriverConfigFromEnv(config, env)`, and
open the driver with the result. The driver open itself,
`openPostgresDriver(pgConfig, init)`, reads no environment variable — a script
that calls it gets exactly the config it passed.

### Bootstrapper Lifecycle

`createRebaseBackend()` — and so `initializeRebaseBackend()` — calls bootstrapper methods in this order:

| Step | Method | Purpose |
|------|--------|---------|
| 1 | `initializeDriver(config)` | Creates the `PostgresBackendDriver`, registry, realtime service, read replica, pool manager. `config.provision: false` opens it with no DDL of any kind (no role, branch table, channel-history tables or capture triggers); `config.realtime: false` takes no part in realtime. it returns `{ driver, tableRegistry, … }` (`tableRegistry` was `collectionRegistry` before 0.31) |
| 2 | `initializeAuth(config, driverResult)` | Creates auth tables, `UserService`, `PostgresAuthRepository`. After a `provision: false` open it creates nothing and only checks the existing tables are compatible |
| 3 | `initializeHistory(config, driverResult)` | Creates the `rebase.entity_history` table and `HistoryService`. After a `provision: false` open it creates nothing and attaches to what an earlier boot left |
| 4 | `initializeRealtime(config, driverResult)` | Returns the `RealtimeService` for WebSocket subscriptions |
| 5 | `initializeWebsockets(server, ...)` | Called by `backend.attachRealtime(server)`. Attaches the socket to the server's `upgrade` event for its own path only (`limits.path`, or `realtime.path`); other upgrades are left to the host's listeners, or answered 404 when there are none |
| 6 | `getAdmin(driverResult)` | Returns the `DatabaseAdmin` for SQL execution |
| 7 | `mountRoutes(app, basePath, driverResult)` | **Deprecated, never called** — still accepted by the types. A driver that needs HTTP routes exports a route factory for the app to mount |

### Opening the driver yourself (0.25+)

Under `createPostgresAdapter` are two public pieces: `postgresDriverConfigFromEnv`
(the boot's environment reading) and `openPostgresDriver` (the open). For a
script that only reads and writes rows, `openRebase` is the shortcut above both:

```typescript
import {
    createPostgresDatabaseConnection,
    openPostgresDriver,
    openRebase,
    postgresDriverConfigFromEnv
} from "@rebasepro/server-postgres";
import { compileSchema } from "@rebasepro/common";
import { users, posts, defaultRls } from "../schema";

// `openRebase` and `openPostgresDriver` take the tables compiled: each
// descriptor with its hooks, which is what the driver runs on.
const tables = compileSchema([users, posts], { defaultRls });

// The shortcut: dataAsAdmin, sql, withAuth(user), close(). No DDL, no env reads.
// Typed by the Database `rebase generate-sdk --register` registers, or openRebase<Database>(…).
// Pass `hooks` (backend/hooks.ts) so a script write runs the global hooks too.
const rebase = await openRebase({ databaseUrl: process.env.DATABASE_URL!, tables });
await rebase.close();

// One step down, assembling your own boot: no DDL, no part in realtime.
const { db, connectionString } = createPostgresDatabaseConnection(process.env.DATABASE_URL!);
const pgConfig = postgresDriverConfigFromEnv({ connection: db, connectionString });
const { driver, internals } = await openPostgresDriver(pgConfig, { tables, provision: false, realtime: false });
```

`internals` is typed (`PostgresDriverInternals`: `db`, `readDb`, `registry`,
`realtimeService`, `driver`, `poolManager`, `provisioned`), so nothing needs a
cast. `rebase console` is `openRebase` in a REPL — see the `rebase-basics`
skill.

### RebaseBackendConfig Options

The full `RebaseBackendConfig` table — every option, its default, and what the runtime fills it from — is in the **`rebase-basics`** skill: `../rebase-basics/references/backend-configuration.md`. Two defaults differ between a hand-written call and the runtime, and are the ones to check first:

- **`history`** is off when omitted from `initializeRebaseBackend`; the runtime passes `REBASE_HISTORY`, which defaults to `true`.
- **`enableSwagger`** is unset by default: `/api/docs` is served to the schema endpoint's audience, each caller the tables they may see, and there is no Swagger UI. `true` (`REBASE_ENABLE_SWAGGER=true`) publishes the whole spec, `false` keeps it admin-only.

### Auth Configuration (RebaseAuthConfig)

| Property | Type | Default | Description |
|----------|------|---------|-------------|
| `jwtSecret` | `string` | — (`loadEnv()` generates `JWT_SECRET` in dev) | JWT signing secret (≥32 chars) |
| `accessExpiresIn` | `string` | `"1h"` | Access token TTL |
| `refreshExpiresIn` | `string` | `"30d"` | Refresh token TTL |
| `requireAuth` | `boolean` | `true` | Require auth for data routes |
| `allowRegistration` | `boolean` | **`false`** | Enable user self-registration |
| `serviceKey` | `string` | — (the runtime passes `REBASE_SERVICE_KEY`) | Service-to-service auth key (≥32 chars). Unset, the backend generates an internal per-boot key for the singleton's control-plane calls only |
| `defaultRole` | `string` | — | Default role for new users |
| `email` | `EmailConfig` | — | SMTP email configuration |
| `google` | `{ clientId, clientSecret? }` | — | Google OAuth |
| `github` | `{ clientId, clientSecret }` | — | GitHub OAuth |
| `microsoft` | `{ clientId, clientSecret, tenantId? }` | — | Microsoft OAuth |
| `apple` | `{ clientId, teamId, keyId, privateKey }` | — | Apple OAuth |
| `facebook` | `{ clientId, clientSecret }` | — | Facebook OAuth |
| `twitter` | `{ clientId, clientSecret }` | — | Twitter OAuth |
| `discord` | `{ clientId, clientSecret }` | — | Discord OAuth |
| `linkedin` | `{ clientId, clientSecret }` | — | LinkedIn OAuth |
| `gitlab` | `{ clientId, clientSecret, baseUrl? }` | — | GitLab OAuth |
| `bitbucket` | `{ clientId, clientSecret }` | — | Bitbucket OAuth |
| `slack` | `{ clientId, clientSecret }` | — | Slack OAuth |
| `spotify` | `{ clientId, clientSecret }` | — | Spotify OAuth |
| `hooks` | `AuthHooks` | — | Override password hashing, validation, etc. |

> **WARNING FOR AGENTS:** `allowRegistration` defaults to **`false`**, not `true`. You must explicitly set it to `true` if you want users to register themselves.

## Environment Variables

### loadEnv()

The `loadEnv()` function validates all environment variables at startup using Zod. It is exported from `@rebasepro/server`.

**Basic usage:**

```typescript
import dotenv from "dotenv";
import { loadEnv } from "@rebasepro/server";

dotenv.config({ path: "../../.env" });

export const env = loadEnv();
```

**Extended — add your own typed variables:**

```typescript
import dotenv from "dotenv";
import { loadEnv } from "@rebasepro/server";
import { z } from "zod";

dotenv.config({ path: "../../.env" });

export const env = loadEnv({
    extend: z.object({
        SMTP_HOST: z.string().optional(),
        SMTP_PORT: z.string().default("587").transform(Number),
        STRIPE_SECRET_KEY: z.string(),
    })
});
// env.SMTP_HOST       → string | undefined  (fully typed)
// env.STRIPE_SECRET_KEY → string            (validated, required)
```

**Signatures:**

```typescript no-verify
function loadEnv(): RebaseEnv;
function loadEnv<E extends z.ZodObject<z.ZodRawShape>>(options: { extend: E }): RebaseEnv & z.infer<E>;
```

### Dev-Mode Auto-Generated Secrets

In non-production mode, `loadEnv()` **auto-generates** `JWT_SECRET` and `REBASE_SERVICE_KEY` if not set, so developers can start without manual setup. They are cached in `.rebase-dev-secrets.json` (gitignored), so existing tokens survive a restart. Only when that file cannot be written are they regenerated on every boot, and boot warns that they are ephemeral.

### Full Environment Variable Reference

| Variable | Required | Default | Description |
|----------|----------|---------|-------------|
| `DATABASE_URL` | ✅ Yes | — | PostgreSQL connection string (must be a valid URL) |
| `JWT_SECRET` | ✅ Yes (≥32 chars) | Auto-generated in dev | JWT signing secret |
| `NODE_ENV` | No | `development` | `development`, `production`, or `test` |
| `PORT` | No | `3001` | Server port |
| `ADMIN_CONNECTION_STRING` | No | — | Admin-level DB connection for cross-database operations |
| `DATABASE_DIRECT_URL` | No | — | Direct connection URL bypassing PgBouncer (for LISTEN/NOTIFY) |
| `DATABASE_READ_URL` | No | — | Read replica connection URL |
| `DB_POOL_MAX` | No | `20` | Max DB connection pool size |
| `DB_POOL_IDLE_TIMEOUT` | No | `30000` | Pool idle timeout (ms) |
| `DB_POOL_CONNECT_TIMEOUT` | No | `10000` | Pool connect timeout (ms) |
| `DISABLE_DB_ROLE_SWITCHING` | No | — | Set to `true` to skip PostgreSQL role switching |
| `JWT_ACCESS_EXPIRES_IN` | No | `1h` | Access token TTL |
| `JWT_REFRESH_EXPIRES_IN` | No | `30d` | Refresh token TTL |
| `GOOGLE_CLIENT_ID` | No | — | Google OAuth client ID |
| `GOOGLE_CLIENT_SECRET` | No | — | Google OAuth client secret |
| `REBASE_SERVICE_KEY` | No | Auto-generated in dev | Service-to-service auth key |
| `ALLOW_REGISTRATION` | No | `false` | Enable user self-registration |
| `ALLOW_LOCALHOST_IN_PRODUCTION` | No | `false` | Allow localhost URLs in production env vars |
| `CORS_ORIGINS` | No (⚠ required in prod) | — | Comma-separated allowed origins |
| `FRONTEND_URL` | No | — | Frontend URL (used for CORS) |
| `STORAGE_TYPE` | No | `local` | `local`, `s3`, or `gcs` |
| `STORAGE_PATH` | No | — | Path for local file storage |
| `FORCE_LOCAL_STORAGE` | No | `false` | Allow local storage in production. Without it the local backend is not registered — the backend still boots, but uploads answer `501 STORAGE_NOT_CONFIGURED` |
| `S3_BUCKET` | No (if s3) | — | S3 bucket name |
| `S3_REGION` | No | — | S3 region |
| `S3_ACCESS_KEY_ID` | No (if s3) | — | S3 access key |
| `S3_SECRET_ACCESS_KEY` | No (if s3) | — | S3 secret key |
| `S3_ENDPOINT` | No | — | S3 endpoint URL (MinIO, R2) |
| `S3_FORCE_PATH_STYLE` | No | `false` | Force path-style S3 URLs |

### Production Validations

`loadEnv()` enforces strict validation in production (`NODE_ENV=production`):

| Rule | Error Message |
|------|---------------|
| `CORS_ORIGINS` or `FRONTEND_URL` required | `CORS_ORIGINS or FRONTEND_URL must be set in production to secure the API.` |
| `JWT_SECRET` set explicitly (nothing is generated in production) | `JWT_SECRET: is required` |
| No localhost URLs (unless `ALLOW_LOCALHOST_IN_PRODUCTION=true`) | `Environment variable DATABASE_URL points at a local/loopback host...` |

## Migrations and Atlas (there is no `drizzle.config.ts`)

A project has no `drizzle.config.ts` and runs no `drizzle-kit`: do not create one.
`db push`, `db generate` and `db migrate` drive [Atlas](https://atlasgo.io)
(`@ariga/atlas`, a dependency of `backend/package.json`) from the tables:

- The desired state is `.rebase/sql/schema.sql`, rendered from the tables on
  every run (see above). Migrations go to `backend/drizzle/migrations/`, with
  Atlas's `atlas.sum` beside them.
- Atlas plans against a second, empty database: `<db>_dev_diff` on a real server,
  a throwaway in-memory PGlite on the managed one, or the one
  `REBASE_ATLAS_DEV_URL` names.
- **Only tables declared in `backend/schema` (and their many-to-many junctions) are in
  scope.** Every other table is excluded from the diff, so Atlas never drops it;
  the `rebase` schema (auth tables, RLS helpers, Atlas's revision table) is never
  touched. If the database cannot be read to build that list, the command fails
  rather than plan without it.

## Entity History (Audit Trail)

Enable audit logging for all entity mutations by setting `history: true` in the backend config:

```typescript no-verify
const backend = await initializeRebaseBackend({
    // ...
    history: true,  // Enable with default settings
});

// Or with a retention policy:
const backend = await initializeRebaseBackend({
    // ...
    history: { retention: 90 },  // Auto-purge entries older than 90 days
});
```

When enabled:
- The bootstrapper auto-creates a `rebase.entity_history` table
- Every `INSERT`, `UPDATE`, `DELETE` on a table that declares `history: true` is recorded with before/after entities
- History is queryable via the `GET /api/data/:slug/:id/history` REST endpoint

## Health Check

The runtime exposes `/health` (and `/api/health`), which returns — `details` only outside production:

```json
{
    "status": "ok",
    "latencyMs": 2.5,
    "details": { ... }
}
```

HTTP 200 for healthy, 503 for degraded. Internally executes `SELECT 1` to verify database connectivity.

## Graceful Shutdown

The recommended graceful shutdown pattern:

```typescript
let isShuttingDown = false;
const gracefulShutdown = async (signal: string) => {
    if (isShuttingDown) return;
    isShuttingDown = true;

    const forceTimer = setTimeout(() => {
        console.error("Shutdown timed out after 15s. Force-exiting.");
        process.exit(1);
    }, 15000);
    forceTimer.unref();

    try {
        // 1. backend.shutdown() stops cron, destroys realtime, closes HTTP server
        //    (a backend from createRebaseBackend closes it only with { server })
        await backend.shutdown();

        // 2. Drain the database connection pool
        await pool.end();

        clearTimeout(forceTimer);
        process.exit(0);
    } catch (err) {
        console.error("Error during shutdown:", err);
        process.exit(1);
    }
};

process.on("SIGTERM", () => gracefulShutdown("SIGTERM"));
process.on("SIGINT", () => gracefulShutdown("SIGINT"));
```

### backend.shutdown() Sequence

The `shutdown(timeoutMs?: number, options?: { server })` method (default timeout: 15000ms) performs:

1. **Stop cron scheduler** (and the job workers and sweeps) — prevents new executions, waits for one in flight within the budget
2. **Destroy realtime services** — tears down LISTEN clients, debounce timers, subscriptions (must happen before pool.end())
3. **Close HTTP server** — stops accepting new connections, drains in-flight requests. The backend `initializeRebaseBackend` returns closes the server it was given; one built with `createRebaseBackend` closes only the server passed as `{ server }`
4. **Force-resolve** after timeout (pass `0` to disable, useful in tests)

> **WARNING FOR AGENTS:** Call `backend.shutdown()` FIRST, then `pool.end()`. Do NOT call `server.close()` separately when `backend.shutdown()` closes the HTTP server (always, from `initializeRebaseBackend`; with `{ server }`, from `createRebaseBackend`). Calling both will deadlock.

## JWT Dual-Package Hazard

> [!WARNING]
> **JWT Dual-Package Hazard (Monorepos / pnpm)**
> When running a backend inside a monorepo workspace (especially with `tsx` and `--preserve-symlinks`), you may encounter a `RebaseApiError: JWT secret not configured. Call configureJwt() first` error. This occurs because Node.js resolves two different module instances of `@rebasepro/server`.
>
> **Fix:** Explicitly call `configureJwt` in your backend's entry point **before** `initializeRebaseBackend`:
> ```typescript
> import { initializeRebaseBackend, configureJwt } from "@rebasepro/server";
>
> configureJwt({
>     secret: process.env.JWT_SECRET!,
>     accessExpiresIn: "1h",
>     refreshExpiresIn: "30d"
> });
>
> const backend = await initializeRebaseBackend({ ... });
> ```
>
> `configureJwt` sets the **process default** that the module-level helpers (`verifyAccessToken`, `extractUserFromToken`, …) answer for. From 0.25, a backend given `auth.jwtSecret` signs and verifies with its own keys (`backend.services.jwt`), whatever the default holds; a backend with no secret of its own (an `AuthAdapter`) follows the default.

## Important Notes

- **To import a legacy database**, use `rebase schema introspect`: it writes one `defineTable`
  module per table into `backend/schema/` and an export for each in `index.ts`, with
  `rls: "admin-only"` on every table — write the real rules before anyone but an admin needs
  the rows.
- **Never use `schema introspect` then `db migrate`** — introspected databases already have the tables
- **Always backup before production migrations** — `ALTER COLUMN` or `DROP COLUMN` can cause data loss
- **Tables not in schema are ignored** — custom tables and internal Rebase tables are safe
- **CHECK and EXCLUDE constraints are kept (0.25+)** — a table cannot declare either, so
  `db push` leaves every one on a declared table alone: hand-written, or carried by a database
  `schema introspect` adopted (a readable CHECK also becomes the column's `.validate()`). On 0.24
  the first push after an introspection dropped them all without a prompt. To remove one, drop it
  by hand. Triggers and functions a user wrote are never touched (Atlas does not inspect them).
  `db generate` is different: it diffs the migration files, so a CHECK a hand-written migration
  added is dropped again by the next generated migration. Its report names the drop when the
  database holds the constraint, but the statement stays in the file — delete it before
  `db migrate`, or let `db migrate --refuse-destructive` stop on it
- **Odd names are safe (0.25+)** — every exclude matches the name exactly, a `.`, `[`, `]` or `*`
  in it included. On 0.24 a hand-written index named like `orders_idx[v1]` was dropped without a
  prompt, and a `.` in a table's or an index's name failed every push (`too many parts in pattern`)
- **Indexes are declared too** — a table's `indexes` array is emitted into `schema.sql` by
  `db push`/`db generate` *and* created at boot with `CREATE INDEX CONCURRENTLY IF NOT EXISTS`,
  so a deployment that never runs `db push` still gets them. See the `rebase-tables` skill.
- **A hand-written index on a managed table used to be dropped by `db push`.** Push is
  declarative: an index absent from `schema.sql` was drift, Atlas planned `DROP INDEX`, and that
  is not a destructive pattern, so the auto-approved apply took it with no prompt. Ownership is
  now decided by the name — `<table>_<columns>_ix_<7 hex>` / `_ux_` is Rebase's, everything else
  is excluded from the diff and never touched. Deleting a declaration still drops that index, on
  purpose. Never rename a hand-written index to look generated.
- **Review generated SQL** — always inspect the new migration in `./drizzle/migrations/` before applying
- **Schema directory** — Tables are defined in `backend/schema/` (or `rebase.json`'s `apps.<backend>.tables`; `--schema-dir <dir>` on every command).

## Troubleshooting

### 1. SQL Editor Permission Denied (`permission denied for table <name>`)
- **Symptoms:** You can view data in the CMS spreadsheet view, but running custom SQL queries (like `SELECT * FROM table;`) in the Rebase Studio SQL Editor throws `cause: error: permission denied for table <table_name>`.
- **Cause:** Rebase tries to switch database roles to match the active user's role (e.g., `SET LOCAL ROLE "admin"`). If you are using custom auth (roles that exist only in Rebase, in the users' `roles` column, rather than as actual PostgreSQL roles), or if the database-level role doesn't have `SELECT` privileges, the query fails. The CMS views do not switch to a role named after yours: they go through the data API, which runs every request as `rebase_user` under the tables' RLS policies, so they keep working.
- **Solution:** Add `DISABLE_DB_ROLE_SWITCHING=true` to your backend `.env` configuration. This skips role switching, executing queries under the connection owner user.

### 2. SQL Editor/Studio Schema Fetch Failed (`Cross-database execution requires adminConnectionString`)
- **Symptoms:** Running queries in the SQL Editor or attempting to load schemas in Studio throws `Failed to fetch schema: Cross-database execution requires adminConnectionString to be configured in the backend.`
- **Cause:** The PostgreSQL bootstrapper requires `adminConnectionString` and `getAdmin()` to be configured to execute database administration commands (including schema fetch). If `adminConnectionString` is set to `undefined` or `getAdmin()` returns `undefined` (often done to enforce a zero-schema-change requirement), administrative commands fail.
- **Solution:** Ensure `adminConnectionString` is passed to `createPostgresBootstrapper` or `createPostgresAdapter` (typically `adminConnectionString: env.ADMIN_CONNECTION_STRING || env.DATABASE_URL`) and ensure `getAdmin()` is not overridden to return `undefined`.

### 3. Read Replica Not Being Used
- **Symptoms:** All queries go to the primary database even though `DATABASE_READ_URL` (or `readConnectionString`) is set.
- **Cause:** The bootstrapper only creates the read replica connection when the read URL differs from the primary `connectionString`. In 0.24 and earlier the `readConnectionString` option was ignored, and only `DATABASE_READ_URL` worked.
- **Solution:** Ensure the read URL is a different URL from `DATABASE_URL` and points to an actual read replica.

### 4. PgBouncer Breaks Realtime (LISTEN/NOTIFY)
- **Symptoms:** Realtime subscriptions don't receive updates in production.
- **Cause:** PgBouncer in transaction mode does not support session-level LISTEN/NOTIFY.
- **Solution:** Set `DATABASE_DIRECT_URL` (or, from 0.25, the adapter's `directConnectionString`) to a connection string that bypasses PgBouncer and connects directly to PostgreSQL.

## References

- **Documentation:** [rebase.pro/docs](https://rebase.pro/docs)
- **GitHub:** [github.com/rebasepro/rebase](https://github.com/rebasepro/rebase)
