Device-scoped linear merge sync is a data replication strategy where a desktop or mobile client enumerates its local database tables, prefixes every key with a unique device identifier, and pushes records in batches to a shared cloud database through a merge API. The “linear” aspect refers to the use of a monotonically advancing cursor (last_merged_id) that tracks the high-water mark of what has been synced, ensuring that each record is pushed exactly once in a single forward pass.
This pattern solves a fundamental problem in multi-device applications: how do you replicate personal data from many devices into a single cloud store without conflicts? By scoping every key with a random device_id, each device owns a non-overlapping keyspace. There are no write–write conflicts because two devices never write to the same key. The cloud store becomes a union of all device-scoped keyspaces, and readers can query across devices by scanning the shared table and filtering or aggregating by device prefix.
As a concrete example, a desktop search application might use this pattern to sync personal documents from a user’s local database instance to a cloud database. The desktop syncer enumerates local database tables, mirrors the schema into a shared cloud table, and pushes 1,000-record batches through a /merge API endpoint. The device ID ensures that documents from different machines never collide, while the cursor-based approach means sync can resume from exactly where it left off after a crash or network interruption.
Every key written by a device is prefixed with a unique identifier, typically a UUID or random string generated on first launch. If device A has key doc:123, it becomes deviceA:doc:123 in the cloud store. Device B’s version of the same logical document becomes deviceB:doc:123. This transforms a multi-writer conflict problem into a single-writer-per-partition problem, which is dramatically simpler to reason about.
Device-scoped prefixing eliminates write–write conflicts at the storage level. Because each device writes to a disjoint subset of the keyspace, there is no possibility of two devices overwriting each other’s data. This is a form of partitioned single-writer architecture—simpler than CRDTs or operational transforms, and sufficient when each device is the sole authority over its own data.
The syncer maintains a last_merged_id cursor that points to the last record successfully pushed to the cloud. On each sync cycle, it queries the local database for records with an ID greater than the cursor, batches them, pushes them, and advances the cursor on success. This guarantees at-least-once delivery with forward-only progress. If a batch fails, the cursor does not advance, and the same batch is retried on the next cycle.
Before syncing data, the syncer must ensure that the cloud table has a compatible schema. Schema mirroring reads the local table definition (column names, types, indexes) and creates or updates the corresponding cloud table. In practice, this is done once on first sync and then verified periodically. The cloud schema includes the additional device_id prefix column and any cloud-specific indexes (e.g., a composite index on (device_id, original_key)).
The /merge API must be idempotent: pushing the same record twice should produce the same result as pushing it once. This is critical because the cursor advances only after a successful push, but the push itself may have succeeded even if the acknowledgment was lost (e.g., due to a network timeout). Idempotency is typically achieved by using the device-scoped key as the primary key in the cloud store, so duplicate inserts become upserts.
Pushing records one at a time is inefficient due to per-request overhead (connection setup, serialization, API authentication). Batching amortizes this overhead. The 1,000-record batches are a typical choice, balancing throughput against memory usage and API payload limits. Larger batches improve throughput but increase the cost of retries (the entire batch must be re-sent on failure).
The cursor-based approach provides at-least-once delivery semantics. Each record will be pushed at least once, but may be pushed more than once if a batch succeeds on the server but the client fails to record the cursor advance. Combined with idempotent merge, this achieves effectively-once semantics from the data’s perspective.
The sync process follows a well-defined lifecycle that runs periodically or on-demand:
last_merged_id for this table from persistent storage (typically a local SQLite or key-value store).id > last_merged_id, ordered by ID ascending.device_id to the key field./merge API endpoint.last_merged_id to the highest ID in the batch.The /merge API accepts a batch of key-value records and performs an upsert operation: if a key already exists, its value is updated; if not, a new record is created. The API is designed for bulk ingestion with these properties:
The cursor is the single piece of state that determines sync progress. Losing it means re-syncing from the beginning (which is safe due to idempotency, but wasteful). The The desktop syncer stores the cursor in a local SQLite database alongside the device ID and table metadata. On crash recovery, the syncer reads the last persisted cursor and resumes from that point.
// Cursor storage schema (SQLite)
CREATE TABLE sync_cursors (
table_name TEXT PRIMARY KEY,
device_id TEXT NOT NULL,
last_merged_id INTEGER NOT NULL DEFAULT 0,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
// Read cursor for a table
SELECT last_merged_id FROM sync_cursors
WHERE table_name = ? AND device_id = ?;
// Advance cursor after successful push
UPDATE sync_cursors
SET last_merged_id = ?, updated_at = CURRENT_TIMESTAMP
WHERE table_name = ? AND device_id = ?;The last_merged_id cursor is the single source of truth for sync progress. It must be persisted durably (not just in memory) and updated only after a confirmed successful push. If the cursor is ahead of reality (records were not actually pushed), data is lost. If behind (records are pushed twice), idempotency saves you. The asymmetry means: when in doubt, do not advance the cursor.
A desktop search application can use device-scoped linear merge sync to replicate a user’s personal document index from their desktop to a cloud database. This enables cross-device search: documents indexed on a laptop are searchable from a phone or browser. Each desktop generates a unique device_id on first launch, and all documents are prefixed with this ID before being pushed to the cloud.
When a user has multiple devices (work laptop, home desktop, tablet), each device syncs its local data independently. The cloud store contains the union of all device data, prefixed by device ID. A cloud-side query can aggregate across all devices by scanning records for a given user, or filter to a single device by including the device ID prefix in the query.
The linear cursor approach is inherently offline-friendly. The desktop application works entirely against its local database, with sync running as a background process when connectivity is available. If the network is down for hours or days, the local database accumulates changes, and the next sync cycle pushes everything that happened offline in batch order. No special conflict resolution is needed because the device owns its keyspace.
The same sync pattern works for incremental backups: push all new records since the last backup to a remote store. It also supports migration scenarios where data must be moved from one database to another in a controlled, resumable fashion. The cursor ensures that the migration can be paused, resumed, or restarted without data loss.
The device ID must be unique and stable. A UUID v4 is the standard choice: collision probability is negligible, and it requires no coordination. The ID is generated once on first launch and stored persistently. If the ID is lost (e.g., app reinstalled), a new ID is generated, and the device starts syncing as a “new” device. The old device’s data remains in the cloud under the old prefix and can be garbage collected if the user deregisters the device.
package syncer
import (
"crypto/rand"
"database/sql"
"fmt"
)
// getOrCreateDeviceID returns a stable device ID, creating one on first call.
func getOrCreateDeviceID(db *sql.DB) (string, error) {
var id string
err := db.QueryRow("SELECT device_id FROM device_info LIMIT 1").Scan(&id)
if err == sql.ErrNoRows {
b := make([]byte, 16)
if _, err := rand.Read(b); err != nil {
return "", fmt.Errorf("generating device id: %w", err)
}
id = fmt.Sprintf("%x-%x-%x-%x-%x", b[0:4], b[4:6], b[6:8], b[8:10], b[10:])
_, err = db.Exec("INSERT INTO device_info (device_id) VALUES (?)", id)
if err != nil {
return "", fmt.Errorf("storing device id: %w", err)
}
} else if err != nil {
return "", fmt.Errorf("reading device id: %w", err)
}
return id, nil
}package syncer
import (
"database/sql"
)
const batchSize = 1000
type Record struct {
ID int64
Key string
Value []byte
}
// fetchBatch returns up to batchSize records after the given cursor.
func fetchBatch(db *sql.DB, table string, cursor int64) ([]Record, error) {
query := fmt.Sprintf(
"SELECT id, key, value FROM %s WHERE id > ? ORDER BY id ASC LIMIT ?",
table,
)
rows, err := db.Query(query, cursor, batchSize)
if err != nil {
return nil, err
}
defer rows.Close()
var batch []Record
for rows.Next() {
var r Record
if err := rows.Scan(&r.ID, &r.Key, &r.Value); err != nil {
return nil, err
}
batch = append(batch, r)
}
return batch, rows.Err()
}
// prefixKeys prepends the device ID to each record's key.
func prefixKeys(deviceID string, batch []Record) []Record {
for i := range batch {
batch[i].Key = deviceID + ":" + batch[i].Key
}
return batch
}package syncer
import (
"bytes"
"encoding/json"
"fmt"
"net/http"
)
// syncTable runs one full sync pass for a single table.
func syncTable(localDB *sql.DB, deviceID, table, cloudURL string) error {
cursor, err := loadCursor(localDB, table, deviceID)
if err != nil {
return fmt.Errorf("loading cursor: %w", err)
}
for {
batch, err := fetchBatch(localDB, table, cursor)
if err != nil {
return fmt.Errorf("fetching batch: %w", err)
}
if len(batch) == 0 {
break // fully synced
}
prefixed := prefixKeys(deviceID, batch)
body, _ := json.Marshal(prefixed)
resp, err := http.Post(cloudURL+"/merge", "application/json", bytes.NewReader(body))
if err != nil {
return fmt.Errorf("pushing batch: %w", err)
}
resp.Body.Close()
if resp.StatusCode != http.StatusOK {
return fmt.Errorf("merge API returned %d", resp.StatusCode)
}
// Advance cursor only after confirmed success
newCursor := batch[len(batch)-1].ID
if err := saveCursor(localDB, table, deviceID, newCursor); err != nil {
return fmt.Errorf("saving cursor: %w", err)
}
cursor = newCursor
}
return nil
}The most dangerous bug in a cursor-based syncer is advancing the cursor before the push is confirmed. If the push fails after the cursor advances, those records are silently skipped. Always advance the cursor only after receiving a success response from the server. If the response is ambiguous (e.g., timeout), do not advance—the next cycle will re-push the batch, and idempotency will handle the duplicate.
The linear cursor approach assumes that local record IDs are monotonically increasing. If IDs are UUIDs, random integers, or otherwise non-sequential, the cursor-based approach breaks: records can appear “behind” the cursor and never be synced. Solutions include using auto-increment IDs, adding a separate sequence column, or using timestamps as the cursor (with caveats about clock skew and duplicate timestamps).
If a user reinstalls the desktop app, a new device ID is generated. The old device’s records remain in the cloud under the old prefix. If the app has no mechanism to reclaim or deduplicate old device data, the cloud store grows indefinitely with orphaned device partitions. Consider a device registration system that allows users to deregister old devices and garbage-collect their prefixed data.
If a specific batch consistently fails (e.g., a record has invalid data that the merge API rejects), the syncer enters an infinite retry loop because the cursor never advances past the bad record. Implement a maximum retry count with exponential backoff, and after exhausting retries, log the offending record and skip it by advancing the cursor.
If the desktop app is updated with new columns or changed types but the cloud table is not migrated, the merge API may reject records or silently drop fields. Schema mirroring should run on every sync cycle (or at least on version change) to detect and resolve drift before pushing data.