Ordering Database (v1.0.0)
PostgreSQL database implementing event sourcing with Marten for order management, maintaining complete audit trails and supporting CQRS patterns.
Overview
The Ordering Database is the central persistence layer for the Ordering Service, implementing a hybrid architecture that combines traditional relational data storage with event sourcing through Marten. Built on PostgreSQL 18, this database maintains both the current state of orders and buyers alongside a complete immutable event history, enabling powerful audit capabilities, temporal queries, and event replay functionality. The database serves as the authoritative source for all order-related operations in the BookWorm e-commerce platform.
BEGIN;
CREATE TABLE IF NOT EXISTS public."__EFMigrationsHistory"( migration_id character varying(150) COLLATE pg_catalog."default" NOT NULL, product_version character varying(32) COLLATE pg_catalog."default" NOT NULL, CONSTRAINT pk___ef_migrations_history PRIMARY KEY (migration_id));
CREATE TABLE IF NOT EXISTS public.buyers( id uuid NOT NULL DEFAULT uuidv7(), name character varying(20) COLLATE pg_catalog."default" NOT NULL, address_street character varying(50) COLLATE pg_catalog."default", address_city character varying(50) COLLATE pg_catalog."default", address_province character varying(50) COLLATE pg_catalog."default", CONSTRAINT pk_buyers PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS public.inbox_state( id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ), message_id uuid NOT NULL, consumer_id uuid NOT NULL, lock_id uuid NOT NULL, row_version bytea, received timestamp with time zone NOT NULL, receive_count integer NOT NULL, expiration_time timestamp with time zone, consumed timestamp with time zone, delivered timestamp with time zone, last_sequence_number bigint, CONSTRAINT pk_inbox_state PRIMARY KEY (id), CONSTRAINT ak_inbox_state_message_id_consumer_id UNIQUE (message_id, consumer_id));
CREATE TABLE IF NOT EXISTS public.mt_doc_deadletterevent( id uuid NOT NULL, data jsonb NOT NULL, mt_last_modified timestamp with time zone DEFAULT transaction_timestamp(), mt_version uuid NOT NULL DEFAULT (md5(((random())::text || (clock_timestamp())::text)))::uuid, mt_dotnet_type character varying COLLATE pg_catalog."default", CONSTRAINT pkey_mt_doc_deadletterevent_id PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS public.mt_doc_ordersummaryview( id uuid NOT NULL, data jsonb NOT NULL, mt_last_modified timestamp with time zone DEFAULT transaction_timestamp(), mt_dotnet_type character varying COLLATE pg_catalog."default", mt_version integer NOT NULL DEFAULT 0, CONSTRAINT pkey_mt_doc_ordersummaryview_id PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS public.mt_event_progression( name character varying COLLATE pg_catalog."default" NOT NULL, last_seq_id bigint, last_updated timestamp with time zone DEFAULT transaction_timestamp(), CONSTRAINT pk_mt_event_progression PRIMARY KEY (name));
CREATE TABLE IF NOT EXISTS public.mt_events( seq_id bigint NOT NULL, id uuid NOT NULL, stream_id uuid, version bigint NOT NULL, data jsonb NOT NULL, type character varying(500) COLLATE pg_catalog."default" NOT NULL, "timestamp" timestamp with time zone NOT NULL DEFAULT '2025-09-28 17:03:19.743016+00'::timestamp with time zone, tenant_id character varying COLLATE pg_catalog."default" DEFAULT '*DEFAULT*'::character varying, mt_dotnet_type character varying COLLATE pg_catalog."default", correlation_id character varying COLLATE pg_catalog."default", causation_id character varying COLLATE pg_catalog."default", headers jsonb, is_archived boolean NOT NULL DEFAULT false, CONSTRAINT pkey_mt_events_seq_id_is_archived PRIMARY KEY (seq_id, is_archived));
CREATE TABLE IF NOT EXISTS public.mt_streams( id uuid NOT NULL, type character varying COLLATE pg_catalog."default", version bigint, "timestamp" timestamp with time zone NOT NULL DEFAULT now(), snapshot jsonb, snapshot_version integer, created timestamp with time zone NOT NULL DEFAULT now(), tenant_id character varying COLLATE pg_catalog."default" DEFAULT '*DEFAULT*'::character varying, is_archived boolean NOT NULL DEFAULT false, CONSTRAINT pkey_mt_streams_id_is_archived PRIMARY KEY (id, is_archived));
CREATE TABLE IF NOT EXISTS public.order_items( id uuid NOT NULL DEFAULT uuidv7(), quantity integer NOT NULL, price numeric NOT NULL, book_id uuid NOT NULL, order_id uuid NOT NULL, CONSTRAINT pk_order_items PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS public.orders( id uuid NOT NULL DEFAULT uuidv7(), status smallint NOT NULL, note character varying(500) COLLATE pg_catalog."default", buyer_id uuid NOT NULL, is_deleted boolean NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT (now() AT TIME ZONE 'UTC'::text), last_modified_at timestamp with time zone DEFAULT (now() AT TIME ZONE 'UTC'::text), CONSTRAINT pk_orders PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS public.outbox_message( sequence_number bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ), enqueue_time timestamp with time zone, sent_time timestamp with time zone NOT NULL, headers text COLLATE pg_catalog."default", properties text COLLATE pg_catalog."default", inbox_message_id uuid, inbox_consumer_id uuid, outbox_id uuid, message_id uuid NOT NULL, content_type character varying(256) COLLATE pg_catalog."default" NOT NULL, message_type text COLLATE pg_catalog."default" NOT NULL, body text COLLATE pg_catalog."default" NOT NULL, conversation_id uuid, correlation_id uuid, initiator_id uuid, request_id uuid, source_address character varying(256) COLLATE pg_catalog."default", destination_address character varying(256) COLLATE pg_catalog."default", response_address character varying(256) COLLATE pg_catalog."default", fault_address character varying(256) COLLATE pg_catalog."default", expiration_time timestamp with time zone, CONSTRAINT pk_outbox_message PRIMARY KEY (sequence_number));
CREATE TABLE IF NOT EXISTS public.outbox_state( outbox_id uuid NOT NULL, lock_id uuid NOT NULL, row_version bytea, created timestamp with time zone NOT NULL, delivered timestamp with time zone, last_sequence_number bigint, CONSTRAINT pk_outbox_state PRIMARY KEY (outbox_id));
ALTER TABLE IF EXISTS public.mt_events ADD CONSTRAINT fkey_mt_events_stream_id_is_archived FOREIGN KEY (stream_id, is_archived) REFERENCES public.mt_streams (id, is_archived) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE;
ALTER TABLE IF EXISTS public.order_items ADD CONSTRAINT fk_order_items_orders_order_id FOREIGN KEY (order_id) REFERENCES public.orders (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE;CREATE INDEX IF NOT EXISTS ix_order_items_order_id ON public.order_items(order_id);
ALTER TABLE IF EXISTS public.orders ADD CONSTRAINT fk_orders_buyers_buyer_id FOREIGN KEY (buyer_id) REFERENCES public.buyers (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL;CREATE INDEX IF NOT EXISTS ix_orders_buyer_id ON public.orders(buyer_id);
ALTER TABLE IF EXISTS public.outbox_message ADD CONSTRAINT fk_outbox_message_inbox_state_inbox_message_id_inbox_consumer_ FOREIGN KEY (inbox_message_id, inbox_consumer_id) REFERENCES public.inbox_state (message_id, consumer_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE IF EXISTS public.outbox_message ADD CONSTRAINT fk_outbox_message_outbox_state_outbox_id FOREIGN KEY (outbox_id) REFERENCES public.outbox_state (outbox_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
END;-- Business Insight Queries for Ordering Database-- These queries provide valuable business metrics and insights for decision-making
-- ============================================================================-- ORDER PERFORMANCE METRICS-- ============================================================================
-- 1. Daily Order Summary with Conversion Rates-- Provides overview of order volume, revenue, and cancellation rates by daySELECT DATE(created_at) as order_date, COUNT(*) as total_orders, COUNT(*) FILTER (WHERE status = 0) as new_orders, COUNT(*) FILTER (WHERE status = 2) as completed_orders, COUNT(*) FILTER (WHERE status = 1) as cancelled_orders, ROUND(COUNT(*) FILTER (WHERE status = 2)::NUMERIC / NULLIF(COUNT(*), 0) * 100, 2) as completion_rate_pct, ROUND(COUNT(*) FILTER (WHERE status = 1)::NUMERIC / NULLIF(COUNT(*), 0) * 100, 2) as cancellation_rate_pct, SUM(oi.price * oi.quantity) as total_revenue, AVG(oi.price * oi.quantity) as avg_order_valueFROM orders oLEFT JOIN order_items oi ON o.id = oi.order_idWHERE o.is_deleted = falseGROUP BY DATE(created_at)ORDER BY order_date DESCLIMIT 90;
-- 2. Order Status Distribution-- Shows current state of all orders in the systemSELECT CASE status WHEN 0 THEN 'New' WHEN 1 THEN 'Cancelled' WHEN 2 THEN 'Completed' END as order_status, COUNT(*) as order_count, ROUND(COUNT(*)::NUMERIC / SUM(COUNT(*)) OVER () * 100, 2) as percentage, SUM(oi.price * oi.quantity) as total_revenueFROM orders oLEFT JOIN order_items oi ON o.id = oi.order_idWHERE o.is_deleted = falseGROUP BY statusORDER BY order_count DESC;
-- 3. Average Order Processing Time-- Measures time from order creation to completionSELECT DATE_TRUNC('week', o.created_at) as week, COUNT(*) as completed_orders, AVG(EXTRACT(EPOCH FROM (o.last_modified_at - o.created_at)) / 3600) as avg_hours_to_complete, MIN(EXTRACT(EPOCH FROM (o.last_modified_at - o.created_at)) / 3600) as min_hours_to_complete, MAX(EXTRACT(EPOCH FROM (o.last_modified_at - o.created_at)) / 3600) as max_hours_to_complete, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (o.last_modified_at - o.created_at)) / 3600) as median_hours_to_completeFROM orders oWHERE o.status = 2 AND o.is_deleted = false AND o.last_modified_at IS NOT NULLGROUP BY DATE_TRUNC('week', o.created_at)ORDER BY week DESCLIMIT 12;
-- ============================================================================-- CUSTOMER BEHAVIOR ANALYSIS-- ============================================================================
-- 4. Top Buyers by Order Volume and Revenue-- Identifies most valuable customersSELECT b.id as buyer_id, b.name as buyer_name, b.address_city as city, b.address_province as province, COUNT(o.id) as total_orders, COUNT(*) FILTER (WHERE o.status = 2) as completed_orders, COUNT(*) FILTER (WHERE o.status = 1) as cancelled_orders, SUM(oi.price * oi.quantity) as lifetime_revenue, AVG(oi.price * oi.quantity) as avg_order_value, MAX(o.created_at) as last_order_date, EXTRACT(DAY FROM NOW() - MAX(o.created_at)) as days_since_last_orderFROM buyers bINNER JOIN orders o ON b.id = o.buyer_idLEFT JOIN order_items oi ON o.id = oi.order_idWHERE o.is_deleted = falseGROUP BY b.id, b.name, b.address_city, b.address_provinceHAVING COUNT(o.id) > 0ORDER BY lifetime_revenue DESCLIMIT 100;
-- 5. Customer Segmentation by Order Frequency-- Categorizes customers into segments for targeted marketingSELECT CASE WHEN order_count >= 10 THEN 'VIP (10+ orders)' WHEN order_count >= 5 THEN 'Frequent (5-9 orders)' WHEN order_count >= 2 THEN 'Regular (2-4 orders)' ELSE 'One-time (1 order)' END as customer_segment, COUNT(*) as customer_count, AVG(order_count) as avg_orders_per_customer, SUM(total_revenue) as segment_revenue, AVG(total_revenue) as avg_customer_lifetime_valueFROM ( SELECT b.id, COUNT(o.id) as order_count, SUM(oi.price * oi.quantity) as total_revenue FROM buyers b INNER JOIN orders o ON b.id = o.buyer_id LEFT JOIN order_items oi ON o.id = oi.order_id WHERE o.is_deleted = false AND o.status = 2 GROUP BY b.id) customer_metricsGROUP BY customer_segmentORDER BY CASE customer_segment WHEN 'VIP (10+ orders)' THEN 1 WHEN 'Frequent (5-9 orders)' THEN 2 WHEN 'Regular (2-4 orders)' THEN 3 ELSE 4 END;
-- 6. Customer Churn Analysis-- Identifies customers who haven't ordered recentlySELECT b.id as buyer_id, b.name as buyer_name, COUNT(o.id) as total_orders, MAX(o.created_at) as last_order_date, EXTRACT(DAY FROM NOW() - MAX(o.created_at)) as days_since_last_order, CASE WHEN EXTRACT(DAY FROM NOW() - MAX(o.created_at)) > 180 THEN 'At Risk (6+ months inactive)' WHEN EXTRACT(DAY FROM NOW() - MAX(o.created_at)) > 90 THEN 'Dormant (3-6 months inactive)' WHEN EXTRACT(DAY FROM NOW() - MAX(o.created_at)) > 30 THEN 'Inactive (1-3 months)' ELSE 'Active' END as churn_risk, SUM(oi.price * oi.quantity) as lifetime_revenueFROM buyers bINNER JOIN orders o ON b.id = o.buyer_idLEFT JOIN order_items oi ON o.id = oi.order_idWHERE o.is_deleted = falseGROUP BY b.id, b.nameHAVING MAX(o.created_at) < NOW() - INTERVAL '30 days'ORDER BY days_since_last_order DESCLIMIT 100;
-- ============================================================================-- PRODUCT PERFORMANCE ANALYSIS-- ============================================================================
-- 7. Best Selling Books-- Identifies top-performing productsSELECT oi.book_id, COUNT(DISTINCT o.id) as order_count, SUM(oi.quantity) as total_units_sold, SUM(oi.price * oi.quantity) as total_revenue, AVG(oi.price) as avg_price, COUNT(DISTINCT o.buyer_id) as unique_buyersFROM order_items oiINNER JOIN orders o ON oi.order_id = o.idWHERE o.status = 2 AND o.is_deleted = falseGROUP BY oi.book_idORDER BY total_revenue DESCLIMIT 50;
-- 8. Product Performance by Time Period-- Trends for specific products over timeSELECT DATE_TRUNC('month', o.created_at) as month, oi.book_id, COUNT(DISTINCT o.id) as order_count, SUM(oi.quantity) as units_sold, SUM(oi.price * oi.quantity) as revenue, AVG(oi.price) as avg_priceFROM order_items oiINNER JOIN orders o ON oi.order_id = o.idWHERE o.status = 2 AND o.is_deleted = false AND o.created_at >= NOW() - INTERVAL '12 months'GROUP BY DATE_TRUNC('month', o.created_at), oi.book_idORDER BY month DESC, revenue DESC;
-- 9. Order Item Distribution-- Analyzes order basket compositionSELECT item_count as items_per_order, COUNT(*) as order_count, ROUND(COUNT(*)::NUMERIC / SUM(COUNT(*)) OVER () * 100, 2) as percentage, AVG(total_value) as avg_order_valueFROM ( SELECT o.id, COUNT(oi.id) as item_count, SUM(oi.price * oi.quantity) as total_value FROM orders o LEFT JOIN order_items oi ON o.id = oi.order_id WHERE o.status = 2 AND o.is_deleted = false GROUP BY o.id) order_metricsGROUP BY item_countORDER BY item_count;
-- ============================================================================-- GEOGRAPHIC ANALYSIS-- ============================================================================
-- 10. Revenue by Geographic Location-- Shows performance across different regionsSELECT b.address_province as province, b.address_city as city, COUNT(DISTINCT b.id) as total_customers, COUNT(o.id) as total_orders, COUNT(*) FILTER (WHERE o.status = 2) as completed_orders, SUM(oi.price * oi.quantity) as total_revenue, AVG(oi.price * oi.quantity) as avg_order_value, ROUND(COUNT(*) FILTER (WHERE o.status = 2)::NUMERIC / NULLIF(COUNT(o.id), 0) * 100, 2) as completion_rate_pctFROM buyers bLEFT JOIN orders o ON b.id = o.buyer_idLEFT JOIN order_items oi ON o.id = oi.order_idWHERE o.is_deleted = false OR o.is_deleted IS NULLGROUP BY b.address_province, b.address_cityHAVING COUNT(o.id) > 0ORDER BY total_revenue DESCLIMIT 50;
-- 11. Top Cities by Order Volume-- Identifies key markets for expansionSELECT b.address_city as city, b.address_province as province, COUNT(DISTINCT b.id) as customer_base, COUNT(o.id) as total_orders, SUM(oi.price * oi.quantity) as total_revenue, AVG(oi.price * oi.quantity) as avg_order_value, COUNT(DISTINCT oi.book_id) as unique_products_orderedFROM buyers bINNER JOIN orders o ON b.id = o.buyer_idLEFT JOIN order_items oi ON o.id = oi.order_idWHERE o.status = 2 AND o.is_deleted = false AND b.address_city IS NOT NULLGROUP BY b.address_city, b.address_provinceORDER BY total_revenue DESCLIMIT 20;
-- ============================================================================-- OPERATIONAL EFFICIENCY METRICS-- ============================================================================
-- 12. Order Cancellation Analysis-- Investigates patterns in cancelled ordersSELECT DATE(o.created_at) as cancellation_date, COUNT(*) as cancelled_orders, AVG(EXTRACT(EPOCH FROM (o.last_modified_at - o.created_at)) / 60) as avg_minutes_to_cancel, COUNT(DISTINCT o.buyer_id) as affected_customers, SUM(oi.price * oi.quantity) as lost_revenueFROM orders oLEFT JOIN order_items oi ON o.id = oi.order_idWHERE o.status = 1 AND o.is_deleted = false AND o.created_at >= NOW() - INTERVAL '90 days'GROUP BY DATE(o.created_at)ORDER BY cancellation_date DESC;
-- 13. Hourly Order Pattern-- Shows peak ordering hours for capacity planningSELECT EXTRACT(HOUR FROM created_at) as hour_of_day, EXTRACT(DOW FROM created_at) as day_of_week, COUNT(*) as order_count, AVG(total_value) as avg_order_value, COUNT(*) FILTER (WHERE status = 2) as completed_orders, COUNT(*) FILTER (WHERE status = 1) as cancelled_ordersFROM ( SELECT o.id, o.created_at, o.status, SUM(oi.price * oi.quantity) as total_value FROM orders o LEFT JOIN order_items oi ON o.id = oi.order_id WHERE o.is_deleted = false AND o.created_at >= NOW() - INTERVAL '30 days' GROUP BY o.id, o.created_at, o.status) order_dataGROUP BY EXTRACT(HOUR FROM created_at), EXTRACT(DOW FROM created_at)ORDER BY day_of_week, hour_of_day;
-- 14. Monthly Growth Trends-- Year-over-year and month-over-month comparisonSELECT DATE_TRUNC('month', o.created_at) as month, COUNT(*) as total_orders, COUNT(*) FILTER (WHERE o.status = 2) as completed_orders, SUM(oi.price * oi.quantity) as revenue, COUNT(DISTINCT o.buyer_id) as unique_customers, AVG(oi.price * oi.quantity) as avg_order_value, LAG(SUM(oi.price * oi.quantity)) OVER (ORDER BY DATE_TRUNC('month', o.created_at)) as prev_month_revenue, ROUND( (SUM(oi.price * oi.quantity) - LAG(SUM(oi.price * oi.quantity)) OVER (ORDER BY DATE_TRUNC('month', o.created_at))) / NULLIF(LAG(SUM(oi.price * oi.quantity)) OVER (ORDER BY DATE_TRUNC('month', o.created_at)), 0) * 100, 2 ) as revenue_growth_pctFROM orders oLEFT JOIN order_items oi ON o.id = oi.order_idWHERE o.is_deleted = falseGROUP BY DATE_TRUNC('month', o.created_at)ORDER BY month DESCLIMIT 24;
-- ============================================================================-- REVENUE INSIGHTS-- ============================================================================
-- 15. Revenue Breakdown by Order Status-- Understanding revenue at risk from pending/cancelled ordersSELECT CASE status WHEN 0 THEN 'New (Pending)' WHEN 1 THEN 'Cancelled (Lost)' WHEN 2 THEN 'Completed (Realized)' END as order_status, COUNT(DISTINCT o.id) as order_count, SUM(oi.price * oi.quantity) as total_value, AVG(oi.price * oi.quantity) as avg_order_value, ROUND(SUM(oi.price * oi.quantity) / SUM(SUM(oi.price * oi.quantity)) OVER () * 100, 2) as revenue_percentageFROM orders oLEFT JOIN order_items oi ON o.id = oi.order_idWHERE o.is_deleted = falseGROUP BY o.statusORDER BY o.status;
-- 16. Top Revenue Days-- Identifies highest-earning days for understanding seasonalitySELECT DATE(o.created_at) as order_date, EXTRACT(DOW FROM o.created_at) as day_of_week, COUNT(DISTINCT o.id) as order_count, SUM(oi.price * oi.quantity) as daily_revenue, AVG(oi.price * oi.quantity) as avg_order_value, COUNT(DISTINCT o.buyer_id) as unique_customersFROM orders oLEFT JOIN order_items oi ON o.id = oi.order_idWHERE o.status = 2 AND o.is_deleted = false AND o.created_at >= NOW() - INTERVAL '6 months'GROUP BY DATE(o.created_at), EXTRACT(DOW FROM o.created_at)ORDER BY daily_revenue DESCLIMIT 30;
-- ============================================================================-- CUSTOMER LIFETIME VALUE (CLV)-- ============================================================================
-- 17. Customer Lifetime Value Analysis-- Comprehensive customer value metricsWITH customer_metrics AS ( SELECT b.id as buyer_id, b.name, MIN(o.created_at) as first_order_date, MAX(o.created_at) as last_order_date, COUNT(o.id) as total_orders, COUNT(*) FILTER (WHERE o.status = 2) as completed_orders, SUM(oi.price * oi.quantity) as lifetime_revenue, AVG(oi.price * oi.quantity) as avg_order_value, EXTRACT(DAY FROM MAX(o.created_at) - MIN(o.created_at)) as customer_lifespan_days FROM buyers b INNER JOIN orders o ON b.id = o.buyer_id LEFT JOIN order_items oi ON o.id = oi.order_id WHERE o.is_deleted = false GROUP BY b.id, b.name)SELECT buyer_id, name, first_order_date, last_order_date, total_orders, completed_orders, lifetime_revenue, avg_order_value, customer_lifespan_days, CASE WHEN customer_lifespan_days > 0 THEN ROUND(lifetime_revenue / (customer_lifespan_days / 30.0), 2) ELSE lifetime_revenue END as avg_monthly_revenue, ROUND(lifetime_revenue / NULLIF(completed_orders, 0), 2) as revenue_per_orderFROM customer_metricsWHERE completed_orders > 0ORDER BY lifetime_revenue DESCLIMIT 100;
-- 18. Cohort Analysis by First Order Month-- Retention and revenue patterns by customer acquisition cohortSELECT DATE_TRUNC('month', first_order_date) as cohort_month, COUNT(DISTINCT buyer_id) as cohort_size, SUM(lifetime_revenue) as total_cohort_revenue, AVG(lifetime_revenue) as avg_customer_value, AVG(total_orders) as avg_orders_per_customer, COUNT(*) FILTER (WHERE total_orders > 1) as repeat_customers, ROUND(COUNT(*) FILTER (WHERE total_orders > 1)::NUMERIC / COUNT(*) * 100, 2) as repeat_rate_pctFROM ( SELECT b.id as buyer_id, MIN(o.created_at) as first_order_date, COUNT(o.id) as total_orders, SUM(oi.price * oi.quantity) as lifetime_revenue FROM buyers b INNER JOIN orders o ON b.id = o.buyer_id LEFT JOIN order_items oi ON o.id = oi.order_id WHERE o.is_deleted = false AND o.status = 2 GROUP BY b.id) cohort_dataGROUP BY DATE_TRUNC('month', first_order_date)ORDER BY cohort_month DESCLIMIT 12;
-- ============================================================================-- ORDER QUALITY METRICS-- ============================================================================
-- 19. Average Items Per Order Trend-- Tracks basket size over timeSELECT DATE_TRUNC('month', o.created_at) as month, AVG(item_count) as avg_items_per_order, AVG(total_value) as avg_order_value, AVG(total_value / item_count) as avg_value_per_item, COUNT(*) as order_countFROM ( SELECT o.id, o.created_at, COUNT(oi.id) as item_count, SUM(oi.price * oi.quantity) as total_value FROM orders o LEFT JOIN order_items oi ON o.id = oi.order_id WHERE o.status = 2 AND o.is_deleted = false GROUP BY o.id, o.created_at) order_detailsGROUP BY DATE_TRUNC('month', created_at)ORDER BY month DESCLIMIT 12;
-- 20. Order Value Distribution-- Understanding order size segmentsSELECT CASE WHEN total_value < 20 THEN 'Under $20' WHEN total_value < 50 THEN '$20-$50' WHEN total_value < 100 THEN '$50-$100' WHEN total_value < 200 THEN '$100-$200' ELSE 'Over $200' END as order_value_range, COUNT(*) as order_count, ROUND(COUNT(*)::NUMERIC / SUM(COUNT(*)) OVER () * 100, 2) as percentage, SUM(total_value) as total_revenue, AVG(total_value) as avg_order_valueFROM ( SELECT o.id, SUM(oi.price * oi.quantity) as total_value FROM orders o LEFT JOIN order_items oi ON o.id = oi.order_id WHERE o.status = 2 AND o.is_deleted = false GROUP BY o.id) order_valuesGROUP BY order_value_rangeORDER BY CASE order_value_range WHEN 'Under $20' THEN 1 WHEN '$20-$50' THEN 2 WHEN '$50-$100' THEN 3 WHEN '$100-$200' THEN 4 ELSE 5 END;Database Purpose & Role
Hybrid Architecture Pattern
The Ordering Database implements a sophisticated dual-storage approach:
- State Storage: Traditional relational tables (orders, order_items, buyers) for efficient querying of current state
- Event Store: Marten-managed event sourcing tables (mt_events, mt_streams) for complete audit history
- CQRS Support: Separation enables independent optimization of read and write operations
- Projections: Marten projections (mt_doc_ordersummaryview) provide optimized read models
- Temporal Queries: Event store enables point-in-time reconstruction of any order state
Order Lifecycle Management
The database tracks orders through their complete lifecycle:
- Creation: Orders start in “New” status when placed by customers
- Completion: Successful fulfillment transitions orders to “Completed” status
- Cancellation: Customer or system-initiated cancellations move orders to “Cancelled” status
- Soft Delete: Maintains historical data while marking orders as deleted
- Audit Trail: Every state change recorded as immutable domain event
Event Sourcing with Marten
Marten provides event sourcing capabilities on top of PostgreSQL:
- Event Append: All domain events appended to immutable event log
- Stream Management: Events grouped by aggregate root (order ID)
- Projections: Automated read model updates from event streams
- Subscriptions: Real-time event processing for integration events
- Snapshots: Performance optimization for long event streams
Schema Design
Domain Tables
orders - Order Aggregate State
The primary table storing current order state with the following columns:
- id (uuid, PRIMARY KEY, DEFAULT uuidv7()): Unique order identifier using UUID v7 for time-ordered IDs
- status (smallint, NOT NULL): Current order status (0=New, 1=Cancelled, 2=Completed)
- note (varchar(500)): Optional customer notes or special instructions
- buyer_id (uuid, NOT NULL, FOREIGN KEY): Reference to buyer who placed the order
- is_deleted (boolean, NOT NULL): Soft delete flag for data retention
- created_at (timestamptz, NOT NULL, DEFAULT NOW() AT TIME ZONE ‘UTC’): Order creation timestamp
- last_modified_at (timestamptz, DEFAULT NOW() AT TIME ZONE ‘UTC’): Last modification timestamp
Key Features:
- UUID v7 IDs: Time-ordered identifiers for efficient indexing and natural sorting
- Status Enum: Simple integer enumeration for order states
- Soft Delete: Preserves order history while marking as deleted
- Audit Timestamps: Automatic tracking of creation and modification times
- Buyer Reference: Foreign key relationship with CASCADE on delete
Status Lifecycle:
- New (0): Initial state when order is placed
- Cancelled (1): Order cancelled before fulfillment
- Completed (2): Order successfully fulfilled and delivered
order_items - Order Line Items
Stores individual items within each order with the following columns:
- id (uuid, PRIMARY KEY, DEFAULT uuidv7()): Unique line item identifier
- quantity (integer, NOT NULL): Number of units ordered (must be greater than 0)
- price (numeric, NOT NULL): Unit price at time of order (must be greater than or equal to 0)
- book_id (uuid, NOT NULL): Reference to product in catalog service
- order_id (uuid, NOT NULL, FOREIGN KEY): Parent order reference with CASCADE on delete
Key Features:
- Price Snapshot: Captures price at order time (not current catalog price)
- Quantity Validation: Business rules enforce positive quantities
- Cascade Delete: Line items automatically deleted when parent order deleted
- Product Reference: Links to catalog service via book_id (not enforced FK for microservice boundaries)
Business Rules:
- Each order must have at least one order item
- Quantities must be positive integers
- Prices must be non-negative decimals
- Line items are immutable once order is placed
buyers - Customer Information
Manages buyer profiles and shipping addresses with the following columns:
- id (uuid, PRIMARY KEY, DEFAULT uuidv7()): Unique buyer identifier
- name (varchar(20), NOT NULL): Buyer’s full name (limited to 20 characters)
- address_street (varchar(50)): Street address for shipping
- address_city (varchar(50)): City name
- address_province (varchar(50)): Province or state name
Key Features:
- Minimal Profile: Stores only essential information for order fulfillment
- Optional Address: Address fields nullable for flexible buyer creation
- Name Constraint: 20-character limit ensures concise naming
- Geographic Data: Enables location-based analytics and shipping logistics
Address Components:
- Street address for delivery location
- City for regional analytics
- Province for broader geographic insights
- No postal code (simplified addressing)
Event Sourcing Tables (Marten)
mt_events - Event Store
The core event store table managed by Marten with the following columns:
- seq_id (bigint, PRIMARY KEY): Global sequence number for all events (auto-incrementing)
- id (uuid, NOT NULL): Unique event identifier
- stream_id (uuid, FOREIGN KEY): Reference to aggregate stream
- version (bigint, NOT NULL): Event version within its stream (for optimistic concurrency)
- data (jsonb, NOT NULL): Event payload stored as JSON
- type (varchar(500), NOT NULL): Fully qualified event type name
- timestamp (timestamptz, NOT NULL, DEFAULT NOW()): When event was recorded
- tenant_id (varchar, DEFAULT ‘DEFAULT’): Multi-tenancy support
- mt_dotnet_type (varchar): .NET type information for deserialization
- correlation_id (varchar): Distributed tracing correlation
- causation_id (varchar): Causal relationship tracking
- headers (jsonb): Additional metadata
- is_archived (boolean, NOT NULL, DEFAULT false): Archive flag for old events
Key Features:
- Immutable Log: Events never modified after creation
- Sequential Ordering: Global sequence ensures total ordering
- Version Control: Per-stream versioning prevents concurrent update conflicts
- JSONB Storage: Efficient storage and querying of event data
- Metadata Rich: Correlation and causation for distributed systems
- Archival Support: Old events can be archived without deletion
Event Types:
- OrderPlacedEvent: When new order is created
- OrderCompletedEvent: When order fulfillment succeeds
- OrderCancelledEvent: When order is cancelled
mt_streams - Event Stream Metadata
Manages aggregate root streams with the following columns:
- id (uuid, PRIMARY KEY): Stream identifier (matches aggregate root ID)
- type (varchar): Aggregate type (e.g., “Order”)
- version (bigint): Current stream version (number of events)
- timestamp (timestamptz, NOT NULL, DEFAULT NOW()): Last event timestamp
- snapshot (jsonb): Snapshot data for performance optimization
- snapshot_version (integer): Version at which snapshot was taken
- created (timestamptz, NOT NULL, DEFAULT NOW()): Stream creation time
- tenant_id (varchar, DEFAULT ‘DEFAULT’): Multi-tenancy identifier
- is_archived (boolean, NOT NULL, DEFAULT false): Archive flag
Key Features:
- Stream Tracking: One stream per aggregate root instance
- Version Control: Optimistic concurrency for aggregate updates
- Snapshot Support: Performance optimization for long event histories
- Tenant Isolation: Multi-tenant support for SaaS scenarios
- Archival: Historical streams can be archived
Snapshot Strategy:
- Snapshots created every N events (configurable, typically 20-50)
- Reduces replay time for aggregates with long histories
- Snapshots optional, not required for correctness
mt_doc_ordersummaryview - Order Summary Projection
Marten-managed read model projection with the following columns:
- id (uuid, PRIMARY KEY): Order identifier
- data (jsonb, NOT NULL): Projected order summary data
- mt_last_modified (timestamptz, DEFAULT transaction_timestamp()): Last projection update
- mt_dotnet_type (varchar): .NET type for deserialization
- mt_version (integer, NOT NULL, DEFAULT 0): Projection version
Key Features:
- Automated Updates: Marten automatically updates from event stream
- Optimized Queries: Pre-computed data for fast reads
- JSONB Storage: Flexible schema for evolving read models
- Version Tracking: Detects out-of-sync projections
Projected Data:
- Order ID and status
- Total price calculation
- Creation and modification timestamps
- Derived business metrics
mt_event_progression - Projection State Tracking
Tracks last processed event for projections with the following columns:
- name (varchar, PRIMARY KEY): Projection name
- last_seq_id (bigint): Last processed event sequence ID
- last_updated (timestamptz, DEFAULT transaction_timestamp()): Last processing time
Key Features:
- Checkpoint Tracking: Enables resumable projection processing
- Multiple Projections: Each projection tracks independently
- Failure Recovery: Restart from last successful event
mt_doc_deadletterevent - Failed Event Processing
Stores events that failed projection processing with the following columns:
- id (uuid, PRIMARY KEY): Dead letter event identifier
- data (jsonb, NOT NULL): Failed event data
- mt_last_modified (timestamptz, DEFAULT transaction_timestamp()): When failure occurred
- mt_version (uuid, NOT NULL, DEFAULT random_uuid()): Version for concurrency
- mt_dotnet_type (varchar): Event type information
Key Features:
- Error Isolation: Failed events don’t block other processing
- Retry Support: Can be replayed after fixing projection logic
- Debugging Aid: Captures problematic events for analysis
MassTransit Integration Tables
inbox_state - Message Deduplication
Tracks consumed integration events for idempotency with the following columns:
- id (bigint, PRIMARY KEY IDENTITY): Internal identifier
- message_id (uuid, NOT NULL): Incoming message identifier
- consumer_id (uuid, NOT NULL): Consumer endpoint identifier
- lock_id (uuid, NOT NULL): Processing lock
- row_version (bytea): Optimistic concurrency token
- received (timestamptz, NOT NULL): When message arrived
- receive_count (int, NOT NULL): Delivery attempt count
- expiration_time (timestamptz): Message expiration
- consumed (timestamptz): When message was processed
- delivered (timestamptz): When processing completed
- last_sequence_number (bigint): Sequence tracking
- UNIQUE CONSTRAINT on (message_id, consumer_id): Deduplication key
Key Features:
- Exactly-Once Semantics: Prevents duplicate command processing
- Composite Key: Per-consumer deduplication
- Retry Tracking: Monitors delivery attempts
- Processing State: Complete lifecycle tracking
outbox_message - Transactional Outbox
Ensures reliable integration event publishing with the following columns:
- sequence_number (bigint, PRIMARY KEY IDENTITY): Auto-incrementing sequence
- message_id (uuid, NOT NULL): Unique message identifier
- message_type (text, NOT NULL): Full message type name
- body (text, NOT NULL): Serialized message content (JSON)
- content_type (varchar(256), NOT NULL): Serialization format
- enqueue_time (timestamptz): When message was stored
- sent_time (timestamptz, NOT NULL): When message was published
- conversation_id (uuid): Conversation tracking
- correlation_id (uuid): Event correlation
- initiator_id (uuid): Original message initiator
- request_id (uuid): Request-response tracking
- source_address (varchar(256)): Message source endpoint
- destination_address (varchar(256)): Target endpoint
- response_address (varchar(256)): Reply-to address
- fault_address (varchar(256)): Error handling address
- expiration_time (timestamptz): Message TTL
- headers (text): Additional message headers (JSON)
- properties (text): Custom message properties (JSON)
- inbox_message_id (uuid): Related inbox message
- inbox_consumer_id (uuid): Related inbox consumer
- outbox_id (uuid): Related outbox state
Key Features:
- Atomic Publishing: Messages stored in same transaction as domain events
- Sequential Processing: Ensures message ordering
- Complete Envelope: Full routing and correlation metadata
outbox_state - Outbox Processing State
Manages outbox processing cursor with the following columns:
- outbox_id (uuid, PRIMARY KEY): Unique outbox identifier
- lock_id (uuid, NOT NULL): Distributed lock identifier
- row_version (bytea): Optimistic concurrency token
- created (timestamptz, NOT NULL): Outbox creation time
- delivered (timestamptz): Last delivery time
- last_sequence_number (bigint): Last processed message sequence
Key Features:
- Processing Checkpoint: Tracks last published message
- Distributed Locking: Prevents concurrent processing
- Recovery Support: Resume from last sequence after failures
__EFMigrationsHistory - Schema Version Control
Standard Entity Framework Core migrations table with the following columns:
- migration_id (varchar(150), PRIMARY KEY): Migration identifier
- product_version (varchar(32), NOT NULL): EF Core version
Tracks applied database migrations for schema evolution management.
Data Flow & Integration
Order Creation Flow
1. Command Reception
- CreateOrderCommand received via HTTP API
- Validated for business rules (buyer exists, items valid, prices correct)
- Basket data retrieved from Basket Service via gRPC
2. Domain Event Generation
- Order aggregate created with New status
- OrderPlacedEvent registered as domain event
- Event includes order details, buyer info, and line items
3. Transactional Persistence
- INSERT into orders table (current state)
- INSERT into order_items table (line items)
- INSERT into mt_events table (OrderPlacedEvent)
- INSERT into outbox_message table (UserCheckedOutEvent for Finance Service)
- Transaction COMMIT (all atomic)
4. Event Processing
- Marten projections update mt_doc_ordersummaryview
- Outbox worker publishes UserCheckedOutEvent to RabbitMQ
- Finance Service begins saga orchestration
Order Completion Flow
1. Command Execution
- CompleteOrderCommand received (typically from Finance Service)
- Order retrieved with status validation (must be New)
- Business rules enforced (only new orders can be completed)
2. State Transition
- Order status updated to Completed
- OrderCompletedEvent registered
- last_modified_at timestamp updated
3. Event Persistence
- UPDATE orders SET status=2, last_modified_at=NOW()
- INSERT into mt_events (OrderCompletedEvent)
- INSERT into outbox_message (OrderStatusChangedToCompleteEvent)
- Transaction COMMIT
4. Integration
- Event published to message broker
- Finance Service saga transitions to Completed state
- Notification Service sends completion email to customer
Order Cancellation Flow
1. Cancellation Request
- CancelOrderCommand received (from customer or system)
- Order retrieved and validated (must be in New status)
- Business logic prevents cancellation of completed orders
2. Domain Event
- Order status transitioned to Cancelled
- OrderCancelledEvent registered with cancellation reason
- Inventory reservations released (via integration events)
3. Database Updates
- UPDATE orders SET status=1, last_modified_at=NOW()
- INSERT into mt_events (OrderCancelledEvent)
- INSERT into outbox_message (OrderStatusChangedToCancelEvent)
- Transaction COMMIT
4. Downstream Impact
- Finance Service saga transitions to Cancelled state
- Notification Service sends cancellation confirmation
- Basket Service may restore items (if configured)
Event Sourcing Replay
Event Stream Reconstruction:
- Read all events for order ID from mt_events ORDER BY version
- Apply events sequentially to reconstruct aggregate state
- Validate current state matches orders table (consistency check)
- Used for debugging, audit, and temporal queries
Projection Rebuilding:
- Truncate projection tables (mt_doc_ordersummaryview)
- Replay all events from mt_events in order
- Marten automatically updates projections
- Used after projection schema changes
Performance & Optimization
Indexing Strategy
Critical Indexes:
- Primary keys (automatically indexed): orders(id), order_items(id), buyers(id), mt_events(seq_id, is_archived), mt_streams(id, is_archived)
- Foreign keys (automatically indexed): order_items(order_id), orders(buyer_id), mt_events(stream_id, is_archived)
- Unique constraints (automatically indexed): inbox_state(message_id, consumer_id)
- Recommended indexes: orders(buyer_id), orders(created_at), orders(status), mt_events(stream_id), mt_events(type), mt_events(timestamp)
Performance Characteristics:
- Order Lookup: O(1) via primary key
- Buyer Orders: O(log n) via buyer_id index
- Event Stream: O(log n) via stream_id index
- Status Queries: O(log n) via status index
- Time-Range Queries: O(log n) via created_at index
Query Patterns
High-Frequency Queries:
- Get Order by ID: SELECT from orders WHERE id = ? (with JOIN to order_items)
- List Buyer Orders: SELECT from orders WHERE buyer_id = ? ORDER BY created_at DESC
- Get Event Stream: SELECT from mt_events WHERE stream_id = ? ORDER BY version
- Poll Outbox: SELECT from outbox_message WHERE sent_time IS NULL ORDER BY sequence_number
- Check Inbox: SELECT from inbox_state WHERE message_id = ? AND consumer_id = ?
Write Patterns:
- Create Order: INSERT into orders, order_items (batch), mt_events, outbox_message
- Update Status: UPDATE orders, INSERT into mt_events, INSERT into outbox_message
- Delete Order: UPDATE orders SET is_deleted = true (soft delete)
Database Sizing
Estimated Growth (per 1,000 orders/day):
- orders: ~150 KB/day (150 bytes/row × 1,000 rows)
- order_items: ~600 KB/day (assuming avg 3 items/order, 200 bytes/row × 3,000 rows)
- buyers: ~50 KB/day (assuming 30% new buyers, 150 bytes/row × 300 rows)
- mt_events: ~1.5 MB/day (assuming avg 3 events/order, 500 bytes/row × 3,000 events)
- mt_streams: ~100 KB/day (100 bytes/row × 1,000 streams)
- inbox_state: ~500 KB/day (500 bytes/row × 1,000 messages)
- outbox_message: ~1.5 MB/day (purged after 7 days)
Annual Storage (with retention):
- Domain tables: ~300 MB/year (orders + order_items + buyers)
- Event store: ~550 MB/year (mt_events + mt_streams)
- MassTransit tables: ~200 MB (rolling windows)
- Total: ~1 GB/year + indexes (~2.5 GB total with 7-year retention)
Marten Performance Optimizations
Snapshot Strategy:
- Snapshots created every 20 events (configurable)
- Reduces event replay time for order aggregates
- Snapshot overhead: ~10% storage increase
- Performance gain: 80% faster aggregate loading for long streams
Projection Performance:
- Inline projections: Updated in same transaction (slower writes, fast reads)
- Async projections: Updated by background process (fast writes, eventual consistency)
- Current configuration: Inline for OrderSummaryView (strong consistency)
Connection Pooling:
- PgBouncer recommended for connection management
- Default pool size: 100 connections
- Typical active: 20-30 connections during peak load
Data Lifecycle & Retention
Retention Policy
- orders: 7 years (regulatory and business compliance)
- order_items: 7 years (tied to parent order)
- buyers: Indefinite (customer master data)
- mt_events: 7 years (complete audit trail)
- mt_streams: 7 years (tied to events)
- inbox_state: 30 days (deduplication window)
- outbox_message: 7 days (after successful delivery)
- outbox_state: Indefinite (small metadata)
Archival Strategy
Hot Storage (Active Database):
- Active orders (New status)
- Recent orders (last 90 days)
- Active event streams
- Current inbox/outbox records
Warm Storage (Archive Database or Partition):
- Completed/Cancelled orders (90 days - 2 years)
- Associated event streams
- Queryable for customer service and analytics
Cold Storage (Data Lake):
- Historical orders (2+ years)
- Complete event history for compliance
- Compressed and immutable archives
- Rarely accessed, batch queries only
Cleanup Jobs
Automated Maintenance:
- Daily: Purge old outbox_message records (sent more than 7 days ago)
- Weekly: Purge old inbox_state records (consumed more than 30 days ago)
- Monthly: Archive old orders and events (older than 2 years) to warm storage
- Quarterly: Vacuum and analyze tables for optimal query planning
- Annual: Archive to cold storage (older than 7 years, per retention policy)
Soft Delete Management
Soft Delete Strategy:
- Orders marked is_deleted=true instead of physical deletion
- Preserves referential integrity
- Enables “undelete” functionality if needed
- Audit trail remains intact
Cleanup Process:
- Soft-deleted orders retained for 90 days
- After 90 days, eligible for archival
- Physical deletion only after retention period expires
Monitoring & Observability
Key Performance Indicators
Order Metrics:
- Order Volume: Total orders created per day/hour
- Completion Rate: Percentage of orders reaching Completed status
- Cancellation Rate: Percentage of orders cancelled before completion
- Average Order Value: Mean total price across all orders
- Order Processing Time: Time from New to Completed status
Event Store Metrics:
- Event Append Rate: Events written per second
- Stream Size Distribution: Number of events per aggregate
- Projection Lag: Time delay between event append and projection update
- Event Replay Performance: Time to reconstruct aggregate from events
Database Metrics:
- Query Performance: p95/p99 latency for common queries
- Connection Pool Utilization: Active vs. available connections
- Table Sizes: Growth rate monitoring for capacity planning
- Index Usage: Identify unused or under-utilized indexes
- Disk Usage: Current vs. allocated storage
Integration Metrics:
- Outbox Backlog: Count of unsent messages
- Inbox Duplicate Rate: Percentage of rejected duplicate messages
- Event Publishing Latency: Time from outbox insert to broker publish
Health Checks
Database Connectivity:
- Verify PostgreSQL is reachable and accepting connections
- Check connection pool availability
Event Store Health:
- Validate mt_events table is writable
- Check for event append failures or conflicts
- Monitor projection processing lag
Data Consistency:
- Verify orders table and mt_events table are synchronized
- Check for orphaned order_items (missing parent order)
- Validate projection data matches event stream
Outbox Processing:
- Alert if outbox backlog exceeds 1,000 messages
- Monitor for stalled outbox workers (no messages sent in 15 minutes)
Logging & Tracing
Structured Logging:
- Order creation/completion/cancellation logged with order ID
- Event appends logged with stream ID and event type
- Domain events include correlation IDs for distributed tracing
- Database errors logged with query context
Distributed Tracing:
- OpenTelemetry spans for order operations
- Correlation IDs link commands, events, and integration events
- End-to-end tracing from HTTP request to message broker publish
Event Store Audit:
- Complete immutable history in mt_events
- Every domain event captured with timestamp and metadata
- Temporal queries enable point-in-time reconstruction
Security & Compliance
Data Classification
- Classification: Internal - Contains customer PII and financial transaction data
- Access Mode: Read/Write - Ordering Service only
- Residency: East Asia region - Data sovereignty compliance
- Authoritative: True - Single source of truth for order data
Security Measures
Access Control:
- Database user with least-privilege permissions
- No direct external access (internal microservices only)
- Service-level authentication via connection strings
- Row-level security not required (single-tenant per service)
Data Protection:
- Encryption at rest (Azure PostgreSQL managed encryption)
- Encryption in transit (TLS 1.2+)
- Customer PII limited to buyer name and address
- No sensitive payment data stored (handled by Finance Service)
Event Store Security:
- Immutable event log prevents tampering
- Append-only access pattern (events never modified)
- Cryptographic hashing for event integrity (optional)
- Access logs for audit compliance
Audit Trail:
- Complete order history in event store
- Every state change captured as domain event
- Correlation IDs link related events across services
- Immutable audit trail for compliance and disputes
Compliance Considerations
Financial Regulations:
- 7-year retention for order financial records
- Complete audit trail via event sourcing
- Transaction history reconstructable from events
GDPR / Privacy:
- Customer names and addresses stored (PII)
- Right to access: Query orders and events by buyer ID
- Right to erasure: Anonymize buyer data while retaining order metadata
- Right to portability: Export order history in machine-readable format
Data Retention:
- 7-year retention for business and regulatory requirements
- Automatic archival after 2 years to warm storage
- Secure deletion after retention period expires
Disaster Recovery & High Availability
Backup Strategy
Automated Backups (Azure PostgreSQL Flexible Server):
- Point-in-Time Recovery (PITR): 30-day retention
- Backup Frequency: Continuous transaction log streaming
- Recovery Objectives: RPO less than 5 minutes, RTO less than 1 hour
- Geo-Redundancy: Backups replicated to secondary region
Event Store Backups:
- mt_events and mt_streams backed up together
- Immutable event log ideal for incremental backups
- Event replay capability provides additional resilience
Manual Snapshots:
- Pre-deployment snapshots before schema migrations
- Retained for 90 days
- Used for rollback scenarios
High Availability
Azure PostgreSQL HA Configuration:
- Availability Zone Redundancy: Synchronous replica in different AZ
- Automatic Failover: Sub-60-second failover time
- Read Replicas: Optional for reporting and analytics queries
- Connection Pooling: PgBouncer for efficient connection management
Marten Resilience:
- Event append idempotency prevents duplicate events
- Optimistic concurrency prevents conflicting updates
- Event replay enables recovery from projection failures
Disaster Recovery Procedures
Failure Scenarios:
-
Database Connection Failure:
- Connection pool retry with exponential backoff
- Circuit breaker trips after 5 consecutive failures
- Graceful degradation: Return 503 Service Unavailable
-
Event Store Corruption:
- Restore from PITR to point before corruption
- Rebuild projections from restored event store
- Validate consistency with downstream services
-
Projection Failure:
- Failed events moved to mt_doc_deadletterevent
- Fix projection logic
- Replay dead letter events or rebuild projection from scratch
-
Complete Database Loss:
- Failover to geo-redundant replica (RTO approximately 1 hour)
- Restore from latest backup snapshot
- Replay unacknowledged messages from message broker
Recovery Testing:
- Quarterly disaster recovery drills
- Automated backup restoration tests
- Event replay validation
- Chaos engineering experiments (random failures)
Integration with Marten
Event Sourcing Configuration
Stream Management:
- One stream per order aggregate (stream_id matches order.id)
- Events appended with optimistic concurrency (version checking)
- Snapshots generated every 20 events for performance
- Soft-deleted streams marked but events retained
Event Serialization:
- Events stored as JSONB for efficient querying
- System.Text.Json for serialization
- .NET type information stored for deserialization
- Forward-compatible schema evolution
Projection Configuration:
- OrderSummaryView projection updated inline (same transaction)
- Custom projections can be added for specific read models
- Projection state tracked in mt_event_progression
- Failed events captured in mt_doc_deadletterevent
Domain Event Publishing
Event Flow:
- Domain event registered in aggregate root
- Aggregate persisted via Marten (events appended to mt_events)
- Marten’s event subscription detects new events
- Events transformed to integration events
- Integration events published to outbox_message
- Background worker publishes to RabbitMQ
Integration Event Mapping:
- OrderPlacedEvent maps to UserCheckedOutEvent
- OrderCompletedEvent maps to OrderStatusChangedToCompleteEvent
- OrderCancelledEvent maps to OrderStatusChangedToCancelEvent
Temporal Queries
Point-in-Time Queries:
- Query order state at specific timestamp
- Replay events up to target time
- Useful for dispute resolution and debugging
Aggregate History:
- Retrieve complete event history for order
- Visualize order lifecycle and state changes
- Audit trail for compliance and investigations