Database Schema Reference

This document provides comprehensive documentation for the LUStores database schema, including all tables, relationships, and data types.

Overview

The LUStores application uses PostgreSQL with Drizzle ORM for type-safe database operations. The schema is defined in shared/schema.ts and uses the following key design principles:

• Referential Integrity: Foreign key constraints ensure data consistency

• Audit Trails: All critical operations are logged with user and timestamp information

• Soft Deletes: Items are marked as inactive rather than permanently deleted

• Flexible Permissions: Granular permission system for different user roles

• VAT Support: Built-in VAT calculation and tracking for sales

Core Tables

Users Table

CREATE TABLE users (
  id VARCHAR PRIMARY KEY,
  email VARCHAR UNIQUE NOT NULL,
  password_hash VARCHAR,  -- NULL for SSO users
  first_name VARCHAR,
  last_name VARCHAR,
  role VARCHAR NOT NULL DEFAULT 'user',  -- 'user', 'manager', 'admin', 'superuser'
  is_active BOOLEAN NOT NULL DEFAULT true,
  must_change_password BOOLEAN NOT NULL DEFAULT false,
  last_login TIMESTAMP,
  profile_image_url VARCHAR,
  show_picking_list BOOLEAN NOT NULL DEFAULT true,  -- User preference for displaying picking lists
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Key Features: - Supports both local authentication (with password hash) and SSO (password_hash = NULL) - Role-based access control with four levels: user, manager, admin, superuser - Audit fields for tracking login activity

Categories Table

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL,
  description TEXT,
  icon VARCHAR(50) NOT NULL DEFAULT 'fas fa-box',
  color VARCHAR(50) NOT NULL DEFAULT 'blue',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Key Features: - Visual customization with icons and colors for UI display - Unique category names prevent duplicates - Default values ensure consistent appearance

Items Table

CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  sku VARCHAR(100) UNIQUE NOT NULL,
  description TEXT,
  category_id INTEGER NOT NULL REFERENCES categories(id),
  price DECIMAL(10,2) NOT NULL,
  vat_rate DECIMAL(5,4) NOT NULL DEFAULT 0.2000,  -- 20% VAT default
  vat_included BOOLEAN NOT NULL DEFAULT true,
  current_stock INTEGER NOT NULL DEFAULT 0,
  minimum_stock INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_by VARCHAR REFERENCES users(id),
  updated_by VARCHAR REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Key Features: - SKU (Stock Keeping Unit) must be unique across all items - Built-in VAT rate and calculation support - Stock level tracking with minimum threshold alerts - Soft delete using is_active flag - Full audit trail with created_by and updated_by fields

Stock Movements Table

CREATE TABLE stock_movements (
  id SERIAL PRIMARY KEY,
  item_id INTEGER NOT NULL REFERENCES items(id),
  type VARCHAR(20) NOT NULL,  -- 'in', 'out', 'adjustment'
  quantity INTEGER NOT NULL,
  previous_stock INTEGER NOT NULL,
  new_stock INTEGER NOT NULL,
  reason TEXT,
  performed_by VARCHAR REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW()
);

Movement Types: - in: Stock received (adds to current stock) - out: Stock issued (subtracts from current stock) - adjustment: Manual correction (sets stock to exact quantity)

Notes Table

CREATE TABLE notes (
  id SERIAL PRIMARY KEY,
  text TEXT NOT NULL,
  reference_type VARCHAR(50) NOT NULL,  -- 'item', 'sale', 'order', 'charge_code', 'vendor'
  reference_id VARCHAR(255) NOT NULL,  -- ID of the referenced entity
  created_by VARCHAR REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_notes_reference ON notes(reference_type, reference_id);
CREATE INDEX idx_notes_created_by ON notes(created_by);

Key Features: - Flexible annotation system for any entity type - Polymorphic reference design (reference_type + reference_id) - Full audit trail with creator and timestamps - Efficient querying with composite index on reference fields

Supported Reference Types: - item: Notes on inventory items (stock issues, supplier notes, etc.) - sale: Notes on completed sales (special instructions, customer feedback) - order: Notes on purchase orders (delivery instructions, supplier communications) - charge_code: Notes on charge codes (usage restrictions, budget notes) - vendor: Notes on suppliers/vendors (contact notes, quality issues)

Use Cases: - Track item-specific issues or handling instructions - Record customer requests or delivery notes for sales - Document supplier communications for orders - Add usage restrictions or budget notes for charge codes - Record vendor quality issues or preferred contact methods

Sales and Quotes Tables

Sales Table

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  sale_id VARCHAR(50) UNIQUE NOT NULL,  -- Generated ID like S202501291234
  charge_code VARCHAR(100) NOT NULL,
  subtotal_amount DECIMAL(12,2) NOT NULL,  -- Amount before VAT
  vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_amount DECIMAL(12,2) NOT NULL,  -- Total including VAT
  vat_applied BOOLEAN NOT NULL DEFAULT true,
  customer_info JSONB,
  notes TEXT,
  status VARCHAR(20) NOT NULL DEFAULT 'completed',
  is_paid BOOLEAN NOT NULL DEFAULT false,  -- Payment reconciliation status
  delivered_to VARCHAR(200),  -- Name of recipient who received items
  delivered_to_email VARCHAR(200),  -- Email of recipient
  delivered_at TIMESTAMP,  -- When delivery was confirmed
  processed_by VARCHAR REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Sale Items Table

CREATE TABLE sale_items (
  id SERIAL PRIMARY KEY,
  sale_id INTEGER NOT NULL REFERENCES sales(id),
  item_id INTEGER NOT NULL REFERENCES items(id),
  item_name VARCHAR(200) NOT NULL,  -- Snapshot at time of sale
  item_sku VARCHAR(100) NOT NULL,   -- Snapshot at time of sale
  unit_price DECIMAL(10,2) NOT NULL,
  vat_rate DECIMAL(5,4) NOT NULL,
  vat_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  quantity INTEGER NOT NULL,
  subtotal DECIMAL(12,2) NOT NULL,
  total_with_vat DECIMAL(12,2) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

Quotes Table

CREATE TABLE quotes (
  id SERIAL PRIMARY KEY,
  quote_id VARCHAR(50) UNIQUE NOT NULL,  -- Generated ID like Q202501291234
  charge_code VARCHAR(100) NOT NULL,
  subtotal_amount DECIMAL(12,2) NOT NULL,
  vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_amount DECIMAL(12,2) NOT NULL,
  vat_applied BOOLEAN NOT NULL DEFAULT true,
  customer_info JSONB,
  notes TEXT,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',  -- 'draft', 'processed', 'deleted'
  created_by VARCHAR REFERENCES users(id),
  processed_by VARCHAR REFERENCES users(id),
  processed_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Quote Items Table

CREATE TABLE quote_items (
  id SERIAL PRIMARY KEY,
  quote_id INTEGER NOT NULL REFERENCES quotes(id),
  item_id INTEGER NOT NULL REFERENCES items(id),
  item_name VARCHAR(200) NOT NULL,
  item_sku VARCHAR(100) NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  vat_rate DECIMAL(5,4) NOT NULL,
  vat_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  quantity INTEGER NOT NULL,
  subtotal DECIMAL(12,2) NOT NULL,
  total_with_vat DECIMAL(12,2) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

Procurement Tables

Suppliers Table

CREATE TABLE suppliers (
  id VARCHAR PRIMARY KEY,
  name VARCHAR NOT NULL,
  contact VARCHAR,
  email VARCHAR,
  phone VARCHAR,
  address VARCHAR,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Sources Table (Item-Supplier Relationship)

CREATE TABLE sources (
  id SERIAL PRIMARY KEY,
  item_id INTEGER NOT NULL REFERENCES items(id),
  supplier_id VARCHAR NOT NULL REFERENCES suppliers(id),
  price DECIMAL(10,2),
  notes TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

Orders Table

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_id VARCHAR(50) UNIQUE NOT NULL,  -- Generated ID like O202501291234
  supplier_id VARCHAR REFERENCES suppliers(id),
  status VARCHAR(20) NOT NULL DEFAULT 'pending',  -- 'pending', 'received', 'cancelled'
  notes TEXT,
  total_amount DECIMAL(12,2),
  created_by VARCHAR REFERENCES users(id),
  received_by VARCHAR REFERENCES users(id),
  received_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Order Items Table

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  item_id INTEGER REFERENCES items(id),  -- Optional for new items
  item_name VARCHAR(200) NOT NULL,
  item_sku VARCHAR(100) NOT NULL,
  item_description TEXT,
  category_id INTEGER REFERENCES categories(id),
  unit_cost DECIMAL(10,2) NOT NULL,
  quantity INTEGER NOT NULL,
  total_cost DECIMAL(12,2) NOT NULL,
  received BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW()
);

Charge Codes and Exclusions

Charge Codes Table

CREATE TABLE chargecodes (
  code VARCHAR PRIMARY KEY,
  title VARCHAR NOT NULL,
  authorised_by VARCHAR REFERENCES users(id),
  valid_from TIMESTAMP,
  valid_until TIMESTAMP,
  pin VARCHAR,
  cost_centre VARCHAR,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Charge Code Exclusions Table

CREATE TABLE charge_code_exclusions (
  id SERIAL PRIMARY KEY,
  charge_code VARCHAR NOT NULL REFERENCES chargecodes(code),
  category_id INTEGER NOT NULL REFERENCES categories(id),
  created_by VARCHAR REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW()
);

Purpose: Defines which categories a charge code cannot be used for, enabling budget control and compliance.

System Configuration Tables

System Settings Table

CREATE TABLE system_settings (
  id SERIAL PRIMARY KEY,
  key VARCHAR(100) UNIQUE NOT NULL,
  value JSONB NOT NULL,
  description TEXT,
  category VARCHAR(50) NOT NULL DEFAULT 'general',
  is_system BOOLEAN NOT NULL DEFAULT false,  -- System settings cannot be deleted
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

User Permissions Table

CREATE TABLE user_permissions (
  id SERIAL PRIMARY KEY,
  user_id VARCHAR NOT NULL REFERENCES users(id),
  permission VARCHAR(100) NOT NULL,
  granted BOOLEAN NOT NULL DEFAULT true,
  granted_by VARCHAR NOT NULL REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(user_id, permission)
);

Permission Definitions Table

CREATE TABLE permission_definitions (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL,
  description TEXT NOT NULL,
  category VARCHAR(50) NOT NULL,
  default_roles JSONB NOT NULL,  -- Array of roles that get this permission by default
  is_system BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

System Monitoring Tables

System Tests Table

CREATE TABLE system_tests (
  id SERIAL PRIMARY KEY,
  test_type VARCHAR(32) UNIQUE NOT NULL,  -- 'unit', 'integration', 'sales'
  status VARCHAR(32) NOT NULL,  -- 'running', 'completed', 'failed'
  output TEXT,
  start_time TIMESTAMP NOT NULL,
  end_time TIMESTAMP,
  duration INTEGER,  -- milliseconds
  updated_at TIMESTAMP DEFAULT NOW()
);

Purpose: Stores the latest test results for system monitoring and health checks.

Session Storage Table

CREATE TABLE sessions (
  sid VARCHAR PRIMARY KEY,
  sess JSONB NOT NULL,
  expire TIMESTAMP NOT NULL
);

CREATE INDEX IDX_session_expire ON sessions(expire);

Purpose: Required for Replit Auth and session management. This table is mandatory and should not be dropped.

Entity Relationships

Primary Relationships

users ||--o{ items : created_by/updated_by
users ||--o{ stock_movements : performed_by
users ||--o{ sales : processed_by
users ||--o{ quotes : created_by/processed_by
users ||--o{ orders : created_by/received_by
users ||--o{ chargecodes : authorised_by
users ||--o{ user_permissions : user_id/granted_by

categories ||--o{ items : category_id
categories ||--o{ charge_code_exclusions : category_id
categories ||--o{ order_items : category_id

items ||--o{ stock_movements : item_id
items ||--o{ sale_items : item_id
items ||--o{ quote_items : item_id
items ||--o{ sources : item_id
items ||--o{ order_items : item_id

suppliers ||--o{ sources : supplier_id
suppliers ||--o{ orders : supplier_id

sales ||--o{ sale_items : sale_id
quotes ||--o{ quote_items : quote_id
orders ||--o{ order_items : order_id

chargecodes ||--o{ charge_code_exclusions : charge_code

Data Types and Constraints

Common Data Types

• VARCHAR: Variable-length strings with optional length limits

• TEXT: Unlimited length text fields

• INTEGER/SERIAL: Integer values, SERIAL for auto-incrementing primary keys

• DECIMAL(p,s): Fixed-precision decimal numbers (precision, scale)

• BOOLEAN: True/false values

• TIMESTAMP: Date and time values

• JSONB: Binary JSON for flexible structured data

Key Constraints

1. Primary Keys: All tables have auto-incrementing or explicit primary keys

2. Unique Constraints: SKUs, email addresses, charge codes must be unique

3. Foreign Key Constraints: Maintain referential integrity between related tables

4. Check Constraints: Validate data ranges and formats (implemented in application layer)

5. Not Null Constraints: Critical fields cannot be empty

Indexes

-- Session management
CREATE INDEX IDX_session_expire ON sessions(expire);

-- Performance indexes for common queries
CREATE INDEX idx_items_category ON items(category_id);
CREATE INDEX idx_items_active ON items(is_active);
CREATE INDEX idx_stock_movements_item ON stock_movements(item_id);
CREATE INDEX idx_sale_items_sale ON sale_items(sale_id);
CREATE INDEX idx_quote_items_quote ON quote_items(quote_id);

Schema Evolution

Migration Strategy

The schema uses Drizzle ORM migrations for version control:

1. Migration Files: Located in migrations/ directory

2. Incremental Changes: Each migration represents a single schema change

3. Rollback Support: Migrations can be rolled back if needed

4. Environment Sync: Ensures development, testing, and production schemas match

Common Migration Operations

// Add new column
await db.schema.alterTable('items')
  .addColumn('new_field', 'varchar(100)')
  .execute();

// Create new table
await db.schema.createTable('new_table')
  .addColumn('id', 'serial', (col) => col.primaryKey())
  .addColumn('name', 'varchar(200)', (col) => col.notNull())
  .execute();

// Add foreign key constraint
await db.schema.alterTable('child_table')
  .addForeignKeyConstraint('fk_parent', ['parent_id'], 'parent_table', ['id'])
  .execute();

Best Practices

Schema Design Guidelines

1. Consistent Naming: Use snake_case for database fields, camelCase in TypeScript

2. Audit Fields: Include created_at, updated_at, created_by where appropriate

3. Soft Deletes: Use is_active flags instead of hard deletes for important data

4. Referential Integrity: Always use foreign key constraints

5. Data Snapshots: Store item details in sale/quote items for historical accuracy

Performance Considerations

1. Indexing: Add indexes for frequently queried fields

2. Query Optimization: Use appropriate joins and limit result sets

3. Connection Pooling: Configure connection pools for high-traffic scenarios

4. Batch Operations: Use transactions for multiple related operations

Security Measures

1. Role-Based Access: Implement proper role checks at application level

2. Input Validation: Validate all inputs using Zod schemas

3. SQL Injection Prevention: Use parameterized queries through Drizzle ORM

4. Audit Logging: Track all critical data changes

5. Data Encryption: Encrypt sensitive fields like password hashes