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

Atomicity: Every transfer must be a "Single Action" (The ACID standard).
Immutability: Once a transaction is recorded, it can never be deleted or changed.
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.

sql

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.

sql

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.

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.
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

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

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

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

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.

Part of the SQL Mastery Course — engineering the truth.