SQL Triggers for Source Database

Here are the SQL triggers you'll need on your source database:

-- Create changelog table on source database
CREATE TABLE IF NOT EXISTS _changelog (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  table_name TEXT NOT NULL,
  record_id TEXT NOT NULL,
  operation TEXT NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_changelog_updated_at ON _changelog(updated_at);
CREATE INDEX IF NOT EXISTS idx_changelog_table_updated_at ON _changelog(table_name, updated_at);

-- Example triggers for a 'users' table:
-- Make sure the table has updated_at column
ALTER TABLE users ADD COLUMN updated_at INTEGER DEFAULT (strftime('%s', 'now') * 1000);

-- Update trigger to set updated_at
CREATE TRIGGER IF NOT EXISTS users_update_timestamp
AFTER UPDATE ON users
BEGIN
  UPDATE users SET updated_at = strftime('%s', 'now') * 1000 WHERE id = NEW.id;
END;

-- Insert trigger to set updated_at
CREATE TRIGGER IF NOT EXISTS users_insert_timestamp
AFTER INSERT ON users
BEGIN
  UPDATE users SET updated_at = strftime('%s', 'now') * 1000 WHERE id = NEW.id;
END;

-- Delete trigger to record in changelog
CREATE TRIGGER IF NOT EXISTS users_delete_log
AFTER DELETE ON users
BEGIN
  INSERT INTO _changelog (table_name, record_id, operation, updated_at)
  VALUES ('users', OLD.id, 'DELETE', strftime('%s', 'now') * 1000);
END;

Usage Example

Here's how to use the decorator:

import { DurableObject } from "cloudflare:workers";
import { Syncable } from "./syncable-decorator";

@Syncable({
  basePath: "https://source-database-api.example.com",
  authorization: "Bearer your-auth-token", // optional
  syncIntervalMs: 60000, // 1 minute
  batchSize: 100,
})
export class SyncableDatabaseDO extends DurableObject {
  sql: SqlStorage;

  constructor(state: DurableObjectState, env: any) {
    super(state, env);
    this.sql = state.storage.sql;
    this.initialize();
  }

  async fetch(request: Request): Promise<Response> {
    const url = new URL(request.url);

    if (url.pathname === "/data") {
      // Your regular API endpoints
      const cursor = this.sql.exec("SELECT * FROM users LIMIT 10");
      const users = cursor.toArray();
      return new Response(JSON.stringify(users), {
        headers: { "Content-Type": "application/json" },
      });
    }

    // This will handle /sync/trigger and /sync/status endpoints
    return super.fetch(request);
  }
}

export interface Env {
  SYNCABLE_DB: DurableObjectNamespace;
}

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const url = new URL(request.url);

    // Route to your syncable DO
    const id = env.SYNCABLE_DB.idFromName("main-sync-db");
    const stub = env.SYNCABLE_DB.get(id);

    return stub.fetch(request);
  },
};