Entity Relationship Diagrams
This document describes the core entity–relationship model for the Tripinger platform.
Document Purpose
This document describes the core entity–relationship model for the Tripinger platform. It explains the main entities, how they relate to each other, and how those relationships support marketplace, booking, payment, review, and planning workflows. It is the authoritative ERD narrative that complements docs/DATABASE.md and the per-domain database module files.
Tripinger is a travel marketplace and planning product centered on users, partners, supply (hotels, vehicles, experiences), bookings, payments, reviews, and itineraries. ERDs help developers, data engineers, and stakeholders understand how these entities connect and which domain owns which data, while MIGRATIONS.md explains how this model changes safely over time.
ERD Overview
At a high level, the relational model is organized into logical groups:
- Identity and access: users, sessions, verification records, admin roles, permissions.
- Supply: partners, hotels, rooms, vehicles, experiences, amenities, policies.
- Commerce: bookings, booking items, payments, refunds, payouts, commissions.
- Trust and community: reviews, moderation signals, flags, responses.
- Planning and engagement: AI planner sessions, itineraries, notifications.
- Operations (future): audit logs, support workflows, configuration, finance artifacts.
The ERDs below focus on the Phase 1–2 core: users, partners, hotels, vehicles, experiences, bookings, payments, reviews, AI planner, and notifications. Future domains (community, content, support, audit) will add more entities later.
Core Marketplace ERD (Simplified)
Tripinger’s database architecture already includes a simplified logical relationship map in Mermaid form. Below is an expanded version, still at the “canonical” level but with more explanation.
Key points:
- A user can be a traveler, a partner user, an admin user, or multiple roles depending on access.
- A partner manages one or more supply entities (hotels, vehicles, experiences).
- Supply entities create bookable inventory (rooms, vehicles, slots), exposed via booking items.
- A booking belongs to a traveler and references one or more booking items.
- A payment is attached to the booking lifecycle and can produce refunds and payouts.
- A review is tied to an eligible booking outcome.
- An itinerary references trip structure and may reference supply or bookings but does not own commercial records.
Identity and Roles ERD
Identity is the base of almost every flow. This ERD covers core user and admin-role entities.
Notes:
- Users are canonical identities for travelers, partners, and admins. Separate flags or roles indicate which surfaces they access.
- Profiles and preferences can evolve independently (for example, traveler preferences, notification settings).
- Admin roles are modeled separately from basic user flags to support internal RBAC.
MIGRATIONS.md governs how new roles, flags, or profile fields are added over time without breaking existing flows.
Partner and Supply ERD
Supply-side entities (partners, hotels, vehicles, experiences) are the backbone of the marketplace.
Notes:
- A partner is the supply-side identity; each partner maps back to a user for login/permissions.
- Hotels, vehicles, and experiences are distinct but share similar patterns: partner link, publish/verify flags, pricing and availability configs.
- Hotel rooms are separate entities to support multiple room types per hotel.
When the schema for partners or supply changes (for example, new verification fields or pricing configs), those changes are governed by migration rules rather than ad hoc DB edits.
Commerce ERD (Bookings, Items, Payments, Refunds, Payouts)
Commerce entities capture transactional flows. This ERD focuses on bookings and financial records.
Notes:
- Bookings belong to users and reference one or more booking items, which link to supply entities or preserved snapshots.
- Payments are tied to bookings; refunds and payouts are tied to payments, aligning with finance traceability.
- Booking and payment lifecycle history (for example, status history tables) are part of the model but may be documented in per-domain files; ERD focuses on core relationships.
MIGRATIONS.md defines how to introduce status-history tables, new payment fields, or changes to booking-item relationships without breaking existing transactions.
Trust and Reviews ERD
Reviews are tied to bookings and contribute to trust and community.
Notes:
- Reviews reference bookings to enforce eligibility (only users who booked can review).
- TargetType/targetId allow the same review structure to be used for hotels, vehicles, or experiences.
- Moderation statuses and flags support trust and safety workflows; more moderation entities may be added later.
Migration rules govern how new moderation states, flag types, or relationships are introduced (for example, splitting moderation into a separate table or adding audit logs).
Planning and Itineraries ERD
Tripinger includes AI-assisted trip planning and itineraries. This ERD summarizes planner entities.
Notes:
- Planner sessions capture AI input/output; itineraries are structured trip plans.
- Itinerary items may reference supply or bookings but do not own commercial records.
- Future changes may denormalize portions of this model for performance;
MIGRATIONS.mdcovers how to evolve these tables safely.
Notifications ERD
Notifications connect operational events to user-facing messages.
Notes:
- Templates define reusable messaging; notifications are actual deliveries.
- DeliveryStatus and RetryCount support robust delivery and monitoring.
- As new channels or states are introduced, migrations must update enums and constraints carefully.
Relationship to Migrations
This ERD document focuses on what entities exist and how they are related. docs/database/MIGRATIONS.md focuses on how those entities evolve over time:
- ERD:
- Defines tables, relationships, ownership (users ↔ partners ↔ supply ↔ bookings ↔ payments ↔ reviews ↔ plans ↔ notifications).
- Explains why certain relationships exist (for example, bookings reference users and items, payments reference bookings).
- MIGRATIONS:
- Defines versioning, naming, and workflows for adding/changing tables and relationships.
- Provides patterns for introducing history tables (booking status history), backfills, constraints, enums, and DevOps integration.
When the ERD changes (for example, new entities, new relationships, or restructuring existing ones), those changes must be implemented via migrations that follow the rules in MIGRATIONS.md to protect data integrity and production safety.
Current Status
This ERD is part of Tripinger Phase 1–2 database documentation. It captures the canonical entities and relationships expected for the initial marketplace, booking, payment, review, planner, and notification workflows. Detailed table-level definitions, indexes, and per-domain constraints will be documented in the individual docs/database/0X-*.md files and updated as implementation decisions are finalized.