Capstone: Build a Multi-Tenant SaaS AI Assistant, End to End

The capstone: a multi-tenant SaaS assistant with organizations, row-level security, per-org RAG, usage metering, plan limits, evals, and a launch checklist.

Advanced26 min readUpdated October 7, 2026

On this page· The product

Why this matters

Everything in this wiki comes together here. The capstone is a product, not a demo: several companies (tenants) use it at once, each with its own users, documents, and limits, and none may ever see another's data. Building it proves you can make AI features safe, reliable, measurable, and affordable for many customers at once, which is the actual job of an AI-powered full-stack engineer. It is the project to lead with in your portfolio.

The product#

A knowledge assistant for teams: an organization uploads its documents (handbooks, specs, policies), and its members ask questions and get cited answers. The assistant can also take a few actions, such as creating a task in the team's tracker, with approval.

Organizations with members and roles (owner, admin, member).
Per-organization RAG over uploaded documents, ingested by background jobs.
An assistant agent with cited answers and approved actions.
Usage metering per organization, with plan limits (Free, Team).
Evals, tracing with cost, and an admin dashboard.

Hover or tap a component to see what it does.

Capstone SaaS architecture: a Next.js app over an API layer that resolves the user's organization, runs a RAG agent with scoped tools, and queues background jobs; Postgres holds tenant data and vectors, and tracing feeds cost per organization.

Show text description

Every box is something you built in an earlier article. The capstone is about making them work together safely for many customers.

Org users
Next.js app: Server components for pages, a streaming chat UI, document upload, and admin settings.
API layer: Every request resolves the user and organization first; every query is scoped to that organization.
Auth: Sessions, roles, per-organization plans, rate limits, and budgets.
Agent: Answers from the organization's documents and acts through scoped tools with approvals for risky actions.
Jobs: Durable jobs for document ingestion, scheduled evals, and batch processing.
Postgres: One database with row-level tenant isolation, pgvector for retrieval, and run state.
Claude API: Called only from the server, with caching, routing, and retries.
Tracing: Traces with token and cost attributes feed dashboards and usage-based billing.

Milestones#

#	Milestone	Builds on
1	Auth, organizations, memberships, roles	Foundations: APIs and auth
2	Tenant isolation with row-level security	This article
3	Document upload and background ingestion	Project 2, durable execution
4	Streaming chat with cited answers	Project 1, RAG
5	Agent actions with approval	Project 3
6	Usage metering and plan limits	This article, cost engineering
7	Evals in CI, tracing, dashboards	Evals, observability
8	Security review and launch	Security, launch checklist below

The rest of this article covers the parts that are new: tenant isolation and usage limits, plus how to put it all together.

Tenant isolation with row-level security#

In a multi-tenant app, one missing WHERE org_id = $1 leaks a customer's data to another customer. Checking in application code is necessary but not sufficient. Postgres row-level security (RLS) adds a second line of defense: the database itself filters rows by the current tenant, even if a query forgets to.

db/schema.sql

CREATE EXTENSION IF NOT EXISTS vector;
 
CREATE TABLE orgs (
  id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name  text NOT NULL,
  plan  text NOT NULL DEFAULT 'free'  -- free | team
);
 
CREATE TABLE memberships (
  org_id  uuid NOT NULL REFERENCES orgs(id),
  user_id text NOT NULL,
  role    text NOT NULL,              -- owner | admin | member
  PRIMARY KEY (org_id, user_id)
);
 
CREATE TABLE documents (
  id      bigserial PRIMARY KEY,
  org_id  uuid NOT NULL REFERENCES orgs(id),
  title   text NOT NULL,
  content text NOT NULL
);
 
CREATE TABLE usage_events (
  id            bigserial PRIMARY KEY,
  org_id        uuid NOT NULL REFERENCES orgs(id),
  feature       text NOT NULL,
  model         text NOT NULL,
  input_tokens  int NOT NULL,
  output_tokens int NOT NULL,
  cost_usd      numeric(12, 6) NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now()
);
 
-- Row-level security: rows are visible only when app.org_id matches.
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE usage_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_documents ON documents USING (org_id = current_setting('app.org_id', true)::uuid);
CREATE POLICY tenant_usage ON usage_events USING (org_id = current_setting('app.org_id', true)::uuid);
 
-- The application connects as a non-owner role: table owners and superusers bypass RLS.
CREATE ROLE app_user NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON documents, usage_events TO app_user;
GRANT SELECT ON orgs, memberships TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

