Data Models & Schema

Database entity relationships, table structures, and data model design for Smart Shelf.

Data Models & Schema

Comprehensive overview of Smart Shelf's database schema, entity relationships, and data model design patterns using PostgreSQL and Supabase.

Entity Relationship Model

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│   Users     │     │  Products   │     │ Categories  │
│             │     │             │     │             │
│ • id        │     │ • id        │────▶│ • id        │
│ • email     │     │ • name      │     │ • name      │
│ • role      │     │ • sku       │     │ • parent_id │
│ • profile   │     │ • category  │     │ • is_active │
└─────────────┘     │ • barcode   │     └─────────────┘
                    │ • price     │
                    │ • cost      │
                    └─────────────┘
                           │
                           ▼
                    ┌─────────────┐     ┌─────────────┐
                    │ Inventory   │     │ Warehouses  │
                    │             │     │             │
                    │ • product   │────▶│ • id        │
                    │ • warehouse │     │ • name      │
                    │ • quantity  │     │ • address   │
                    │ • allocated │     │ • capacity  │
                    │ • available │     │ • is_active │
                    └─────────────┘     └─────────────┘
                           │
                           ▼
                    ┌─────────────┐
                    │Stock Moves  │
                    │             │
                    │ • id        │
                    │ • product   │
                    │ • quantity  │
                    │ • type      │
                    │ • reason    │
                    │ • timestamp │
                    └─────────────┘

Core Table Structures

1. Authentication & User Management

auth.users (Supabase Auth)

-- Managed by Supabase Auth
CREATE TABLE auth.users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  encrypted_password VARCHAR(255),
  email_confirmed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

user_profiles

CREATE TABLE user_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name VARCHAR(255) NOT NULL,
  avatar_url TEXT,
  role user_role NOT NULL DEFAULT 'staff',
  warehouse_id UUID REFERENCES warehouses(id),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create user role enum
CREATE TYPE user_role AS ENUM ('admin', 'manager', 'staff', 'viewer');

user_roles

CREATE TABLE user_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES user_profiles(id) ON DELETE CASCADE,
  role VARCHAR(50) NOT NULL,
  permissions JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, role)
);

2. Product Management

products

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  sku VARCHAR(100) UNIQUE NOT NULL,
  category_id UUID REFERENCES product_categories(id),
  brand VARCHAR(100),
  unit_of_measure unit_type NOT NULL DEFAULT 'piece',
  cost_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  selling_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  min_stock_level INTEGER DEFAULT 0,
  max_stock_level INTEGER,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create unit of measure enum
CREATE TYPE unit_type AS ENUM ('piece', 'kg', 'gram', 'liter', 'ml', 'box', 'pack', 'dozen');

-- Indexes for performance
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_active ON products(name) WHERE is_active = true;

product_categories

CREATE TABLE product_categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  parent_id UUID REFERENCES product_categories(id),
  sort_order INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- Prevent circular references
  CONSTRAINT no_self_parent CHECK (id != parent_id)
);

-- Index for hierarchical queries
CREATE INDEX idx_categories_parent ON product_categories(parent_id);
CREATE INDEX idx_categories_active ON product_categories(name) WHERE is_active = true;

product_barcodes

CREATE TABLE product_barcodes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  barcode VARCHAR(50) UNIQUE NOT NULL,
  barcode_type barcode_type NOT NULL DEFAULT 'EAN13',
  is_primary BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create barcode type enum
CREATE TYPE barcode_type AS ENUM ('UPC', 'EAN8', 'EAN13', 'CODE128', 'CODE39', 'QR');

-- Ensure only one primary barcode per product
CREATE UNIQUE INDEX idx_product_primary_barcode 
ON product_barcodes(product_id) 
WHERE is_primary = true;

product_variants

CREATE TABLE product_variants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  sku VARCHAR(100) UNIQUE NOT NULL,
  cost_price DECIMAL(10,2),
  selling_price DECIMAL(10,2),
  attributes JSONB DEFAULT '{}',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for variant attributes
CREATE INDEX idx_variant_attributes ON product_variants USING GIN(attributes);

