From a table to a log

A post after the series CQRS with effect-cqrs, on its lending library. November 2026.

The series built its library from the first event. Nobody’s real project starts there. The library already runs on a database, with a books table that has years of data in it, and the question after reading about event sourcing is not how do I start but how do I get there from here.

This post moves the library’s books from a table to a log, without stopping it and without pretending to know more than the table did. Every snippet runs in examples/blog/table-to-log.test.ts.

What the table knows

examples/blog/table-to-log.ts

/** A row of the old `books` table, as `SELECT * FROM books` returns it. */
export interface BookRow {
  readonly id: string
  readonly title: string
  readonly author: string
  readonly lent_to: string | null
  readonly loan_id: string | null
  readonly due_on: string | null
}

export const rows: ReadonlyArray<BookRow> = [
  {
    id: "book-1",
    title: "Dune",
    author: "Frank Herbert",
    lent_to: "alice",
    loan_id: "L-1042",
    due_on: "2026-12-01T00:00:00Z"
  },
  { id: "book-2", title: "Emma", author: "Jane Austen", lent_to: null, loan_id: null, due_on: null }
]

The table knows each book’s current state: its title, its author, who has it and until when. It doesn’t know when the book was added, how many times it has been lent, or who had it before Alice. That history was thrown away one UPDATE at a time, as part 1 put it. No migration can bring it back.

Don’t invent the past

The tempting import writes the history the events would have had: a BookAdded, then a BookLent for Alice. Every read model would work at once, since they already handle those events.

It is also false. The BookAdded would carry the date of the import, not the date the book was added. The BookLent would claim Alice borrowed the book at the moment of the import. Any read model that counts loans per month, or measures how long books stay out, would now count a spike of fake loans on import day. A log is valuable because it says what happened, and an import that makes things up spends that on day one.

Import what the table says

Instead, the import is a fact of its own: on this date, the old system said this about the book. It gets an event that says exactly that:

examples/blog/table-to-log.ts

const Loan = Schema.Struct({ loanId: Schema.String, memberId: Schema.String, dueOn: Schema.DateTimeUtc })

export const BookEvent = Schema.TaggedUnion({
  // What the old system knew about the book on the day of the import, and nothing more.
  BookImported: { title: Schema.String, author: Schema.String, loan: Schema.NullOr(Loan) },
  BookAdded: { title: Schema.NonEmptyString, author: Schema.NonEmptyString },
  BookLent: { loanId: Schema.String, memberId: Schema.String, dueOn: Schema.DateTimeUtc },
  BookReturned: { loanId: Schema.String, memberId: Schema.String, late: Schema.Boolean }
})

BookImported carries the row’s state, the loan included if there is one, and nothing more. The book’s projection starts from it like from any other first event, and the importBook command records it only for a book that has no stream yet:

examples/blog/table-to-log.ts

export const Book = Aggregate.make<BookState>()({
  name: "book",
  events: BookEvent,
  initial: { status: "unknown", loan: null },
  projection: {
    BookImported: (_, { loan }) => ({ status: loan === null ? "available" : "lent", loan }),
    BookAdded: () => ({ status: "available", loan: null }),
    BookLent: (_, loan) => ({ status: "lent", loan }),
    BookReturned: () => ({ status: "available", loan: null })
  }
})
  .command("importBook", {
    payload: Schema.Struct({ title: Schema.String, author: Schema.String, loan: Schema.NullOr(Loan) }),
    // A book that already has a stream was imported already: running the import again is harmless.
    handler: (state, payload) =>
      Effect.succeed(state.status === "unknown" ? BookEvent.cases.BookImported.make(payload) : [])
  })

The import script reads the rows and sends one command per row, with metadata naming where the data came from:

examples/blog/table-to-log.ts