Every tenant-scoped query runs inside a transaction that sets the tenant first. set_config(..., true) scopes the setting to the transaction, so a pooled connection never carries one tenant's ID into another request:

lib/tenant.ts

import pg from "pg";
 
export const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
 
export type Role = "owner" | "admin" | "member";
const RANK: Record<Role, number> = { member: 0, admin: 1, owner: 2 };
 
/** Throws unless the user belongs to the org with at least `minRole`. Returns their role. */
export async function requireMember(userId: string, orgId: string, minRole: Role = "member"): Promise<Role> {
  const { rows } = await pool.query<{ role: Role }>("SELECT role FROM memberships WHERE org_id = $1 AND user_id = $2", [orgId, userId]);
  const role = rows[0]?.role;
  if (!role || RANK[role] < RANK[minRole]) throw new Error("Forbidden");
  return role;
}
 
/** Run `fn` in a transaction where row-level security only shows `orgId`'s rows. */
export async function withOrg<T>(orgId: string, fn: (db: pg.PoolClient) => Promise<T>): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SET LOCAL ROLE app_user");
    await client.query("SELECT set_config('app.org_id', $1, true)", [orgId]);
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

SET LOCAL ROLE lets you keep one connection string while running tenant queries under the restricted role. In production you can also connect as app_user directly and drop that line.

Prove isolation with a test#

Isolation is too important to assume. This test inserts data for two organizations and checks that a query without any tenant filter still sees only the current tenant's rows:

tenant.test.ts

import { test } from "node:test";
import assert from "node:assert/strict";
import { pool, requireMember, withOrg } from "./lib/tenant.ts";
 
const ACME = "00000000-0000-4000-8000-000000000001";
const GLOBEX = "00000000-0000-4000-8000-000000000002";
 
test("row-level security isolates tenants even without a WHERE clause", async () => {
  await pool.query("INSERT INTO orgs (id, name) VALUES ($1, 'Acme'), ($2, 'Globex') ON CONFLICT DO NOTHING", [ACME, GLOBEX]);
  await withOrg(ACME, (db) => db.query("INSERT INTO documents (org_id, title, content) VALUES ($1, 'Acme handbook', '…')", [ACME]));
  await withOrg(GLOBEX, (db) => db.query("INSERT INTO documents (org_id, title, content) VALUES ($1, 'Globex secrets', '…')", [GLOBEX]));
 
  const seenByAcme = await withOrg(ACME, async (db) => (await db.query<{ title: string }>("SELECT title FROM documents")).rows);
  assert.deepEqual(seenByAcme.map((r) => r.title), ["Acme handbook"]);
});
 
test("a tenant cannot write rows for another tenant", async () => {
  await assert.rejects(withOrg(ACME, (db) => db.query("INSERT INTO documents (org_id, title, content) VALUES ($1, 'Planted', '…')", [GLOBEX])));
});
 
test("role checks", async () => {
  await pool.query("INSERT INTO memberships (org_id, user_id, role) VALUES ($1, 'ana', 'member') ON CONFLICT DO NOTHING", [ACME]);
  assert.equal(await requireMember("ana", ACME), "member");
  await assert.rejects(requireMember("ana", ACME, "admin"), /Forbidden/);
  await assert.rejects(requireMember("ana", GLOBEX), /Forbidden/);
});

The second test works because a policy's USING clause also applies to new rows when no separate WITH CHECK is given: a row for another tenant violates the policy and is rejected.

Usage metering and plan limits#

Each organization's plan includes a monthly AI budget. Record usage after every model call (the traced wrapper already computes the cost) and check the budget before expensive calls:

lib/usage.ts

import type pg from "pg";
 
export const PLAN_LIMITS_USD: Record<string, number> = { free: 5, team: 200 };
 
export async function recordUsage(
  db: pg.PoolClient,
  orgId: string,
  e: { feature: string; model: string; inputTokens: number; outputTokens: number; costUSD: number },
) {
  await db.query(
    "INSERT INTO usage_events (org_id, feature, model, input_tokens, output_tokens, cost_usd) VALUES ($1, $2, $3, $4, $5, $6)",
    [orgId, e.feature, e.model, e.inputTokens, e.outputTokens, e.costUSD],
  );
}
 
export interface BudgetStatus {
  spentUSD: number;
  limitUSD: number;
  remainingUSD: number;
  allowed: boolean;
}
 
