# Sync Internals

Sync bridges mutable source tables and an immutable base CSR graph. The current
implementation uses durable trigger logs, backend-local apply, node
tombstones/inserts, filter refresh, tenant refresh, and edge overlays. Background
workers run build, maintenance, or one-shot internal due-job passes. Hosted
schedulers can run due durable sync policy jobs through `graph.run_due_jobs()`,
and installations that allow dynamic workers can launch the same path through
`graph.run_due_jobs_async()`. pgGraph does not broadcast in-place updates to
every backend-local engine.

## Modules

| Module | Responsibility |
|---|---|
| `sync.rs` | Generate trigger functions/triggers and create sync tables |
| `sql_sync.rs` | Parse sync mode, read durable sync log in bounded batches, apply row changes |
| `sql_build.rs` | Maintenance rebuild and vacuum orchestration |
| `sql_jobs.rs` | Durable job rows, hosted job helpers, and dynamic background worker launch for build/maintenance |
| `engine.rs` | Edge mutation buffer, read-only flag, overlay reduction |
| `persistence.rs` | `.pggraph` load/write plus base-only projection manifest discovery |
| `projection/ingest.rs` | Core committed-row to L0 segment publication logic |
| `projection/layered.rs` | Base CSR plus durable-segment plus transaction-delta layered read source |
| `projection/manifest.rs` | Projection manifest metadata and active-generation heartbeats |
| `projection/normalize.rs` | Deterministic committed-mutation normalization and ingest buffer limits |
| `projection/segment.rs` | Durable delta segment format for future layered projections |

## Durable Tables

Bootstrap and sync schema helpers create:

```text
graph._sync_log
  id bigserial primary key
  op char(1)
  table_oid oid
  table_name text
  pk text
  old_pk text
  new_pk text
  properties jsonb
  old_row jsonb
  new_row jsonb
  xid bigint
  needs_vacuum boolean
  error_message text
  created_at timestamptz

graph._sync_buffer
  legacy compatibility buffer

graph._projection_generations
  graph_id uuid
  generation_id bigint
  backend_pid integer
  database_oid oid
  heartbeat_at timestamptz
  expires_at timestamptz
  sync_watermark bigint
  validation_status text
  repair_status text
  is_current boolean
  published_at timestamptz
  retained_until timestamptz

graph._sync_watermarks
  graph_id uuid
  backend_pid integer
  database_oid oid
  applied_sync_id bigint
  heartbeat_at timestamptz
  expires_at timestamptz

graph._jobs
  job_id uuid primary key
  graph_id uuid
  policy_kind text
  enabled boolean
  schedule_interval_secs bigint
  max_runtime_secs bigint
  max_retries integer
  next_run_at timestamptz
  last_run_at timestamptz
  last_status text
  last_error text
  last_sqlstate text

graph._job_runs
  run_id uuid primary key
  job_id uuid
  graph_id uuid
  started_at timestamptz
  finished_at timestamptz
  status text
  sqlstate text
  error text
  rows_applied bigint
  retry_count integer
  worker_identity text
  execution_mode text

graph._sync_policies
  policy_id uuid primary key
  graph_id uuid
  job_id uuid
  schedule_interval_secs bigint
  max_sync_lag_rows bigint
  enabled boolean
  last_run_at timestamptz
  next_run_at timestamptz
  last_status text
  last_error text
```

Replay treats `id`, `op`, and `table_name` as required structural fields. The
durable log keeps `old_pk` and `new_pk` nullable because valid operations need
different PK images, while the legacy buffer requires its coalesced old/new PK
fields before replay.

For tables with a registered `tenant_column`, durable sync replay reads old and
new tenant values from captured row images when available. Tenant bitmap
maintenance can then touch only the affected old/new tenant entries; legacy rows
without row images retain the broader compatibility fallback.

## Trigger Capture

Generated triggers capture:

