Database Architecture

Data models, database design, and relationship patterns for the Smart Shelf system.

Database Architecture

Comprehensive guide to Smart Shelf's database architecture, covering data models, security patterns, performance optimization, and migration strategies using PostgreSQL and Supabase.

Architecture Overview

Smart Shelf's database architecture is built on PostgreSQL through Supabase, providing:

Scalable Data Models - Well-structured entity relationships
Row Level Security (RLS) - Fine-grained access control
Performance Optimization - Strategic indexing and query optimization
Migration Management - Safe schema evolution and versioning

Technology Stack

Database: PostgreSQL (via Supabase)
ORM: Supabase JavaScript Client
Schema Management: Supabase Migrations
Security: Row Level Security (RLS)
Real-time: Supabase Realtime Engine

Database Architecture Sections

Data Models & Schema

Comprehensive overview of database entity relationships, table structures, and data model design patterns.

Key Topics:

Entity Relationship Model
Core Table Structures (Users, Products, Inventory, Warehouses)
Validation and Constraints
Database Functions and Views
Data Integrity Rules

Security & Access Control

Database security implementation using Row Level Security (RLS), access control patterns, and data protection strategies.

Key Topics:

Row Level Security (RLS) Policies
Role-Based Access Control (RBAC)
Data Isolation Patterns
Audit and Logging
Data Encryption
GDPR Compliance

Performance & Optimization

Database performance optimization strategies, indexing patterns, and query optimization techniques.

Key Topics:

Strategic Indexing
Query Optimization
Connection Pooling
Caching Strategies
Performance Monitoring
Maintenance Tasks

Migrations & Versioning

Database migration and versioning strategy covering schema evolution, deployment processes, and data migration patterns.

Key Topics:

Migration Strategy
Version Control
Rollback Procedures
Data Migration Patterns
Zero-Downtime Deployments
Best Practices

Quick Reference

Core Entities

Users & Profiles - Authentication and user management
Products & Categories - Product catalog management
Inventory - Stock levels and availability
Warehouses - Location and storage management
Stock Movements - Transaction history and tracking

Security Features

Row Level Security (RLS) for data isolation
Role-based access control (Admin, Manager, Staff, Viewer)
Audit logging for all sensitive operations
Data encryption for sensitive information

Performance Features

Strategic indexing for common query patterns
Materialized views for complex reporting
Connection pooling and resource management
Query optimization and caching strategies

Migration Features

Version-controlled schema changes
Safe rollback procedures
Data migration batching
Zero-downtime deployment support

Getting Started

To understand Smart Shelf's database architecture:

Start with Data Models & Schema to understand the core data structures
Review Security & Access Control to see how data is protected
Explore Performance & Optimization to understand scalability approaches
Study Migrations & Versioning to learn about schema evolution

Database Design Principles

1. Data Integrity First

Comprehensive constraints and validation
Foreign key relationships
Check constraints for business rules
Audit trails for critical operations

2. Security by Design

Row Level Security (RLS) implementation
Role-based access control
Data encryption for sensitive fields
Complete audit logging

3. Performance Optimization

Strategic indexing for query patterns
Efficient data types and structures
Connection pooling and resource management
Caching layers for frequently accessed data

4. Scalability Planning

Normalized data structures
Efficient query patterns
Horizontal scaling considerations
Real-time capability integration

This database architecture ensures data integrity, security, and performance while supporting the complex inventory management requirements of Smart Shelf.

created_at: timestamp
updated_at: timestamp

product_categories

id: UUID - Primary key
name: string - Category name
description: text - Category description
parent_id: UUID - References product_categories(id)
sort_order: integer - Display order
is_active: boolean - Category status
created_at: timestamp

product_barcodes

id: UUID - Primary key
product_id: UUID - References products(id)
barcode: string - Barcode value (unique)
barcode_type: enum - Type (UPC, EAN, CODE128, etc.)
is_primary: boolean - Primary barcode flag
created_at: timestamp

product_variants

id: UUID - Primary key
product_id: UUID - References products(id)
name: string - Variant name (size, color, etc.)
sku: string - Variant SKU
cost_price: decimal - Variant cost
selling_price: decimal - Variant price
attributes: jsonb - Variant attributes
is_active: boolean
created_at: timestamp

3. Inventory Management

inventory

