How edge SQLite sync works

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.

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.

​

What gets replicated

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.
3. Computes a schema fingerprint (SHA-256 of canonical column metadata).

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:

1. Reads journal entries since the last published commit_seq.
2. Builds a JSON batch with batch_id (UUIDv7), journal_epoch, and row operations.
3. Publishes to {root_topic}/sync/rows/batch at QoS 1.

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.
• Forwards to the integration worker via telemetry.edge_export_segment.received.v1.

​

5. Worker intake (cloud)

The integration worker:

1. Dedupes on (device_id, batch_id).
2. Records segment metadata in edge_export_segments (Timescale).
3. Upserts a lineage — one row per (device_id, source_table).

​

6. Schema classification (cloud)

When a batch carries a new schema_hash: Default production policy treats the first schema for a table as ambiguous → schema review required.

​

7. Staging (cloud)

While lineage is paused or materialization is blocked, row bodies are stored in edge_staged_rows (Timescale) with a reason: 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 mirror DDL, creates column bindings, inserts a replay intent, and sets lineage active.

​

9. Replay and materialization (cloud)

• Replay drains staged rows into the mirror table (post-approve backlog).
• Live batches on an active lineage write directly to mirror or telemetry.

Mirror tables are named edge_mirror_{device8}_{table} (device-scoped) or coalesced names when using registry coalescing.

​

10. Query (operator / product)

Query materialized data via:

• GET .../edge/lineages/{id}/mirror-rows
• platform-tui Written view
• QueryScript against mirror or telemetry tables

→ Querying synced data

​

Two kinds of “pause”

Operators often confuse these — they behave differently: 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

​

Data stores (cloud)

​

Related pages

• Lineages and staging
• Schema governance
• Omega setup
• Schema review workflow