Building a multi-tenant CRM with Amazon Aurora DSQL

A multi-tenant customer relationship management (CRM) system serves many customer organizations (tenants) from shared infrastructure: every tenant needs its own accounts, contacts, and opportunities, and no tenant can ever see another tenant’s data. On a traditional relational database, that work carries real operational weight, from sizing instances for peak load to scheduling maintenance windows and rotating credentials. Onboarding a tenant can mean provisioning capacity rather than a single write.

Amazon Aurora DSQL, a serverless, PostgreSQL-compatible distributed SQL database, removes most of that operational overhead. There are no instances or replicas to manage, it scales automatically, it gives strong read-after-write consistency, and connections authenticate with AWS Identity and Access Management (IAM) tokens rather than stored passwords.

This post assumes familiarity with the core design patterns of Amazon Aurora DSQL, which Building scalable applications on Amazon Aurora DSQL covers in depth. Rather than re-explain them, this post focuses on what changes when the workload is multi-tenant.

In this post, you learn how to design and implement a multi-tenant CRM on Amazon Aurora DSQL using the pooled tenancy model. You see how tenant isolation is enforced in a centralized data-access layer and explore the design considerations specific to Amazon Aurora DSQL. Then you use a provided deployment script to run a working proof of concept on Amazon Elastic Container Service (Amazon ECS) Express Mode.

Architecture overview

The following diagram shows the architecture.

Figure 1: Architecture of the pooled multi-tenant CRM. Tenant clients send HTTPS requests to Amazon ECS Express Mode on AWS Fargate, which connects to Amazon Aurora DSQL over an IAM-authenticated connection.

The service runs on Amazon ECS Express Mode, which provisions a managed AWS Fargate service with automatic scaling, load balancing, and networking. It pairs well with Amazon Aurora DSQL because neither requires you to manage instances.

The architecture uses three AWS services, each chosen for a specific reason:

Amazon ECS Express Mode is the compute layer: your containerized Node.js application on AWS Fargate, with automatic scaling, load balancing, and an HTTPS endpoint.

Amazon Aurora DSQL is the data layer: every tenant’s records in a shared schema, with strong read-after-write consistency and IAM-authenticated connections.

IAM is the security layer: short-lived tokens decide which compute can reach the database, minted by the Amazon Aurora DSQL connector from the task role.

Inside the task, the application is layered: routes validate input and call tenant-scoped repositories, middleware resolves and validates the tenant context, and the repositories reach Amazon Aurora DSQL through parameterized queries. Write paths go through an Optimistic Concurrency Control (OCC) retry wrapper, covered later, and all connections go through the Amazon Aurora DSQL connector.

The full request flow is as follows:

1. Client sends an HTTPS request that identifies its tenant.
2. Amazon ECS Express Mode routes the request through its load balancer to the Express.js application running on AWS Fargate.
3. Application resolves the tenant, validates it, and runs tenant-scoped business logic. Every query is filtered by tenant_id.
4. Amazon Aurora DSQL stores and retrieves data with strong consistency using a connection authenticated through IAM.

How Amazon Aurora DSQL simplifies multi-tenant workloads

Amazon Aurora DSQL removes several operational tasks that multi-tenant workloads usually require on a managed relational database:

• No database infrastructure. Create a cluster, get an endpoint, write SQL. No instance sizing or read replicas, and it scales with the workload, so onboarding a tenant is a write rather than a capacity exercise.
• Strong consistency by default. An account created by a tenant’s user is visible to the next request, so there is no read-your-writes problem to design around.
• PostgreSQL compatibility. Familiar tools and drivers work, including psql and node-postgres.
• IAM-based authentication. Short-lived IAM tokens replace database passwords, so there are no credentials to store or rotate. The chain is short: create an IAM role with dsql:DbConnect permission on your cluster, then assign it to the Amazon ECS Express Mode service as its task role. At runtime, the Amazon Aurora DSQL connector uses those task-role credentials to generate short-lived auth tokens, which Amazon Aurora DSQL validates against IAM before accepting the connection.
• No maintenance windows. Patches and minor-version upgrades apply with no downtime, so there is no window to negotiate across tenants in different time zones. Schema changes are non-blocking too: CREATE INDEX ASYNC builds in the background while the table serves traffic, though DDL, GRANT, and REVOKE can fail concurrent transactions with a schema conflict.

Choosing a multi-tenancy model