id: UUID - Primary key
product_id: UUID - References products(id)
warehouse_id: UUID - References warehouses(id)
quantity_on_hand: integer - Current stock
quantity_allocated: integer - Reserved stock
quantity_available: integer - Available stock
last_counted: timestamp - Last physical count
updated_at: timestamp

stock_movements

id: UUID - Primary key
product_id: UUID - References products(id)
warehouse_id: UUID - References warehouses(id)
movement_type: enum - Type (IN, OUT, TRANSFER, ADJUSTMENT)
quantity: integer - Movement quantity
unit_cost: decimal - Cost per unit
reference_id: UUID - Reference to source document
reference_type: string - Source type (purchase_order, sales_order, etc.)
reason: string - Movement reason
notes: text - Additional notes
created_by: UUID - References user_profiles(id)
created_at: timestamp

inventory_adjustments

id: UUID - Primary key
product_id: UUID - References products(id)
warehouse_id: UUID - References warehouses(id)
quantity_before: integer - Stock before adjustment
quantity_after: integer - Stock after adjustment
adjustment_quantity: integer - Adjustment amount
reason: string - Adjustment reason
notes: text - Additional notes
approved_by: UUID - References user_profiles(id)
created_by: UUID - References user_profiles(id)
created_at: timestamp

4. Order Management

purchase_orders

id: UUID - Primary key
po_number: string - Purchase order number (unique)
supplier_id: UUID - References suppliers(id)
warehouse_id: UUID - References warehouses(id)
status: enum - Order status (DRAFT, SENT, RECEIVED, COMPLETED)
order_date: date - Order date
expected_date: date - Expected delivery date
received_date: date - Actual received date
subtotal: decimal - Order subtotal
tax_amount: decimal - Tax amount
total_amount: decimal - Total amount
notes: text - Order notes
created_by: UUID - References user_profiles(id)
created_at: timestamp
updated_at: timestamp

purchase_order_items

id: UUID - Primary key
purchase_order_id: UUID - References purchase_orders(id)
product_id: UUID - References products(id)
quantity_ordered: integer - Ordered quantity
quantity_received: integer - Received quantity
unit_cost: decimal - Cost per unit
line_total: decimal - Line total
notes: text - Line item notes

sales_orders

id: UUID - Primary key
order_number: string - Sales order number (unique)
customer_id: UUID - References customers(id)
warehouse_id: UUID - References warehouses(id)
status: enum - Order status (PENDING, PROCESSING, SHIPPED, DELIVERED)
order_date: date - Order date
ship_date: date - Ship date
delivery_date: date - Delivery date
subtotal: decimal - Order subtotal
tax_amount: decimal - Tax amount
total_amount: decimal - Total amount
shipping_address: jsonb - Shipping address
billing_address: jsonb - Billing address
created_by: UUID - References user_profiles(id)
created_at: timestamp

sales_order_items

id: UUID - Primary key
sales_order_id: UUID - References sales_orders(id)
product_id: UUID - References products(id)
quantity: integer - Ordered quantity
unit_price: decimal - Price per unit
line_total: decimal - Line total
notes: text - Line item notes

5. Warehouse Management

warehouses

id: UUID - Primary key
name: string - Warehouse name
code: string - Warehouse code (unique)
address: jsonb - Warehouse address
contact_info: jsonb - Contact information
capacity: integer - Storage capacity
is_active: boolean - Warehouse status
created_at: timestamp

warehouse_locations

id: UUID - Primary key
warehouse_id: UUID - References warehouses(id)
location_code: string - Location code (aisle, bin, etc.)
location_type: enum - Type (AISLE, RACK, SHELF, BIN)
parent_location_id: UUID - References warehouse_locations(id)
capacity: integer - Location capacity
is_active: boolean
created_at: timestamp

warehouse_transfers

id: UUID - Primary key
transfer_number: string - Transfer number (unique)
from_warehouse_id: UUID - References warehouses(id)
to_warehouse_id: UUID - References warehouses(id)
status: enum - Transfer status (PENDING, IN_TRANSIT, COMPLETED)
transfer_date: date - Transfer date
notes: text - Transfer notes
created_by: UUID - References user_profiles(id)
created_at: timestamp

6. Partner Management

suppliers

id: UUID - Primary key
name: string - Supplier name
code: string - Supplier code (unique)
contact_person: string - Contact person
email: string - Email address
phone: string - Phone number
address: jsonb - Supplier address
payment_terms: string - Payment terms
is_active: boolean - Supplier status
created_at: timestamp

