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

Practice

1. What is the main purpose of a transaction history in a system?

easy

A. To record all important actions with details for tracking

B. To speed up the system by caching data

C. To delete old data automatically

D. To encrypt user passwords

Solution

1. Step 1: Understand the role of transaction history

   Transaction history stores records of actions with details like timestamps and IDs.

2. Step 2: Identify the correct purpose

   This helps users and systems track past events clearly and reliably.

3. Final Answer:

   To record all important actions with details for tracking -> Option A

4. Quick Check:

   Transaction history purpose = record actions [OK]

Hint: Transaction history = record actions with details [OK]

Common Mistakes:

• Confusing transaction history with caching
• Thinking it deletes data automatically
• Mixing it with security features like encryption

2. Which of the following is the correct way to uniquely identify each transaction in a history system?

easy

A. Using a timestamp only

B. Using a unique transaction ID

C. Using the user's name

D. Using the transaction amount

Solution

1. Step 1: Identify unique identifiers in transaction history

   Unique transaction IDs ensure each record is distinct and traceable.

2. Step 2: Compare options

   Timestamps alone can repeat; user names and amounts are not unique identifiers.

3. Final Answer:

   Using a unique transaction ID -> Option B

4. Quick Check:

   Unique ID = unique transaction record [OK]

Hint: Unique transaction ID ensures distinct records [OK]

Common Mistakes:

• Assuming timestamp alone is unique
• Using user name as unique key
• Using transaction amount as identifier

3. Given this simplified transaction record list:

transactions = [
  {"id": "t1", "time": "2024-01-01T10:00:00Z"},
  {"id": "t2", "time": "2024-01-01T09:00:00Z"},
  {"id": "t3", "time": "2024-01-01T11:00:00Z"}
]

What is the correct order of transaction IDs if sorted by time ascending?

medium

A. ["t1", "t2", "t3"]

B. ["t2", "t3", "t1"]

C. ["t3", "t1", "t2"]

D. ["t2", "t1", "t3"]

Solution

1. Step 1: Analyze timestamps for each transaction

   t2 = 09:00, t1 = 10:00, t3 = 11:00 in UTC time.

2. Step 2: Sort transactions by ascending time

   Order is t2 (earliest), then t1, then t3 (latest).

3. Final Answer:

   ["t2", "t1", "t3"] -> Option D

4. Quick Check:

   Sorted by time ascending = [t2, t1, t3] [OK]

Hint: Sort by timestamp ascending for correct order [OK]

Common Mistakes:

• Sorting by ID instead of time
• Confusing ascending with descending order
• Ignoring timestamp format

4. You have this code snippet to add a transaction record:

def add_transaction(history, transaction):
    if transaction['id'] not in [t['id'] for t in history]:
        history.append(transaction)
    else:
        print("Duplicate transaction")

history = [{"id": "t1"}]
add_transaction(history, {"id": "t1"})

What is the output when running this code?

medium

A. Duplicate transaction

B. KeyError exception

C. No output, transaction added

D. TypeError exception

Solution

1. Step 1: Check if transaction ID exists in history

   The code checks if 't1' is already in the list of IDs in history.

2. Step 2: Since 't1' exists, print duplicate message

   The else branch runs and prints "Duplicate transaction".

3. Final Answer:

   Duplicate transaction -> Option A

4. Quick Check:

   Duplicate ID detected = print message [OK]

Hint: Check for existing ID before adding to avoid duplicates [OK]

Common Mistakes:

• Assuming transaction is added anyway
• Expecting an exception instead of print
• Confusing list comprehension syntax

5. You want to design a scalable transaction history system for millions of users. Which approach best ensures fast retrieval of a user's transactions sorted by time?

hard

A. Store transactions in separate files per day without indexing

B. Store all transactions in one big list and scan it every time

C. Use a database with an index on user ID and timestamp

D. Keep transactions only in memory without persistence

Solution

1. Step 1: Consider scalability and retrieval speed

   Scanning one big list or files without index is slow for millions of users.

2. Step 2: Use database indexing on user ID and timestamp

   This allows fast queries to get transactions per user sorted by time efficiently.

3. Step 3: Avoid in-memory only storage for persistence and scale

   Memory-only storage risks data loss and limits scale.

4. Final Answer:

   Use a database with an index on user ID and timestamp -> Option C

5. Quick Check:

   Indexing = fast retrieval at scale [OK]

Hint: Index on user ID and timestamp for fast queries [OK]

Common Mistakes:

• Scanning large lists for each query
• Ignoring indexing benefits
• Relying on memory-only storage