How to Design Your SaaS Database Schema

Multi-Tenant Data Isolation

The most important principle in SaaS database design is multi-tenancy. Every query your application runs must be scoped to a single customer's data. If a bug in your code ever shows one customer's data to another customer, you have a catastrophic trust problem that can end your business.

The platform handles this automatically. In the built-in NoSQL database, every record uses the customer's account ID as the partition key. This means it is physically impossible for a database query to accidentally return another customer's data, because the partition key is always included in every operation. You do not need to remember to add a WHERE clause or worry about SQL injection leaking data across tenants.

Choosing Between NoSQL and SQL

The platform supports both approaches, and the right choice depends on your data patterns:

Use NoSQL (built-in) when:

• Your data structure will change frequently as you add features
• Each record is relatively self-contained (not lots of JOINs between tables)
• You want zero database configuration and instant setup
• You need automatic scaling without managing database servers
• Your access patterns are mostly "get record by ID" or "list records by prefix"

The built-in NoSQL database uses a partition key (pid) and sort key pattern. For a typical SaaS app, the partition key is the account ID and the sort key identifies the record type and specific item. For example, a project management app might store data like: pid="account123", sortkey="project-001" for a project record, and pid="account123", sortkey="task-001" for a task record.

Use SQL (MySQL or PostgreSQL) when:

• Your data has complex relationships that need JOINs (orders with line items, projects with tasks and subtasks)
• You need aggregation queries (SUM, AVG, GROUP BY) for reporting
• You want to run ad-hoc queries during development
• Your team already knows SQL and has existing data in a relational database

You can connect a MySQL database or connect a PostgreSQL database to your custom app. The AI SQL assistant can even help users query their data using plain English.

Designing Your Data Model

Start by listing the core "things" your product manages. A helpdesk app manages tickets, users, and responses. A booking app manages appointments, services, and clients. Each of these becomes a data type in your schema.

For each data type, define:

• Required fields: What must every record have? A ticket needs a title, status, and creation date at minimum.
• Optional fields: What might some records have? A ticket might optionally have a priority, assigned agent, or category.
• Relationships: How does this data type connect to others? A ticket belongs to a customer and may have multiple responses.
• Access patterns: How will your app read this data? List all tickets for an account? Get one ticket by ID? Search tickets by status?

Schema Design for NoSQL

In NoSQL, you design your schema around your access patterns rather than around data normalization. The key principle is: store data the way you read it.

For a helpdesk SaaS example:

• All tickets for an account: pid="accountID", sortkey prefix="ticket-" (query returns all tickets)
• Single ticket: pid="accountID", sortkey="ticket-{ticketID}"
• Ticket responses: stored as an array field inside the ticket record (no separate table needed)
• App settings: pid="accountID", sortkey="settings" (one record per account)

This pattern means listing all tickets is a single query, getting one ticket is a single query, and adding a response is a single update. No JOINs, no complex queries, no performance concerns at scale.

Schema Design for SQL

If your data has complex relationships and you need reporting queries, a SQL schema with proper normalization works well. For the same helpdesk example:

• A tickets table with columns for id, account_id, title, status, priority, created_at
• A responses table with columns for id, ticket_id, author, message, created_at
• An account_id column on every table for multi-tenant filtering
• Indexes on account_id and status for fast filtered queries

Always include an account_id column on every table and add it to every WHERE clause. This is your multi-tenancy boundary.

Planning for Growth

Design your schema to handle growth without restructuring. This means:

• Use flexible record structures. In NoSQL, you can add new fields to records at any time without migration. In SQL, plan for an extras or metadata JSON column for fields you might add later.
• Keep records reasonably sized. If a single record could grow to contain thousands of items (like an unbounded array of messages), consider splitting it into separate records or paginating the data.
• Use timestamps as part of sort keys. This lets you query for recent records efficiently and keeps data naturally ordered by time.