Views

Database views for simplified data access and reporting in the Smart Shelf inventory management system.

Views

Database views provide simplified, secure, and efficient access to complex data relationships in the Smart Shelf system. They serve as a abstraction layer for reporting, analytics, and application queries.

Overview

Views in the Smart Shelf database serve multiple purposes:

  • Simplification: Complex joins reduced to simple SELECT statements
  • Security: Hide sensitive columns and restrict access
  • Performance: Pre-optimized queries for common operations
  • Consistency: Standardized data access patterns
  • Reporting: Ready-made datasets for analytics

Product Views

Product Summary View

Comprehensive product information with inventory levels:

CREATE OR REPLACE VIEW v_product_summary AS
SELECT 
    p.id,
    p.sku,
    p.name,
    p.description,
    p.price,
    p.cost,
    p.barcode,
    p.status,
    c.name as category_name,
    c.code as category_code,
    s.name as supplier_name,
    -- Inventory totals across all warehouses
    COALESCE(SUM(inv.quantity_on_hand), 0) as total_quantity,
    COALESCE(SUM(inv.quantity_reserved), 0) as total_reserved,
    COALESCE(SUM(inv.quantity_on_hand - inv.quantity_reserved), 0) as total_available,
    -- Value calculations
    COALESCE(SUM(inv.quantity_on_hand * p.cost), 0) as inventory_value,
    -- Status indicators
    CASE 
        WHEN COALESCE(SUM(inv.quantity_on_hand), 0) = 0 THEN 'Out of Stock'
        WHEN COALESCE(SUM(inv.quantity_on_hand - inv.quantity_reserved), 0) <= 0 THEN 'Reserved'
        WHEN COALESCE(SUM(inv.quantity_on_hand), 0) <= MIN(inv.minimum_level) THEN 'Low Stock'
        ELSE 'In Stock'
    END as stock_status,
    p.created_at,
    p.updated_at
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN suppliers s ON p.supplier_id = s.id
LEFT JOIN inventory inv ON p.id = inv.product_id
WHERE p.status = 'active'
GROUP BY p.id, p.sku, p.name, p.description, p.price, p.cost, p.barcode, 
         p.status, c.name, c.code, s.name, p.created_at, p.updated_at;

Product Movement History

Recent stock movements for products:

CREATE OR REPLACE VIEW v_product_movements AS
SELECT 
    sm.id,
    p.sku,
    p.name as product_name,
    w.name as warehouse_name,
    sm.movement_type,
    sm.quantity,
    sm.unit_cost,
    sm.total_cost,
    sm.reference_id,
    sm.notes,
    u.username as created_by_user,
    sm.created_at
FROM stock_movements sm
JOIN products p ON sm.product_id = p.id
JOIN warehouses w ON sm.warehouse_id = w.id
LEFT JOIN users u ON sm.created_by = u.id
ORDER BY sm.created_at DESC;

Inventory Views

Current Inventory Levels

Real-time inventory across all locations:

CREATE OR REPLACE VIEW v_inventory_levels AS
SELECT 
    inv.id,
    p.sku,
    p.name as product_name,
    p.barcode,
    c.name as category_name,
    w.name as warehouse_name,
    wz.name as zone_name,
    wl.name as location_name,
    inv.quantity_on_hand,
    inv.quantity_reserved,
    inv.quantity_on_hand - inv.quantity_reserved as available_quantity,
    inv.minimum_level,
    inv.maximum_level,
    CASE 
        WHEN inv.quantity_on_hand = 0 THEN 'Empty'
        WHEN inv.quantity_on_hand <= inv.minimum_level THEN 'Low'
        WHEN inv.quantity_on_hand >= inv.maximum_level THEN 'Overstocked'
        ELSE 'Normal'
    END as stock_level_status,
    inv.last_counted,
    inv.last_updated,
    -- Calculate days since last count
    EXTRACT(DAYS FROM NOW() - inv.last_counted) as days_since_count
FROM inventory inv
JOIN products p ON inv.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN warehouses w ON inv.warehouse_id = w.id
LEFT JOIN warehouse_locations wl ON inv.location_id = wl.id
LEFT JOIN warehouse_zones wz ON wl.zone_id = wz.id
WHERE p.status = 'active';

Low Stock Alert View

Products that need attention:

CREATE OR REPLACE VIEW v_low_stock_alerts AS
SELECT 
    p.id as product_id,
    p.sku,
    p.name as product_name,
    w.name as warehouse_name,
    inv.quantity_on_hand,
    inv.minimum_level,
    inv.minimum_level - inv.quantity_on_hand as shortage_quantity,
    p.cost * (inv.minimum_level - inv.quantity_on_hand) as reorder_value,
    s.name as supplier_name,
    s.email as supplier_email,
    inv.last_updated
FROM inventory inv
JOIN products p ON inv.product_id = p.id
JOIN warehouses w ON inv.warehouse_id = w.id
LEFT JOIN suppliers s ON p.supplier_id = s.id
WHERE inv.quantity_on_hand <= inv.minimum_level
AND p.status = 'active'
ORDER BY (inv.minimum_level - inv.quantity_on_hand) DESC;

