# v0.69.0 Full Details — DuckLake Sink Reliability & Security

> **Summary:** [v0.69.0.md](v0.69.0.md)
> **Assessment source:** [plans/PLAN_OVERALL_ASSESSMENT_13.md](../plans/PLAN_OVERALL_ASSESSMENT_13.md)

---

## Motivation

After v0.68.0 fixes the most urgent correctness defects, v0.69.0 focuses
entirely on the DuckLake integration surface. The assessment identified five
gaps that cluster around sink reliability and schema safety:

- ARCH-002 / REL-001: sink failures are invisible and unretried — data-lake
  delivery is not trustworthy.
- COR-005: view registration is stale after query-only ALTER — DuckLake clients
  see the old view definition.
- COR-006: snapshot IDs are allocated without locking — concurrent sinks
  could collide.
- SEC-002: schema resolution is unqualified — `search_path` manipulation can
  misdirect catalog writes.
- OBS-001: no health metrics — operators cannot detect degraded sink delivery.

---

## Detailed Implementation

### ARCH-002 / REL-001: Sink Delivery State Machine

**New catalog object:**
```sql
CREATE TABLE pgtrickle.pgt_ducklake_sink_delivery (
    delivery_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    stream_table_id oid     NOT NULL REFERENCES pgtrickle.pgt_stream_tables(pgt_id),
    refresh_id      bigint,
    status          text    NOT NULL
                      CHECK (status IN ('PENDING','WRITING','DELIVERED',
                                        'FAILED_RETRYABLE','FAILED_PERMANENT')),
    attempt_count   int     NOT NULL DEFAULT 0,
    bytes_written   bigint,
    rows_written    bigint,
    started_at      timestamptz NOT NULL DEFAULT now(),
    finished_at     timestamptz,
    last_error      text
);
CREATE INDEX ON pgtrickle.pgt_ducklake_sink_delivery (stream_table_id, started_at DESC);
```

**New GUC:**
- `pg_trickle.ducklake_sink_max_retries` (int, default 3): maximum
  `FAILED_RETRYABLE` attempts before transitioning to `FAILED_PERMANENT`.
- `pg_trickle.ducklake_sink_failure_mode` (enum `warn|error`, default `warn`):
  whether a `FAILED_PERMANENT` row propagates as a PostgreSQL error.

**Files changed:**
- `sql/pg_trickle--0.68.0--0.69.0.sql` — add table, indexes, GUC docs
- `src/ducklake_sink.rs` — rewrite `run_ducklake_sink()` as state machine
- `src/config.rs` — add two new GUCs
- `src/shmem.rs` — add DuckLake sink Prometheus counters
- `tests/e2e_ducklake_tests.rs` — tests for retry, permanent failure, status
  function

---

### COR-005: View Registration on Query-Only ALTER

**Files changed:**
- `src/api/alter.rs` — after `alter_stream_table_query()` returns `Ok`,
  check `st.ducklake_sink_mode.is_some()` and if true call
  `register_ducklake_view()` with the updated query
- `tests/e2e_ducklake_tests.rs` — `test_ducklake_sink_view_updates_on_query_alter`

---

### COR-006: Snapshot ID Advisory Lock

**Files changed:**
- `src/ducklake_sink.rs` — `register_ducklake_data_file()`: add
  `Spi::run("SELECT pg_advisory_xact_lock($1)", &[hashtext_of(table_id)])`
  before `MAX(snapshot_id)` computation
- `tests/e2e_ducklake_tests.rs` — `test_ducklake_sink_concurrent_snapshot_ids`
  (two stream tables pointing at the same `ducklake_sink_table_id`, concurrent
  refresh, no duplicate `snapshot_id`)

---

### SEC-002: Qualified Schema Resolution

**Files changed:**
- `src/config.rs` — add `pg_trickle.ducklake_catalog_schema` GUC
- `src/ducklake_sink.rs` — `ducklake_view_table_exists()` queries
  `pg_class JOIN pg_namespace`; all INSERT/UPDATE statements use
  `format!("{}.ducklake_view", quote_ident(schema))`
- `docs/GUC_CATALOG.md` — document `ducklake_catalog_schema`
- `docs/CONFIGURATION.md` — cross-reference DuckLake schema setup

---

### OBS-001: Sink Health Metrics

**New SQL function:**
```sql
CREATE OR REPLACE FUNCTION pgtrickle.ducklake_sink_status()
RETURNS TABLE (
    stream_table_name    text,
    last_delivery_status text,
    last_delivery_at     timestamptz,
    last_bytes_written   bigint,
    last_rows_written    bigint,
    failed_attempts      int,
    last_error           text
) LANGUAGE sql STABLE SECURITY DEFINER ...;
```

**Files changed:**
- `src/monitor/mod.rs` — add `ducklake_sink_status()` pg_extern
- `src/shmem.rs` — five new atomic counters per-sink
- `monitoring/dashboards/pg_trickle_ducklake.json` — add DuckLake sink
  delivery panel to the Grafana dashboard (or create new dashboard)

---

## Upgrade Notes

The new `pgt_ducklake_sink_delivery` table is created by the migration script.
Existing deployments will have no rows in it until the first post-upgrade
DuckLake sink refresh. No data loss occurs during upgrade. The
`ducklake_catalog_schema` GUC defaults to `main`; if your DuckLake installation
uses a different schema name, set the GUC before the first post-upgrade refresh.

---

## Implementation Status

| ID | Title | Status |
|----|-------|--------|
| ARCH-002 / REL-001 | Sink delivery state machine with retry/backoff | ✅ Done |
| COR-005 | View registration on query-only ALTER | ✅ Done |
| COR-006 | Snapshot ID advisory lock | ✅ Done |
| SEC-002 | Qualified schema resolution (`ducklake_catalog_schema` GUC) | ✅ Done |
| OBS-001 | Sink health metrics (`ducklake_sink_status()` SQL function) | ✅ Done |
| DEP-002 | Dependency policy docs (`docs/DEPENDENCIES.md`) | ✅ Done |

---

## Exit Criteria

- [x] All P0 items ✅ Done
- [x] `just test-unit` passes
- [x] `just lint` passes with zero warnings
- [x] `just check-version-sync` exits 0
- [x] CHANGELOG.md entry written
- [ ] ROADMAP.md v0.69.0 row marked ✅ Released