| Operation | Captured data |
|---|---|
| INSERT | new PK and row properties |
| UPDATE | old/new PKs and row images |
| DELETE | old PK and old row |
| TRUNCATE | statement-level table marker; replay uses table membership to tombstone affected nodes |

The trigger layer writes durable rows. It does not update backend-local engines
directly.

## Apply Flow

```mermaid
flowchart TD
  A["capture max graph._sync_log id"] --> B["read_sync_log_entries_after(applied, batch, high_water)"]
  B --> C["guard edge buffer capacity for this batch"]
  C --> D["apply entries with shared catalog context"]
  D --> E{"operation"}
  E --> F["insert node/update resolution delta"]
  E --> G["update properties/filter/tenant"]
  E --> H["tombstone delete"]
  E --> I["edge overlay mutation"]
  F --> J["advance applied_sync_id after each row"]
  G --> J
  H --> J
  I --> J
  J --> K{"more rows up to high-water?"}
  K -->|yes| B
  K -->|no| L["apply bounded legacy buffer batches"]
```

`graph.sync_batch_size` controls the maximum durable sync rows fetched and
replayed in one internal batch. `graph.apply_sync()` still drains the durable log
that was pending when the call started; the bound is an internal memory and
latency control, not a change to the public admin contract.

`graph.query_freshness = 'apply_pending_sync'` is the default and routes topology
reads through the same high-water replay primitive before they execute. The wired
topology-read entrypoints are traversal variants, shortest-path queries,
weighted shortest-path queries, component APIs, and `graph.traverse_search()`
before its traversal phase. `graph.query_freshness = 'off'` keeps the
compatibility/manual catch-up path available. `graph.search()` remains separate
because it reads source-table properties through SQL rather than graph topology.

`graph.sync_health()` is the operator-facing read model for this state. It
combines backend-local replay progress, durable `_sync_log` high-water state,
freshness configuration, trigger health, projection mode, transaction-delta
counts, and overlay pressure into one row for external schedulers and
monitoring checks.

The durable projection metadata table records graph-scoped published generations
and backend heartbeats that keep older generations alive while a backend is
still using them. Cleanup logic can only remove derived projection files after
they are no longer referenced by retained manifests or unexpired
active-generation rows for the same graph. `projection::gc` scans valid
manifest metadata, follows the checksummed current pointer through
`previous_generation_id` for `graph.projection_retention_generations`, treats
any generation with an active heartbeat as protected, and deletes only files
that a manifest lists in `obsolete_files`. Cleanup-intent manifests remain until
temporarily reader-protected obsolete files can be removed. The table is
extension-owned operational state; PostgreSQL
source tables remain the authoritative graph data.

Projection manifests are published under a graph-scoped advisory lock and a
cross-process artifact lock with a temp-file write, file fsync, directory
fsync, atomic rename, final directory fsync, and current-generation
compare-and-swap. Loaders hold a shared generation lock, follow
`projection-current.json`, verify its manifest checksum, and validate the
manifest plus active base, segment, and chunk references before exposing a
generation. Legacy stores without a pointer bootstrap it from the highest
valid final manifest during their first publication. GC ignores unrelated
files and removes abandoned temp files only while holding the exclusive
artifact lock.

Projection segments use a fixed little-endian header with magic bytes, version,
kind, level, direction coverage, source-node range, sync watermark, section row
counts, payload offsets, CRC32 checksum, and reserved bytes that must remain
zero. The loader validates offsets, checksum, row bounds, and section ownership
before decoding edge topology/delete/weight sections or node, resolution,
filter, and tenant sections.

Before committed rows are written to segments, normalization sorts rows into a
stable order, groups them by generation, direction, source, target, and edge
type, cancels net-neutral insert/delete pairs, and lets delete rows win
conflicts that still have a net effect. The bounded normalization buffer
rejects oversized batches before segment encoding starts.
Edge segment writers reject normalized node rows so node and edge deltas remain
separate segment domains.

