CRxx: poor man's event sourcing

I have wanted to use event sourcing since I heard Greg Young’s talk.

• Using a ledgerlike log of events means immutability.
• Treating state as a fold over events fits with FP patterns.
• Events are the ground truth.
• Capturing events at high fidelity allows two kinds of “time machine”:
  • I wish I hadn’t mutated that data; and
  • I wish I had started calculating that data earlier.
• Ledgerlike events provide a robust change history and troubleshooting log.

The problem is that event sourcing can get hard in practice. None of what follows is inherent to event sourcing itself, but once events cross service boundaries you tend to inherit distributed-systems problems like Lamport clocks, infrastructure like Kafka or Kinesis, and deletion obligations under GDPR or other privacy laws.

Within a consistency boundary, I have been using a system that gets several of the benefits of event sourcing without a queue, distributed schema, etc. I’ve been calling it CRxx, as in CRUD, but without the update or delete (except as required by regulation). It uses ordinary SQL, which gives us transaction boundaries, a single authoritative write model, and straightforward ordering where we need it.

The benefits of immutable data are known throughout industry, and are especially relevant to functional programming. As Greg Young said in his talk, accountants don’t use erasers.

This is deliberately not full event sourcing. Like event sourcing, it has an append-only history, immutability, and allows some “time travel,” but stores immutable state snapshots, not events/diffs.

Example: a user model whose name can change

Consider a user model, and consider further that a user can change her name. The table is intentionally just the fact of identity:

CREATE TABLE
  user_identities
  ( id uuid PRIMARY KEY DEFAULT uuid_generate_v7()
  );

Note that we can use UUIDv7 in lieu of created_at, because a millisecond-resolution timestamp is derivable from a UUIDv7. And we don’t need updated_at because we don’t update!

The functions aren’t PostgreSQL built-ins; PostgreSQL 18 calls its built-ins uuidv7() and uuid_extract_timestamp(). Our generator encodes microsecond-level clock precision and adds a per-backend counter for IDs generated in the same microsecond. Given a nondecreasing database clock, IDs from a single backend (including within a transaction) sort in generation order. We can’t guarantee total order on concurrent writes from different backends within the same microsecond. I’ll cover that implementation and its tradeoffs in a follow-up post.

Now, every “mutation” inserts a new ledgerlike row. This describes a user’s name:

CREATE TABLE
  user_name_states
  ( id          uuid PRIMARY KEY DEFAULT uuid_generate_v7()
  , user_id     uuid NOT NULL REFERENCES user_identities(id)
  , given_name  text NOT NULL
  , family_name text NOT NULL
  );

-- Critical: this index powers the "latest state" resolution
CREATE INDEX idx_user_name_states_resolve
ON user_name_states (user_id, id DESC);

Per CRxx, we never update or delete. We reflect new information with a new row, and abstract over the fancy parts with a view that looks like an ordinary mutable table. The view joins the identity to its latest state using LEFT JOIN LATERAL:

CREATE VIEW user_names AS
SELECT i.id AS user_id
       , s.given_name
       , s.family_name
       , uuid_v7_to_timestamptz(i.id) AS created_at
       , uuid_v7_to_timestamptz(s.id) AS updated_at
FROM user_identities i
LEFT JOIN LATERAL (
  SELECT * FROM user_name_states
  WHERE user_id = i.id
  ORDER BY id DESC LIMIT 1
) s ON true;

The LATERAL subquery picks the most recent state row for each identity. The (user_id, id DESC) index lets PostgreSQL resolve the latest state efficiently without sorting the state rows for each user.

Application code reads from the user_names view exactly as if it were a regular table: SELECT * FROM user_names WHERE family_name = 'Lovelace' works (the actual code tends to use INNER JOIN rather than LEFT JOIN to avoid adding a fully blank row).

CRxx doesn’t replace distributed systems

This approach works well within a consistency boundary, but doesn’t replace an event-driven system across consistency boundaries. One view of event sourcing says that it should live within a consistency boundary. According to that view, this approach fits within the same footprint as event sourcing.

If events cross a consistency boundary, ordinary techniques like transactional outboxes, idempotency, and asynchronous materialization are still necessary.

