Data Follow a database’s changes

Change data capture

Let the database say what changed.

Every search box sits on a second copy of your data: the database has the truth, and the index has what search can find. The hard part is not the copying. It is knowing, after an outage, a restart, or a script nobody mentioned, whether the copy is still true. Let’s keep a museum’s collection search in step with its database and find out what that takes.

TypeScriptGoOne collection, three designs, two recorded builds.

01 / The prompt

“When a curator saves an object, update the search index too.”

A city museum puts its collection online. Curators edit objects in the collection database, and the public searches a separate index. The obvious build adds one line to the save route: after the database write, call search. It passes every test, because in the tests search is always up and every change comes through the route.

In the museum, neither is true. The registrar runs bulk-edit scripts straight against the table. Search goes down for a reindex while a curator withdraws an object from public view, and the call fails; in the lesson’s example the bronze horse, withdrawn, is still searchable after search comes back. Two curators save the same object a moment apart and their calls arrive in the other order. Each time the search index is wrong, and nothing in the system knows.

The brief never answered: when search misses a change, what notices, and where does it catch up from?

02 / Name the shape

One follower writes search, from the database’s own log.

Change data capture reads the database’s own record of committed changes and delivers them, in commit order, to the systems that keep copies. In PostgreSQL that record is the write-ahead log, read through a logical replication slot; in SQLite, and in this lesson’s recorded builds, it is a changes table the database fills with triggers. Either way the database writes it, so every change is there, whoever made it. A connector follows the log for each target and remembers where that target got to.

Search has one writer: a connector that applies each logged change in order and moves the index’s own offset only after the index has it. If the log no longer reaches back to that offset, rebuild from a snapshot; never skip ahead.

Who owns what:

What each part of the pipeline owns
Part	Owns	Promises
The database	The table and its change log	Every commit logged in order, from any writer
The connector	Delivering changes to search	In log order; the only thing that writes search
The index’s offset	Where search got to	Stored; moves only after a change is applied
Retention	How far back the log reaches	Long enough for the longest outage you will tolerate, or a snapshot

Words to put in a prompt or a review

Change log: The database’s ordered record of committed changes. In PostgreSQL, the WAL.
Offset: The last change one target has applied. Each target keeps its own.
Lag: How far a target is behind the log, in changes or in seconds.
Delete event: A delete, delivered as a change, so the copy removes the row too.
Snapshot: A copy of the table as of one log position, used to start or restart a target.
Retention: How much of the log is kept. A target further behind than that needs a snapshot.

How is this different from an outbox?And from CQRS

A transactional outbox is written by the application, in its own transaction, and holds a message the application designed: “a pledge was recorded”. Change data capture reads what the database already records, row changes, and sees writers the application never knew about, such as the registrar’s scripts. The outbox is the right tool for telling other services a business fact; change data capture is the right tool for keeping a copy of a table.

CQRS decides that reads get a model of their own; change data capture is one way to feed such a model, and a search index is a read model for the search box.

What PostgreSQL does with a follower that stopsSlots keep the log

PostgreSQL keeps its log for a follower through a replication slot, which “represents a stream of changes that can be replayed to a client in the order they were made on the origin server”. Slots “persist across crashes” and “prevent removal of required resources even when there is no connection using them” (Logical Decoding Concepts, PostgreSQL 18, fetched 23 September 2026). So a stopped connector does not lose its place; the database’s disk fills instead. With max_slot_wal_keep_size set, a slot that falls too far behind “may no longer be able to continue replication due to removal of required WAL files” (Replication settings, fetched 23 September 2026), and that is the gap the story’s last chapter shows. The default is -1, no limit.

03 / A collection and its search

Same edits, same outage. Which search still matches the table?

Each column runs one of the lesson’s designs on the same steps, and compares the public search with the collection table after each one. Watch four situations, then open Try it and lose a change yourself.

Playback controls

Change data capture

A collection, its search, and the log between them

The app calls search

Reply: saved

Collection table

obj-1=Blue vase, Ming
obj-2=Portrait of a woman
obj-3=Bronze horse

Public search

obj-1=Blue vase
obj-2=Portrait of a woman
obj-3=Bronze horse

Behind by: unknown

Change data capture

Reply: lsn 1

Collection table

obj-1=Blue vase, Ming
obj-2=Portrait of a woman
obj-3=Bronze horse

Public search

obj-1=Blue vase
obj-2=Portrait of a woman
obj-3=Bronze horse

Behind by: 1 changes

01/ 04

Two curators, one object

Ana saves obj-1 as “Blue vase, Ming”, and the call to search is slow.

