High-Scale Personal Income, Expense & Budget Tracking System (100M+ DAU)

Intermediate

Designing a global, enterprise-grade Personal Income, Expense, and Budget Tracking system capable of serving 100 million Daily Active Users (DAU) requires a highly resilient, low-latency, and event-driven architecture.

The core challenge lies in handling high write throughput for financial transactions while simultaneously serving instant (<100ms) daily, weekly, monthly, and yearly aggregation queries and real-time budget threshold alert notifications.

1. System Requirements & Scope

Functional Requirements

Transaction Management:
- Record income and expense entries across multiple accounts (Cash, Bank, Credit Cards) with multi-currency support.
- Categorize transactions (e.g., Food, Rent, Salary, Entertainment).
- Split transactions across multiple categories.
- Transfer funds between accounts (must not count as income or expense).
Budget Management:
- Set flexible budget limits per category or globally across different time windows: Daily, Weekly, Monthly, and Yearly.
- Track live budget consumption with real-time alerts when threshold levels (e.g., 80%, 100%) are breached.
- Support rollover, rolling periods, and custom cycles.
Analytics & Aggregations:
- Provide sub-second dashboard responses for breakdown views: Daily trends, Weekly rollups, Monthly summaries, and Yearly reports.
- Compare current spending against historical periods.
Multi-Device Sync & Idempotency:
- Support seamless sync across mobile and web clients with strict idempotency to prevent duplicate transaction posting.
- Offline-first mobile clients with queued mutations.

Non-Functional Requirements

High Availability: 99.99% uptime ( minutes downtime/year).
Scalability: Handle 100M DAU with peak traffic spikes (e.g., end-of-month salary credits, holiday sales).
Low Latency:
- Transaction Ingestion (Write Path): (P99).
- Analytical Dashboard Queries (Read Path): (P99).
- Budget Alert Triggering: from transaction entry.
Data Consistency & Durability:
- ACID compliance for individual user ledger transactions (zero data loss).
- Eventual consistency () for analytical rollups and aggregate materialized views.
Security & Privacy: End-to-end encryption at rest (AES-256) and in transit (TLS 1.3), PCI-DSS alignment.
Compliance: SOC 2 Type II, ISO 27001, GDPR/CCPA, PSD2/Open Banking where applicable.

2. Capacity Estimation & Math (100M DAU Scale)

Traffic Estimation

Daily Active Users (DAU): (100M)
Monthly Active Users (MAU): (200M)
Transactions per User/Day: Average 5 transactions per active user.

Total Daily Transactions:

text

Daily Write Volume = 100M × 5 = 500,000,000 transactions/day

Average Write Throughput (TPS):

text

Avg Write TPS = 500,000,000 / 86,400 seconds ≈ 5,787 TPS

Peak Write Throughput (Peak TPS):

text

Peak Write TPS = 5,787 × 4 (Peak Multiplier) ≈ 23,148 TPS

Read Query Traffic (Dashboard Views & Budget Checks):
- Average 15 read requests per active user per day (app launches, category browsing, monthly charts).
- Total Daily Reads: .
- Avg Read QPS: .
- Peak Read QPS: .

Storage Estimation

Single Transaction Record Payload: ~250 Bytes
- transaction_id (UUID - 16B), user_id (UUID - 16B), account_id (UUID - 16B), category_id (INT - 8B), amount (DECIMAL - 8B), currency (CHAR(3) - 3B), transaction_time (TIMESTAMP - 8B), notes (VARCHAR - 150B), Index Metadata (~25B).

Daily Raw Data Ingestion:

text

500M transactions × 250 bytes = 125 GB/day

Yearly Raw Data Ingestion:
text
```
125 GB/day × 365 ≈ 45.6 TB/year
```
5-Year Storage Footprint (including DB indexes and 3x Replication Factor):
text
```
Total Storage = 45.6 TB/year × 5 years × 3 = 684 TB
```

Memory / Cache Estimation

Cache active user budget limits and current month category consumption counters in Redis.
Active state size per user: ~1 KB.
Active daily cached working set (100M users):
text
```
100M users × 1 KB = 100 GB Redis Cluster Memory
```

Latency Targets

Operation	p95 Target
Read account balances	< 150ms
List transactions	< 200ms
Create transaction	< 250ms
Update budget	< 250ms
Load dashboard	< 500ms
Generate large export	async job
Analytical report	< 2 sec

3. High-Level Architecture (HLD)

To handle high-concurrency write traffic while serving instant aggregate queries across Daily, Weekly, Monthly, and Yearly dimensions, we adopt a CQRS (Command Query Responsibility Segregation) and Event-Driven Architecture.

Architectural Component Breakdown

API Gateway & Auth: Handles TLS termination, OAuth2/JWT validation, global rate-limiting (Token Bucket), and request routing.
Transaction Ingestion Service (Write Path):
- Accepts transaction creation/modification requests.
- Enforces Idempotency via Redis using client-supplied UUIDs to prevent double-entries.
- Writes directly to the Sharded OLTP Database (PostgreSQL/MySQL sharded by user_id).
Change Data Capture (CDC) & Event Streaming:
- Uses Debezium CDC to capture database commit logs asynchronously and push them to Apache Kafka.
- Kafka topics (transaction.events) are partitioned strictly by user_id, ensuring in-order processing per user.
- Transactional Outbox pattern guarantees that every committed database writing has a corresponding Kafka event.
Apache Flink Stream Aggregations Engine:
- Consumes transaction events in real time.
- Calculates sliding window aggregations and updates hierarchical time buckets (Daily Weekly Monthly Yearly).
Budget & Alert Service:
- Maintains real-time budget balances in Redis.
- Checks budget threshold triggers (e.g. 80%, 100%) atomically using Redis Lua Scripts.
- Pushes alert messages to notification queues (FCM, APNs, Email) within <2 seconds.
ClickHouse OLAP Database (Read Path):
- Stores pre-aggregated columnar data for daily, weekly, monthly, and yearly summaries.
- Enables sub-10ms analytical queries across millions of rows per user.
OpenSearch (Search):
- Full-text search on merchant, description, and tags.
- Every query contains a strict user_id filter; never treated as a source of truth.
Notification Gateway:
- Fan-out to FCM, APNs, email, SMS, and in-app channels.
- Owns retry/back-off and delivery status; tolerates lag.

