Deploy PTM SQL schema to Supabase

{
  "cost": {
    "input_tokens": 17,
    "estimated_usd": 0.052476,
    "output_tokens": 3495
  },
  "model": "claude-sonnet-4-20250514",
  "status": "success",
  "transport": "anthropic_api",
  "latency_ms": 44207,
  "response_text": "I'll help you deploy a PTM (Product Taxonomy Management) SQL schema to Supabase. Here's a comprehensive schema that covers the main aspects of product taxonomy management:\n\n## PTM SQL Schema for Supabase\n\n```sql\n-- Enable Row Level Security\nALTER DATABASE postgres SET \"app.jwt_secret\" TO 'your-jwt-secret';\n\n-- Create extensions\nCREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";\nCREATE EXTENSION IF NOT EXISTS \"ltree\";\n\n-- 1. Categories table with hierarchical structure\nCREATE TABLE categories (\n    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n    name VARCHAR(255) NOT NULL,\n    slug VARCHAR(255) UNIQUE NOT NULL,\n    description TEXT,\n    path LTREE, -- Hierarchical path using ltree extension\n    parent_id UUID REFERENCES categories(id) ON DELETE CASCADE,\n    level INTEGER NOT NULL DEFAULT 0,\n    sort_order INTEGER DEFAULT 0,\n    is_active BOOLEAN DEFAULT true,\n    metadata JSONB DEFAULT '{}',\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    created_by UUID REFERENCES auth.users(id),\n    updated_by UUID REFERENCES auth.users(id)\n);\n\n-- Index for ltree path queries\nCREATE INDEX categories_path_idx ON categories USING GIST(path);\nCREATE INDEX categories_parent_id_idx ON categories(parent_id);\nCREATE INDEX categories_slug_idx ON categories(slug);\n\n-- 2. Attributes table for category properties\nCREATE TABLE attributes (\n    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n    name VARCHAR(255) NOT NULL,\n    slug VARCHAR(255) UNIQUE NOT NULL,\n    description TEXT,\n    data_type VARCHAR(50) NOT NULL CHECK (data_type IN ('string', 'number', 'boolean', 'date', 'json', 'enum')),\n    is_required BOOLEAN DEFAULT false,\n    is_filterable BOOLEAN DEFAULT false,\n    is_searchable BOOLEAN DEFAULT false,\n    validation_rules JSONB DEFAULT '{}', -- JSON schema for validation\n    default_value TEXT,\n    unit VARCHAR(50), -- For numeric values (kg, cm, etc.)\n    sort_order INTEGER DEFAULT 0,\n    is_active BOOLEAN DEFAULT true,\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    created_by UUID REFERENCES auth.users(id),\n    updated_by UUID REFERENCES auth.users(id)\n);\n\nCREATE INDEX attributes_slug_idx ON attributes(slug);\nCREATE INDEX attributes_data_type_idx ON attributes(data_type);\n\n-- 3. Attribute options for enum-type attributes\nCREATE TABLE attribute_options (\n    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n    attribute_id UUID NOT NULL REFERENCES attributes(id) ON DELETE CASCADE,\n    value VARCHAR(255) NOT NULL,\n    label VARCHAR(255) NOT NULL,\n    sort_order INTEGER DEFAULT 0,\n    is_active BOOLEAN DEFAULT true,\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()\n);\n\nCREATE INDEX attribute_options_attribute_id_idx ON attribute_options(attribute_id);\n\n-- 4. Category-Attribute mapping\nCREATE TABLE category_attributes (\n    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n    category_id UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,\n    attribute_id UUID NOT NULL REFERENCES attributes(id) ON DELETE CASCADE,\n    is_required BOOLEAN DEFAULT false,\n    is_inherited BOOLEAN DEFAULT false, -- If inherited from parent category\n    sort_order INTEGER DEFAULT 0,\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    UNIQUE(category_id, attribute_id)\n);\n\nCREATE INDEX category_attributes_category_id_idx ON category_attributes(category_id);\nCREATE INDEX category_attributes_attribute_id_idx ON category_attributes(attribute_id);\n\n-- 5. Products table\nCREATE TABLE products (\n    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n    sku VARCHAR(255) UNIQUE NOT NULL,\n    name VARCHAR(255) NOT NULL,\n    description TEXT,\n    category_id UUID NOT NULL REFERENCES categories(id),\n    brand_id UUID REFERENCES brands(id),\n    status VARCHAR(50) DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'inactive', 'discontinued')),\n    price DECIMAL(10,2),\n    currency VARCHAR(3) DEFAULT 'USD',\n    weight DECIMAL(10,3),\n    dimensions JSONB, -- {length, width, height, unit}\n    images JSONB DEFAULT '[]', -- Array of image URLs\n    tags TEXT[],\n    metadata JSONB DEFAULT '{}',\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    created_by UUID REFERENCES auth.users(id),\n    updated_by UUID REFERENCES auth.users(id)\n);\n\nCREATE INDEX products_sku_idx ON products(sku);\nCREATE INDEX products_category_id_idx ON products(category_id);\nCREATE INDEX products_status_idx ON products(status);\nCREATE INDEX products_tags_idx ON products USING GIN(tags);\n\n-- 6. Brands table\nCREATE TABLE brands (\n    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n    name VARCHAR(255) NOT NULL,\n    slug VARCHAR(255) UNIQUE NOT NULL,\n    description TEXT,\n    logo_url TEXT,\n    website_url TEXT,\n    is_active BOOLEAN DEFAULT true,\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()\n);\n\nCREATE INDEX brands_slug_idx ON brands(slug);\n\n-- Add foreign key constraint for products.brand_id\nALTER TABLE products ADD CONSTRAINT fk_products_brand_id \n    FOREIGN KEY (brand_id) REFERENCES brands(id);\n\n-- 7. Product attribute values\nCREATE TABLE product_attribute_values (\n    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,\n    attribute_id UUID NOT NULL REFERENCES attributes(id) ON DELETE CASCADE,\n    value_text TEXT,\n    value_number DECIMAL(15,6),\n    value_boolean BOOLEAN,\n    value_date DATE,\n    value_json JSONB,\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    UNIQUE(product_id, attribute_id)\n);\n\nCREATE INDEX product_attribute_values_product_id_idx ON product_attribute_values(product_id);\nCREATE INDEX product_attribute_values_attribute_id_idx ON product_attribute_values(attribute_id);\nCREATE INDEX product_attribute_values_value_text_idx ON product_attribute_values(value_text);\nCREATE INDEX product_attribute_values_value_number_idx ON product_attribute_values(value_number);\n\n-- 8. Taxonomy versions for change tracking\nCREATE TABLE taxonomy_versions (\n    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n    version_number INTEGER NOT NULL,\n    description TEXT,\n    change_summary JSONB,\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    created_by UUID REFERENCES auth.users(id),\n    is_active BOOLEAN DEFAULT false\n);\n\n-- 9. Category mappings for external systems\nCREATE TABLE category_mappings (\n    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n    category_id UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,\n    external_system VARCHAR(100) NOT NULL,\n    external_category_id VARCHAR(255) NOT NULL,\n    external_category_name VARCHAR(255),\n    mapping_confidence DECIMAL(3,2) DEFAULT 1.0,\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    UNIQUE(category_id, external_system, external_category_id)\n);\n\n-- Functions and Triggers\n\n-- 1. Function to update the updated_at timestamp\nCREATE OR REPLACE FUNCTION update_updated_at_column()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = NOW();\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\n-- 2. Apply updated_at trigger to relevant tables\nCREATE TRIGGER update_categories_updated_at\n    BEFORE UPDATE ON categories\n    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();\n\nCREATE TRIGGER update_attributes_updated_at\n    BEFORE UPDATE ON attributes\n    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();\n\nCREATE TRIGGER update_products_updated_at\n    BEFORE UPDATE ON products\n    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();\n\nCREATE TRIGGER update_category_attributes_updated_at\n    BEFORE UPDATE ON category_attributes\n    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();\n\nCREATE TRIGGER update_product_attribute_values_updated_at\n    BEFORE UPDATE ON product_attribute_values\n    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();\n\n-- 3. Function to update category path and level\nCREATE OR REPLACE FUNCTION update_category_path()\nRETURNS TRIGGER AS $$\nBEGIN\n    IF NEW.parent_id IS NULL THEN\n        NEW.path = NEW.slug::ltree;\n        NEW.level = 0;\n    ELSE\n        SELECT path || NEW.slug::ltree, level + 1\n        INTO NEW.path, NEW.level\n        FROM categories\n        WHERE id = NEW.parent_id;\n    END IF;\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\nCREATE TRIGGER update_category_path_trigger\n    BEFORE INSERT OR UPDATE ON categories\n    FOR EACH ROW EXECUTE FUNCTION update_category_path();\n\n-- Views for common queries\n\n-- 1. Category hierarchy view\nCREATE VIEW category_hierarchy AS\nWITH RECURSIVE category_tree AS (\n    SELECT id, name, slug, description, parent_id, path, level, 0 as depth,\n           ARRAY[name] as name_path\n    FROM categories\n    WHERE parent_id IS NULL\n    \n    UNION ALL\n    \n    SELECT c.id, c.name, c.slug, c.description, c.parent_id, c.path, c.level, \n           ct.depth + 1, ct.name_path || c.name\n    FROM categories c\n    JOIN category_tree ct ON c.parent_id = ct.id\n)\nSELECT * FROM category_tree;\n\n-- 2. Product with category path view\nCREATE VIEW products_with_category AS\nSELECT \n    p.*,\n    c.name as category_name,\n    c.path as category_path,\n    b.name as brand_name\nFROM products p\nJOIN categories c ON p.category_id = c.id\nLEFT JOIN brands b ON p.brand_id = b.id;\n\n-- RLS Policies (enable as needed)\nALTER TABLE categories ENABLE ROW LEVEL SECURITY;\nALTER TABLE products ENABLE ROW LEVEL SECURITY;\nALTER TABLE attributes ENABLE ROW LEVEL SECURITY;\n\n-- Sample RLS policy (adjust based on your authentication needs)\nCREATE POLICY \"Users can view active categories\" ON categories\n    FOR SELECT USING (is_active = true);\n\nCREATE POLICY \"Authenticated users can manage categories\" ON categories\n    FOR ALL USING (auth.role() = 'authenticated');\n\n-- Sample data\nINSERT INTO categories (name, slug, description, parent_id) VALUES\n('Electronics', 'electronics', 'Electronic products and devices', NULL),\n('Clothing', 'clothing', 'Apparel and fashion items', NULL),\n('Books', 'books', 'Books and publications', NULL);\n\nINSERT INTO categories (name, slug, description, parent_id) VALUES\n('Smartphones', 'smartphones', 'Mobile phones and accessories', \n (SELECT id FROM categories WHERE slug = 'electronics')),\n('Laptops', 'laptops', 'Portable computers', \n (SELECT id FROM categories WHERE slug = 'electronics'));\n\nINSERT INTO attributes (name, slug, data_type, description) VALUES\n('Color', 'color', 'enum', 'Product color'),\n('Weight', 'weight', 'number', 'Product weight in grams'),\n('Brand', 'brand', 'string', 'Product brand name'),\n('Screen Size', 'screen_size', 'number', 'Screen size in inches');\n\nINSERT INTO attribute_options (attribute_id, value, label) VALUES\n((SELECT id FROM attributes WHERE slug = 'color'), 'red', 'Red'),\n((SELECT id FROM attributes WHERE slug = 'color'), 'blue', 'Blue'),\n((SELECT id FROM attributes WHERE slug = 'color'), 'black', 'Black'),\n((SELECT id FROM attributes WHERE slug = 'color'), 'white', 'White');\n\nINSERT INTO brands (name, slug, description) VALUES\n('Apple', 'apple', 'Technology company'),\n('Samsung', 'samsung', 'Electronics manufacturer'),\n('Nike', 'nike', 'Sportswear brand');\n```\n\n## Deployment Steps for Supabase:\n\n1. **Access Supabase SQL Editor:**\n   - Go to your Supabase dashboard\n   - Navigate to \"SQL Editor\"\n\n2. **Run the Schema:**\n   - Copy the above SQL schema\n   - Paste it into the SQL editor\n   - Execute the script\n\n3. **Configure RLS Policies:**\n   - Adjust the Row Level Security policies based on your authentication requirements\n   - Add more specific policies as needed\n\n4. **Set up API:**\n   - The tables will automatically be available through Supabase's auto-generated API\n   - Configure any additional API settings in the API section\n\n5. **Test the Schema:**\n   - Use the sample data to verify the relationships work correctly\n   - Test queries through the SQL editor\n\n## Key Features of this Schema:\n\n- **Hierarchical Categories:** Uses ltree for efficient category hierarchy\n- **Flexible Attributes:** Support for different data types with validation\n- **Product Management:** Complete product catalog functionality\n- **Versioning:** Track taxonomy changes over time\n- **External Mappings:** Map to external category systems\n- **Audit Trail:** Created/updated timestamps and user tracking\n- **Performance:** Optimized with appropriate indexes\n- **Security:** RLS policies for data access control\n\nWould you like me to explain any specific part of the schema or help you customize it for your particular use case?"
}