3. Inventory Management

inventory

CREATE TABLE inventory (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  warehouse_id UUID NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
  quantity_on_hand INTEGER NOT NULL DEFAULT 0,
  quantity_allocated INTEGER NOT NULL DEFAULT 0,
  quantity_available INTEGER GENERATED ALWAYS AS (quantity_on_hand - quantity_allocated) STORED,
  last_counted TIMESTAMPTZ,
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- Ensure positive quantities
  CONSTRAINT positive_on_hand CHECK (quantity_on_hand >= 0),
  CONSTRAINT positive_allocated CHECK (quantity_allocated >= 0),
  
  -- Unique product per warehouse
  UNIQUE(product_id, warehouse_id)
);

-- Indexes for performance
CREATE INDEX idx_inventory_product ON inventory(product_id);
CREATE INDEX idx_inventory_warehouse ON inventory(warehouse_id);
CREATE INDEX idx_inventory_low_stock ON inventory(product_id, quantity_available) 
WHERE quantity_available <= 10;

stock_movements

CREATE TABLE stock_movements (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES products(id),
  warehouse_id UUID NOT NULL REFERENCES warehouses(id),
  movement_type movement_type NOT NULL,
  quantity INTEGER NOT NULL,
  unit_cost DECIMAL(10,2),
  reference_id UUID,
  reference_type VARCHAR(50),
  reason VARCHAR(255),
  notes TEXT,
  created_by UUID NOT NULL REFERENCES user_profiles(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create movement type enum
CREATE TYPE movement_type AS ENUM ('IN', 'OUT', 'TRANSFER', 'ADJUSTMENT');

-- Indexes for reporting and queries
CREATE INDEX idx_stock_movements_product_date ON stock_movements(product_id, created_at DESC);
CREATE INDEX idx_stock_movements_warehouse_date ON stock_movements(warehouse_id, created_at DESC);
CREATE INDEX idx_stock_movements_type ON stock_movements(movement_type);
CREATE INDEX idx_stock_movements_reference ON stock_movements(reference_id, reference_type);

inventory_adjustments

CREATE TABLE inventory_adjustments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES products(id),
  warehouse_id UUID NOT NULL REFERENCES warehouses(id),
  quantity_before INTEGER NOT NULL,
  quantity_after INTEGER NOT NULL,
  adjustment_quantity INTEGER GENERATED ALWAYS AS (quantity_after - quantity_before) STORED,
  reason adjustment_reason NOT NULL,
  notes TEXT,
  approved_by UUID REFERENCES user_profiles(id),
  created_by UUID NOT NULL REFERENCES user_profiles(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create adjustment reason enum
CREATE TYPE adjustment_reason AS ENUM (
  'physical_count', 'damage', 'expired', 'theft', 'found', 'correction', 'other'
);

4. Warehouse Management

warehouses

CREATE TABLE warehouses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  code VARCHAR(20) UNIQUE NOT NULL,
  address JSONB,
  contact_info JSONB,
  capacity INTEGER,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for active warehouses
CREATE INDEX idx_warehouses_active ON warehouses(name) WHERE is_active = true;

warehouse_locations

CREATE TABLE warehouse_locations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  warehouse_id UUID NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
  location_code VARCHAR(50) NOT NULL,
  location_type location_type NOT NULL,
  parent_location_id UUID REFERENCES warehouse_locations(id),
  capacity INTEGER,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- Unique location code within warehouse
  UNIQUE(warehouse_id, location_code)
);

-- Create location type enum
CREATE TYPE location_type AS ENUM ('AISLE', 'RACK', 'SHELF', 'BIN');

Validation and Constraints

Data Integrity Rules

-- Function to validate stock quantities
CREATE OR REPLACE FUNCTION validate_stock_movement()
RETURNS TRIGGER AS $$
BEGIN
  -- Validate OUT and TRANSFER movements don't exceed available stock
  IF NEW.movement_type IN ('OUT', 'TRANSFER') THEN
    IF NEW.quantity > (
      SELECT quantity_available 
      FROM inventory 
      WHERE product_id = NEW.product_id 
        AND warehouse_id = NEW.warehouse_id
    ) THEN
      RAISE EXCEPTION 'Insufficient stock for movement. Available: %, Requested: %', 
        (SELECT quantity_available FROM inventory 
         WHERE product_id = NEW.product_id AND warehouse_id = NEW.warehouse_id), 
        NEW.quantity;
    END IF;
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply validation trigger
CREATE TRIGGER validate_stock_movement_trigger
  BEFORE INSERT ON stock_movements
  FOR EACH ROW
  EXECUTE FUNCTION validate_stock_movement();

Audit Trail

-- Generic audit trail table
CREATE TABLE audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name VARCHAR(50) NOT NULL,
  record_id UUID NOT NULL,
  operation VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE
  old_values JSONB,
  new_values JSONB,
  changed_by UUID REFERENCES user_profiles(id),
  changed_at TIMESTAMPTZ DEFAULT NOW()
);

-- Audit function
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (
    table_name,
    record_id,
    operation,
    old_values,
    new_values,
    changed_by
  ) VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END,
    COALESCE(NEW.updated_by, OLD.updated_by, current_setting('app.current_user_id', true)::UUID)
  );
  
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

