# v0.84.0 — Catalog, Privilege, and Upgrade Integrity

> **Status:** Planned
> **Scope:** Large
> **Releases after:** [v0.83.0](v0.83.0.md) — but shares no subsystem with
> [v0.82.0](v0.82.0.md)/[v0.83.0](v0.83.0.md) and may be developed in parallel
> **Driven by:** August 2026 pre-scaling hardening review
> **Renumbered:** previously planned as v0.81.3. Revoking mutating functions
> from `PUBLIC` and converting or removing `pgtrickle.migrate()` will break
> existing deployments and scripts; that is a minor-release change.

## Theme

Make fresh installs, upgraded installs, documented role setups, backup/restore,
and every mutating SQL API agree on one enforceable contract. This release
closes lifecycle and release-engineering gaps before v0.82 introduces another
process and a larger privilege surface.

## Non-Goals

- No trusted-extension conversion.
- No new lifecycle APIs.
- No new backup format or external control plane.

## Items

### CAT-81-1: Canonical Fresh-Install and Upgrade Schema

Restore supported migration-era objects missing from the bootstrap schema,
including snapshot/subscription catalogs and view-evolution columns. Ship an
idempotent repair migration for affected installations.

Generate normalized live-catalog manifests for fresh and upgraded databases.
Compare relations, columns, types, defaults, constraints, indexes, function
identity arguments, return types, volatility, strictness, parallel safety,
owners, `prosecdef`, `proconfig`, and ACLs in both directions. A removed object
or changed signature must fail CI just as an un-migrated addition does.

### CAT-81-2: Explicit SQL Privilege Matrix

Define and enforce ACLs for diagnostics, owner lifecycle operations,
administrative controls, background/event-trigger entry points, and internal
functions. Revoke mutating and internal functions from PUBLIC. Require owner or
administrator authorization for stream-table changes, repair, snapshots,
scheduler controls, drain, and restore.

Use narrowly scoped, search-path-pinned SECURITY DEFINER functions only for
internal catalog work. Arbitrary-SQL APIs such as `write_and_refresh` remain
invoker-only. Role tests must execute the documented grants instead of testing a
different privilege model.

### CAT-81-3: Typed Identifier and Snapshot Provenance Safety

Replace ad hoc identifier escaping with the central typed identifier builder.
Snapshot restore/drop must resolve cataloged snapshot identity, verify
provenance, and enforce ownership; an ordinary table must never be accepted as
a snapshot merely because its name exists.

### CAT-81-4: Lifecycle API Correctness

Make bulk alter/drop call typed internal implementations rather than generated,
unsupported, or signature-mismatched SQL. Define atomic or explicit per-item
failure semantics, validate a closed JSON schema, and bound input cardinality.

Clean CDC resources by their cataloged stable names for every supported polling
source type. Removing the final consumer must leave no orphan buffers,
snapshots, sequences, triggers, slots, or publications.

### CAT-81-5: Truthful Logical Backup and Restore

Implement OID rebinding and CDC reconstruction after logical restore, or replace
the current no-op helper with a clear unsupported-state error and mandatory safe
reinitialization. Classify every catalog as durable configuration,
derived/rebuildable state, or intentionally excluded; register all durable user
configuration with `pg_extension_config_dump`.

Restore tests must use deliberately changed relation OIDs and prove capture of
new DML without relying on post-data trigger restoration to hide missing repair.

### CAT-81-6: Real Upgrade and Version Verification

For the newest supported upgrade hop, create populated stream tables and CDC
state with the actual released old binary, then restart on the new binary and
verify pre- and post-upgrade mutations. Current-library execution against old
SQL is not sufficient evidence of binary compatibility.

Make publishing workflows reject a Git tag that differs from Cargo, META,
control, install SQL, extension, runtime function, artifact, and OCI versions.
Convert `pgtrickle.migrate()` into a read-only diagnostic or remove it; it must
never claim to apply an extension migration or advance a private version ledger.

### CAT-81-7: Validated API Contracts and Generated Reference

Centralize finite/range validation and checked integer conversions for direct,
preset, bulk-create, and alter paths. Add catalog constraints as defense in
depth for values such as `max_delta_fraction`, TopK limits, and offsets.

Generate SQL signatures, defaults, volatility, strictness, security mode, ACL
classification, and supported upgrade hops from packaged SQL or a live catalog.
Documentation checks must validate contracts, not merely search for function
names.

## Release Gate

v0.84.0 will not ship until all of the following pass:

- Fresh install and every supported upgrade path produce the same normalized
  catalog manifest.
- Snapshot, subscription, and view-evolution APIs pass on a fresh install and on
  an upgraded install.
- Documented administrator, owner, application, and unrelated roles pass a full
  allow/deny matrix; PUBLIC cannot invoke mutating global controls.
- Quoted, Unicode, whitespace, comment-marker, and semicolon-containing
  identifiers are safe through create, alter, snapshot, restore, and drop.
- Logical restore into changed OIDs rebuilds capture safely and preserves every
  catalog classified as durable.
- A real old binary with populated CDC state upgrades and converges with a fresh
  defining-query result.
- Tag/version mismatch, removed schema objects, signature drift, ACL drift,
  NaN/infinity, and integer narrowing all fail CI before mutation or publish.

No fresh/upgrade schema difference or privilege-boundary ambiguity may be
waived for release.
