DOCS / DATA & INFRASTRUCTURE / DATA MODEL

Data model

The Prisma schema in one page. Lists every table, the columns that matter for understanding the system, and the relationships. Source of truth is packages/db/prisma/schema.prisma; this page summarises the locked shape.

~5 MIN READ

Shape

SQLite locally (Cloudflare D1 in prod, same SQLite dialect via the Prisma D1 adapter). Two pools: core tables tied to user activity, and reference tables that are read-mostly catalog data. Soft-deletes are not used — hard delete on user request (data wipe).

Core tables

TABLE	KEY FIELDS	NOTES
`User`	cid (unique), displayName, email?, homeCountry, vCredBalance, createdAt	One row per signed-in pilot. Keyed by VATSIM CID. All in-game state cascades from here.
`Mission`	userId, class, tier, dep, arr, aircraft, baseReward, finalReward, status, acceptedAt, completedAt, …	One row per offered/accepted/completed mission. State machine: OFFERED → ACCEPTED → IN_PROGRESS → COMPLETED \| COMPLETED_DIVERTED \| FAILED \| EXPIRED \| ABANDONED.
`PilotPositionSample`	userId, capturedAt, latitude, longitude, altitude, groundspeed, callsign	Live position samples written every 15s during accepted missions. 90-day retention after the related mission's terminal state.
`MissionCompletionLog`	missionId, snapshot, outcome, vCredAwarded, xpAwarded, createdAt	Audit row per terminal-state transition. Immutable; sourced from the completion tick.
`UserSim`	userId, platform	Declared simulator platforms (MSFS_2024, MSFS_2020, XPLANE, P3D, FSX).
`UserAircraft`	userId, aircraftRefId	Declared aircraft in the hangar.
`TypeRating`	userId, aircraftRefId, vCredPaid, purchasedAt	Type ratings held. Authorizes a hangar entry for completion credit.
`ClassMembership`	userId, class, tier, xpInClass	Per-class state — current tier + accumulated XP.
`TheoryAttempt`	userId, class, tier, questions, answers, scorePercent, outcome, feePaid, cooldownUntil	One row per test attempt. JSON-encoded question snapshot for replay + audit.
`VisitedAirport`	userId, icao, firstVisitedAt	Tracks the 'first-time at airport' multiplier — one-shot per airport.

Reference tables

TABLE	KEY FIELDS	NOTES
`AirportRef`	icao (unique), name, country, lat/lon, runwayCodeNumber, maxAircraftCodeLetter, surface, ifrSuitable	Structural airport catalog. ~8,000 entries. Filtered by class templates at generation time.
`AircraftRef`	icaoType (unique), displayName, category, typeRatingCost	Curated aircraft catalog. ICAO type designators only; no per-livery entries.
`AircraftRefClass`	aircraftRefId, class	Which classes a given aircraft is flyable for. Many-to-many.
`TheoryQuestion`	externalId, class, tier, prompt, options, correctIndex, studyLinks	Question bank. Authored as YAML, imported into the table.
`BetaSignup`	email (unique), source, createdAt	Waitlist. Throwaway table — dropped on public-beta open.

Conventions

IDs. Internal PKs are String @id @default(cuid()). External IDs (VATSIM cid, ICAO codes, ICAO type designators) are stored as @unique strings, never used as PKs.
Money + XP. All ∀Cred and XP fields are Int (no floats — currency precision matters).
Timestamps. Every row has createdAt and updatedAt (UTC DateTime).
Enums. SQLite doesn't support Prisma enums; enum-like fields use String columns. Values are constrained in code via @vcareer/shared constants.
JSON. Where needed (theory question snapshots, mission completion log details), JSON is stored as a String column with a structured parser in the app layer.

Deletes + cascades

Every owned row cascades from User. Deleting a user wipes sims, hangar, classes, type ratings, missions, theory attempts, position samples, completion logs, visited airports — all of it. This is the contract behind the privacy-page "delete your account" promise.

Reference tables (AirportRef, AircraftRef, TheoryQuestion) are not user-owned. They persist across user deletes; they're updated on release-deploy via the seed pipeline.

For the full Prisma definition see packages/db/prisma/schema.prisma.

← PREVIOUS

Long Haul class

VATSIM API research