Designing a Consistency Model for Hybrid SQL + Event Store

Modern systems often deal with two conflicting needs:

A relational database (like SQL Server) for structured, transactional business data.
An event store (like Kafka, EventStoreDB, or a custom append-only table) for auditability, replayability, and history.

Together, they build a system where the current state is stored in SQL, and the state evolution is stored as events.

However, combining these two creates challenges:

How do we keep SQL and the event stream consistent?
What happens when events are replayed long after the original transaction?
How do we avoid duplication or divergence?
Is SQL authoritative or the event store?

This article explains how to design a strong, predictable consistency model between SQL and an event store, used in financial software, ERPs, logistics systems, manufacturing software, and workflow engines.

Introduction

A hybrid SQL + Event Store architecture gives the best of both worlds:

SQL offers relational integrity, joins, validation rules, and indexing.
Event store offers history, audit trails, replaying, and reconstructing domain state.

But this duality requires a well-defined consistency model.

A poorly implemented hybrid system leads to:

Ghost records in SQL not backed by events
Event replay breaking live data
Lost updates
Race conditions
Conflicting versions

A well-implemented system provides:

Full traceability of changes
Predictable replay behavior
Safe parallel writes
High audit confidence
Resilience

Core Design Principles

A Hybrid SQL + Event Store system must follow five rules:

Events are immutable.
SQL stores current state; event store stores historical evolution.
Every SQL change must correspond to exactly one event.
Replay must produce the exact state deterministically.
The system must detect and prevent conflicting event versions.

High-Level Architecture

Below is a conceptual model for read/write separation and event synchronization.

        ┌──────────────────────────┐
        │     Frontend (Angular)   │
        └───────────────┬──────────┘
                        │ Commands / Queries
                        │
        ┌───────────────▼───────────────┐
        │     API / Command Gateway      │
        └───────┬─────────────────┬──────┘
                │ Write            │ Read
                │ Pipeline         │ Pipeline
                │                  │
      ┌─────────▼───────┐     ┌───▼────────────────┐
      │ Validation/Rules │     │ Query/Projection   │
      └─────────┬───────┘     └───────┬────────────┘
                │                     │
        ┌───────▼──────────┐     ┌────▼───────────────┐
        │ Event Store       │     │ SQL (Current State)│
        └───────┬──────────┘     └─────────┬─────────┘
                │                          │
                └───────── Replay ─────────┘

This architecture separates:

Write path: Event-driven and authoritative.
Read path: SQL-backed for efficient reporting and queries.
Replay: Used for audit, recovery, and reconciling inconsistencies.

Consistency Models

There are three recommended approaches depending on system guarantees.

1. SQL-As-Source (Event Store is auditing only)

SQL writes happen first, and events are generated as side effects.

Pros

Easy to adopt
Existing applications can be migrated gradually

Cons

Replay cannot rebuild SQL state
Two sources of truth

Suitable for: ERP modules, reporting logic, historical logs.

2. Event-As-Source (SQL is a projection)

Events drive all writes. SQL is updated by processing the event stream.

Pros:

Perfect replay
Deterministic state

Cons:

Requires full event-driven design
Hard for legacy systems

Suitable for: Ledger, logistics tracking, distributed systems.

3. Hybrid With Priority Rules

Some domains use SQL as final state but use events for audit and replay to detect drift.

Best practice rule:
If SQL and event store diverge, replay events and correct SQL unless manual override is required.

This article focuses on this Hybrid Priority Model because it balances practicality and auditability.

Versioning and Concurrency Control

To prevent inconsistencies, both SQL and event store must track version numbers.

Example:

Entity ID	Name	Version	UpdatedAt
1023	ItemA	14	2025-01-01

Event store record format:

EventId	EntityId	Version	Action	Payload	CreatedAt
35123	1023	15	Rename	{Name:"ItemX"}	2025-01-02

SQL accepts the event only if:

event.Version == SQL.Version + 1

If not, a replay or conflict handler runs.

Replay Workflow

Replay rebuilds SQL from event history:

 ┌───────────────────────────────┐
 │ Load events by version order │
 └───────────────┬──────────────┘
                 │
         ┌───────▼──────────┐
         │ Apply validator   │
         └───────┬──────────┘
                 │
       ┌─────────▼───────────┐
       │ Update SQL state     │
       └─────────┬───────────┘
                 │
        ┌────────▼────────────┐
        │ Commit and advance   │
        │ SQL version pointer  │
        └──────────────────────┘

Replay is safe only when:

The system is not accepting live writes
Or writes are queued temporarily

Angular Frontend Considerations

Angular should not interact directly with SQL or the event store. It interacts only using:

Commands (write operations)
Queries (read operations)

Example Angular service

createCommand<T>(type: string, payload: T) {
  return this.http.post(`/api/commands/${type}`, payload);
}

query<T>(entity: string, filter: any) {
  return this.http.post<T>(`/api/query/${entity}`, filter);
}

This ensures frontend is decoupled from storage implementation.

.NET Backend Implementation Pattern

Recommended structure:

/Domain
/Infrastructure
/Events
/SqlProjections
/ReplayEngine

A sample event class:

public abstract record DomainEvent(Guid EntityId, int Version, DateTime OccurredAt);

Concrete event:

public record StockAdjusted(Guid EntityId, int Version, int DeltaQty, string Reason)
    : DomainEvent(EntityId, Version, DateTime.UtcNow);

Event handler pattern:

public interface IEventHandler<T> where T : DomainEvent
{
    Task ApplyAsync(T domainEvent);
}

Replay engine:

public class ReplayService 
{
    public async Task ReplayAsync(Guid entityId)
    {
        var events = await eventStore.GetEvents(entityId);
        foreach (var evt in events.OrderBy(e => e.Version))
            await dispatcher.Dispatch(evt);
    }
}

Handling Failures and Inconsistency Scenarios

Scenario: Event exists but SQL is missing or outdated.

Action: Trigger replay automatically.

Scenario: SQL version > event version.

Action: Log anomaly and allow manual override.

Scenario: Conflicting versions during write.

Action: Reject write and return HTTP 409 Conflict.

Testing Strategy

Recommended tests include:

Single-event write and projection
Multi-event replay reproducibility
Out-of-order event protection
High-concurrency event creation
Cross-transaction idempotency checks

Production Best Practices

Use append-only event storage
Enable event stream archiving
Keep SQL schemas small and focused
Introduce event compaction for entities with high history volume
Audit every replay request
Prefer optimistic concurrency with retry strategies

Summary

A Hybrid SQL + Event Store system requires a clear consistency model. When implemented with strict versioning, deterministic replay, and separation of write and read pipelines, it delivers:

Traceability
Recoverability
Predictable data evolution
High audit integrity

This architecture is now common in finance, logistics, multi-tenant SaaS systems, and regulated enterprise software.