Database Overview

Document Purpose

This document is the entry point for Tripinger's database documentation. It explains the role of the database layer, the main data domains it supports, the intended documentation structure inside docs/database/, and the working rules that should guide schema design, migrations, integrity, and future expansion.

Tripinger is being designed as a multi-surface travel platform with a public web application, partner portal, admin dashboard, and shared backend API. Because those surfaces all depend on the same business records and operational workflows, the database layer is a platform foundation rather than a backend implementation detail.

This document does not attempt to replace the high-level architecture or platform-wide database strategy already described elsewhere. Instead, it serves as the local index and operating guide for the database documentation set that will support schema evolution during implementation.

Why the Database Layer Matters

Tripinger combines hotel booking, vehicle rentals, food arrangements, activities, itinerary planning, budget tools, reviews, community features, partner operations, and admin workflows into one connected product. That means the database must support both traveler-facing experiences and internal operational controls without fragmenting core business data.

The architecture currently treats PostgreSQL as the documented primary source of truth for transactional and structured business entities. The approved documentation set also defines database documentation specifically around PostgreSQL-focused artifacts rather than a polyglot-persistence model, so this database module should treat PostgreSQL as canonical unless a later approved decision changes that direction.

Database Role in the System

At the platform level, the database stores the durable records that define users, supply-side entities, inventory, bookings, payments, reviews, and operational events. The architecture positions Redis as a cache and coordination layer, Elasticsearch as a derived search system, and object storage as the home for files and media, which means the relational database remains the authoritative system of record for core business workflows.

In practice, this means:

• PostgreSQL stores business truth.
• Search indexes are derived from database-backed entities.
• Caches may accelerate reads but must not replace durable truth.
• Media files live outside the relational store, while their metadata and ownership links belong in the database.
• External provider outcomes, such as payment confirmations, should be reconciled into persistent relational records rather than trusted only in transit.

Scope of the Database Domain

The platform scope and architecture documents imply a broad but coherent relational model. The database should be expected to support at least the following major domains over time.

Core identity and access

• users
• authentication-related records
• profiles
• roles and permissions where applicable
• partner access and approval state

Supply-side marketplace data

• partners
• hotel properties and room structures
• vehicle owners and vehicles
• restaurants, guides, and activities where enabled
• availability, pricing, and listing publication state

Transactional commerce data

• bookings
• booking items or booking-linked entities
• payment records
• refund and reconciliation records
• payout-related records for partners

Trust, moderation, and operations

• reviews and ratings
• verification records
• compliance and document tracking
• support notes
• admin actions and operational audit trails

Planning and engagement

• itineraries
• budgets
• saved trips
• social or journal content if retained in PostgreSQL
• notifications and activity-linked metadata where relational storage is useful

The exact table list will evolve as implementation becomes more concrete, but the database design should remain aligned with these domain boundaries rather than being shaped only around individual screens or temporary endpoint needs.

Database Documentation Structure

The root documentation map already defines the database documentation set as a small module with four files. This README is the entry point, while the other files each serve a distinct purpose.

Files in this folder

• docs/database/README.md Entry point for the database documentation set, scope, conventions, and navigation.

• docs/database/ENUMS.md Shared enum definitions and status vocabularies used across the schema and application layers.

• docs/database/MIGRATIONS.md Rules for schema changes, migration authoring, rollout discipline, and environment safety.

• docs/database/ERD.md Entity relationship documentation and Mermaid diagrams describing the relational model.

Design Principles

The database layer should follow a small set of stable principles from the beginning.

1. PostgreSQL is the source of truth

The current documented architecture explicitly treats PostgreSQL as the primary transactional store for users, partners, listings, bookings, payments, reviews, and operational records. Even if secondary systems are introduced for caching, search, or analytics, the relational schema remains the authoritative state model for core product workflows.

2. Model business domains, not UI pages

Tables should reflect durable business concepts such as users, partners, hotels, vehicles, bookings, payments, reviews, and itineraries. They should not be shaped around temporary frontend layouts or narrowly scoped view-specific payloads.

3. Favor explicit workflow state

Tripinger contains many stateful processes, including partner onboarding, listing verification, booking lifecycles, payment confirmation, refunds, moderation, and review eligibility. These flows should use explicit relational fields, enums, timestamps, and audit-friendly transitions rather than hidden or inferred status logic.

4. Preserve auditability

The architecture and API direction both emphasize operational traceability for payments, admin actions, moderation, and partner verification. Database design should support that expectation through timestamps, status history where appropriate, and careful handling of destructive changes.

5. Support progressive scale without premature complexity

The broader architecture is intentionally designed to scale progressively. The database should therefore begin with a clean and maintainable relational core, while leaving room for indexing, partitioning, read replicas, search pipelines, and async projections later if usage requires them.

Relationship to Other Documents

This file should be read together with the platform-level documents that define the broader context.

