---
# === IDENTITY ===
id: software/mvp-development/database-schema-design-for-mvps/2026
canonical_question: "What are common MVP database schemas by business model — SaaS, marketplace, ecommerce with migration strategy?"
aliases:
  - "MVP database schema patterns for SaaS startups"
  - "PostgreSQL schema design for marketplace MVP"
  - "Ecommerce database schema for MVP with Supabase"
  - "Database migration strategy for early-stage startups"
  - "Multi-tenant database design for MVP"
entity_type: execution_recipe
domain: software > mvp-development > database schema design for MVPs
region: global
jurisdiction: global
temporal_scope: 2025-2026

# === VERIFICATION ===
last_verified: 2026-03-12
confidence: 0.90
version: 1.0
first_published: 2026-03-12

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: evolving
  last_breaking_change: "Supabase introduced declarative schema management in 2025; Drizzle ORM reached v1.0 stability as a Prisma alternative"
  next_review: 2026-09-08
  change_sensitivity: high

# === CONSTRAINTS ===
constraints:
  - "PostgreSQL 15+ recommended for JSON path queries, MERGE statement, and improved partition performance"
  - "Supabase migrations use supabase/migrations directory — never edit production database directly without a migration file"
  - "Row Level Security must be enabled on all user-facing tables in Supabase — tables without RLS are accessible to all authenticated users"
  - "Schema changes on production require down-migration strategy — never assume you can drop columns without data loss"
  - "Foreign key constraints are essential for data integrity but slow bulk inserts — use deferred constraints for batch operations"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "User needs to choose a database technology, not design a schema"
    use_instead: "Search knowledgelib.io for MVP tech stack selection — no dedicated unit yet"
  - condition: "User needs a complete project scaffold, not just database design"
    use_instead: "software/mvp-development/coded-mvp-architecture-templates/2026"
  - condition: "User needs enterprise-scale multi-tenant architecture"
    use_instead: "software/system-design/multi-tenant-saas/2026"

# === AGENT HINTS ===
inputs_needed:
  - key: business_model
    question: "What business model is the MVP?"
    type: choice
    options: ["SaaS (B2B or B2C)", "marketplace (two-sided)", "ecommerce (single vendor)", "internal tool", "API-first product"]
  - key: database_platform
    question: "Which database platform?"
    type: choice
    options: ["Supabase (PostgreSQL)", "Firebase (Firestore)", "PostgreSQL (self-managed)", "no preference"]
  - key: multi_tenancy
    question: "Does the app need multi-tenancy?"
    type: choice
    options: ["no — single-tenant", "yes — shared schema with tenant_id", "yes — schema-per-tenant", "unsure"]
  - key: expected_scale
    question: "Expected scale at MVP launch?"
    type: choice
    options: ["< 100 users", "100-1000 users", "1000-10000 users", "> 10000 users"]

# === EXECUTION METADATA ===
execution:
  required_inputs:
    - name: "Business model and core entities"
      source: "founder/product manager"
      format: "list of core objects (users, products, orders, etc.)"
    - name: "Tech stack decision"
      source: "software/mvp-development/mvp-tech-stack-selection-guide/2026"
      format: "selected database platform"
  outputs:
    - name: "Database schema SQL"
      format: "SQL migration files"
      description: "Complete CREATE TABLE statements with indexes, RLS policies, and seed data"
    - name: "Entity relationship diagram"
      format: "text or image"
      description: "Visual representation of tables and relationships"
  tools_required:
    - name: "Supabase"
      purpose: "Managed PostgreSQL with built-in auth, RLS, and migration tools"
      tier: "free"
      cost: "$0 (free tier) to $25/mo (Pro)"
      alternatives: ["Railway PostgreSQL", "Neon", "PlanetScale"]
    - name: "Supabase CLI"
      purpose: "Local development, migration generation, and schema diffing"
      tier: "free"
      cost: "$0"
      alternatives: ["Prisma Migrate", "Drizzle Kit", "Knex migrations"]
  credentials_needed:
    - service: "Supabase"
      type: "Project URL + service role key"
      where_to_get: "https://supabase.com/dashboard"
      free_tier_limits: "500 MB database, 2 projects"
  estimated_duration: "30-60 minutes for initial schema + migrations"
  estimated_cost: "$0 (Supabase free tier)"

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/mvp-development/database-schema-design-for-mvps/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-03-12)"