Service Topology

Mobile / Web Client
        │
   CDN / WAF / DDoS
        │
   API Gateway (Auth, Rate Limit, Idempotency Check)
        │
   ┌────┴────────────────────────────┐
   │                                 │
   ▼                                 ▼
Write path services            Read path services
(Identity, Accounts,           (Analytics, Reports,
 Transactions, Budgets,         Search, Notif.)
 Imports, Categorization)
   │                                 │
   ▼                                 ▼
Sharded OLTP DB                Redis + ClickHouse
 +/- ClickHouse (mirror)        + OpenSearch
   │
   ▼
Transactional Outbox
   │
   ▼
Kafka (partitioned by user_id)
   │
   ├── Flink (budgets, alerts, rollups)
   ├── Search indexer
   ├── ML categorization
   └── Notification dispatcher

4. Core Financial Model

4.1 Transaction Types

INCOME
EXPENSE
TRANSFER
REFUND
ADJUSTMENT
OPENING_BALANCE

4.2 Amount Representation

Never store monetary values as floating-point numbers. Use integer minor units plus a currency code:

sql

amount_minor = 125099
currency     = 'USD'

This represents $1,250.99. For currencies with different decimal rules, rely on per-currency metadata (e.g., JPY uses 0 decimals).

4.3 Ledger Principle

Treat every financial change as an immutable event/transaction record. Examples:

text

Expense:
  Account: Checking  -100.00
  Category: Groceries -100.00

Income:
  Account: Checking  +3,000.00
  Category: Salary    +3,000.00

Transfer:
  Checking -500.00
  Savings  +500.00   (NOT income / NOT expense)

4.4 Corrections

To preserve an audit trail, do not silently rewrite a historical transaction:

Create a correction event.
Mark the original as superseded.
Re-run affected aggregate computations.
Preserve the audit history.

5. Low-Level System Design (LLD)

Database Schema Design

A. Relational OLTP (Sharded PostgreSQL / MySQL)

Sharded by user_id using consistent hashing.

sql

-- Users Table
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    home_region VARCHAR(32) NOT NULL DEFAULT 'us-east-1',
    timezone VARCHAR(64) NOT NULL DEFAULT 'UTC',
    locale VARCHAR(16) NOT NULL DEFAULT 'en-US',
    base_currency CHAR(3) DEFAULT 'USD',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Accounts Table (Bank, Cash, Credit Card)
CREATE TABLE accounts (
    account_id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    account_name VARCHAR(100) NOT NULL,
    account_type VARCHAR(50) NOT NULL, -- CASH, BANK, CREDIT_CARD
    balance DECIMAL(15, 4) NOT NULL DEFAULT 0.0000,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    institution_id UUID,
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE INDEX idx_accounts_user ON accounts(user_id);

-- Categories Table
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id UUID NULL, -- NULL for system defaults, UUID for custom user categories
    parent_id INT NULL,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL, -- INCOME, EXPENSE
    icon_url VARCHAR(255),
    color VARCHAR(16),
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE'
);
CREATE INDEX idx_categories_user ON categories(user_id);

-- Transactions Table (Partitioned by Range on transaction_date)
CREATE TABLE transactions (
    transaction_id UUID NOT NULL,
    user_id UUID NOT NULL,
    account_id UUID NOT NULL,
    category_id INT NOT NULL,
    amount DECIMAL(15, 4) NOT NULL,
    currency CHAR(3) NOT NULL,
    transaction_type VARCHAR(20) NOT NULL, -- INCOME, EXPENSE, TRANSFER
    transaction_date TIMESTAMP WITH TIME ZONE NOT NULL,
    notes TEXT,
    idempotency_key VARCHAR(128) UNIQUE NOT NULL,
    dedup_hash VARCHAR(128), -- for bank-import dedup
    status VARCHAR(32) NOT NULL DEFAULT 'POSTED',
    version BIGINT NOT NULL DEFAULT 1,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, transaction_date, transaction_id)
) PARTITION BY RANGE (transaction_date);

CREATE INDEX idx_tx_user_date ON transactions(user_id, transaction_date DESC);
CREATE INDEX idx_tx_category ON transactions(user_id, category_id, transaction_date);
CREATE INDEX idx_tx_account ON transactions(user_id, account_id, transaction_date);

-- Transaction Splits (one tx across multiple categories)
CREATE TABLE transaction_splits (
    transaction_id UUID NOT NULL,
    user_id UUID NOT NULL,
    split_id UUID NOT NULL,
    category_id INT NOT NULL,
    amount DECIMAL(15, 4) NOT NULL,
    note TEXT,
    PRIMARY KEY (user_id, transaction_id, split_id),
    CONSTRAINT fk_split_tx FOREIGN KEY (user_id, transaction_id)
        REFERENCES transactions(user_id, transaction_id)
);

