Database Configuration

Complete guide to setting up Supabase as your Smart Shelf database backend, including schema setup, authentication, and security policies.

Supabase Project Setup

1. Create Supabase Project

1. Navigate to Supabase

   • Go to supabase.com
   • Sign in to your account

2. Create New Project

   • Click "New Project"
   • Select your organization (or create one)

3. Project Configuration

   Project Name: smart-shelf
   Database Password: [strong-password]
   Region: [select closest to your location]
   Pricing Plan: Free (for development)
   

4. Wait for Setup

   • Project creation takes 2-3 minutes
   • You'll see a progress indicator

2. Get Project Credentials

1. Access Project Dashboard

   • Navigate to your project dashboard
   • Go to Settings → API

2. Copy Required Values

   Project URL: https://[project-id].supabase.co
   API Key (anon public): eyJ...
   API Key (service_role): eyJ... (keep secret)
   

3. Update Environment Variables

   # Add to .env.local
   NEXT_PUBLIC_SUPABASE_URL=https://[project-id].supabase.co
   NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ[anon-key]
   SUPABASE_SERVICE_ROLE_KEY=eyJ[service-role-key]
   

Database Schema Setup

Option A: SQL Editor (Recommended)

1. Open SQL Editor

   • In your Supabase dashboard
   • Go to SQL Editor
   • Click "New query"

2. Run Schema Script

   -- Smart Shelf Database Schema
   -- Run this script in Supabase SQL Editor
   
   -- Enable necessary extensions
   CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
   CREATE EXTENSION IF NOT EXISTS "pgcrypto";
   
   -- Create custom types
   CREATE TYPE user_role AS ENUM ('admin', 'manager', 'employee', 'viewer');
   CREATE TYPE order_status AS ENUM ('draft', 'sent', 'confirmed', 'partially_received', 'received', 'closed');
   CREATE TYPE movement_type AS ENUM ('in', 'out', 'transfer', 'adjustment');
   
   -- Companies table
   CREATE TABLE companies (
     id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
     name VARCHAR(255) NOT NULL,
     address TEXT,
     phone VARCHAR(50),
     email VARCHAR(255),
     website VARCHAR(255),
     logo_url TEXT,
     settings JSONB DEFAULT '{}',
     created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
   );
   
   -- Users profile table (extends auth.users)
   CREATE TABLE user_profiles (
     id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
     company_id UUID REFERENCES companies(id),
     full_name VARCHAR(255),
     role user_role DEFAULT 'employee',
     phone VARCHAR(50),
     avatar_url TEXT,
     settings JSONB DEFAULT '{}',
     is_active BOOLEAN DEFAULT true,
     created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
   );
   
   -- Warehouses table
   CREATE TABLE warehouses (
     id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
     company_id UUID REFERENCES companies(id) NOT NULL,
     name VARCHAR(255) NOT NULL,
     code VARCHAR(50) UNIQUE NOT NULL,
     address TEXT,
     phone VARCHAR(50),
     email VARCHAR(255),
     manager_id UUID REFERENCES user_profiles(id),
     is_active BOOLEAN DEFAULT true,
     settings JSONB DEFAULT '{}',
     created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
   );
   
   -- Product categories table
   CREATE TABLE categories (
     id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
     company_id UUID REFERENCES companies(id) NOT NULL,
     name VARCHAR(255) NOT NULL,
     description TEXT,
     parent_id UUID REFERENCES categories(id),
     is_active BOOLEAN DEFAULT true,
     created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
   );
   
   -- Suppliers table
   CREATE TABLE suppliers (
     id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
     company_id UUID REFERENCES companies(id) NOT NULL,
     name VARCHAR(255) NOT NULL,
     contact_person VARCHAR(255),
     email VARCHAR(255),
     phone VARCHAR(50),
     address TEXT,
     website VARCHAR(255),
     tax_id VARCHAR(100),
     payment_terms VARCHAR(255),
     is_active BOOLEAN DEFAULT true,
     created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
   );
   
   -- Products table
   CREATE TABLE products (
     id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
     company_id UUID REFERENCES companies(id) NOT NULL,
     sku VARCHAR(100) UNIQUE NOT NULL,
     name VARCHAR(255) NOT NULL,
     description TEXT,
     category_id UUID REFERENCES categories(id),
     supplier_id UUID REFERENCES suppliers(id),
     barcode VARCHAR(255),
     unit_of_measure VARCHAR(50) DEFAULT 'pcs',
     cost_price DECIMAL(10,2) DEFAULT 0,
     selling_price DECIMAL(10,2) DEFAULT 0,
     reorder_point INTEGER DEFAULT 0,
     reorder_quantity INTEGER DEFAULT 0,
     images TEXT[] DEFAULT '{}',
     specifications JSONB DEFAULT '{}',
     is_active BOOLEAN DEFAULT true,
     created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
   );
   
   -- Inventory table
   CREATE TABLE inventory (
     id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
     product_id UUID REFERENCES products(id) NOT NULL,
     warehouse_id UUID REFERENCES warehouses(id) NOT NULL,
     quantity INTEGER DEFAULT 0,
     reserved_quantity INTEGER DEFAULT 0,
     available_quantity INTEGER GENERATED ALWAYS AS (quantity - reserved_quantity) STORED,
     last_count_date TIMESTAMP WITH TIME ZONE,
     last_movement_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
     created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
     UNIQUE(product_id, warehouse_id)
   );
   
   -- Inventory movements table
   CREATE TABLE inventory_movements (
     id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
     product_id UUID REFERENCES products(id) NOT NULL,
     warehouse_id UUID REFERENCES warehouses(id) NOT NULL,
     movement_type movement_type NOT NULL,
     quantity INTEGER NOT NULL,
     unit_cost DECIMAL(10,2),
     reference_type VARCHAR(50), -- 'order', 'adjustment', 'transfer'
     reference_id UUID,
     notes TEXT,
     user_id UUID REFERENCES user_profiles(id),
     created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
   );
   
   -- Purchase orders table
   CREATE TABLE purchase_orders (
     id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
     company_id UUID REFERENCES companies(id) NOT NULL,
     order_number VARCHAR(100) UNIQUE NOT NULL,
     supplier_id UUID REFERENCES suppliers(id) NOT NULL,
     warehouse_id UUID REFERENCES warehouses(id) NOT NULL,
     status order_status DEFAULT 'draft',
     order_date DATE DEFAULT CURRENT_DATE,
     expected_date DATE,
     total_amount DECIMAL(12,2) DEFAULT 0,
     notes TEXT,
     created_by UUID REFERENCES user_profiles(id),
     approved_by UUID REFERENCES user_profiles(id),
     approved_at TIMESTAMP WITH TIME ZONE,
     created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
   );
   
   -- Purchase order items table
   CREATE TABLE purchase_order_items (
     id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
     order_id UUID REFERENCES purchase_orders(id) ON DELETE CASCADE,
     product_id UUID REFERENCES products(id) NOT NULL,
     quantity INTEGER NOT NULL,
     unit_cost DECIMAL(10,2) NOT NULL,
     total_cost DECIMAL(12,2) GENERATED ALWAYS AS (quantity * unit_cost) STORED,
     received_quantity INTEGER DEFAULT 0,
     created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
   );
   