Order Views

Sales Order Summary

Comprehensive sales order information:

CREATE OR REPLACE VIEW v_sales_order_summary AS
SELECT 
    so.id,
    so.order_number,
    so.status,
    so.order_date,
    so.required_date,
    so.shipped_date,
    c.name as customer_name,
    c.email as customer_email,
    w.name as warehouse_name,
    u.username as created_by_user,
    -- Order totals
    COUNT(soi.id) as item_count,
    SUM(soi.quantity) as total_quantity,
    SUM(soi.unit_price * soi.quantity) as subtotal,
    so.tax_amount,
    so.shipping_amount,
    so.total_amount,
    -- Fulfillment metrics
    CASE 
        WHEN so.status = 'shipped' THEN 
            EXTRACT(DAYS FROM so.shipped_date - so.order_date)
    END as fulfillment_days,
    CASE 
        WHEN so.required_date < CURRENT_DATE AND so.status != 'shipped' THEN true
        ELSE false
    END as is_overdue
FROM sales_orders so
JOIN customers c ON so.customer_id = c.id
JOIN warehouses w ON so.warehouse_id = w.id
LEFT JOIN users u ON so.created_by = u.id
LEFT JOIN sales_order_items soi ON so.id = soi.sales_order_id
GROUP BY so.id, so.order_number, so.status, so.order_date, so.required_date,
         so.shipped_date, c.name, c.email, w.name, u.username,
         so.tax_amount, so.shipping_amount, so.total_amount;

Purchase Order Status

Purchase order tracking and status:

CREATE OR REPLACE VIEW v_purchase_order_status AS
SELECT 
    po.id,
    po.order_number,
    po.status,
    po.order_date,
    po.expected_date,
    po.received_date,
    s.name as supplier_name,
    s.contact_person,
    s.email as supplier_email,
    w.name as warehouse_name,
    -- Order metrics
    COUNT(poi.id) as item_count,
    SUM(poi.quantity) as total_quantity,
    SUM(poi.quantity_received) as total_received,
    SUM(poi.unit_cost * poi.quantity) as total_value,
    -- Completion percentage
    CASE 
        WHEN SUM(poi.quantity) > 0 THEN
            ROUND((SUM(poi.quantity_received)::DECIMAL / SUM(poi.quantity)) * 100, 2)
        ELSE 0
    END as completion_percentage,
    -- Status indicators
    CASE 
        WHEN po.expected_date < CURRENT_DATE AND po.status = 'ordered' THEN true
        ELSE false
    END as is_overdue
FROM purchase_orders po
JOIN suppliers s ON po.supplier_id = s.id
JOIN warehouses w ON po.warehouse_id = w.id
LEFT JOIN purchase_order_items poi ON po.id = poi.purchase_order_id
GROUP BY po.id, po.order_number, po.status, po.order_date, po.expected_date,
         po.received_date, s.name, s.contact_person, s.email, w.name;

Financial Views

Inventory Valuation

Current inventory value by various methods:

CREATE OR REPLACE VIEW v_inventory_valuation AS
SELECT 
    w.name as warehouse_name,
    c.name as category_name,
    COUNT(DISTINCT p.id) as product_count,
    SUM(inv.quantity_on_hand) as total_quantity,
    -- FIFO valuation
    SUM(inv.quantity_on_hand * p.cost) as fifo_value,
    -- Average cost valuation
    SUM(inv.quantity_on_hand * COALESCE(avg_cost.average_cost, p.cost)) as average_cost_value,
    -- Retail value
    SUM(inv.quantity_on_hand * p.price) as retail_value
FROM inventory inv
JOIN products p ON inv.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN warehouses w ON inv.warehouse_id = w.id
LEFT JOIN (
    SELECT 
        product_id,
        AVG(unit_cost) as average_cost
    FROM stock_movements 
    WHERE movement_type IN ('purchase', 'adjustment_in')
    GROUP BY product_id
) avg_cost ON p.id = avg_cost.product_id
WHERE inv.quantity_on_hand > 0
GROUP BY w.name, c.name
ORDER BY fifo_value DESC;

Sales Performance

Sales metrics and performance tracking:

CREATE OR REPLACE VIEW v_sales_performance AS
SELECT 
    DATE_TRUNC('month', so.order_date) as month,
    c.name as category_name,
    w.name as warehouse_name,
    -- Order metrics
    COUNT(DISTINCT so.id) as order_count,
    COUNT(DISTINCT so.customer_id) as unique_customers,
    SUM(soi.quantity) as units_sold,
    -- Financial metrics
    SUM(soi.unit_price * soi.quantity) as gross_revenue,
    SUM((soi.unit_price - p.cost) * soi.quantity) as gross_profit,
    -- Average metrics
    AVG(so.total_amount) as average_order_value,
    AVG(soi.unit_price) as average_unit_price,
    -- Profit margins
    CASE 
        WHEN SUM(soi.unit_price * soi.quantity) > 0 THEN
            ROUND((SUM((soi.unit_price - p.cost) * soi.quantity) / 
                   SUM(soi.unit_price * soi.quantity)) * 100, 2)
        ELSE 0
    END as profit_margin_percentage
