Requirements, estimates and the data model

The problem

The primer's Design Mint.com is a personal-finance service. It connects to users' bank and card accounts, pulls in their transactions, categorizes them, and tracks spending against budgets.

Use cases (from the primer)

• A user connects to a financial account.
• The service extracts transactions from the account. It updates daily and categorizes transactions; users may override a category manually, and there is no automatic re-categorization. It also analyzes monthly spending by category.
• The service recommends a budget. Users may set one manually, and it sends notifications when spending approaches or exceeds the budget.
• The service has high availability.

Assumptions (from the primer)

• Traffic is not evenly distributed.
• Automatic daily updates apply only to users active in the past 30 days.
• Adding or removing financial accounts is rare.
• Budget notifications don't need to be instant.
• 10 million users with 10 budget categories each (100 million budget items), and 50,000 sellers that determine categories.
• 30 million financial accounts.
• 5 billion transactions and 500 million read requests per month.
• A 10:1 write-to-read ratio: "users make transactions daily, but few visit the site daily".

That last point is the opposite of most of this course. Here writes dominate.

---

Back-of-the-envelope estimates

Size per transaction (primer): user_id 8 bytes, created_at 5 bytes, seller 32 bytes, amount 5 bytes, about 50 bytes in all.

With 400 requests per second ≈ 1 billion a month:

Both are higher at peak. The primer adds that 2,000 writes per second "might be tough for a single SQL Write Master-Slave". It points to federation, sharding, denormalization and SQL tuning, or moving some data to NoSQL.

---

Data model (from the primer)

accounts holds each linked account: id, created_at, last_update, account_url, account_login, a hashed account credential and user_id, with indexes on id, user_id and created_at.

transactions holds id, created_at, seller, amount (decimal) and user_id, with the same indexes.

monthly_spending holds id, month_year, category, amount and user_id, indexed on id and user_id.

Two refinements that later lessons rely on:

• Store money as an exact type: a decimal, as the primer does, or integer cents. Never use binary floating point.
• Add the provider's transaction id and the account to transactions, with a unique constraint on that pair. That is what makes ingestion idempotent (lesson 2).

---

The flow

• The Accounts API stores a newly linked account.
• Extraction is slow, so it runs asynchronously: the API puts a job on a queue such as Amazon SQS or RabbitMQ. A job is created when the user first links an account, when they refresh manually, and daily for users active in the past 30 days.
• The Transaction Extraction Service pulls jobs from the queue and fetches the account's transactions, storing the raw results as log files in an object store.
• It then uses the Category Service to categorize each transaction, and the Budget Service to update monthly spending by category. The Budget Service notifies users who are nearing or over budget.
• Finally it updates the transactions and monthly_spending tables and notifies the user, again through a queue.