SaaS database for real-time finance: ledger and state table, your ultimate source of truth

How to Structure a Database for Real-Time Financial Data

As your startup scales, the financial data flowing from systems like Stripe or your application backend grows from a stream into a river. The initial need for live financial data often appears in a customer-facing dashboard or an internal tool for tracking unit economics. The first impulse is to query a simple transaction table. This works for a while, but soon, dashboards slow to a crawl, cloud database costs spike unexpectedly, and the pressure to deliver instant financial insights clashes with the need for a perfect, auditable record. You need a way to structure your database for live financial data that is fast, scalable, and audit-ready without hiring a dedicated data engineering team. This is a common architectural crossroads, and choosing the right path early prevents significant refactoring later. For a complete overview, see the Transitioning to Real-Time Visibility framework.

The Foundational Trade-off in Financial Data Architecture

Why can't you just use one big table and make it fast? The answer lies in a fundamental conflict between two distinct jobs: recording history and reporting the present. A system designed to perfectly record every event is optimized for writing data, while a system built for speed is optimized for reading data. Trying to force a single database table to do both well is the primary source of performance issues in most financial data architecture.

To build a scalable finance database, you must recognize this conflict and design a system with two specialized components instead of one compromised one.

• An Auditable Log: This is a write-heavy system. Its only job is to record every single financial event that occurs, in order, without ever changing past entries. This provides transactional integrity and serves as your permanent historical record.
• A Performance Layer: This is a read-heavy system. Its job is to provide answers, like a customer's current balance, instantly. It prioritizes speed above all else, acting as a snapshot of the current state.

This separation is the key to achieving both speed and accuracy at scale. You are not choosing one over the other; you are building a dedicated solution for each requirement.

Pattern 1: The Immutable Ledger as Your Source of Truth

To ensure integrity and be instantly audit-ready, the foundation of your financial data architecture must be an immutable ledger. This is an append-only table where you record every event. Nothing is ever deleted or updated. If a mistake is made, a new reversing transaction is added to correct it. This creates a permanent, chronological history of everything that has ever happened, which is your ultimate source of truth.

This approach directly supports compliance mandates. For instance, the US Bank Secrecy Act and UK's FCA rules require maintaining clear, accessible transaction histories for 5-7 years. An immutable ledger inherently meets this requirement. For British companies, this also aligns with HMRC guidance on VAT record keeping. Every balance, report, or metric can be recreated and verified by replaying the events in this ledger, providing a bulletproof audit trail.

A Simple Ledger Schema

A basic SQL schema for an immutable ledger serves as an excellent starting point. The focus is on capturing the essential details of each event without modification.

CREATE TABLE ledger_events (
event_id UUID PRIMARY KEY,
account_id VARCHAR(255) NOT NULL,
transaction_time TIMESTAMPOFFSET NOT NULL,
event_type VARCHAR(50) NOT NULL, -- e.g., 'CHARGE', 'REFUND', 'PAYOUT'
amount_minor INTEGER NOT NULL, -- in cents to avoid floating point issues
currency CHAR(3) NOT NULL
);

Performance Limits of a Ledger-Only System

The ledger is perfect for audits, but it’s slow for checking a user's current balance. To get a balance, you have to sum every single transaction for that account from the beginning of time. While this is acceptable for occasional checks, it fails under the load of a real-time application.

The performance degradation threshold for ledger-only queries is typically hit when balance checks require summing more than a few thousand rows, often around the 10-50 million-row mark in the ledger table. At this point, queries become too slow and computationally expensive for interactive use. This is where the second pattern becomes necessary.

Pattern 2: The Current State Table for Real-Time Reporting

The current state table is the solution to the ledger's speed problem. It acts as a performance layer, or a materialized view, designed for one purpose: providing lightning-fast lookups of a current value, like a customer's balance or available credit. Instead of storing a history of events, this table stores just one row per account with the final, calculated result.

When your SaaS finance dashboard needs to display a user's balance, it queries this table. The database reads a single row, a highly efficient operation, and returns the value instantly. This is how you provide instant financial insights without running slow, expensive queries against the full transaction history every time a page loads.

A Read-Optimized State Table Schema

The schema for a current state table is simple and optimized for fast reads. The primary key allows for direct lookups, delivering the required data in milliseconds.

CREATE TABLE account_balances (
account_id VARCHAR(255) PRIMARY KEY,
current_balance_minor INTEGER NOT NULL,
currency CHAR(3) NOT NULL,
last_updated_time TIMESTAMPOFFSET NOT NULL,
last_ledger_event_id UUID NOT NULL -- for reconciliation
);

This design creates a critical distinction in your system. The ledger is the write-optimized source of truth, guaranteeing data integrity. The state table is the read-optimized performance layer, guaranteeing speed. The next challenge is ensuring they work together seamlessly for reliable financial data synchronization.

Bringing It All Together: Building a Scalable System

