DATABASE SCHEMA PRACTICE: Event-Sourced Banking Platform

Scenario: You’re a PM at a fintech startup building a modern banking platform. Due to strict regulatory requirements around audit trails and the need to reconstruct account state at any point in time, your engineering team has decided to implement event sourcing with CQRS (Command Query Responsibility Segregation).

Core Features to Support:

• Customer account creation and management
• Money transfers between accounts (both internal and external)
• Transaction history and account balance queries
• Account statement generation for any date range
• Regulatory audit trail showing every state change
• Real-time fraud detection requiring access to event stream
• Customer-facing balance and transaction queries (must be fast, <100ms)
• Ability to “replay” account state to any point in time for investigations

Scale Context:

• 5 million customers
• 200 million transactions per month
• Regulatory requirement to retain all events for 7 years
• Customer queries for balance/history must be <100ms
• Event processing for fraud detection must happen within 500ms

Your Task: Design the schema by defining:

1. What tables or collections do you need for both the write side (events) and read side (projections)?
2. What are the key fields for each?
3. How do events relate to projections? What’s the update mechanism?
4. Would you use SQL or NoSQL for each side? Why?
5. How would you handle event versioning and schema evolution?
6. What are the scaling considerations for event storage and projection updates?

Take 10-15 minutes to design your schema, then share your design.