Designing SaaS Database Architecture

Bad database decisions in your first week will haunt you for years.

A SaaS founder needs a schema that handles multi-tenancy, billing, and growth. If you get it wrong, you will rewrite your entire system in six months.

Here is how to build a production-ready schema.

  1. Choose Your Tenant Strategy

You must isolate data between customers.

• Row-level (Shared DB): Low complexity. Best for most startups. Use a WHERE organization_id = ? filter on every query. • Schema-level (Separate Schemas): Medium complexity. Good for regulatory needs. • Database-level (Separate DBs): High complexity. Best for enterprise clients.

For 90% of new products, row-level multi-tenancy is the right choice.

  1. The Correct Entity Hierarchy

Do not bill users directly. Users come and go. Organizations stay.

The hierarchy should look like this: User ── MemberOf ── Organization ── Subscription ── Invoice

• Users: Keep this table for identity only. Use UUIDs to prevent attacks. • Organizations: This is your tenant boundary. Always link subscriptions to the organization. • Memberships: Use a join table to link users to organizations. Store roles here, not on the user table.

  1. Billing and Subscriptions

Never use a simple boolean like is_active. This creates messy data.

Use a state machine for subscriptions. Common states include: • trialing • active • past_due • canceled • expired • incomplete

This allows you to handle payment failures and grace periods without manual data fixes.

  1. Money and Invoicing

Follow these strict rules to avoid financial errors:

• Never use FLOAT or REAL for money. Use integers to store cents. For example, $29.99 becomes 2999. • Use an invoice_line_items table. You need this for tax reports and refunds. • Use a usage_events table if you charge based on how much a customer uses your product.

  1. Performance Tips

• Index your foreign keys. • Index your status columns. • Index your timestamp columns. • Avoid N+1 queries. Use JOINs to get organization and subscription data in one trip.

A good schema is invisible when it works. It is catastrophic when it fails.

Build for the organization. Use state machines. Store money as integers.

Source: https://dev.to/feidou/designing-saas-database-architecture-users-organizations-subscriptions-and-billing-df

Optional learning community: https://t.me/GyaanSetuAi