> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ilyama.golain.io/llms.txt
> Use this file to discover all available pages before exploring further.

# How edge SQLite sync works

> End-to-end pipeline from application writes to queryable cloud data.

Edge SQLite sync moves data from a **local SQLite database** on a device into **Golain-managed storage** in the cloud. The edge agent captures changes; the cloud governs schema and materializes approved rows into relational state tables or telemetry hypertables.

## What gets replicated

| Source                                              | Mechanism                                                                               | Cloud destination                                                                                          | Pipeline                        |
| --------------------------------------------------- | --------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------- | ------------------------------- |
| Time-series tables (sensor readings)                | Cursor-based batches → `sync/telemetry/batch`                                           | Project-scoped **`edge_ts_*`** hypertable by source table (fleet-scoped if you configure fleet coalescing) | **A** — telemetry ingest stream |
| Mutable state tables (settings, enrollment, config) | SQLite triggers → journal → `sync/rows/batch`                                           | Relational **`edge_state_*`** table after schema approval                                                  | **B** — state ingest queue      |
| Mixed verification DBs                              | `vrf_*_events` + `vrf_*_state` over row batches, `vrf_*_metrics` over telemetry batches | One lineage per source table, with telemetry and state materialized independently                          | **A + B**                       |

The cloud does **not** store a replica of your SQLite file. It stores **governed projections** you approve column-by-column.

## Pipeline stages

### 1. Application writes (device)

Your application reads and writes a normal SQLite database (`source.db`). Omega's module does not require application code changes beyond using SQLite.

### 2. Capture (device)

On startup the `sqlite-replication` module:

1. Discovers user tables (excludes `sqlite_*` and internal `__omega_*` tables).
2. Installs `AFTER INSERT/UPDATE/DELETE` triggers that append to `__omega_journal` (Pipeline B tables).
3. Computes a **schema fingerprint** (SHA-256 of canonical column metadata).

For telemetry tables (Pipeline A), the agent maintains a cursor on the highest `event_ts` included in an accepted batch. For state tables (Pipeline B), each journal entry records `commit_seq`, operation, primary key JSON, full row JSON (except deletes), and `schema_hash`.

### 3. Publish (device → MQTT)

Every **5 seconds** (or when batch size reaches **512 KiB**), the module publishes at **QoS 1**:

* Pipeline A: `sync/telemetry/batch` with `batch_id` (UUIDv7) and monotonic `batch_seq`
* Pipeline B: `sync/rows/batch` with `batch_id`, `journal_epoch`, and row operations

The device **never** embeds `org_id`, `project_id`, or `device_id` in payloads — the broker derives tenant scope from the mTLS session.

### 4. Broker ingest (cloud)

The MQTT broker:

* Authenticates the device (mTLS or username/password).
* Enforces topic ACLs (devices cannot publish on `sync/ingest/control`).
* Strips any untrusted tenant fields from JSON.
* Publishes to **`edge_ingress_exchange`** with routing keys for the target pipeline:

| MQTT topic suffix      | Exchange event                        | Consumer                            |
| ---------------------- | ------------------------------------- | ----------------------------------- |
| `sync/telemetry/batch` | `edge.telemetry_segment.received.v1`  | Edge worker telemetry ingest stream |
| `sync/rows/batch`      | `edge.state_segment.received.v1`      | Edge worker state ingest queue      |
| `sync/schema/observe`  | `edge.schema_observation.received.v2` | Edge worker meta queue              |
| `sync/ingest/ack`      | `edge.ingest_ack.received.v2`         | Edge worker meta queue              |
| `sync/ingest/request`  | `edge.ingest_request.received.v2`     | Edge worker meta queue              |

