Delta Synchronization

Delta synchronization sends only what changed, not the entire resource. When a large dataset — a product catalog, a user directory, an order book — changes by one record, re-reading the entire resource wastes bandwidth, processing time, and context window tokens. Delta sync maintains a version on the server and returns only the records that were added, modified, or deleted since the client's last known version. The client merges the delta into its local state, keeping a synchronized view with minimal data transfer.

Implementing delta sync requires change tracking on the server. The simplest approach uses a version column (updated_at timestamp or monotonic version number) on each record. When the client reads with a since parameter, the server returns only records where updated_at > since. Deletions require a soft-delete pattern — a deleted_at column that marks records as removed without physically deleting them. Without soft deletes, the client cannot distinguish "this record was deleted" from "this record was not in the delta because it did not change."

The delta response format includes three sections: created (new records), updated (modified records with full current state), and deleted (record IDs that were removed). The client applies these in order: delete first, then update, then create. This ordering prevents conflicts when a record is deleted and a new record is created with the same ID (unlikely with UUIDs but possible with sequential IDs). The response also includes a serverVersion that the client sends in the next delta request.

interface DeltaResponse<T> {
  created: T[];
  updated: T[];
  deleted: string[];  // IDs of removed records
  serverVersion: string;  // Send this back on next request
  fullResyncRequired: boolean; // True if delta is too large
}

server.resource(
  "products-delta",
  new ResourceTemplate("db://products/delta{?since}", { list: undefined }),
  async (uri, params) => {
    const since = params.since;

    if (!since) {
      // No cursor — return full snapshot as initial sync
      const all = await pool.query(
        "SELECT * FROM products WHERE deleted_at IS NULL LIMIT 5000"
      );
      const version = await getCurrentVersion();
      const response: DeltaResponse<unknown> = {
        created: all.rows,
        updated: [],
        deleted: [],
        serverVersion: version,
        fullResyncRequired: false,
      };
      return {
        contents: [{ uri: uri.href, mimeType: "application/json",
          text: JSON.stringify(response) }],
      };
    }

    // Delta since last version
    const created = await pool.query(
      `SELECT * FROM products
       WHERE created_at > $1 AND deleted_at IS NULL`,
      [since]
    );
    const updated = await pool.query(
      `SELECT * FROM products
       WHERE updated_at > $1 AND created_at <= $1 AND deleted_at IS NULL`,
      [since]
    );
    const deleted = await pool.query(
      `SELECT id FROM products WHERE deleted_at > $1`,
      [since]
    );

    const totalChanges = created.rowCount! + updated.rowCount! + deleted.rowCount!;
    const version = await getCurrentVersion();

    // If too many changes, tell client to do a full resync
    if (totalChanges > 1000) {
      return {
        contents: [{ uri: uri.href, mimeType: "application/json",
          text: JSON.stringify({
            created: [], updated: [], deleted: [],
            serverVersion: version,
            fullResyncRequired: true,
          }) }],
      };
    }

    return {
      contents: [{ uri: uri.href, mimeType: "application/json",
        text: JSON.stringify({
          created: created.rows,
          updated: updated.rows,
          deleted: deleted.rows.map((r: { id: string }) => r.id),
          serverVersion: version,
          fullResyncRequired: false,
        }) }],
    };
  }
);

1. Add change tracking columns Add created_at, updated_at, and deleted_at columns to every table you want to delta-sync. Use database triggers to auto-update updated_at on every change.
2. Implement the delta endpoint Accept a since parameter, query for records changed after that timestamp, and return them in created/updated/deleted buckets with a serverVersion cursor.
3. Handle large deltas Set a threshold (e.g., 1000 changes). If the delta exceeds it, return fullResyncRequired: true and let the client do a fresh full read instead of processing a massive delta.