FROM sales_orders so
JOIN sales_order_items soi ON so.id = soi.sales_order_id
JOIN products p ON soi.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN warehouses w ON so.warehouse_id = w.id
WHERE so.status = 'shipped'
GROUP BY DATE_TRUNC('month', so.order_date), c.name, w.name
ORDER BY month DESC, gross_revenue DESC;

Materialized Views

For frequently accessed, complex data, materialized views provide better performance:

Daily Inventory Summary (Materialized)

CREATE MATERIALIZED VIEW mv_daily_inventory_summary AS
SELECT 
    CURRENT_DATE as summary_date,
    w.id as warehouse_id,
    w.name as warehouse_name,
    c.id as category_id,
    c.name as category_name,
    COUNT(DISTINCT p.id) as product_count,
    SUM(inv.quantity_on_hand) as total_quantity,
    SUM(inv.quantity_reserved) as total_reserved,
    SUM(inv.quantity_on_hand - inv.quantity_reserved) as total_available,
    SUM(inv.quantity_on_hand * p.cost) as total_value,
    COUNT(CASE WHEN inv.quantity_on_hand = 0 THEN 1 END) as out_of_stock_count,
    COUNT(CASE WHEN inv.quantity_on_hand <= inv.minimum_level THEN 1 END) as low_stock_count
FROM inventory inv
JOIN products p ON inv.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN warehouses w ON inv.warehouse_id = w.id
WHERE p.status = 'active'
GROUP BY w.id, w.name, c.id, c.name;

-- Create index for performance
CREATE INDEX idx_mv_daily_inventory_summary_date 
ON mv_daily_inventory_summary(summary_date);

-- Refresh function
CREATE OR REPLACE FUNCTION refresh_daily_inventory_summary()
RETURNS VOID AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_inventory_summary;
END;
$$ LANGUAGE plpgsql;

Reporting Views

ABC Analysis View

Product classification by value and movement:

CREATE OR REPLACE VIEW v_abc_analysis AS
WITH product_metrics AS (
    SELECT 
        p.id,
        p.sku,
        p.name,
        SUM(soi.quantity * soi.unit_price) as annual_revenue,
        COUNT(soi.id) as transaction_count,
        SUM(inv.quantity_on_hand * p.cost) as inventory_value
    FROM products p
    LEFT JOIN sales_order_items soi ON p.id = soi.product_id
    LEFT JOIN sales_orders so ON soi.sales_order_id = so.id 
        AND so.order_date >= CURRENT_DATE - INTERVAL '12 months'
    LEFT JOIN inventory inv ON p.id = inv.product_id
    GROUP BY p.id, p.sku, p.name
),
ranked_products AS (
    SELECT *,
        PERCENT_RANK() OVER (ORDER BY annual_revenue DESC) as revenue_percentile
    FROM product_metrics
)
SELECT 
    *,
    CASE 
        WHEN revenue_percentile <= 0.8 THEN 'A'
        WHEN revenue_percentile <= 0.95 THEN 'B'
        ELSE 'C'
    END as abc_classification
FROM ranked_products
ORDER BY annual_revenue DESC;

Security Views

Views can also implement security by filtering sensitive data:

Public Product Catalog

Customer-facing product information without costs:

CREATE OR REPLACE VIEW v_public_product_catalog AS
SELECT 
    p.id,
    p.sku,
    p.name,
    p.description,
    p.price,
    p.barcode,
    c.name as category_name,
    -- Availability without exact quantities
    CASE 
        WHEN SUM(inv.quantity_on_hand - inv.quantity_reserved) > 0 THEN 'In Stock'
        ELSE 'Out of Stock'
    END as availability
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN inventory inv ON p.id = inv.product_id
WHERE p.status = 'active'
AND p.is_public = true
GROUP BY p.id, p.sku, p.name, p.description, p.price, p.barcode, c.name;

View Maintenance

Performance Monitoring

-- Monitor view performance
SELECT 
    schemaname,
    viewname,
    definition
FROM pg_views 
WHERE schemaname = 'public'
ORDER BY viewname;

-- Check materialized view freshness
SELECT 
    schemaname,
    matviewname,
    hasindexes,
    ispopulated,
    definition
FROM pg_matviews
WHERE schemaname = 'public';

Refresh Strategies

Automated refresh for materialized views:

-- Daily refresh schedule (to be used with pg_cron or similar)
CREATE OR REPLACE FUNCTION refresh_all_materialized_views()
RETURNS VOID AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_inventory_summary;
    -- Add other materialized views here
    
    RAISE NOTICE 'All materialized views refreshed at %', NOW();
END;
$$ LANGUAGE plpgsql;

Best Practices

  1. Naming Convention: Use v_ prefix for views, mv_ for materialized views
  2. Documentation: Include clear comments explaining view purpose
  3. Performance: Use indexes on underlying tables for view performance
  4. Security: Filter sensitive data in security-focused views
  5. Maintenance: Regular refresh schedule for materialized views
  6. Testing: Test view performance with realistic data volumes