Functions & Triggers

Database functions, stored procedures, and triggers for business logic and data integrity in the Smart Shelf system.

Functions & Triggers

The Smart Shelf database uses PostgreSQL functions and triggers to implement business logic, maintain data integrity, and automate common operations.

Overview

Functions and triggers provide:

Data Validation: Enforce complex business rules
Automation: Automatically update related data
Auditing: Track changes and maintain history
Performance: Optimize common operations
Consistency: Ensure data integrity across tables

Core Functions

Inventory Management Functions

Calculate Available Inventory

Calculates available inventory considering reserved quantities:

CREATE OR REPLACE FUNCTION calculate_available_inventory(
    p_product_id UUID,
    p_warehouse_id UUID DEFAULT NULL
)
RETURNS INTEGER AS $$
DECLARE
    available_qty INTEGER;
BEGIN
    SELECT 
        COALESCE(SUM(i.quantity_on_hand - i.quantity_reserved), 0)
    INTO available_qty
    FROM inventory i
    WHERE i.product_id = p_product_id
    AND (p_warehouse_id IS NULL OR i.warehouse_id = p_warehouse_id)
    AND i.quantity_on_hand > 0;
    
    RETURN COALESCE(available_qty, 0);
END;
$$ LANGUAGE plpgsql STABLE;

Update Inventory Levels

Safely updates inventory with validation:

