Database Operations - SkyTeam ROBLOX

Overview

The database package (@skyteam/database) provides functions for querying and manipulating data. All operations use Drizzle ORM.

User Operations

Location: packages/database/src/users.ts

fetchUser

Fetches a user by their ROBLOX user ID.

export async function fetchUser(userId: string): Promise<User | null>

Parameters:

userId - ROBLOX user ID

Returns: User object or null if not found Example:

const user = await fetchUser("123456789");
if (user) {
  console.log(`${user.displayName} has ${user.miles} miles`);
}

createUser

Creates a new user in the database.

export async function createUser(data: {
  userId: string;
  username: string;
  displayName: string;
  avatarUrl?: string;
}): Promise<User>

Parameters:

data.userId - ROBLOX user ID
data.username - ROBLOX username
data.displayName - Display name
data.avatarUrl - Optional avatar URL

Returns: Created user object

incrementMiles

Increases a user’s miles balance.

export async function incrementMiles(
  userId: string,
  amount = 1,
  tx?: DbTransaction,
): Promise<User | null>

Parameters:

userId - ROBLOX user ID
amount - Miles to add (default: 1)
tx - Optional transaction context

Returns: Updated user object or null if not found Implementation:

const result = await dbInstance
  .update(users)
  .set({ miles: increment(users.miles, amount) })
  .where(eq(users.userId, userId))
  .returning();

spendMiles

Deducts miles from a user’s balance with validation.

export async function spendMiles(
  userId: string,
  amount: number,
  note?: string,
): Promise<User | null>

Parameters:

userId - ROBLOX user ID
amount - Miles to spend (must be positive)
note - Optional transaction note

Returns: Updated user object or null Throws:

"User not found" - User doesn’t exist
"Insufficient miles" - User has fewer miles than amount
"amount must be positive" - Invalid amount

Behavior:

Atomic transaction - either both miles deduction and transaction record succeed, or neither
Automatically creates a miles transaction record

Flight Operations

Location: packages/database/src/flights.ts

fetchFlight

Fetches a single flight by ID.

export async function fetchFlight(id: string): Promise<Flight | null>

fetchFlightsByAirline

Fetches all flights for an airline.

export async function fetchFlightsByAirline(airlineId: string): Promise<Flight[]>

fetchComingFlights

Fetches upcoming flights (where endTime is null).

export async function fetchComingFlights(airlineId: string): Promise<Flight[]>

Returns: Flights ordered by startTime

createFlight

Creates a new flight.

export async function createFlight(data: {
  code: string;
  gameId: string;
  aircraft: string;
  airlineId: string;
  brandId: string;
  departure: string;
  arrival: string;
  codeshareAirlineId?: string;
  discordEventLink?: string;
}): Promise<Flight>

startFlight

Marks a flight as started by setting startedAt.

export async function startFlight(id: string): Promise<Flight | null>

Implementation:

const result = await db
  .update(flights)
  .set({ startedAt: new Date() })
  .where(eq(flights.id, id))
  .returning();

endFlight

Marks a flight as ended by setting endTime.

export async function endFlight(id: string): Promise<Flight | null>

fetchUserFlights

Fetches all flights a user has participated in.

export async function fetchUserFlights(
  userId: string,
): Promise<(Flight & { miles: number })[]>

Returns: Flight objects with an additional miles field showing miles earned Implementation:

Joins flights and flightPassengers tables
Filters by userId

Airline Operations

Location: packages/database/src/airlines.ts

fetchAirline

Fetches an airline by ID.

export async function fetchAirline(airlineId: string): Promise<Airline | null>

fetchAllAirlines

Fetches all airlines.

export async function fetchAllAirlines(): Promise<Airline[]>

fetchAirlineByToken

Fetches an airline by API token (used for authentication).

export async function fetchAirlineByToken(token: string): Promise<Airline | null>

createAirline

Creates a new airline with auto-generated token.

export async function createAirline(data: {
  airlineId: string;
  name: string;
  inviteLink?: string;
  serverId?: string;
}): Promise<Airline>

Behavior:

Automatically generates a CUID2 token

fetchMilesProducts

Fetches all products for an airline.

export async function fetchMilesProducts(airlineId: string): Promise<MilesProduct[]>

createMilesProduct

Creates a new miles product.

export async function createMilesProduct(data: {
  airlineId: string;
  name: string;
  priceMiles: number;
  description?: string;
  active?: boolean;
}): Promise<MilesProduct>

Behavior:

Automatically generates a 6-character alphanumeric product ID
Uses nanoid with custom alphabet: 0123456789abcdefghijklmnopqrstuvwxyz

Brand Operations

Location: packages/database/src/brands.ts

fetchAllBrands

Fetches all brands.

export async function fetchAllBrands(): Promise<Brand[]>

fetchBrand

Fetches a brand by ID.

