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:
A well-implemented system provides:
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
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:
Perfect replay
Deterministic state
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 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:
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:
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:
This architecture is now common in finance, logistics, multi-tenant SaaS systems, and regulated enterprise software.