Designing a Consistency Model for Hybrid SQL + Event Store

Introduction

Combining a relational SQL database with an event store is a powerful architecture: SQL gives you rich querying, constraints, and transactions; the event store gives you auditability, replayability, and an immutable history. But this hybrid approach raises an important question: what consistency guarantees does your system provide, and how do you achieve them in a reliable, maintainable way?

This article describes a production-ready consistency model for hybrid systems. It covers patterns (Transactional Outbox, Event Sourcing, CDC), concrete implementation recipes with .NET and SQL Server, projection strategies, UI patterns for Angular to handle eventual consistency, testing approaches, monitoring, and an operational playbook for production incidents.

Goals And Trade-Offs

Before designing a model, be explicit about what you want:

Atomicity of intent: When an application updates state, an event reflecting that change must not be lost.
Deterministic replay: Replaying events should reproduce state exactly.
Ordering: Events for the same aggregate must be applied in order.
Idempotency: Consumers must tolerate duplicate events.
Latency vs correctness: Immediate read-after-write correctness is expensive; eventual consistency is cheaper and more scalable.

Trade-offs

Strong consistency (synchronous cross-service transactions) reduces scalability and availability.
Eventual consistency is scalable but requires careful UX and compensating logic. Choose the model per bounded context.

High-Level Architecture (Block Diagram)

 ┌───────────────┐    ┌────────────────────┐    ┌───────────────┐
 │   Client App  │ -> │ API / Command Gate  │ -> │  SQL Master   │
 │ (Angular)     │    │ (.NET)              │    │ (Write Side)  │
 └───────────────┘    └──────┬───────────────┘    └──────┬────────┘
                             │                             │
                             │ Write changes + Outbox      │
                             ▼                             ▼
                       ┌──────────────┐            ┌────────────────┐
                       │ Outbox Table │  ----poll-> │ Event Broker   │
                       └──────────────┘            └────────────────┘
                             │                             │
                             ▼                             ▼
                      Publisher Worker                Projection Workers
                             │                             │
                             ▼                             ▼
                      External Consumers               Read Models (SQL)

Key idea: make event emission atomic with the SQL write by using a transactional outbox. Publish asynchronously to the event broker and power read-model updates via projections.

Pattern 1 — Transactional Outbox (Recommended Default)

Why Outbox?

If an operation must both update SQL and emit an event, doing them separately risks loss (update succeeds but event fails) or duplication (event succeeded but update failed). The Transactional Outbox guarantees the intent is recorded within the same DB transaction as the state change.

Implementation Steps

In the same DB transaction, update domain tables and insert an outbox row (JSON payload + metadata).
Commit transaction.
A background publisher polls outbox rows and publishes them to the event broker.
Publisher marks outbox rows as processed.

Outbox Table Example (SQL Server)

CREATE TABLE Outbox (
  OutboxId BIGINT IDENTITY PRIMARY KEY,
  AggregateId UNIQUEIDENTIFIER NOT NULL,
  EventType NVARCHAR(200) NOT NULL,
  Payload NVARCHAR(MAX) NOT NULL,
  CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME(),
  Processed BIT DEFAULT 0,
  ProcessedAt DATETIME2 NULL,
  Attempt INT DEFAULT 0
);

CREATE INDEX IX_Outbox_Processing ON Outbox(Processed, CreatedAt);

.NET Save Pattern (EF Core)

public async Task SaveWithEventAsync(DbContext ctx, DomainEvent evt)
{
    using var tx = await ctx.Database.BeginTransactionAsync();
    // 1. update domain state
    await ctx.SaveChangesAsync();
    // 2. insert into Outbox
    ctx.Set<Outbox>().Add(new Outbox { AggregateId = evt.AggregateId, EventType = evt.Type, Payload = JsonSerializer.Serialize(evt) });
    await ctx.SaveChangesAsync();
    await tx.CommitAsync();
}

Publisher Worker

Claim a batch (UPDATE TOP ... SET Processing = 1 OUTPUT inserted.* WHERE Processed = 0 AND Processing = 0) to avoid concurrent publishers.
Publish to broker with dedup key (OutboxId).
On success set Processed = 1, ProcessedAt = SYSUTCDATETIME().
On failure increment Attempt and backoff.

Guarantees

Atomic write of state + outbox → no lost intent.
Delivery to broker is at-least-once unless broker provides stronger semantics; consumers must be idempotent.

Pattern 2 — Event Sourcing (Event Store as Source of Truth)

Event Sourcing stores every state change as an event. SQL projections are generated from the event stream.

Pros

Full audit trail and deterministic state via replay.
Easy to rebuild projections.

Cons

Higher complexity on domain modeling and migrations.
Requires strong discipline for versioning.

If you adopt event sourcing, the event store is primary; projections are derived artifacts.

Pattern 3 — CDC (Change Data Capture)

CDC tools (Debezium, SQL Server CDC) stream DB changes to message brokers. CDC reduces code changes but can be noisy: database-level changes lack domain intent and may produce multiple events per logical operation. Use CDC when migrating legacy systems, but prefer outbox for domain semantics.

Idempotency And Deduplication

Always assume at-least-once delivery. Design consumers and projections with idempotency:

Store processed event id as unique key per projection.
Use MERGE/UPSERT operations with a version number.
Use an event version or sequence to apply ordering checks.

Example projection upsert (SQL Server)

MERGE INTO OrderProjection AS target
USING (VALUES (@OrderId, @Total, @Version)) AS src(OrderId, Total, Version)
ON target.OrderId = src.OrderId
WHEN MATCHED AND target.Version < src.Version THEN
  UPDATE SET Total = src.Total, Version = src.Version
