Modern systems often deal with two conflicting needs:

  1. A relational database (like SQL Server) for structured, transactional business data.

  2. 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:

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:

But this duality requires a well-defined consistency model.

A poorly implemented hybrid system leads to:

A well-implemented system provides:

Core Design Principles

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

  1. Events are immutable.

  2. SQL stores current state; event store stores historical evolution.

  3. Every SQL change must correspond to exactly one event.

  4. Replay must produce the exact state deterministically.

  5. 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:

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

Cons

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:

Cons:

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 IDNameVersionUpdatedAt
1023ItemA142025-01-01

Event store record format:

EventIdEntityIdVersionActionPayloadCreatedAt
35123102315Rename{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:

Angular Frontend Considerations

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

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:

  1. Single-event write and projection

  2. Multi-event replay reproducibility

  3. Out-of-order event protection

  4. High-concurrency event creation

  5. Cross-transaction idempotency checks

Production Best Practices

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:

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