What This Template Covers
The database schema starter template covers seven core table groups that most SaaS products need from day one. The identity tables cover users and authentication. The user record in your database links to the authentication provider (Clerk, Auth0, Supabase Auth) and stores the product-specific user attributes that the auth provider does not hold. The organisation tables cover the tenant model. Most B2B SaaS products are built on organisations: groups of users that share data and a subscription. The organisation and membership tables define this model. The subscription tables cover billing state. These tables track what plan each organisation is on, when their subscription was created and renewed, and what features they have access to. They are kept in sync with Stripe through webhook handlers. The permission tables cover role-based access control. Most SaaS products need at least admin and member roles within an organisation, with different permissions for each. The audit log tables cover who did what and when. Audit logs are essential for debugging, for customer trust, for GDPR accountability, and for regulatory compliance in sectors like fintech and healthtech. The vector storage tables cover the embedding storage needed for AI features. As most modern SaaS products incorporate AI, having a well-designed vector storage schema from the start prevents the need to add it later in a way that does not integrate cleanly with the rest of the data model. The soft-delete and GDPR patterns section covers the column-level and query-level patterns that make GDPR data subject requests manageable.
How to Use This Template Step by Step
Step one: review the core tables and confirm they match your product's tenant model. Most B2B SaaS products use the organisations-plus-memberships model in this template. If your product is purely consumer-facing with no organisation concept, you can simplify by removing the organisations and memberships tables and attaching subscriptions directly to users. Step two: customise the users table. The template includes the universal fields (id, email, created_at, updated_at, deleted_at). Add the product-specific user fields your application needs. Common additions: display name, avatar URL, onboarding status, notification preferences, and last active timestamp. Do not store authentication credentials in this table. Those belong with the auth provider. Step three: review the organisations table and add your product-specific organisation fields. Common additions: plan tier, billing email (if different from the organisation owner's email), company size, industry, and onboarding completion status. Step four: implement the memberships table. This is the many-to-many join table between users and organisations. It should include: user_id, organisation_id, role (the user's role within this organisation), invited_by (which user invited them), invited_at, accepted_at, and the standard timestamps. The combination of user_id and organisation_id should have a unique constraint to prevent duplicate memberships. Step five: implement the subscriptions table. This stores the current subscription state for each organisation. Required fields: organisation_id, stripe_customer_id, stripe_subscription_id, status (the Stripe subscription status: active, past_due, canceled, etc.), plan (your internal plan identifier), current_period_start, current_period_end, and cancel_at_period_end flag. The stripe_subscription_id should be a unique index. Step six: implement the audit_logs table. Required fields: id, organisation_id, user_id, action (a categorised string like USER_INVITED or DOCUMENT_DELETED), target_type (the entity type being acted on), target_id (the entity ID), metadata (a JSON field for action-specific context), ip_address, user_agent, and created_at. Audit logs should be append-only: never update or delete an audit log record. Step seven: add vector storage tables if the product uses AI features. The documents table stores source documents with their content and metadata. The document_chunks table stores the chunked versions with their embedding vectors. Use pgvector for PostgreSQL-native vector storage and similarity search. Step eight: implement row-level security for multi-tenant isolation. Enable RLS on all tables that contain tenant-specific data and create policies that restrict SELECT, INSERT, UPDATE, and DELETE operations to data belonging to the current tenant context.
Section-by-Section Walkthrough
The users table has one design decision that teams frequently get wrong: how to handle the relationship between the user record in your database and the user record in your auth provider. The correct pattern is to store the auth provider's user ID as a foreign key in your users table (typically as clerk_user_id or auth0_user_id). This allows you to look up your user record from the auth provider's ID on each authenticated request, and it means your auth provider is the authoritative source for authentication state while your database is the authoritative source for application state. The memberships table is where permission logic lives. The role field should be an enum with the roles your product supports. Start with a simple model: owner (can transfer ownership, manage billing, invite admins), admin (can manage users and settings, cannot manage billing), and member (standard product access). Add more granular roles only when users ask for them, not in anticipation. The subscriptions table needs careful design around the status transitions. A subscription can move through states: trialing, active, past_due, canceled, and unpaid. Your application code needs to handle each state and determine what features are accessible in each state. Define this logic once in a central permissions module and reference it throughout the codebase, rather than checking subscription status in individual feature checks. The audit_logs table should be treated as immutable infrastructure. Use a separate database user with INSERT-only permissions on this table if possible. The action field should use a consistent taxonomy: ENTITY_ACTION format (USER_INVITED, SUBSCRIPTION_UPGRADED, DOCUMENT_DELETED) makes querying and filtering audit logs much more manageable. The metadata JSON field should follow a consistent structure per action type: document that structure in a comment or schema file alongside the migrations. The vector tables need to account for content lifecycle management. When a source document is updated or deleted, the associated chunks and embeddings need to be updated or deleted. Build cascade delete constraints and update triggers into the schema from the start, not as an afterthought.
Common Mistakes This Template Prevents
The most common schema mistake is attaching subscriptions to users rather than organisations. In a B2B product, the subscription belongs to the company, not to the individual user. When users leave a company or change email addresses, the subscription should remain with the organisation. This template's organisation-centric subscription model prevents the billing logic bugs that arise from user-centric subscription models. The second common mistake is not including soft-delete from the start. Teams add hard delete initially for simplicity, then realise mid-project that they need to maintain deleted records for audit purposes, GDPR subject access requests, or referential integrity. Adding soft-delete after launch requires a data migration and changes to every query in the codebase. Including the deleted_at column from the start costs almost nothing. The third mistake is not having a consistent ID strategy. Mix of UUID, auto-increment integer, and CUID across different tables creates an inconsistent API surface and makes cross-table joins confusing. Choose one ID strategy (UUIDs are recommended for SaaS products because they are non-sequential and can be generated client-side) and apply it consistently. The fourth mistake is building audit logging as an afterthought. Audit logs added after the product is built are always incomplete: they cover the events the team remembered to instrument, not all events that should be recorded. Starting with the audit_logs table and instrumenting every write operation from day one produces a complete record.
Customisation Tips for Different Project Types
For AI-first SaaS products, expand the vector storage schema with a prompts table (storing versioned system prompts with their evaluation results), a model_responses table (storing AI responses with their input hash for caching), and an evaluations table (storing automated quality evaluation scores for AI outputs). These tables support the monitoring and improvement workflow that keeps AI product quality high over time. For products serving enterprises with strict data requirements, add a data_residency column to the organisations table that records which region the organisation's data should be stored in. This is relevant for UK GDPR and EU GDPR compliance when you host data in multiple regions. If different tables are stored in different regions based on data residency, document this clearly in the architecture documentation. For fintech products operating under FCA oversight, the audit_logs table should be extended to capture the fields required under FCA SYSC data retention rules: exact timestamp to millisecond precision, the system component that generated the event, and a cryptographic hash of the log entry to detect tampering. Consider using an append-only audit log store separate from the main database. For healthtech products using NHS data or operating under CQC oversight, the schema needs to address clinical data standards separately from product data. Personal health data should be stored in tables with explicit data classification labels, stricter access controls, and extended retention periods. MHRA requirements may apply if the product assists in clinical decision-making.