• docs/ARCHITECTURE.md explains where the database sits within the full platform and how it relates to caches, search, files, integrations, and async processing.
• docs/DATABASE.md provides the higher-level database strategy for the repository as a whole.
• apps/api/README.md explains how the API treats PostgreSQL as the source of truth for business records and how backend modules map onto domain entities.
• apps/api/docs/API_CONVENTIONS.md and apps/api/docs/AUTH.md should stay aligned with database naming, status fields, identifiers, and authorization-related data structures once those implementation docs are finalized.

What This Module Should Eventually Describe

As the schema becomes real, this database documentation set should grow to answer practical questions such as:

• Which entities exist in the relational model?
• What are the primary ownership boundaries?
• Which statuses and enums are shared across domains?
• How are bookings linked to listings, users, partners, and payments?
• How are partner verification and document workflows represented?
• Which records are mutable, append-only, soft-deletable, or operationally sensitive?
• Which tables are source-of-truth records versus derived support tables?

The goal is not just to document table names. The goal is to make the database understandable enough that backend, admin, partner, and data-related work can evolve consistently across the monorepo.

Expected High-Level Data Domains

The architecture and platform scope suggest the following high-level clusters for the relational model. These clusters are conceptual and may map to schemas, modules, or grouped table families depending on implementation choices.

Ownership and Multi-Surface Use

A single business entity may be used by multiple product surfaces at once. For example, a hotel listing may be created or edited in the partner portal, reviewed in the admin dashboard, searched in the public web app, and managed through API business rules in the backend. That shared usage is one of the main reasons the relational model needs stable ownership rules and clear boundaries.

A practical database rule for Tripinger is:

• traveler-facing data should remain clearly separated from internal control metadata,
• partner-managed records should remain linked to partner ownership,
• admin actions should be attributable and auditable,
• and external-system events should be stored in ways that preserve reconciliation and traceability.

IDs, Timestamps, and Statuses

The API direction already expects stable identifiers, explicit timestamps, and documented status vocabularies across bookings, payments, partner states, and listing workflows. The database layer should therefore be designed to support opaque identifiers, standard timestamp columns, and enum-backed or constrained status fields that remain consistent across schema, API, and frontend usage.

At minimum, most durable entities should be expected to support:

• a primary identifier,
• createdAt,
• updatedAt,
• optional workflow timestamps such as approvedAt, paidAt, cancelledAt, or verifiedAt,
• and documented status values where lifecycle state matters.

Integrity and Constraints

Because Tripinger supports commerce, verification, and operational workflows, data integrity must be treated as a first-class design concern. The schema should use relational constraints, foreign keys, uniqueness rules, and controlled nullability to prevent invalid states that would otherwise leak into API and admin behavior.

Examples of integrity expectations include:

• bookings must belong to valid users and inventory records,
• payments must be linked to valid booking or transaction context,
• partner-owned listings must trace back to a valid partner account,
• review eligibility should not be detached from booking or completion rules,
• and verification or compliance records should remain attributable to the correct entity and reviewer context.

Operational Expectations

The architecture expects the system to run across local, test, staging, and production environments, and it emphasizes observability, auditability, and operational safety for booking, payment, and notification workflows. That means database documentation should not stop at entity definitions; it should also support safe change management, migration discipline, and environment-aware rollout practices.

This is why the database module includes a separate migration document. Schema change process is part of platform reliability, not just developer convenience.

Current Boundaries and Assumptions

Several database-adjacent decisions are still intentionally open at the platform level. The architecture notes that some implementation details remain unconfirmed, including whether MongoDB remains part of the eventual production shape, how async infrastructure is finalized, and how services may be split later. For the current documentation set, however, PostgreSQL should be treated as the documented primary database boundary.

The following assumptions apply unless a later approved document changes them:

• PostgreSQL is the canonical source of truth for core structured entities.
• Redis is not a substitute for relational durability.
• Elasticsearch is a derived search system, not the source of booking or listing truth.
• Files and media are stored outside the relational database, but their references and ownership metadata belong in the schema.
• Database documentation should remain aligned with the API domain structure rather than drifting into disconnected table catalogs.

Contribution Expectations for Database Docs

When database-related work is added to the repository, updates should usually touch more than one file in this folder.

Typical examples:

• A new status model should update ENUMS.md.
• A schema change process update should modify MIGRATIONS.md.
• A new or materially changed relationship should update ERD.md.
• A structural documentation change that affects database scope or conventions should update this README.

This keeps the database docs internally consistent instead of letting one file become more current than the others.

Current Status

This database README defines the intended role and structure of the database documentation module for Tripinger based on the approved documentation map and architecture direction. It should evolve as concrete schemas, migrations, and entity relationship diagrams are added, but it should continue to preserve one central idea: the relational database is the durable business backbone of the Tripinger platform.