Change Data Capture in SQLite, Without the Triggers

You have a projects table. When a project's status changes, four other things need to happen. The search index needs the new status. A cached dashboard has to be invalidated. A customer's integration expects a webhook, and compliance wants an audit log entry.

Most teams start by putting all of it in one function: update the row, then handle each of the four follow-ups in turn. It works for a while. Then the webhook endpoint times out after the audit entry was already written, and now one system thinks the project shipped while another has no idea. You add retries. Then idempotency keys. Then a job queue. A few months later, a single UPDATE projects SET status = ? WHERE id = ? has turned into a small distributed system.

Change data capture (CDC) moves that work out of the request path. The database records what changed, and downstream consumers react to that record on their own schedule.

In this post I cover what CDC gives you, why it has been awkward in SQLite, how the built-in CDC in Turso Database works (rough edges included), and how to write a consumer that survives restarts.

#What CDC gives you

CDC means capturing every row-level insert, update, and delete in a source database and exposing those changes as an ordered stream, so consumers never have to re-read whole tables. If you've used Debezium or Postgres logical replication, you already know how it works.

CDC sees deletes, which a watermark query like SELECT ... WHERE updated_at > ? never will, because a deleted row has no updated_at left to compare. That blind spot is the common reason hand-rolled incremental pipelines slowly drift from their source (Datus has a good comparison of capture methods). CDC is also ordered. If a row is updated three times in one second, a consumer that applies those changes in commit order ends up exactly where the source did.

It has its limits. Row changes are database facts: "Row 42 in users was updated" carries less meaning than "Alice upgraded her plan," so if you want domain events you still need a translation layer such as the outbox pattern. CDC also doesn't deliver anything in itself: it gets changes out of the database, but whether they go to Kafka, a webhook queue, or a Redis channel is still your decision.

#SQLite CDC before Turso

SQLite never exposed its write-ahead log as a replication stream. There’s no logical replication slot and no durable change feed that another process can read, so developers have had to piece one together.

The most common approach is triggers. You write insert, update, and delete triggers for every table you care about, each copying the row into a shadow log table, and a worker polls that log. Kevin Conway's sqlite-cdc is an implementation of this approach, and its benchmarks are refreshingly honest. On narrow tables the triggers roughly double the cost of each write (which makes sense, since every modification now does an extra insert), and on 64-column tables updates and deletes run at two to three times the baseline. In absolute terms that's tens of microseconds, and the author considers it marginal for most workloads. The cost in this case is maintenance: add a column and you rewrite the trigger, add a table and you write three more.

The other option is reading the WAL file directly. That gives you page-level changes that you then have to decode into rows, it depends on SQLite internals that can shift between versions, and it's a lot to own as an application developer.

SQLite does ship two lower-level building blocks. The session extension records changes to the tables you attach and hands them back as a changeset, but it has to be compiled in, and you extract changesets on your own schedule rather than reading a continuous stream. The update hook calls a function in your process on every row change, but nothing is written down, so a crash loses whatever your callback hadn't handled yet.

#How CDC works in Turso Database

In Turso Database, our open-source rewrite of SQLite in Rust, CDC is one PRAGMA:

PRAGMA capture_data_changes_conn('full');

From then on, the engine itself records every insert, update, and delete made on that connection into a regular table, turso_cdc by default. You don't write triggers, and there's nothing to keep in step with your schema. Each row looks like this:

Column	What it holds
`change_id`	Auto-incrementing ID, in commit order
`change_time`	Unix timestamp of the change
`change_txn_id`	Transaction ID shared by every row changed in one transaction
`change_type`	`1` insert, `0` update, `-1` delete, `2` commit
`table_name`	The table that changed
`id`	Primary key or rowid of the changed row
`before`, `after`	Binary images of the row before and after the change
`updates`	Per-column change details (`full` mode only)

Built-in helpers decode those images into JSON:

SELECT change_id, id,
  bin_record_json_object(table_columns_json_array('projects'), after) AS after_state
FROM turso_cdc
WHERE table_name = 'projects' AND change_type != 2
ORDER BY change_id;

The mode argument controls how much gets stored. id keeps only the primary key, which is plenty for cache invalidation. before and after each keep one side of the change, and full keeps both, plus per-column details for updates. You can also name the table, as in PRAGMA capture_data_changes_conn('full,audit_log'), so different connections can feed different streams. The CDC docs cover the full reference.

CDC respects transaction boundaries. Changes are recorded only when a transaction commits, so a rollback leaves nothing behind. Every row changed in one transaction shares a change_txn_id, and a single COMMIT record closes the group. That grouping traces back to a community request built around credits and debits tables updated in one transaction. A consumer that applies the credit without the debit shows a balance that never existed. Grouping by transaction lets the consumer apply both or neither.

