SQL Project: Financial Reporting Engine (Phase 3 Capstone)

Welcome to the Phase 3 Capstone of the SQL Masterclass. You have explored the depths of relational logic, reached into the JSONB NoSQL future, and optimized set operations. Now, you will handle the highest-stakes data of all: Real Money.

This 1,500+ word project guide is designed as an architectural assignment. You are the Core Ledger Architect at "LedgerLock," a financial infrastructure company serving global banks. Your mission is to build a high-performance system where not a single cent can ever "Vanishing" or "Appear" without a cryptographic-grade mathematical trail.

1. Project Goal: The "Integrity Hub" Standard

Most apps handle money as a "Balance" column in a users table. This is an Architectural Anti-Pattern. If a row update fails, the balance becomes a "Ghost Mirror" and your data is corrupted.

In this project, your goal is to build an Insert-Only Ledger. You never UPDATE a balance; you only record the Movement of value. The "Balance" is an aggregate result of that history.

The Integrity Success Pillars

1. Atomicity: Every transfer must be a "Single Action" (The ACID standard).
2. Immutability: Once a transaction is recorded, it can never be deleted or changed.
3. Auditability: Every penny must be traceable back to a specific timestamp, user, and IP address.

2. Task 1: Double-Entry Schema Design (The Math Mirror)

In professional finance, the sum of all accounts in the universe must be Zero. Every Credit (+) must have a corresponding Debit (-).

The Mission

Design the accounts and journal_entries tables. This requires precision types.

• The Precision Mirror: You must use NUMERIC(20, 4) or store values in BIGINT (cents/micros) to avoid Floating Point Drift.
• Requirement: Use a CHECK constraint on the journal table to ensure amount != 0.

CREATE TABLE accounts (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    currency VARCHAR(3) NOT NULL, -- ISO 4217 (USD, EUR, etc)
    type VARCHAR(20) CHECK (type IN ('ASSET', 'LIABILITY', 'EQUITY', 'REVENUE', 'EXPENSE'))
);

