# BGW-Managed Duroxide Migrations

**Status:** Implemented  
**Target version:** 0.2.0

## Motivation

Previously, `CREATE EXTENSION pg_durable` included the full duroxide-pg schema DDL via `extension_sql_file!("../sql/duroxide_install.sql")`, creating all duroxide tables, functions, indexes, and triggers as extension-owned objects. This change moved schema migration responsibility from the extension to the background worker (BGW).

Three concrete motivations:

1. **Decoupling**: pg_durable extension state (`df.*`) and duroxide engine state (`duroxide.*`) have different lifecycles. The extension schema is a stable contract; the duroxide schema is an implementation detail that may evolve independently.

2. **Future provider flexibility**: pg_durable may use a different duroxide provider in the future. If DDL lives in the BGW rather than the extension SQL, swapping providers does not require touching extension install/upgrade scripts.

3. **Backward compatibility relief**: duroxide-pg does not offer binary compatibility with old schema versions. With BGW-managed migrations, new duroxide migrations are applied automatically at BGW startup — no extension upgrade involvement needed, no `.so` compatibility burden.

## Architecture

### What `CREATE EXTENSION pg_durable` does (changed)

**Before:** Extension SQL includes the full duroxide DDL (~2300 lines), creating all duroxide objects as extension members. The `_duroxide_migrations` table is pre-populated with rows 1–N.

**After:** Extension SQL creates only a bare `CREATE SCHEMA duroxide;` — no `IF NOT EXISTS`. The empty schema is an extension-owned object.

The absence of `IF NOT EXISTS` is intentional: if a `duroxide` schema already exists when the user runs `CREATE EXTENSION pg_durable`, the statement fails immediately with a clear PostgreSQL error. This prevents a pre-existing (potentially attacker-crafted) schema from silently becoming part of the extension's state.

The `df.*` tables, RLS policies, functions, and operators are all unchanged.

### Background worker initialization (changed)

**Before:**
1. Wait for extension to be created
2. `PostgresProvider::new_with_config(VerifyOnly)` — verifies schema is already populated
3. Write epoch sentinel to `df._worker_epoch`
4. Start duroxide runtime

**After:**
1. Wait for extension to be created
2. Verify `duroxide` schema exists **and is extension-owned** (security gate)
3. Release extension ownership of any objects inside the `duroxide` schema (no-op on fresh installs; on upgrades from ≤0.1.1 it de-registers the embedded DDL so that `ApplyAll` can modify those objects)
4. `PostgresProvider::new_with_config(ApplyAll)` — applies pending migrations (no-op if already current), creates activity pool, starts duroxide runtime
5. Write readiness record to `duroxide._worker_ready`
6. Write epoch sentinel to `df._worker_epoch`

Steps 4–6 are grouped inside the main loop in `run_duroxide_runtime()`. Step 4 is performed by `initialize_duroxide_runtime()`, which returns the running runtime handle. Steps 5 and 6 execute immediately after, before entering the running-state poll loop.

Step 1 is unchanged from the previous design and is not detailed further.

#### Steps 2–3: ownership check and release

The BGW queries `pg_depend` to confirm the schema was created by the extension:

```sql
SELECT EXISTS (
    SELECT 1
    FROM pg_namespace n
    JOIN pg_depend d
        ON d.objid = n.oid
        AND d.classid = 'pg_namespace'::regclass
        AND d.deptype = 'e'
    JOIN pg_extension e
        ON e.oid = d.refobjid
        AND e.extname = 'pg_durable'
    WHERE n.nspname = 'duroxide'
)
```

If this check fails (schema missing or not extension-owned), the BGW logs a warning and retries after the normal initialization retry interval (`INIT_RETRY_INTERVAL`). Under normal operation this can only fail transiently (race between extension creation and BGW polling) or if someone manually created a `duroxide` schema before or after extension install, which warrants visible log noise.

After the ownership check passes, the BGW releases extension ownership of any objects inside the `duroxide` schema. This is a no-op on fresh installs (schema is empty). On upgrades from ≤0.1.1 it de-registers the embedded DDL so that future migration DDL can modify those objects.