Database Functions and Views

Inventory Summary View

CREATE VIEW inventory_summary AS
SELECT 
  i.product_id,
  p.name AS product_name,
  p.sku,
  i.warehouse_id,
  w.name AS warehouse_name,
  i.quantity_on_hand,
  i.quantity_allocated,
  i.quantity_available,
  p.min_stock_level,
  CASE 
    WHEN i.quantity_available <= 0 THEN 'OUT_OF_STOCK'
    WHEN i.quantity_available <= p.min_stock_level THEN 'LOW_STOCK'
    ELSE 'IN_STOCK'
  END AS stock_status,
  i.last_counted,
  i.updated_at
FROM inventory i
JOIN products p ON i.product_id = p.id
JOIN warehouses w ON i.warehouse_id = w.id
WHERE p.is_active = true AND w.is_active = true;

Stock Movement Summary Function

CREATE OR REPLACE FUNCTION get_stock_movements(
  p_product_id UUID DEFAULT NULL,
  p_warehouse_id UUID DEFAULT NULL,
  p_from_date TIMESTAMPTZ DEFAULT NULL,
  p_to_date TIMESTAMPTZ DEFAULT NULL,
  p_limit INTEGER DEFAULT 50
)
RETURNS TABLE (
  id UUID,
  product_name VARCHAR,
  warehouse_name VARCHAR,
  movement_type movement_type,
  quantity INTEGER,
  created_at TIMESTAMPTZ,
  created_by_name VARCHAR,
  reason VARCHAR,
  running_total BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    sm.id,
    p.name AS product_name,
    w.name AS warehouse_name,
    sm.movement_type,
    sm.quantity,
    sm.created_at,
    up.full_name AS created_by_name,
    sm.reason,
    SUM(
      CASE 
        WHEN sm.movement_type IN ('IN', 'TRANSFER') THEN sm.quantity
        ELSE -sm.quantity
      END
    ) OVER (
      PARTITION BY sm.product_id, sm.warehouse_id 
      ORDER BY sm.created_at ASC 
      ROWS UNBOUNDED PRECEDING
    ) AS running_total
  FROM stock_movements sm
  JOIN products p ON sm.product_id = p.id
  JOIN warehouses w ON sm.warehouse_id = w.id
  JOIN user_profiles up ON sm.created_by = up.id
  WHERE 
    (p_product_id IS NULL OR sm.product_id = p_product_id)
    AND (p_warehouse_id IS NULL OR sm.warehouse_id = p_warehouse_id)
    AND (p_from_date IS NULL OR sm.created_at >= p_from_date)
    AND (p_to_date IS NULL OR sm.created_at <= p_to_date)
  ORDER BY sm.created_at DESC
  LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

This comprehensive data model ensures data integrity, performance, and scalability for Smart Shelf's inventory management requirements.

Database Architecture

Security & Access Control