There are three common ways to isolate tenants in a relational database:

Cluster per tenant: a separate Amazon Aurora DSQL cluster per tenant. This gives the strongest isolation, since tenants share no schema, data, or endpoint, and because Amazon Aurora DSQL is serverless and billed by use, an idle cluster costs nothing beyond the storage its data occupies. The trade-off is fan-out rather than infrastructure: nothing to size or rebalance, but every per-cluster task multiplies, including an endpoint to route to, its own IAM grants, and a migration run per tenant. The default quota is 20 single-Region clusters per account per Region, which can be raised through Service Quotas.

Schema per tenant: one cluster, a separate PostgreSQL schema for each tenant. Amazon Aurora DSQL currently allows up to 10 schemas per database, and a cluster currently has one database, so schema-per-tenant suits a small, fixed set of tenants.

Single shared schema: one schema, with every row tagged by a tenant_id. This is the simplest to implement and fastest to onboard, since adding a tenant is one INSERT, and it makes the most of the automatic scaling of Amazon Aurora DSQL. The cost is that the application, not the database, keeps tenants apart.

This post implements the pooled model, which best illustrates the Amazon Aurora DSQL design considerations that follow. For the strongest isolation available today, prefer a cluster per tenant. In the pooled model, isolation is the application’s responsibility. For defense in depth, you can extend this boundary into the database with security_barrier views. Amazon Aurora DSQL does not support row-level security, and security_barrier only forces the view’s own filter to run first: the view needs a per-session tenant value to filter on, and the runtime role needs no privileges on the base tables.

Designing the data model

The CRM needs a tenant registry plus the core CRM entities. Every tenant-scoped table carries a tenant_id column, which is the pooled model’s isolation discriminator:

CREATE TABLE IF NOT EXISTS tenants (
  id UUID PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants (id),
  email VARCHAR(254) NOT NULL,
  name VARCHAR(200) NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'member',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (tenant_id, email) -- email unique per tenant
);