Ana saves “Ming”, Ben corrects it to “Qing” a moment later, and the table keeps Ben’s. Their calls to search arrive the other way round, so the public search says Ming. Following the log applies the two commits in the order the database made them.

Reduced motion: choose a scene to see its completed state.

Read this scene

The app calls search: table obj-1=Blue vase, Ming, obj-2=Portrait of a woman, obj-3=Bronze horse; search obj-1=Blue vase, obj-2=Portrait of a woman, obj-3=Bronze horse; behind by an unknown number of changes.

Change data capture: table obj-1=Blue vase, Ming, obj-2=Portrait of a woman, obj-3=Bronze horse; search obj-1=Blue vase, obj-2=Portrait of a woman, obj-3=Bronze horse; behind by 1 changes.

Try the example by reading

Two curators, one object: The app calls search, obj-1 has an old title in search; Change data capture, search matches the table. A script edits the table: The app calls search, obj-1 has an old title in search; obj-4 is not in search; Change data capture, search matches the table. Search is down during a restart: Follows the log, place in memory, obj-2 has an old title in search; obj-3 was withdrawn and is still in search; Change data capture, search matches the table. Down longer than the log is kept: Follows the log, place in memory, obj-1 has an old title in search; obj-2 has an old title in search; Change data capture, knows search needs a snapshot.

Keep the public search honest

Pick a design. Rename the vase, withdraw an object, let a script add one, take search down, restart the service, trim the log, and run the follower. The line below compares search with the table, and says whether the design knows.

Design The app calls searchFollows the log, place in memoryChange data capture

Collection table

obj-1=Blue vase

obj-2=Portrait of a woman

obj-3=Bronze horse

Public search (up)

obj-1=Blue vase

obj-2=Portrait of a woman

obj-3=Bronze horse

Log: 0 changes kept
Behind by: unknown to this design

Search matches the table.

The lab runs the lesson’s TypeScript designs in your browser. The followers apply nothing until you run them, which is what a real connector does between its reads of the log.

Watch restarts the story when you come back. Step through shows where each chapter ends. Try it starts a new collection whenever you change the design or press Reset.

04 / Read the shape

A follower with its own offset, and a stop when the log has moved on.

Basic form is the connector’s pass. In the wild is what it does when it has fallen behind the log. At the call site is who writes what. Notice that no save handler mentions search.

Read in

Compare with

The connector’s pass for the search index: read the log after the index’s own offset, apply each change in order, and move the offset only after the index has it. A delete is a change like any other.

TypeScriptReading

sync.ts

/**
 * One pass of the connector for the search index. It reads the log after
 * the index's own offset, applies each change, and only then moves the
 * offset. A delete is a change too: the index removes the document.
 */
sync(): string {
	if (!this.index.up) return 'down';
	if (this.fellBehindRetention()) return 'gap';
	let applied = 0;
	for (const change of this.db.log.filter((c) => c.lsn > this.offset)) {
		this.index.apply(change);
		this.offset = change.lsn; // committed after the index took it
		applied += 1;
	}
	return `applied ${applied}`;
}

GoAlongside

sync.go

// Sync is one pass of the connector for the search index. It reads the log
// after the index's own offset, applies each change, and only then moves the
// offset. A delete is a change too: the index removes the document.
func (c *ChangeDataCapture) Sync() string {
	if !c.Index.Up {
		return "down"
	}
	if c.FellBehindRetention() {
		return "gap"
	}
	applied := 0
	for _, ch := range c.DB.Log {
		if ch.LSN > c.Offset {
			c.Index.Apply(ch)
			c.Offset = ch.LSN // committed after the index took it
			applied++
		}
	}
	return fmt.Sprintf("applied %d", applied)
}

The dual writeThe app calls search itself

It is correct for every change that goes through the app, while search is up, one save at a time. A failed call is logged and gone, and a held call can land after a newer one.

sync.ts

/** The app writes the database, then calls the index. Nothing else ever does. */
export class DualWrite extends Collection {
	queued = new Map<string, Change>();
	saved(change: Change, who: string, held: boolean): string {
		if (held) {
			this.queued.set(who, change);
			return 'saved';
		}
		return this.call(change);
	}
	scripted(): string {
		return 'saved';
	}
	send(who: string): string {
		const change = this.queued.get(who);
		if (!change) return 'nothing';
		this.queued.delete(who);
		return this.call(change);
	}
	private call(change: Change): string {
		if (!this.index.up) return 'index-failed'; // logged, and gone
		this.index.apply(change);
		return 'indexed';
	}
}

The behavior these examples promiseChecked by 15 shared scenarios

