# v0.91.0 — Lifecycle & Schema Evolution

> **Status:** Planned
> **Scope:** Very Large
> **User promise:** *"Production changes don't break my stream tables."*
> **Blocked by:** [v0.90.0](v0.90.0.md)
> **Renumbered:** previously v0.86.0.

## Theme

An extension that works perfectly until the first PostgreSQL major upgrade is
not production-ready. Neither is one that breaks when someone adds a column,
restores a backup, clones a database into staging, or renames a schema.

This release is dedicated entirely to making the boring, unavoidable operations
of a real production database boring for pg_trickle too. Nothing here is a
feature users will brag about; all of it is the reason they will still be
running pg_trickle in three years.

## Items

### LC-1: Safe defining-query replacement

Changing the defining query today means dropping and recreating. Instead,
`pgtrickle.alter_stream_table_query()` gains a classification and swap protocol:

1. Classify the change: **compatible** (same output schema and provably
   compatible incremental plan), **rebuildable** (output schema changes, or the
   plan changes such that existing state is invalid), or **rejected**
2. Compatible changes swap the plan atomically, keeping existing state and
   frontier — no repopulation, no visible gap
3. Rebuildable changes populate a shadow table, then swap under a brief lock,
   so readers never see a partially populated result
4. `pgtrickle.explain_alter('st', $$new query$$)` reports the classification and
   the estimated rebuild cost *before* the user runs it

> This is a function-API change, not new DDL grammar. Declarative
> `ALTER STREAM TABLE ... AS` was dropped from the product arc; the
> `pgtrickle.exec_stream_ddl()` shim is unchanged by this release.

### LC-2: Automatic handling of compatible source-schema changes

DDL event triggers detect source changes and act, controlled by
`pg_trickle.auto_evolve` (default `true` for additive changes only):

**Automatic:**
- `ADD COLUMN` — propagate when the defining query uses `SELECT *`; otherwise
  no-op
- `ADD CONSTRAINT`, `CREATE INDEX`, `DROP INDEX`, `SET STATISTICS`, `COMMENT` —
  no-op
- source table rename, schema move, ownership change — catalog references
  updated in place

**Suspend with a clear diagnosis:**
- `DROP COLUMN` used by the defining query
- `ALTER COLUMN TYPE` on a column used by the defining query
- replica identity or primary key removal on a source

Suspension is loud and actionable: `NOTIFY`, `health_check()` entry, an entry in
refresh history with reason `SCHEMA_INCOMPATIBLE`, and a `HINT` naming the exact
`pgtrickle.alter_stream_table_query()` call that would resolve it. A suspended
stream table stops refreshing but never serves silently wrong data.

### LC-3: Backup, restore and PITR

- `pg_dump`/`pg_restore` round-trip for every catalog object, building on the
  durable/derived catalog classification established in
  [v0.84.0](v0.84.0.md) CAT-81-5, with a restore-time consistency check
- documented and tested PITR behaviour: after a point-in-time restore, frontiers
  are validated against the restored WAL position, and stream tables whose
  frontier is ahead of the restored database are automatically marked for
  reinitialisation rather than diverging silently
- `pg_basebackup` and physical replica promotion covered by E2E tests
- restoring a dump into a database without the extension yields ordinary tables
  and a clear message, never a broken restore

### LC-4: Database cloning and environment copies

`CREATE DATABASE ... TEMPLATE`, dump/restore into staging, and logical copies
are first-class: cloned stream tables detect that they are a clone (database
identity changed), drop stale replication-slot bindings, and either resume with
fresh CDC or suspend with an explicit message. No clone ever consumes the
original's replication slots.

### LC-5: `pgtrickle.preflight_upgrade()`

One function a DBA runs before a PostgreSQL major upgrade or an extension
upgrade, which answers "is this safe?":

```sql
SELECT * FROM pgtrickle.preflight_upgrade();
```

```
 check                            | status | detail
----------------------------------+--------+-------------------------------------------
 extension version supported      | OK     | 0.91.0 → 1.0.0 upgrade path exists
 stream tables suspended          | WARN   | 1 suspended (customer_360) — resolve first
 in-flight refreshes              | OK     | none
 replication slots                | OK     | 3 slots, all active, max lag 1.2 MB
 change buffer backlog            | WARN   | orders backlog 2.1M rows — drain recommended
 catalog integrity                | OK     | 0 orphaned entries
 disk headroom for rebuild        | OK     | 41 GB free, 3.2 GB estimated
```

Backed by a documented drain-and-quiesce procedure
(`pgtrickle.quiesce()` / `pgtrickle.resume_all()`) so an upgrade window can be
entered cleanly.

### LC-6: PostgreSQL major upgrades and extension upgrades

- `pg_upgrade` across major versions covered end to end in CI, including a run
  with active stream tables and a non-empty change buffer
- extension upgrade E2E extended to cover every supported source version to the
  release under test, with the existing cutoff policy documented in place
- downgrade remains unsupported and is enforced with a clear error plus the
  rollback runbook shipped in v0.80.0

### LC-7: Automatic CDC repair

When capture breaks — dropped replication slot, dropped trigger, a source
restored out from under a stream table, WAL removed before it was consumed —
pg_trickle detects it, reports it precisely, and where safe repairs it
automatically by rebuilding capture and reinitialising affected stream tables.
Where automatic repair is unsafe, it suspends and names the manual step. The
existing repair tooling becomes the automatic path rather than a documented
procedure the user has to find.

## Exit criteria

- [ ] `pgtrickle.alter_stream_table_query()` supports compatible and
      rebuildable changes; `explain_alter()` classifies correctly for every DVM
      support matrix entry
- [ ] Additive source DDL flows through with no user action; destructive DDL
      suspends with a named remediation; no path produces silently wrong data
- [ ] PITR, `pg_basebackup`, replica promotion, `pg_dump`/`pg_restore` and
      database cloning each covered by an E2E test
- [ ] `preflight_upgrade()` shipped and referenced from the upgrade runbook
- [ ] `pg_upgrade` major-version test green with active stream tables and a
      non-empty change buffer
- [ ] CDC break-and-repair chaos test: every injected failure is either
      auto-repaired or suspended with an actionable message