/** Month-to-date spend against the plan. Run inside withOrg so RLS scopes the sum. */
export async function checkBudget(db: pg.PoolClient, plan: string): Promise<BudgetStatus> {
  const { rows } = await db.query<{ spent: string }>(
    "SELECT coalesce(sum(cost_usd), 0) AS spent FROM usage_events WHERE created_at >= date_trunc('month', now())",
  );
  const spentUSD = Number(rows[0]!.spent);
  const limitUSD = PLAN_LIMITS_USD[plan] ?? 0;
  return { spentUSD, limitUSD, remainingUSD: Math.max(0, limitUSD - spentUSD), allowed: spentUSD < limitUSD };
}

usage.test.ts

import { test } from "node:test";
import assert from "node:assert/strict";
import { pool, withOrg } from "./lib/tenant.ts";
import { checkBudget, recordUsage } from "./lib/usage.ts";
 
const ORG = "00000000-0000-4000-8000-000000000003";
const OTHER = "00000000-0000-4000-8000-000000000004";
 
test("budget sums only this org's usage this month", async () => {
  await pool.query("INSERT INTO orgs (id, name) VALUES ($1, 'Initech'), ($2, 'Hooli') ON CONFLICT DO NOTHING", [ORG, OTHER]);
  const call = { feature: "chat", model: "claude-opus-5-5", inputTokens: 10_000, outputTokens: 1_000, costUSD: 3 };
  await withOrg(ORG, (db) => recordUsage(db, ORG, call));
  await withOrg(OTHER, (db) => recordUsage(db, OTHER, { ...call, costUSD: 50 }));
 
  const status = await withOrg(ORG, (db) => checkBudget(db, "free"));
  assert.deepEqual(status, { spentUSD: 3, limitUSD: 5, remainingUSD: 2, allowed: true });
 
  await withOrg(ORG, (db) => recordUsage(db, ORG, call));
  assert.equal((await withOrg(ORG, (db) => checkBudget(db, "free"))).allowed, false);
});

When an organization reaches its limit, tell its admins in the UI, email the owner, and offer the upgrade. Never fail silently.

Putting it together: the chat route#

The pieces from earlier projects plug in with tenancy added at the edges:

Resolve the user from the session and the organization from the URL (/org/[orgId]/chat); call requireMember.
Inside withOrg: check the budget; run retrieval (chunks get an org_id column and the same RLS policy); stream the answer; record usage.
Agent tools receive orgId and userId in their context and run their queries through withOrg.
Background ingestion jobs carry orgId in the job payload and use withOrg too.

Launch checklist#

Area	Check
Isolation	RLS on every tenant table; isolation tests pass; no admin connection in request paths
Auth	Roles enforced on every route and action; invitation and removal flows work
Secrets	API keys only on the server; separate keys per environment
Security	Prompt-injection cases in evals; model output sanitized; tools scoped and approved
Reliability	Timeouts, retries, fallback model, degraded mode tested
Cost	Usage recorded per call; plan limits enforced; per-org cost dashboard; spend alerts
Quality	Eval suite in CI for retrieval and answers; feedback collected
Observability	Traces with tokens and cost; dashboards for latency, errors, cache hits
Privacy	Data retention defined; export and delete per organization; privacy policy updated
Docs	README with architecture diagram, decisions, and a demo video

Presenting it#

This project is your strongest portfolio piece. Show it as a product: a short demo video of two organizations side by side, the architecture diagram, the isolation tests, the eval results with numbers, and a page on what it costs per organization per month. The portfolio article covers how.

Common mistakes#

Tenant filtering only in application code. One forgotten clause leaks data; RLS catches it.
Connecting as the table owner. Owners and superusers bypass RLS. Use a restricted role.
Tenant settings that outlive the request. Use set_config(..., true) inside a transaction with pooled connections.
Usage recorded after the fact, or not at all. Meter every call, and check budgets before expensive ones.
Building every feature before any is solid. Ship milestones 1 to 4 well before adding agents.

Try it

Prove the hard parts

Implement the schema, withOrg, and the tests, and run them against a local Postgres database.
Remove the WHERE clause from a real query in your chat route and confirm the isolation test still passes (then put it back).
Write the README section "How tenant isolation works", with the policy, the test, and one paragraph on what would happen without RLS.

Check your understanding

Key takeaways

The capstone combines every earlier project into a multi-tenant product: RAG, chat, agent actions, evals, and observability.
Enforce tenant isolation in the database with row-level security, a restricted role, and transaction-scoped settings, and prove it with tests.
Meter usage per organization and enforce plan limits before expensive calls.
Launch with a checklist, and present the project as a product with numbers.

Next steps#

Portfolio

Turn your projects into evidence that gets interviews.

Security

Review the threat model before launch.

Finished this article? Track it to see your progress on the roadmap.