Versioned Event Table Structure

Summary

Store every version as a new row. Keep the event that produced it, generate each version with a pure function, and mark one row per ID as the latest version.

A versioned event table never updates domain state in place. Each change inserts a new row for the same logical entity, with a monotonically increasing version number.

The important columns are:

• id: the stable ID of the logical entity.
• version: the version number for that ID.
• latest_version: whether this is the current version.
• event: the complete input used to produce this version.
• state columns: the data for this version, such as title, body, and status.

latest_version avoids finding MAX(version) or sorting versions on every current-state read. A compound index on (id, latest_version) makes finding the latest row for one ID a direct lookup.

Example SQL

A blog post can be a draft, active, or deleted. Changing its status inserts a new row rather than updating the current row.

CREATE TABLE blog_posts (
    id UUID NOT NULL,
    version BIGINT NOT NULL,
    latest_version BOOLEAN NOT NULL,
    event JSONB NOT NULL,
 
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('draft', 'active', 'deleted')),
 
    PRIMARY KEY (id, version)
);
 
CREATE UNIQUE INDEX blog_posts_one_latest_version
ON blog_posts (id, latest_version)
WHERE latest_version = TRUE;

The primary key is a compound index for version history. The partial compound index both speeds up current-state reads and enforces at most one latest row per ID.

Latest version of one post:

SELECT *
FROM blog_posts
WHERE id = $1
  AND latest_version = TRUE;

Full history of one post:

SELECT *
FROM blog_posts
WHERE id = $1
ORDER BY version;

Example history for one blog post

Every row has the same id. Only the newest row has latest_version = true.

Version	Latest	Event	Title	Status
1	false	Created { title: "Hello world", body: "My first post" }	Hello world	draft
2	false	SetVisibility { visible: true }	Hello world	active
3	true	Deleted	Hello world	deleted

Reading the latest row returns version 3 immediately. Reading all rows in version order reconstructs the post’s complete history.

Events and version generation

The event must contain everything needed to change the domain state. No caller may alter the generated version afterward.

type BlogPostEvent =
  | { type: "Created"; title: string; body: string }
  | { type: "SetVisibility"; visible: boolean }
  | { type: "Deleted" };
 
type BlogPostVersion = {
  version: number;
  event: BlogPostEvent;
  title: string;
  body: string;
  status: "draft" | "active" | "deleted";
};
 
function generateNewVersion(
  currentVersion: BlogPostVersion | null,
  event: BlogPostEvent,
): BlogPostVersion {
  switch (event.type) {
    case "Created":
      if (currentVersion) throw new Error("Post already exists");
      return {
        version: 1,
        event,
        title: event.title,
        body: event.body,
        status: "draft",
      };
 
    case "SetVisibility":
      if (!currentVersion) throw new Error("Post does not exist");
      return {
        ...currentVersion,
        version: currentVersion.version + 1,
        event,
        status: event.visible ? "active" : "draft",
      };
 
    case "Deleted":
      if (!currentVersion) throw new Error("Post does not exist");
      return {
        ...currentVersion,
        version: currentVersion.version + 1,
        event,
        status: "deleted",
      };
  }
}

generateNewVersion(currentVersion, event) -> newVersion is pure: the same current version and event always produce the same new version. Making a post visible is represented by SetVisibility; deleting it is represented by Deleted. There is no separate mutation hidden outside the event.

Create and update

There are only two write operations. Both call generateNewVersion and persist its result.

type CreatedEvent = Extract<BlogPostEvent, { type: "Created" }>;
type UpdateEvent = Exclude<BlogPostEvent, CreatedEvent>;
 
async function create(event: CreatedEvent) {
  const id = crypto.randomUUID();
  const newVersion = generateNewVersion(null, event);
 
  await db.insert({
    id,
    ...newVersion,
    latest_version: true,
  });
 
  return { id, ...newVersion };
}
 
async function update(id: string, event: UpdateEvent) {
  return db.transaction(async tx => {
    const currentVersion = await tx.getLatestForUpdate(id);
    if (!currentVersion) throw new Error("Post does not exist");
 
    const newVersion = generateNewVersion(currentVersion, event);
 
    await tx.markLatestFalse(id, currentVersion.version);
    await tx.insert({
      id,
      ...newVersion,
      latest_version: true,
    });
 
    return { id, ...newVersion };
  });
}

The update transaction must serialize concurrent writes for the same ID. It clears the old latest_version flag and inserts the new latest row atomically.

IDs, version numbers, and latest_version are persistence metadata. Every domain-state change - title, body, status, or any future field - must come from the event passed to generateNewVersion.