SQL Final Project: Global Billing Engine

1. Task 1: Global Sharding (Declarative Partitioning)

A global system must be legally compliant. European users must have their data stored in the EU, and US users in the US.

The Mission: Geographic Data Residency

• Implementation: Use Declarative Partitioning to shard the invoices table by region_code.
• Hardware-Mirror Logic: By partitioning your table, you are physically splitting the large "Global" table into several smaller "Region" tables on the disk.
• The Benefit:
  1. Compliance: You can physically host the EU shard in a Frankfurt data center and the US shard in Virginia.
  2. Performance: When a US user queries their history, the database only scans the invoices_us partition, ignoring billions of EU and Asian rows.
• Requirement: Use UUID v7 as the partition key. Its time-ordered nature ensures that even with distributed writes across the globe, your B-Tree index remains efficient and sequentially packed.

2. Task 2: Subscriptions & Proration (The State Machine)

Financial logic must be absolute. If a user upgrades from a $10/month plan to a $100/month plan on day 15 of a 30-day month, you cannot charge them the full $100.

The Mission: Atomic Proration Engine

• Logic: You must calculate how much credit they have left from the $10 plan and apply it to the $100 plan for the remaining 15 days.
• Mission: Write a Stored Procedure calculate_billing_cycle.
• The Hardware Guard: Use SERIALIZABLE Isolation Level.
  • The Risk: If two upgrade requests hit the database at the exact same millisecond, and you use a lower isolation level (Read Committed), both requests might see the "Old Plan" and process a proration, leading to a "Double Refund."
  • The Solution: Serializable isolation detects this "Dependency Conflict" and rolls back the second transaction, ensuring one—and only one—accurate result.

3. Task 3: The Event Sourcing Ledger (No Deletes)

In professional finance (GAAP Compliance), you never use an UPDATE command to change a user's balance. If you change a balance from $10 to $20, and there is no record of "Why," you have failed.

The Mission: Immutable Ledger Design

• Requirement: Build a ledger table where every row is an event (Credit/Debit).
• The Pattern: Event Sourcing. To find a user's current balance, you SUM() their historical entries.
• The "Materialization" Hack: Because SUM() on 1 million entries is slow, you will use a Trigger (Module 28) to maintain a "Materialized Balance" in the accounts table, but the ledger remains the primary source of truth.
• Hard Rule: The ledger table should have its DELETE permission revoked for all users. You only append.

4. Task 4: Hierarchical Tax Recursive CTE

Taxes are not a simple flat fee. They are a tree (Federal > State > City).

The Mission: The Tax Chain

• Goal: Given a user's location, find the "Cumulative Active Tax Rate."
• Technique: Use a Recursive CTE (Module 16) to traverse a tax_regions table.
• Requirement: If a user is in "Austin, Texas," your query must find the City tax, the State tax, and the Federal tax by walking up the "Parent" ID chain, and sum them into a single percentage.

5. Task 5: Multi-Tenant Security (The RLS Shield)

As a fintech VP, your worst nightmare is "Cross-Talk"—User A seeing User B's invoices.

The Mission: Row-Level Security

• Implementation: Enable Row Level Security (RLS) on the invoices and ledger tables.
• The Mirror Physics: Define a policy that checks the tenant_id against the current_setting('app.active_tenant').
• The Guarantee: Even if a developer writes a bug in the Node.js API that defaults a query to SELECT * FROM invoices, the database will automatically inject the WHERE tenant_id = ... filter at the kernel level. Data leakage is physically impossible.

6. Task 6: Strategic Fraud Detection with Window Functions

Identify duplicate charges in a high-velocity stream.

The Mission: The "Idempotency" Check

• Technology: Use Window Functions (LAG and LEAD) to find payments from the same card_id with the same amount within a 60-second window.
• The Output: Flag these transactions as "Potential Duplicate" for human review.

7. Task 7: The Multi-Currency Mirror: Handling Floating Point Drift

Quantum SaaS launches in Europe and Japan. You now have to handle EUR and JPY.

• The Physics of Money: Never use the FLOAT or REAL data types for money. These are "Approximate" types based on binary fractions. In the world of finance, 0.1 + 0.2 in floating point is 0.30000000000000004. Over 1 million transactions, this "Precision Drift" will create a multi-thousand dollar hole in your accounting or cause your audits to fail.
• The Solution: Use the NUMERIC (or DECIMAL) data type with a specific scale (e.g., NUMERIC(19, 4)). This stores the data as a fixed-point decimal in the hardware, ensuring that 0.1000 + 0.2000 is exactly 0.3000.
• The JPY Exception: Some currencies have zero decimal places. Your database logic must handle the "Unit Scale" dynamically, often by storing all values as "Cents" or the smallest possible unit (as an INTEGER or BIGINT) and performing the conversion only at the presentation layer.

8. Task 8: Idempotency: The "Double-Charge" Prevention

The most common billing bug in digital commerce is the Double Charge. A user clicks "Buy" twice, or a network timeout causes the application to retry a successful payment request.

The Idempotency Key Mirror

• Implementation: Add a UNIQUE idempotency_key (UUID) to your invoices and ledger tables.
• The Physics: When the application sends a payment request, it generates a unique key for that specific intent. If the database receives a request with a key it has already processed, it uses the Unique Constraint to reject the second insertion.
• The Business Result: Instead of charging the user twice, the database returns the "Success" of the existing record. This uniqueness is enforced by the B-Tree index, meaning it is physically impossible for two identical charges to land in your storage, even if two different app servers try to process them simultaneously. This is the cornerstone of Nuclear-Grade Billing Safety.

10. Task 9: The Future Mirror: Analytics and AI Integration

In 2026, a billing engine is more than a processor; it is a Data Source.

• The Physics of Insights: Every financial event you have recorded in the ledger is a signal. Marketing needs to know the "Lifetime Value (LTV)" of users and the "Churn Risk" based on payment failures.
• The Integration: Build a Materialized View that aggregates monthly revenue by plan type.
• The AI Edge: Use the pgvector extension to store "Customer Behavioral Embeddings." By analyzing the timing and frequency of billing events, your AI model can predict with 90% accuracy which users are likely to cancel their subscription next month, allowing your success team to intervene with a discount offer.

11. Summary: The Final Graduation Criteria

To graduate the SQL Masterclass and receive your certification, your OrbitPay Billing Engine must satisfy:

1. Structural Integrity: 3NF normalization for base tables, JSONB for webhook event logs.
2. Concurrency Mastery: The Proration engine must pass a "Race Condition" test script.
3. Audits & Compliance: The ledger table must prove it handles "Compensating Entries" rather than DELETEs.
4. Security Sovereignty: RLS must bock any unauthorized cross-tenant queries.
5. Performance Stability: All summary reports must use Materialized Views with Concurrent Refreshes to maintain sub-100ms response times.

You are no longer a coder who "knows a bit of SQL." You are a Database Principal. You understand the physics of storage, the mathematics of sets, and the ethics of data integrity. You are ready to architect the next generation of global systems.

Go Build the Future.

Phase 30: Final Deliverables Checkout

• A single SQL script containing the full Partitioned Schema.
• A Stored Procedure showcasing SERIALIZABLE isolation.
• A Window-Function-powered Monthly Financial Report (Total revenue, Churn, Prorated Credits).
• Your Certificate of Mastery from TopicTrick.

You have completed the SQL Mastery Course — engineering the truth.