3. Execute Schema Script

   • Click "Run" to execute the schema
   • Verify tables are created in the Table Editor

Option B: Supabase CLI

1. Install Supabase CLI

   npm install -g supabase
   

2. Login and Link Project

   # Login to Supabase
   supabase login
   
   # Link your project
   supabase link --project-ref [your-project-ref]
   

3. Apply Database Changes

   # Generate migration
   supabase db diff --use-migra
   
   # Push to remote
   supabase db push
   

Row Level Security (RLS)

1. Enable RLS on Tables

-- Enable RLS on all tables
ALTER TABLE companies ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE warehouses ENABLE ROW LEVEL SECURITY;
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE suppliers ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory_movements ENABLE ROW LEVEL SECURITY;
ALTER TABLE purchase_orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE purchase_order_items ENABLE ROW LEVEL SECURITY;

2. Create RLS Policies

-- Helper function to get user's company_id
CREATE OR REPLACE FUNCTION auth.user_company_id()
RETURNS UUID AS $$
  SELECT company_id FROM user_profiles WHERE id = auth.uid();
$$ LANGUAGE SQL SECURITY DEFINER;

-- User profiles policies
CREATE POLICY "Users can view own profile" ON user_profiles
  FOR SELECT USING (auth.uid() = id);

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

