Design Salesforce (Multi-Tenant CRM)

1Clarifying the requirements#

Pin down what "CRM" and "multi-tenant" actually mean before drawing a single box. "Salesforce" covers a product the size of a continent, and scope decisions here change the architecture. Here is how I would open:

Candidate: When you say Salesforce, are we focused on the core record platform, Accounts, Contacts, Leads, Opportunities, plus the ability for customers to define their own objects and fields? Or do I also own analytics, workflow automation, email send?

Interviewer: Focus on the record platform and the custom-object capability. Treat reporting and workflow as things we feed, not build.

Candidate: Key question: do tenants get to define their own schema at runtime? A customer admin adds a field called "Preferred Yacht Size" to Contact and it exists five seconds later?

Interviewer: Yes. That self-service schema is the product. No deploys.

Candidate: That one answer changes everything, so let me lock it in. That rules out static per-tenant tables and rules out any design that needs a migration to add a field. Good. Now isolation. Is a hard guarantee that Tenant A can never read Tenant B's row a hard requirement, even under a bug in application code?

Interviewer: Hard requirement. Assume we will be audited.

Candidate: Last one. Do we need strong consistency on writes, read-your-writes at least? A sales rep edits an Opportunity and immediately refreshes.

Interviewer: Read-your-writes, yes. Cross-record transactions within a tenant, yes. Global consistency across tenants, no, there is no such concept.

What those answers just told me:

• The interview is about runtime schema and audited isolation, not CRUD.
• "Self-service schema is the product" rules out per-tenant tables and anything that needs a migration to add a field.
• "Audited, even under an app bug" means one layer of isolation is not enough. I will need defense in the database itself.
• "Single-tenant transactions, no cross-tenant" is the interviewer handing me permission to shard by tenant and never do two-phase commit.

Functional requirements

• CRUD on standard objects (Account, Contact, Lead, Opportunity) and tenant-defined custom objects.
• Runtime schema changes: add or remove custom fields and objects per tenant, no migration downtime.
• Query, filter, and sort records within a tenant (list views, basic reports).
• Relationships (lookups) between objects, with referential rules.
• Field-level and record-level permissions per tenant.

Non-functional requirements

• Hard tenant isolation. A query without a tenant filter must be impossible to express.
• Read-your-writes consistency and single-tenant transactions.
• Noisy-neighbor protection: one tenant cannot starve others.
• Per-tenant governor limits (API calls, query rows, storage).
• p99 record read under 100 ms, list query under 400 ms.
• 99.95% availability.

2Back-of-the-envelope estimation#

Get the scale in your head, because it decides the architecture. One number drives everything, and I will flag it when I reach it.

Tenants and users.

• 50,000 orgs. Average 200 active users each, so 10M total users, maybe 2M daily active.

Records (the scary number lives here).

• Average org has 5M records (contacts, activities, history rows add up fast). Big orgs have 500M+.
• Total rows across the platform: 50,000 x 5M = 250 billion rows.

Reads vs writes.

• CRM is read-heavy, roughly 20:1. Say 2M DAU each doing 300 reads and 15 writes a day.
• Reads: 2M x 300 = 600M/day = ~7,000 QPS average, peak ~35,000 QPS (business-hours bunching, 5x).
• Writes: 2M x 15 = 30M/day = ~350 QPS average, peak ~1,800 QPS.

Storage.

• 250B rows, average row plus indexes ~2 KB, = 500 TB of structured data, before file attachments.
• Call it 1 PB with attachments and history. Growth ~30% a year.

Bandwidth.

• Reads dominate: 35,000 QPS x ~4 KB payload = ~140 MB/s egress at peak.

The number that bites.

• 35,000 read QPS is not scary. A sharded Postgres fleet eats that for breakfast.
• The figure that actually drives the architecture is 250 billion rows across 50,000 tenants with runtime-custom schema per tenant.

Sit with the two dead ends that number creates:

• You cannot give every tenant its own table set. 50,000 tenants x ~30 tables each is 1.5M tables. No relational engine keeps a healthy query planner or a sane backup story with a catalog that size.
• You cannot let tenants run raw DDL on a shared cluster. One ALTER TABLE that locks a table everyone shares is an outage for the whole pod.