The core projection ingester filters committed sync rows above the latest
manifest watermark, normalizes edge mutations, writes L0 edge segments by
direction, writes node/resolution/filter/tenant L0 deltas into node segments,
and narrows each segment's source-node range to the rows it actually covers
when that range is known. Mapped relationship source keys are interned into a
deterministically ordered cumulative dictionary, written as a checksummed
artifact, and referenced by projection manifest version 2. It then publishes
durable no-overwrite segment files, validates segment and identity-artifact
reloads, and publishes the next manifest under an artifact-root ingestion
publication lock. The SQL boundary also holds the graph-scoped PostgreSQL
transaction advisory lock across current-manifest loading, deterministic node
allocation, validation, publication, and backend reload. Aborted transaction
rows are ignored, and the manifest watermark advances only after every
referenced artifact succeeds.

Every generated source trigger takes a shared transaction-scoped advisory lock
before it appends a sync row. Durable ingestion and persisted builds use the
corresponding exclusive try-lock before reading source or sync state. Active
source writers therefore make the graph operation fail with SQLSTATE `55P03`;
new source writers wait behind an operation that already owns the exclusive
lock. This creates a commit-safe snapshot boundary even when sequence
allocation and transaction commit order differ, while shared locks still let
source transactions run concurrently with one another. A same-transaction
guard also rejects durable ingestion after the calling transaction has already
written applicable sync rows. Before either durable apply path acquires the
barrier, it verifies that all applicable row and truncate trigger functions
contain the current shared-lock call. This makes older installed definitions a
fail-closed upgrade condition. Trigger-mode builds reinstall the functions
before reading source tables; the trigger DDL relation locks establish a clean
boundary with transactions that began under an older definition.

The admin-only `graph.ingest_projection(max_rows bigint DEFAULT NULL,
max_bytes bigint DEFAULT NULL)` entrypoint reads committed `graph._sync_log`
rows after the current projection manifest watermark, converts rows through the
same registered table, edge, filter, and tenant catalog semantics as
`graph.apply_sync()`, and publishes the resulting L0 generation in the selected
graph's artifact root. The base graph must be persisted, such as by building
with `graph.persist_on_build = on`; its current manifest identifies the exact
immutable base used by the new generation. Scheduled maintenance treats a
missing persisted artifact as a no-op so existing in-memory sync maintenance
keeps its behavior.

Trigger installation uses the union of registered node relations and mapped
relationship source relations. For a standalone relationship table, replay
resolves the source and target from the registered endpoint columns and uses
the relationship table's primary key as the canonical identity. For an
FK-style edge stored on a registered node table, replay continues to use the
node primary key as the source and the mapped foreign-key column as the target.
For every standalone mapping, node deletion and primary-key changes fail with
rebuild guidance. PostgreSQL permits deferred foreign keys to survive an
endpoint delete/reinsert transaction, so foreign-key presence alone cannot
prove that an existing relationship row should move to the replacement node
slot. Exact target-table resolution never falls back to another registered
table; source fallback without a known table succeeds only when one registered
table matches the endpoint key.

The layered projection runtime merges base CSR neighbors, durable edge
insert/delete/weight segments, durable node visibility and tenant membership
deltas, manifest-referenced base chunk replacements, any remaining
backend-local compatibility edge overlays, and transaction-local edge deltas in
a fixed order. Base CSR is read first, base chunks replace their covered
source-node ranges, durable tombstones suppress older base or segment edges,
later durable inserts can reactivate an edge, duplicate edges are suppressed,
legacy `engine.edge_buffer` changes remain visible when present, and
transaction-local deltas are applied last for read-your-own-writes semantics.
When a loaded projection manifest references durable segments, traversal,
unweighted shortest path, weighted shortest path, connected components, and GQL
relationship expansion select this layered runtime. `csr_readonly` mode and
base-only manifests keep the clean CSR fast path and avoid segment lookup.

