# v0.86.0 — Product UX & Transparency

> **Status:** Planned
> **Scope:** Large
> **User promise:** *"I understand what pg_trickle is doing."*
> **Blocked by:** [v0.82.0](v0.82.0.md), [v0.83.0](v0.83.0.md), [v0.84.0](v0.84.0.md), and [v0.85.0](v0.85.0.md)
> **Renumbered:** previously planned as v0.82.0.

## Theme

pg_trickle already has unusually broad SQL coverage, automatic FULL fallback,
DAGs, repair tooling, observability, PgBouncer support and Citus support. The
remaining gap is not capability — it is **legibility**. A user who runs
`create_stream_table()` today cannot easily answer: will this be incremental or
FULL? how expensive is each refresh? how stale is the table right now? what
will this cost my write path?

This release turns the existing machinery into a product surface. Everything
here is user-facing: a standard statistics view and an `explain()` function
that answers the questions users actually ask, in plain language, before and
after they commit to a query.

It also introduces `target_freshness` as an *accepted input* — deliberately
four releases before the closed-loop controller in [v0.90.0](v0.90.0.md) is
asked to honour it. The control that v1.0 freezes as the primary user-facing
knob should have been in users' hands for several releases before the freeze,
not introduced immediately before it.

Work starts only after the v0.82.0–v0.85.0 pre-scaling gates prove the current
frontier, DVM, catalog, privilege, upgrade, scheduler and resource contracts.
Everything below reports on those contracts; none of it may create a parallel
implementation of refresh finalization, error accounting or frontier selection.

## Items

### UX-2: `pgtrickle.explain(stream_table)` — the flagship diagnostic

A single function that answers "what is this thing doing, and is that
reasonable?" in human terms:

```
SELECT * FROM pgtrickle.explain('sales_by_region');
```

```
 Refresh mode:            DIFFERENTIAL
 Estimated changed rows:  2,400
 Dominant cost:           orders → customers join (est. 78% of refresh time)
 Expected refresh time:   ~40 ms
 Current lag:             620 ms
 Next scheduled refresh:  in 4.4 s
 FULL fallback threshold: 50% of base rows changed
 Write-path overhead:     ~3.1 µs per INSERT on orders (trigger CDC)
```

Output is available as a readable text form and as a structured `jsonb` form
(`pgtrickle.explain_json()`) for tooling. It is built from data that already
exists — the cost model summary, refresh history, OpTree classifier and CDC
metrics — rather than new instrumentation.

### UX-3: "Why did AUTO choose FULL?"

Every FULL refresh that occurred under `mode = 'auto'` records a machine-readable
reason code plus a one-sentence explanation, surfaced by `explain()` and by
`pgtrickle.refresh_history`:

| Reason code | Explanation shown to the user |
|-------------|-------------------------------|
| `DELTA_RATIO_EXCEEDED` | 62% of base rows changed; recomputing is cheaper than diffing |
| `CORRELATED_SUBQUERY_DELTA_QUADRATIC` | Correlated subquery would scan the base table once per changed row |
| `CASE_IN_LIST_DVM_DRIFT_FULL_FALLBACK` | CASE/IN-list aggregate shape is not provably incremental |
| `SOURCE_TRUNCATED` | A source table was truncated; the change buffer cannot describe the transition |
| `SCHEMA_CHANGED` | A source column used by the query changed type |
| `FIRST_REFRESH` | Initial population |

The reason codes shipped in v0.80.0 are extended to cover every FULL path, and
a CI test asserts that no code path can trigger FULL without a reason code.

### UX-4: Creation-time warnings for expensive or problematic queries

`create_stream_table()` and `preview_stream_table()` emit `WARNING`s at
creation time, before the user has invested in the object:

- query will always run FULL (with the reason)
- estimated refresh cost exceeds the requested refresh interval
- a source table has RLS enabled (already shipped in v0.79.0; folded into the
  unified warning surface)
- a source table has no primary key or replica identity
- estimated write-path overhead exceeds a configurable threshold
- the stream table would join more than `warn_join_sources` (default 6) sources

