Database Architecture

Table of Contents

• Document Purpose
• Database Overview
• Database Objectives
• Primary Data Domains
• Core Database Strategy
• Schema Organization
• Entity Relationship Overview
• Canonical Domain Modules
• Data Ownership Rules
• Transaction Strategy
• Read and Search Strategy
• Caching Strategy
• File and Media Data Strategy
• Audit and History Strategy
• Multi-Environment Data Approach
• Migration Strategy Summary
• Data Quality and Integrity Rules
• Security and Sensitive Data
• Backup and Recovery Direction
• Known Open Decisions
• Assumptions

Document Purpose

This document defines the planned high-level database architecture for Tripinger. It explains how data is expected to be organized, what the system of record is, how major domains relate to one another, and which strategies should govern schema design, migrations, integrity, and operational data management.

This document is intentionally high-level. Detailed table-level definitions are handled in the docs/database/ module files listed later in the documentation plan.

Database Overview

Tripinger is planned as a transaction-heavy travel platform with marketplace, booking, payment, partner, review, and operational workloads. The approved documentation plan treats the database layer as a structured relational system centered on PostgreSQL, with supporting infrastructure for caching, search, and media storage rather than placing all workloads into a single persistence model.

At the architecture level, PostgreSQL is the planned source of truth for core business entities such as users, partners, hotels, vehicles, experiences, bookings, payments, reviews, notifications, and admin-controlled operational data. Redis and Elasticsearch are treated as supporting systems, not replacements for the transactional database.

Database Objectives

The database architecture must support the following goals:

• Preserve transactional integrity for bookings, payments, partner verification, and operational actions.
• Model supply-side inventory for hotels, vehicles, and experiences in a way that supports availability and pricing logic.
• Support user accounts, traveler preferences, saved plans, and review history.
• Allow the platform to scale from an MVP implementation to a larger marketplace without frequent structural rewrites.
• Provide traceability for finance, moderation, and partner operations.
• Keep the relational database as the canonical source of truth while enabling fast read patterns via search and caching.

Primary Data Domains

The current documentation plan defines the following major persistent data areas:

Core Database Strategy

System of record

PostgreSQL is the planned primary system of record for Tripinger. The Phase 2 and Phase 3 documentation structure is explicitly organized around PostgreSQL-backed domain modules, which indicates that structured relational modeling is the primary database strategy.

Supporting stores

The broader platform architecture also anticipates the following supporting infrastructure:

• Redis for caching, short-lived coordination data, and optional rate-limiting or pub/sub support.
• Elasticsearch for full-text search, filtering, autocomplete, and ranked discovery experiences.
• Object/media storage for images, partner documents, review attachments, and other binary assets.

Polyglot persistence note

An earlier planning artifact referenced MongoDB for flexible content such as reviews or social data. However, the approved 79-file documentation plan does not currently define MongoDB-specific schema ownership, so this database architecture document treats PostgreSQL as the canonical planned persistent store unless a later architecture decision reintroduces a second primary data store.

Schema Organization

Assumption: Tripinger will begin with a single PostgreSQL database cluster containing logically separated application domains, rather than many physically separate databases.

Two organization patterns are possible:

1. A single default schema with strongly prefixed and convention-driven tables.
2. Multiple PostgreSQL schemas grouped by business area.

For current planning purposes, the preferred direction is:

• One primary application database.
• Convention-based grouping by domain.
• Migration ownership defined per domain module.
• Physical separation introduced later only when operational complexity justifies it.

Recommended logical grouping

Entity Relationship Overview

At a high level, the database is centered around a marketplace relationship model:

• A user can be a traveler, a partner user, an admin user, or multiple roles depending on the access model.
• A partner can manage one or more supply entities such as hotels, vehicles, experiences, or food-related offerings.
• Supply entities create bookable inventory.
• A booking belongs to a traveler and references one or more bookable items.
• A payment is attached to a booking lifecycle and may later produce refunds, payouts, and reconciliation records.
• A review is tied to a verified or otherwise eligible booking outcome.
• An itinerary may reference saved or bookable products but should not own the authoritative commercial records.

Simplified logical relationship map

See docs/database/ERD.md for the more formal ERD narrative and Mermaid definitions.

Canonical Domain Modules

The approved file plan already defines the future detailed database modules. This high-level document treats them as canonical domain breakdowns.

Data Ownership Rules

To avoid ambiguity and duplication, each major entity should have one authoritative owning domain.

Ownership principles

• User identity data is owned by the users/authentication domains.
• Partner compliance data is owned by the partners domain.
• Inventory definitions are owned by their supply domains, not by bookings.
• Bookings store snapshots necessary for commercial integrity, but do not replace canonical inventory records.
• Payments own transaction state, while bookings own booking lifecycle state.
• Reviews own trust content but must reference validated participation rules.
• AI planner entities may reference supply or booking data, but do not replace authoritative booking records.

Snapshot rule

Because prices, names, and policies can change over time, booking-related tables should preserve commercial snapshots required for historical integrity. This avoids future disputes where the source listing changed after purchase.

Transaction Strategy

Tripinger includes several flows where strong transactional behavior matters.

Strongly transactional areas

• User registration and verification state changes
• Partner verification updates
• Inventory reservation and booking creation
• Booking state transitions
• Payment creation and confirmation handling
• Refund issuance and payout accounting
• Review eligibility enforcement

Transaction design rules

• Multi-step commercial operations should use explicit transactional boundaries.
• Side effects such as emails or search reindexing should not be required to complete the core transaction.
• Idempotency must be considered for payment webhooks and retryable external callbacks.
• History tables or status transition tables should be used where lifecycle traceability matters.

Read and Search Strategy