Dirty base CSR ranges are repaired by publishing immutable base chunk files and
referencing them from the next projection manifest generation. A base chunk is a
full replacement for an inclusive/exclusive source-node range and carries its
own checksum plus dirty source/edge counters. Publishing a replacement manifest
does not remove older chunk files, so older generations remain readable until
generation-aware cleanup makes them obsolete. If a chunk checksum fails,
targeted repair rewrites the corrupted range into a new generation.

`projection::recovery` validates the latest active manifest, referenced
segments, and base chunks before deciding whether a projection is healthy,
repairable by chunk rewrite, or requires a full rebuild. Full rebuild repair
moves the latest final manifest aside before calling the persisted maintenance
rebuild path, then publishes a higher base-only generation and reloads it so
future backends do not select the corrupt manifest again.

Compaction reduces durable segment fanout by publishing a new generation with
higher-level segment files that preserve the prior layered output. L0 segments
compact to L1, L1 segments compact to L2, and delete/tombstone precedence is
preserved by comparing the final layered view against the base CSR for the
affected source ranges. When segment pressure crosses the configured dirty
chunk threshold, compaction can publish base chunk replacements instead of more
segments. The admin-facing `graph.projection_compact()` function runs one
bounded compaction pass. Budget failures abort before manifest publication,
leaving the previous generation current and readable.

When a logical `.pggraph` path is loaded, the loader takes the generation reader
lock and follows the checksummed `projection-current.json` pointer to one
immutable manifest. That manifest selects the exact generation-specific base;
the logical path is used only as a compatibility fallback when projection
metadata is absent. The engine installs only a manifest whose referenced base
matches the current artifact format version and CRC32 checksum. The active read
path remains CSR for base-only manifests unless the persisted build-mode
sidecar says the graph was built as
`mutable_overlay`. Segment-backed manifests are installed with mutable-overlay
read mode so public read APIs consume durable L0/L1/L2 segment deltas after
reload. Segment format v3 stores registered filter deltas as tagged,
self-contained values: signed numeric, boolean, UTF-8 text, date,
timestamp-with-time-zone, UUID, and SQL `NULL`. Text is stored as text rather
than as a backend-local dictionary token, then interned when the manifest is
installed. A filter tombstone remains distinct from SQL `NULL`. The loader
validates each tag, payload length, UTF-8 string, registered column, and column
domain before switching the engine to the new manifest. Filter node identifiers
must be within the loaded projection's supported node range, and the base filter
index's storage and text-dictionary arrays must agree before any segment value
is applied. An invalid value or malformed index leaves the prior engine state
unchanged.

Projection segment v6 stores the exact UTF-8 primary key beside its table OID
and the exact UTF-8 tenant identifier beside its hash. A batch-wide planner
allocates new node slots contiguously before it emits topology, so relationships
can target nodes introduced later in the same committed batch. Identity-stable
tenant updates retain their existing slot, while insert/delete lifecycles keep
any allocated slot as an inactive tombstone. Existing slots cannot change
identity, and edge endpoints are validated against the final staged node count.
The planner reads a fresh persisted base-plus-manifest snapshot while holding
the graph publication lock; transaction-local serving slots therefore cannot
change durable allocation order or create gaps after reload.
The loader can read v5 resolution rows only when they verify an existing base
node. A v5 tenant delta is rejected with rebuild guidance because its hash is
not sufficient to reconstruct exact membership. Segment format v6 keys
topology, tombstone, and weight rows by optional stable
relationship identity, which preserves equal-endpoint parallel relationships
through layered reads, compaction, and dirty-range chunk replacement. After an
upgrade from an older or identity-incomplete segment format, rebuild the graph
from its authoritative PostgreSQL tables before serving the mutable projection. A clean
`graph.build()` publishes a compatible base and manifest; old derived segment
files can then be removed by the normal generation cleanup path.

Manifest version 2 adds the cumulative post-build relationship-identity
dictionary reference. The loader validates its checksum, declared slot count,
reserved empty slot, uniqueness, and base-dictionary prefix before installing
segments. Segment relationship IDs outside that dictionary fail closed. GC
protects identity artifacts referenced by retained manifests and accounts for
their bytes in projection status.