The dual write loses a change made while search is down, applies two saves in the order their calls arrive, and never sees a script. It cannot say how far behind it is.
A follower of the log sees every commit in order, but one that keeps its place in memory starts at the end after a restart and skips what happened in between.
Change data capture resumes from the index’s stored offset after an outage or a restart. When the log was trimmed past that offset, it stops with a gap until a snapshot, rather than applying what is left.

Every expectation was generated by a separate model written from the contract in the examples’ README, not copied from either implementation, and it is kept beside the examples.

Reading the TypeScriptAn abstract class and three followers

Collection gives every design a database and an index and default answers; each design overrides what it does on a save, a sync, and a restart. db.commit logs every change, so the dual write has a log too; it just never reads it.

Reading the GoAn interface over embedded structs

The three designs embed a base with the database and the index and satisfy one Collection interface. Lag returns a pointer so the dual write can answer “unknown” as nil, which is the honest answer for it.

Run it yourselfNo dependencies

Save the complete files at the paths in their banners. Then run node --experimental-strip-types run.ts (Node 22.18 or later), or go run . in the Go folder. Both print:

dual-write · two curators edit one object: drift [stale:obj-1], lag unknown -> silent-drift
dual-write · a bulk edit from a script: drift [stale:obj-1 missing:obj-4], lag unknown -> silent-drift
log-from-now · the index is down during a restart: drift [stale:obj-2 ghost:obj-3], lag 0 -> silent-drift
cdc · the index is down during a restart: drift [], lag 0 -> in step
log-from-now · down longer than the log is kept: drift [stale:obj-1 stale:obj-2], lag 0 -> silent-drift
cdc · down longer than the log is kept: drift [stale:obj-1 stale:obj-2 ghost:obj-3], lag 3 -> needs-snapshot

05 / Review the agent’s diff

“Search is instant now.”

Curators asked why an edit takes a second to appear in search, and an agent made it instant. Read what the change removes.

The agent’s pull request

“Curators said search takes a second to show an edit. The save route now writes to search directly, so it is instant, and the connector is no longer needed. All tests pass.”

 // routes/objects.ts
			 router.put('/objects/:id', async (req, res) => {
			   const object = db.update(req.params.id, req.body);
			+(added)   await search.put(object.id, object); // search is instant now
			   res.json(object);
			 });
			 // connector.ts
			-(removed) setInterval(followChanges, 1000);
			+(added) // followChanges is redundant now that routes write to search

The tests run with search always up and no scripts. What do you do with this change? Merge it. Curators complained that search took a second to update, and now it does not.Reject it. Keep the connector as the only writer to search, and if a second is too slow, run it more often or have it wait on the log.Merge it, and wrap the search call in three retries with backoff.Merge the route change and keep the connector running too, so search gets both.

06 / How it fails

A copy fails by being wrong quietly. The question is whether anything knows.

The first five rows are shared scenarios the tests run; the last three are not modeled.

Failure modes of keeping the collection search in step
What happens	The app calls search	Follows the log, place in memory	Change data capture
Two saves of one object; their calls arrive in the other order	Search keeps the older title.	Applied in commit order.
A script writes the table	Search never hears of it.	Logged like any commit, and applied.
Search is down for an edit and a withdrawal	Both calls fail; a withdrawn object stays searchable.	Search falls behind, then catches up.
The service restarts while search is down	Nothing to resume.	Starts at the end of the log and skips both changes.	Resumes from the stored offset.
Search is down longer than the log is kept	Wrong, silently.	Applies what is left and looks caught up.	Stops with a gap until a snapshot.
Search is slow	Every save waits for it.	Saves are unaffected; lag grows and can be alerted on. Not modeled.
The connector crashes after applying, before saving its offset	Not applicable.	The change is applied again. Harmless when applying is an upsert or delete by id. Not modeled.
A connector is gone for good	Not applicable.	In PostgreSQL its slot keeps the log and the disk fills, unless a size limit gives up on it. Not modeled.

Applying a change twice is safe here because every change is keyed by the object’s id; Idempotency and at-least-once covers why that matters, and Event-driven architecture covers readers that each follow a log from their own position.

07 / Is it worth it?

A log, a connector, and an offset, against one line in the save route.

The shared four changes, against the app calling search
Change	The app calls search	Change data capture
A second entry point: a loans import from a partner museum	It must remember to call search, with the same gaps.	No change: the import’s commits are in the log.
The search engine is replaced	Every route that calls it changes, and the new index starts empty.	The connector changes; a snapshot fills the new index.
A new rule: objects not on view are searchable but marked	The mapping, in every route.	The mapping, in the connector.
A second team: recommendations want every change	Another call in every route.	Their own connector and offset on the same log.

