# v0.61.0 — DX, Documentation & Final Pre-1.0 Polish (Full Details)

> **Summary:** [v0.61.0.md](v0.61.0.md)
> **Assessment source:** [plans/PLAN_OVERALL_ASSESSMENT_12.md](../plans/PLAN_OVERALL_ASSESSMENT_12.md)
> **Findings addressed:** E-1, E-2, C-7, C-8, C-9, S-5, Q-4, Q-5, D-1, D-2, F-1, F-2

---

## Developer Experience

### DX-1: health_check() Foreign-Owned Attachment Detection

**Assessment finding:** E-1 (MEDIUM)
**File:** `src/api/diagnostics.rs`, `src/monitor/health.rs`

The 8-check `health_check()` matrix gains a ninth check:
`attachment_owner_check`.

**Logic:** For every stream table, query `pgtrickle.pgt_outbox_config` and
the downstream publication catalogue to find any attachment where the
attaching role differs from the stream table owner.  The result set includes:
`pgt_name`, `attachment_type` (`outbox` or `publication`), `attached_by`,
`owned_by`.

**Output row example:**
```
check_name         | attachment_owner_check
status             | WARNING
detail             | ST "public.orders" has an outbox attached by role "analyst" but owned by "app_owner"
action_required    | Review outbox created by non-owner; consider re-attaching as owner or granting explicit permissions
```

This check is most useful immediately after upgrading from a version below
v0.58.0 to audit any pre-existing ownership mismatches.

### DX-2: SQL_REFERENCE.md Completeness Reconciliation

**Assessment finding:** E-2 (LOW)
**File:** `docs/SQL_REFERENCE.md`, `src/api/*.rs`

A new `scripts/gen_sql_reference.py` script compares `#[pg_extern]` symbols
in the source with entries in `docs/SQL_REFERENCE.md` and produces a diff.

Functions categorised as internal API:
- Functions in `src/api/metrics_ext.rs` that are DBA-facing but not in the
  reference are promoted to a new "Advanced Diagnostics" section.
- Functions intended only for internal use (e.g., `__pgt_trigger_fn_*`) are
  annotated with `#[pg_extern(sql = "")]` so they produce no SQL declaration
  and do not appear in `\df pgtrickle.*` output.

The script is wired into CI as a check step: adding a new `#[pg_extern]`
without updating the reference fails the linting job.

---

## Correctness Hardening

### COR-7: ctid Invariant Comment in phd1.rs

**Assessment finding:** C-7 (LOW)
**File:** `src/refresh/phd1.rs` (~line 180)

Added:
```rust
// INVARIANT: The `ctid` values captured by the preceding CTE are stable
// within this transaction's snapshot (no concurrent VACUUM or HOT chain
// updates).  Do NOT split this DELETE into a separate transaction or
// sub-transaction — if the snapshot isolation guarantee is lost, use
// __pgt_row_id-based deletion instead.
```

### COR-8: Snapshot Cache Key Secondary Equality Check

**Assessment finding:** C-8 (LOW)
**File:** `src/dvm/diff.rs`

The `SnapshotCacheEntry` struct gains a `fingerprint: String` field alongside
`hash: u64`.  When `get_or_register_snapshot_cte()` finds a hash hit, it also
compares `fingerprint` strings.  On mismatch (collision), the cached entry is
evicted, a shmem counter `pg_trickle_snapshot_cache_collisions_total` is
incremented, and the new CTE is registered.

```rust
struct SnapshotCacheEntry {
    hash: u64,
    fingerprint: String,   // NEW
    cte_name: String,
}
```

### COR-9: DiffContext cte_counter Reset

**Assessment finding:** C-9 (LOW)
**File:** `src/dvm/diff.rs` — `differentiate()` entry point

```rust
pub fn differentiate(&mut self, op: &OpTree, …) -> Result<String> {
    self.cte_counter = 0;  // NEW: reset per differentiation call
    self.diff_node(op, …)
}
```

---

## Security

### SEC-5: Outbox Name Collision Prevention

**Assessment finding:** S-5 (LOW)
**File:** `src/api/outbox.rs` — `outbox_table_name_for`

**Before:**
```rust
raw.chars().take(63).collect()
```

**After:**
```rust
if raw.len() <= 63 {
    raw
} else {
    let hash = blake3::hash(st_name.as_bytes());
    let suffix = &hex::encode(hash.as_bytes())[..8];
    format!("{}_{}", &raw[..54], suffix)
}
```

The `blake3` crate is already a dependency via `src/hash.rs`.

---

## Code Quality

### QUAL-4: src/dvm/parser/sublinks.rs Decomposition

**Assessment finding:** Q-4 (LOW)

The 7 065-line `src/dvm/parser/sublinks.rs` is split into five focused modules:

```
src/dvm/parser/
├── sublinks/
│   ├── mod.rs          (public re-exports, ~200 lines)
│   ├── exists.rs       (EXISTS/NOT EXISTS → SemiJoin/AntiJoin, ~800 lines)
│   ├── scalar.rs       (scalar SubLink hoisting, ~600 lines)
│   ├── in_list.rs      (IN/NOT IN → SemiJoin/AntiJoin, multi-column, NULL-safety, ~1400 lines)
│   └── having.rs       (HAVING aggregate rewrites, ~400 lines)
```