Ingestion generations retain all segment, base-chunk, and obsolete-file
references from the preceding manifest before appending new segments. A sync
batch that cannot produce a durable row does not advance the watermark.
Incremental `TRUNCATE` currently fails with rebuild guidance rather than
publishing an empty generation, so later generations cannot silently omit a
committed source-table change.

Before publishing a candidate manifest, ingestion loads the persisted base into
a fresh engine and installs every candidate segment and relationship identity
artifact. Only a candidate that passes this semantic reload can become current.
The serving backend is replaced only by a fully loaded engine after publication;
a failed validation leaves both the previous manifest and serving state intact.
Inactive historical node identities remain available for relationship
tombstones, so deleting an endpoint before deleting its mapped relationship row
does not prevent the relationship deletion from being replayed.

The engine keeps base manifest generation and sync watermark metadata
backend-locally, and `graph.active_generation_count()` exposes the selected
graph's active heartbeat count so operators can see whether concurrent backends
are retaining projection generations for that graph.
The SQL `graph.status()` table is already at pgrx's tuple-return arity limit, so
durable projection diagnostics live in `graph.projection_status()`. That row is
computed by `projection::status` from the latest manifest, persisted operation
timestamps, and referenced artifact metadata. `graph.sync_health()` uses the
metadata-only status collector so scheduler polling does not decode every
durable projection segment; full artifact validation remains on
`graph.projection_status()` and repair paths.

Scheduler ownership intentionally stays outside the extension. PostgreSQL
background-worker lifecycle, crash recovery, privilege boundaries, and cadence
controls belong to pg_cron, Kubernetes CronJobs, systemd timers, Docker init
SQL, or application schedulers; the stable pgGraph boundary is the admin-only
`graph.run_scheduled_maintenance()` call.

## Node Changes

Mmap-backed node stores cannot be mutated. Before node mutation, the engine
materializes node data into owned arrays. Inserted nodes are appended and added
to the indexed `resolution_delta`; deleted nodes are tombstoned through
`NodeStore`.

## Edge Changes

The base `EdgeStore` remains immutable. For persisted `mutable_overlay` graphs,
`graph.apply_sync()` publishes committed sync edge mutations into durable L0
projection segments, reloads the latest manifest, and records the applied
watermark. A fresh backend can load the persisted graph artifact, ingest the
committed sync rows, and observe the new durable edge segment without a full
rebuild. `csr_readonly` graphs and non-persisted graphs keep the clean CSR path
unless an explicit legacy overlay path is needed.

Transaction-local edge deltas live in `TxGraphDelta`. Overlay-aware graph
algorithms reduce durable segments, any remaining backend-local compatibility
overlays, and transaction-local deltas into insert and delete overlays for the
selected direction, with transaction-local deltas applied last for
read-your-own-writes semantics. Traversal, unweighted shortest path, weighted
shortest path, connected components, and read-only GQL relationship expansion
consume the shared neighbor-source abstraction.

When the legacy edge buffer reaches `graph.edge_buffer_size`, the engine enters
read-only mode with `read_only_reason = 'edge_buffer_full'` and returns
`EdgeBufferFull`. Later sync mutations against an already read-only engine
return `ReadOnly` with the stored reason.

## Filter And Tenant Refresh

`sql_sync.rs` refreshes registered filter values from authoritative new row
images and publishes exact typed values into durable projection segments for
persisted `mutable_overlay` graphs. Reload applies those values to a staged
filter index before the manifest becomes active. Filter-only rows do not change
node lifecycle state. Tenant membership is a `HashMap<String, RoaringBitmap>`
from tenant value to node indices.

## Maintenance Rebuild

Maintenance applies pending sync state and rebuilds from source tables. This
folds:

| State | Folded into |
|---|---|
| Tombstones | Fresh NodeStore without deleted rows |
| Edge overlays | Fresh CSR stores |
| Resolution delta | Fresh finalized ResolutionIndex |
| Filter changes | Fresh FilterIndex |
| Tenant membership | Fresh tenant bitmaps |