#### Step 4: `MigrationPolicy::ApplyAll`

- **Fresh 0.2.0 install**: schema is empty → BGW creates all duroxide tables, functions, indexes, triggers, and records all migrations in `_duroxide_migrations` (5 as of 0.2.0).
- **Upgraded from 0.1.1**: all migrations already recorded in `_duroxide_migrations` → `ApplyAll` detects no pending work → no-ops → starts runtime normally.
- **Future duroxide-pg upgrade**: new migration files embedded in the binary are applied automatically without any `ALTER EXTENSION` involvement.

Unknown-migration rejection is always enforced: `duroxide-pg` unconditionally calls `check_no_unknown_migrations()` after both `ApplyAll` and `VerifyOnly`. If the database has a migration row the binary does not recognize, initialization fails with an error (schema is newer than code — indicates a downgrade scenario).

#### Step 5: readiness record via `duroxide._worker_ready`

After `CREATE EXTENSION`, the duroxide schema is empty until the BGW completes its first `ApplyAll`. Similarly, after a binary upgrade that introduces new duroxide migrations, the schema may be incompatible until the BGW applies the pending changes. Calling `df.status()`, `df.result()`, or any function that instantiates a duroxide client during either window fails with a schema-not-initialized error.

Readiness is **not** exposed as a SQL function. It is an internal Rust check called by `get_duroxide_client()` before instantiating the duroxide client. If the check fails, the function raises a PostgreSQL error: `"pg_durable background worker not yet initialized — try again in a moment"`. The readiness state is stored in a single-row table written by the BGW:

