Database Schema - Entity Relationship Diagram

Mermaid ERD Diagram

erDiagram
    ACCESS_TOKENS ||--o{ PRICING_RULE_HISTORIES : "created_by"
    
    PRICING_RULES ||--o{ PRICING_RULE_HISTORIES : "has"
    PRICING_RULES ||--o{ DATE_TIME_CONDITIONS : "has"
    PRICING_RULES ||--o{ BOOKING_TIMING_CONDITIONS : "has"
    PRICING_RULES ||--o{ PRICING_RULES_PACKAGES : "scopes_to"
    
    DATE_TIME_CONDITIONS ||--o{ CONDITION_ITEMS : "contains"
    
    GLOBAL_PRICING_SETTINGS ||--o{ PRICING_RULES : "constraints"

    ACCESS_TOKENS {
        bigint id PK
        string token_digest UK
        string user_id
        string user_name
        string user_email
        string user_role
        jsonb restaurants
        string current_restaurant_id
        datetime expires_at
        datetime last_validated_at
        datetime created_at
        datetime updated_at
    }

    PRICING_RULES {
        bigint id PK
        string restaurant_id FK
        string name
        string rule_type
        integer priority
        boolean active
        boolean apply_to_all_packages
        boolean requires_approval
        string date_type
        date start_date
        date end_date
        time start_time
        time end_time
        boolean is_no_end_date
        boolean is_no_end_time
        boolean is_anytime
        string period_mode
        datetime created_at
        datetime updated_at
    }

    DATE_TIME_CONDITIONS {
        bigint id PK
        bigint pricing_rule_id FK
        string date_type
        string period_mode
        integer[] days_of_week
        string action_type
        string value_type
        integer value
        datetime created_at
        datetime updated_at
    }

    CONDITION_ITEMS {
        bigint id PK
        bigint date_time_condition_id FK
        date start_date
        date end_date
        time start_time
        time end_time
        boolean is_no_end_date
        boolean is_no_end_time
        boolean is_anytime
        datetime created_at
        datetime updated_at
    }

    BOOKING_TIMING_CONDITIONS {
        bigint id PK
        bigint pricing_rule_id FK
        string timing_type
        integer period_value
        string period_unit
        string action_type
        string value_type
        integer value
        datetime created_at
        datetime updated_at
    }

    PRICING_RULES_PACKAGES {
        bigint id PK
        bigint pricing_rule_id FK
        string package_id
        datetime created_at
        datetime updated_at
    }

    PRICING_RULE_HISTORIES {
        bigint id PK
        bigint pricing_rule_id FK
        string restaurant_id
        string user_id
        string user_name
        string action
        string status
        string rule_type
        string rule_name
        jsonb date_range
        jsonb price_change
        jsonb package_info
        jsonb rule_snapshot
        jsonb changes_diff
        boolean is_undone
        datetime undone_at
        string undone_by_user_id
        string undone_by_user_name
        datetime created_at
    }

    GLOBAL_PRICING_SETTINGS {
        bigint id PK
        string restaurant_id UK
        string price_increase_limit_type
        decimal price_increase_limit_value
        string price_decrease_limit_type
        decimal price_decrease_limit_value
        datetime created_at
        datetime updated_at
    }

Table Descriptions

Core Tables

`pricing_rules`

Purpose: Main entity for pricing rules
Key Fields:
- restaurant_id: External reference to restaurant
- rule_type: Type of rule (specific_date_time, demand_capacity, etc.)
- priority: Execution order when multiple rules apply
- date_type: Specific type of date handling (specific_date, specific_date_time, etc.)
- period_mode: Range or single occurrence
- Date/Time fields: start_date, end_date, start_time, end_time
Relationships:
- 1 → Many: date_time_conditions (conditions for date-based rules)
- 1 → Many: booking_timing_conditions (conditions for timing-based rules)
- 1 → Many: pricing_rules_packages (package scoping)
- 1 → Many: pricing_rule_histories (audit trail)

`date_time_conditions`

Purpose: Defines date/time based conditions for pricing rules
Key Fields:
- date_type: Type of date condition
- days_of_week: Array of day numbers (0-6)
- action_type: Type of price action (increase, decrease)
- value_type: Unit type (percentage, fixed_amount)
- value: Numeric value (basis points or cents)
Relationships:
- 1 → Many: condition_items (detailed date/time ranges)

`condition_items`

Purpose: Detailed date/time range specifications within conditions
Key Fields:
- start_date, end_date: Date range
- start_time, end_time: Time range
- is_no_end_date: Flag for ongoing rules
- is_no_end_time: Flag for all-day rules
- is_anytime: Flag for anytime applicability

`booking_timing_conditions`

Purpose: Defines booking window based conditions
Key Fields:
- timing_type: Type of booking timing rule
- period_value: Numeric period
- period_unit: Unit (days, hours, minutes, etc.)
- action_type, value_type, value: Same as DateTimeCondition

`pricing_rules_packages`

Purpose: Join table to scope rules to specific packages
Key Fields:
- pricing_rule_id: Reference to pricing rule
- package_id: External reference to restaurant package
Note: Denormalized - apply_to_all_packages boolean avoids this table when true

Support Tables

`global_pricing_settings`

Purpose: Global constraints per restaurant
Key Fields:
- price_increase_limit_type: “percentage” or “fixed_amount”
- price_increase_limit_value: Maximum allowed increase
- price_decrease_limit_type: “percentage” or “fixed_amount”
- price_decrease_limit_value: Maximum allowed decrease

`pricing_rule_histories`

Purpose: Audit trail for all rule modifications
Key Fields:
- action: created, updated, deleted, activated, deactivated
- status: pending, approved, rejected
- rule_snapshot: JSONB copy of rule state
- changes_diff: JSONB of what changed
- is_undone: Flag if action was reversed
- Undo tracking: undone_at, undone_by_user_id, undone_by_user_name

`access_tokens`

Purpose: API authentication and authorization
Key Fields:
- token_digest: Hashed token (indexed, unique)
- user_id, user_name, user_email: User info
- restaurants: JSONB array of accessible restaurants
- current_restaurant_id: Active restaurant context
- expires_at: Token expiration
- last_validated_at: Last validation timestamp

Key Relationships

Restaurant (external)
    ↓
    ├── PRICING_RULES (has many)
    │   ├── DATE_TIME_CONDITIONS (specific_date_time rules)
    │   │   └── CONDITION_ITEMS (detailed ranges)
    │   ├── BOOKING_TIMING_CONDITIONS (booking window rules)
    │   ├── PRICING_RULES_PACKAGES (package scoping)
    │   └── PRICING_RULE_HISTORIES (audit trail)
    │
    ├── GLOBAL_PRICING_SETTINGS (constraints)
    │
    └── ACCESS_TOKENS (user auth)

Index Strategy

Primary Indexes

pricing_rules(restaurant_id, active, rule_type)
date_time_conditions(pricing_rule_id)
condition_items(date_time_condition_id, start_date, end_date)
pricing_rule_histories(restaurant_id, created_at)

Performance Indexes

pricing_rules(start_date, end_date) - Date range queries
date_time_conditions(days_of_week) - GIN index for array search
access_tokens(token_digest) - Token lookups
access_tokens(expires_at) - Token cleanup queries

Constraints

All foreign keys have ON DELETE CASCADE (except pricing_rule_histories → pricing_rules: ON DELETE SET NULL)
restaurant_id in GLOBAL_PRICING_SETTINGS is UNIQUE
token_digest in ACCESS_TOKENS is UNIQUE
pricing_rule_id + package_id combination is UNIQUE in PRICING_RULES_PACKAGES

Keyboard shortcuts

HungryHub Knowledge Base