Phase 5: Database Schema & Entity Relationships

This document visualizes the core entity relationships within the Itinera database. The platform uses a highly normalized MySQL schema, grouping models into bounded contexts: Account, Commerce, Catalog, Trips, and System.

1. Global Entity-Relationship Diagram (ERD)

The following Mermaid diagram maps the primary relationships across the platform's core domains.

2. Key Architectural Decisions in the Schema

Polymorphic Relations

The database heavily leverages Laravel's morph maps to keep the schema DRY.

OrderItem (product_type, product_id): Orders can contain Plan models (subscriptions), or Trip models (agency packages).

ItineraryItem (activity_type, activity_id): A trip's schedule can contain Hotel, Restaurant, or Attraction models uniformly.

Review (reviewable_type, reviewable_id): Users can review both absolute Destination entities and curated Trip packages.

Note: Enforced in AppServiceProvider::boot() via Relation::enforceMorphMap() to prevent tightly coupling the database rows to specific PHP class namespaces.

Data Integrity & Idempotency

orders.idempotency_key: Defined as a VARCHAR(64) with a UNIQUE index. This guarantees at the database level that no two identical intent requests from the client can result in duplicated orders.

payments.paymob_transaction_id: Defined as a UNIQUE index to prevent the same Webhook payload from spawning multiple payment records.

AI Quota Economics

The users table tracks ai_generations_count and ai_reset_at. Instead of building a massive, heavy ledger of every prompt to calculate usage on the fly, quota is calculated directly on the user row. The AI_GENERATION table is strictly for async telemetry/reporting, not synchronous quota blocking.

Phase 5: Database Schema &amp; Entity Relationships

1. Global Entity-Relationship Diagram (ERD)#

2. Key Architectural Decisions in the Schema#

Polymorphic Relations#