Database Design for an E-commerce SaaS Platform

When we built CardDeckr, the platform needed to handle three things at once: sell physical products, manage user subscriptions, and serve dynamic content to thousands of concurrent users. The database schema ended up with 66 tables. That number makes some developers wince. But every single table exists for a reason, and the architecture still performs under load.

This is what database design looks like when you are building a real commerce platform, not a tutorial project. The schema needs to support payments, inventory, user authentication, product variants, order fulfilment, and subscription billing, all while staying queryable and maintainable as the platform grows.

If you are designing a PostgreSQL schema for an e-commerce or SaaS platform, here is what we learned building CardDeckr’s Supabase architecture from the ground up.

Why Database Architecture Matters More for Commerce Than Marketing Sites

Marketing sites like the ones we build in our Launch Sprint or Studio Site packages typically touch a database for three things: contact form submissions, CMS content updates, and analytics events. The data model is simple. A dozen tables covers everything.

E-commerce platforms are different. The database is not just storing content. It is the source of truth for financial transactions, inventory levels, user entitlements, and order history. A mistake in your schema design does not just break a page. It can double-charge customers, oversell inventory, or lose track of who paid for what.

The stakes are higher. The relationships are more complex. And you cannot iterate your way out of a bad schema once real money starts flowing through the system.

That is why we spent three weeks on CardDeckr’s data model before writing a single line of application code. We mapped every entity, every relationship, every state transition. We identified where foreign key constraints were critical and where they would create bottlenecks. We planned for scale, refunds, and edge cases from day one.

The result: a 66-table schema that handles thousands of transactions without breaking, supports complex product variants and subscription tiers, and still lets us ship new features without rewriting the database layer.

Core Schema Domains: How to Organise a Multi-Concern Platform

The biggest mistake in e-commerce database design is treating everything as one big schema. Products mixed with users mixed with payments mixed with content. It becomes unmanageable fast.

CardDeckr’s schema is organised into five core domains, each with its own set of tables and concerns:

1. Users & Authentication

This domain handles user accounts, profiles, authentication sessions, and permissions. In Supabase, the auth.users table is managed by the platform, but we extend it with our own profiles table that stores business-specific user data: display names, avatar URLs, subscription status, and permission flags.

Key tables:

• profiles, one-to-one with auth.users, stores extended user metadata
• user_roles, maps users to permission groups (admin, creator, customer)
• user_preferences, stores UI settings, notification preferences, theme choices

The authentication domain is deliberately isolated. We never join directly from auth.users to transactional tables. Instead, we reference user_id from profiles, which gives us control over soft-deletes and user migrations without touching Supabase’s internal auth schema.

2. Products & Inventory

This is where the complexity lives. CardDeckr sells physical card decks with multiple variants (edition, language, bundle options) and digital add-ons. Each product can have stock constraints, pricing tiers, and promotional discounts.

Key tables:

• products, master product catalogue (name, description, category)
• product_variants, SKU-level records (size, colour, edition)
• inventory, stock levels per warehouse per variant
• pricing_rules, tiered pricing, bulk discounts, subscription pricing
• product_images, one-to-many relationship for product photography

The critical design decision: separate inventory from products. A product is the conceptual item (“CardDeckr Starter Deck”). A product_variant is the specific SKU (“Starter Deck, English, 2nd Edition”). An inventory record tracks how many units of that SKU exist in a specific warehouse.

This separation lets us track stock at a granular level, support multiple fulfilment centres, and handle pre-orders for variants that are not yet in stock, all without cluttering the product table with warehouse-specific fields.

3. Orders & Transactions

Every purchase creates an order. Every order contains line items. Every line item references a product variant at a specific price. This domain captures the entire purchase lifecycle from cart to fulfilment.

Key tables:

• orders, top-level order record (customer, total, status, timestamps)
• order_items, line items within an order (variant, quantity, unit price)
• order_status_history, audit log of order state changes
• payment_transactions, records of payment attempts, captures, refunds
• shipments, fulfilment records linked to order items
• returns, return requests and refund processing

Notice the audit trail tables: order_status_history and payment_transactions. These are immutable append-only logs. When an order moves from “pending” to “paid” to “shipped,” we write a new row to order_status_history instead of updating the orders.status field. This gives us a complete audit trail for every order, which is critical for resolving payment disputes and tracking fulfilment issues.

The payment_transactions table is similarly append-only. Every Stripe charge, refund, or failed payment attempt gets its own row. We never update or delete payment records. We only add new ones. This ensures we can reconstruct the full payment history for any order, even if the order itself has been refunded or cancelled.

4. Subscriptions & Entitlements

CardDeckr offers monthly and annual subscriptions that unlock exclusive content and early access to new products. The subscription domain manages billing cycles, entitlements, and grace periods for lapsed payments.

Key tables:

• subscriptions, active subscription records (plan, start date, renewal date, status)
• subscription_plans, plan definitions (monthly, annual, pricing tiers)
• entitlements, what a user is entitled to based on their subscription
• billing_events, log of subscription charges, renewals, cancellations

The complexity here is grace periods and prorated refunds. When a user cancels a subscription mid-cycle, we need to calculate how much to refund based on unused days. When a user upgrades from monthly to annual, we need to prorate the remaining monthly balance and apply it to the annual fee.

All of this logic lives in the database schema. The billing_events table captures every charge, refund, and adjustment. The entitlements table tracks what the user currently has access to, which may differ from their subscription status if they are in a grace period or have lifetime access from a past promotion.

5. Content & Dynamic Data

CardDeckr serves user-generated content: card designs, collections, community resources. This domain is essentially a CMS layer within the commerce platform.

Key tables:

• content_items, user-created content (card designs, guides, templates)
• collections, curated groupings of content items
• tags, taxonomy for categorising content
• content_permissions, who can view or edit each content item
• activity_feed, user actions, likes, shares, comments

The content domain is intentionally decoupled from products. A user might subscribe to access premium content, but they can also purchase one-time access to specific collections without subscribing. The entitlements system bridges the two: “User X has access to Collection Y because they purchased Product Z.”

This separation keeps the schema flexible. We can add new content types (video tutorials, live workshops) without touching the product or subscription tables. We can also build new access models (pay-per-view, bundle access, creator revenue sharing) by modifying entitlements logic, not the core commerce schema.

Supabase as the PostgreSQL Host: Why We Chose It

CardDeckr runs on Supabase, which is Postgres-as-a-service with built-in auth, realtime subscriptions, and edge functions. We chose it for three reasons:

1. Row Level Security (RLS) built into the database layer

Supabase’s RLS policies let us define access control at the schema level. A user can only query their own orders. An admin can query all orders. A creator can only update content items they own. These rules are enforced by Postgres itself, not by application-layer guards.

This is critical for a multi-tenant platform. We do not rely on the application code to filter queries by user_id. The database does it. If a developer writes a sloppy query, RLS prevents data leaks. It is defence in depth.

2. Realtime subscriptions for live inventory updates

CardDeckr’s product pages show live stock levels. When inventory changes (someone makes a purchase, a warehouse receives a shipment), the product page updates in realtime without polling.

Supabase’s realtime engine listens to Postgres change logs and pushes updates to connected clients over WebSockets. We subscribe to changes on the inventory table, and the frontend reactively updates stock counts. No polling. No stale data. No race conditions where two users buy the last item.

3. Edge functions for payment webhooks and post-purchase workflows

Stripe sends payment confirmation webhooks. We need to capture those events, update order status, trigger fulfilment, and send confirmation emails. Supabase Edge Functions let us deploy serverless handlers that run close to the database, with sub-10ms query latency.

These functions have direct database access via the Supabase client, so we can atomically update orders, payment_transactions, and inventory in a single transaction. No external API calls. No eventually-consistent headaches.

Is 66 Tables Bloat or Discipline?

When developers see “66 tables,” they assume over-engineering. But look at the breakdown:

• Users & Auth: 8 tables (profiles, roles, sessions, preferences, permissions)
• Products & Inventory: 14 tables (products, variants, images, inventory, pricing, categories, tags)
• Orders & Transactions: 12 tables (orders, line items, payments, refunds, shipments, returns, status history)
• Subscriptions & Entitlements: 9 tables (plans, subscriptions, billing events, entitlements, grace periods)
• Content & Dynamic Data: 11 tables (content items, collections, tags, permissions, activity, comments)
• Platform & Admin: 12 tables (audit logs, feature flags, support tickets, notifications, analytics events, system config)

Each table has a job. Each domain is cohesive. The schema is not bloated. It is complete. It handles the real complexity of a commerce platform: variants, subscriptions, refunds, inventory tracking, user permissions, content access, and audit trails.

Could you build a simpler schema? Yes. But you would sacrifice data integrity, auditability, or flexibility. The 66-table design lets CardDeckr support complex business logic without hacks, workarounds, or “we’ll fix it later” technical debt.

Foreign Keys and Referential Integrity: Where to Be Strict

Every order_item must reference a valid product_variant. Every payment_transaction must reference a valid order. These are hard foreign key constraints. If the referenced record does not exist, Postgres rejects the insert. No orphaned line items. No payment records pointing to deleted orders.

But not every relationship is a hard constraint. The activity_feed table logs user actions: “User X liked Content Y.” If Content Y is later deleted, we do not want to cascade-delete the activity record. It is historical data, not transactional. So we use a soft foreign key: store the content ID, but do not enforce referential integrity with a database constraint.

The rule: hard constraints for financial and transactional data, soft constraints for historical and analytics data.

This distinction keeps the database strict where it matters (you cannot charge someone for a product that does not exist) while staying flexible for reporting and analytics (you can still see that a user liked something, even if that something is now gone).