The costs are real: a log to keep and size, a connector to run and watch, a snapshot path you must test before you need it, and search that is a second or so behind instead of “instant”. When the table has one writer and search can be minutes stale, a nightly full reindex is simpler than either design here, and it repairs itself every night.

Measure before and after:

Drift: objects missing from search, still in search after withdrawal, or with old titles, from a nightly comparison of the table and the index. With a dual write, this is the number that is not zero.
Lag, in changes and in seconds, per target, and how long the longest outage was.
Retained log size, against the disk and the retention you chose.

This lesson did not measure a real museum, and gives no numbers.

08 / Ask for it

One brief, two prompts.

Two agents running Claude Sonnet each got the brief from section 01, which also said the registrar’s scripts write the table directly and that search must match the table. One prompt added a Change data capture block: triggers that record every change, a connector with a stored position that moves only after search accepts a change, and a snapshot when the position falls behind what is kept. A script ran both builds and a control we wrote as a plain dual write, playing the search service and the registrar’s scripts.

What the checker found, run 2026-09-23
Question	Plain prompt	Change-data-capture prompt	Control (not an agent)
Curators edit through the app	In step after 1.3 s	In step after 1 s	In step
Search down 20 s during an edit and a withdrawal	In step	In step after 1 s	2 objects still wrong after 15 s
A script writes the table, server running	In step after 1 s	In step after 1 s	3 objects still wrong after 15 s
A script writes the table, server stopped	In step	In step after 1 s	2 objects still wrong after 15 s
Killed while search is down, changes pending	In step	In step after 1 s	2 objects still wrong after 15 s
Twenty saves of one object at once, search slow	In step after 0.8 s	In step after 7.1 s	In step
2,000 objects, then ten quiet seconds	9 full listings of search, 1.3 MB	Asked nothing of search	The load never reached search (1,999 objects missing)
Its own tests	23 of 23 pass	15 of 15 pass	None

Neither agent built the dual write this lesson warns about, and both kept search in step under every question. The control, which does dual write, lost every change made while search was down or made by a script, so the questions can fail. The brief did the work: it said scripts write the table and stated the requirement as “search must match the table”, and the plain agent built exactly that comparison. Every second it reads the whole table, lists the whole index, and fixes the difference:

server.ts · plain prompt

