saas-multi-tenant – skill Thiết kế & UI cho agent AI

SaaS Multi-Tenant Architecture

When to Use This Skill

• The user is building a SaaS application where multiple customers share the same database
• The user asks about tenant isolation, row-level security, or data leakage prevention
• The user needs to scope every database query to a specific tenant without manual WHERE clauses
• The user asks about shared-schema vs schema-per-tenant vs database-per-tenant tradeoffs
• The user is implementing admin endpoints that must access data across tenants
• The user needs to add tenant_id columns to an existing single-tenant application
• The user asks about PostgreSQL RLS policies for tenant isolation
• The user is building tenant-aware middleware in Express, Fastify, or Next.js API routes

Do NOT use this skill when:

• The user is building a single-user application with no shared infrastructure
• The user asks about authentication only without tenant scoping (use an auth skill instead)
• The user needs general database schema design without multi-tenancy requirements

Core Workflow

1. Determine the tenancy model. Ask the user about their scale expectations and isolation requirements. For most SaaS apps under 1000 tenants, shared-schema with a tenant_id column on every table is the correct default. Schema-per-tenant adds operational overhead (migrations run N times). Database-per-tenant can also be justified by isolation, noisy-neighbor, recovery, customer-managed keys or operational needs; compare actual requirements.

2. Add tenant_id to every tenant-scoped table. The column must be NOT NULL, type UUID or TEXT, and included in every composite index. Never allow a tenant-scoped table to exist without this column — a missing tenant_id is a data leak waiting to happen.

3. Set up PostgreSQL Row-Level Security (RLS). Create a policy on each tenant-scoped table that filters rows by current_setting('app.current_tenant_id'). This acts as a database-level safety net — even if application code forgets a WHERE clause, RLS blocks cross-tenant reads.

4. Build tenant-aware middleware. At the start of every request, extract the tenant_id from the authenticated session or JWT claims. Set it on the database connection using SET LOCAL app.current_tenant_id = '...' inside a transaction. Every subsequent query in that request inherits the tenant scope automatically.

5. Scope all ORM queries by tenant. If using Prisma, apply a global middleware that injects where: { tenantId } into every findMany, findFirst, update, and delete call. If using Drizzle, create a base query builder that includes the tenant filter. Never rely on developers remembering to add the filter manually.

6. Handle tenant-aware migrations. Every new table migration must include tenant_id as a column. Write a linting rule or CI check that rejects any migration creating a table without tenant_id unless the table is explicitly marked as global (e.g., plans, feature_flags).

7. Build cross-tenant admin routes separately. Admin endpoints that aggregate data across tenants must bypass RLS explicitly using SET LOCAL role = 'admin_bypass' or a dedicated database role. These routes must be protected by a separate admin authentication flow — never reuse tenant user sessions for admin access.

8. Implement tenant provisioning. When a new customer signs up, create their tenant record, seed default data (roles, settings, onboarding state), and assign the founding user. Wrap this in a database transaction so partial provisioning never leaves orphan records.

Examples

Example 1: PostgreSQL RLS Policy for Tenant Isolation

-- Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

-- Policy: users can only see rows where tenant_id matches the session variable
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Policy for INSERT: new rows must match the current tenant
CREATE POLICY tenant_insert ON projects
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

Example 2: Explicit Transaction Boundary

Do not commit on the HTTP response finish event: the handler may fail, the client may disconnect, or the response may already report success before COMMIT fails. Use an awaited transaction wrapper and send the response only after commit:

async function withTenantTransaction(pool, tenantId, work) {
  const client = await pool.connect();
  let destroy = false;
  try {
    await client.query("BEGIN");
    await client.query("SELECT set_config('app.current_tenant_id', $1, true)", [tenantId]);
    const result = await work(client); // Every tenant query uses this client.
    await client.query("COMMIT");
    return result;
  } catch (error) {
    try { await client.query("ROLLBACK"); }
    catch { destroy = true; }
    throw error;
  } finally {
    client.release(destroy); // Destroy on rollback failure; release exactly once.
  }
}

Add request timeout/cancellation and target-specific integration tests before use. Validate the user's current tenant membership server-side before calling it; never trust an unverified tenant claim or routing header.

Example 3: Historical Prisma Middleware Sketch

This incomplete legacy $use example is not an isolation layer. It omits findUnique, upsert, nested operations, relation loads and raw queries, and does not bind a transaction-local RLS context. Current Prisma versions may require extensions instead. Do not deploy it as automatic protection; enumerate every operation, forbid client changes to tenant ownership and execute under a non-owner, non-BYPASSRLS database role with tested policies.

import { PrismaClient } from "@prisma/client";

// Tables that do NOT have tenant_id (global tables)
const GLOBAL_TABLES = new Set(["Plan", "FeatureFlag", "SystemConfig"]);

function createTenantPrisma(tenantId: string): PrismaClient {
  const prisma = new PrismaClient();

  prisma.$use(async (params, next) => {
    if (GLOBAL_TABLES.has(params.model ?? "")) return next(params);

    // Initialize args.where — Prisma passes undefined args for calls like findMany()
    params.args = params.args ?? {};
    params.args.where = params.args.where ?? {};

    // Inject tenant filter on reads (skip findUnique — it only accepts unique-field selectors)
    if (["findMany", "findFirst", "count", "aggregate"].includes(params.action)) {
      params.args.where = { ...params.args.where, tenantId };
    }

    // Inject tenant_id on creates
    if (["create", "createMany"].includes(params.action)) {
      params.args.data = params.args.data ?? {};
      if (params.action === "createMany") {
        params.args.data = params.args.data.map((d: any) => ({ ...d, tenantId }));
      } else {
        params.args.data = { ...params.args.data, tenantId };
      }
    }

    // Scope updates and deletes
    if (["update", "updateMany", "delete", "deleteMany"].includes(params.action)) {
      params.args.where = { ...params.args.where, tenantId };
    }

    return next(params);
  });

  return prisma;
}

Never Do This

1. Never query a tenant-scoped table without a tenant_id filter. Even if your ORM middleware handles it, raw SQL queries bypass middleware entirely. Every raw query must include WHERE tenant_id = $1 or rely on RLS. A single unscoped SELECT * FROM invoices leaks every customer's billing data.

2. Never store tenant_id only in the application session without enforcing it at the database level. Application-layer filtering is a suggestion. RLS is enforcement. If a bug in your middleware skips the tenant filter, only RLS prevents the data leak. Run both layers.

3. Never use auto-incrementing integer IDs for tenant-scoped resources. Sequential IDs (invoice #1042) let attackers enumerate other tenants' resources by incrementing the ID. Use UUIDs for all tenant-scoped primary keys. Reserve integer IDs for internal-only tables.

4. Never let tenant users access admin aggregation endpoints. A route like GET /admin/metrics that queries across all tenants must never be reachable with a regular tenant JWT. Use a separate authentication mechanism (API key, admin role claim with a different issuer) for cross-tenant routes.

5. Use a separate least-privilege migration role. RLS governs row access, not DDL visibility; ALTER TABLE does not silently alter only one tenant. Test data migrations against every intended tenant and grant elevated privileges only for the required operation, never default to superuser.

6. Share pools only with reliable transaction cleanup. If you use SET LOCAL app.current_tenant_id inside a transaction, that setting is scoped to the transaction. But if a previous request's transaction was not properly committed or rolled back, the connection returns to the pool with stale tenant context. Commit/rollback before release

… (nội dung đầy đủ có trong gói tải về)