01 / The prompt
“Show each team a leaderboard: this week’s minutes per member.”
A running club’s fitness tracker. Members log runs, correct them (“that was 45 minutes, not 30”), and delete the ones they logged twice. Every member’s phone shows the team leaderboard and refreshes it often. The obvious handler adds up the workouts table on every request. It is always right, and every test passes.
It is also the same table every new run is written to, and the history only grows. In the lesson’s example a week of 300 runs means every view reads 300 rows to show 3 totals; a table kept for the leaderboard reads 3. The busiest screen and the most important write are fighting over one shape.
The prompt never asked the question: should the model that checks a run’s rules also answer the leaderboard, and if not, how does a second copy stay right, and how does a member know how fresh it is?
Martin Fowler’s summary of the idea: “At its heart is the notion that you can use a different model to update information than the model you use to read information” (CQRS, 2011). The same page warns that for most systems it “adds risky complexity”. This lesson is about both halves.
02 / Name the shape
The tracker decides. The leaderboard is a copy that knows how far it has read.
CQRS, command query responsibility segregation, gives writes and reads separate models. Commands (log, correct, delete) go to a write model that holds the rules and the record. Queries go to a read model, also called a projection: a table shaped for one question and fed by the write model’s changes.
Every accepted change is recorded with a position and the workout’s version. The projection applies a change only if it is newer than what it holds, and every answer says the position it reflects. The copy can always be rebuilt from the record.
Who owns what:
| Part | Owner | Promises |
|---|---|---|
| Workouts and their rules | The tracker (write model) | 1 to 600 minutes; a correction names the version it read |
| The feed of changes | The tracker | Written in the same step as the change, with a position and a version |
| The leaderboard table | The projector, and only the projector | Newer versions only; a repeat or a late change does nothing |
| Freshness | The projector | Every answer carries asOf, the last position applied |
| Showing a member their own change | The app | “Updating” until asOf reaches the command’s position |
Words to put in a prompt or a review
- Command
- A request to change something, checked against the rules. It answers yes or no.
- Query
- A request to read. It changes nothing and needs no rules.
- Read model
- A copy shaped for one question, fed by the write model’s changes. A projection.
- Position
- Where a change sits in the feed. The reply to a command carries it.
- asOf
- The last position a read model has applied: how fresh the answer is.
- Rebuild
- Empty the read model and replay the feed. The repair for a copy that went wrong.
Is this event sourcing?No; the tracker is an ordinary table
Here the workouts table is the record, and the feed is a log of changes written beside it, the way an outbox is. In Event sourcing the events are the record and current state is derived from them. The two are often used together and do not need each other: a read model can be fed from any change log, including the database’s own (Change data capture).
03 / A copy that lags
Same runs, three dashboards. Which one tells Ines the truth, and which one knows when it can’t?
Each column runs the same tracker with a different dashboard and compares it with the tracker: fresh, stale and saying so, or wrong with nothing left to fix it. Watch five situations, then open Try it and deliver the changes yourself.
One tracker, three dashboards
The tracker says: Ines 3000, Kai 3000, Tom 3000
Add up the workouts
Leaderboard
- Ines 3000
- Kai 3000
- Tom 3000
Projection that trusts delivery
Leaderboard
- no minutes
Projection that checks versions
Leaderboard
- no minutes
The club logs 300 runs.
Three hundred runs this week. Adding up the workouts table reads all 300 rows every time a member opens the app, on the same table every new run is written to. Either projection answers from three rows, one per member, however long the history grows.
Reduced motion: choose a scene to see its completed state.
Read this scene
Three hundred runs this week. Adding up the workouts table reads all 300 rows every time a member opens the app, on the same table every new run is written to. Either projection answers from three rows, one per member, however long the history grows.
Tracker: Ines 3000, Kai 3000, Tom 3000.
Add up the workouts: Ines 3000, Kai 3000, Tom 3000, as of #300, 300 rows read.
Projection that trusts delivery: no minutes, as of #0, 0 rows read.
Projection that checks versions: no minutes, as of #0, 0 rows read.
Watch restarts the story when you come back. Step through shows where each chapter ends. Try it starts a new tracker whenever you change the dashboard or press Reset.
04 / Read the shape
Commands that record every change, and a copy that keeps each workout’s version.
Basic form is the write model. In the wild is the projection. At the call site is delivery. Notice that the projection never asks the tracker anything: everything it knows came in a change.
The write model: each command checks its rule against the current workout, changes it, and appends the change to the feed with a position and the workout’s new version.
/** Commands check the rules against the current workout, change it, and record the change. */
log(id: string, member: string, minutes: number): Reply {
if (minutes < 1 || minutes > 600) return { status: 'invalid' };
if (this.workouts.has(id)) return { status: 'exists' };
const w = { id, member, minutes, version: 1, deleted: false };
this.workouts.set(id, w);
return this.append('logged', w);
}
correct(id: string, minutes: number, expected: number): Reply {
const w = this.workouts.get(id);
if (!w || w.deleted) return { status: 'not-found' };
if (w.version !== expected) return { status: 'conflict' };
if (minutes < 1 || minutes > 600) return { status: 'invalid' };
Object.assign(w, { minutes, version: w.version + 1 });
return this.append('corrected', w);
}
remove(id: string): Reply {
const w = this.workouts.get(id);
if (!w || w.deleted) return { status: 'not-found' };
Object.assign(w, { minutes: 0, version: w.version + 1, deleted: true });
return this.append('deleted', w);
}// Log, Correct, and Remove check the rules against the current workout, change it, and record the change.
func (t *Tracker) Log(id, member string, minutes int) Reply {
if minutes < 1 || minutes > 600 {
return "invalid"
}
if _, ok := t.Workouts[id]; ok {
return "exists"
}
w := &Workout{ID: id, Member: member, Minutes: minutes, Version: 1}
t.Workouts[id] = w
t.order = append(t.order, id)
return t.appendChange("logged", w)
}
func (t *Tracker) Correct(id string, minutes, expected int) Reply {
w, ok := t.Workouts[id]
if !ok || w.Deleted {
return "not-found"
}
if w.Version != expected {
return "conflict"
}
if minutes < 1 || minutes > 600 {
return "invalid"
}
w.Minutes, w.Version = minutes, w.Version+1
return t.appendChange("corrected", w)
}
func (t *Tracker) Remove(id string) Reply {
w, ok := t.Workouts[id]
if !ok || w.Deleted {
return "not-found"
}
w.Minutes, w.Version, w.Deleted = 0, w.Version+1, true
return t.appendChange("deleted", w)
}The projection that trusts deliveryRight when every change arrives once, in order
It moves each total by what the change says, and remembers what it counted per workout. With one delivery in order it is exactly right. It has no way to tell a copy or a late change from a new one.
/** A projection that trusts delivery: each change moves the total by what it says. */
export class NaiveProjection extends Dashboard {
private counted = new Map<string, number>();
apply(c: Change): void {
const before = this.counted.get(c.workout) ?? 0;
const after = c.type === 'logged' ? before + c.minutes : c.minutes;
this.add(c.member, after - before);
this.counted.set(c.workout, after);
this.asOf = Math.max(this.asOf, c.seq);
}
reset(): void {
super.reset();
this.counted = new Map();
}
}The behavior these examples promiseChecked by 27 shared scenarios
- Adding up the workouts is always fresh and reads every workout row on every view.
- Both projections read one row per member, and are behind until changes are delivered,
with
asOfsaying so. - The trusting projection is wrong after a repeated delivery, a correction delivered before its workout, and a deletion delivered backward, with nothing left to deliver.
- The versioned projection matches the tracker in every one of those.
- A rebuild from the feed repairs either projection.
- A correction that names an old version is refused, and records nothing.
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 TypeScriptThree classes, one base
Dashboard holds the totals and asOf; each design overrides apply. OneModel ignores changes and overrides view to add up the tracker instead. A delete is a change with 0 minutes and a
higher version, so the projection needs no special case for it.
Reading the GoAn interface and an embedded table
Dashboard is an interface; both projections embed a table with
the totals and asOf, and add their own memory of each workout. The
leaderboard sorts with sort.Slice, so map order never reaches the output.
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:
one-model · a busy week: dashboard [Ines 3000, Kai 3000, Tom 3000] as of #300, 300 rows read; tracker [Ines 3000, Kai 3000, Tom 3000] -> fresh naive · a busy week: dashboard [Ines 3000, Kai 3000, Tom 3000] as of #300, 3 rows read; tracker [Ines 3000, Kai 3000, Tom 3000] -> fresh cqrs · a busy week: dashboard [Ines 3000, Kai 3000, Tom 3000] as of #300, 3 rows read; tracker [Ines 3000, Kai 3000, Tom 3000] -> fresh one-model · just logged: dashboard [Ines 30] as of #1, 1 row read; tracker [Ines 30] -> fresh naive · just logged: dashboard [] as of #0, 0 rows read; tracker [Ines 30] -> stale cqrs · just logged: dashboard [] as of #0, 0 rows read; tracker [Ines 30] -> stale one-model · delivered twice: dashboard [Ines 30] as of #1, 1 row read; tracker [Ines 30] -> fresh naive · delivered twice: dashboard [Ines 60] as of #1, 1 row read; tracker [Ines 30] -> wrong cqrs · delivered twice: dashboard [Ines 30] as of #1, 1 row read; tracker [Ines 30] -> fresh one-model · the correction arrives first: dashboard [Ines 45] as of #2, 1 row read; tracker [Ines 45] -> fresh naive · the correction arrives first: dashboard [Ines 75] as of #2, 1 row read; tracker [Ines 45] -> wrong cqrs · the correction arrives first: dashboard [Ines 45] as of #2, 1 row read; tracker [Ines 45] -> fresh
05 / Review the agent’s diff
“I update the leaderboard in the same transaction, so it’s instant.”
Members complained that a run they had just logged was missing. Read what the fix does to the projector that is still running.
06 / How it fails
A copy fails by being late, or by being wrong and not knowing it.
Each row except the last is a shared scenario the tests run.
| What happens | What Ines sees | One model | Trusting projection | Versioned projection |
|---|---|---|---|---|
| She reads right after logging | Her run missing, for a moment | Fresh. | Behind by one; asOf says so. | |
| A change is delivered twice | Double minutes, for good | Not affected. | Counted twice. | The copy is ignored. |
| A correction arrives before its workout | 30 + 45 minutes | Not affected. | Both counted. | The older version is dropped. |
| A deletion arrives first | A deleted run still counted | Not affected. | The run comes back. | The deletion wins; older changes are dropped. |
| Two corrections from one version | One of them refused | The tracker refuses the second before any copy sees it. | ||
| The copy is wrong | A total that never fixes itself | Not applicable. | Rebuild from the feed. | |
| The projector stops | A leaderboard frozen in time | Not applicable. | asOf stops moving; only an alert on lag notices. Not modeled in the example. | |
Delivery twice is normal, not a bug: Idempotency and at-least-once is why a projector has to be safe to run twice.
07 / Is it worth it?
A second model, a projector, and a lag, against one query that may be fast enough.
| Change | One model | Write model and read model |
|---|---|---|
| A second entry point: a watch syncs a week of runs at once | The same inserts; the leaderboard slows while it runs. | The same commands; the projector catches up after. |
| Replacing a dependency: the leaderboard moves to a cache | A cache in front of a query, and its invalidation. | A new projection target; commands do not change. |
| A new rule: cycling counts at half the minutes | Change the query. Done. | Change the projector, then rebuild. More work. |
| A second team: coaches want a monthly report | Another heavy query on the same table. | Another projection from the same feed, owned by that team. |
The costs are real: a second table, a projector to run and watch, a lag every screen has to be honest about, and a rule change that needs a rebuild. When the leaderboard query is fast enough on the history you actually have, one model is simpler and always fresh.
Measure before you change anything, with one model still running:
- Leaderboard latency and log-a-run latency, at the 95th percentile, on a copy of production’s history, and again with members reading while others log. This is the baseline and the reason to split, or not.
- Projection lag: the feed’s last position minus
asOf, and how old the oldest unapplied change is. The number to alert on after the split. - Mismatches from a nightly comparison of the read table with a full recount. It should be zero; anything else is a projector bug to fix with a rebuild.
This lesson did not measure a real tracker, and gives no numbers.
08 / Ask for it
One brief, two prompts.
Two agents running Claude Sonnet each got the brief from section 01, with the endpoints and the
product sentence: the leaderboard “must stay fast as the club’s history grows to hundreds of
thousands of workouts”, and a member “expects the leaderboard to reflect” a change. One prompt
added a Read model block: a read table the request never sums, change records
with a position in the same transaction, a projector that applies only newer versions and
resumes after a restart, position and asOf in the replies, and a rebuild.
A script logged, corrected, and deleted runs against both builds, read straight after, killed the
servers, and timed both paths over a year of history.
| Question | Plain prompt | Read-model prompt |
|---|---|---|
| A week of logs, corrections, and deletions | matches the checker’s tally | matches the checker’s tally |
| Read straight after each of 20 runs | 20 of 20 already in; the reply carries id, version | 20 of 20 already in; the reply carries id, version, position |
| Two corrections from the same version | 200 and 409; the leaderboard shows the one accepted | 200 and 409; the leaderboard shows the one accepted |
| Killed straight after 40 runs, then restarted | 40 accepted; matches the checker’s tally | 40 accepted; matches the checker’s tally |
| Leaderboard over a year of history | 30,100 workouts: 0.5 ms median, 1.5 ms at the 95th percentile | 30,100 workouts: 0.5 ms median, 1.6 ms at the 95th percentile |
| Reads and runs at the same time | Reads 1 ms median, 9.9 ms at the 95th percentile; runs 1.1 ms median, 8.1 ms at the 95th percentile | Reads 1 ms median, 4 ms at the 95th percentile; runs 1.1 ms median, 4 ms at the 95th percentile |
| Rebuild the leaderboard | No way to rebuild (HTTP 404) | Rebuilt; matches the checker’s tally |
| The leaderboard says how fresh it is | No | Yes: asOf |
| Its own tests | 16 of 16 pass | 18 of 18 pass |
Both builds answered every question correctly, and both kept the leaderboard in step with no lag at all. The plain agent read “must stay fast” and built a read model without being asked: a weekly totals table, updated in the same transaction as every write. That is the simplest honest answer to a slow dashboard, and for a single database it is the one to try first.
// The leaderboard is read far more often than workouts are written, and must
// stay fast even with hundreds of thousands of workouts in the history. To
// achieve that, we do NOT compute SUM(minutes) over the workouts table on
// every read. Instead we maintain a small aggregate table, weekly_totals,
// keyed by (team, week, member), and update it incrementally inside the same
// transaction as every insert/update/delete of a workout. A leaderboard read
// is then a single indexed range scan over just that team+week's rows,
// independent of how many workouts have ever been logged.The read-model build had a feed and a projector, and still never lagged: “expects the leaderboard to reflect it” led the agent to run the projector inside each command, before the reply, and again at the start of every read.
// Same process: apply the change to the read model before replying, so a
// client that immediately re-reads the leaderboard sees its own write.
catchUp(db);
/**
* Reads the leaderboard for one team/week straight from the pre-aggregated
* read table — never by summing the workouts/changes tables. Catches the
* projector up first (a no-op if it is already current) so `asOf` is
* always accurate and callers get read-your-writes within this process.
*/
export function getLeaderboard(
db: DatabaseSync,
team: string,
week: string,
): LeaderboardResult {
const asOf = catchUp(db);
So the lag this lesson is about never appeared, because neither prompt allowed it. What the block bought was what the plain build cannot do: tell a client how fresh an answer is, and rebuild the table from a record when it goes wrong. The plain build’s totals are right because its code is right today; the day a bug adds a run twice, there is nothing to rebuild them from.
The missing line is the product decision both prompts left implicit: say whether the leaderboard may lag a write, and by how much. “A member expects to see it” quietly meant “never lag”, which keeps the copy in the write’s process and its database. Allowing a few seconds is what lets the copy move to another store, another process, or another team, and that is when the failures in section 06 start to matter. The prompt snippet in section 10 asks for both the lag and the rebuild.
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, starts the server, and compares the leaderboard with its own tally of what it logged, corrected, and deleted.
- The checker ran twice: first on the plain build alone while the read-model agent was still working, then on both. The table reads the second run; the plain build’s answers were the same, and its timings moved by a few milliseconds between the two, most for reads under writes at the 95th percentile (2.1 ms in the first run, 9.9 ms in the second).
- The timings are one run on one laptop, both servers on the same machine as the checker. They compare the two builds with each other and nothing else.
- The plain agent wrote two reply files to
/tmpduring its own check, against the prompt, and left them there. Neither agent 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
A read model breaks when a second writer appears. Three checks notice.
The database’s door: the change and its record in one transaction
SQLite’s documentation: “No reads or writes occur except within a transaction” (SQLite, Transaction). Write the workout and its change record inside one explicit transaction, so the feed can never miss a change the tracker accepted, or hold one it refused.
Only the projector writes the leaderboard table
The diff in section 05 is the failure: a handler that updates the read table directly makes a second writer that no rebuild can replay. A rule that allows writes to that table from the projector’s module and nowhere else keeps it out, the way Enforcement layer enforces import rules.
Compare the copy with a recount, and alert on lag
Tests that deliver twice, backward, and after a restart cover the projector. In production, a nightly job recounts a sample of teams from the tracker and compares; an alert fires when
asOfstops moving.
Your query cache is already a read modelA mutation writes, a cached query reads, and invalidation is how the two stay in step.
Where it already is in your components
If you use TanStack Query or SvelteKit, your components already keep commands and queries
apart. A mutation sends the change; a query holds a cached copy of the answer; and you
tell the copy it is out of date. TanStack’s guide on invalidation says it directly: “when
a mutation in your app succeeds, it’s VERY likely that there are related queries in your
application that need to be invalidated and possibly refetched” (TanStack Query). In SvelteKit, use:enhance will “invalidate all data using invalidateAll on a successful response” (SvelteKit, form actions).
When you have to own it
Invalidation assumes the server answers a refetch with the new state. When the server’s
leaderboard is a projection, the refetch straight after a successful log can still be the
old one, and the screen says the run is missing. You own the gap: keep the position the
command returned, compare it with the leaderboard’s asOf, and show “updating”
and ask again until it is in.
A leaderboard and a log button: the mutation invalidates the leaderboard query, or the form action reruns load.
import { useMutation, useQuery, useQueryClient } from '@tanstack/react-query';
type Board = { week: string; rows: { member: string; minutes: number }[] };
// The command and the query are already two paths: a mutation writes, a
// cached query reads, and invalidation tells the read side it is out of date.
export function Leaderboard({ team, week }: { team: string; week: string }) {
const queryClient = useQueryClient();
const board = useQuery({
queryKey: ['leaderboard', team, week],
queryFn: async (): Promise<Board> =>
(await fetch(`/teams/${team}/leaderboard?week=${week}`)).json()
});
const logRun = useMutation({
mutationFn: (minutes: number) =>
fetch('/workouts', {
method: 'POST',
headers: { 'content-type': 'application/json' },
body: JSON.stringify({ member: 'Ines', team, minutes, day: week })
}),
onSuccess: () => queryClient.invalidateQueries({ queryKey: ['leaderboard', team, week] })
});
return (
<section>
<button onClick={() => logRun.mutate(30)} disabled={logRun.isPending}>
Log a 30-minute run
</button>
<ol>
{board.data?.rows.map((row) => (
<li key={row.member}>
{row.member}: {row.minutes} min
</li>
))}
</ol>
</section>
);
}
10 / Make the call
Split the read when one question is fighting the writes, and not before.
Keep one model while the query is fast enough on real history: it is always fresh and has nothing to rebuild. The next step is usually a summary table updated in the same transaction as each write, which is what the plain agent in section 08 built on its own; it stays fresh, and it cannot be rebuilt or moved. Give a question its own copy when it is read far more than the data changes, when its shape fights the write model’s (totals, rankings, search), or when another team needs its own view of the same changes. Reopen the decision when the lag shows up in support tickets, or when the read side needs history the tracker does not keep; that is where event sourcing starts to pay.
Take it with you
Explain it without saying “CQRS”: “The club secretary keeps the official logbook and checks every entry. Someone else keeps the scoreboard on the wall, updated from a list of every change the secretary made, numbered in order. The scoreboard says ‘up to change 41’ in the corner, ignores a change it has already seen, and if it is ever wrong you wipe it and replay the list.” Then find the slowest screen in your own app and ask whether it is answering from the same tables your writes go to.
Paste into your next prompt, and fill in the blanks
Keep [the leaderboard] in its own read table, shaped for that one query; the request never adds up [the workouts table]. The [leaderboard] may lag a write by up to [5 seconds]; the projector runs [in the background], not inside the command. Every accepted change to [a workout] writes a change record with an increasing position in the same transaction. One projector is the only writer of the read table. It applies changes in position order, and skips a change whose version is not newer than the one it holds, so a repeat or a late change does nothing. The projector records the last position it applied and resumes from it after a restart; [POST /admin/rebuild] rebuilds the table from the change records. Command replies include the change's position; the [leaderboard] reply includes asOf, so the app can show "updating" until its own change is in. Alert when the projector is more than [5 seconds] behind, and compare the read table with a full recount [nightly].
Connections to follow nextRelated lessons
- Event sourcing makes the feed the record, and the next lesson in this section.
- Change data capture feeds a read model from the database’s own log instead of a table the code writes.
- Transactional outbox is the same move as the change record: the change and its message in one transaction.
- Server state in the client is the query cache from the frontend row, in depth.
- Idempotency and at-least-once is why the projector must be safe to run twice.