SQL Expert Lab — Event Sourcing and CQRS (Expert)

🖥️

SQL Terminal

📚

Reference

🗂️

Schema

⭐⭐⭐⭐⭐ EXPERT OBJECTIVES

○Design event_store table: immutable, append-only, versioned

○Migrate existing balances to seed events (MIGRATION)

○Create balance_projection materialised view

○Build append_event() with optimistic locking (version check)

○Implement snapshot table + rebuild_from_snapshot()

○Add idempotency_key UNIQUE to prevent duplicate events

○Write CQRS command handler as stored procedure

○Validate: replay all events produces correct balances

0 / 8 complete

EXPERT SQL TERMINAL — EVENT SOURCING AND CQRS (EXPERT)

PostgreSQL 16 — Expert Lab Environment loaded ⭐ Event Sourcing and CQRS (Expert) — Expert Tier ⚠ No guidance provided. Mastery is assessed on correctness AND trade-off analysis. ⚠ Use -- comments or REPORT to document every design decision. ──────────────────────────────────────────

pg=#

KEY COMMANDS

CREATE TABLE event_store (id BIGSERIAL PRIMARY KEY, aggregate_id UUID NOT NULL...
INSERT INTO event_store(aggregate_id,aggregate_type,event_type,event_data,vers...
CREATE MATERIALIZED VIEW balance_projection AS SELECT aggregate_id AS account_...
CREATE FUNCTION append_event(p_agg UUID, p_type TEXT, p_data JSONB, p_expected...
CREATE TABLE event_snapshots (aggregate_id UUID PRIMARY KEY, snapshot_data JSO...
SELECT * FROM event_store WHERE aggregate_id=? ORDER BY version;

Expert tier: syntax starting points only. Your reasoning counts as much as the SQL.

SCHEMA BROWSER

Type \d or SHOW TABLES to list tables, SHOW SEARCH_PATH for active schema.

EXPERT BRIEFING

EVENT SOURCING — MERIDIAN LEDGER — EXPERT ⭐⭐⭐⭐⭐

⭐⭐⭐⭐⭐ Expert tier. No walkthrough. No syntax examples beyond the reference window. You are assessed on correctness, justification of trade-offs, and depth of reasoning.

SCENARIO

Meridian Ledger's traditional mutable-state banking database cannot produce a reliable audit trail, cannot replay history, and struggles with concurrent balance updates. You must migrate to a full event sourcing model: an immutable append-only event store, materialised views as read projections, a snapshot strategy for performance, and an idempotent command handler to prevent duplicate events. This is a design-heavy lab requiring deep understanding of CQRS and eventual consistency.

OBJECTIVES (8)

Design event_store table: immutable, append-only, versioned
Migrate existing balances to seed events (MIGRATION)
Create balance_projection materialised view
Build append_event() with optimistic locking (version check)
Implement snapshot table + rebuild_from_snapshot()
Add idempotency_key UNIQUE to prevent duplicate events
Write CQRS command handler as stored procedure
Validate: replay all events produces correct balances

EXPERT RULES

Every DDL decision must be justified with a -- comment or REPORT statement
Trade-off analysis is required for all design choices
All 8 objectives must be completed before the report is accepted
Reference window provides syntax starting points only

Display Mode