Each sub-module retains the full existing test coverage from its predecessor.

### QUAL-5: Brittle split().nth() Test Pattern Fixed

**Assessment finding:** Q-5 (LOW)
**File:** `src/dvm/operators/lateral_subquery.rs`

**Before:**
```rust
let inner_branch = sql.split("lat_sq_changed").nth(1).unwrap_or("");
// assertion on inner_branch — silently vacuous if split fails
```

**After:**
```rust
let parts: Vec<&str> = sql.splitn(2, "lat_sq_changed").collect();
assert_eq!(parts.len(), 2, "expected 'lat_sq_changed' marker in generated SQL");
let inner_branch = parts[1];
```

---

## Documentation

### DOC-1: Three Foundational ADRs

**Assessment finding:** D-1 (LOW)
**Files:** `plans/adrs/ADR-001.md`, `plans/adrs/ADR-002.md`,
`plans/adrs/ADR-003.md`, `plans/adrs/PLAN_ADRS.md` (updated to ACTIVE)

**ADR-001: Trigger-Based CDC over Logical Replication** covers:
- Decision: default CDC path uses `AFTER` row/statement triggers.
- Rationale: single-transaction atomicity (change captured and committed
  atomically with source write), no replication slot lifecycle management,
  simpler failure modes (no slot lag accumulation).
- Trade-offs acknowledged: higher write amplification vs. logical replication;
  mitigation via statement-level triggers.
- WAL-based CDC as the opt-in alternative for minimal write overhead.

**ADR-002: Z-Set / Multiset Formalism** covers:
- Decision: differential engine uses Z-sets (signed integer multiplicities).
- Rationale: SQL deletes require negative multiplicities; true multisets
  (non-negative only) cannot represent "undo" operations.
- Implications for operator correctness: all 22 operator files must maintain
  the invariant that the sum of multiplicities for any given row is either 0
  (row absent) or 1 (row present) after each refresh cycle.

**ADR-003: EC-01 Join-Correctness Invariant** covers:
- The phantom-row problem in outer-join delta rules.
- The R₀ snapshot-splitting fix (v0.38.0).
- The cross-cycle reconciliation invariant (phd1.rs).
- Conditions under which the PHD1 pass must run and the guarantees it provides.

### DOC-2: LIMITATIONS.md — Multi-Column NOT IN with Nullable Columns

**Assessment finding:** D-2 (LOW)
**File:** `docs/LIMITATIONS.md`

New subsection "Multi-column NOT IN with nullable columns":

> When using `(col1, col2) NOT IN (SELECT ...)` and any of `col1`, `col2`, or
> the subquery's corresponding output columns can be `NULL`, the DVM parser
> cannot safely rewrite to an anti-join.  The delta is instead computed via
> subquery-based execution, which is correct but slower.
>
> To restore anti-join performance, add explicit `IS NOT NULL` predicates:
> ```sql
> WHERE col1 IS NOT NULL AND col2 IS NOT NULL
> AND (col1, col2) NOT IN (SELECT x, y FROM t WHERE x IS NOT NULL AND y IS NOT NULL)
> ```

---

## Features

### FEAT-1: SEARCH / CYCLE Clause — Clear Error

**Assessment finding:** F-2 (LOW)
**File:** `src/dvm/parser/mod.rs`

Added detection for `WITH RECURSIVE … SEARCH BREADTH FIRST BY …` and
`… CYCLE … SET … USING …` clauses during DVM parsing.  When detected:

```
ERROR: pg_trickle: SEARCH and CYCLE clauses are not yet supported in
differential mode (planned for v1.1.0).
HINT: Remove the SEARCH/CYCLE clause from the recursive CTE definition, or use
REFRESH MODE FULL if the query requires it.
```

### FEAT-2: LATERAL + DIFFERENTIAL Documentation

**Assessment finding:** F-1 (MEDIUM)
**Files:** `docs/DVM_OPERATORS.md`, `docs/LIMITATIONS.md`

New section "LATERAL Joins and DIFFERENTIAL Mode":

| LATERAL pattern | DIFFERENTIAL support |
|-----------------|---------------------|
| `LATERAL` SRF (set-returning function) | ✅ Full support |
| `LATERAL` subquery with simple filter | ✅ Full support |
| `LEFT JOIN LATERAL` with outer-applied subquery | ⚠️ Supported; uses R₀ snapshot for outer side (EC-01 semantics) |
| `LATERAL` with correlated aggregate over LEFT source | ❌ Falls back to FULL; UnsupportedFeature hint |
| `LATERAL` referencing a volatile SRF | ❌ FULL only (volatility check at parse time) |

---

## Upgrade Notes

No SQL schema changes.  No `ALTER EXTENSION` migration is required.

After upgrading, run `SELECT * FROM pgtrickle.health_check()` to check the new
`attachment_owner_check` row.  If it reports `WARNING`, review any pre-v0.58.0
outbox or publication attachments that were created by non-owners.