Database Schema Design for MVPs

Purpose

This recipe produces a complete PostgreSQL database schema tailored to your business model — SaaS, marketplace, or ecommerce — with migration files, Row Level Security policies, indexes, and a schema evolution strategy. [src1]

Prerequisites

• Business model identified — SaaS, marketplace, ecommerce, or internal tool
• Core entities listed — users, products, orders, subscriptions, etc.
• Database platform selected — Supabase (recommended)
• Supabase CLI installed — npm install -g supabase
• Project scaffold exists — from Coded MVP Architecture Templates

Constraints

• Enable RLS on every user-facing table — tables without RLS expose all data. [src1]
• Use UUIDs as primary keys — avoids enumeration attacks and simplifies multi-environment syncing. [src2]
• Add created_at and updated_at to every table. [src4]
• Never alter production without a migration file. [src6]
• Index foreign keys and frequently filtered columns from day one. [src4]

Tool Selection Decision

Which schema pattern?
├── SaaS (B2B multi-tenant)
│   └── SCHEMA A: Shared schema with tenant_id + RLS
├── SaaS (B2C single-tenant)
│   └── SCHEMA B: User-scoped tables with user_id + RLS
├── Marketplace (two-sided)
│   └── SCHEMA C: Vendors + buyers + listings + transactions
├── Ecommerce (single vendor)
│   └── SCHEMA D: Products + carts + orders + payments
└── Internal tool
    └── SCHEMA E: Users + core entity + audit log

Execution Flow

Step 1: Initialize Migration Infrastructure

Duration: 5 minutes · Tool: Supabase CLI

Run supabase init and supabase start to create local development environment with migrations directory.

Verify: supabase status shows running instance. · If failed: Ensure Docker is running.

Step 2: Create Core Schema

Duration: 15-25 minutes · Tool: SQL Editor

Create tables matching your business model pattern. B2B SaaS uses organizations + profiles + items with tenant_id scoping. Marketplace adds vendors + listings + orders + reviews. Ecommerce uses products + carts + orders + order_items. [src2] [src5]

Verify: All tables created without errors. · If failed: Check SQL syntax and auth.users reference.

Step 3: Apply Row Level Security

Duration: 10 minutes · Tool: SQL Editor

Enable RLS on all user-facing tables. Create policies for SELECT, INSERT, UPDATE, DELETE scoped to user/organization membership. [src1]

Verify: Test with two users — user A cannot see user B's data. · If failed: Check RLS is enabled and policies reference auth.uid() correctly.

Step 4: Create Functions and Triggers

Duration: 10 minutes · Tool: SQL Editor

Add auto-profile creation on signup, updated_at triggers, and business logic functions (inventory management, etc.).

Verify: Create test user, confirm profile auto-created. · If failed: Check trigger uses SECURITY DEFINER to bypass RLS.

Step 5: Apply Migrations and Seed Data

Duration: 5 minutes · Tool: Supabase CLI

Run supabase db reset to apply all migrations. Add seed data in supabase/seed.sql. Push to remote with supabase db push. [src6]

Verify: Reset completes without errors; all tables exist in Studio. · If failed: Run migrations individually to isolate failing statement.

Step 6: Set Up Migration Workflow

Duration: 5 minutes · Tool: Git + Supabase CLI

Establish the change-diff-test-commit-push workflow. Use supabase db diff to generate migrations from local changes. Commit migration files to Git. Deploy with supabase db push. [src1]

Verify: supabase db reset applies all migrations cleanly. · If failed: Use supabase migration repair to fix state.

Output Schema

{
  "output_type": "database_schema",
  "format": "SQL migration files",
  "columns": [
    {"name": "migration_files", "type": "string", "description": "Ordered SQL files in supabase/migrations/", "required": true},
    {"name": "table_count", "type": "number", "description": "Number of tables created", "required": true},
    {"name": "rls_policies", "type": "number", "description": "Number of RLS policies", "required": true},
    {"name": "indexes", "type": "number", "description": "Number of indexes", "required": true},
    {"name": "business_model", "type": "string", "description": "Schema pattern used", "required": true}
  ],
  "expected_row_count": "3-6 migration files",
  "sort_order": "chronological",
  "deduplication_key": "migration_files"
}

Quality Benchmarks

If below minimum: Review each table for missing RLS. Add indexes on WHERE-clause columns. Test with supabase db reset.

Error Handling

Cost Breakdown

Anti-Patterns

Wrong: Starting without Row Level Security

Deploying without RLS means the public anon key can access any table data — the most common Supabase security vulnerability. [src1]

Correct: Enable RLS in the same migration that creates the table

Write policies before application code.

Wrong: Using auto-incrementing integer IDs

Sequential IDs expose data volume and enable enumeration attacks. [src2]

Correct: Use UUIDs as primary keys

gen_random_uuid() produces globally unique, non-sequential identifiers.

Wrong: Storing prices as decimal/float

Floating-point arithmetic causes rounding errors in financial calculations. [src7]

Correct: Store prices as integer cents

Use INTEGER for price_cents. Format for display only at the presentation layer.

When This Matters

Use this recipe when a developer needs to design the database schema for an MVP, choosing the right pattern for their business model. Requires a tech stack decision and project scaffold. Handles schema design, RLS, migrations, and evolution — not application code.