Never Delete Rows

Ever wondered how the data got into such a weird situation? Cancelled state, yet the DB says "completed_at" is yesterday? Operation succeeded, the patient died?

There's a simple rule of thumb that I apply for every table that tracks anything meaningful: rows go in, they never leave. No deletes. No updates in place. New row for every state change.

It's the core concept behind event sourcing. I'll talk about a simplification I use before I elaborate on that.

It might sound wasteful or redundant at first. Storage is cheap, though, and the thing you're storing is something you cannot reconstruct after the fact: a complete history of what happened and when.

And when working with money? You really want to know what happened and when.

---

What you lose when you update

Say a sync record transitions from ready to submit_failed. If you update in place, the ready state is gone. You know where it ended up. You don't know the path.

That's fine until it isn't. Until a bug causes records to skip states. Until a retry runs twice. Until a client asks why their invoice changed between Monday and Tuesday.

An append-only table answers all of those questions without a debugger. Every state the record ever held is a row. The current state is just the latest one.

Here's what that looks like in practice — the comment at the top of the model is the entire policy:

class FootifyMatter(Base):
    """
    Tracks Footify matters fetched via SOQL and their APP submission state.

    Append-only: never update/delete - insert new row for each state change.
    Deduplication: check for existing record with same footify_id + created_at.
    """

That's it. One comment. The discipline it enforces is worth more than any framework.

The same principle went into Quillet — the newsletter engine running this very post. There's an audit_log table that records every subscribe, confirm, unsubscribe, publish, and send. It never gets updated. It never gets pruned. If something weird happens with a subscriber, the log has the answer.

_audit_log = Table(
    "audit_log",
    metadata,
    Column("id", Integer, primary_key=True, autoincrement=True),
    Column("newsletter_id", Integer, ForeignKey("newsletters.id"), nullable=True),
    Column("event_type", String(64), nullable=False),
    Column("details", Text, nullable=False, default="{}"),
    Column("created_at", DateTime, nullable=False),
)

No updated_at. No soft-delete flag. No mutation path. If it happened, it's a row. If it didn't, it isn't.

---

The mature version of the same idea

In a full event-sourced system, you don't store state at all. You store events — MatterCreated, BuildFailed, SubmittedToAPP — and current state is a projection you derive by replaying them. Want to know what a record looked like last Tuesday? Replay events up to that timestamp. Want to add a new view of the data? Build a new projection from the same events.

It's powerful. It's also a genuine infrastructure commitment — event stores, projections, eventual consistency, CQRS if you go the full distance. The kind of thing that earns its complexity when you're building a financial ledger or an audit-critical system at scale.

The append-only table is what you reach for when you want the core benefit — reconstructable history — without the architecture overhead. Same idea, different weight class.

---

When to reach for which

Default to append-only. If something meaningful changes state and you might ever need to explain it — almost everything — don't delete the row.

Graduate to Event Sourcing when state is a projection, not a fact: when you need temporal queries, system replays, or multiple views of the same events. It earns its weight when the domain demands it. Until then, it's overhead.

The rule of thumb: someone will ask "what happened to this record" during an active outage. Make sure you can answer. The cheapest way to ensure that is to never throw anything away.

This pairs excellently with The Same Machine, Twice — state machines govern what can happen next; append-only records preserve what already happened. Together, they make a system you can debug without even turning on your IDE.