## Sync-Log Retention

`graph._sync_log` rows are never deleted outside of `sql_sync::prune_sync_log`
and `sql_sync::prune_sync_log_if_safe`, called only from
`sql_build::execute_maintenance_rebuild_with_progress` (both the foreground
`graph.maintenance()` path and the background maintenance worker path).
`graph.apply_sync()` never deletes rows.

Backend-local `applied_sync_id` (`csr_readonly` mode's replay progress) is
otherwise invisible outside the owning backend, so a naive
`DELETE ... WHERE id < X` could remove a row a slower or idle backend has not
replayed yet. `sql_sync::record_sync_watermark_heartbeat` registers each
backend's `applied_sync_id` into `graph._sync_watermarks` (TTL
`SYNC_WATERMARK_HEARTBEAT_TTL`, currently 300s) whenever
`sql_facade::admin::refreshed_engine_status` runs — the shared path behind
`graph.status()` and `graph.sync_health()` — and only when
`current_sync_mode() == SyncMode::Trigger`, since a `manual`-mode graph's
`applied_sync_id` never advances past 0.

`sql_sync::sync_watermark_diagnostics` computes the safe floor as the minimum
of the durable projection's own `sync_watermark` (for a persisted
`mutable_overlay` graph) and the minimum `applied_sync_id` across all
currently unexpired heartbeats. `compute_sync_log_retention_floor` (a pure
function, unit- and proptest-covered independent of SPI) implements the
bootstrap safety rule: `(None, None) => None`, never a default floor of `0`
or any other guess. `sql_sync::prune_sync_log` deletes
`graph._sync_log` rows with `id <= floor` (inclusive: a row exactly at the
floor is, by construction, already applied by everyone counted in the
computation) scoped to the selected graph's registered table oids via
`SyncReplayContext::applicable_table_oids`, matching the same scoping
`read_sync_log_entries_after` already uses.

Every successful prune records its floor into
`graph._graphs.sync_log_pruned_before_id` as a running maximum
(`sql_sync::record_sync_log_prune_floor`). `apply_sync_to_high_watermark`
checks `sql_sync::ensure_sync_replay_not_pruned(applied_sync_id)` before
doing any work — the single shared entry point for both explicit
`graph.apply_sync()` (via `apply_sync_internal`) and the `apply_pending_sync`
query-freshness auto-apply path (`sql_facade::runtime::ensure_current_graph_for_query`).
A backend whose `applied_sync_id` predates a completed prune fails closed
with `GraphError::SyncLogPruned` (`PG022`) instead of silently replaying past
a gap. `applied_sync_id == 0` is exempt from this check — a backend that has
never applied anything is not blocked by a prior prune.

See: `todo/full-graph-engine/12-sync-log-retention-plan.md` for the original
design and `todo/progress.md`'s 2026-07-17 entry for the implementation
record.

## Status Interaction

Runtime status and graph validation refresh:

| Field | Source |
|---|---|
| `pending_sync_rows` | Count of sync log rows above `applied_sync_id` |
| `disabled_trigger_count` | Catalog inspection of disabled graph triggers |
| `schema_state` | Current catalog/schema drift validation |
| `needs_vacuum` | Edge/tombstone overlay state |
| `needs_rebuild` | Catalog/schema drift |

## Current Boundaries

| Boundary | Current code behavior |
|---|---|
| WAL sync mode | Reserved and rejected for active use |
| Query-time hidden catch-up | Queries do not silently apply all pending durable sync rows |
| CSR mutation | Not supported; use overlays then rebuild |
| Cross-backend engine sync | Backend-local; persisted files and source tables coordinate state |
| Sync-log retention | Pruned only from `graph.maintenance()`/`run_scheduled_maintenance()`, only below a floor backed by active backend heartbeats or the durable projection watermark; never by `graph.apply_sync()` |