# PostgreSQL migrations

> Fresh-install and upgrade SQL, component selection, custom schemas, and exported SQL assets.

Source: https://good-webhooks.dev/docs/reference/migrations



`good-webhooks/migrations` generates the fresh-install and upgrade SQL for management and delivery tables; your migration tool applies it.

```ts
import {
  getPostgresMigration,
  getPostgresUpgrade,
  POSTGRES_SCHEMA_VERSION,
} from 'good-webhooks/migrations'
import type { PostgresMigrationOptions, PostgresUpgradeOptions } from 'good-webhooks/migrations'
```

The [migrations guide](https://good-webhooks.dev/docs/operations/migrations.md) covers applying, upgrading, and splitting storage.

## getPostgresMigration [#getpostgresmigration]

```ts
getPostgresMigration(options?: PostgresMigrationOptions): string
```

```ts title="scripts/write-migration.ts"
import { writeFile } from 'node:fs/promises'
import { getPostgresMigration } from 'good-webhooks/migrations'

await writeFile(
  'migrations/001-webhooks.sql',
  getPostgresMigration({ schema: 'webhooks', component: 'all' }),
)
```

Returns fresh-install SQL. It never connects to a database or applies DDL. The host applies the SQL once in a transaction and records it in its migration ledger.

| Field | Type | Default | Description |
| --- | --- | --- | --- |
| `schema?` | `string` |  | PostgreSQL namespace, shared by both components unless generated separately. Defaults to public. |
| `component?` | `"all" \| "delivery" \| "management"` |  | Generate only the component you use, or both for standalone setup. Defaults to all. |

| Option      | Default    | Meaning                                        |
| ----------- | ---------- | ---------------------------------------------- |
| `schema`    | `'public'` | Namespace for all tables emitted by this call. |
| `component` | `'all'`    | `'management'`, `'delivery'`, or `'all'`.      |

Every generated script includes `CREATE SCHEMA IF NOT EXISTS` for its target namespace before its tables. Table creation is not idempotent. Reapplying initial SQL is not an upgrade procedure. Invalid names or components produce `INVALID_CONFIG`.

## Components and SQL assets [#components-and-sql-assets]

| Component    | Created tables                                                                                                             | Equivalent shipped asset for `public`      |
| ------------ | -------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------ |
| `management` | `webhook_schema_version` (if absent), `webhook_endpoints`                                                                  | `good-webhooks/migrations/management.sql`  |
| `delivery`   | `webhook_schema_version` (if absent), `webhook_endpoint_state`, `webhook_events`, `webhook_deliveries`, `webhook_attempts` | `good-webhooks/migrations/delivery.sql`    |
| `all`        | Both groups                                                                                                                | `good-webhooks/migrations/001-initial.sql` |

Each component records its version in `webhook_schema_version`; components that share a namespace share the table. The `.sql` files are public package exports. They are text assets, not executable JavaScript imports. Resolve them with `import.meta.resolve` or `require.resolve` and read them with your migration tool:

```ts
import { readFile } from 'node:fs/promises'

const sql = await readFile(
  new URL(import.meta.resolve('good-webhooks/migrations/delivery.sql')),
  'utf8',
)
```

The current schema version is `4`, exported as `POSTGRES_SCHEMA_VERSION`. Every `check()` expects exactly that version for its component. Fresh-install assets never upgrade existing data; use `getPostgresUpgrade` for that.

## getPostgresUpgrade [#getpostgresupgrade]

```ts
getPostgresUpgrade(options: PostgresUpgradeOptions): string
```

```ts title="scripts/write-upgrade.ts"
import { writeFile } from 'node:fs/promises'
import { getPostgresUpgrade } from 'good-webhooks/migrations'

await writeFile(
  'migrations/002-webhooks-schema-4.sql',
  getPostgresUpgrade({ schema: 'webhooks', component: 'all', from: 3 }),
)
```

Returns SQL that moves storage recorded at version `from` to the current version. It never connects to a database. The SQL first checks the recorded version and raises inside your transaction if it differs, so a wrong or repeated application rolls back cleanly.

| Field | Type | Default | Description |
| --- | --- | --- | --- |
| `schema?` | `string` |  | PostgreSQL namespace the installation uses. Defaults to public. |
| `component?` | `"all" \| "delivery" \| "management"` |  | Upgrade only the component in this namespace, or both for a shared namespace. Defaults to all. |
| `from` | `number` |  | The schema version currently recorded in the database. Only 3 can be upgraded. |

| Option      | Default    | Meaning                                                               |
| ----------- | ---------- | --------------------------------------------------------------------- |
| `schema`    | `'public'` | Namespace of the installation being upgraded.                         |
| `component` | `'all'`    | `'management'`, `'delivery'`, or `'all'` for a shared namespace.      |
| `from`      | required   | The version recorded in the database. `3` is the only upgradable one. |

`from: 4` throws `INVALID_CONFIG` because no upgrade is needed. Versions before `3` were never published and need fresh storage.

| Upgrade | Shipped asset for `public`                             | What it changes                                                                           |
| ------- | ------------------------------------------------------ | ----------------------------------------------------------------------------------------- |
| 3 to 4  | `good-webhooks/migrations/upgrades/004-all.sql`        | Records a version per component. Delivery rewrites its version row; management gains one. |
|         | `good-webhooks/migrations/upgrades/004-management.sql` | Management in its own namespace: creates the version table and records `management`.      |
|         | `good-webhooks/migrations/upgrades/004-delivery.sql`   | Delivery in its own namespace: rewrites the version row.                                  |

In a shared namespace apply `all`, or `delivery` before `management`; the management step needs the rewritten table and fails otherwise. No data tables change in this upgrade, so processes on the previous package keep working until they restart. The [migrations guide](https://good-webhooks.dev/docs/operations/migrations.md#upgrades) gives the rollout order.

## Schema names [#schema-names]

A schema must be a nonblank, well-formed string of at most 63 UTF-8 bytes without null bytes. The generator and runtime quote names as PostgreSQL identifiers. Invalid names produce `INVALID_CONFIG`.

The `schema` passed to a runtime must match the corresponding migration. Management and delivery can use separate namespaces by generating each component separately. An injected management provider keeps its own configured namespace regardless of delivery options.

Better Auth management follows its adapter's schema workflow. These migrations cover the standalone PostgreSQL provider and delivery only. `goodWebhooks({ schema: { modelName, fields } })` changes model and field names, not PostgreSQL placement.