Database Schema Overview

High-level overview of the Lager Guru database schema.

Overview

Lager Guru uses PostgreSQL with a multi-tenant architecture. This document provides an overview of the database schema structure.

Schema Architecture

erDiagram
    TENANTS ||--o{ USERS : has
    TENANTS ||--o{ WAREHOUSES : has
    WAREHOUSES ||--o{ ZONES : contains
    WAREHOUSES ||--o{ INVENTORY : stores
    ORDERS ||--o{ ORDER_ITEMS : contains
    ORDERS ||--o{ SHIPMENTS : generates
    SHIPMENTS ||--o{ DRIVERS : assigned
    WORKERS ||--o{ TASKS : performs

Core Tables

Tenant Management

tenants

Multi-tenant organization table.

• id (uuid, primary key)
• name (text)
• slug (text, unique)
• settings (jsonb)
• created_at (timestamp)
• updated_at (timestamp)

users

User accounts across all tenants.

• id (uuid, primary key)
• email (text, unique)
• tenant_id (uuid, foreign key)
• role (text)
• metadata (jsonb)
• created_at (timestamp)

Warehouse Management

warehouses

Warehouse locations.

• id (uuid, primary key)
• tenant_id (uuid, foreign key)
• name (text)
• address (jsonb)
• settings (jsonb)
• created_at (timestamp)

zones

Warehouse zones and areas.

• id (uuid, primary key)
• warehouse_id (uuid, foreign key)
• name (text)
• coordinates (jsonb)
• type (text)
• created_at (timestamp)

Inventory

inventory_items

Inventory stock items.

• id (uuid, primary key)
• tenant_id (uuid, foreign key)
• sku (text)
• name (text)
• quantity (integer)
• location (text)
• metadata (jsonb)
• updated_at (timestamp)

inventory_movements

Inventory movement history.

• id (uuid, primary key)
• item_id (uuid, foreign key)
• type (text) - 'inbound', 'outbound', 'adjustment'
• quantity (integer)
• from_location (text)
• to_location (text)
• created_at (timestamp)

Orders & Shipments

orders

Customer orders.

• id (uuid, primary key)
• tenant_id (uuid, foreign key)
• order_number (text, unique)
• status (text)
• customer_info (jsonb)
• created_at (timestamp)
• updated_at (timestamp)

order_items

Order line items.

• id (uuid, primary key)
• order_id (uuid, foreign key)
• item_id (uuid, foreign key)
• quantity (integer)
• picked_quantity (integer)
• status (text)

shipments

Shipping records.

• id (uuid, primary key)
• order_id (uuid, foreign key)
• driver_id (uuid, foreign key)
• status (text)
• tracking_number (text)
• route (jsonb)
• created_at (timestamp)

Operations

tasks

Work tasks and assignments.

• id (uuid, primary key)
• tenant_id (uuid, foreign key)
• type (text) - 'pick', 'pack', 'inventory', 'maintenance'
• worker_id (uuid, foreign key)
• status (text)
• priority (integer)
• metadata (jsonb)
• created_at (timestamp)
• completed_at (timestamp)

workers

Worker profiles.

• id (uuid, primary key)
• user_id (uuid, foreign key)
• tenant_id (uuid, foreign key)
• role (text)
• shift_id (uuid, foreign key)
• status (text)
• metadata (jsonb)

drivers

Driver profiles.

• id (uuid, primary key)
• user_id (uuid, foreign key)
• tenant_id (uuid, foreign key)
• vehicle_info (jsonb)
• status (text)
• current_location (jsonb)
• metadata (jsonb)

Row-Level Security (RLS)

All tables implement Row-Level Security for tenant isolation:

• Policies enforce tenant-based access
• Users can only access data from their tenant
• Service role bypasses RLS for system operations

See RLS Policies for detailed information.

Indexes

Primary Indexes

• All primary keys are automatically indexed
• Foreign keys are indexed for join performance

Custom Indexes

• tenants.slug - Unique index for tenant lookup
• users.email - Unique index for authentication
• inventory_items.sku - Index for SKU lookups
• orders.order_number - Unique index for order tracking
• tasks.status - Index for task filtering

See Indexing for detailed indexing strategies.

Views

Materialized Views

• inventory_summary - Aggregated inventory statistics
• worker_performance - Worker productivity metrics
• order_statistics - Order completion statistics

Standard Views

• active_tasks - Currently active tasks
• pending_shipments - Shipments awaiting assignment
• inventory_locations - Inventory by location

See Views for complete view documentation.

Best Practices

Schema Design

• Use UUIDs for primary keys
• Include tenant_id in all tenant-scoped tables
• Use JSONB for flexible metadata
• Add timestamps (created_at, updated_at) to all tables

Performance

• Index frequently queried columns
• Use materialized views for complex aggregations
• Partition large tables by date when appropriate
• Monitor query performance regularly

Related Documentation

• Database Schema - Complete schema reference
• RLS Policies - Row-Level Security details
• Indexing - Indexing strategies
• Views - Database views
• Migrations - Schema migrations

Next Steps

• Database Schema - Detailed schema documentation
• RLS Policies - Security policies
• Multi-Tenant Structure - Multi-tenant architecture