In previous 2 posts, we explored what pseudotime is, the conceptual area it covers and how it can be leveraged as a force that provides the foundation for valuable software properties and capabilities. This post is all about seeing pseudotime as demonstrated by a minimal implementation running in common tools (any SQL database). A runnable demo is linked at the end.
The Schema
To govern the "writes" of an application and organise them via pseudotime, we need an object we can grab to serialize access to a "world"
In database-backed applications, this is easy to setup with a table of "worlds" (in DDD/event sourcing jargon you might call them "aggregates"), using a schema such as:
CREATE TABLE worlds (id, pseudotime)
a typical TODO tasks table then "belongs to" a world via 3 columns, and houses its own domain meaning in other columns:
CREATE TABLE tasks (id, eid, content, completed, world_id, created_at_pseudotime, valid_before_pseudotime)
The eid (entity ID) is the actual "ID" of the task in a common sense ("I have 3 tasks" => 3 eids). The content column holds the actual domain content (e.g. "buy milk"). The remaining columns — world_id, created_at_pseudotime, valid_before_pseudotime — link this entity to a world in a pseudotime-organized way.
The Reads
to access our Todo tasks, we would use queries like this:
-- time_in_world(world_id) SELECT pseudotime FROM worlds WHERE id = ? -- world_id -- what is the state of the world at the time? (T = pseudotime) -- tasks_of_world_at_time(world_id, pseudotime) SELECT * FROM tasks WHERE world_id = ? -- world_id AND created_at_pseudotime <= ? -- T AND (valid_before_pseudotime >= ? OR valid_before_pseudotime IS NULL) -- T
As you can see, world provides basic multi-tenancy (my tasks are in my world, yours are in yours) and houses the "what's the current time".
The Writes
A typical interaction with an application rarely involves "writes" alone. To permit or inform a write, we often need to do a read first. So "write" flow has to orchestrate the reads and writes by giving our application code access to a "moment" (facilitating the write-informing "reads") and housekeep the "revisions" the application transaction provides. 4 steps:
(all steps are within a BEGIN ... COMMIT transaction)
Step 1: Acquire exclusive access to the current moment
SELECT pseudotime FROM worlds WHERE id = ? FOR UPDATE
(SQLite may skip FOR UPDATE because it's a single-writer system already)
Now we know the current T (pseudotime) at which our subsequent reads will be at.
Note: The reason it's ok to do a single-writer-per-world approach like this is:
- human-paced business apps do not generate the amount of "contention" that would cause a performance bottleneck.
- Things can be optimized by switching this "pessimistic locking" strategy to an "optimistic one" ( a classic )
- When the domain is relatively well understood, you can draw the lines between worlds differently such that contention is further minimized. But really, the "updates per second" (contention rate) would need to be very high to justify this.
Step 2: Read the current state
We can now access the current state of tasks via the tasks_of_world_at_time(world_id, pseudotime) shown in the previous section.
This is the load-bearing ordering rule: reads that influence writes must be inside the transaction, not before/outside.
Step 3: Produce the list of what's true in the next state
This part is opaque to SQL - your application produces what it wants to tell the database about. A list of entity objects, each with its eid is all we need for the next step.
Step 4: House keeping:
Expire old revisions
UPDATE tasks SET valid_before_pseudotime = ? -- T WHERE eid = ? AND valid_before_pseudotime IS NULL -- eid
Insert new revisions (skip this step if you're "deleting" an entity)
INSERT INTO tasks (eid, content, world_id, created_at_pseudotime, valid_before_pseudotime) VALUES (?, ?, ?, ? + 1, NULL) -- eid, content, world_id, T
Advance the time forward
UPDATE worlds SET pseudotime = ? + 1 WHERE id = ? -- T, world_id
In something like Ruby, you can imagine the above being hidden behind a signature such as:
# marking a task as completed World.find(world_id).transact do |moment| task = moment.find_task(task_id) return if task.completed? [Task.new(task.merge(completed: true))] end
or in Javascript:
// marking a task as completed await World.find(worldId).transact(async (repo) => { const task = await repo.findTask(taskId); if (task.completed) return []; return [{ ...task, completed: true }]; });
The past is accessible
Since the only "mutation" our "write" did is fill in a valid_before_pseudotime, any past state of the world would still be accessible via the same "read" query for any past T (recall the query from above):
-- tasks_of_world_at_time(world_id, T) SELECT * FROM tasks WHERE world_id = ? -- world_id AND created_at_pseudotime <= ? -- T AND (valid_before_pseudotime >= ? OR valid_before_pseudotime IS NULL) -- T
Imagine we use our above read/write process to execute a series of 4 transactions (the full range of CRUD):
- Insert "buy bread"
- Insert "buy milk"
- Delete "buy bread"
- Update "buy milk" → "buy ice-cream"
Our tasks_of_world_at_time read, given pseudotimes 1,2,3,4 would yield the following "moments" as they were when they "happened":
left of | = visible state at that ptime; right = raw table rows
name(created_pt → valid_before_pt); empty arrow = still current
p=0 [] | (empty)
p=1 [buy bread] | buy bread(1→)
p=2 [buy bread, buy milk] | buy bread(1→), buy milk(2→)
p=3 [buy milk] | buy bread(1→2), buy milk(2→)
p=4 [buy ice-cream] | buy bread(1→2), buy milk(2→3), buy ice-cream(4→)
The key thing to notice: the tasks table never loses rows. Every prior revision is still there — "buy bread" at ptime 1, "buy milk" at ptime 2 — each with a valid_before_pt showing when it was superseded. The visible state shifts as pseudotime advances, but the full history is retained and queryable at any time. Re-running the same read query at ptime 2 (even after the world has advanced to 4) will return buy bread and buy milk — that's time travel.
A pseudotime gives you a handle that provides access to all the entities in the world "as of" that pseudotime.
Databases like XTDB or Datomic sidestep the question of what entities belong in or outside a given "world" by providing a database-wide, global pseudotime (system_time in XTDB, txid in Datomic). These are wonderful databases and the better way to get pseudotime and time-travel ergonomics "out of the box".
This implementation, by contrast, is closer to event sourcing: it's up to your application to choose where one world ends and another begins. Writes involving two or more worlds are not ordered — simultaneity is allowed across world boundaries. As discussed in the earlier post, what is or isn't allowed to be simultaneous varies from application to application. This approach sides with the event-sourcing community in arguing that contention management is an application-by-application issue.
A runnable demo
You can experiment with a runnable demo over here. It's based in Ruby, but runs in a docker container so you don't have to worry about dependencies. The code is short and demonstrates a sketch of the ergonomics of this organization of data.
Summary
With one extra table and three columns per entity table, any SQL database can retain full history and answer point-in-time queries — no Datomic, no event-sourcing framework, no wall-clock machinery. A per-world counter serializes writes; a single WHERE clause addresses any past moment. The cost is single-writer-per-world, which is rarely a bottleneck in human-paced business apps. For multi-tenant applications where each account is its own world, the pattern is immediately practical.
In future posts, I'll explore what schema evolution looks like when past states are retained as first-class, and other related topics.
If this helped you understand what "pseudotime" is all about, or if you have questions or comments, I'd love to hear from you. Cheers!