# === RELATED UNITS ===
related_kos:
  depends_on:
    - id: "software/mvp-development/coded-mvp-architecture-templates/2026"
      label: "Project scaffold before schema design"
  feeds_into:
    - id: "software/mvp-development/authentication-authorization-setup/2026"
      label: "Auth tables and user profiles"
    - id: "software/mvp-development/payment-integration-setup/2026"
      label: "Payment and subscription tables"
  related_to:
    - id: "software/mvp-development/ai-assisted-code-generation-workflow/2026"
      label: "Use AI to generate schema SQL"
  alternative_to: []

# === SOURCES ===
sources:
  - id: src1
    title: "Database Migrations — Supabase Docs"
    author: Supabase
    url: https://supabase.com/docs/guides/deployment/database-migrations
    type: official_docs
    published: 2025-01-01
    reliability: authoritative
  - id: src2
    title: "Designing Your Postgres Database for Multi-tenancy"
    author: Crunchy Data
    url: https://www.crunchydata.com/blog/designing-your-postgres-database-for-multi-tenancy
    type: technical_blog
    published: 2024-06-01
    reliability: high
  - id: src3
    title: "Choose the Right PostgreSQL Data Access Pattern for Your SaaS Application"
    author: AWS
    url: https://aws.amazon.com/blogs/database/choose-the-right-postgresql-data-access-pattern-for-your-saas-application/
    type: official_docs
    published: 2024-09-01
    reliability: authoritative
  - id: src4
    title: "Designing your SaaS Database for Scale with Postgres"
    author: Citus Data
    url: https://www.citusdata.com/blog/2016/10/03/designing-your-saas-database-for-high-scalability/
    type: technical_blog
    published: 2024-01-01
    reliability: high
  - id: src5
    title: "E-commerce PostgreSQL Database Schema"
    author: Larbisahli
    url: https://github.com/larbisahli/e-commerce-database-schema
    type: open_source
    published: 2024-01-01
    reliability: high
  - id: src6
    title: "Local Development with Schema Migrations — Supabase Docs"
    author: Supabase
    url: https://supabase.com/docs/guides/local-development/overview
    type: official_docs
    published: 2025-01-01
    reliability: authoritative
  - id: src7
    title: "Building a PostgreSQL Database for E-commerce"
    author: DEV Community
    url: https://dev.to/dbvismarketing/building-a-postgresql-database-for-e-commerce-96o
    type: technical_blog
    published: 2024-08-01
    reliability: high
---

# 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. The output is a set of SQL migration files ready to apply to Supabase or any PostgreSQL instance, plus a migration workflow for iterating on the schema as the product evolves. [src1]

## Prerequisites

