> **Plain-language companion:** [v0.33.0.md](v0.33.0.md)

## v0.33.0 — Reactive Subscriptions & Zero-Downtime Operations

**Status: Planned.** Derived from [plans/PLAN_OVERALL_ASSESSMENT_3.md](plans/PLAN_OVERALL_ASSESSMENT_3.md) §10.1, §10.8.

> **Release Theme**
> v0.33.0 delivers two long-requested operational capabilities. Reactive
> subscriptions expose stream-table changes as PostgreSQL `NOTIFY` events,
> enabling browser-side reactive UIs and event-driven microservices with
> nothing but a standard PG connection — no Kafka, no Debezium, no Hasura.
> Zero-downtime view evolution adds a shadow-ST mode to `ALTER QUERY` that
> builds the new query's materialisation side-by-side with the live table,
> then swaps atomically — eliminating the operational risk of running
> `ALTER QUERY` on a large production stream table.

---

### Correctness

| ID | Title | Effort | Priority |
|----|-------|--------|----------|
| CORR-1 | Reactive subscription: coalesce NOTIFY storms | S | P0 |

**CORR-1** — The subscribe API must coalesce rapid successive changes into a
single NOTIFY payload (or a "changes pending" signal) when the refresh
interval is shorter than the LISTEN client's poll loop. Implement a
`pg_trickle.notify_coalesce_ms` GUC (default 250 ms) and a per-stream-table
flag that debounces NOTIFY calls.

---

### Ease of Use

| ID | Title | Effort | Priority |
|----|-------|--------|----------|
| UX-1 | `pgtrickle.subscribe(name, channel)` and `unsubscribe()` SQL functions | M | P0 |
| UX-2 | `pg_trickle.notify_coalesce_ms` GUC | XS | P0 |
| UX-3 | `ALTER QUERY` shadow-ST mode (`shadow_build := true` parameter) | L | P1 |
| UX-4 | `pgtrickle.view_evolution_status(name)` monitoring function | S | P1 |
| UX-5 | Documentation: subscribe() quick-start + shadow-ST runbook | M | P1 |

**UX-1 — Reactive subscription API.**
`pgtrickle.subscribe(stream_table TEXT, channel TEXT)` registers a per-stream-
table listener: after every successful differential or full refresh, if the
delta is non-empty, the refresh path emits `pg_notify(channel, payload_jsonb::text)`
within the same transaction. The payload carries `{"name": …, "refresh_id": …,
"inserted_count": N, "deleted_count": N}`. Build on the `pg_notify`
infrastructure already wired by the v0.28.0 outbox. `pgtrickle.unsubscribe(name, channel)`
removes the registration; `pgtrickle.list_subscriptions()` returns all active
registrations. **Schema change:** Yes — new `pgtrickle.pgt_subscriptions`
catalog table.

**UX-3 — Shadow-ST for zero-downtime `ALTER QUERY`.**
Today `ALTER QUERY` triggers a full refresh of the stream table. For tables
with millions of rows, this causes a multi-minute outage during which the
stream table is locked. A `shadow_build := true` parameter to `alter_query()`
creates a parallel stream table `__pgt_shadow_<name>` built from the new query,
refreshes it to convergence in the background without locking the live table,
then atomically swaps the storage tables and drops the shadow. The live table
is readable and writable throughout. The new query goes live at the next refresh
cycle after the swap. **Schema change:** Yes — add `in_shadow_build BOOLEAN` and
`shadow_table_name TEXT` columns to `pgtrickle.pgt_stream_tables`.

---

### Test Coverage

| ID | Title | Effort | Priority |
|----|-------|--------|----------|
| TEST-1 | E2E: subscribe() receives NOTIFY on every non-empty refresh | M | P0 |
| TEST-2 | E2E: NOTIFY coalescing under high-frequency refresh | S | P1 |
| TEST-3 | E2E: shadow-ST ALTER QUERY while reads/writes are in flight | L | P1 |
| TEST-4 | E2E: shadow-ST rollback if new query fails to converge | M | P1 |

---

### Conflicts & Risks

- **UX-3** (shadow-ST) touches the refresh orchestrator and catalog — the
  highest-change-risk modules. Must ship behind a feature flag, stabilised
  with the full TPC-H test suite before removing the flag.
- Shadow-ST competes with the live stream table's change buffer, potentially
  doubling CDC write overhead during the build window. Add a
  `shadow_refresh_throttle_ms` GUC to rate-limit background refreshes.

### Exit Criteria

- [ ] UX-1: `subscribe()` / `unsubscribe()` / `list_subscriptions()` registered; NOTIFY emitted on non-empty refresh; `pgt_subscriptions` catalog table in migration script
- [ ] CORR-1: NOTIFY coalescing tested at 10 Hz refresh; client receives ≤ 1 NOTIFY per `notify_coalesce_ms` window
- [ ] UX-3: Shadow-ST builds in background; swap is atomic; live table readable throughout; rollback on convergence failure documented
- [ ] UX-4: `view_evolution_status()` returns `in_progress | converged | failed` with row counts
- [ ] Extension upgrade path tested (`0.31.0 → 0.32.0`)
- [ ] `just check-version-sync` passes

---