Technical Documentation

This document describes the technical internals of Tours — a complete tours & experiences booking marketplace built as a single Next.js 16 App Router application with React 19, TypeScript, Tailwind CSS, and PostgreSQL (Neon in production, local Postgres in development). It is written for developers extending or maintaining the platform and is grounded in the actual codebase: the route handlers, the self-healing schema layer, the core/product split, and the auth/storage helpers.

Architecture at a Glance

Unlike a split client/server stack, Tours is one deployable Next.js application. The same project renders the public site, the admin panel, the traveler (customer) portal, the operator portal, and the influencer portal, and it also exposes the HTTP API through route handlers (route.ts files under src/app/api). There is no separate API server, no ORM, and no message broker.

Domain vocabulary vs database names

Tours speaks the language of tours & experiences, but for stability the underlying table and column names were kept from the original build. Keep the mapping in mind when reading SQL:

Use tour vocabulary in code and UI copy; use the real table/column names only in SQL.

Route Groups

src/app is organised into App Router route groups — parenthesised folders do not appear in the URL:

Request Lifecycle

A typical API request flows like this:

Browser (site / admin / traveler / operator / influencer)
   │  fetch  /api/v1/<resource>
   ▼
Next.js Route Handler  (src/app/api/v1/<resource>/route.ts)
   ├─ ensure<Table>Schema()           # idempotent CREATE TABLE IF NOT EXISTS
   ├─ auth guard FIRST                 # getSession() (admin) / getCustomerId() /
   │                                   #   getOperatorId() (portals)
   ├─ requirePermission(...)           # admin routes only, per-route RBAC
   ├─ validate + coerce input          # never trust client amounts/ids
   ├─ business logic + parameterized SQL   # sql`SELECT … FROM …`  (src/lib/db.ts)
   └─ ok(data) / err(msg, status)      # JSON via src/lib/api-helpers
   ▼
PostgreSQL (Neon serverless over HTTPS, or local Postgres)
   +  Cloudflare R2 (uploads)  +  License server  +  Integration providers

Because each handler calls its ensure*Schema() function before querying, a fresh database never throws relation does not exist — the schema heals itself on first use.

Cross-cutting Concerns

• Schema healing — every table is defined by an ensure*Schema() function that runs CREATE TABLE IF NOT EXISTS plus ALTER TABLE … ADD COLUMN IF NOT EXISTS for later additions. An in-memory guard runs each one once per server process.
• Secrets — third-party credentials (SMTP, payment keys, storage keys) are stored encrypted with AES-256-GCM (encryptSecret / decryptJson in src/lib/secrets.ts) inside the integration_connections table and never returned to the client in plaintext.
• Storage — uploads go to Cloudflare R2 (or any S3-compatible bucket) through the AWS S3 SDK; images can be resized with sharp before upload.
• Public IDs — booking- and tour-facing entities get a non-sequential public_id (b… / p…) so URLs never leak integer counters (bookings.public_id, listings.public_id).
• Extensibility — features and integration providers are add-ons under src/product, aggregated into generated indexes and surfaced through the src/core registries (see Core / Product Layer Split).

Core / Product Layer Split

Tours separates stable framework code from swappable features and providers, so the same engine can power many products. Two top-level folders cooperate:

src/core/      # contracts + registries (stable, shipped with every build)
src/product/   # add-ons (features + integration providers) + GENERATED indexes

• src/core defines the contracts and the registries the app reads at runtime:

  • core/channels/registry.ts → CHANNELS — the catalog of integration channels (email, payments, storage, analytics, video, calendar, captcha, sms, chat…), each with its free built-in providers, merged with provider add-ons.
  • core/plugins/registry.ts → FEATURES / FeaturePlugin — the feature-plugin contract (nav items; admin/portal/public pages; API handlers; lifecycle hooks like booking.confirmed; ensureSchema()).
  • core/payments/registry.ts → PAYMENT_DRIVERS — checkout drivers, with built-in free drivers (cod, bank, paypal) plus premium gateway add-ons.
  • core/adapters/{client,server}.ts → clientAdapters() / serverAdapters() — runtime dispatch that resolves a provider's implementation by kind + provider.

• src/product/addons/<id>/ holds the 63 built-in add-ons. Each has an addon.json manifest plus an index.ts that exports a channel, a plugin, a driver, and/or client/server adapters. Feature add-ons (e.g. announcements, audit-logs, webhooks, digest) own their own tables through an ensureSchema() and ship their own React pages and API handlers.

• Generated indexes are produced by scripts/bundle-addons.mjs and must not be edited by hand: src/product/addons/index.ts (ADDON_CHANNELS), src/product/features/index.ts (ADDON_FEATURES), src/product/adapters-server/index.ts, and src/product/adapters-client/index.ts. The core registries import these to merge add-ons in at build time.

New features and new integration providers are added by dropping an add-on folder and regenerating the indexes (npm run addons:bundle) — core code is not touched.

Data Access Pattern

There is no ORM and no migration framework. Data access is raw, parameterized SQL through a thin database wrapper.

src/lib/db.ts picks the right driver from DATABASE_URL — Neon's HTTP driver for *.neon.tech hosts, postgres.js for a vanilla local/self-hosted Postgres — and re-exposes the same tagged-template API so callers keep writing sql\…``:

import sql, { rawSql } from "@/lib/db";