/** Writes one `BookImported` per row. Safe to run again, for instance after a failure halfway. */
export const importBooks = Effect.fnUntraced(function*(rows: ReadonlyArray<BookRow>) {
  const books = yield* AggregateService.make(Book)
  for (const row of rows) {
    const loan = row.lent_to === null || row.loan_id === null || row.due_on === null
      ? null
      : { loanId: row.loan_id, memberId: row.lent_to, dueOn: DateTime.makeUnsafe(row.due_on) }
    yield* books.commands.importBook(row.id, { title: row.title, author: row.author, loan })
  }
}, Metadata.withMetadata({ importedFrom: "books table", script: "scripts/import-books.ts" }))

It can be run as often as needed. A book that has a stream is skipped, so a script that dies halfway through ten thousand rows is simply started again.

Carry on

Once a book has a stream, it only changes through commands:

examples/blog/table-to-log.ts

yield* importBooks(rows)
yield* importBooks(rows) // the second run records nothing

// From here on, the books only change through commands.
yield* books.commands.returnBook("book-1", {})
yield* books.commands.lend("book-2", { loanId: "loan-1", memberId: "bob" })
yield* viewModels.caughtUp()

const history = yield* books.history("book-1")
// BookImported, with metadata { importedFrom: "books table", … }, then BookReturned
const catalog = yield* Catalog.use(Ref.get)
// Map { "book-1" => { title: "Dune", …, available: true }, "book-2" => { title: "Emma", …, available: false } }

Alice brings Dune back, and the book decides on it with the due date from the old table. Emma is lent to Bob. The book’s history says where it came from: the first event is the import, with the script in its metadata, and from then on, what really happened.

Read models need one more handler, for BookImported. Here is the catalog of the series with it:

examples/blog/table-to-log.ts

// The catalog of the series, which now also reads imported books.
export const CatalogView = ViewModel.make({
  name: "catalog",
  aggregates: [Book],
  dispose: Catalog.use((catalog) => Ref.set(catalog, new Map())),
  handlers: {
    BookImported: ({ author, loan, title }, { aggregateId }) =>
      Catalog.use((catalog) =>
        Ref.update(catalog, (entries) => new Map(entries).set(aggregateId, { title, author, available: loan === null }))
      ),
    BookAdded: ({ author, title }, { aggregateId }) =>
      Catalog.use((catalog) =>
        Ref.update(catalog, (entries) => new Map(entries).set(aggregateId, { title, author, available: true }))
      ),
    BookLent: (_, { aggregateId }) => setAvailable(aggregateId, false),
    BookReturned: (_, { aggregateId }) => setAvailable(aggregateId, true)
  }
})

A read model like “loans per month” simply ignores it: an imported loan didn’t start that month, and the event says so by not being a BookLent.

Switching over

The import is the easy part. The hard part is the day the old code stops writing the table.

• One aggregate type at a time. Move books first, members later. Commands on books go through the new code, and the rest of the application keeps its tables until its turn.
• Stop writes to the table, then import. Anything written to the table after the import is lost to the log. Make the old code read-only for books, run the import, switch the commands over, and turn writes back on through the new path. If that pause is too long, import first, then import again for the rows that changed in between, as BookImporteds for new books and corrections for the others, then switch.
• Keep the old queries working with a read model in the old table’s shape, written by a view model. Reports and other teams that read books don’t notice the move, and can be moved to better read models one by one.
• Keep the table read-only for a while after the switch, to compare with the read models. Drop it once they agree.

Note

If the old database has an audit or history table, it can be imported too, as events of their own, such as LoanRecordedBeforeImport, with the dates it recorded. Read models then decide whether to trust them. The same rule holds: import what was recorded, and invent nothing.

Rules of thumb

• An import is an event. BookImported says what the old system knew, on which day, from where. It doesn’t pretend to be the history the table never kept.
• Make the import idempotent. One event per row, skipped if the stream exists, so the script can run again after any failure.
• Put the source in the metadata. Every imported stream then says where it came from, and an import with a bug can be found and corrected.
• Move one aggregate type at a time, and keep the old shape alive as a read model until nobody reads it.

Next

Why CQRS and event sourcing is where the series starts, and the storage guide covers moving to SQLite or PostgreSQL.