But within a consistency boundary, this approach gives you some useful properties of event sourcing. Keeping it all in a single write instance removes many ticklish distributed-systems issues, while abstracting behind a view lets you write more ordinary-looking read code without special features.

Using snapshots rather than events does lose the “ground truth” element of a truly event-driven system. A state row doesn’t record who commanded the change, what command it was, or why (though you’re free to add a provenance or JSONB metadata column). Anything not represented in the stored state is gone, so data computed from events may be unrecoverable. To be clear, losing this is a big deal. Rebuilding state by replaying the log is the capability Martin Fowler says defines event sourcing.

Algebra of partial updates

If an application needs to update fewer than all fields in a DB record and represents unchanged fields as NULL, simply taking the most recent state row wouldn’t work: the NULL fields would suggest the new update meant to set the field to NULL, rather than signify no change from the previous record.

Semigroups and monoids, oh my

Functional programming has a good answer: a semigroup. A semigroup is just fancypants talk for “a thing where you can combine two values,” where the combining operation has to be associative, meaning (A concat B) concat C gives the same result as A concat (B concat C).

And actually, while we’re at it, let’s add one more condition: there should be some value that doesn’t change anything if we concatenate using that specific value. Taking a semigroup and adding a value that, when you concatenate it, doesn’t change anything, means you now have a monoid.

Think about when you sum over an array in TypeScript:

[1n, 2n, 3n].reduce((sum, element) => sum + element, 0n);

Why that 0n? Because you need to set sum to some value to get started, and we know adding zero to any number leaves the number unchanged. Zero is the arithmetic identity. So bigints under addition form a semigroup, and when you throw in zero as the identity element (called mempty sometimes for monoid empty), they form a monoid.

This example uses bigint because floating-point numbers are weird in JS: floating-point addition isn’t associative (!), so Number under + isn’t actually a semigroup.

Why are you telling me this?

At which point the reader may ask, “Why in the hell are you telling me this?” Fair.

It’s because a monoid is a great abstraction for combining database rows. The semigroup capabilities provide the “mash ’em together in the right order but without caring how they associate” step. And the monoid capability means “mash ’em together” works even if we have just one row to do the “mashing together,” like the initial value in the reduce example, above.

And the coolest part is that if we know how to treat each column monoidally, we can make the whole row monoidal via composition (I’ll explain this again shortly).

Every field in the update payload is wrapped in Option:

import { Schema } from "effect";

const UpdateUserName = Schema.Struct({
	givenName: Schema.OptionFromOptionalKey(Schema.String),
	familyName: Schema.OptionFromOptionalKey(Schema.String),
});

• None means “the caller didn’t send this field” (identity: leave it).
• Some(value) means “set this field to value.”
• For clearable nullable fields: Some(None) means “clear it”, Some(Some(value)) means “set it.”

Now we need the combining definition. Effect v4 renamed these: Combiner is the semigroup-like abstraction, an associative combine with no identity, and Reducer is the monoid-like one, a Combiner plus the identity value:

import { Combiner, Option, Struct } from "effect";

// "Last Some wins": None is identity; a supplied value replaces.
const lastSome = <A>() => Option.makeReducer(Combiner.last<A>());

type UserNamePatch = {
	readonly givenName: Option.Option<string>;
	readonly familyName: Option.Option<string>;
};

const UserNameStateMerge = Struct.makeCombiner<UserNamePatch>({
	givenName: lastSome<string>(),
	familyName: lastSome<string>(),
});

Combiner.last is the underlying semigroup: given two actual values, it keeps the right one. Option.makeReducer lifts¹ that into an Option monoid: None is the identity, and two Some values combine by keeping the right one. Thus Some(old) combine None preserves old, while Some(old) combine Some(new) yields Some(new).

Struct.makeCombiner lifts those field-level combiners into a combiner for the whole record, applying each field’s rule independently. We don’t need an identity for the whole record because we always combine an existing state with a patch. We do need None to act as the identity at each field, because that is what makes an omitted field leave the existing value alone.

That’s a lot to grok, but think about what it means: field-level algebras compose, meaning that updating the overall record is a matter of composing the field-level algebras.