customers

id: UUID - Primary key
name: string - Customer name
code: string - Customer code (unique)
contact_person: string - Contact person
email: string - Email address
phone: string - Phone number
billing_address: jsonb - Billing address
shipping_address: jsonb - Shipping address
payment_terms: string - Payment terms
is_active: boolean - Customer status
created_at: timestamp

Row Level Security (RLS) Policies

User-Based Access Control

-- Users can only access their own profile
CREATE POLICY "Users can view own profile" ON user_profiles
  FOR SELECT USING (auth.uid() = id);

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

Role-Based Access Control

-- Only admins can manage users
CREATE POLICY "Admins can manage users" ON user_profiles
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

-- Managers can view all data in their warehouse
CREATE POLICY "Managers can view warehouse data" ON inventory
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() 
      AND (role IN ('admin', 'manager') OR warehouse_id = inventory.warehouse_id)
    )
  );

Data Isolation

-- Users can only access data from their assigned warehouse
CREATE POLICY "Warehouse data isolation" ON stock_movements
  FOR ALL USING (
    warehouse_id IN (
      SELECT COALESCE(warehouse_id, (SELECT id FROM warehouses LIMIT 1))
      FROM user_profiles
      WHERE id = auth.uid()
    )
  );

Database Performance Optimization

Indexing Strategy

-- Primary indexes for foreign keys
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_inventory_product_id ON inventory(product_id);
CREATE INDEX idx_inventory_warehouse_id ON inventory(warehouse_id);

-- Composite indexes for common queries
CREATE INDEX idx_inventory_product_warehouse ON inventory(product_id, warehouse_id);
CREATE INDEX idx_stock_movements_product_date ON stock_movements(product_id, created_at DESC);

-- Partial indexes for active records
CREATE INDEX idx_products_active ON products(name) WHERE is_active = true;
CREATE INDEX idx_suppliers_active ON suppliers(name) WHERE is_active = true;

Query Optimization

Efficient Inventory Queries

-- Get current inventory levels with product details
SELECT 
  p.name,
  p.sku,
  i.quantity_on_hand,
  i.quantity_available,
  w.name as warehouse_name
FROM inventory i
JOIN products p ON i.product_id = p.id
JOIN warehouses w ON i.warehouse_id = w.id
WHERE i.quantity_available > 0
  AND p.is_active = true;

Stock Movement History

-- Get stock movement history for a product
SELECT 
  sm.movement_type,
  sm.quantity,
  sm.created_at,
  up.full_name as created_by_name
FROM stock_movements sm
JOIN user_profiles up ON sm.created_by = up.id
WHERE sm.product_id = $1
ORDER BY sm.created_at DESC
LIMIT 50;

Data Migration and Versioning

Migration Strategy

Schema Versioning: Each migration has a version number
Rollback Support: All migrations include rollback scripts
Data Validation: Post-migration data integrity checks
Backup Strategy: Automated backups before major migrations

Example Migration

-- Migration: 001_create_products_table.sql
CREATE TABLE products (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  sku VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Create updated_at trigger
CREATE TRIGGER update_products_updated_at
  BEFORE UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

Real-time Subscriptions

Inventory Updates

// Subscribe to inventory changes
const subscription = supabase
  .channel('inventory-changes')
  .on('postgres_changes', {
    event: '*',
    schema: 'public',
    table: 'inventory',
    filter: `warehouse_id=eq.${warehouseId}`
  }, (payload) => {
    // Handle inventory updates
    updateInventoryState(payload);
  })
  .subscribe();

Stock Movement Tracking

// Subscribe to stock movements
const subscription = supabase
  .channel('stock-movements')
  .on('postgres_changes', {
    event: 'INSERT',
    schema: 'public',
    table: 'stock_movements'
  }, (payload) => {
    // Handle new stock movements
    addStockMovement(payload.new);
  })
  .subscribe();

Data Backup and Recovery

Automated Backups

Daily Backups: Full database backup daily at 2 AM UTC
Point-in-Time Recovery: 7-day recovery window
Geographic Replication: Backups stored in multiple regions

Disaster Recovery

Primary Failure: Automatic failover to replica
Data Corruption: Point-in-time recovery from backups
Regional Outage: Cross-region backup restoration

This database architecture ensures data integrity, security, and performance while supporting the complex inventory management requirements of Smart Shelf.

Code Organization

Data Models & Schema