The relational database is not expected to serve every discovery use case directly.

PostgreSQL should handle

• Transactional reads and writes
• Admin and partner operational queries
• Consistency-sensitive booking and payment reads
• Structured relationship traversal

Search infrastructure should handle

• Full-text search across hotels, vehicles, and experiences
• Filter-heavy traveler discovery flows
• Autocomplete and relevance ranking
• Optional destination and content discovery at scale

Read model principle

Search indexes are derived read models. They should be rebuildable from canonical source data stored in PostgreSQL and supporting media metadata.

Caching Strategy

Redis is a supporting performance layer, not a source of truth.

Planned cache use cases

• Hot listing and search result caching
• Rate-limiting support
• Session-adjacent ephemeral data if needed
• Availability query acceleration where safe
• Temporary workflow coordination for async jobs

Cache rules

• Cached data must be invalidatable.
• Booking and payment correctness must never depend solely on cache state.
• Cache misses must be recoverable from canonical data stores.

File and Media Data Strategy

Tripinger will manage significant non-database assets such as listing photos, partner documents, and review media.

Storage model

• Binary files should be stored in object storage or a media platform.
• PostgreSQL should store metadata, references, ownership, type, moderation state, and audit-relevant attributes.
• Sensitive partner documents must be separated logically from public listing media.

Common media categories

• Hotel images
• Vehicle images
• Experience images
• Review images/videos
• Partner onboarding documents
• Optional admin-managed content media

Audit and History Strategy

Travel marketplaces require strong historical traceability for disputes, moderation, payouts, and internal accountability.

History requirements

The database design should preserve historical records for:

• Booking status transitions
• Payment status transitions
• Refund events
• Payout events
• Partner verification changes
• Admin moderation actions
• Sensitive permission or configuration changes

Implementation direction

• Use status history tables where lifecycle reconstruction matters.
• Use audit tables for privileged or internal actions.
• Avoid destructive updates where regulated, financial, or dispute-sensitive history is important.

Multi-Environment Data Approach

Database handling differs by environment.

Environment rules

• Production data should not be copied casually into lower environments.
• Sensitive partner and financial records should be masked or synthetically generated for testing.
• Migration validation should be performed before production rollout.

Migration Strategy Summary

The approved documentation plan references Prisma migration workflows in docs/database/MIGRATIONS.md, which suggests Prisma-managed schema evolution as the current intended direction.

Migration principles

• Every schema change must be versioned.
• Migrations must be reviewed with application changes.
• Backward compatibility should be considered for staged deployments.
• Destructive changes should be avoided or phased.
• Large backfills or data rewrites should be isolated from fragile release paths.

Naming direction

Assumption: Migration naming should reflect business intent rather than only timestamps, for example:

• add_partner_bank_accounts
• split_booking_status_history
• create_vehicle_pricing_tables

Detailed workflow rules belong in docs/database/MIGRATIONS.md.

Data Quality and Integrity Rules

The database model should enforce quality as close to the data layer as practical.

Required integrity patterns

• Foreign keys for authoritative relationships
• Unique constraints for identity-like fields where appropriate
• Check constraints for bounded enum-like or numeric conditions where useful
• Explicit enum usage or equivalent controlled-domain modeling for finite state fields
• Soft delete or archive patterns only when business rules require record retention

Business integrity examples

• A review should not be accepted for an ineligible booking state.
• A payout should not exist without traceable payment and partner references.
• A booking item should reference a valid commercial source entity or preserved snapshot.
• Partner-managed inventory must remain attributable to a verified partner account.

Security and Sensitive Data

Tripinger stores identity, partner compliance, and financial-adjacent data, so the database strategy must minimize unnecessary exposure.

Sensitive data categories

• User identity and contact data
• Authentication-related records
• Partner KYC-like verification artifacts
• Banking and payout details
• Payment references and reconciliation data
• Admin activity logs tied to internal actions

Security rules

• Never store provider secrets in the database unless explicitly required and encrypted.
• Limit raw payment data storage to provider-safe references and necessary metadata.
• Restrict document access metadata carefully.
• Apply least-privilege access patterns across applications and operational tooling.

See also SECURITY.md for reporting and disclosure policy.

Backup and Recovery Direction

Backup and disaster recovery procedures are documented later in Phase 3, but the database architecture should be designed with recovery in mind from the start.

Directional requirements

• Automated backups for the primary relational database
• Defined retention windows by environment
• Tested restoration procedures
• Awareness of point-in-time recovery requirements for production workloads
• Recovery planning for search and cache layers as rebuildable/supporting systems

Recovery principle

PostgreSQL is the primary recovery-critical datastore. Search indexes and caches should be reproducible from canonical data and therefore should not be treated as the sole copy of important business state.

Known Open Decisions

The following decisions remain open and should be finalized in later detailed documents:

1. Whether PostgreSQL schemas are physically separated by business domain.
2. Whether MongoDB or another second persistent datastore is needed for community/content workloads.
3. Final enum implementation strategy across Prisma and PostgreSQL.
4. Exact soft-delete versus hard-delete policy per domain.
5. Exact retention policy for audit, notification, and operational history data.
6. Whether itinerary and planner data should remain fully relational or partially denormalized.
7. Final indexing priorities once real query patterns are measured.

Assumptions

• Assumption: PostgreSQL is the authoritative transactional database for the approved documentation scope.
• Assumption: Prisma is the intended schema and migration management layer because a dedicated Prisma migration document is already in scope.
• Assumption: Redis and Elasticsearch are supporting infrastructure, not canonical persistence layers.
• Assumption: Booking, payment, and partner records require durable historical traceability.
• Assumption: Media binaries are stored outside PostgreSQL, while metadata and ownership references remain inside the relational model.