- [ ] **Business model identified** — SaaS, marketplace, ecommerce, or internal tool
- [ ] **Core entities listed** — users, products, orders, subscriptions, etc.
- [ ] **Database platform selected** — Supabase (recommended), Railway PostgreSQL, or self-managed
- [ ] **Supabase CLI installed** — `npm install -g supabase` ([docs](https://supabase.com/docs/guides/local-development/cli/getting-started))
- [ ] **Project scaffold exists** — from [Coded MVP Architecture Templates](/software/mvp-development/coded-mvp-architecture-templates/2026)

## Constraints

- Enable Row Level Security on every user-facing table in Supabase — tables without RLS expose all data to any authenticated user. [src1]
- Use UUIDs (not auto-incrementing integers) as primary keys — avoids enumeration attacks and simplifies multi-environment syncing. [src2]
- Add `created_at` and `updated_at` timestamps to every table — essential for debugging, auditing, and sync operations. [src4]
- Never alter production tables without a migration file — use `supabase db diff` to generate migration SQL from local changes. [src6]
- Index foreign keys and frequently filtered columns from day one — missing indexes on tenant_id or user_id cause 10-100x query slowdowns at scale. [src4]

## Tool Selection Decision

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

| Schema | Tables | Complexity | Multi-tenancy | Best For |
|--------|--------|------------|---------------|----------|
| A: B2B SaaS | 8-12 | Medium | tenant_id on all tables | Team-based products, dashboards |
| B: B2C SaaS | 5-8 | Low | user_id scoping | Consumer apps, personal tools |
| C: Marketplace | 10-15 | High | vendor_id + buyer_id | Two-sided platforms |
| D: Ecommerce | 8-12 | Medium | None (single vendor) | Online stores, DTC brands |
| E: Internal tool | 4-6 | Low | Optional | Admin panels, data tools |

## Execution Flow

### Step 1: Initialize Migration Infrastructure

**Duration**: 5 minutes
**Tool**: Supabase CLI

```bash
# Initialize Supabase locally
supabase init

# Start local Supabase instance
supabase start

# This creates:
# supabase/
#   config.toml        # Local Supabase config
#   migrations/         # SQL migration files (version-controlled)
#   seed.sql           # Seed data for development

# Verify local instance is running
supabase status
# Should show: DB URL, Studio URL, API URL, etc.
```

**Verify**: `supabase status` shows a running local instance with a DB URL.
**If failed**: Ensure Docker is running. Run `supabase stop` then `supabase start` to reset.

### Step 2: Create Core Schema

**Duration**: 15-25 minutes
**Tool**: SQL Editor (Supabase Studio or psql)

**Schema A — B2B SaaS (multi-tenant):** [src2] [src4]

```sql
-- supabase/migrations/001_core_schema.sql

-- Organizations (tenants)
CREATE TABLE organizations (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  plan TEXT DEFAULT 'free' CHECK (plan IN ('free', 'starter', 'pro', 'enterprise')),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- User profiles (extends auth.users)
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  org_id UUID REFERENCES organizations(id) ON DELETE SET NULL,
  full_name TEXT,
  role TEXT DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Core entity (customize per product — e.g., projects, documents, tickets)
CREATE TABLE items (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  created_by UUID REFERENCES auth.users(id),
  title TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'archived', 'deleted')),
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Indexes
CREATE INDEX idx_profiles_org_id ON profiles(org_id);
CREATE INDEX idx_items_org_id ON items(org_id);
CREATE INDEX idx_items_created_by ON items(created_by);
CREATE INDEX idx_items_status ON items(status);

-- Updated_at trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at BEFORE UPDATE ON organizations
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER set_updated_at BEFORE UPDATE ON profiles
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER set_updated_at BEFORE UPDATE ON items
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();
```

**Schema C — Marketplace:** [src5]

```sql
-- Vendors (sellers)
CREATE TABLE vendors (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  store_name TEXT NOT NULL,
  description TEXT,
  verified BOOLEAN DEFAULT false,
  commission_rate DECIMAL(5,2) DEFAULT 10.00,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Listings
CREATE TABLE listings (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  vendor_id UUID NOT NULL REFERENCES vendors(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  price_cents INTEGER NOT NULL CHECK (price_cents > 0),
  currency TEXT DEFAULT 'usd',
  status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'sold', 'archived')),
  images JSONB DEFAULT '[]',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Orders
CREATE TABLE orders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  buyer_id UUID NOT NULL REFERENCES auth.users(id),
  listing_id UUID NOT NULL REFERENCES listings(id),
  vendor_id UUID NOT NULL REFERENCES vendors(id),
  amount_cents INTEGER NOT NULL,
  platform_fee_cents INTEGER NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'refunded', 'cancelled')),
  stripe_payment_intent_id TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Reviews
CREATE TABLE reviews (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  reviewer_id UUID NOT NULL REFERENCES auth.users(id),
  vendor_id UUID NOT NULL REFERENCES vendors(id),
  rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);
```

**Schema D — Ecommerce:** [src5] [src7]

```sql
-- Products
CREATE TABLE products (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  description TEXT,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  compare_at_price_cents INTEGER,
  currency TEXT DEFAULT 'usd',
  sku TEXT UNIQUE,
  inventory_count INTEGER DEFAULT 0,
  status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'archived')),
  images JSONB DEFAULT '[]',
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Cart items
CREATE TABLE cart_items (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  quantity INTEGER NOT NULL DEFAULT 1 CHECK (quantity > 0),
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(user_id, product_id)
);

-- Orders
CREATE TABLE orders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'processing', 'shipped', 'delivered', 'refunded', 'cancelled')),
  subtotal_cents INTEGER NOT NULL,
  tax_cents INTEGER DEFAULT 0,
  shipping_cents INTEGER DEFAULT 0,
  total_cents INTEGER NOT NULL,
  shipping_address JSONB,
  stripe_payment_intent_id TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Order line items
CREATE TABLE order_items (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price_cents INTEGER NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);
```

**Verify**: Tables created without errors. Run `\dt` in psql or check Supabase Studio Table Editor.
**If failed**: Check for syntax errors in SQL. Ensure `auth.users` table exists (Supabase creates it automatically). For self-managed PostgreSQL, create a users table first.

### Step 3: Apply Row Level Security

**Duration**: 10 minutes
**Tool**: SQL Editor

```sql
-- supabase/migrations/002_rls_policies.sql

-- Enable RLS on all tables
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE items ENABLE ROW LEVEL SECURITY;

-- Profiles: users can read/update their own profile
CREATE POLICY "Users can view own profile"
  ON profiles FOR SELECT
  USING (id = auth.uid());

CREATE POLICY "Users can update own profile"
  ON profiles FOR UPDATE
  USING (id = auth.uid());

-- Items: users can access items in their organization
CREATE POLICY "Org members can view items"
  ON items FOR SELECT
  USING (
    org_id IN (
      SELECT org_id FROM profiles WHERE id = auth.uid()
    )
  );

CREATE POLICY "Org members can create items"
  ON items FOR INSERT
  WITH CHECK (
    org_id IN (
      SELECT org_id FROM profiles WHERE id = auth.uid()
    )
  );

CREATE POLICY "Org members can update items"
  ON items FOR UPDATE
  USING (
    org_id IN (
      SELECT org_id FROM profiles WHERE id = auth.uid()
    )
  );

-- Items: only owners/admins can delete
CREATE POLICY "Admins can delete items"
  ON items FOR DELETE
  USING (
    org_id IN (
      SELECT org_id FROM profiles
      WHERE id = auth.uid() AND role IN ('owner', 'admin')
    )
  );
```

**Verify**: Test with two different users — user A should not see user B's data. Use Supabase SQL Editor with `SET LOCAL ROLE authenticated; SET request.jwt.claims = '{"sub": "user-uuid"}'` to simulate.
**If failed**: Check that RLS is enabled (`SELECT tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public'`). Verify policy conditions reference `auth.uid()` correctly.

### Step 4: Create Database Functions and Triggers

**Duration**: 10 minutes
**Tool**: SQL Editor

```sql
-- supabase/migrations/003_functions.sql

-- Auto-create profile on user signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO profiles (id, full_name, avatar_url)
  VALUES (
    NEW.id,
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'avatar_url'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

-- Decrement inventory on order creation (ecommerce)
CREATE OR REPLACE FUNCTION decrement_inventory()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE products
  SET inventory_count = inventory_count - NEW.quantity
  WHERE id = NEW.product_id
  AND inventory_count >= NEW.quantity;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Insufficient inventory for product %', NEW.product_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```

**Verify**: Create a new auth user and confirm a profile row is auto-created in the profiles table.
**If failed**: Check the trigger is on `auth.users`, not `public.users`. Ensure the function has `SECURITY DEFINER` to bypass RLS during trigger execution.

### Step 5: Apply Migrations and Seed Data

**Duration**: 5 minutes
**Tool**: Supabase CLI

```bash
# Apply all migrations to local database
supabase db reset

# Add seed data for development
cat > supabase/seed.sql << 'EOF'
-- Seed organization
INSERT INTO organizations (id, name, slug, plan)
VALUES ('00000000-0000-0000-0000-000000000001', 'Demo Company', 'demo', 'pro');

-- Seed will auto-create profile via trigger when auth user is created
-- Use Supabase Dashboard to create test users
EOF

# Push migrations to remote Supabase project
supabase db push

# Or generate a diff from local changes
supabase db diff --schema public -f my_changes
```

**Verify**: Run `supabase db reset` without errors. Check all tables exist in Supabase Studio.
**If failed**: Review migration SQL for syntax errors. Run migrations one at a time to isolate the failing statement.

### Step 6: Set Up Migration Workflow

**Duration**: 5 minutes
**Tool**: Git + Supabase CLI

```bash
# Development workflow for schema changes:

# 1. Make changes in Supabase Studio (local) or edit SQL directly
# 2. Generate migration from changes
supabase db diff --schema public -f descriptive_name

# 3. Review the generated migration
cat supabase/migrations/*_descriptive_name.sql

# 4. Test migration
supabase db reset  # Reapplies all migrations from scratch

# 5. Commit migration
git add supabase/migrations/
git commit -m "Add [table/column/index]: [description]"

# 6. Deploy to production
supabase db push
```

**Verify**: `supabase db reset` applies all migrations cleanly. Schema matches between local and remote.
**If failed**: If migrations conflict, use `supabase migration repair` to fix state. Never manually edit applied migration files — create new migration files instead.

## Output Schema

```json
{
  "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 applied", "required": true},
    {"name": "indexes", "type": "number", "description": "Number of indexes created", "required": true},
    {"name": "business_model", "type": "string", "description": "Schema pattern used (SaaS/marketplace/ecommerce)", "required": true}
  ],
  "expected_row_count": "3-6 migration files",
  "sort_order": "chronological",
  "deduplication_key": "migration_files"
}
```

## Quality Benchmarks

| Quality Metric | Minimum Acceptable | Good | Excellent |
|---------------|-------------------|------|-----------|
| RLS coverage | All user-facing tables | + service role bypass documented | + policy unit tests |
| Foreign key integrity | All relationships have FK constraints | + ON DELETE actions defined | + deferred constraints for batch ops |
| Index coverage | Primary keys + foreign keys | + frequently filtered columns | + composite indexes for common queries |
| Migration reversibility | Can reset from scratch | + down migrations for each up | + tested rollback procedures |
| Naming consistency | snake_case throughout | + prefix convention (idx_, trg_) | + documented naming guide |

**If below minimum**: Review each table for missing RLS policies. Add indexes on any column used in WHERE clauses. Test migrations with `supabase db reset`.

## Error Handling

| Error | Likely Cause | Recovery Action |
|-------|-------------|----------------|
| `relation "auth.users" does not exist` | Running outside Supabase (no auth schema) | Use Supabase local dev or create a users table manually |
| `permission denied for table` | RLS enabled but no matching policy | Add a policy for the current user's role/context |
| `duplicate key value violates unique constraint` | Seed data conflicts with existing rows | Use `ON CONFLICT DO NOTHING` in seed SQL |
| `supabase db push` fails with conflict | Remote has changes not in local migrations | Run `supabase db pull` to sync, then rebase migrations |
| Slow queries after launch | Missing indexes on filtered columns | Run `EXPLAIN ANALYZE` on slow queries, add composite indexes |
| `column "X" cannot be dropped because other objects depend on it` | Views or functions reference the column | Drop dependent objects first, recreate after column change |

## Cost Breakdown

| Component | Free Tier | Pro ($25/mo) | Scale |
|-----------|-----------|--------------|-------|
| Supabase database | 500 MB, 2 projects | 8 GB, unlimited projects | Custom |
| Supabase local dev | Free (Docker) | Free | Free |
| Migration tooling (CLI) | Free | Free | Free |
| Database monitoring | Supabase Dashboard | + pg_stat_statements | Datadog/pganalyze ($50+/mo) |
| **Total** | **$0** | **$25/mo** | **$75+/mo** |

## Anti-Patterns

### Wrong: Starting without Row Level Security
Deploying a Supabase app without RLS means the anon key (which is public) can access any table data. This is the most common security vulnerability in Supabase MVPs. [src1]

### Correct: Enable RLS on every table as part of schema creation
Add `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` in the same migration that creates the table. Write policies before writing application code.

### Wrong: Using auto-incrementing integer IDs
Sequential IDs expose your data volume (user #47 knows there are at most 46 other users), enable enumeration attacks, and complicate multi-environment data merging. [src2]

### Correct: Use UUIDs as primary keys
`gen_random_uuid()` produces globally unique, non-sequential identifiers. The 16-byte storage cost is negligible compared to the security and portability benefits.

### Wrong: Storing prices as decimal/float
Floating-point arithmetic introduces rounding errors in financial calculations. $19.99 + $5.01 may not equal $25.00 with float types. [src7]

### Correct: Store prices as integer cents
Use `INTEGER` for `price_cents` (1999 = $19.99). Perform all arithmetic on integers. Format for display only at the presentation layer.

### Wrong: Editing production database schema directly
Manual `ALTER TABLE` commands in production bypass the migration system, creating drift between environments and making rollbacks impossible. [src6]

### Correct: All schema changes through migration files
Use `supabase db diff` to generate SQL, review it, commit to Git, then `supabase db push` to deploy.

## 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 already made and a project scaffold in place. This recipe handles schema design, RLS, migrations, and evolution strategy — not application code or API routes.

## Related Units

- [Coded MVP Architecture Templates](/software/mvp-development/coded-mvp-architecture-templates/2026) — scaffold project before schema design
- [Authentication & Authorization Setup](/software/mvp-development/authentication-authorization-setup/2026) — auth tables and user profiles
- [Payment Integration Setup](/software/mvp-development/payment-integration-setup/2026) — payment and subscription tables
- [AI-Assisted Code Generation Workflow](/software/mvp-development/ai-assisted-code-generation-workflow/2026) — use AI to generate schema SQL
- [MVP Tech Stack Selection Guide](/software/mvp-development/mvp-tech-stack-selection-guide/2026) — choose database platform