That tension (millions of tables on one side, no DDL on the other) is exactly what forces the metadata-driven design in section 6.

3Core APIs#

Everything is scoped by tenant, but the tenant never comes from the request body. It comes from the authenticated session, resolved server-side.

• That is the first isolation rule, and I say it out loud: a client that can name its own tenant is a client that can read someone else's data.

GET /api/v1/objects/{objectName}/{recordId}
Authorization: Bearer <session-token>

POST /api/v1/objects/{objectName}
{ "fields": { "Name": "Acme Corp", "Custom_Yacht_Size__c": "Large" } }

PATCH /api/v1/objects/{objectName}/{recordId}
{ "fields": { "Stage__c": "Closed Won" }, "ifVersion": 7 }

POST /api/v1/query
{ "object": "Opportunity", "where": "Stage = 'Open' AND Amount > 10000",
  "orderBy": "Amount desc", "limit": 200 }

POST /api/v1/metadata/objects/{objectName}/fields
{ "name": "Preferred_Yacht_Size__c", "type": "picklist",
  "values": ["Small","Medium","Large"] }

Two details worth defending:

• ifVersion on PATCH is optimistic concurrency for a record. It gives read-your-writes safety without pessimistic locks: the write succeeds only if nobody moved the record out from under us, and a conflict comes back as a clean 409 instead of a silent lost update.
• The metadata endpoint is a first-class API, not an admin backdoor. Creating a field is a normal authenticated write, because self-service schema is the product.

4High-level design#

The spine is standard, so draw it fast. All the interesting choices are about how one shared table holds 50,000 schemas and how a tenant stays boxed into its own shard.