// `listings` is the tours table — the original table names are kept in the DB.
const rows =
  await sql`SELECT id, name FROM listings WHERE status = ${"published"}`;

// dynamic / parameterized form ($1, $2, …):
const dyn = await rawSql("SELECT 1 FROM listings WHERE slug = $1 LIMIT 1", [slug]);

The tagged template and rawSql(text, params) are the only safe ways to query — never string-concatenate user input into SQL, and never build dynamic table/column names from input.

Schema is defined in code, not in .sql files. Each domain area has a self-healing ensure*Schema() function:

// src/product/booking/schema.ts (abridged)
let ready = false;
export async function ensureBookingSchema(): Promise<void> {
  if (ready) return; // in-memory guard → runs once per process
  await sql`CREATE TABLE IF NOT EXISTS listings ( … )`;   // listings = tours
  await sql`ALTER TABLE listings ADD COLUMN IF NOT EXISTS itinerary JSONB DEFAULT '[]'`;
  await sql`CREATE TABLE IF NOT EXISTS units ( … )`;      // units = departures
  await sql`CREATE TABLE IF NOT EXISTS bookings ( … )`;
  await sql`CREATE INDEX IF NOT EXISTS idx_units_listing ON units(listing_id, start_date)`;
  ready = true;
}

Conventions throughout the schema:

• SERIAL integer primary keys; snake_case columns.
• Money stored as integer cents, paired with a currency column (default 'EUR').
• Arrays/objects stored as JSONB; enums stored as VARCHAR "soft enums".
• Common columns is_active, sort_order, status, created_at, updated_at.
• Idempotent CREATE TABLE IF NOT EXISTS + ALTER TABLE … ADD COLUMN IF NOT EXISTS.

The schema is split across files: src/lib/vendor-schema.ts (customers, commerce, users, settings), src/product/booking/schema.ts (tours=listings, departures=units, bookings, extras, coupons, reviews), src/product/master/schema.ts (inclusion / itinerary master items), src/lib/operators.ts (operators=hosts, earnings, payouts), src/lib/influencers.ts (affiliate), plus scripts/init-db.ts (CMS tables) and per-add-on schema.ts files. The init scripts (scripts/init-db.ts then scripts/init-core.ts, chained by npm run db:init) simply call the same ensure*Schema() functions in bulk so a fresh database is fully provisioned.

Authentication & Authorization

Tours has two independent identities, both custom JWT with no refresh tokens.

Admin

src/lib/auth.ts issues an HS256 JWT signed with JWT_SECRET, stored in the admin_token httpOnly cookie. Admins live in the users table (email + bcrypt password, optional role_id → roles).

export interface JWTPayload { id: number; email: string; name: string; }
export async function signToken(p: JWTPayload): Promise<string> { … }   // jose, HS256
export async function getSession(): Promise<JWTPayload | null> { … }     // reads cookie

• Admin API routes are gated per route with permission checks (RBAC via the roles.permissions JSONB list + the permissions catalog and require-permission.ts). There is no single global middleware guard.
• In production JWT_SECRET must be set — the app fails fast otherwise (src/lib/jwt-secret.ts), and auth-schema.ts generates a random admin password if ADMIN_PASSWORD is unset.

Traveler / Operator (Portal)

src/lib/customer-auth.ts issues a separate JWT delivered as the member_session httpOnly cookie, and also accepted as an Authorization: Bearer <jwt> header for mobile clients. Accounts live in the customers table; a customer who also sells tours has a linked row in the hosts table (the operator profile).

• Passwords are bcrypt-hashed (password_hash).
• Email/phone verification and password reset use bcrypt-hashed OTP codes (otp_code, otp_expires_at, otp_purpose).
• getCustomerId() resolves the current traveler (bearer first, then cookie); getOperatorId() (src/lib/operators.ts) resolves the current operator and rejects suspended hosts.
• Every route scopes its queries to the owner (AND customer_id = me / owner_id = operatorId) to prevent IDOR, and re-validates all client-supplied values.

Storage & Secrets

• src/lib/r2.ts wraps the AWS S3 SDK against any S3-compatible bucket. Active storage config is read from the integration_connections table (channel STORAGE, providers r2 / aws_s3 / gcs / do_spaces), falling back to R2_* env vars. It exposes helpers such as putObject, getUploadUrl (presigned PUT), deleteByUrl, listAllObjects, and getPublicBaseUrl. A SignatureDoesNotMatch upload error means the stored storage secret is wrong — fix it in admin → Settings → Channels, not in code.
• src/lib/secrets.ts encrypts integration credentials with AES-256-GCM using a key derived (SHA-256) from SECRET_KEY (falling back to JWT_SECRET). Encrypted values are tagged enc:v1:<base64>. Exposed via encryptSecret, decryptSecret, encryptJson, decryptJson.

Licensing

The domain license is verified offline with a bundled RS256 public key. A single app_license row (JSONB blob) stores the activation record. src/lib/license provides activateLicense, recheckLicense, getLicenseStatus, and isLicensed. A daily background heartbeat re-validates against LICENSE_SERVER_URL with a grace period, and localhost / private-LAN hosts bypass licensing for development. See the License Guide.

Key Directories

Configuration

Runtime configuration is environment-driven, with most behaviour also editable at runtime through admin-managed settings tables (app_settings, theme_settings, integration_connections).

Verify anything after edits with npx tsc --noEmit -p tsconfig.json.

© CreativeCape Solutions · creative-cape.com · support@creative-cape.com