Building a multi-tenant SaaS CRM

Four subsystems, one product. Authentication and the data model are load-bearing — get those wrong and everything above them has to be rebuilt. Analytics and invoicing are well understood. The AI automation is the only part where I would plan for it to be wrong some of the time.

1 · Architecture

A single application tier talking to one Postgres cluster, with the AI work pushed onto a queue so a slow model never holds a request open. Everything below is stateless apart from the database and the object store.

The gateway resolves the tenant once, from the token, and sets it as a session variable. Nothing downstream is trusted to filter by tenant, because eventually something will forget.

2 · Data model

Five core tables. Every tenant-scoped table carries tenant_id and a policy that reads it from the session, so a missing WHERE clause returns nothing rather than everything.

-- every tenant row is invisible unless the session claims it
CREATE TABLE accounts (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   uuid NOT NULL REFERENCES tenants(id),
  name        text NOT NULL,
  owner_id    uuid REFERENCES users(id),
  mrr_cents   integer NOT NULL DEFAULT 0,
  created_at  timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON accounts
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE INDEX accounts_tenant_owner_idx
  ON accounts (tenant_id, owner_id);

At those volumes the composite index on (tenant_id, …) does all the work. An index on tenant_id alone is close to useless once one tenant holds a large share of the rows.

3 · Authentication

Sessions over JWTs unless you need cross-domain access. A short-lived access token with a rotating refresh token, and the tenant resolved server side on every request rather than read from anything the client sends.

// tenant-guard.ts — runs before any handler touches the database
export async function withTenant(req: Request, next: Next) {
  const session = await resolveSession(req);
  if (!session) throw new Unauthorized();

  // local scope — released with the transaction, not the connection
  await db.query("SELECT set_config('app.tenant_id', $1, true)", [
    session.tenantId,
  ]);

  return next();
}

• Access token 15 minutes, refresh token 30 days, rotated on every use.
• SSO via SAML for enterprise tenants — plan the table now, build it later.
• Audit every permission change; it is the first thing a security questionnaire asks for.

4 · Analytics

Do not query the operational tables for dashboards. A nightly rollup into a summary table keeps the CRM responsive and makes the numbers reproducible, which matters more than freshness for revenue reporting.

Build the dashboard around net revenue retention. It is the one metric that only moves when the product is genuinely working:

NRR = (start + expansion − contraction − churn) ÷ start

Compute it monthly from the rollup, never on the fly. Two people computing it two ways is how a board meeting goes badly.

5 · Invoicing

Invoices are a state machine, not a table of rows you edit. Once a document is issued it is immutable — corrections happen by issuing a credit note, because tax authorities and customers both expect the original to still exist.

Store money as integer minor units with an explicit currency column. Floating point currency is a bug you will ship and then spend a week finding.

6 · AI automation

The useful version of this is narrow: summarise a thread, draft a follow-up, score a lead, flag a deal that has gone quiet. All of it runs asynchronously, and all of it is a suggestion a human confirms.

Queue every job, cap the spend per tenant per day, and log the prompt with its response. Without that log you cannot answer the first question a customer asks when it gets something wrong.

7 · Interface

One dense workspace beats five thin pages. Salespeople live in a list and a record, so make those two excellent and treat everything else as secondary.

8 · Build order

1. Tenancy and auth — weeks 1–3. Nothing else can start safely.
2. Core CRM objects — weeks 3–7. Accounts, contacts, deals, activity.
3. Invoicing — weeks 7–10. State machine first, PDF rendering last.
4. Analytics rollups — weeks 9–12. Runs alongside invoicing.
5. AI suggestions — weeks 12–16. Last, deliberately, because it needs real data to be any good.

Roughly sixteen engineer-weeks for a credible first version with two engineers. If you have to ship in one quarter, cut the analytics rollups and the SSO work — both can be added later without touching the schema.

Artifacts

Everything generated with this answer. Open one to jump to the section it came from.