-- Company data policies (users can only access their company's data)
CREATE POLICY "Users can view company data" ON companies
  FOR SELECT USING (id = auth.user_company_id());

CREATE POLICY "Users can view company warehouses" ON warehouses
  FOR SELECT USING (company_id = auth.user_company_id());

CREATE POLICY "Users can view company products" ON products
  FOR SELECT USING (company_id = auth.user_company_id());

CREATE POLICY "Users can view company inventory" ON inventory
  FOR SELECT USING (warehouse_id IN (
    SELECT id FROM warehouses WHERE company_id = auth.user_company_id()
  ));

-- Add similar policies for other tables...

Authentication Setup

1. Configure Authentication

1. Go to Authentication Settings

   • Navigate to Authentication → Settings
   • Configure the following settings:

2. Site Configuration

   Site URL: http://localhost:3000 (development)
   Additional redirect URLs:
   - http://localhost:3000/auth/callback
   - https://yourdomain.com (production)
   - https://yourdomain.com/auth/callback (production)
   

3. Email Templates (Optional)

   • Customize signup confirmation email
   • Customize password reset email
   • Add your company branding

2. Authentication Providers

Email Authentication (Default)

• Enabled by default
• Users can sign up with email/password
• Email confirmation required

Social Authentication (Optional)

Google OAuth:

1. Go to Authentication → Providers
2. Enable Google provider
3. Add your Google OAuth credentials:
   Client ID: [from Google Console]
   Client Secret: [from Google Console]
   

GitHub OAuth:

1. Enable GitHub provider
2. Add GitHub OAuth app credentials:
   Client ID: [from GitHub]
   Client Secret: [from GitHub]
   

3. User Registration Flow

1. Default Registration

   // Users register through the application
   // First user becomes admin automatically
   // Subsequent users need admin approval
   

2. Admin User Setup

   -- After first user signs up, make them admin
   UPDATE user_profiles 
   SET role = 'admin' 
   WHERE id = '[first-user-id]';
   

Database Functions and Triggers

1. Useful Database Functions

-- Function to update timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply to all tables with updated_at
CREATE TRIGGER update_companies_updated_at 
  BEFORE UPDATE ON companies 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Inventory movement trigger
CREATE OR REPLACE FUNCTION handle_inventory_movement()
RETURNS TRIGGER AS $$
BEGIN
  -- Update inventory quantity based on movement
  IF NEW.movement_type = 'in' THEN
    UPDATE inventory 
    SET quantity = quantity + NEW.quantity,
        last_movement_date = NOW()
    WHERE product_id = NEW.product_id 
      AND warehouse_id = NEW.warehouse_id;
  ELSIF NEW.movement_type = 'out' THEN
    UPDATE inventory 
    SET quantity = quantity - NEW.quantity,
        last_movement_date = NOW()
    WHERE product_id = NEW.product_id 
      AND warehouse_id = NEW.warehouse_id;
  END IF;
  
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER inventory_movement_trigger
  AFTER INSERT ON inventory_movements
  FOR EACH ROW EXECUTE FUNCTION handle_inventory_movement();

2. Indexes for Performance

-- Create indexes for better query performance
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_barcode ON products(barcode);
CREATE INDEX idx_inventory_product_warehouse ON inventory(product_id, warehouse_id);
CREATE INDEX idx_inventory_movements_product ON inventory_movements(product_id);
CREATE INDEX idx_inventory_movements_date ON inventory_movements(created_at);
CREATE INDEX idx_purchase_orders_status ON purchase_orders(status);
CREATE INDEX idx_user_profiles_company ON user_profiles(company_id);

Database Verification

1. Test Database Connection

// Test in your application
import { supabase } from '@/lib/supabase'

const testConnection = async () => {
  const { data, error } = await supabase
    .from('companies')
    .select('*')
    .limit(1)
  
  if (error) {
    console.error('Database connection error:', error)
  } else {
    console.log('Database connected successfully')
  }
}

2. Verify Tables and Policies

1. Check Tables

   • Go to Table Editor in Supabase
   • Verify all tables are created
   • Check column types and constraints

2. Test RLS Policies

   -- Test as authenticated user
   SELECT * FROM products LIMIT 5;
   
   -- Should only return user's company data
   

Troubleshooting Database Setup

Common Issues

Schema Creation Errors

• Syntax Errors: Check SQL syntax carefully
• Permission Issues: Ensure you're project owner
• Extension Issues: Enable required extensions first

RLS Policy Issues

• Access Denied: Check policy conditions
• Function Errors: Verify helper functions exist
• Authentication: Ensure user is properly authenticated

Connection Issues

• Wrong URL: Verify Supabase project URL
• Invalid API Key: Check anon key vs service role key
• Network Issues: Check firewall and proxy settings

Performance Issues

Slow Queries

-- Enable query analysis
EXPLAIN ANALYZE SELECT * FROM products WHERE sku = 'SKU001';

-- Add missing indexes
CREATE INDEX IF NOT EXISTS idx_products_sku ON products(sku);

Memory Issues

• Monitor database usage in Supabase dashboard
• Optimize queries with proper indexes
• Consider upgrading plan for larger datasets

Next Steps

Once your database is configured:

1. Initial Configuration - Set up admin user and basic data
2. Environment Setup - Verify application connection
3. Production Deployment - Deploy with production database

Database configuration complete! Your Smart Shelf database is ready for use. Check the Troubleshooting Guide if you encounter any issues.