WHEN NOT MATCHED THEN
  INSERT (OrderId, Total, Version) VALUES (src.OrderId, src.Total, src.Version);

This ensures out-of-order or duplicate events do not corrupt state.

Ordering Guarantees

Ordering is critical for aggregate-level correctness. Strategies:

Partition events by aggregate id when publishing (Kafka partition key) → guarantees order within a partition.
Within publisher, preserve original sequence numbers from the outbox to maintain order.
For cross-aggregate operations, use causal modeling (sagas) rather than global ordering.

Read-After-Write Consistency Options

Users often expect to see immediate results after a write. In hybrid systems there are three patterns to satisfy this:

Read-from-Write DB: After write, return fresh state from the write-side SQL (the same transaction result). Use this for immediate responsiveness.
Tailored Read Model Update: For short-lived needs, update a lightweight cache or projection synchronously.
Optimistic UI + Polling: Frontend applies optimistic changes and polls projection until it reflects the final state.

Recommended approach: return authoritative write-side state for immediate confirmation, while projections remain eventually consistent for heavy reads.

Angular UX example (optimistic + polling):

After save, show updated UI with a “Pending” badge.
Poll the projection endpoint until the projection version equals the written version or timeout.

Sagas And Long-Running Consistency

For multi-step distributed transactions (book-and-pay), implement sagas or state machines:

Saga listens to events and emits commands for next steps.
Saga state persisted in SQL ensures resilient progression and retry.
Compensating actions are defined to rollback or compensate if a step fails.

Sagas accept eventual consistency as a core property.

Versioning And Schema Evolution

Events and projections evolve. Use robust versioning:

Embed eventVersion in events and preserve original payloads.
Maintain event adapters to transform old events into the new format during replay.
Use backward-compatible changes where possible; for breaking changes add adapters or migration steps.

Event adapter example in .NET

public DomainEvent Adapt(EventEnvelope envelope)
{
    switch(envelope.EventType)
    {
        case "OrderCreated_v1": return adapterV1.ToV2(envelope.Payload);
        case "OrderCreated_v2": return JsonSerializer.Deserialize<OrderCreatedV2>(envelope.Payload);
    }
}

Projection Strategies

Per-aggregate projections: compact, ordered, good for OLTP reads.
Denormalized read models: precomputed joins for UI performance.
Materialized views: database-side, useful for analytical queries.

Ensure projections include metadata: LastEventId, LastEventVersion to know their position relative to the event stream.

Monitoring And Observability

Key metrics:

Outbox depth (rows unprocessed) and age of oldest row.
Publisher throughput and failure rate.
Projection lag (event offset vs applied offset).
Duplicate event count and idempotency skips.
Saga stuck instances and processing errors.

Instrument with Prometheus/Grafana, export traces with OpenTelemetry and include traceId in logs for correlation.

Testing Strategy

Unit Tests: event-to-state mapping and upsert logic.
Integration Tests: DB + outbox + publisher + projection worker in containers (Docker Compose).
Chaos Tests: simulate publisher crash, network partition, and ensure resume without data loss.
Replay Tests: perform replay into staging and verify final SQL state matches expectations.
Performance Tests: measure publisher throughput and projection scaling.

Operational Playbook

Migration: to enable outbox in legacy systems, implement writes to outbox in the same transaction, deploy publisher, and validate no outbox backlog.
Monitoring Alerts: set alerts for outbox age > threshold.
Rebuild Projections: provide safe replay tooling that writes into test projection schema before swapping.
Idempotency Failures: log and surface duplicates for investigation; keep metrics for trends.
Schema Changes: perform event adapter deployment and test replay before enabling for production.
Disaster Recovery: snapshot event store and projections; replay to rebuild state if needed.

Example End-to-End Flow (Concrete)

Scenario: Place Order operation

Client sends POST /orders to API.
API validates and in a DB transaction: inserts Orders row, inserts outbox event OrderPlaced with Version = 1. Commit.
API returns order confirmation with OrderId and Version = 1.
Publisher picks up outbox row, publishes OrderPlaced to Kafka partition keyed by OrderId, marks outbox processed.
Projection worker consuming OrderPlaced upserts OrderProjection with Version = 1.
If projection lags, client UI shows data from write-side DB; when projection reflects the change, the UI removes “Pending” status.

If a bug is fixed in projection logic later, operator runs replay for orders stream to rebuild projections deterministically.

Decision Guide: Which Model To Use

Use Transactional Outbox when you have existing relational writes and need reliable event emission without adopting full event sourcing.
Use Event Sourcing when domain logic benefits from immutable events and replayability is a primary feature.
Use CDC as a pragmatic migration aid or when you cannot change application code, but be aware of semantic gaps.

Common Pitfalls And How To Avoid Them

Forgetting Outbox Inserts on Some Paths: centralize persistence logic or use repository patterns so outbox writes are not missed.
Non-Idempotent Handlers: always persist ProcessedEventId and use MERGE for projections.
Assuming Global Ordering: enforce aggregate-level partitioning and avoid cross-aggregate global ordering assumptions.
Blind Replay to Production: always replay to staging projections first; provide dry-run and diff tools.
Huge Outbox Backlog: monitor and throttle publishers, or increase publisher capacity.

Conclusion

Designing a consistency model for a hybrid SQL + Event Store system is about making explicit promises—what is synchronous, what is eventual, and where compensating patterns are required. The Transactional Outbox combined with idempotent projections, partitioned ordering, careful versioning, and a robust operational toolkit gives you a pragmatic, reliable foundation that balances correctness with scale.