Database Schema Structure for Trips and Reservations in TREK: A Complete Guide

The TREK travel planning application uses a SQLite database with Zod-defined schemas where trips store travel metadata with computed statistics, while reservations track flight legs, accommodations, and external sync data via normalized tables defined in shared/src/trip/trip.schema.ts and shared/src/reservation/reservation.schema.ts.

Understanding the database schema structure for trips and reservations in TREK is essential for developers extending the open-source travel app maintained by mauriceboe. The codebase uses Zod schemas as the single source of truth for both runtime validation and API contracts, with computed fields populated via SQL views and joins. This guide breaks down the exact column definitions, relationships, and query patterns used across the trips, reservations, and accommodations tables.

Trip Schema Structure

The trip schema in shared/src/trip/trip.schema.ts defines the core travel plan entity and its associated request payloads.

Core Columns

The trips table stores the following direct columns as defined at lines 22-34:

• id – Primary key integer
• user_id – Foreign key to the owner
• title – Human-readable trip name (required)
• description – Optional free-form text
• start_date / end_date – ISO date strings (nullable)
• currency – ISO-4217 code (e.g., USD)
• cover_image – URL string (nullable)
• is_archived – Stored as 0/1 integer (SQLite boolean)
• reminder_days – Integer for notification timing
• created_at / updated_at – Timestamp strings managed by SQLite triggers

Computed and Joined Fields

The TRIP_SELECT view enriches raw trip data with aggregated statistics. These fields are populated via SQL joins and appear in list/get endpoints at lines 35-41:

• day_count – Count of distinct days in the trip
• place_count – Count of distinct places visited
• is_owner – 1 if requester is owner, else 0
• owner_username – Joined from users table
• shared_count – Count of collaborators from trip_members table

API Request Schemas

The schema defines strict validation for mutable operations:

• TripCreateRequest – Requires non-empty title with optional description, dates, and currency (lines 61-69)
• TripUpdateRequest – Partial update schema accepting is_archived as boolean or numeric for flexibility (lines 72-82)
• TripCopyRequest – Minimal schema allowing only an optional new title (lines 86-88)
• TripAddMemberRequest – Accepts email, username, or user-ID identifier (lines 91-93)

Reservation and Accommodation Schema

The reservation system in shared/src/reservation/reservation.schema.ts handles transportation bookings and lodging with complex endpoint tracking.

Reservation Endpoints

The reservationEndpointSchema defines individual legs of a journey (e.g., flight segments) at lines 22-34:

• role – Enum: 'from' | 'to' | 'stop'
• sequence – Ordering number
• name / code – Location identifiers (airport codes, station names)
• lat / lng – Geographic coordinates
• timezone – IANA timezone string
• local_time / local_date – Localized arrival/departure times

Reservation Entity

The reservationSchema spans lines 44-78 and maps to the reservations table with these key columns:

Core Fields:

• id / trip_id – Primary and foreign keys
• day_id / end_day_id – Span of trip days
• place_id – Linked location reference
• title – Required display name
• reservation_time / reservation_end_time – ISO timestamps

Status and Metadata:

• status / type – Booking state (e.g., "booked", "canceled")
• metadata – JSON string for provider-specific data
• needs_review – Integer flag for manual verification
• day_plan_position – Ordering within a day's itinerary

External Sync Fields (lines 64-70):

• external_source, external_id, external_owner_user_id
• external_synced_at, sync_enabled

Computed Joins (lines 71-78):

• day_number, place_name, accommodation_*
• day_positions, endpoints – Array of endpoint objects

Accommodation Entity

The accommodationSchema (lines 87-106) represents lodging attached to trip days:

• id, trip_id – Primary keys
• place_id – Optional link to places table
• start_day_id, end_day_id – Duration span
• check_in, check_in_end, check_out – ISO timestamps
• confirmation, notes – Booking details
• Joined fields: place_name, place_address, place_image, place_lat, place_lng, reservation_title

API Request Payloads

Reservation mutations use flexible schemas:

• ReservationCreateRequest – Open record (z.record(z.string(), z.unknown())) merged with required title (lines 9-12)
• ReservationUpdateRequest – Fully open schema allowing arbitrary field patches
• ReservationPositionsRequest – Reorders reservations within a day plan
• AccommodationCreateRequest – Requires place_id, start_day_id, and end_day_id with optional check-in/out times (lines 22-33)

