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

> Explore the TREK database schema structure for trips and reservations. Discover how Zod schemas manage travel metadata, computed stats, and normalized reservation tables efficiently.

- Repository: [Maurice/TREK](https://github.com/mauriceboe/TREK)
- Tags: architecture
- Published: 2026-07-04

---

**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`](https://github.com/mauriceboe/TREK/blob/main/shared/src/trip/trip.schema.ts) and [`shared/src/reservation/reservation.schema.ts`](https://github.com/mauriceboe/TREK/blob/main/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`](https://github.com/mauriceboe/TREK/blob/main/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`](https://github.com/mauriceboe/TREK/blob/main/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

```typescript
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)

```typescript
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:

```sql
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`](https://github.com/mauriceboe/TREK/blob/main/shared/src/trip/trip.schema.ts) and [`shared/src/reservation/reservation.schema.ts`](https://github.com/mauriceboe/TREK/blob/main/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.