How do these two tables stay in sync without becoming a complex, expensive mess? The key is a one-way data flow and a clear understanding of consistency models. The pattern across SaaS startups is consistent: separate the audit log from the performance layer and connect them with an event-driven approach. This manages cost and complexity as you scale.

The One-Way Data Flow

The process ensures that your source of truth is always updated first, protecting data integrity before performance is considered.

1. Write to the Ledger First: A new financial event, like a customer's monthly subscription payment, occurs. Your application's first and most critical action is to write a new entry into the ledger_events table. This operation must be strongly consistent; your system should not proceed until this write is confirmed. This is your non-negotiable source of truth.
2. Trigger an Asynchronous Update: Once the ledger write is successful, the event is passed to an asynchronous processor. This can be done using a message queue like RabbitMQ or AWS SQS, or a database feature like Postgres's logical decoding. The key is that this step happens *after* the ledger is safely updated.
3. Update the State Table: The processor reads the event and updates the corresponding row in the account_balances table. For a $20 charge, it would increase the current_balance_minor by 2000. This update is idempotent, meaning if the event is processed twice by mistake, the final result is the same.

Embracing Eventual Consistency

This system uses eventual consistency for the state table. For a few milliseconds, the balance in the state table might not reflect the absolute latest transaction from the ledger. This is an acceptable trade-off for most dashboards and internal reporting tools. The user experience is instantaneous, and the brief delay has no material impact. For financial data synchronization, this model provides the resilience and scalability needed to handle high transaction volumes without compromising the integrity of the underlying record.

Managing Costs at Scale

This separation also has significant cost benefits. The ledger table will grow indefinitely. As it becomes massive, you can partition it by date and move older, "cold" data to cheaper storage, such as Amazon S3 Glacier or Google Cloud Storage. The "hot" account_balances table, which is essential for real-time queries, remains small, fast, and resides in your primary performance database, keeping query costs low and performance high.

Implementing This Architecture in Your Startup

For an early-stage company, implementing this two-table system provides a robust foundation for live accounting data integration that scales with your business. It directly solves the pain points of balancing transactional integrity, query performance, and audit readiness.

Simplifying Audits and Financial Operations

This structure makes audits and financial reporting far simpler. When auditors ask for a transaction history, you provide read-only access to the immutable ledger. For your day-to-day operations and customer-facing features, you rely on the fast current state table. This separation of concerns is a pragmatic approach for teams without extensive data engineering resources.

Detailed transaction tracking also becomes crucial for financial operations. When you need to justify R&D expenditures for programs like the US R&D tax credit capitalization rules: Section 174, or the UK R&D tax credit scheme: HMRC R&D scheme, having an immutable log of every related cost is invaluable. Similarly, this structured data makes it much easier to map your operational data to formal accounting standards, whether that's US GAAP for US companies using QuickBooks or FRS 102 for UK companies on Xero. It is also good practice to align with IRS guidance on tax record retention.

Your Immediate Next Steps

To put this architecture into practice, follow these steps:

1. Define Your Core Events: Identify the critical financial events in your application. These could be actions like 'subscription_charge', 'refund_issued', 'platform_fee_taken', or 'payout_processed'.
2. Build the Ledger: Implement the immutable ledger_events table. Treat this as the unchangeable historical record of your business and ensure all financial events are written here first.
3. Add the State Table: Create the account_balances table and the asynchronous process to update it from the ledger. Choose a synchronization method that fits your tech stack.
4. Route Your Queries: Direct all performance-sensitive queries for dashboards and application UIs to the state table. Use the ledger exclusively for auditing, reconciliation, and deep analytics.

This two-pattern architecture is how you structure a database for live financial data that is fast, affordable, and built to last. To learn more about applying these principles, continue at the Transitioning to Real-Time Visibility hub.

Frequently Asked Questions

Q: What happens if a mistaken transaction is written to the immutable ledger?
A: A core principle of immutable ledgers is that nothing is ever deleted. To correct a mistake, you append a new, reversing transaction. For example, to undo an incorrect charge, you add a corresponding refund event. This preserves a perfect audit trail, showing both the error and the correction.

Q: Is this two-table architecture overkill for a very early-stage startup?
A: It can be implemented in stages. Starting with just the immutable ledger is a robust foundation. You can run queries directly against it while your data volume is low. As performance needs grow, you can add the current state table and the asynchronous updater without having to re-architect your source of truth.

Q: Which database technology works best for this pattern?
A: Standard relational databases like PostgreSQL are excellent for the immutable ledger due to their strong transactional guarantees (ACID compliance). The state table can reside in the same database or, for extreme read performance at scale, in a system like Redis. The best choice depends on your specific performance needs and team expertise.

Q: How does this model handle multi-currency accounts?
A: The schemas provided can easily support multiple currencies. The `currency` column in both tables allows you to track events and balances distinctly for each currency. The current state table would simply hold one row per account, per currency, allowing for fast lookups of a customer's balance in USD, GBP, or any other currency.