Database Connection and Query Patterns

The TREK codebase connects these Zod schemas to SQLite through specific service layer patterns:

1. Read Operations – Services in server/src/services/ execute raw SQL that aliases columns to match Zod schema keys, then validate results against tripSchema or reservationSchema before returning to clients.

2. Write Operations – Controllers validate payloads against *RequestSchema types, then services map validated fields directly to INSERT or UPDATE statements on the underlying tables.

3. Computed Data – Aggregate fields like day_count and shared_count are derived via LEFT JOIN operations and subqueries in the TRIP_SELECT view, ensuring the API returns denormalized data without additional requests.

Code Examples

Typing a Trip in the Frontend

import { Trip } from '@/shared/src/trip/trip.schema';

function renderTripCard(trip: Trip) {
  return (
    <div>
      <h2>{trip.title}</h2>
      <p>{trip.start_date} – {trip.end_date}</p>
      <p>{trip.day_count ?? '–'} days • {trip.place_count ?? '–'} places</p>
    </div>
  );
}

Creating a Reservation (Node/Express Handler)

import { reservationCreateRequestSchema } from '@/shared/src/reservation/reservation.schema';
import type { ReservationCreateRequest } from '@/shared/src/reservation/reservation.schema';

export async function createReservation(req: Request, res: Response) {
  const parsed = reservationCreateRequestSchema.safeParse(req.body);
  if (!parsed.success) return res.status(400).json(parsed.error);

  const payload: ReservationCreateRequest = parsed.data;
  // Service layer writes to DB
  const reservation = await reservationService.create(req.params.tripId, payload);
  res.status(201).json(reservation);
}

Querying Trips (SQL Fragment)

This query mirrors the TRIP_SELECT view that populates computed fields:

SELECT
  t.id,
  t.user_id,
  t.title,
  t.start_date,
  t.end_date,
  t.currency,
  t.is_archived,
  COUNT(DISTINCT d.id)   AS day_count,
  COUNT(DISTINCT p.id)   AS place_count,
  CASE WHEN t.user_id = :currentUserId THEN 1 ELSE 0 END AS is_owner,
  u.username AS owner_username,
  (SELECT COUNT(*) FROM trip_members tm WHERE tm.trip_id = t.id) AS shared_count
FROM trips t
LEFT JOIN days d ON d.trip_id = t.id
LEFT JOIN places p ON p.trip_id = t.id
JOIN users u ON u.id = t.user_id
WHERE t.id = :tripId
GROUP BY t.id;

Summary

• TREK uses Zod schemas in shared/src/trip/trip.schema.ts and shared/src/reservation/reservation.schema.ts as the authoritative source for database structure and API contracts.
• Trips store core metadata (title, dates, currency) alongside computed fields (day count, place count) populated via the TRIP_SELECT view.
• Reservations track transportation legs with endpoint schemas (from/to/stop) and support external sync fields for integrations like AirTrail.
• Accommodations link to trip days via start_day_id and end_day_id, with joined place data for display.
• SQLite stores booleans as integers (0/1) and timestamps as strings, validated at runtime by Zod before reaching the database layer.

Frequently Asked Questions

How does TREK handle the relationship between trips and reservations?

Reservations contain a trip_id foreign key linking them to the trips table, along with optional day_id and end_day_id fields that position the reservation within the trip timeline. The schema supports many reservations per trip, with ordering controlled by the day_plan_position integer field.

What is the purpose of the metadata column in the reservation schema?

The metadata field stores a JSON string containing provider-specific data (e.g., airline confirmation details, booking site references). This allows TREK to capture arbitrary external data without rigid schema migrations, while the external_source and external_id fields enable synchronization with third-party services.

Can reservations exist without accommodations in TREK?

Yes. The schema separates these concerns: reservations track transportation (flights, trains) while accommodations track lodging. They share optional place_id references but maintain independent tables. A reservation may have an accommodation_id field, but this is optional and stored as TEXT to accommodate legacy identifiers.

How are computed fields like day_count generated in TREK?

Computed fields are generated by SQL aggregate queries in the service layer, not stored in the database. The TRIP_SELECT view joins trips with days and places tables, using COUNT(DISTINCT) and subqueries to calculate statistics on the fly, ensuring data consistency without manual synchronization.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}