Domain Modeling with Event Sourcing and Decoupled Projections in Relational Databases

The Challenge of Traditional Persistence in Complex Systems

When building business-centric software, the common pattern is to update the current state of a record directly inside a database table. In practice, this means that if a user changes their shipping address, we overwrite the old data, erasing the past forever. In financial or e-commerce systems, losing the history of how we arrived at a certain state creates serious auditing issues and makes bug hunting much harder. The traditional CRUD (Create, Read, Update, Delete) model works fine for simple registries, but hits bottlenecks when domain complexity increases and we need to know exactly what happened, when it happened, and who authorized each change.

To solve this limitation, engineers adopt an event-driven approach where the absolute truth of the system is not the current state, but the chronological sequence of facts that occurred over time. In practice, this means every user action generates an immutable event, such as 'OrderCreated' or 'PaymentApproved', which is recorded in a log table. This unidirectional flow eliminates destructive concurrency disputes and turns the database into a reliable logbook. The great advantage is that any future audit becomes trivial, as you only need to read the log from start to finish to reconstruct the exact scenario of any past operation.

Implementing Event Sourcing with Traditional Relational Databases

There is a myth that event storage requires exotic or complex databases built around graphs and streams. In practice, mature relational databases like PostgreSQL or MySQL handle this workload exceptionally well when structured correctly. The technical secret consists of creating an append-only event table, meaning inserts are permitted, but updates and deletions are strictly prohibited by business rules and database constraints. Each row stores the entity identifier, the event type, the sequential version for optimistic concurrency control, and the complete payload serialized in a flexible format.

To illustrate this structure in practice, let's examine how we can model the event table and state recovery in a typical application using a pure relational approach. The code below demonstrates the basic SQL structure and a conceptual routine for state reconstruction by sequentially reading the accumulated events in the database.

CREATE TABLE event_store (
    event_id UUID PRIMARY KEY,
    aggregate_id UUID NOT NULL,
    event_type VARCHAR(255) NOT NULL,
    version INT NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_aggregate_version UNIQUE (aggregate_id, version)
);

CREATE INDEX idx_aggregate_stream ON event_store (aggregate_id, version);

Using JSONB columns in modern relational databases removes excessive rigidity from traditional schemas, allowing different event types to store varied data structures without requiring complex column migrations for every new feature. The uniqueness constraint on the combination of the entity identifier and version ensures that two concurrent transactions can never write the same version number, preventing race conditions and silent inconsistencies.

The Crucial Role of Decoupled Projections

Storing only events solves the problem of auditing and data safety, but introduces a new obstacle for reading. If we need to calculate the current balance of an account or a customer's active order list, reading thousands of events and summing them up every time the screen loads would be unfeasible from a performance standpoint. In practice, this means we must separate writes from reads. This is where decoupled projections come into play, serving as tables or data models optimized exclusively for fast querying, asynchronously fed by the event stream.

When a new event is inserted into the main event table, an internal messaging mechanism or background process captures that fact and updates the corresponding projection tables. In practice, this means the user interface queries a fast, standard relational table, while heavy business logic happens in the background. If a projection gets corrupted or needs a new column to support a management report, we can simply drop it and reprocess all events from day zero, rebuilding the view without losing any precious historical information.

Managing Eventual Consistency in Practice

Separating event storage from read tables brings an important shift in how software handles time and user feedback. Because the projection is updated asynchronously, there is a gap of milliseconds or seconds between event recording and the effective update on the query screen. In practice, this means the application must handle eventual consistency, designing interfaces that subtly inform the user their data is being processed instead of locking the entire navigation waiting for a synchronous database response.

To mitigate this perception of slowness, engineering teams often employ frontend techniques such as optimistic UI updates, where the client immediately simulates action success while the backend processes the real event in the background. Should an event processing failure occur, the system emits a corrective signal that rolls back the interface in a controlled manner. This operational resilience requires architectural discipline, but rewards the team with a highly scalable system capable of absorbing intense traffic spikes without crashing the main relational database.

Final Considerations on Scalability and Maintenance

Adopting event-driven domain modeling and decoupled projections in relational databases is not a silver bullet, but a deliberate architectural decision for systems demanding rigorous traceability and high resilience. The initial complexity of setting up event storage and managing asynchronous projections pays off amply over the software lifecycle, easing refactoring, audits, and horizontal scalability. By mastering these concepts using relational tools your team already knows and masters, you eliminate the need for exotic infrastructures and maintain total control over your data architecture.