CREATE TABLE journal_entries (
    id UUID PRIMARY KEY,
    transaction_id UUID NOT NULL, -- Connects the Debit and Credit
    account_id UUID REFERENCES accounts(id),
    amount NUMERIC(20, 4) NOT NULL, 
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

2.1 The Physics of Transaction Isolation: Serializability vs. Reality

In financial systems, "Almost Correct" is "Completely Wrong."

• The Problem: If two transfers happen simultaneously for the same account, they might both read a balance of $$100$, both see that $$60$ is available, and both succeed—leaving the account with $-$20$. This is the Lost Update anomaly.
• The Hardware Mirror: At the silicon level, the database must decide how to lock these rows.
  • READ COMMITTED: Fast, but allows "Non-Repeatable Reads."
  • SERIALIZABLE: The Gold Standard. It acts as if every transaction happened one after another in a single line.
• The Tradeoff: SERIALIZABLE increases the rate of Serialization Failures (40001 errors). Your application code must be architected to catch these errors and Retry the transaction automatically. This is the hallmark of a Senior Financial Architect.

3. Task 2: The Atomic Transfer Procedure (ACID Lab)

Moving $$1,000$ from a User to a Merchant must be an atomic "All-or-Nothing" operation.

The Mission

Write a Stored Procedure execute_transfer that moves funds between two accounts.

The Logic of Balance Enforcement

Before you allow an INSERT into journal_entries, you must ensure the sender has enough "Live" value.

• The Physics of the Total: Since we are using an insert-only ledger, the "Balance" is a dynamic calculation. You should use a Subquery with a Locking Clause (FOR UPDATE) to lock the account's history during the transfer calculation.

CREATE OR REPLACE PROCEDURE execute_transfer(
    sender_id UUID,
    receiver_id UUID,
    amount_to_move NUMERIC(20, 4),
    memo TEXT
) LANGUAGE plpgsql AS $$
DECLARE
    txn_id UUID := gen_random_uuid();
    current_balance NUMERIC(20, 4);
BEGIN
    -- Lock both accounts to prevent deadlocks (Sort by ID first)
    PERFORM * FROM accounts WHERE id IN (sender_id, receiver_id) ORDER BY id FOR UPDATE;

    -- Calculate balance
    SELECT SUM(amount) INTO current_balance FROM journal_entries WHERE account_id = sender_id;

    IF current_balance < amount_to_move THEN
        RAISE EXCEPTION 'Insufficient Funds: Found %, required %', current_balance, amount_to_move;
    END IF;

    -- 1. Deduct from Sender
    INSERT INTO journal_entries (transaction_id, account_id, amount, description)
    VALUES (txn_id, sender_id, -amount_to_move, memo);

    -- 2. Add to Receiver
    INSERT INTO journal_entries (transaction_id, account_id, amount, description)
    VALUES (txn_id, receiver_id, amount_to_move, memo);
END;
$$;

4. Task 3: Point-in-Time Reconciliation (Snapshot Engine)

A regulator asks: "What was the total liability of your platform at exactly midnight on New Year's Eve?"

The Mission

Build a query that calculates "Snapshots" of balances for all accounts at any historical timestamp.

The Performance Challenge

As your journal_entries table grows to 100 million rows, calculating a balance by summing everything from the beginning of time will take forever.

• The Optimization Strategy: Implement Materialized Snapshots. Every month, your tool should "Freeze" the balance into a monthly_balances table. To get a current balance, you only sum Latest_Snapshot + Entries_since_then.

5. Task 4: Multi-Currency Reporting (The Aggregation Gate)

Your company operates in USD, EUR, and JPY. The "Consolidated Balance Sheet" must be displayed in a single master currency (e.g., USD).

The Mission

Implement a "Currency Conversion View" that joins your transaction data with an exchange_rates table.

The Challenge: Temporal Joining

Exchange rates change every second. You must join each transaction with the rate that was active at that specific microsecond.

6. Task 5: The "Immutable Shield" (Security Audit)

A rogue employee with DBA access tries to hide a $$50,000$ theft by deleting the transaction record.

The Mission

Implement Administrative Hardening.

1. No Updates/Deletes: Create a Postgres role for your API that has INSERT and SELECT rights but NO UPDATE or DELETE rights on the financial tables.
2. Audit Logs: Use a TRIGGER to record every log attempt into a separate audit_trail table that is shadowed to an external system.

6.1 The Physics of Write-Ahead Logs (WAL) for Auditing

Even if a delete succeeds, the data isn't "Gone."

• The WAL Mirror: Every insert, update, or delete is first written to the Write-Ahead Log on the SSD as a sequential stream of bytes.
• The Forensic Standard: In high-security finance, you should implement Logical Decoding. This allows a separate process to "Read" the WAL stream and stream every change to a geographically distant, read-only "Immutable Audit Bucket" (like S3 with Object Lock). This ensures that even if the entire database server is compromised, the mathematical history of the money remains intact.

7. Performance Lab: Tuning for 1M Transactions/Day

To pass this Lab, your reporting engine must be fast.

• Task: Use an Index-Only Scan strategy. Create a covering index on (account_id, created_at, amount). This allows the balance-calculator to run entirely in the RAM of the index buffer, never touching the slow SSD storage of the table heap.
• Mission Result: You must demonstrate a SUM() over 1 million rows in under 200 milliseconds.

8. Summary: The Integrity Success Criteria

1. Transactional Zero-Sum: You must provide a "Check Query" that proves the entire database sums to 0.0000 across all accounts.
2. Precision Verification: Demonstrate that 1 million addition operations don't result in a single "micro-cent" of rounding error.
3. Multi-Currency Logic: Show a balance sheet that converts JPY and EUR to USD using temporal (Time-aware) join logic.
4. Security Denial: Prove that a DELETE command sent from the application role results in a PERMISSION DENIED error.
5. Recovery Readiness: Explain how the WAL-based audit stream ensures that a deleted record can be mathematically reconstructed.

This project transitions you from a "Coder" to a "Guardian of the Ledger." By mastering the physical internals of immutable storage and the temporal logic of financial movement, you gain the skills to engineer the infrastructure for the next generation of Fintech unicorns. You graduate from "Gathering data" to "Architecting the Truth."

Phase 16: Deliverables

• Your accounts and journal_entries schema DDL.
• The execute_transfer Stored Procedure (with ACID protections).
• A "Snapshot" query engine capable of historical balance calculation.
• Advanced Goal: Implement a WAL-listening audit firehose using Logical Decoding.
• Evidence of the "Read-Only" role denying a deletion attempt.

Read next: SQL Window Functions: Mastering Analysis Across Time →

Part of the SQL Mastery Course — engineering the truth.

7. Performance Lab: Tuning for 1M Transactions/Day

To pass this Lab, your reporting engine must be fast.

• Task: Use an Index-Only Scan strategy. Create a covering index on (account_id, created_at, amount). This allows the balance-calculator to run entirely in the RAM of the index buffer, never touching the slow SSD storage of the table heap.
• Mission Result: You must demonstrate a SUM() over 1 million rows in under 200 milliseconds.

8. Summary: The Integrity Success Criteria

1. Transactional Zero-Sum: You must provide a "Check Query" that proves the entire database sums to 0.0000 across all accounts.
2. Precision Verification: Demonstrate that 1 million addition operations don't result in a single "micro-cent" of rounding error.
3. Multi-Currency Logic: Show a balance sheet that converts JPY and EUR to USD using temporal (Time-aware) join logic.
4. Security Denial: Prove that a DELETE command sent from the application role results in a PERMISSION DENIED error.

This project transitions you from a "Coder" to a "Guardian of the Ledger." By mastering the physical internals of immutable storage and the temporal logic of financial movement, you gain the skills to engineer the infrastructure for the next generation of Fintech unicorns. You graduate from "Gathering data" to "Architecting the Truth."

Phase 16: Deliverables

• Your accounts and journal_entries schema DDL.
• The execute_transfer Stored Procedure (with ACID protections).
• A "Snapshot" query engine capable of historical balance calculation.
• Evidence of the "Read-Only" role denying a deletion attempt.

Read next: SQL Window Functions: Mastering Analysis Across Time →

Frequently Asked Questions

Q: What SQL techniques are essential for financial reporting?

Period-over-period comparisons using LAG or self-joins on a date spine (a complete series of dates with no gaps), running totals with SUM() OVER (ORDER BY period), and pivot tables using conditional aggregation (SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1) are the core techniques. Financial reports also require careful handling of NULL vs zero (a missing month should show zero, not NULL — use COALESCE), consistent rounding (ROUND(amount, 2)), and fiscal year logic that may not align with calendar year. CTEs make complex multi-step financial logic readable and maintainable.

Q: How do I generate a report with zero-fill for periods with no data?

Generate a complete date spine using generate_series (PostgreSQL) and LEFT JOIN your data to it: SELECT d.month, COALESCE(SUM(amount), 0) AS revenue FROM generate_series('2024-01-01', '2024-12-01', '1 month'::interval) AS d(month) LEFT JOIN orders ON DATE_TRUNC('month', order_date) = d.month GROUP BY d.month ORDER BY d.month. The LEFT JOIN ensures every month appears even with no orders; COALESCE converts NULL (no data) to zero. This pattern works for any time grain — days, weeks, quarters — by adjusting the series interval and truncation function.

Q: How do I handle currency precision in SQL financial calculations?

Store monetary values as integers (cents, not dollars) or as NUMERIC(precision, scale) — never FLOAT or DOUBLE PRECISION, which introduce rounding errors. NUMERIC(19, 4) stores up to 15 digits before the decimal with 4 decimal places, sufficient for most financial use cases. Perform all intermediate calculations in the full precision type and round only at the final output step. For multi-currency systems, store the amount and currency code separately, never pre-convert to a single currency in the database — exchange rates change and historical conversions must remain at the original rate.

Part of the SQL Mastery Course — engineering the truth.