Database Schema

The kit uses 18 Supabase tables organized into four domains: catalog, commerce, users, and marketing. All tables have row-level security (RLS) enabled.

Schema Overview

Catalog:        products, product_variants, product_images, categories,
                subcategories, collections, collection_products, product_bundles

Commerce:       orders, order_items, order_notes, cart_items, wishlists,
                shipping_methods, return_requests

Users:          profiles, addresses

Marketing:      discount_codes, reviews

Tables

products

The core product table. Every storefront query starts here.

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	Product name
slug	text (unique)	URL-friendly identifier
description	text	Short description
long_description	text	Detailed product description
features	text[]	Feature bullet points
brand	text	Brand name
price	numeric	Regular price
sale_price	numeric	Sale price (nullable)
category_id	uuid (FK)	References categories.id
subcategory	text	Subcategory name
tags	text[]	Searchable tags
status	text	active, draft, or archived
stock	integer	Available quantity
sku	text	Stock keeping unit
specs	jsonb	Product specifications
rating	numeric	Average rating (auto-calculated by trigger)
review_count	integer	Total reviews (auto-calculated by trigger)

product_variants

Size and color options for each product.

Column	Type	Notes
id	uuid (PK)
product_id	uuid (FK)	References products.id
name	text	Display name (e.g., "Small", "Red")
type	text	size or color
value	text	The variant value
stock	integer	Variant-specific stock
price_adjustment	numeric	Price delta from base

product_images

Multiple images per product with ordering.

Column	Type	Notes
id	uuid (PK)
product_id	uuid (FK)	References products.id
url	text	Image URL (Supabase Storage or external)
alt	text	Alt text for accessibility
position	integer	Display order
is_primary	boolean	Primary image flag

orders

Created by the Stripe webhook after successful payment.

Column	Type	Notes
id	uuid (PK)
order_number	text (unique)	Human-readable order number
user_id	uuid (FK)	References auth.users (nullable for guests)
email	text	Customer email
status	text	pending, processing, shipped, delivered, cancelled
payment_status	text	pending, paid, refunded
stripe_checkout_session_id	text	Stripe session ID
stripe_payment_intent_id	text	Stripe payment intent
subtotal	numeric
shipping	numeric
tax	numeric
discount	numeric
total	numeric
shipping_address	jsonb	Full shipping address
billing_address	jsonb	Full billing address
shipping_method	text	standard, express, or overnight
tracking_number	text	Carrier tracking number
discount_code	text	Applied discount code

cart_items

Server-side cart for authenticated users.

Column	Type	Notes
id	uuid (PK)
user_id	uuid (FK)	References auth.users
product_id	uuid (FK)	References products.id
quantity	integer
variant_selections	jsonb	Array of {type, value}

profiles

Auto-created on user signup via database trigger.

Column	Type	Notes
id	uuid (PK, FK)	References auth.users.id
full_name	text
avatar_url	text
phone	text
role	text	customer or admin

reviews

Customer product reviews with moderation.

Column	Type	Notes
id	uuid (PK)
product_id	uuid (FK)	References products.id
user_id	uuid (FK)	References auth.users
author_name	text	Display name
rating	integer	1-5 stars
title	text	Review title
body	text	Review body
verified	boolean	Verified purchase
status	text	pending, approved, flagged, rejected
admin_response	text	Admin reply
helpful_count	integer	Upvote count

Row-Level Security

RLS is enabled on every table. The policies follow these principles:

Table	Public Read	Owner Read	Owner Write	Admin Read	Admin Write
products	Active only	-	-	All	Yes
cart_items	-	Own rows	Own rows	-	-
wishlists	-	Own rows	Own rows	-	-
orders	-	Own rows	-	All	Yes
reviews	Approved only	-	Own (insert)	All	Yes
profiles	-	Own row	Own row	All	-
addresses	-	Own rows	Own rows	-	-
discount_codes	Active only	-	-	All	Yes

Admin Detection

Admin access is determined by app_metadata.role:

CREATE OR REPLACE FUNCTION is_admin()
RETURNS boolean AS $$
  SELECT coalesce(
    auth.jwt() -> 'app_metadata' ->> 'role' = 'admin',
    false
  );
$$ LANGUAGE sql SECURITY DEFINER;

Triggers

Profile Auto-Creation

When a new user signs up, a trigger automatically creates their profile:

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, avatar_url, role)
  VALUES (
    new.id,
    coalesce(new.raw_user_meta_data ->> 'full_name', ''),
    coalesce(new.raw_user_meta_data ->> 'avatar_url', ''),
    'customer'
  );
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Review Aggregation

When reviews are inserted, updated, or deleted, a trigger recalculates the product's rating and review count:

-- Automatically updates products.rating and products.review_count
-- Only counts reviews with status = 'approved'

Seed Data

The supabase/seed.sql file includes:

Entity	Count	Notes
Categories	4	Clothing, Footwear, Accessories, Home
Products	40	With variants, images, and specs
Product variants	~200	Size and color options
Product images	~160	4 images per product
Collections	4	Summer, trending, eco, premium
Reviews	12	With ratings and responses
Orders	5	Various statuses
Customers	8	With profiles and addresses
Admin user	1	admin@example.com / password123
Discount codes	3	Percentage and fixed-amount
Shipping methods	4	Standard, express, overnight, free

Regenerating Types

After modifying the schema, regenerate TypeScript types:

npx supabase gen types typescript --project-id your-project-id > types/database.ts

This updates the typed Supabase client used throughout the application.