Skip to main content

​
Overview

The SkyTeam ROBLOX database uses PostgreSQL with Drizzle ORM. The schema is defined in packages/database/src/schema.ts.

​
Tables

​
users

Stores ROBLOX user information and miles balance.
​
userId
text
required
Primary key. ROBLOX user ID.
​
username
text
required
ROBLOX username (unique).
​
displayName
text
required
User’s display name.
​
miles
integer
default:"0"
required
Current miles balance.
​
avatarUrl
text
URL to user’s avatar image.
​
createdAt
timestamp
default:"now()"
required
When the user record was created.
​
updatedAt
timestamp
default:"now()"
required
When the user record was last updated.

​
airlines

Stores airline information and API tokens.
​
airlineId
text
required
Primary key. Unique airline identifier.
​
name
text
required
Airline name.
​
token
text
required
API authentication token (unique). Generated using CUID2.
​
inviteLink
text
default:"https://discord.gg/skyteam"
required
Discord invite link for the airline.
​
serverId
text
default:"1122953128703168532"
required
Discord server ID.
​
createdAt
timestamp
default:"now()"
required
When the airline was created.
​
updatedAt
timestamp
default:"now()"
required
When the airline was last updated.

​
brands

Stores airline brands (sub-brands or operating brands).
​
brandId
text
required
Primary key. Unique brand identifier.
​
airlineId
text
required
Foreign key to airlines.airlineId.
​
name
text
required
Brand name.
​
iata
text
required
IATA airline code (2 characters, unique).
​
icao
text
required
ICAO airline code (3 characters, unique).
​
callsign
text
required
Radio callsign (unique).
​
isPrimary
boolean
default:"false"
required
Whether this is the airline’s primary brand.
​
logoUrl
text
URL to brand logo image.
​
accentColor
text
default:"#2ea5f7"
required
Brand accent color (hex).
​
secondaryColor
text
default:"#242429"
required
Brand secondary color (hex).
​
elementColor
text
default:"#fbfbfb"
required
Brand element color (hex).

​
flights

Stores scheduled and completed flights.
​
id
uuid
required
Primary key. Auto-generated UUID.
​
code
text
required
Flight code (e.g., “ST123”).
​
gameId
text
required
ROBLOX game/place ID.
​
aircraft
text
required
Aircraft type.
​
airlineId
text
required
Foreign key to airlines.airlineId.
​
brandId
text
required
Foreign key to brands.brandId.
​
startTime
timestamp
default:"now()"
required
Scheduled start time.
​
endTime
timestamp
When the flight ended (null for active/upcoming flights).
​
codeshareAirlineId
text
Foreign key to airlines.airlineId for codeshare partner.
​
departure
text
required
Departure airport code.
​
arrival
text
required
Arrival airport code.
​
startedAt
timestamp
When the flight actually started (null until started).
​
discordEventLink
text
default:"https://discord.gg/skyteam"
required
Discord event link for the flight.

​
flightPassengers

Links users to flights and tracks miles earned per flight.
​
flightId
uuid
required
Foreign key to flights.id.
​
userId
text
required
Foreign key to users.userId.
​
miles
integer
required
Miles earned from this flight.
​
joinedAt
timestamp
default:"now()"
required
When the user joined the flight.
This table has a composite key of (flightId, userId).

​
milesTransactions

Logs all miles earnings and spending for audit trail.
​
id
uuid
required
Primary key. Auto-generated UUID.
​
userId
text
required
Foreign key to users.userId.
​
amount
integer
required
Miles amount (positive for earn, negative for spend).
​
type
text
required
Transaction type: ‘earn’ or ‘spend’.
​
source
text
required
Source of transaction: ‘flight’, ‘purchase’, or custom string.
​
flightId
uuid
Foreign key to flights.id (if transaction related to a flight).
​
productId
text
Product ID (if transaction was a purchase).
​
note
text
Optional note describing the transaction.
​
createdAt
timestamp
default:"now()"
required
When the transaction occurred.

​
milesProducts

Stores purchasable products that airlines can offer.
​
productId
text
required
Primary key. 6-character alphanumeric ID.
​
airlineId
text
required
Foreign key to airlines.airlineId.
​
name
text
required
Product name.
​
description
text
Product description.
​
priceMiles
integer
required
Cost in miles.
​
active
boolean
default:"true"
required
Whether product is available for purchase.
​
createdAt
timestamp
default:"now()"
required
When the product was created.

​
Relationships

​
Airlines → Brands

  • One-to-many: An airline can have multiple brands
  • Foreign key: brands.airlineId → airlines.airlineId

​
Airlines → Flights

  • One-to-many: An airline can operate multiple flights
  • Foreign key: flights.airlineId → airlines.airlineId

​
Brands → Flights

  • One-to-many: A brand can operate multiple flights
  • Foreign key: flights.brandId → brands.brandId

​
Flights → FlightPassengers → Users

  • Many-to-many through flightPassengers
  • A flight can have many passengers
  • A user can be on many flights

​
Users → MilesTransactions

  • One-to-many: A user can have multiple transactions
  • Foreign key: milesTransactions.userId → users.userId

​
Airlines → MilesProducts

  • One-to-many: An airline can offer multiple products
  • Foreign key: milesProducts.airlineId → airlines.airlineId

​
Schema Definition

Location: packages/database/src/schema.ts 
import {
  pgTable,
  text,
  timestamp,
  integer,
  boolean,
  uuid,
} from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  userId: text("userId").primaryKey(),
  username: text("username").notNull().unique(),
  displayName: text("displayName").notNull(),
  miles: integer("miles").notNull().default(0),
  avatarUrl: text("avatarUrl"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().notNull(),
});

export const airlines = pgTable("airlines", {
  airlineId: text("airlineId").primaryKey(),
  name: text("name").notNull(),
  token: text("token").notNull().unique(),
  inviteLink: text("inviteLink")
    .notNull()
    .default("https://discord.gg/skyteam"),
  serverId: text("serverId").notNull().default("1122953128703168532"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().notNull(),
});

// ... additional tables

Build docs developers (and LLMs) love

Get started for freeTalk to us