Wire details: [ilyama edge-client-guide](https://github.com/golain-io/ilyama/blob/main/docs/knowledge/edge-client-guide.md).

### 5. Edge worker intake (cloud)

The **edge worker** (not the integration worker) owns all ingest and governance:

1. **Dedupes** on `(device_id, batch_id)`.
2. Records segment metadata in the batch ledger.
3. **Upserts a lineage** — one row per `(device_id, source_table)`.

Pipeline A batches with an approved schema publish to the telemetry ready stream for the telemetry ingester. Pipeline B batches on an active lineage materialize to `edge_state_*` directly.

### 6. Schema classification (cloud)

When a batch carries a **new `schema_hash`**:

| Classification        | Typical meaning                         | Lineage         | Rows                            |
| --------------------- | --------------------------------------- | --------------- | ------------------------------- |
| `compatible_additive` | Safe additive change (policy-dependent) | May stay active | Materialize or stage per policy |
| `compatible_mapped`   | Matches existing bindings               | Active          | Materialize                     |
| `ambiguous`           | First sight or unclear mapping          | **Paused**      | **Staged**                      |
| `breaking`            | Incompatible change                     | **Paused**      | **Staged**                      |

Default production policy treats the **first schema** for a table as **ambiguous** → schema review required.

On schema miss, **both pipelines stage first, then advance governance**:

* **Pipeline A (telemetry):** rows land in `edge_staged_rows`, then lineage classification and review queue run.
* **Pipeline B (rows/batch):** paused lineages stage incoming batches with `staging_reason=schema_review`; active lineages follow the live materialization path.

The device does **not** receive `pause_lineage` for review-only pauses — it keeps publishing while the cloud holds rows.

### 7. Staging (cloud)

While lineage is **paused** or materialization is blocked, row bodies are stored in **`edge_staged_rows`** (Timescale) with a reason:

| `staging_reason`         | Meaning                                                      |
| ------------------------ | ------------------------------------------------------------ |
| `schema_review`          | Waiting for operator approval                                |
| `lineage_paused`         | Volume backpressure or circuit breaker                       |
| `materialization_failed` | Transient materialization write error before pause confirmed |

Staging is **server-side**. The device may continue publishing during schema review; the cloud holds rows until you approve.

### 8. Schema review (operator)

An operator:

1. **Claims** the review (`queued` → `in_review`).
2. Inspects staged sample rows.
3. **Approves** with **column actions** (`mirror`, `map`, `ignore`, …) or **rejects**.

On approve the worker provisions the target DDL, creates **column bindings**, inserts a **replay intent**, and sets lineage **active**.

### 9. Replay and materialization (cloud)

* **Replay** drains staged rows into the provisioned telemetry or state target.
* **Live batches** on an **active** lineage write directly to `edge_ts_*` or `edge_state_*`.

For the recommended project-coalesced flow, telemetry tables materialize into `edge_ts_p{project8}_{source_table}`. Golain enables Timescale compression on these hypertables with a **1 day** compression policy.

### 10. Downlink control (cloud → device)

When the edge worker needs to pause ingestion, grant a presigned URL, or push cloud-authoritative state, it stages **`EdgeIngestControlRequestedV2`** on the MQTT downlink exchange. The broker delivers the JSON body on `sync/ingest/control` at QoS 1.

→ [Downlink control](/edge/data-sync/downlink-control)

### 11. Query (operator / product)

Query materialized data via:

* `GET .../edge/lineages/{id}/mirror-rows`
* platform-tui **Written** view
* QueryScript against the lineage's `edge_ts_*` hypertable for telemetry

→ [Querying synced data](/edge/data-sync/querying-data)

## Two kinds of "pause"

Operators often confuse these — they behave differently:

| Pause cause                                           | Cloud lineage | Device receives `pause_lineage`? | Device should keep sending? |
| ----------------------------------------------------- | ------------- | -------------------------------- | --------------------------- |
| **Schema review** (ambiguous/breaking)                | `paused`      | **No**                           | **Yes** — server stages     |
| **Volume backpressure** (staged cap exceeded)         | `paused`      | **Yes**                          | **No** — buffer locally     |
| **Materialization circuit breaker** (repeated errors) | `paused`      | **Yes**                          | **No** — buffer locally     |

Schema approval sets lineage **active** in the database but does **not** automatically send `resume_lineage` unless the device was paused by a downlink control message.

→ [Backpressure](/edge/data-sync/backpressure)

## Data stores (cloud)

| Store                                       | Purpose                                                            | Operator-visible?                       |
| ------------------------------------------- | ------------------------------------------------------------------ | --------------------------------------- |
| `edge_export_segments` / batch dedup ledger | Batch metadata and idempotency                                     | Via API/TUI lineage detail              |
| `edge_staged_rows`                          | Row bodies awaiting materialization                                | Staged rows API / TUI **`d`**           |
| `edge_table_lineages`                       | Lineage status (Postgres OLTP)                                     | Lineages list                           |
| `edge_schema_reviews`                       | Review workflow (OLTP)                                             | Schema reviews list                     |
| `edge_state_*`                              | Materialized relational and cloud-authoritative state rows (TSDB)  | Mirror-rows API / TUI **`w`**           |
| `edge_ts_*`                                 | Materialized telemetry hypertables, one per source table and scope | QueryScript / Data explorer             |
| Device data point events                    | Device-centric telemetry APIs derived from edge writes             | Console widgets, device data point APIs |

## Related pages

* [Lineages and staging](/edge/data-sync/lineages-and-staging)
* [Schema governance](/edge/data-sync/schema-governance)
* [Omega setup](/edge/data-sync/omega-setup)
* [Schema review workflow](/edge/data-sync/schema-review-workflow)