-- Budgets Table
CREATE TABLE budgets (
    budget_id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    category_id INT NULL, -- NULL implies total overall budget
    period_type VARCHAR(20) NOT NULL, -- DAILY, WEEKLY, MONTHLY, YEARLY, CUSTOM
    amount_limit DECIMAL(15, 4) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    rollover_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    start_date DATE NOT NULL,
    end_date DATE,
    alert_threshold_percent INT DEFAULT 80,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_budgets_user_period ON budgets(user_id, period_type, start_date, end_date);

-- Budget Period Status (incremental / pre-computed)
CREATE TABLE budget_period_status (
    user_id UUID NOT NULL,
    budget_id UUID NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    allocated_minor BIGINT NOT NULL,
    spent_minor BIGINT NOT NULL,
    remaining_minor BIGINT NOT NULL,
    percentage_used DECIMAL(8, 4) NOT NULL,
    generated_at TIMESTAMP WITH TIME ZONE NOT NULL,
    PRIMARY KEY (user_id, budget_id, period_start)
);

-- Daily Aggregate (read-side, fed by CDC + Flink)
CREATE TABLE daily_financial_aggregates (
    user_id UUID NOT NULL,
    aggregate_date DATE NOT NULL,
    account_id UUID,
    category_id INT,
    income_minor BIGINT NOT NULL DEFAULT 0,
    expense_minor BIGINT NOT NULL DEFAULT 0,
    transfer_in_minor BIGINT NOT NULL DEFAULT 0,
    transfer_out_minor BIGINT NOT NULL DEFAULT 0,
    transaction_count INT NOT NULL DEFAULT 0,
    generated_at TIMESTAMP WITH TIME ZONE NOT NULL,
    PRIMARY KEY (user_id, aggregate_date, account_id, category_id)
);

-- Audit Log (immutable, append-only)
CREATE TABLE audit_log (
    audit_id UUID PRIMARY KEY,
    actor_id UUID NOT NULL,
    user_id UUID NOT NULL,
    resource_type VARCHAR(64) NOT NULL,
    resource_id VARCHAR(128) NOT NULL,
    action VARCHAR(64) NOT NULL,
    diff JSONB,
    request_id VARCHAR(64),
    ip_address_hash VARCHAR(128),
    device_id_hash VARCHAR(128),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_audit_user_time ON audit_log(user_id, created_at DESC);

B. Analytical OLAP (ClickHouse Pre-Aggregated Schema)

Engine: SummingMergeTree for instant sums without full table scans.

sql

-- Daily Category Rollups Table in ClickHouse
CREATE TABLE daily_user_aggregates (
    user_id UUID,
    aggregate_date Date,
    category_id UInt32,
    currency LowCardinality(String),
    total_income Decimal(15, 4),
    total_expense Decimal(15, 4),
    transaction_count UInt32
) ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(aggregate_date)
ORDER BY (user_id, aggregate_date, category_id, currency);

-- Monthly User Rollups Table
CREATE TABLE monthly_user_aggregates (
    user_id UUID,
    year_month UInt32, -- e.g. 202608
    category_id UInt32,
    currency LowCardinality(String),
    total_income Decimal(15, 4),
    total_expense Decimal(15, 4),
    transaction_count UInt32
) ENGINE = SummingMergeTree()
PARTITION BY toYYYY(toDate(concat(toString(year_month), '01')))
ORDER BY (user_id, year_month, category_id, currency);

-- Yearly User Rollups Table
CREATE TABLE yearly_user_aggregates (
    user_id UUID,
    year UInt32,
    category_id UInt32,
    currency LowCardinality(String),
    total_income Decimal(15, 4),
    total_expense Decimal(15, 4),
    transaction_count UInt32
) ENGINE = SummingMergeTree()
PARTITION BY year
ORDER BY (user_id, year, category_id, currency);

Weekly aggregates can be computed on the fly from daily_user_aggregates (bounded row counts per user) or precomputed using SummingMergeTree keyed by ISO year_week.

6. Data Flow: Write and Read Paths

6.1 Write Path (Step-by-Step)

Client sends POST /v1/transactions with header X-Idempotency-Key: <uuid>.
API Gateway validates token, routes, and rate-limits.
Transaction Service checks Redis for the idempotency key.
- Hit → return the original response.
- Miss → enter the workflow below.
Open a single DB transaction and:
- INSERT INTO transactions (...) with idempotency_key.
- INSERT INTO outbox (...) with the serialized event payload.
- UPDATE accounts.balance (within the same transaction).
COMMIT.
Return 201 Created to the client (write durability is now guaranteed).
Outbox publisher (debounced worker) reads the outbox row, publishes to Kafka, marks the row as published.
Subscribers (Flink, search, notifications) consume and process.

6.2 Read Path (Dashboard)

ClickHouse serves pre-aggregated daily/monthly/yearly totals.
Redis caches the user's most recent dashboard with a short TTL (30s–5m).
Cache is invalidated event-driven by the outbox pipeline.

7. Core API Specifications

1. Ingest New Transaction

Endpoint: POST /v1/transactions
Headers: X-Idempotency-Key: 9b1deb4d-3b7d-4bad-9bdd-2b0d7b3dcb6d, Authorization: Bearer <jwt>

json

{
  "account_id": "e7b72f88-12ab-4c3e-821e-123456789abc",
  "category_id": 12,
  "amount": 45.5,
  "currency": "USD",
  "transaction_type": "EXPENSE",
  "transaction_date": "2026-08-08T14:30:00Z",
  "notes": "Grocery shopping at Walmart"
}

json

{
  "status": "SUCCESS",
  "data": {
    "transaction_id": "a1b2c3d4-e5f6-7a8b-9c0d-1e2f3a4b5c6d",
    "user_id": "u9876543-210a-4b3c-8d7e-6f5a4b3c2d1e",
    "amount": 45.5,
    "currency": "USD",
    "version": 1,
    "budget_status": {
      "budget_id": "b1112223-3344-5566-7788-99aabbccddeeff",
      "limit": 500.0,
      "spent": 385.5,
      "remaining": 114.5,
      "percentage_used": 77.1,
      "alert_triggered": false
    }
  }
}

2. Update Transaction (Optimistic Lock)

http

PATCH /v1/transactions/{transaction_id}
If-Match: "1"

json

{ "amount": 47.0, "notes": "Updated" }

The server must reject stale updates with 409 Conflict.

3. List Transactions (Cursor Pagination)

http

GET /v1/transactions?from=2026-08-01&to=2026-08-31&category_id=12&limit=50&cursor=eyJ0IjoxNzI...

json

{
  "items": [
    {
      "transaction_id": "a1b2c3d4-...",
      "type": "EXPENSE",
      "amount_minor": 4550,
      "currency": "USD",
      "category_id": 12,
      "occurred_at": "2026-08-08T14:30:00Z"
    }
  ],
  "next_cursor": "eyJ0IjoxNzI..."
}

4. Create Budget

http

POST /v1/budgets

json

{
  "name": "Monthly Food Budget",
  "category_id": 12,
  "period_type": "MONTHLY",
  "amount_limit": 500.0,
  "currency": "USD",
  "rollover_enabled": false,
  "start_date": "2026-08-01",
  "alert_threshold_percent": 80
}

5. Get Aggregated Analytics (Daily / Weekly / Monthly / Yearly)

Endpoint: GET /v1/analytics/summary
Query Params: timeframe=MONTHLY&start_date=2026-01-01&end_date=2026-08-08&currency=USD

json

{
  "timeframe": "MONTHLY",
  "currency": "USD",
  "summary": {
    "total_income": 32000.0,
    "total_expense": 14250.75,
    "net_savings": 17749.25,
    "savings_rate_percent": 55.46
  },
  "period_breakdown": [
    {
      "period": "2026-08",
      "income": 4000.0,
      "expense": 1850.5,
      "top_categories": [
        { "category_id": 1, "name": "Rent", "amount": 1200.0 },
        { "category_id": 12, "name": "Groceries", "amount": 385.5 }
      ]
    }
  ]
}

Standard Error Format

json

{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "The amount must be greater than zero",
    "request_id": "req-123",
    "details": []
  }
}

8. Algorithms & Core Mechanics

A. Incremental Hierarchical Aggregation Algorithm

Instead of computing sum aggregates dynamically over raw transaction tables (which degrades to query speed as transaction volume grows), we maintain Hierarchical Time Buckets updated incrementally ( amortized time).

Mathematical Aggregation Formulation

Let be a transaction where is user_id, is category_id, is amount, and is timestamp.

Daily Bucket Aggregate:

text

A_day(u, c, YYYY-MM-DD) = SUM of a_i for t in Day

Monthly Bucket Aggregate:

text

A_month(u, c, YYYY-MM) = SUM of A_day(u, c, day) for day in Month

Yearly Bucket Aggregate:

text

A_year(u, c, YYYY) = SUM of A_month(u, c, month) for month in Year

B. Daily / Weekly / Monthly / Yearly Period Calculations

All window math is anchored on the user's timezone, and transaction_date is stored in UTC with the original offset.

text

Daily:   [local_midnight, local_midnight)
Weekly:  ISO week (Mon or Sun start) OR rolling 7-day window
Monthly: calendar month OR custom cycle (e.g., payday-based)
Yearly:  calendar year OR fiscal year with user-defined start

Recommended aggregate hierarchy:

Transactions
     │
     ▼
Daily aggregates (incremental via Flink)
     │
     ├── Weekly reports (computed from daily)
     ├── Monthly aggregates (incremental via Flink)
     │       │
     │       └── Yearly reports (computed from monthly OR incremental)
     │
     └── Rolling-period reports

If a transaction is backdated, the system emits a compensating delta, mutates the affected daily aggregate, and recomputes any weekly/monthly/yearly totals that depend on it.

C. Atomic Redis Lua Script for Budget Consumption

To avoid race conditions when concurrent requests (e.g., dual-card swipes or automated subscription charges) hit the budget service simultaneously, we execute atomic updates in Redis via Lua scripts:

lua

-- Key 1: budget:user_id:category_id:month (e.g. budget:u123:cat12:2026-08)
-- Key 2: budget_limit:user_id:category_id:month
-- ARGV[1]: Transaction Amount
-- ARGV[2]: Alert Threshold Percent (e.g. 80)

local current_spent = redis.call("INCRBYFLOAT", KEYS[1], ARGV[1])
local limit = tonumber(redis.call("GET", KEYS[2]) or "0")

local percentage = 0
if limit > 0 then
    percentage = (current_spent / limit) * 100
end

local trigger_alert = false
if percentage >= tonumber(ARGV[2]) then
    -- Check if alert was already sent for this period
    local alert_key = "alert_sent:" .. KEYS[1]
    if redis.call("SET", alert_key, "1", "NX", "EX", 86400 * 30) then
        trigger_alert = true
    end
end

return {tostring(current_spent), tostring(limit), tostring(percentage), trigger_alert and "1" or "0"}

D. Budget Reconciliation (Drift Repair)

Incremental counters drift over time due to retries, bugs, and back-dated transactions. A nightly reconciliation job re-derives counts from the authoritative ledger:

text

For each budget period:
  calculated_spent = SUM(eligible ledger transactions)
  stored_spent     = budget_period_status.spent_minor

  if calculated_spent != stored_spent:
      repair stored value
      emit reconciliation event
      write audit_log entry

9. High Availability, Sharding & Scalability Strategy

Database Sharding Architecture

To support 100M active users, relational databases are sharded by user_id using Consistent Hashing:

text

Shard ID = MurmurHash3(user_id) mod N_shards

Zero Cross-Shard Transactions: Because user_id is the shard key, all accounts, transactions, and budgets for a given user reside on the exact same physical DB shard.
Replication: Each shard operates as a Multi-AZ Primary-Replica cluster (1 Primary Write Node, 2 Read Replicas).
Routing: A router layer (Vitess, sharding-spider, or custom) translates user_id to the right shard.

Event-Driven Architecture

Kafka topics are partitioned by user_id:

text

partition = hash(user_id) % N

This guarantees strict per-user ordering even under heavy retry and partition fail-over.

Outbox Pattern (Why It Matters)

Without an outbox, a database COMMIT can succeed, but the Kafka publish can fail, leaving derived views permanently inconsistent. The outbox pattern fixes this:

text

Open DB transaction
  ├── INSERT OLTP row               ──┐
  ├── INSERT INTO outbox (...)      ──┤  ← atomic
  └── UPDATE accounts.balance       ──┘
COMMIT
                                    ──► Outbox publisher (separate process)
                                          reads outbox → publishes Kafka
                                          marks row "published"

Caching Layers

Layer	What is cached	TTL	Invalidation
CDN	static assets, exports	1d–30d	content hash
API gateway	public FX rates	1–5 min	provider push
Redis	balances, budget status, dashboards	30s–5m	event-driven deltas
Read models	per-user aggregates	1–15m	tx event triggers refresh
DB	query cache	engine mgmt	plan invalidation

Stale-while-revalidate for non-critical views.
Negative caching with jitter to avoid thundering herd.
Cache-key norms:
- dashboard:{user_id}:{period}:{date}:{currency}
- budget:{user_id}:{budget_id}:{period_start}
- balance:{user_id}:{account_id}
- category-summary:{user_id}:{period_start}:{period_end}

Rate Limiting

Apply token-bucket limits at multiple levels:

Tier	Example limit
IP	60 req/min unauthenticated
User account	600 req/min
Transaction write	100/min per user
Transaction list	300/min per user
Report generation	10/hour per user
File import jobs	5 concurrent per user

Limits are adaptive: trusted internal integration tokens and back-office roles receive higher quotas.

Cell-Based Architecture

A cell is an independent deployment unit owning a subset of users, application pods, DB shards, event consumers, caches, and observability:

Limits blast radius on incidents.
Enables independent scaling and progressive releases.
Simplifies capacity planning: capacity per cell is known.

Rolling-out globally:

1% → 10% → 50% → 100% in one cell
       → 25% → 100% across all cells

Multi-Region

Home-region writes: Each user is pinned to a home region. Writes always land there. Cross-region replication is asynchronous to a "warm" standby in another region.

Read-after-write is satisfied by routing reads to the same home region.
Cross-region reads (analytics, search) tolerate eventual consistency.
Region failure: drain traffic to the failover region, promote the user's shard, replay any pending outbox events on the new primary.

10. Idempotency & Fault Tolerance

Idempotency Key (Client-Side)

Clients generate a UUID for every transaction creation attempt and send it in the header X-Idempotency-Key.

text

SET idempotency:<key> <tx_id> NX EX 86400

If the key exists, the service returns the cached transaction response without re-executing writes. If the same key is reused with a different payload, reject with 409 Conflict.

Bank-Import Deduplication

Imported transactions use a fingerprint:

text

dedup_hash = H(
  institution_id,
  external_account_id,
  external_transaction_id,
  posted_date,
  amount_minor,
  currency,
  merchant_normalized
)

This makes duplicate-detection independent of merchant name spelling.

11. Security & Privacy

Authentication

OAuth 2.0 / OpenID Connect with PKCE.
Short-lived access tokens (≤ 15 min) + rotating refresh tokens.
MFA (TOTP, WebAuthn/passkeys).
Risk-based step-up auth for sensitive actions.

Authorization

Every request must verify:

text

authenticated_user_id == requested_resource.user_id

Object-level authorization for every transaction, budget, and account.
OAuth scopes for third-party integrations.
Household / shared wallet permissions where applicable.

Encryption

TLS 1.3 in transit.
AES-256 (envelope encryption) at rest, with per-user DEKs wrapped by a KEK in KMS/HSM.
Field-level encryption for sensitive PII (e.g., bank tokens).
Mobile local DB encrypted (SQLCipher / Realm encryption).

Secrets

All secrets in a vault or cloud secret manager.
Short-lived, rotated credentials.
mTLS for service-to-service traffic (SPIFFE/SVID).

Audit Logging

Every writing records an immutable audit row:

text

actor_id, user_id, resource_type, resource_id, action,
diff, request_id, ip_address_hash, device_id_hash, created_at

Compliance

GDPR right-to-erasure (crypto-shredding of DEK).
Data export.
Data residency by region.
SOC 2 / ISO 27001 controls.
PCI-DSS-style controls for any bank credentials (tokenized; never stored).

Threat Model Highlights

Threat	Mitigation
Account takeover	MFA, passkeys, login throttling, risk ML
Credential stuffing	Bot mitigation, rate limit, IP reputation
IDOR / cross-user data leaks	Per-request object-level checks
Duplicate transaction creation	Idempotency keys
Malicious file uploads	AV scanning, MIME enforcement, sandboxing
Bank-token exfiltration	Tokenization + KMS, no plaintext secrets
Insider access	JIT privilege, dual-control, audit trails
DDoS	WAF, CDN, scrubbers, adaptive rate limits

12. Mobile Offline Sync

Mobile apps must work offline (subways, flights, rural connections).

text

1. LOCAL DB: SQLite / Realm / Room, encrypted at rest.
2. MUTATION QUEUE: client idempotency_key + created_at + payload.
3. SYNC: when online, push queued mutations in order.
4. SERVER: dedup by idempotency_key; returns latest version.
5. CONFLICT: server rejects with 409 and versions; client surfaces UI.

Conflict policy: server is authoritative for monetary totals. UI never auto-merges amounts.

Optimistic UI: clients update locally immediately, then reconcile on server ack.

13. Multi-Currency

Always store original values and capture the FX metadata:

json

{
  "amount_minor": 10000,
  "currency": "EUR",
  "converted_amount_minor": 10850,
  "converted_currency": "USD",
  "fx_rate": "1.0850",
  "fx_rate_source": "provider-a",
  "fx_rate_time": "2026-08-08T13:00:00Z"
}

Supported FX policies:

Rate at transaction time (default).
Rate at posting time.
Current rate (live view).
User-entered manual rate.

Reports explicitly state which policy is in use.

14. Observability

Metrics

Request rate, error rate, p50/p95/p99 latency.
DB CPU, connections, lock-wait time.
Cache hit ratio.
Kafka consumer lag and event-processing delay.
Aggregate freshness lag.
Reconciliation mismatch count.
Unauthorized access attempts.
Notification delivery success / failure.

Logs (Structured, PII-Redacted)

json

{
  "timestamp": "2026-08-08T23:30:00Z",
  "level": "INFO",
  "service": "transaction-service",
  "request_id": "req-123",
  "user_id_hash": "hash-123",
  "transaction_id": "txn-123",
  "operation": "create_transaction",
  "duration_ms": 42
}

Never log raw tokens, bank credentials, or full account numbers.

Distributed Tracing

Propagate trace_id, span_id, request_id, user_region, cell_id across:

API Gateway → Transaction Service → DB → Outbox → Kafka → Aggregator

SLOs

text

99.99% successful transaction writes
99.99% successful authenticated reads
p95 transaction creation latency < 250 ms
p95 dashboard latency < 500 ms
99% of aggregate events processed within 30 seconds
99.9% of search updates available within 60 seconds
100% of committed transactions recoverable
0 unauthorized cross-user data accesses

Burn-rate alerts at 1h / 6h / 24h / 72h windows.

15. Disaster Recovery

Tier	Example Data	RPO	RTO
0	Financial ledger	5 minutes	30 min
1	Accounts, budgets	5 minutes	1 hour
2	Search, analytics	Hours	4 hours

Continuous WAL shipping + daily snapshots.
35-day backup retention.
Cross-region backup replication.
Quarterly restore tests.
Region-failure simulations (GameDay).
Event-replay tests.

16. Failure Scenarios

Scenario	Expected behavior
DB temporarily unavailable	Return retryable error; client retries with same idempotency key; no duplicate rows.
Kafka temporarily unavailable	DB commit still succeeds; outbox drains once Kafka recovers; dashboard may lag briefly.
Redis unavailable	Read from materialized views / DB; never lose authoritative financial data.
Search index delayed	Transaction API still correct; full-text search lags; rebuildable from ledger.
Incorrect aggregate	Recalculate from ledger; repair affected rows; record correction; alert on threshold.
Wrong home region for a user	Sticky session or token carries home region; gateway routes accordingly.

17. Testing Strategy

Level	What is tested
Unit	Currency arithmetic, period boundaries, timezones, rollover, idempotency
Integration	DB tx, outbox publishing, Kafka consumers, cache invalidation, search
Contract	API schemas, event schemas, backward compatibility, mobile client compat
Load	Dashboard traffic, transaction bursts, month-end reports, cache failure
Chaos	Pod kills, region partition, network drops, Kafka lag
Security	AuthN/AuthZ, IDOR, dependency scanning, penetration testing, file uploads
Reconciliation	Ledger vs Redis vs ClickHouse vs OpenSearch drift simulation

18. MVP & Migration Path

Stage 1: Modular Monolith

Web/Mobile Client
  → API Gateway
    → Finance Application (Identity, Users, Accounts, Tx, Categories, Budgets, Reports)
      → PostgreSQL
      → Redis
      → Background Job Queue

Features: auth, accounts, manual income/expense, categories, budgets, daily/weekly/monthly/yearly reports, CSV export, basic notifications, audit history, mobile sync.

Stage 2: Extract High-Load Workers

Extract: reporting, imports, notifications, search — into separate services.

Stage 3: Event-Driven Read Models

Add Kafka, outbox publisher, aggregation consumers, budget consumers, search consumers.

Stage 4: Global Scale

User-based sharding.
Multi-region cells.
Regional routing.
OLAP warehouse.
Global observability.
Automated reconciliation.

This staged approach keeps early operational complexity low while preserving a clear migration path to 100M+ DAU.

19. End-to-End Example: Adding a $50 Grocery Expense

text

User enters $50 grocery expense
        │
        ▼
Mobile app POST /v1/transactions (X-Idempotency-Key: <uuid>)
        │
        ▼
API Gateway validates JWT + rate limit
        │
        ▼
Transaction Service:
   • validate account ownership
   • validate category ownership
   • check idempotency in Redis
        │
        ▼
Open DB transaction:
   INSERT INTO transactions (...)
   INSERT INTO outbox (...)
   UPDATE accounts.balance
COMMIT
        │
        ▼
Return 201 to client
        │
        ▼
Outbox publisher → Kafka topic tx.events (partitioned by user_id)
        │
        ├── Budget consumer: incr food budget counter (Lua)
        ├── Daily aggregator: increment daily food rollup
        ├── Search consumer: index new transaction
        ├── Notification consumer: evaluate threshold rules
        └── Analytics consumer: write to ClickHouse

After the writing, the user's monthly food budget might look like:

text

Allocated: $600.00
Spent:     $372.00  + $50.00  = $422.00
Remaining: $228.00
Used:      70.33%

If the next transaction crosses the 80% threshold, the notification dispatcher fires:

text

"You have used 82% of your monthly food budget."

If the budget is exceeded:

text

Allocated: $600.00
Spent:     $650.00
Remaining: -$50.00
Used:      108.33%
State:     OVER_BUDGET

20. Recommended Technology Stack

Layer	Options
Mobile	Swift, Kotlin, Flutter, React Native
Web	React, Vue, Angular
API (services)	Java/Kotlin, Go, TypeScript, Rust
Internal RPC	gRPC + Protobuf
External API	REST + JSON
Primary DB	PostgreSQL/Vitess, Spanner, CockroachDB, YugabyteDB
Cache	Redis / KeyDB / DragonflyDB
Event bus	Apache Kafka, Pulsar, Pub/Sub, Kinesis
Stream processor	Apache Flink, Kafka Streams, Materialize (read-your-writes)
OLAP	ClickHouse, BigQuery, Snowflake, DuckDB (small scale)
Search	OpenSearch / Elasticsearch
Object storage	S3 / GCS / Azure Blob
Workflow engine	Temporal, Cadence, Argo Workflows
Container platform	Kubernetes (EKS / GKE / AKS / self-hosted)
Observability	OpenTelemetry, Prometheus, Grafana, Loki, Tempo, Datadog
Secrets	Vault / AWS Secrets Manager / KMS
Encryption	KMS / HSM (envelope encryption, per-tenant DEKs)

Technology choices should be evaluated against:

Operational maturity and team expertise.
Regional availability and data residency.
Strong consistency support.
Cost and vendor lock-in.
Compliance posture.

21. Summary & Technical Trade-offs

Design Axis	Chosen Strategy	Alternative	Trade-off Rationale
Write/Read Split	CQRS + CDC (Debezium + Kafka)	Single Dual-Write in API	Prevents DB write locks, decouples analytics from core transaction processing, guarantees event consistency.
Analytical Store	ClickHouse (`SummingMergeTree`)	PostgreSQL Aggregations	ClickHouse provides 100x faster rollup query speeds over hundreds of millions of historical rows.
Budget Counters	Redis + Lua Scripting	RDBMS `UPDATE ... WHERE`	Lua scripts guarantee atomic, lock-free counter increments under extreme concurrency.
Sharding Key	`user_id`	`transaction_id` or Date	`user_id` ensures 100% of single-user transactions execute on a single node without distributed cross-node joins.
Money Representation	DECIMAL(15,4) + currency	FLOAT / DOUBLE	Avoids floating-point errors; required for financial integrity.
Idempotency	Redis + DB unique key	None / client-only	Eliminates duplicate postings even on client retries, partial failures, and multi-device sync.
Event Reliability	Transactional Outbox	App-level dual-write	Eliminates the "DB committed but Kafka didn't" failure class.
Multi-Region Writes	Home-region writes + async replication	Active-active writes	Strong consistency for financial records with simpler conflict resolution.
Mobile Offline	Optimistic UI + queued mutations	Online-only	Better UX; safe under poor network; conflict resolution is explicit.
Budget Corrections	Compensating deltas + reconciliation	Silent rewrites	Preserves audit history; rebuildable state.
Search	OpenSearch user-scoped	Postgres full-text	Sub-second search across millions of users' worth of transactions.
Deployment	Cell-based, multi-region	Single monolith	Limits blast radius, enables progressive rolls, simplifies capacity planning.

22. Glossary

DAU/MAU — Daily/Monthly Active Users.
CQRS — Command Query Responsibility Segregation.
CDC — Change Data Capture.
OLTP — Online Transaction Processing.
OLAP — Online Analytical Processing.
Outbox — Pattern that ties DB commits to event publishing.
Idempotency Key — Client-supplied unique operation ID.
Cell — Independent deployment unit owning a user subset.
FX — Foreign Exchange.
RPO/RTO — Recovery Point / Time Objective.
Multi-AZ — Multiple Availability Zones.
Ledger — Immutable record of financial events.
Rollover — Budget rule that carries unused allowance to the next period.
Compensating Delta — Event that reverses a previous aggregated effect.
Reconciliation — Process that aligns derived views with the authoritative ledger.
Backdated Transaction — Posting with transaction_date earlier than the time of submission.
Object-Level Authorization — Per-resource ownership check on every request.
Crypto-Shredding — Deleting a tenant's key to make their data permanently unreadable.

High-Scale Personal Income, Expense & Budget Tracking System (100M+ DAU) ​

1. System Requirements & Scope ​

Functional Requirements ​

Non-Functional Requirements ​

2. Capacity Estimation & Math (100M DAU Scale) ​

Traffic Estimation ​

Storage Estimation ​

Memory / Cache Estimation ​

Latency Targets ​

3. High-Level Architecture (HLD) ​

Architectural Component Breakdown ​

Service Topology ​

4. Core Financial Model ​

4.1 Transaction Types ​

4.2 Amount Representation ​

4.3 Ledger Principle ​

4.4 Corrections ​

5. Low-Level System Design (LLD) ​

Database Schema Design ​

A. Relational OLTP (Sharded PostgreSQL / MySQL) ​

B. Analytical OLAP (ClickHouse Pre-Aggregated Schema) ​

6. Data Flow: Write and Read Paths ​

6.1 Write Path (Step-by-Step) ​

6.2 Read Path (Dashboard) ​

7. Core API Specifications ​

1. Ingest New Transaction ​

2. Update Transaction (Optimistic Lock) ​

3. List Transactions (Cursor Pagination) ​

4. Create Budget ​

5. Get Aggregated Analytics (Daily / Weekly / Monthly / Yearly) ​

Standard Error Format ​

8. Algorithms & Core Mechanics ​

A. Incremental Hierarchical Aggregation Algorithm ​

Mathematical Aggregation Formulation ​

B. Daily / Weekly / Monthly / Yearly Period Calculations ​

C. Atomic Redis Lua Script for Budget Consumption ​

D. Budget Reconciliation (Drift Repair) ​

9. High Availability, Sharding & Scalability Strategy ​

Database Sharding Architecture ​

Event-Driven Architecture ​

Outbox Pattern (Why It Matters) ​

Caching Layers ​

Rate Limiting ​

Cell-Based Architecture ​

Multi-Region ​

10. Idempotency & Fault Tolerance ​

Idempotency Key (Client-Side) ​

Bank-Import Deduplication ​

11. Security & Privacy ​

Authentication ​

Authorization ​

Encryption ​

Secrets ​

Audit Logging ​

Compliance ​

Threat Model Highlights ​

12. Mobile Offline Sync ​

13. Multi-Currency ​

14. Observability ​

Metrics ​

Logs (Structured, PII-Redacted) ​

Distributed Tracing ​

SLOs ​

15. Disaster Recovery ​

16. Failure Scenarios ​

17. Testing Strategy ​

18. MVP & Migration Path ​

Stage 1: Modular Monolith ​

Stage 2: Extract High-Load Workers ​

Stage 3: Event-Driven Read Models ​

Stage 4: Global Scale ​

19. End-to-End Example: Adding a $50 Grocery Expense ​

20. Recommended Technology Stack ​

21. Summary & Technical Trade-offs ​

22. Glossary ​

High-Scale Personal Income, Expense & Budget Tracking System (100M+ DAU)

1. System Requirements & Scope

Functional Requirements

Non-Functional Requirements

2. Capacity Estimation & Math (100M DAU Scale)

Traffic Estimation

Storage Estimation

Memory / Cache Estimation

Latency Targets

3. High-Level Architecture (HLD)

Architectural Component Breakdown

Service Topology

4. Core Financial Model

4.1 Transaction Types

4.2 Amount Representation

4.3 Ledger Principle

4.4 Corrections

5. Low-Level System Design (LLD)

Database Schema Design

A. Relational OLTP (Sharded PostgreSQL / MySQL)

B. Analytical OLAP (ClickHouse Pre-Aggregated Schema)

6. Data Flow: Write and Read Paths

6.1 Write Path (Step-by-Step)

6.2 Read Path (Dashboard)

7. Core API Specifications

1. Ingest New Transaction

2. Update Transaction (Optimistic Lock)

3. List Transactions (Cursor Pagination)

4. Create Budget

5. Get Aggregated Analytics (Daily / Weekly / Monthly / Yearly)

Standard Error Format

8. Algorithms & Core Mechanics

A. Incremental Hierarchical Aggregation Algorithm

Mathematical Aggregation Formulation

B. Daily / Weekly / Monthly / Yearly Period Calculations

C. Atomic Redis Lua Script for Budget Consumption

D. Budget Reconciliation (Drift Repair)

9. High Availability, Sharding & Scalability Strategy

Database Sharding Architecture

Event-Driven Architecture

Outbox Pattern (Why It Matters)

Caching Layers

Rate Limiting

Cell-Based Architecture

Multi-Region

10. Idempotency & Fault Tolerance

Idempotency Key (Client-Side)

Bank-Import Deduplication

11. Security & Privacy

Authentication

Authorization

Encryption

Secrets

Audit Logging

Compliance

Threat Model Highlights

12. Mobile Offline Sync

13. Multi-Currency

14. Observability

Metrics

Logs (Structured, PII-Redacted)

Distributed Tracing

SLOs

15. Disaster Recovery

16. Failure Scenarios

17. Testing Strategy

18. MVP & Migration Path

Stage 1: Modular Monolith

Stage 2: Extract High-Load Workers

Stage 3: Event-Driven Read Models

Stage 4: Global Scale

19. End-to-End Example: Adding a $50 Grocery Expense

20. Recommended Technology Stack

21. Summary & Technical Trade-offs

22. Glossary