Multi-Tenant Ledger Schema

HardSQL · PostgreSQL

Problem

How would you design a database schema for Mercury that supports a multi-tenant ledger system where startups can track internal transactions?

Use the provided relational model to produce a tenant-isolated account summary for posted transactions during January 2025. Include accounts with no qualifying activity.

Output

One row per Mercury account.
Columns: startup_name, account_id, account_name, balance_cents, posted_transaction_count, latest_posted_at, and balance_rank.
Include only activity from the account's startup, exclude non-posted or out-of-range transactions, and treat missing activity as zero.
Order by startup, balance rank ascending, then account ID ascending.

Schema

startups

Column	Type	Description
idPK	INT	Mercury startup tenant identifier
startup_name	VARCHAR(100)	Startup legal or display name

ledger_accounts

Column	Type	Description
idPK	INT	Ledger account identifier
startup_id	INT	Owning startup tenant
account_name	VARCHAR(100)	Human-readable account name
account_type	VARCHAR(30)	Account classification

ledger_transactions

Column	Type	Description
idPK	INT	Ledger transaction identifier
startup_id	INT	Owning startup tenant
occurred_at	DATE	Transaction business date
description	TEXT	Optional transaction description
status	VARCHAR(20)	Transaction lifecycle status

ledger_entries

Column	Type	Description
idPK	INT	Ledger entry identifier
transaction_id	INT	Parent ledger transaction
account_id	INT	Account affected by the entry
amount_cents	BIGINT	Signed amount in the smallest currency unit

Tablesstartupsledger_accountsledger_transactionsledger_entries