```sql
CREATE TABLE IF NOT EXISTS duroxide._worker_ready (
    sentinel        BOOLEAN PRIMARY KEY DEFAULT TRUE,
    CONSTRAINT      only_one_sentinel CHECK (sentinel),
    schema_version  INT NOT NULL,
    initialized_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

After `ApplyAll` succeeds, the BGW writes a row with the current `WORKER_SCHEMA_VERSION` — but only if the row is missing or has a different version. If a row already exists with `schema_version = WORKER_SCHEMA_VERSION`, the write is skipped: the schema state hasn't changed and `initialized_at` should reflect when it was first established, not when the BGW last restarted.

```rust
/// Monotonically increasing schema version written to duroxide._worker_ready
/// after successful BGW initialization. Increment whenever a new binary
/// introduces new duroxide-pg migration scripts or any other BGW-applied
/// duroxide schema change.
const WORKER_SCHEMA_VERSION: i32 = 1;
```

The internal readiness check in `get_duroxide_client()` (SPI, backend process context):

```rust
fn is_worker_ready() -> bool {
    Spi::get_one::<i32>(
        "SELECT schema_version FROM duroxide._worker_ready LIMIT 1"
    )
    .ok()
    .flatten()
    .map(|v| v >= WORKER_SCHEMA_VERSION)
    .unwrap_or(false)  // table missing or no row → not ready
}
```

The integer version is decoupled from the pg_durable release version string:

- A patch release that introduces no duroxide schema changes does not increment `WORKER_SCHEMA_VERSION` — the existing row from the previous binary remains valid.
- When a release does change the duroxide schema (new migration files or any other BGW-applied DDL), `WORKER_SCHEMA_VERSION` is incremented, and the BGW overwrites the row after completing `ApplyAll`.
- Old binaries writing a lower version number will cause `is_worker_ready()` in the new binary to return `false` until the new BGW has run.

Under normal circumstances the table is populated within a few seconds of `CREATE EXTENSION` (bounded by the BGW's extension-detection poll interval, ~5s).

**Backend sessions** are unchanged: `backend_provider_config()` retains `MigrationPolicy::VerifyOnly`. Backend sessions never run DDL.

### `DROP EXTENSION` semantics

Once the BGW has run (any install path):

- The `duroxide` schema itself is extension-owned.
- The tables and functions inside it are **not** extension-owned (fresh installs: created by `ApplyAll` outside the extension transaction; upgrades: released by the BGW before `ApplyAll`).
- `DROP EXTENSION pg_durable` fails: PostgreSQL tries to drop the extension-owned schema but finds non-owned objects inside it.
- `DROP EXTENSION pg_durable CASCADE` succeeds: drops `df.*` (extension-owned), drops the `duroxide` schema (extension-owned), CASCADE drops everything inside the `duroxide` schema.

**CASCADE is always required.** We document this unconditionally and do not qualify it by upgrade path.

### Security model

Two-layer defense against schema squatting:

1. **`CREATE SCHEMA duroxide`** (no `IF NOT EXISTS`): blocks installation if a `duroxide` schema already exists. Prevents pre-installation schema squatting — an attacker cannot pre-create a `duroxide` schema with malicious functions and have the extension silently adopt it.

2. **BGW ownership check via `pg_depend`**: prevents the BGW from applying migrations into a schema it does not own. Handles the residual case where something creates a `duroxide` schema after extension installation but before the BGW first polls.

Together these ensure the BGW only ever writes into state it (via the extension) created and owns.

## Upgrade path: 0.1.1 → 0.2.0

The 0.1.1 → 0.2.0 upgrade script does not touch the duroxide schema. The BGW handles ownership release at startup.

### Fresh 0.2.0 install
- `CREATE EXTENSION pg_durable` creates `df.*` + empty `duroxide` schema.
- BGW polls, detects extension, verifies ownership, runs `ApplyAll` (applies migrations 1–5), writes `duroxide._worker_ready` row (`schema_version = 1`), writes epoch sentinel to `df._worker_epoch`, starts runtime.
- Duroxide tables/functions exist but are not extension-owned.

### Upgrade 0.1.1 → 0.2.0
- `ALTER EXTENSION pg_durable UPDATE TO '0.2.0'` applies the standard 0.2.0 changes to `df.*` (vars owner column, RLS, search_path hardening).
- The duroxide schema is left untouched by the upgrade script.
- BGW (0.2.0 binary) polls, detects extension, verifies ownership (✓ — duroxide schema is extension-owned), releases extension ownership of duroxide objects, runs `ApplyAll` (all 5 migrations already recorded → no-op), writes `duroxide._worker_ready` row (`schema_version = 1`), writes epoch sentinel to `df._worker_epoch`, starts runtime.

The BGW releases extension ownership of objects in the duroxide schema before running `ApplyAll`; otherwise PostgreSQL would reject any migration DDL that modifies them.

### Upgrade test impact

#### Scenario A (schema equivalence)

`test-upgrade.sh`'s `snapshot_schema()` only captures the `df` schema (all queries filter `table_schema = 'df'` or `n.nspname = 'df'`). The duroxide schema is not compared. Scenario A continues to pass without changes.

**Documentation note:** the Scenario A contract ("fresh install matches upgrade path") applies only to the `df` schema. The duroxide schema is not compared, but both paths converge to the same state (objects not extension-owned) after the BGW runs.

#### Scenario B1 (binary backward compatibility)

The 0.2.0 `.so` running against a 0.1.1 schema:
- Ownership check: 0.1.1 has duroxide schema extension-owned → passes.
- Release: de-registers extension ownership of duroxide objects.
- `ApplyAll`: migrations 1–5 already recorded → no-ops.
- Runtime starts normally.

The `df.vars.owner` B1 concern (from PR #53, version-detection at runtime) is orthogonal and handled separately.

#### Readiness polling in tests

Previously `CREATE EXTENSION` populated duroxide synchronously. Now it is asynchronous. Any code that exercises the full workflow lifecycle (calls `df.start()` and waits for completion) must wait for the BGW to finish after creating the extension. Three places handle this:

- **`tests/e2e/sql/00_setup_playground.sql`** — `_e2e_wait_for_worker_ready()` polls `duroxide._worker_ready` directly (checks row existence, not `schema_version` — E2E tests always run against the current version). Called at end of setup and by tests that DROP/CREATE the extension mid-suite. The same pattern is used inline in `sql/00_init.sql`.

- **`scripts/test-upgrade.sh`** — `wait_for_ready()` must work against both 0.1.1 and 0.2.0+ schemas. Probes `information_schema.tables` for `duroxide._worker_ready` at runtime; falls back to `df._worker_epoch` for pre-0.2.0 schemas.

Call `wait_for_ready` after any `CREATE EXTENSION` in scenarios that subsequently call `df.start()`.

## Files changed

### Deleted
- `scripts/gen-duroxide-install-sql.sh`
- `scripts/verify-duroxide-migrations.sh`
- `sql/duroxide_install.sql`
- `sql/duroxide_upstream/` (entire directory, 5 migration copies)

### Modified

| File | What changes |
|------|-------------|
| `src/lib.rs` | Replace `extension_sql_file!("../sql/duroxide_install.sql", name = "duroxide_migrations_install", ...)` with `extension_sql!("CREATE SCHEMA duroxide;", name = "create_duroxide_schema", requires = ["validate_database"])`. Add `WORKER_SCHEMA_VERSION` constant. |
| `src/client.rs` | Add internal `is_worker_ready()` fn (SPI check against `duroxide._worker_ready`). Call it from `get_duroxide_client()` and raise an error if not ready. |
| `src/types.rs` | `worker_provider_config()`: change `MigrationPolicy::VerifyOnly` → `MigrationPolicy::ApplyAll`. Update doc comment. |
| `src/worker.rs` | Add `check_duroxide_schema_owned`, `release_extension_owned_duroxide_objects`, `has_extension_owned_duroxide_objects` async helpers. Insert ownership check + release step in `initialize_duroxide_runtime()` before constructing `PostgresProvider`. After `ApplyAll` succeeds, upsert `duroxide._worker_ready` row with `WORKER_SCHEMA_VERSION`. |
| `sql/pg_durable--0.1.1--0.2.0.sql` | Unchanged — no duroxide DDL added. Included for completeness. |
| `sql/pg_durable--0.1.1.sql` | **Not changed** — kept as upgrade test fixture. Contains extension-owned duroxide DDL as it was when generated, which is correct for B1/B2 testing. |
| `scripts/test-upgrade.sh` | Add `wait_for_ready()` helper (polls `duroxide._worker_ready`). Call it after any `CREATE EXTENSION` in scenarios that subsequently exercise workflow execution. |
| `.github/workflows/ci.yml` | Remove the "Verify duroxide migrations match upstream" step. |
| `.github/copilot-instructions.md` | Remove "Duroxide Migration Sync Workflow" section and related scripts from the scripts table. Update BGW and activity descriptions. |
| `docs/extension_lifecycle.md` | Update sections on extension-managed schema (section 1) and BGW lifecycle (section 3). Remove references to `gen-duroxide-install-sql.sh` and migration verification. Update state machine to show ownership-check and `_worker_ready` write steps. |
| `docs/upgrade-testing.md` | Add duroxide ownership entry to the v0.1.1→v0.2.0 version-specific changes section. Note that both paths converge (objects not extension-owned after BGW runs). Note `wait_for_ready()` requirement in upgrade test infrastructure. |
| `USER_GUIDE.md` | Add note that `DROP EXTENSION pg_durable CASCADE` is always required. Update readiness polling to use `duroxide._worker_ready` directly. |

`duroxide-pg` is a crates.io dependency. No extra provider checkout or CI repository-recursion configuration is required.

## What this enables going forward

To summarize the benefits outlined in Motivation:

- **Duroxide upgrades decouple from pg_durable releases**: adding a new migration to `duroxide-pg` requires no changes to pg_durable extension SQL, upgrade scripts, or the migration-copy sync scripts. The BGW applies it on next startup.
- **Provider swap path**: swapping `duroxide-pg` for a different provider means changing BGW initialization code, not extension DDL.
- **No extension upgrade required for engine fixes**: duroxide bug fixes that involve schema changes are applied automatically by the BGW after the `.so` is updated, even for customers who never run `ALTER EXTENSION UPDATE`.
- **Cleaner `pg_dump`**: duroxide schema objects are not extension-owned (on any install path), so they do not appear as extension members in `pg_dump` output.