Migration Strategy: How to Evolve a Schema When Money Is Involved

Changing a database schema after launch is risky. Especially when that database is processing live payments.

Our migration strategy for CardDeckr:

1. Write migrations as pure SQL, never use ORM auto-generation

Supabase supports migration files that run sequentially. Each migration is a SQL script with up and down operations. We write these by hand. We do not trust ORMs to generate safe schema changes.

2. Test migrations on a production-identical staging environment

We clone the production database to staging (minus PII), run the migration, run the test suite, manually verify data integrity. Only then do we apply it to production.

3. Use backwards-compatible migrations wherever possible

Instead of renaming a column, we add a new column, backfill data, update the application to use the new column, then drop the old column in a later migration. This ensures zero downtime.

4. Wrap risky migrations in transactions with savepoints

Postgres supports transactional DDL. If a migration fails halfway through, the entire transaction rolls back. The database is left in the pre-migration state, not a half-migrated broken state.

This discipline has let us ship 40+ schema changes to CardDeckr’s production database without a single outage or data corruption incident.

Indexing and Query Performance: What Actually Matters

A well-designed schema is worthless if queries take five seconds to return. CardDeckr’s database handles thousands of reads per minute. We optimise for query performance with strategic indexing.

Key indexes:

• orders(user_id) for fast lookup of user order history
• order_items(order_id) for fast joins from orders to line items
• inventory(product_variant_id, warehouse_id) for fast stock lookups
• subscriptions(user_id, status) for fast active subscription checks
• payment_transactions(order_id, status) for fast payment status queries

We also use partial indexes for common filtered queries. For example, we frequently query for active subscriptions: SELECT * FROM subscriptions WHERE status = 'active'. Instead of indexing the entire status column, we create a partial index:

CREATE INDEX idx_active_subscriptions
ON subscriptions(user_id)
WHERE status = 'active';

This index is smaller (only active subscriptions) and faster (Postgres knows it only covers rows where status = 'active').

For product catalogue queries, we use compound indexes that cover multiple filter dimensions: (category_id, availability, price). This lets us efficiently query “show me all available products in category X under £50” without scanning the full table.

The result: 95th percentile query latency stays under 50ms, even during peak traffic.

When to Build Your Own Schema vs. Use a Commerce Platform

Most businesses should not build their own e-commerce database. Shopify, WooCommerce, and BigCommerce exist for a reason. They handle the schema complexity, payment processing, and compliance headaches for you.

But if you are building a hybrid platform that combines commerce, SaaS, and content, you need custom schema design. Off-the-shelf platforms cannot model the relationships between products, subscriptions, and user-generated content that CardDeckr requires.

When to go custom:

• Your product catalogue includes complex variants or configurators
• You need subscription logic beyond “monthly or annual”
• You are building a marketplace or multi-vendor platform
• You need fine-grained access control and user permissions
• Your business model does not fit Shopify’s data model

When to use an existing platform:

• You are selling straightforward physical or digital products
• Your subscription needs are simple (one plan, recurring billing)
• You do not need custom entitlements or access logic
• You want to launch fast and iterate on product-market fit

For CardDeckr, a custom Supabase schema was the right call. The platform’s business model (hybrid e-commerce + SaaS + content) could not fit into Shopify’s constraints. Building the schema ourselves gave us full control over the data model, query performance, and future extensibility.

If you are evaluating whether your platform needs custom database design, Fernside Studio offers custom software and SaaS builds where we architect, design, and deploy purpose-built systems on Postgres, Supabase, or other managed database platforms. We also build e-commerce storefronts for businesses that need custom product logic, inventory management, or integration with existing ERP systems.

Final Thoughts: Schema Design Is Product Design

Your database schema is not just technical infrastructure. It is a model of your business logic. Every table, every relationship, every constraint reflects a decision about how your platform works.

CardDeckr’s 66-table schema exists because the platform is solving 66 distinct problems: user authentication, product variants, inventory tracking, payment processing, subscription billing, content permissions, order fulfilment, returns, refunds, analytics, and more.

If your schema feels too complex, the question is not “how do we simplify the database?” The question is “does our product actually need to be this complex?” If the answer is yes, then your schema should reflect that complexity. If the answer is no, then simplify the product, and the schema will follow.

At Fernside Studio, we design schemas that match the product requirements, no more, no less. We do not add tables for theoretical future features. We do not over-normalise to the point where simple queries require six joins. We build schemas that are correct, queryable, and maintainable as the platform grows.

If you are building an e-commerce or SaaS platform and need schema design, database architecture, or full-stack development, get in touch. We have built commerce platforms, subscription systems, and multi-tenant SaaS products for clients across the UK. We know what works, what scales, and what breaks under load.

Good database design is not sexy. But it is the difference between a platform that processes millions in transactions reliably and one that collapses under its first big sale.