database-patterns

Database Development Guidelines (PostgreSQL)

Schema Design

Primary Schema

The main database schema is defined in database/init/01_schema.sql:

Core Tables Structure:

1. users - Staff and user management with role-based access
2. categories - Product categorization with sorting and colors
3. products - Menu items with pricing and availability
4. dining_tables - Table and seating management
5. orders - Order lifecycle with status tracking
6. order_items - Individual items within orders
7. payments - Payment processing and history
8. inventory - Stock management and tracking
9. order_status_history - Audit trail for order changes

Naming Conventions

Table Names

• Use plural snake_case: order_items, dining_tables
• Descriptive names that clearly indicate content
• Avoid abbreviations unless universally understood

Column Names

• Use snake_case: created_at, order_number
• Boolean fields start with is_: is_active, is_occupied
• Timestamps end with _at: created_at, processed_at
• Foreign keys follow pattern: {table_name}_id

Constraint Names

• Primary keys: {table}_pkey
• Foreign keys: fk_{table}_{referenced_table}
• Unique constraints: unique_{table}_{column}
• Check constraints: check_{table}_{column}_{description}

Data Types & Standards

UUID Primary Keys

Use UUID v4 for all primary keys:

CREATE TABLE example_table (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    -- other columns
);

Timestamps

Always use TIMESTAMP WITH TIME ZONE for time tracking:

created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP

Money Values

Use DECIMAL(10,2) for all monetary values:

price DECIMAL(10,2) NOT NULL,
total_amount DECIMAL(10,2) NOT NULL DEFAULT 0

String Fields

• Use appropriate VARCHAR lengths based on expected content
• Use TEXT for long content without length restrictions
• Apply NOT NULL constraints where appropriate