Database Schema

Waylog's data layer is built on PostgreSQL (hosted via Supabase) and managed entirely through Drizzle ORM. Our schema is highly normalized and partitioned into distinct logical domains.

All schema definitions reside in packages/db/src/schema/.

1. Identity & Auth (auth.ts)

This domain is managed automatically by Better Auth. It includes the core user table, active session records, OAuth account links, and email verification tokens. We extend these base tables using Drizzle's relations to connect users to their gaming profiles and VTCs.

2. Profiles & Organizations (profile.ts)

This module manages the social and organizational aspects of the platform.

User Profiles & Stats

• userProfile: Stores user preferences, theme settings, and gaming identities (Steam ID, TruckersMP ID). It also caches data directly from the TMP API to minimize external requests.
• driverStats: A heavily denormalized table designed for fast read access. It tracks career statistics (total distance, income, deliveries) split by game (ETS2 vs ATS) and session type (SP vs MP).

Virtual Trucking Companies (VTCs)

The schema supports a massive, Discord-style organization structure:

• vtc: The core company record.
• vtcRole: Custom company roles featuring a hierarchical position and a 64-bit permissions bitmask.
• vtcMember: Tracks individual user memberships, join dates, and crucially, Fine-Grained Access Control (FGAC) overrides.
• vtcApplication: Manages the pipeline of user recruitment.

3. Telemetry & Jobs (jobs.ts)

This is the logistical heart of Waylog.

• job: The definitive record of a delivery. It is uniquely identified by a composite key (source, destination, cargo, time). It captures the state of the truck and trailer at departure, and records the economic outcome (revenue, XP, damage) upon arrival.
• jobRoutePoint: High-frequency, lightweight GPS breadcrumbs. These records power the map visualizations on the web dashboard.
• jobEvent: A JSONB-backed discriminated union that logs discrete moments during a drive, such as paying a toll or catching a ferry.
• jobLiveState: An ephemeral table designed for real-time dashboarding. It holds a single, frequently overwritten JSONB snapshot of a user's current telemetry state.