export async function fetchBrand(brandId: string): Promise<Brand | null>

fetchBrandByIATA

Fetches a brand by IATA code.

export async function fetchBrandByIATA(iata: string): Promise<Brand | null>

fetchAirlineBrands

Fetches all brands for an airline.

export async function fetchAirlineBrands(airlineId: string): Promise<Brand[]>

fetchPrimaryBrand

Fetches the primary brand for an airline.

export async function fetchPrimaryBrand(airlineId: string): Promise<Brand | null>

createBrand

Creates a new brand.

export async function createBrand(data: {
  brandId: string;
  airlineId: string;
  name: string;
  iata: string;
  icao: string;
  callsign: string;
  isPrimary?: boolean;
  logoUrl?: string;
  accentColor: string;
  secondaryColor: string;
  elementColor: string;
}): Promise<Brand>

Flight Passenger Operations

Location: packages/database/src/flightPassengers.ts

addPassengerToFlight

Adds a passenger to a flight and awards miles.

export async function addPassengerToFlight(data: {
  flightId: string;
  userId: string;
  miles: number;
}): Promise<FlightPassenger>

Behavior:

Atomic transaction
Creates passenger record
Increments user’s miles
Creates miles transaction record

Implementation:

return await db.transaction(async (tx) => {
  const inserted = await tx.insert(flightPassengers).values(data).returning();
  await incrementMiles(data.userId, data.miles, tx);
  await addMilesTransaction(
    {
      userId: data.userId,
      amount: data.miles,
      type: "earn",
      source: "flight",
      flightId: data.flightId,
      note: "Miles earned from flight",
    },
    tx,
  );
  return inserted[0];
});

fetchFlightPassengers

Fetches all passengers for a flight.

export async function fetchFlightPassengers(
  flightId: string,
): Promise<FlightPassenger[]>

Miles Transaction Operations

Location: packages/database/src/miles.ts

addMilesTransaction

Logs a miles transaction.

export async function addMilesTransaction(
  data: {
    userId: string;
    amount: number;
    type: "earn" | "spend";
    source: "flight" | "purchase" | string;
    flightId?: string;
    productId?: string;
    note?: string;
  },
  tx?: DbTransaction,
): Promise<MilesTransaction>

Parameters:

data.userId - User ID
data.amount - Miles amount (positive for earn, negative for spend)
data.type - “earn” or “spend”
data.source - Transaction source
data.flightId - Optional flight ID
data.productId - Optional product ID
data.note - Optional description
tx - Optional transaction context

fetchMilesTransactions

Fetches all transactions for a user.

export async function fetchMilesTransactions(
  userId: string,
): Promise<MilesTransaction[]>

Utility Functions

Location: packages/database/src/utils.ts

increment

Helper for incrementing numeric columns in SQL.

export function increment(column: any, amount: number)

Usage:

await db
  .update(users)
  .set({ miles: increment(users.miles, 100) })
  .where(eq(users.userId, userId));

Transaction Support

Many functions accept an optional tx parameter for running operations within a database transaction:

await db.transaction(async (tx) => {
  await incrementMiles(userId, 100, tx);
  await addMilesTransaction({ ... }, tx);
});

This ensures atomicity - either all operations succeed or none do.

Overview

Endpoints

Database

​Overview

​User Operations

​fetchUser

​createUser

​incrementMiles

​spendMiles

​Flight Operations

​fetchFlight

​fetchFlightsByAirline

​fetchComingFlights

​createFlight

​startFlight

​endFlight

​fetchUserFlights

​Airline Operations

​fetchAirline

​fetchAllAirlines

​fetchAirlineByToken

​createAirline

​fetchMilesProducts

​createMilesProduct

​Brand Operations

​fetchAllBrands

​fetchBrand

​fetchBrandByIATA

​fetchAirlineBrands

​fetchPrimaryBrand

​createBrand

​Flight Passenger Operations

​addPassengerToFlight

​fetchFlightPassengers

​Miles Transaction Operations

​addMilesTransaction

​fetchMilesTransactions

​Utility Functions

​increment

​Transaction Support

Build docs developers (and LLMs) love

Overview

User Operations

fetchUser

createUser

incrementMiles

spendMiles

Flight Operations

fetchFlight

fetchFlightsByAirline

fetchComingFlights

createFlight

startFlight

endFlight

fetchUserFlights

Airline Operations

fetchAirline

fetchAllAirlines

fetchAirlineByToken

createAirline

fetchMilesProducts

createMilesProduct

Brand Operations

fetchAllBrands

fetchBrand

fetchBrandByIATA

fetchAirlineBrands

fetchPrimaryBrand

createBrand

Flight Passenger Operations

addPassengerToFlight

fetchFlightPassengers

Miles Transaction Operations

addMilesTransaction

fetchMilesTransactions

Utility Functions

increment

Transaction Support