Product Tables

Tables for managing products, variants, and inventory.

products

Main product catalog.

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  product_type_id UUID REFERENCES product_types(id),
  title TEXT NOT NULL,
  description TEXT,
  base_price NUMERIC(10,2) NOT NULL,
  compare_at_price NUMERIC(10,2),
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'active', 'archived')),
  is_digital BOOLEAN DEFAULT FALSE,
  digital_file_url TEXT,
  seo_title TEXT,
  seo_description TEXT,
  tags TEXT[],
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_products_org_id ON products(org_id);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_products_created_at ON products(created_at DESC);

Column	Type	Description
id	UUID	Primary key
org_id	UUID	FK to organization
product_type_id	UUID	FK to product type
title	TEXT	Product name
description	TEXT	Product description (HTML)
base_price	NUMERIC	Default price
compare_at_price	NUMERIC	Original price (for sales)
status	TEXT	draft, active, archived
is_digital	BOOLEAN	Digital product flag
digital_file_url	TEXT	Download URL for digital
seo_title	TEXT	SEO meta title
seo_description	TEXT	SEO meta description
tags	TEXT[]	Product tags array
metadata	JSONB	Custom metadata

---

product_types

Product categories/types for organization.

CREATE TABLE product_types (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  slug TEXT NOT NULL,
  description TEXT,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(org_id, slug)
);

CREATE INDEX idx_product_types_org_id ON product_types(org_id);

Column	Type	Description
id	UUID	Primary key
org_id	UUID	FK to organization
name	TEXT	Type display name
slug	TEXT	URL identifier
description	TEXT	Type description
sort_order	INTEGER	Display order

---

product_variants

Size, color, and other product variations.

CREATE TABLE product_variants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  sku TEXT,
  name TEXT NOT NULL,
  price NUMERIC(10,2),
  compare_at_price NUMERIC(10,2),
  inventory_count INTEGER DEFAULT 0,
  inventory_policy TEXT DEFAULT 'deny'
    CHECK (inventory_policy IN ('deny', 'continue')),
  weight_grams INTEGER,
  option_values JSONB DEFAULT '{}'::jsonb,
  image_url TEXT,
  sort_order INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_product_variants_product_id ON product_variants(product_id);
CREATE INDEX idx_product_variants_sku ON product_variants(sku);

Column	Type	Description
id	UUID	Primary key
product_id	UUID	FK to product
sku	TEXT	Stock keeping unit
name	TEXT	Variant name (e.g., "Small / Blue")
price	NUMERIC	Price override (null = base price)
compare_at_price	NUMERIC	Original price
inventory_count	INTEGER	Stock quantity
inventory_policy	TEXT	deny or continue selling
weight_grams	INTEGER	Shipping weight
option_values	JSONB	Option selections
image_url	TEXT	Variant-specific image
sort_order	INTEGER	Display order
is_active	BOOLEAN	Available for sale

Option Values Structure

{
  "Size": "Medium",
  "Color": "Ocean Blue"
}

---

product_images

Product photos and media.

CREATE TABLE product_images (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  alt_text TEXT,
  sort_order INTEGER DEFAULT 0,
  is_primary BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_product_images_product_id ON product_images(product_id);

Column	Type	Description
id	UUID	Primary key
product_id	UUID	FK to product
url	TEXT	Image URL (Supabase Storage)
alt_text	TEXT	Accessibility text
sort_order	INTEGER	Display order
is_primary	BOOLEAN	Main product image

---

inventory_items

Detailed inventory tracking across locations.

CREATE TABLE inventory_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  product_id UUID REFERENCES products(id) ON DELETE SET NULL,
  variant_id UUID REFERENCES product_variants(id) ON DELETE SET NULL,
  sku TEXT,
  quantity INTEGER NOT NULL DEFAULT 0,
  reserved_quantity INTEGER DEFAULT 0,
  location TEXT DEFAULT 'default',
  reorder_point INTEGER,
  reorder_quantity INTEGER,
  cost_price NUMERIC(10,2),
  last_counted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_inventory_org_id ON inventory_items(org_id);
CREATE INDEX idx_inventory_product_id ON inventory_items(product_id);
CREATE INDEX idx_inventory_variant_id ON inventory_items(variant_id);
CREATE INDEX idx_inventory_sku ON inventory_items(sku);
CREATE INDEX idx_inventory_low_stock ON inventory_items(quantity)
  WHERE quantity <= reorder_point;

Column	Type	Description
id	UUID	Primary key
org_id	UUID	FK to organization
product_id	UUID	FK to product
variant_id	UUID	FK to variant
sku	TEXT	Stock keeping unit
quantity	INTEGER	Available stock
reserved_quantity	INTEGER	Reserved (pending orders)
location	TEXT	Storage location
reorder_point	INTEGER	Low stock threshold
reorder_quantity	INTEGER	Suggested reorder amount
cost_price	NUMERIC	Purchase cost
last_counted_at	TIMESTAMPTZ	Last inventory count

---

RLS Policies

-- Products: Organization access
CREATE POLICY "Members view org products"
  ON products FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

CREATE POLICY "Members create org products"
  ON products FOR INSERT
  WITH CHECK (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

CREATE POLICY "Members update org products"
  ON products FOR UPDATE
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

CREATE POLICY "Admins delete org products"
  ON products FOR DELETE
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('owner', 'admin')
  ));

-- Variants: Through product relationship
CREATE POLICY "Members view variants"
  ON product_variants FOR SELECT
  USING (product_id IN (
    SELECT id FROM products
    WHERE org_id IN (
      SELECT org_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  ));

---

Common Queries

Get Products with Variants

SELECT
  p.*,
  COALESCE(json_agg(pv.*) FILTER (WHERE pv.id IS NOT NULL), '[]') as variants
FROM products p
LEFT JOIN product_variants pv ON pv.product_id = p.id
WHERE p.org_id = $1
GROUP BY p.id
ORDER BY p.created_at DESC;

Get Product with Images

SELECT
  p.*,
  COALESCE(
    json_agg(pi.* ORDER BY pi.sort_order) FILTER (WHERE pi.id IS NOT NULL),
    '[]'
  ) as images
FROM products p
LEFT JOIN product_images pi ON pi.product_id = p.id
WHERE p.id = $1
GROUP BY p.id;

Low Stock Alert Query

SELECT
  i.*,
  p.title as product_title,
  pv.name as variant_name
FROM inventory_items i
LEFT JOIN products p ON p.id = i.product_id
LEFT JOIN product_variants pv ON pv.id = i.variant_id
WHERE i.org_id = $1
  AND i.quantity <= COALESCE(i.reorder_point, 5)
ORDER BY i.quantity ASC;

Update Inventory on Sale

-- Decrease available, increase reserved
UPDATE inventory_items
SET
  quantity = quantity - $2,
  reserved_quantity = reserved_quantity + $2,
  updated_at = NOW()
WHERE variant_id = $1
  AND quantity >= $2;