Scalable Transaction Logging Schema

How would you design a scalable database schema to handle high-volume transaction logging and reporting requirements? Use the provided schema to produce a monthly report for active Mastercard merchants from January through March 2025, considering only transactions whose latest event is SETTLED.

Output

1. One row per active merchant and reporting month, including months with no settled transactions
2. Columns: merchant_id, merchant_name, report_month, transaction_count, total_amount, prior_month_amount, month_over_month_pct, and cumulative_amount
3. Order by merchant_id, then report_month ascending

Output details

• Use 2025-01-01 through 2025-03-31 inclusively, with timestamps on or after 2025-01-01 00:00:00 and before 2025-04-01 00:00:00.
• Represent each report_month as the first day of its month: 2025-01-01, 2025-02-01, and 2025-03-01.
• For each transaction, use the event with the greatest event_id as its latest event, and include it only when that event is SETTLED.
• Format total_amount and cumulative_amount as numeric values with exactly two decimal places, replacing missing totals with 0.00.
• Set prior_month_amount to NULL for the first month, and calculate it as the preceding reporting month's total_amount.
• Return month_over_month_pct rounded to two decimals, but use NULL when prior_month_amount is NULL or zero.