async function reconcile(): Promise<void> {
  if (reconciling) return;
  reconciling = true;
  try {
    const table = allObjects();
    const docs = await fetchAllDocs();
    if (docs === undefined) {
      // Search service unreachable or erroring; nothing we can do this round.
      return;
    }

    for (const obj of table) {
      const doc = docs.get(obj.id);
      if (!doc || !sameDoc(doc, obj)) {
        await putDoc(obj);
      }
    }

    const tableIds = new Set(table.map((o) => o.id));
    for (const id of docs.keys()) {
      if (!tableIds.has(id)) {
        await deleteDoc(id);
      }
    }

That is a real design with a real name: it is the nightly reindex from section 07, run every second. It repairs anything, including drift nobody logged. Its cost grows with the collection, not with the changes: with 2,000 objects and nothing happening, it listed the whole index nine times in ten seconds. The capture build asked nothing of search while nothing changed, and applied each logged change and then moved its position:

lib/connector.ts · change-data-capture prompt

async function applyPendingChanges(
  db: DatabaseSync,
  searchUrl: string
): Promise<void> {
  const position = getConnectorPosition(db);
  const changes = getChangesAfter(db, position, BATCH_SIZE);

  for (const change of changes) {
    if (change.op === 'delete') {
      await deleteDoc(searchUrl, change.object_id);
    } else {
      await putDoc(searchUrl, changeRowToObject(change));
    }
    setConnectorPosition(db, change.seq);
  }
}

Its cost is the other way round: one request per change, one at a time, so twenty saves against a search service answering in 300 ms arrived seven seconds later, where the reconciler, which only sends the final state, took under a second.

The missing line is not the mechanism; both brought one. It is the size: say how many objects there will be and how often they change, and what search may cost when nothing does. For a few thousand objects that rarely change, the reconciler is fine and simpler. For a collection of millions, or a copy that must also feed a second system, the log is the design that still works.

How the runs were made and checkedTwo builds, recorded as written

Both agents were launched at the same time from empty folders; neither was told about the other, the lesson, or the checker.
Both builds are kept byte for byte with checksums. For every question the checker restores a build into a fresh folder with its own database, and plays search and the scripts itself.
The 2,000-object question was added after the plain build turned out to reconcile the whole table; the checker was tried on the builds while writing it, and only the final run is kept. No question waits seven days, so neither build’s retention or snapshot path was exercised.
One agent wrote curl replies to /tmp during its own check, against the prompt; none remains. Neither stopped a process by name or pattern.
One run of each prompt is a sample, not a measurement of the model.

09 / Hold it there

Change data capture breaks when someone adds a second writer to search.

Watch the database’s side of the follower

PostgreSQL shows each slot’s state: “You can see the WAL availability of replication slots in pg_replication_slots” (Replication settings, fetched 23 September 2026). Alert on a slot that is inactive or retaining more than you budgeted, and decide max_slot_wal_keep_size on purpose: unlimited fills the disk, limited means a snapshot.

Only the connector imports the search client

The agent’s diff in section 05 is one import. A rule that allows the search client in the connector and nowhere else keeps it out, the way Enforcement layer enforces import rules. The script below, committed with the lesson, runs it against the recorded capture build and against the same files with section 05’s import added:

examples/checks/check-search-writers.mjs

const allowed = new Set(['lib/connector.ts', 'lib/search-client.ts']);

/** Files outside the connector that import the search client or call its endpoint. */
function secondWriters(files) {
	const problems = [];
	for (const [name, text] of files)
		if (!allowed.has(name) && /search-client|\/docs\b/.test(text))
			problems.push(`${name}: reaches the search service outside the connector`);
	return problems;
}

output

$ node src/lib/content/lessons/change-data-capture/examples/checks/check-search-writers.mjs
The capture build, as recorded:
Only the connector writes search.

The same build with section 05’s import added:
  error server.ts: reaches the search service outside the connector
1 second writer to search.

Compare the copy with the table

Once a night, compare every object in the table with its document in search and count what is missing, withdrawn but present, or stale. With change data capture the count should be zero or explained by current lag. The checker in section 08 does this after every question.

There is no frontend version of this lesson: change data capture runs between a database and the servers that copy from it, and a browser never reads a replication slot. The browser’s own version of resuming from a position, a stream that picks up from the last event id after a dropped connection, is in Polling, server-sent events, or WebSockets.

10 / Make the call

If a copy must stay true, let the database tell it what changed.

Use change data capture when a copy of a table must follow every change, from every writer, and survive its own outages: search, caches that must not serve withdrawn data, a warehouse. Keep the dual write for a copy that is only a convenience and is rebuilt often; keep a nightly reindex when minutes of staleness are fine. Reopen the decision when a second writer to the table appears, or when a change in search starts to matter to someone.

Take it with you

Explain it without saying “change data capture”: “The database keeps a numbered list of every change anyone made. Search has a bookmark in that list. Someone reads from the bookmark, updates search, and moves the bookmark. If the list has been cut shorter than the bookmark, they start search again from a fresh copy.” Then find a copy of your own data, a cache, an index, a spreadsheet export, and ask what updates it when a script changes the source.

Paste into your next prompt, and fill in the blanks

Search is written only by a connector that follows [the database's change log: a logical replication slot, or a changes table filled by triggers], never by request handlers.
The connector keeps [the search index]'s own offset in [the database], applies each change in log order, and moves the offset only after the index accepts it. A restart resumes from the stored offset.
Deletes are changes: the connector removes the document.
If the log no longer reaches back to the offset, the connector stops and rebuilds the index from a snapshot of the table taken at one position, then follows from there. It never skips ahead.
Alert when the index is more than [N changes or M seconds] behind, and when the retained log is larger than [size].

Connections to follow nextRelated lessons

Transactional outbox is the application’s own version, for business messages.
CQRS is what the copy is for: a model shaped for reading.
Event sourcing makes the log the record, instead of a by-product of the table.
Event-driven architecture has several readers following one log, each from its own position.
Idempotency and at-least-once is why applying a change twice must be harmless.

“When a curator saves an object, update the search index too.”

One follower writes search, from the database’s own log.

Same edits, same outage. Which search still matches the table?

The app calls search

Change data capture

Ana saves obj-1 as “Blue vase, Ming”, and the call to search is slow.

A follower with its own offset, and a stop when the log has moved on.

“Search is instant now.”

A copy fails by being wrong quietly. The question is whether anything knows.

A log, a connector, and an offset, against one line in the save route.

One brief, two prompts.

Change data capture breaks when someone adds a second writer to search.

Watch the database’s side of the follower

Only the connector imports the search client

Compare the copy with the table

If a copy must stay true, let the database tell it what changed.

Take it with you