• Clients hit an edge gateway that terminates TLS, authenticates the session, and resolves the tenant ID.
• A tenant context object rides along with every call. No code path below the gateway may construct a query without it.
• A stateless platform engine speaks to a metadata service (what fields a tenant's objects have) and a data service (the actual rows).

Read path.

• Gateway authenticates and resolves the tenant.
• Engine checks Redis for hot records and the tenant's metadata.
• On a miss it hits the tenant's sharded relational store.

Write path.

• Gateway, engine, validate the fields against metadata.
• Open a transaction on the tenant's shard, write the row, bump the version.
• Publish a change event to the log for search indexing and downstream consumers.

The sharding call, stated early and defended later.

• I shard by tenant ID. Each tenant lives entirely on one physical shard (a "pod").
• Every transaction is single-shard, every query is naturally tenant-scoped at the physical layer, and I never do cross-shard joins for normal CRM work.
• Huge tenants that outgrow a pod get their own dedicated pod.
• That single decision is what lets me use plain local database transactions instead of two-phase commit. It is the first thing I would defend if pushed.

5Data model#

Use a small number of shared physical tables and let metadata give them meaning. Here is the logical model so the physical table shape in the deep dive is not a surprise.

tenant

object_def (metadata)

field_def (metadata)

record (the one big physical data table)

Read field_def and record together and the trick jumps out.

• A logical field like Preferred_Yacht_Size__c is just a pointer.
• field_def says it lives in val37 for this tenant's Account object, and record.val37 holds the value as a varchar.
• The physical table has no idea what a yacht size is. The meaning lives entirely in metadata.

SQL vs NoSQL.

• I go relational for the record store. CRM needs transactions, uniqueness constraints, lookups between objects, and read-your-writes, and tenants write SQL-shaped filters.
• A document store would make per-tenant transactional integrity and ad-hoc filtering painful: you end up rebuilding joins and constraints in application code, which is the part a database is good at.
• I accept that the flex-column layout is ugly. For search and reporting over huge tenants, I offload to a separate denormalized index (deep dive 6.3), where eventual consistency is fine.

6Deep dives#

6.1 The metadata-driven schema (the signature hard part)#

This is the decision the whole interview is really about. Three options:

• Schema per tenant. Each tenant gets its own tables (Account_t1234, Contact_t1234). Clean isolation, but 50,000 tenants x 30 tables = 1.5M tables. Catalogs blow up, the query planner chokes, backups are a nightmare, and adding a column to "standard" Account means 50,000 DDL statements. Dead on arrival at our scale.
• Separate database per tenant. Great isolation, but 50,000 databases means 50,000 connection pools and insane operational cost. Reserve this only for the handful of giant or regulated tenants who pay for a dedicated pod.
• Shared tables with flex columns, driven by metadata. One physical record table holds every tenant's every object. The physical columns are generic (val0 through val499), all stored as varchar. field_def says "for tenant X, object Account, field Preferred_Yacht_Size__c lives in slot 37 and is a picklist." The engine reads metadata first, then translates a logical query into a physical one against the flex columns.

Why option 3 wins for the common case.

• When an admin adds a field, we do not run DDL. We insert one row into field_def assigning the next free column slot.
• The field exists instantly: no migration, no downtime.
• That is the product promise, and it is why options 1 and 2 lose. Both make "add a field" an infrastructure event, and we just promised it is a single INSERT.

How a logical write becomes physical:

Option 3 is not free. It creates three real problems, and an interviewer who knows this space will push on all three.

Typing and indexing.

• Everything is varchar, so sorting a number sorts lexically and a date-range filter is meaningless.
• Fix: typed pivot index tables. index_number(tenant_id, object_id, field_name, record_id, num_value) and siblings for dates and strings.
• When a field is marked indexed, the engine maintains the pivot row on write, storing the value in a properly typed column.
• Range queries and sorts hit the pivot table, not the flex columns. This is the single most important trick that makes flex columns usable.

Isolation.

• tenant_id is the leading column of the primary key on every physical table, and of every index.
• The data service refuses to build a query without a tenant predicate.
• Below the app, row-level security policies in the database bind queries to the session's tenant, so even a SQL injection or an app bug cannot return another tenant's rows.
• Isolation is enforced twice, once in the engine and once in the database, because the requirement said "even under a bug in application code" and a single layer does not survive that sentence.

Noisy neighbor.

• Shared tables mean a tenant running a monster report can hog buffer cache and connections for everyone on the pod.
• I attack this on three fronts: tenant-aware connection pools (a cap on concurrent connections per tenant per pod), query cost limits (reject a query estimated to scan more than N rows), and the governor limits in 6.3.

The trade-off, said out loud.

• Flex columns trade clean physical modeling for operational flexibility and isolation.
• Debugging is harder: val37 means nothing without metadata, so every ops tool has to join through field_def.
• I lean entirely on pivot tables for query performance.
• I accept that because the alternative, millions of tables or databases, does not scale operationally, and the whole product rests on instant schema changes.

6.2 Sharding by tenant and keeping transactions single-shard#

I shard the record store by tenant into pods. A pod is a primary plus replicas holding a bounded set of tenants (up to a few thousand small tenants, or one giant tenant alone). A tenant-to-pod map lives in the metadata service and is cached everywhere.

Why tenant-sharding wins here.

• CRM work is almost always "within my org." You rarely ask a question that spans two companies' data, so there is no natural cross-tenant query to optimize for.
• Colocating all of a tenant's objects on one pod makes every transaction single-shard: create an Opportunity, update its parent Account, insert a history row, all one local transaction.
• No two-phase commit, no distributed locks, no saga to reason about. Lookups between objects are local joins.
• This is the reward for the sharding choice I flagged in section 4.

The cost is skew.

• Tenants vary by 1000x, from a 5-person startup to a 500M-row enterprise.
• A placement service tracks per-tenant row count and QPS, packs small tenants to fill pods, and migrates a tenant to a new pod when it grows hot.
• Migration is an online copy plus a short cutover using the change log to catch up, then flip the tenant-to-pod map.

Read scaling inside a pod, and the consistency choice.

• Reads scale on replicas. Writes and read-your-writes reads go to the pod primary; stale-tolerant reads (list views, reports) can hit replicas.
• To honor read-your-writes without pinning everything to the primary, after a write I stash the tenant's latest commit position in Redis.
• A follow-up read checks whether the chosen replica has caught up to that position. If not, it falls back to the primary.
• That gives read-your-writes cheaply, which matters because our 20:1 read ratio means the primary would melt if every read landed on it.

6.3 Per-tenant governor limits and fair use#

Multi-tenant without limits is a tragedy of the commons. One tenant running an infinite loop against the API can drown everyone on their pod, and on shared tables they share the same buffer cache and connection budget. Fairness has to be a hard wall, not a polite request.

Two classes of limit.

• Rate limits. API calls per tenant per rolling window. A token bucket per tenant stored in Redis, each request doing an atomic decrement via a Lua script so concurrent requests cannot both pass when one token remains. The bucket refills at the tenant's plan rate.
• Resource limits per request or transaction. Max rows a query may return, max records touched in one transaction, max CPU time, max storage bytes per tenant. Checked inline by the engine. A query estimated to scan too much is rejected with a clear error before it ever touches the pod.

The trade-off.

• Strict limits sometimes block legitimate heavy users and generate support tickets.
• I accept that because predictable fairness for 50,000 tenants beats letting one tenant degrade a pod and page my on-call at 3am.
• I give limits headroom per plan tier and emit near-limit warnings, which converts most would-be outages into a dashboard nudge.

7Bottlenecks and trade-offs#

What breaks first: the hot pod.

• Packing small tenants is efficient until one goes viral or runs a bad integration, and its pod's primary saturates.
• The placement service plus online migration is the release valve, but migration lag runs minutes to hours for a big tenant.
• So I keep per-tenant circuit breakers that shed a runaway tenant's load before it takes the pod down. The breaker buys time; the migration is the real fix.

Single points of failure.

• The metadata service and the tenant-to-pod map are on every request path, so if either stalls, the platform stalls.
• Metadata is heavily cached (it changes rarely, reads are constant) with versioned invalidation pushed to engine nodes, backed by a replicated durable store.
• The map is small, roughly 50,000 entries, so it replicates to every engine node in memory and a lookup never crosses the network on the hot path.

Pivot index write amplification.

• Every indexed field doubles write work (base row plus pivot row), and some tenants index everything out of habit.
• I cap indexable fields per object and build pivot entries asynchronously for low-priority fields, accepting a short delay before a newly indexed field is sortable.
• This is the back pressure that keeps 6.1's clever trick from quietly turning every write into two.

The consistency call I made on purpose.

• Strong consistency and transactions within a tenant, because reps must trust that an edit they just saved is really saved.
• Eventual consistency across the search index and replicas, because reports and list views tolerate a second of staleness.
• No cross-tenant transactions at all. There is no business reason for them, and avoiding them is what keeps every write single-shard and fast.
• If an interviewer asks for cross-tenant transactions, the right answer is to ask why, not to build a distributed commit protocol nobody needs.

Cache stampede on metadata.

• If a popular tenant's metadata is invalidated, every engine node tries to reload it at once and hammers the metadata store.
• Single-flight load (one node fetches, others wait) plus serving the stale version during refresh turns an invalidation into a brief blip instead of a thundering herd.

Always let the tenant boundary pick the shard, so every transaction stays local and isolation is physical, not just hoped for.

8Summary#

The decisions that matter:

• Metadata-driven flex columns. One shared record table, generic varchar slots, meaning supplied by field_def. Adding a field is an INSERT, so schema changes at runtime with zero DDL. The single most important call, driven entirely by the runtime-schema requirement.
• Shard by tenant. Every transaction single-shard, every query tenant-scoped at the physical layer, no two-phase commit, giant tenants get their own pod.
• Isolation enforced twice. tenant_id leads every key and index, and database row-level security backs it up, because "even under an app bug" needs two layers.
• Typed pivot index tables make flex columns actually queryable, without which the sort-by-Amount case collapses the design.
• Governor limits as a hard wall, per-tenant token buckets plus inline query-cost caps, so one tenant can never starve the pod.
• Strong consistency inside a tenant, eventual everywhere it is safe. Reps trust their own saves; reports tolerate a second of staleness.
• Out of scope by choice: analytics, workflow automation, email send, the file/attachment service, the formula and rollup engine. Naming them keeps me honest about what 45 minutes covered.

Every one of those traces back to the same root number: 250 billion rows across 50,000 runtime-schema tenants.

For the real-world payments side of a SaaS platform like this, see the Stripe system design guide, where idempotency and tenant-scoped billing show up again.