Schema changes show up in the stream too. A CREATE TABLE, DROP TABLE, or CREATE INDEX is recorded as a change to sqlite_schema, so a consumer can detect schema changes.

#Rough edges

CDC is one of Turso Database’s newer features, so it’s being actively developed. Pin your version and check the changelog when you upgrade.

It also comes with some constraints:

Decoding uses the current schema. table_columns_json_array() returns today's columns, so if you drop a column, older records decode with shifted column mappings. If you need history across migrations, snapshot the column list before you change the schema. The sqlite_schema records in the stream tell you when that moment comes.
The change table grows until you prune it. turso_cdc is an ordinary table. The consumer section below shows a retention pattern.
Capture is per connection. Any write connection that never ran the PRAGMA produces changes your consumers will never see. Enable it wherever your application opens write connections, not only in the code path you tested.

#Writing a consumer

The most dependable consumer is a polling loop with a watermark. It's pull-based, easy to reason about, and safe to kill at any moment. Here's a sketch using @tursodatabase/database:

import { connect } from "@tursodatabase/database";

// Application writes go through a connection with CDC enabled.
const writer = await connect("app.db");
await writer.exec("PRAGMA capture_data_changes_conn('full')");

// The consumer uses its own connection with CDC off,
// so its bookkeeping never shows up in the change stream.
const reader = await connect("app.db");
await reader.exec(
  "CREATE TABLE IF NOT EXISTS cdc_offsets (consumer TEXT PRIMARY KEY, last_id INTEGER)"
);

const BATCH = 500;

async function poll(apply) {
  const offset = await reader
    .prepare("SELECT last_id FROM cdc_offsets WHERE consumer = 'search'")
    .get();
  let cursor = offset?.last_id ?? 0; // how far this tick has read
  const open = new Map();            // transactions still waiting for their COMMIT

  while (true) {
    const changes = await reader.prepare(`
      SELECT change_id, change_txn_id, change_type, table_name, id,
        CASE WHEN table_name = 'projects'
          THEN bin_record_json_object(table_columns_json_array('projects'), after)
        END AS after
      FROM turso_cdc
      WHERE change_id > ?
      ORDER BY change_id
      LIMIT ${BATCH}`).all(cursor);

    for (const change of changes) {
      cursor = change.change_id;
      const group = open.get(change.change_txn_id) ?? [];
      if (change.change_type !== 2) {
        group.push(change);
        open.set(change.change_txn_id, group);
        continue;
      }
      await apply(group); // one transaction's changes, applied as a unit
      open.delete(change.change_txn_id);
      if (open.size === 0) {
        await reader
          .prepare("INSERT OR REPLACE INTO cdc_offsets VALUES ('search', ?)")
          .run(change.change_id);
      }
    }

    // Caught up: stop until the next tick.
    if (changes.length < BATCH) return;
  }
}

Changes are buffered per change_txn_id and only handed to apply when that transaction's COMMIT record arrives. The loop keeps reading pages until it's caught up, so a transaction larger than one batch still completes instead of stalling the consumer on the same 500 rows.

The watermark only advances when no groups are open, which means a crash can replay a transaction you already applied. Make apply idempotent; a Redis SET or a search PUT keyed on the row ID already is. For retention, run DELETE FROM turso_cdc WHERE change_id <= ? against the slowest consumer's watermark on a schedule. Because the reader connection has CDC off, that cleanup doesn't generate change records of its own. Keep in mind that on a synced database, this could delete changes that Sync hasn’t pushed yet.

#What to build on it

The most common use is cache and search sync. The write path becomes a single UPDATE, and the consumer keeps Redis and the search index current as derived views of the database. If it falls over, it resumes from its watermark.

Audit trails are a close second. Capture happens below your application code, so a forgotten audit call in some rarely used admin endpoint can't create a gap. before gives you the old state and change_time the timestamp. What CDC can't tell you is who or why. For that, write the user ID and reason to an audit context table inside the same transaction, and the shared change_txn_id ties it to the row changes.

CDC also fits architectures with many small databases, one per tenant, user, or agent. Each database records its own changes, and a consumer per database feeds a central store, which is how platform-wide analytics stay current without re-reading every tenant's tables. My last post on the many-database architecture covers where that pipeline fits.

If you need a local database kept in step with Turso Cloud, use Turso Sync (usage docs). It’s built on this same CDC, and handles push and pull for you. Build on CDC directly when the changes need to go somewhere else: a queue, a search index, or a coordination service that other agents read from.

Back to that projects table: the four side effects still need care, but they now run in a consumer outside the request path, and the write path is one UPDATE again.

Check the CDC documentation, and if you hit something odd, we'd like to hear about it on GitHub or Discord.