# Architecture

> How Supabase ETL initially syncs tables and replicates changes to destinations.

- Canonical HTML: https://supabase.github.io/etl/explanation/architecture/
- Agent-readable Markdown: https://supabase.github.io/etl/explanation/architecture.md
- Source: https://github.com/supabase/etl/blob/main/site/content/docs/explanation/architecture.mdx

Supabase ETL uses **Postgres logical replication** to initially sync published
tables, then replicate subsequent changes to a destination in near real time.

## Overview [#overview]

<Mermaid
  chart="flowchart LR
subgraph Postgres
    WAL[&#x22;WAL + Publication&#x22;]
end

subgraph Pipeline
    Apply[&#x22;Apply Worker&#x22;]
    Sync[&#x22;Table Sync Workers&#x22;]
end

subgraph Storage
    Store[&#x22;Store<br>(State + Schema)&#x22;]
end

subgraph Target
    Dest[&#x22;Destination<br>(Built-ins + Custom)&#x22;]
end

WAL --> Apply
Apply --> Sync
Apply --> Dest
Sync --> Dest
Apply --> Store
Sync --> Store"
/>

## How It Works [#how-it-works]

ETL operates in **two phases**:

### Phase 1: Initial Sync [#phase-1-initial-sync]

When a pipeline starts, it copies all existing data from each table in the publication. Multiple **Table Sync Workers** run in parallel to copy tables concurrently. Each worker:

1. Creates a replication slot to capture a consistent snapshot
2. Copies all rows using Postgres `COPY`
3. Sends rows to the destination via `write_table_rows()`

> **Note**
>
> During this phase, `Begin` and `Commit` events may be delivered multiple times
> because workers consume slots in parallel. This is expected: destinations
> should rely on **per-table event ordering** and **idempotent writes** rather
> than treating transaction markers as a complete global transaction boundary.

### Phase 2: Ongoing Replication [#phase-2-ongoing-replication]

Once tables are copied, the **Apply Worker** handles ongoing replication from
the Postgres WAL. It:

1. Receives change events (inserts, updates, deletes)
2. Batches events for efficiency
3. Sends batches to the destination via `write_events()`

### Schema Changes [#schema-changes]

ETL supports simple column evolution from `ALTER TABLE` and supported
`ALTER PUBLICATION` changes. A source-side event trigger emits internal schema
messages for published permanent tables, ETL stores a new schema snapshot, and
destinations observe the change through a fresh `Relation` event before
following row events. See [Schema Changes](https://supabase.github.io/etl/explanation/schema-changes.md) for
the supported operations and limitations.

## Core Components [#core-components]

### Pipeline [#pipeline]

The **central orchestrator** that manages the entire replication process. It spawns workers, coordinates state transitions, and handles shutdown.

### Destination [#destination]

The `Destination` trait receives bulk rows during initial sync and event batches
during catch-up and ongoing replication. Result handles let a destination distinguish
accepted work from durable work without blocking dispatch. See [Extension
Points](https://supabase.github.io/etl/explanation/traits.md#destination) for the method contract and [Custom
Stores and Destinations](https://supabase.github.io/etl/guides/custom-implementations.md) for an implementation.

### Store [#store]

Three store traits persist the state needed to resume after restarts:

* **StateStore**: Tracks table state, persisted replication checkpoints, and destination table metadata
* **SchemaStore**: Stores versioned table schema information (columns, types, primary keys, snapshot IDs) and prunes obsolete schema versions behind persisted checkpoints while preserving the retained boundary schema and newer versions
* **TableStateLifecycleStore**: Prepares table-copy state, resets table states for resync, and deletes all ETL-owned state when a table leaves the publication

See [Extension Points](https://supabase.github.io/etl/explanation/traits.md#schemastore) for the cache and
durability contracts.

## Delivery Guarantees [#delivery-guarantees]

ETL provides **at-least-once delivery**. If restarts occur, some events may be delivered more than once. This is a deliberate design choice.

### Why Not Exactly-Once? [#why-not-exactly-once]

Exactly-once delivery requires distributed transactions between Postgres and the destination, adding complexity and latency. Instead, ETL optimizes for throughput and simplicity while minimizing duplicates through:

* **Controlled shutdown**: The pipeline attempts to finish in-flight work before its shutdown deadline; interrupted work can be replayed after restart
* **Frequent status updates**: Progress is reported to Postgres regularly, reducing the replay window after restarts

### Handling Duplicates [#handling-duplicates]

Destinations should make writes **idempotent** using the source table's replica
identity or primary key plus ETL's event ordering metadata. For append-style CDC
tables, persist a sequence key derived from `commit_lsn` and `tx_ordinal`. For
current-state tables, upsert by the destination's chosen row key so replayed
events converge to the same state.

The `commit_lsn` and `tx_ordinal` fields on sequenced events provide stable
**ordering and checkpointing**. For example, BigQuery destinations use the pair
to maintain correct event order in destination tables. See [Event
Types](https://supabase.github.io/etl/explanation/events.md#understanding-event-sequence-keys) for details.

## Table States [#table-states]

Each table progresses through these states:

| State            | Set By            | Description                                                                      |
| ---------------- | ----------------- | -------------------------------------------------------------------------------- |
| **Init**         | Pipeline          | Table discovered, ready for initial sync                                         |
| **DataSync**     | Table Sync Worker | Initial table copy in progress                                                   |
| **FinishedCopy** | Table Sync Worker | Initial sync complete                                                            |
| **SyncWait**     | Table Sync Worker | Waiting for Apply Worker to pause (in-memory only)                               |
| **Catchup**      | Apply Worker      | Apply Worker paused; Table Sync Worker catching up to its LSN (in-memory only)   |
| **SyncDone**     | Table Sync Worker | Catch-up complete; durable decoder retained until Apply materializes local state |
| **Ready**        | Apply Worker      | Apply Worker now handles this table exclusively                                  |
| **Errored**      | Either            | Error occurred; contains reason, solution hint, and retry policy                 |