Transaction history in LLD - System Design Exercise

Design: Transaction History System

Design covers backend storage, API design, and data flow for transaction history. Out of scope: payment processing, user authentication system implementation, frontend UI design.

Functional Requirements

FR1: Store transaction records for users including amount, date, type, and status

FR2: Allow users to query their transaction history with filters (date range, type)

FR3: Support pagination for large transaction lists

FR4: Ensure data consistency and durability

FR5: Provide fast read access for recent transactions

FR6: Allow export of transaction history in common formats (CSV, PDF)

Non-Functional Requirements

NFR1: Handle 1 million users with average 100 transactions each

NFR2: Support 500 concurrent read queries with p99 latency under 200ms

NFR3: Ensure 99.9% uptime

NFR4: Data retention for at least 5 years

NFR5: Secure access with user authentication and authorization

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

❓ Question 6

Key Components

API server for handling requests

Database for storing transaction records

Cache layer for recent/frequent queries

Authentication and authorization middleware

Export service for generating CSV/PDF

Pagination and filtering logic

Design Patterns

Pagination with limit-offset or cursor

Read-through caching for recent transactions

Event sourcing if transaction updates are frequent

Batch export generation

Data partitioning by user or time

Reference Architecture

  +-------------+       +-------------+       +-------------+
  |   Client    | <---> | API Server  | <---> | Auth Server |
  +-------------+       +-------------+       +-------------+
                              |
                              v
                       +--------------+
                       | Cache Layer  |
                       +--------------+
                              |
                              v
                       +--------------+
                       |  Database    |
                       +--------------+
                              |
                              v
                       +--------------+
                       | Export Svc   |
                       +--------------+

Components

API Server

Node.js/Express or Python/Flask

Handles client requests, applies filters, pagination, and returns transaction data

Authentication Server

OAuth2 or JWT based service

Validates user identity and permissions before allowing access

Cache Layer

Redis or Memcached

Stores recent or frequently accessed transaction data to reduce DB load and improve latency

Database

PostgreSQL or MySQL

Stores all transaction records with indexes on user_id, timestamp, and type for efficient querying

Export Service

Background worker with libraries for CSV/PDF generation

Generates downloadable transaction history files on demand

Request Flow

1. Client sends authenticated request to API Server to fetch transaction history with filters and pagination parameters.

2. API Server verifies user token with Authentication Server.

3. API Server checks Cache Layer for requested transaction data.

4. If cache miss, API Server queries Database with filters and pagination.

5. Database returns transaction records to API Server.

6. API Server stores recent query results in Cache Layer for future requests.

7. API Server returns transaction data to Client.

8. For export requests, API Server sends job to Export Service.

9. Export Service generates file and provides download link to Client.

Database Schema

Entities: - User(user_id PK, name, email) - Transaction(transaction_id PK, user_id FK, amount, type, status, timestamp, description) Relationships: - One-to-many from User to Transaction (one user has many transactions) Indexes: - Index on Transaction(user_id, timestamp) for fast user history queries - Index on Transaction(type) for filtering by transaction type

Scaling Discussion

Bottlenecks

Database query latency with large transaction volumes

Cache eviction and stale data issues

Export service handling large file generation

Authentication server load under high concurrency

Solutions

Partition database by user_id or time ranges to distribute load

Use TTL and cache invalidation strategies to keep cache fresh

Implement asynchronous export jobs with queueing and chunked file generation

Scale authentication server horizontally and use token caching

Interview Tips

Time: Spend 10 minutes clarifying requirements and constraints, 20 minutes designing architecture and data flow, 10 minutes discussing scaling and trade-offs, 5 minutes summarizing.

Clarify user needs and query patterns before design

Explain choice of database and indexing for efficient queries

Discuss caching strategy to improve read latency

Describe how pagination and filtering are implemented

Address security with authentication and authorization

Plan for scaling bottlenecks and asynchronous export handling