Each warning includes a `HINT` with the concrete remediation, and the full set
is returned by `preview_stream_table()` so users can see them without creating
anything.

### UX-5: `pg_stat_pgtrickle` statistics view

A virtual system view modelled on `pg_stat_user_tables`, exposing per-stream-table
cumulative statistics that standard monitoring tools (pgwatch, Datadog,
pg_stat_monitor, Prometheus exporters) can scrape without bespoke queries:

```sql
CREATE VIEW pgtrickle.pg_stat_pgtrickle AS
SELECT
    pgt_id, schema_name, table_name,
    total_refreshes, total_full_refreshes, total_diff_refreshes,
    total_delta_rows_processed,
    avg_refresh_duration_ms, p95_refresh_duration_ms, p99_refresh_duration_ms,
    last_refresh_at, current_lag_ms, target_freshness_ms,
    last_full_reason, last_error, last_error_at
FROM pgtrickle.pgt_cost_model_summary
JOIN pgtrickle.pgt_stream_tables USING (pgt_id);
```

`target_freshness_ms` reflects the declared target from UX-7 and is `NULL` when
none is set. The SLA evaluation columns (`p95_freshness_ms`, `sla_status`) are
added in [v0.90.0](v0.90.0.md) once there is a controller whose behaviour they
can describe.

Counters reset semantics follow PostgreSQL conventions
(`pgtrickle.stat_reset(pgt_id)`, `pgtrickle.stat_reset_all()`).

### UX-6: Freshness and cost in `EXPLAIN`

Extend PostgreSQL's own `EXPLAIN` output for queries that read a stream table
with a pg_trickle annotation line:

```
Seq Scan on sales_by_region  (cost=0.00..18.50 rows=850 width=44)
  pg_trickle: lag 620 ms, last refresh 2026-06-04 10:31:02, mode DIFFERENTIAL
```

Controlled by `pg_trickle.explain_annotations`, **default `off`**. Changing
`EXPLAIN` output by default would break plan-diffing tools, regression suites
and anything that parses `EXPLAIN` text, for a diagnostic most sessions do not
need. Users opt in per session when a number looks wrong.

### UX-7: `target_freshness` as an accepted control

Accept a declared freshness target on create and alter:

```sql
SELECT pgtrickle.create_stream_table(
    'sales_by_region',
    $$SELECT region, SUM(amount) FROM orders GROUP BY region$$,
    target_freshness => '2 seconds'
);

SELECT pgtrickle.alter_stream_table(
    'sales_by_region', target_freshness => '1 minute'
);
```

In this release the target is **declared, stored, reported and validated — not
yet controlled**. It is translated once into the existing schedule and mode
settings, surfaced by `explain()` and `pg_stat_pgtrickle`, and checked for
obvious infeasibility (SLA-5 from the original freshness-SLA plan) so users are
told immediately when a target cannot be met. The closed-loop controller that
owns the derived parameters continuously arrives in [v0.90.0](v0.90.0.md).

Special values: `'on_commit'` (existing IMMEDIATE mode) and `'manual'` (refresh
only when asked).

> There is no `CREATE STREAM TABLE ... WITH (...)` grammar. Declarative DDL was
> dropped from this release deliberately; `pgtrickle.exec_stream_ddl()` remains
> a thin `CREATE/DROP STREAM TABLE ... AS` shim with no option list, and the
> function API is the supported surface.

## Exit criteria

- [ ] `pgtrickle.explain()` returns all seven fields for every supported query
      shape in the DVM support matrix
- [ ] Every FULL refresh in the test suite carries a reason code; CI test asserts
      no reason-code-free FULL path exists
- [ ] Creation-time warnings fire for all six conditions, each with a `HINT`
- [ ] `pg_stat_pgtrickle` documented in `SQL_REFERENCE.md` and covered by the
      `pg_extern` docs lint
- [ ] `EXPLAIN` annotation appears for stream-table scans when enabled, and is
      off by default
- [ ] `target_freshness` accepted, stored, reported and infeasibility-checked;
      documented as "declared now, controlled from v0.90.0"
- [ ] Docs: a "Understanding your stream table" guide built entirely around
      `explain()` output