Database Schema & Entity Relationship Diagram

Entity Relationship Diagram

The following diagram illustrates the complete database schema and relationships between all entities in the DineFlow Restaurant Management System.

Key Relationships

Restaurant Operations

• TABLES → ORDERS: Each table can have multiple orders
• ORDERS → ORDER_ITEMS: Each order contains multiple menu items
• MENU_ITEMS → ORDER_ITEMS: Menu items are referenced in order items
• ORDERS → BILLS: Each order generates a bill

Customer Management

• CUSTOMERS → ORDERS: Customers can place multiple orders
• CUSTOMERS → INVOICES: Customers receive invoices for purchases

Billing & Invoicing

• INVOICES → INVOICE_ITEMS: Each invoice contains multiple line items
• PRODUCTS → INVOICE_ITEMS: Products are included in invoice items
• INVOICES → PAYMENTS: Invoices can receive multiple payments

User & Activity Tracking

• USERS → INVOICES: Users create invoices
• USERS → PAYMENTS: Users record payments
• USERS → USER_ACTIVITY: User actions are logged
• BUSINESS_SETTINGS → USERS: Business settings are configured by users

Table Descriptions

Restaurant Operations Tables

MENU_ITEMS

Menu items available for customer ordering through the QR code system.

Key Features:

• Categorized for easy browsing
• Image support for visual menu
• Preparation time tracking
• Combo meal support

TABLES

Physical restaurant tables with QR codes for customer ordering.

Attributes:

• Unique table numbers and QR codes
• Tracks capacity and current status
• Status: available, occupied, reserved

ORDERS

Customer orders placed via QR code scanning.

Workflow:

• Links to table and customer information
• Status tracking: pending, preparing, ready, completed, cancelled
• Includes order notes and total amount

ORDER_ITEMS

Individual items within each order.

Details:

• References menu items with quantity
• Stores price at time of order
• Supports special instructions/notes

BILLS

Generated bills for completed orders.

Calculations:

• Calculates subtotal, tax, and total
• Tracks payment status and method
• Links to both order and table

Business Management Tables

CUSTOMERS

Customer database for invoicing and order tracking.

Identification:

• Unique phone numbers for identification
• Stores contact and address information
• Used for both orders and invoices

PRODUCTS

Product catalog for invoicing and inventory management.

Features:

• Supports multiple categories
• Active/inactive status for availability
• Cost and price tracking

INVOICES

Business invoices for product sales.

Capabilities:

• Auto-generated invoice numbers
• Supports discounts and tax calculations
• Tracks payment status and balance due
• Links to customer and creating user

INVOICE_ITEMS

Line items within each invoice.

Calculations:

• References products with quantity and pricing
• Individual discount and tax calculations
• Stores total amount per line item

PAYMENTS

Payment records for invoices.

Methods Supported:

• Cash
• Card
• UPI
• Credit

Tracking:

• Links to invoice and recording user
• Includes reference numbers and notes

User Management Tables

USERS

Internal user accounts for system access.

Access Control:

• Role-based access: admin, manager, staff
• JSONB permissions for granular control
• Tracks login activity and status

USER_ACTIVITY

Audit log for user actions.

Logging:

• Tracks all user operations
• Categorized by module
• Includes detailed descriptions

BUSINESS_SETTINGS

Global business configuration.

Configuration:

• Business information and branding
• Tax rates and currency settings
• Invoice numbering configuration

Data Types

Common Types

• uuid: Universally unique identifier (primary keys)
• string: Variable-length text
• text: Long-form text content
• decimal: Precise decimal numbers for currency
• integer: Whole numbers
• boolean: True/false values
• timestamp: Date and time with timezone
• date: Date only
• jsonb: JSON binary format for structured data
• text[]: Array of text values

Constraints

• PK: Primary Key
• FK: Foreign Key
• UK: Unique Key

Indexes

Indexed Columns:

• Primary keys (automatic)
• Foreign keys for join performance
• Unique constraints (phone, email, invoice_number, etc.)
• Frequently queried columns (status, created_at, etc.)

Security

Row Level Security (RLS)

Policies:

• Authenticated users have full access
• Policies can be refined for role-based access
• Prevents unauthorized data access

Data Integrity

• Foreign key constraints ensure referential integrity
• Check constraints validate data values
• Unique constraints prevent duplicates
• NOT NULL constraints on required fields

Migration Strategy

Database migrations are managed through Supabase:

1. Versioning: Schema changes are versioned
2. Sequential: Migrations are applied sequentially
3. Rollback: Rollback capability for safety
4. Audit: Audit trail of all changes

---

Last Updated: November 9, 2025