CREATE TABLE IF NOT EXISTS accounts (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants (id),
  name VARCHAR(200) NOT NULL,
  industry VARCHAR(100),
  website VARCHAR(254),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS contacts (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants (id),
  account_id UUID NOT NULL REFERENCES accounts (id), -- FK: account must exist
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(254),
  phone VARCHAR(40),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS opportunities (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants (id),
  account_id UUID NOT NULL REFERENCES accounts (id),
  name VARCHAR(200) NOT NULL,
  stage VARCHAR(30) NOT NULL DEFAULT 'prospecting',
  amount NUMERIC(14,2) NOT NULL DEFAULT 0,
  close_date DATE,
  owner_id UUID REFERENCES users (id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS activities (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants (id),
  related_type VARCHAR(20) NOT NULL, -- 'account'|'contact'|'opportunity'
  related_id UUID NOT NULL, -- polymorphic ref; checked in app layer
  subject VARCHAR(200) NOT NULL,
  notes TEXT,
  due_at TIMESTAMPTZ,
  done BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Design considerations for Amazon Aurora DSQL

A tenant_id on every row. The discriminator column is the backbone of the pooled model. It also anchors every secondary index, so tenant-scoped lookups stay selective as the shared tables grow.

UUIDs as primary keys. The implementation generates UUIDs with crypto.randomUUID(). Random UUIDs distribute writes evenly across the key space of Amazon Aurora DSQL, avoiding the single-shard hot spot a monotonically increasing key creates. It pairs that with tenant_id-leading secondary indexes, which group each tenant’s entries and keep its reads selective. Whether that grouping becomes a hot spot depends on the suffix: a random UUID keeps the range divisible, a monotonic or low-cardinality one concentrates writes. See Primary keys in Amazon Aurora DSQL.

Referential integrity with foreign keys. This schema uses foreign keys: contacts.account_id and opportunities.account_id reference accounts.id. A single-column foreign key proves the account exists, not that it belongs to the requesting tenant. The sample closes that gap in the application, checking the parent is in the same tenant_id before inserting. The database can do it instead. Add UNIQUE (tenant_id, id) to accounts, keep the UUID primary key, and declare FOREIGN KEY (tenant_id, account_id) REFERENCES accounts (tenant_id, id): a cross-tenant insert is then rejected with SQLSTATE 23503. Amazon Aurora DSQL accepts a unique constraint as a foreign-key target, so the primary key does not change. The polymorphic activities.related_id has no foreign key.

Asynchronous indexes. Secondary indexes build in the background and don’t block reads or writes:

CREATE INDEX ASYNC IF NOT EXISTS idx_contacts_tenant_acct
  ON contacts (tenant_id, account_id);

Inline PRIMARY KEY and UNIQUE constraints (for example UNIQUE (tenant_id, email) on users) create immediately-valid backing indexes as part of CREATE TABLE. Only standalone CREATE INDEX ASYNC statements build asynchronously.

Database migrations

Amazon Aurora DSQL currently requires each Data Definition Language (DDL) statement to run in its own transaction, so the migration runner executes each CREATE TABLE and CREATE INDEX separately. CREATE INDEX ASYNC returns a job_id and builds the index in the background, so the runner blocks on sys.wait_for_job until the build finishes, which keeps migrations deterministic in CI:

// Three Aurora DSQL details shape this:
// 1. sys.wait_for_job is a stored procedure: CALL it. SELECT raises SQLSTATE 42809.
// 2. CALL cannot run inside a transaction block (SQLSTATE 0A000), so commit the
// DDL first and issue the CALL on the autocommit connection.
// 3. DSQL rejects a subquery as the CALL argument, but a bind parameter is
// accepted, so pass the job id as $1 rather than interpolating it.

for (const sql of DDL_STATEMENTS) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await client.query(sql);
    await client.query("COMMIT");
    const jobId = result.rows?.[0]?.job_id;
    if (jobId) await waitForIndexJob(client, jobId, sql);
  } finally {
    client.release();
  }
}

async function waitForIndexJob(client, jobId, label) {
  // Returns normally whether or not the build worked, reporting it in `succeeded`.
  const { rows } = await client.query("CALL sys.wait_for_job($1)", [jobId]);
  if (rows?.[0]?.succeeded === false) {
    // The index exists but is invalid, and IF NOT EXISTS means a re-run would
    // skip it and leave it broken forever. Fatal, with recovery instructions.
    throw new Error(`Index build failed: ${label}. Drop the index, then re-run.`);
  }
}

Enforcing tenant isolation

Two pieces work together. First, a middleware resolves the tenant for every request from a verified token, not a client-supplied header, and rejects unknown or inactive tenants before any data access:

// The tenant comes from a verified token claim, not a client-supplied header:
// on a public endpoint an X-Tenant-Id header lets any caller claim any tenant's
// data. The sample has an opt-in ALLOW_INSECURE_TENANT_HEADER for local testing,
// so this is the default rather than the only path.
export async function tenantContext(req, _res, next) {
  // On Express 4 an async middleware that throws escapes the error handler and
  // becomes an unhandled rejection, which terminates the process on Node 22.
  // Every async middleware needs this try/catch.
  try {
    const authz = req.header("authorization");
    if (!authz?.toLowerCase().startsWith("bearer ")) {
      throw new ApiError(401, "Missing bearer token");
    }
    // Throws ApiError(401) if the signature or the expiry does not check out.
    const { tenant_id: tenantId } = verifyTenantToken(authz.slice(7).trim());
    if (!UUID_RE.test(tenantId)) {
      throw new ApiError(400, "tenant_id must be a valid UUID");
    }
    const { rows } = await pool.query(
      "SELECT status FROM tenants WHERE id = $1", [tenantId],
    );
    if (rows.length === 0) throw new ApiError(404, "Unknown tenant");
    if (rows[0].status !== "active") throw new ApiError(403, "Tenant is not active");
    req.tenantId = tenantId;
    next();
  } catch (err) {
    next(err);
  }
}

Second, every repository function requires a tenant ID and filters by it:

export async function listAccounts(tenantId: string, limit = 50) {
  const { rows } = await pool.query(
    `SELECT id, tenant_id, name, industry, website, created_at
     FROM accounts WHERE tenant_id = $1
     ORDER BY created_at DESC LIMIT $2`,
    [tenantId, limit],
  );
  return rows;
}

Derive the tenant from a verified identity rather than a raw client-supplied header: an X-Tenant-Id header lets any caller claim any tenant. The sample verifies a signed bearer token and reads the tenant from its claim. In production an identity provider issues that token and you verify it against the provider’s published keys, reading a claim such as custom:tenant_id.

Testing the boundary. src/repositories/tenantIsolation.test.ts drives every repository function against a mock client and asserts that each statement filters on tenant_id and binds the caller’s tenant to it, so removing a predicate fails the suite instead of passing quietly. A coverage guard enumerates the repository exports and fails on any the sweep does not reach.

Connecting to Amazon Aurora DSQL

The official connector handles IAM token generation and refresh, parses the AWS Region from the cluster hostname, and verifies the server certificate by default:

import { AuroraDSQLPool } from "@aws/aurora-dsql-node-postgres-connector";

export const pool = new AuroraDSQLPool({
  host: process.env.DSQL_ENDPOINT,
  user: "app_runtime", // least-privilege role, not admin
  database: "postgres",
  max: 10,
  idleTimeoutMillis: 300_000,
  // The next two are already connector defaults; set here for clarity.
  maxLifetimeSeconds: 3300, // recycle before the 1-hour connection cap
  ssl: { rejectUnauthorized: true },
  retry: { maxRetries: 5, baseDelayMs: 25 },
});

// Required, not optional. node-postgres emits 'error' on the POOL when an idle
// connection fails, which happens when Aurora DSQL retires one. With no
// listener, Node treats it as an unhandled 'error' event and exits.
pool.on("error", (err) => {
  console.error("Idle connection error (the pool recovers):", err.message);
});

No password, no TLS certificate management, no credential rotation. Amazon Aurora DSQL currently caps any single connection at one hour. Setting maxLifetimeSeconds to 3,300 (55 minutes) lets the pool retire connections proactively.

Creating the least-privilege role. The role must exist and be mapped to the task role before the first connection. As the admin: CREATE ROLE app_runtime WITH LOGIN, then AWS IAM GRANT app_runtime TO ‘your-task-role-arn’, then GRANT the table privileges it needs. Without the IAM GRANT, connections fail with SQLSTATE 28000 even though the task role holds dsql:DbConnect. See scripts/setup-db.sh.

Working with CRM data

Creating a contact shows application-level referential integrity and tenant scoping together, inside an OCC-aware transaction:

export async function createContact(tenantId, input) {
  const id = crypto.randomUUID();
  return pool.transaction(async (client) => {
    // The FK on account_id guarantees the account EXISTS; this check guarantees
    // it belongs to THIS tenant (a foreign key does not enforce tenant scoping).
    const { rows } = await client.query(
      "SELECT 1 FROM accounts WHERE tenant_id = $1 AND id = $2",
      [tenantId, input.account_id],
    );
    if (rows.length === 0) throw new ApiError(400, "account_id not in tenant");

    const result = await client.query(
      `INSERT INTO contacts (id, tenant_id, account_id, first_name, last_name)
       VALUES ($1, $2, $3, $4, $5) RETURNING *`,
      [id, tenantId, input.account_id, input.first_name, input.last_name],
    );
    return result.rows[0];
  });
}

Handling concurrent writes without locks

Amazon Aurora DSQL uses Optimistic Concurrency Control instead of locking: transactions proceed without locks and conflicts are detected at commit, surfacing as SQLSTATE 40001. pool.transaction() retries them with exponential backoff, so the repository code stays ordinary. pool.query() does not retry:

const VALID_STAGES = [
  "prospecting", "qualification", "proposal",
  "negotiation", "closed_won", "closed_lost",
];

export async function updateStage(tenantId, id, stage) {
  // Validate in the application: the schema types stage as VARCHAR(30) with no
  // CHECK constraint, so without this any string is accepted. Aurora DSQL
  // does support CHECK; keeping the list here is a choice, not a limit.
  if (!VALID_STAGES.includes(stage)) {
    throw new ApiError(400, `Invalid stage: ${stage}`);
  }
  return pool.transaction(async (client) => {
    const { rows } = await client.query(
      `UPDATE opportunities SET stage = $3
       WHERE tenant_id = $1 AND id = $2 RETURNING *`,
      [tenantId, id, stage],
    );
    if (rows.length === 0) throw new ApiError(404, "Opportunity not found");
    return rows[0];
  });
}

The callback must be idempotent, because it may run more than once. Generating UUIDs before the transaction begins keeps retries safe.

Staying within the transaction limits

Amazon Aurora DSQL currently caps a single transaction at 3,000 modified rows, 10 MiB of modified data, and 5 minutes of execution time. A bulk operation on a busy tenant, such as marking every open activity on an account as done, can exceed one of those in a single UPDATE, so batch it in a resumable loop whose predicate makes each iteration idempotent:

// Aurora DSQL caps a transaction two ways: 3,000 modified rows AND 10 MiB of
// changed data. No fixed row count is safe, which is the point worth taking
// away. The row cap is a count, but the byte cap depends on the data, and
// activities.notes is TEXT, so any constant is either too small for narrow rows
// or too large for wide ones. Start at BATCH_SIZE and halve on SQLSTATE 54000,
// which adapts to whatever the rows actually weigh.
const BATCH_SIZE = 1000;
const BATCH_SIZE_FLOOR = 50;

let batchSize = BATCH_SIZE;
for (;;) {
  let affected;
  try {
    affected = await pool.transaction(async (client) => {
      const r = await client.query(
        `UPDATE activities SET done = TRUE
         WHERE id IN (SELECT id FROM activities
           WHERE tenant_id = $1 AND related_id = $2 AND done = FALSE
           LIMIT $3)`,
        [tenantId, relatedId, batchSize],
      );
      return r.rowCount ?? 0;
    });
  } catch (err) {
    // 54000 is program_limit_exceeded. Shrinking is sticky for the rest of the
    // call, since later batches hold similar data.
    if (err.code === "54000" && batchSize > BATCH_SIZE_FLOOR) {
      batchSize = Math.max(BATCH_SIZE_FLOOR, Math.floor(batchSize / 2));
      continue;
    }
    throw err;
  }
  if (affected < batchSize) break;
}

Security considerations

The design applies several security principles that fit the pooled multi-tenant model:

Centralized tenant isolation. Every query filters by tenant_id, resolved once per request in middleware, so handlers reach data only through tenant-scoped repositories.

Tenant validation. The tenant comes from a verified token claim, and requests for unknown or inactive tenants are rejected before any data access.

Least-privilege database role. The runtime connects as app_runtime, a custom role that has only the table privileges it needs, rather than the cluster admin role.

IAM database authentication. No database passwords in configuration or environment variables. The application’s own token-signing key, APP_JWT_SECRET, is a plaintext environment variable, which is a demo convenience: in production, verify tokens against an identity provider’s JWKS or load the key from AWS Secrets Manager.

No internal error details. The API returns generic messages for unhandled exceptions.

Try it yourself

You can deploy a working proof of concept that implements everything described in this post. The source code, the deployment script, and the full API endpoint reference are in the sample repository.

Prerequisites. You need the following:

• An AWS account with permissions for Amazon Aurora DSQL, IAM, and Amazon ECS.
• The AWS CLI configured for your Region.
• AWS CloudShell, which has psql and Node.js preinstalled, or Docker, Node.js 22, and psql locally.

Deploy, then create the schema and the runtime role:

git clone https://github.com/aws-samples/aurora-dsql-samples.git
cd aurora-dsql-samples/sample-amazon-aurora-dsql-multi-tenant-crm
./scripts/deploy.sh # cluster, task role, image, ECS service
./scripts/setup-db.sh # tables, app_runtime role, IAM GRANT
source scripts/.state.env
DSQL_ENDPOINT=$DSQL_ENDPOINT DSQL_USER=admin npm run seed # prints tenant UUIDs
APP_JWT_SECRET=$APP_JWT_SECRET npm run --silent mint-token -- <tenant-uuid>

Cost. The cluster bills per use, but the Amazon ECS Express Mode service runs continuously. Expect low single-digit US dollars per day while the stack is up.

Clean up

When you are finished, remove the stack so it stops incurring charges:

./scripts/cleanup.sh

This permanently deletes the Amazon Aurora DSQL cluster and every tenant’s data, along with the Amazon ECS service, the container image, and the IAM roles. It cannot be undone, so run it only against a cluster you created for this walkthrough.

Conclusion

In this post, you designed and implemented a multi-tenant CRM on Amazon Aurora DSQL using the pooled tenancy model, with isolation in a centralized data-access layer and tenant_id on every row. The lesson that matters most is that the tenant must come from a verified token claim, never a client-supplied header. Alongside it: foreign keys for existence paired with tenant-scoped ownership checks, asynchronous index creation, OCC retries, and batching that respects the row and byte caps. The same patterns carry to any transactional multi-tenant workload on a distributed SQL database.

The full source code, including the schema for all six CRM tables and the complete set of API route handlers, is available in the sample repository. To get started with Amazon Aurora DSQL, see the Amazon Aurora DSQL overview page and the Amazon Aurora DSQL User Guide.

About the authors