Technical Documentation

This document describes the technical internals of PubCrawl — a white-label pub crawl & nightlife tour booking marketplace built as a single Next.js 16 App Router application with React 19, TypeScript, Tailwind CSS, and serverless PostgreSQL (Neon). Hosts (guides) publish crawls; each crawl runs on scheduled sessions (departures) with a fixed number of spots; customers browse, pick a departure, book spots, and pay a deposit or the full amount. This is written for developers extending or maintaining the platform and is grounded in the actual codebase (route handlers, the idempotent schema layer, the booking spine, and the auth/storage helpers).

Architecture

Unlike a split client/server stack, PubCrawl is one deployable Next.js application. The same project renders the public storefront, the four signed-in portals (customer, host, admin, influencer), and 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.

Route Groups & Portals

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 / customer / host / admin / influencer)
   │  fetch  /api/v1/<resource>
   ▼
Next.js Route Handler  (src/app/api/v1/<resource>/route.ts)
   ├─ auth check                      # getSession() / getCustomerId() / getHostId() / getInfluencerId()
   ├─ ensure<Domain>Schema()          # idempotent CREATE TABLE IF NOT EXISTS on first use
   ├─ validate + coerce input         # never trust client amounts/ids (re-quote server-side)
   ├─ business logic + raw SQL        # sql`SELECT … FROM …`  (src/lib/db.ts)
   └─ ok(data) / err(msg, status)     # JSON response (src/lib/api-helpers.ts)
   ▼
PostgreSQL (Neon, serverless driver over HTTPS)
   +  Cloudflare R2 (uploads)  +  License server  +  Payment / integration providers

Handlers call the relevant ensure*Schema() before querying, so a fresh database never throws relation does not exist — the schema provisions itself on first use.

Cross-cutting Concerns

• Schema provisioning — every domain area 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 before upload.
• Public IDs — customer-facing entities get a non-sequential public_id (ensurePublicId()) so URLs don't leak integer counters; queries prefer public_id over serial ids.
• Authorization — every route guards auth first and scopes queries to the owner (AND customer_id = me / owner_id = hostId), so there is no IDOR.

Data Layer

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

src/lib/db.ts lazily initialises a Neon client and re-exposes the tagged-template API so callers keep writing sql\…``:

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

const rows =
  await sql`SELECT id, name FROM listings WHERE status = ${"published"}`;
// dynamic/parameterised form:
const dyn = await rawSql("SELECT 1 FROM listings WHERE slug = $1 LIMIT 1", [
  slug,
]);

Schema is defined in code, not in .sql files. Each domain area has an idempotent ensure*Schema() function called before its tables are touched:

// e.g. src/lib/booking/* (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 bookings ( … )`;
  await sql`ALTER TABLE bookings ADD COLUMN IF NOT EXISTS booking_mode TEXT DEFAULT 'per_person'`;
  ready = true;
}

Conventions throughout the schema:

• SERIAL integer primary keys; snake_case columns; opaque public_id on customer-facing rows.
• Money stored as integer cents (*_cents) with a sibling currency column.
• Arrays/objects stored as JSONB.
• Common columns is_active, sort_order, created_at, updated_at.
• Idempotent CREATE TABLE IF NOT EXISTS + ALTER TABLE … ADD COLUMN IF NOT EXISTS.

The Booking Spine

The domain centres on three tables, provisioned and queried through src/lib/booking/*:

listings                 a crawl (host_id, experience_type, from_price_cents, policy, …)
   └─ experience_sessions a scheduled sitting (start_at, seats_total, seats_booked, status)
        └─ bookings       a reservation (product_type, session_id, booking_mode, seats, guest_*)

• Availability (getSessionSlots) lists a crawl's bookable departures in a window, each with seats_left; a full sitting renders as sold out rather than disappearing.
• Quote (quoteSession) prices a { session_id, seats, booking_mode } request server-side — per-seat (per_person) or a private buyout (private) — returning subtotal, extras, and totals in cents. The client may only choose options, never the amount.
• Atomic seat claim (claimSeats) makes overselling impossible: the capacity check and the increment are a single statement — UPDATE experience_sessions SET seats_booked = seats_booked + n WHERE seats_total - seats_booked >= n. Seats are claimed before the booking row is written, and releaseSeats (clamped at 0) hands them back on a failed booking or a cancellation.
• Related tables: booking_extras (paid add-ons e.g. a photo package), booking_payments, booking_refunds, booking_emails, booking_notes; amenities/amenity_groups render as "What's included", and extras, coupons, gift_cards price the checkout.

Authentication & Authorization

PubCrawl has four independent sessions, all custom JWT (via jose), each in its own httpOnly cookie and also accepted as an Authorization: Bearer <jwt> header for mobile clients (bearer is checked before the cookie).

• Admin (src/lib/auth.ts) issues an HS256 JWT signed with JWT_SECRET. Admin API routes are gated per route with permission checks (RBAC via roles.permissions + the permissions catalog) — there is no global middleware guard.
• Customer / host (src/lib/customer-auth.ts) share the member_session cookie; a host is a customer who also owns an active hosts row (getHostId() in src/lib/hosts.ts). Passwords are bcrypt-hashed; email/phone verification and password reset use bcrypt-hashed OTP codes.
• Influencer (src/lib/influencer-auth.ts) is a fully separate identity with its own cookie.

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, presigned uploads, deleteByUrl, and getPublicBaseUrl.
• src/lib/secrets.ts encrypts integration credentials with AES-256-GCM using a key derived from SECRET_KEY (falling back to JWT_SECRET). Encrypted values are tagged enc:v1:<base64>. Exposed via encryptSecret, decryptSecret, encryptJson, decryptJson.

Payments & Refunds

• Booking flow: pick crawl → pick a departure (from availability) → choose spots → checkout → pay deposit or full. POST /api/v1/bookings/checkout creates the pending booking (re-quotes server-side), computes the amount (deposit only when the guest chose it and one exists), then starts payment via the chosen gateway (startBookingPayment).
• Gateways are resolved at runtime from admin-configured integration_connections (channel PAYMENTS). Built-in drivers live in src/core/payments; additional gateways (Stripe, Mollie, Paystack, Flutterwave, Razorpay, …) ship as add-ons under src/product/addons/payments-*.
• Cancellation & refunds: tiered policies (flexible / moderate / firm / strict / non-refundable); the refund amount is computed from the policy + lead time (src/lib/booking/cancellation-policy.ts). Refunds go through an admin approval queue (booking_refunds), and seats are released on cancellation. Both customer- and host-initiated cancellations are supported, with email / WhatsApp / SMS notifications.

Internationalisation

• Static UI strings are stored as per-language JSON in Cloudflare R2, keyed by language + scope, and loaded through useT() (client) / getServerT() (server).
• Dynamic content (crawl name / summary / description) is machine-translated (OpenAI / Anthropic) and cached in the translations table with source-hash invalidation and a human-pin override — there are no per-language DB columns. Reference data lives in languages, countries, currencies, and locations.

Licensing

The domain license is verified offline with a bundled RS256 public key; a single license row stores the activation record. src/lib/license provides activate / recheck / status helpers, a daily 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).

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