Or to not say it so fancypants, the things that let you mash each field together roll up beautifully into mashing together two whole rows/a row and an update.

The merge operation

So now we have the building blocks: tables that contain state snapshots and monoid definitions for merging rows. We can now merge in application code and write the new immutable state row with the result.

const updateUserName = (userId: string, input: UserNamePatch) =>
	Effect.gen(function* () {
		const db = yield* Db;

		// Read *inside* the lock: the snapshot we merge onto must not change
		// between this read and the insert below.
		const current = yield* readCurrentUserName(db, userId);

		const merged = UserNameStateMerge.combine(
			// Left: current state, all fields wrapped in Some
			{
				givenName: Option.some(current.givenName),
				familyName: Option.some(current.familyName),
			},
			// Right: update payload, fields are Option (None = not sent)
			{
				givenName: input.givenName,
				familyName: input.familyName,
			},
		);

		// A Kysely builder is not an Effect. `query` compiles it and runs it
		// through the SQL client. `Db`, `query` and `withEntityLock` are
		// project-defined, like the UUID helpers above.
		yield* query(
			db.insertInto("user_name_states").values({
				user_id: userId,
				given_name: Option.getOrThrow(merged.givenName),
				family_name: Option.getOrThrow(merged.familyName),
			}),
		);
	}).pipe(withEntityLock({ table: "user_identities", id: userId }));

The combine call is declarative and produces a complete snapshot under the invariant that the existing state is complete. Anchoring the combiner to UserNamePatch means adding a field requires defining its combining rule. Note several of these functions are project-defined.:

The transaction is critical. Read, merge and insert have to happen together under a lock on the identity row. withEntityLock does that: it opens a transaction and takes SELECT ... FOR UPDATE on user_identities before running the body. Without it, two concurrent partial updates can read the same snapshot, each merge its own field onto that stale copy, and both insert. The later row wins and the earlier update disappears, even though the two changed different fields and neither request failed. Append-only storage doesn’t fix the race condition, but it does mean the overwritten snapshot is recoverable.

Multi-tenant state layering

CRxx naturally extends to multi-tenant systems: tenant-scoped state overrides.

In our LSAT analytics platform, questions have platform-level metadata (sublabel, difficulty, correct answer) seeded from PrepTest editions. But tutors can override a question’s metadata for their tenant: relabel a question type, adjust difficulty, add notes, etc. Each override is a complete snapshot, so it applies only within that tenant’s view.

The question_states table has a nullable tenant_id:

CREATE TABLE question_states (
  id                  uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  question_id         uuid NOT NULL REFERENCES question_identities(id),
  tenant_id           uuid REFERENCES tenants(id),  -- NULL = platform baseline
  lsac_question_type  text NOT NULL,
  lsac_difficulty     smallint NOT NULL,
  sublabel            text NOT NULL,
  correct_answer      text NOT NULL,
  -- ...
);

• tenant_id IS NULL = platform baseline (seeded data, shared by all tenants)
• tenant_id = $tenantId = tenant override (a complete, tenant-specific snapshot)

The resolution CTE uses DISTINCT ON with tenant priority:

WITH resolved_states AS (
  SELECT DISTINCT ON (question_id) *
  FROM question_states
  WHERE tenant_id = $1 OR tenant_id IS NULL
  ORDER BY
    question_id
    , CASE WHEN tenant_id = $1 THEN 0 ELSE 1 END
    , id DESC
)
SELECT
  qi.id AS question_id
  , rs.id AS state_id
  , rs.tenant_id
  , rs.lsac_question_type
  , rs.lsac_difficulty
  , rs.sublabel
  , rs.correct_answer
FROM question_identities qi
INNER JOIN resolved_states rs ON rs.question_id = qi.id;

The CASE WHEN in the ORDER BY ensures tenant-scoped states have a higher sort priority than the platform baselines. The DISTINCT ON (question_id) picks the first row per question: the tenant override if one exists, otherwise the platform baseline. The id DESC tiebreaker picks the most recent state within each tier.

An index on (question_id, tenant_id, id DESC) supports looking up the relevant state rows; the exact resolution plan should be verified with EXPLAIN ANALYZE at production scale.