CREATE OR REPLACE FUNCTION update_inventory_level(
    p_product_id UUID,
    p_warehouse_id UUID,
    p_quantity_change INTEGER,
    p_movement_type inventory_movement_type,
    p_reference_id UUID DEFAULT NULL,
    p_notes TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
    current_qty INTEGER;
    new_qty INTEGER;
BEGIN
    -- Get current quantity
    SELECT quantity_on_hand INTO current_qty
    FROM inventory
    WHERE product_id = p_product_id 
    AND warehouse_id = p_warehouse_id;
    
    -- Calculate new quantity
    new_qty := COALESCE(current_qty, 0) + p_quantity_change;
    
    -- Validate non-negative inventory
    IF new_qty < 0 THEN
        RAISE EXCEPTION 'Insufficient inventory. Current: %, Requested: %', 
            current_qty, ABS(p_quantity_change);
    END IF;
    
    -- Update or insert inventory record
    INSERT INTO inventory (product_id, warehouse_id, quantity_on_hand, last_updated)
    VALUES (p_product_id, p_warehouse_id, new_qty, NOW())
    ON CONFLICT (product_id, warehouse_id)
    DO UPDATE SET 
        quantity_on_hand = new_qty,
        last_updated = NOW();
    
    -- Record stock movement
    INSERT INTO stock_movements (
        product_id, warehouse_id, movement_type, quantity,
        reference_id, notes, created_by
    ) VALUES (
        p_product_id, p_warehouse_id, p_movement_type, p_quantity_change,
        p_reference_id, p_notes, get_current_user_id()
    );
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

Order Processing Functions

Process Sales Order

Comprehensive sales order processing:

CREATE OR REPLACE FUNCTION process_sales_order(
    p_order_id UUID
)
RETURNS BOOLEAN AS $$
DECLARE
    order_rec RECORD;
    item_rec RECORD;
    available_qty INTEGER;
BEGIN
    -- Get order details
    SELECT * INTO order_rec
    FROM sales_orders
    WHERE id = p_order_id AND status = 'pending';
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order not found or not in pending status';
    END IF;
    
    -- Check availability for all items
    FOR item_rec IN 
        SELECT * FROM sales_order_items 
        WHERE sales_order_id = p_order_id
    LOOP
        available_qty := calculate_available_inventory(
            item_rec.product_id, 
            order_rec.warehouse_id
        );
        
        IF available_qty < item_rec.quantity THEN
            RAISE EXCEPTION 'Insufficient inventory for product %', 
                item_rec.product_id;
        END IF;
    END LOOP;
    
    -- Reserve inventory for all items
    FOR item_rec IN 
        SELECT * FROM sales_order_items 
        WHERE sales_order_id = p_order_id
    LOOP
        UPDATE inventory 
        SET quantity_reserved = quantity_reserved + item_rec.quantity
        WHERE product_id = item_rec.product_id 
        AND warehouse_id = order_rec.warehouse_id;
    END LOOP;
    
    -- Update order status
    UPDATE sales_orders 
    SET status = 'processing', processed_at = NOW()
    WHERE id = p_order_id;
    
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        -- Rollback will happen automatically
        RAISE;
END;
$$ LANGUAGE plpgsql;

Pricing Functions

Calculate Product Price

Dynamic pricing calculation:

CREATE OR REPLACE FUNCTION calculate_product_price(
    p_product_id UUID,
    p_customer_id UUID DEFAULT NULL,
    p_quantity INTEGER DEFAULT 1
)
RETURNS DECIMAL(10,2) AS $$
DECLARE
    base_price DECIMAL(10,2);
    customer_discount DECIMAL(5,2);
    quantity_discount DECIMAL(5,2);
    final_price DECIMAL(10,2);
BEGIN
    -- Get base price
    SELECT price INTO base_price
    FROM products
    WHERE id = p_product_id;
    
    -- Apply customer discount
    IF p_customer_id IS NOT NULL THEN
        SELECT discount_percentage INTO customer_discount
        FROM customers
        WHERE id = p_customer_id;
    END IF;
    
    -- Apply quantity discounts
    SELECT discount_percentage INTO quantity_discount
    FROM quantity_discounts
    WHERE product_id = p_product_id
    AND min_quantity <= p_quantity
    ORDER BY min_quantity DESC
    LIMIT 1;
    
    -- Calculate final price
    final_price := base_price;
    final_price := final_price * (1 - COALESCE(customer_discount, 0) / 100);
    final_price := final_price * (1 - COALESCE(quantity_discount, 0) / 100);
    
    RETURN final_price;
END;
$$ LANGUAGE plpgsql STABLE;

Triggers

Audit Triggers

Generic Audit Trigger

Automatically tracks changes to any table:

CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    old_data JSONB;
    new_data JSONB;
    user_id UUID;
BEGIN
    -- Get current user
    user_id := get_current_user_id();
    
    -- Prepare data based on operation
    CASE TG_OP
        WHEN 'INSERT' THEN
            new_data := row_to_json(NEW)::jsonb;
            old_data := NULL;
        WHEN 'UPDATE' THEN
            new_data := row_to_json(NEW)::jsonb;
            old_data := row_to_json(OLD)::jsonb;
        WHEN 'DELETE' THEN
            new_data := NULL;
            old_data := row_to_json(OLD)::jsonb;
    END CASE;
    
    -- Insert audit record
    INSERT INTO audit_logs (
        table_name, operation, record_id, old_data, new_data,
        user_id, timestamp
    ) VALUES (
        TG_TABLE_NAME, TG_OP, 
        COALESCE(NEW.id, OLD.id),
        old_data, new_data, user_id, NOW()
    );
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

Apply Audit Trigger

Function to easily add audit triggers to tables:

CREATE OR REPLACE FUNCTION add_audit_trigger(table_name TEXT)
RETURNS VOID AS $$
BEGIN
    EXECUTE format('
        CREATE TRIGGER audit_trigger_%I
        AFTER INSERT OR UPDATE OR DELETE ON %I
        FOR EACH ROW EXECUTE FUNCTION audit_trigger_function()',
        table_name, table_name
    );
END;
$$ LANGUAGE plpgsql;

-- Apply to critical tables
SELECT add_audit_trigger('users');
SELECT add_audit_trigger('products');
SELECT add_audit_trigger('inventory');
SELECT add_audit_trigger('sales_orders');
SELECT add_audit_trigger('purchase_orders');

Business Logic Triggers

Inventory Update Trigger

Automatically updates inventory on order fulfillment:

CREATE OR REPLACE FUNCTION update_inventory_on_shipment()
RETURNS TRIGGER AS $$
BEGIN
    -- Only process when order status changes to 'shipped'
    IF OLD.status != 'shipped' AND NEW.status = 'shipped' THEN
        -- Update inventory for each order item
        PERFORM update_inventory_level(
            soi.product_id,
            NEW.warehouse_id,
            -soi.quantity,
            'sale',
            NEW.id,
            'Order shipment'
        )
        FROM sales_order_items soi
        WHERE soi.sales_order_id = NEW.id;
        
        -- Update reserved quantities
        UPDATE inventory
        SET quantity_reserved = quantity_reserved - soi.quantity
        FROM sales_order_items soi
        WHERE inventory.product_id = soi.product_id
        AND inventory.warehouse_id = NEW.warehouse_id
        AND soi.sales_order_id = NEW.id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER inventory_shipment_trigger
    AFTER UPDATE ON sales_orders
    FOR EACH ROW
    EXECUTE FUNCTION update_inventory_on_shipment();

Product Validation Trigger

Ensures product data integrity:

CREATE OR REPLACE FUNCTION validate_product_data()
RETURNS TRIGGER AS $$
BEGIN
    -- Validate price is positive
    IF NEW.price <= 0 THEN
        RAISE EXCEPTION 'Product price must be positive';
    END IF;
    
    -- Validate SKU format
    IF NEW.sku !~ '^[A-Z0-9\-]{3,20}$' THEN
        RAISE EXCEPTION 'SKU must be 3-20 characters, alphanumeric with hyphens';
    END IF;
    
    -- Set default values
    NEW.updated_at := NOW();
    
    -- Generate barcode if not provided
    IF NEW.barcode IS NULL THEN
        NEW.barcode := generate_product_barcode(NEW.id);
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER product_validation_trigger
    BEFORE INSERT OR UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION validate_product_data();

Notification Triggers

Low Stock Alert Trigger

Sends alerts when inventory falls below minimum levels:

CREATE OR REPLACE FUNCTION check_low_stock()
RETURNS TRIGGER AS $$
DECLARE
    product_name TEXT;
    warehouse_name TEXT;
BEGIN
    -- Check if inventory is below minimum level
    IF NEW.quantity_on_hand <= NEW.minimum_level THEN
        -- Get product and warehouse names
        SELECT p.name, w.name 
        INTO product_name, warehouse_name
        FROM products p, warehouses w
        WHERE p.id = NEW.product_id 
        AND w.id = NEW.warehouse_id;
        
        -- Create notification
        INSERT INTO notifications (
            type, title, message, user_id, reference_id, created_at
        ) 
        SELECT 
            'low_stock',
            'Low Stock Alert',
            format('Product %s in warehouse %s is below minimum level. Current: %s, Minimum: %s',
                product_name, warehouse_name, NEW.quantity_on_hand, NEW.minimum_level),
            u.id,
            NEW.product_id,
            NOW()
        FROM users u
        WHERE u.role IN ('admin', 'manager')
        OR u.department = 'inventory';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER low_stock_trigger
    AFTER UPDATE ON inventory
    FOR EACH ROW
    EXECUTE FUNCTION check_low_stock();

Utility Functions

User Context Functions

-- Get current user ID from session
CREATE OR REPLACE FUNCTION get_current_user_id()
RETURNS UUID AS $$
BEGIN
    RETURN current_setting('app.current_user_id', true)::UUID;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Get current user role
CREATE OR REPLACE FUNCTION get_current_user_role()
RETURNS TEXT AS $$
BEGIN
    RETURN current_setting('app.current_user_role', true);
EXCEPTION
    WHEN OTHERS THEN
        RETURN 'anonymous';
END;
$$ LANGUAGE plpgsql STABLE;

Barcode Generation

CREATE OR REPLACE FUNCTION generate_product_barcode(p_product_id UUID)
RETURNS TEXT AS $$
DECLARE
    barcode TEXT;
BEGIN
    -- Generate UPC-A compatible barcode
    barcode := '8' || -- Country code for private use
               LPAD(ABS(hashtext(p_product_id::TEXT))::TEXT, 10, '0');
    
    -- Add check digit
    barcode := barcode || calculate_upc_check_digit(barcode);
    
    RETURN barcode;
END;
$$ LANGUAGE plpgsql;

Performance Optimization

Batch Processing Functions

-- Batch update inventory levels
CREATE OR REPLACE FUNCTION batch_update_inventory(
    p_updates JSONB
)
RETURNS INTEGER AS $$
DECLARE
    update_count INTEGER := 0;
    update_item JSONB;
BEGIN
    -- Process each update in the batch
    FOR update_item IN SELECT * FROM jsonb_array_elements(p_updates)
    LOOP
        PERFORM update_inventory_level(
            (update_item->>'product_id')::UUID,
            (update_item->>'warehouse_id')::UUID,
            (update_item->>'quantity_change')::INTEGER,
            (update_item->>'movement_type')::inventory_movement_type,
            (update_item->>'reference_id')::UUID,
            update_item->>'notes'
        );
        update_count := update_count + 1;
    END LOOP;
    
    RETURN update_count;
END;
$$ LANGUAGE plpgsql;

Error Handling

All functions include proper error handling and meaningful error messages:

CREATE OR REPLACE FUNCTION safe_divide(numerator NUMERIC, denominator NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF denominator = 0 THEN
        RAISE EXCEPTION 'Division by zero not allowed';
    END IF;
    
    RETURN numerator / denominator;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE EXCEPTION 'Cannot divide % by zero', numerator;
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Unexpected error in safe_divide: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

Best Practices

Error Handling: Always include proper exception handling
Validation: Validate inputs before processing
Transactions: Use transactions for multi-step operations
Performance: Consider performance impact of complex functions
Security: Implement proper access controls
Documentation: Include clear comments and documentation
Testing: Thoroughly test all functions and triggers

Core Tables - Table structures used by functions
Row Level Security - Security implementation
Indexes & Performance - Performance optimization
Data Types - Custom data types used

Row Level Security

Views

Functions & Triggers

Functions & Triggers

Overview

Core Functions

Inventory Management Functions

Calculate Available Inventory

Update Inventory Levels

Order Processing Functions

Process Sales Order

Pricing Functions

Calculate Product Price

Triggers

Audit Triggers

Generic Audit Trigger

Apply Audit Trigger

Business Logic Triggers

Inventory Update Trigger

Product Validation Trigger

Notification Triggers

Low Stock Alert Trigger

Utility Functions

User Context Functions

Barcode Generation

Performance Optimization

Batch Processing Functions

Error Handling

Best Practices

Related Documentation