TypeScript 97.6%
SQL 1.4%
JavaScript 0.5%
1import { sql } from "drizzle-orm";2import {3 bigint,4 boolean,5 index,6 integer,7 jsonb,8 numeric,9 pgTable,10 primaryKey,11 text,12 timestamp,13 uniqueIndex,14 uuid,15 varchar,16} from "drizzle-orm/pg-core";1718const ts = (name?: string) => (name ? timestamp(name, { withTimezone: true, mode: "date" }) : timestamp({ withTimezone: true, mode: "date" }));19const now = () => ts().notNull().defaultNow();2021/* ------------------------------------------------------------------ users */2223export const users = pgTable(24 "users",25 {26 id: uuid().primaryKey().defaultRandom(),27 username: varchar({ length: 24 }).notNull(),28 usernameNormalized: varchar({ length: 24 }).notNull(),29 passwordHash: text().notNull(),30 level: integer().notNull().default(1),31 xp: bigint({ mode: "number" }).notNull().default(0),32 status: varchar({ length: 16 }).notNull().default("active"), // active | suspended | deleted33 ageConfirmedAt: ts(),34 createdAt: now(),35 lastLoginAt: ts(),36 lastRescueAt: ts(),37 totalSpins: bigint({ mode: "number" }).notNull().default(0),38 gamesPlayed: integer().notNull().default(0),39 biggestWin: bigint({ mode: "number" }).notNull().default(0),40 biggestMultiplier: numeric({ precision: 12, scale: 2 }).notNull().default("0"),41 },42 (t) => [uniqueIndex("users_username_normalized_idx").on(t.usernameNormalized), uniqueIndex("users_username_idx").on(t.username)],43);4445export const sessions = pgTable(46 "sessions",47 {48 id: uuid().primaryKey().defaultRandom(),49 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),50 tokenHash: text().notNull(),51 createdAt: now(),52 expiresAt: ts().notNull(),53 lastSeenAt: now(),54 userAgent: text(),55 ip: varchar({ length: 64 }),56 },57 (t) => [uniqueIndex("sessions_token_hash_idx").on(t.tokenHash), index("sessions_user_idx").on(t.userId)],58);5960export const recoveryCodes = pgTable("recovery_codes", {61 userId: uuid().primaryKey().references(() => users.id, { onDelete: "cascade" }),62 codeHash: text().notNull(),63 createdAt: now(),64 rotatedAt: ts(),65 usedAt: ts(),66 useCount: integer().notNull().default(0),67});6869export const userSettings = pgTable("user_settings", {70 userId: uuid().primaryKey().references(() => users.id, { onDelete: "cascade" }),71 soundEnabled: boolean().notNull().default(true),72 musicVolume: numeric({ precision: 3, scale: 2 }).notNull().default("0.6"),73 effectsVolume: numeric({ precision: 3, scale: 2 }).notNull().default("0.8"),74 masterVolume: numeric({ precision: 3, scale: 2 }).notNull().default("0.8"),75 reduceMotion: boolean().notNull().default(false),76 animationIntensity: varchar({ length: 8 }).notNull().default("high"),77 sessionReminderMinutes: integer().notNull().default(60),78 breakReminder: boolean().notNull().default(true),79 leaderboardOptIn: boolean().notNull().default(true),80 updatedAt: now(),81});8283/* ----------------------------------------------------------------- wallet */8485export const wallets = pgTable("wallets", {86 userId: uuid().primaryKey().references(() => users.id, { onDelete: "cascade" }),87 balance: bigint({ mode: "number" }).notNull().default(0),88 lifetimeWagered: bigint({ mode: "number" }).notNull().default(0),89 lifetimeWon: bigint({ mode: "number" }).notNull().default(0),90 lifetimeGranted: bigint({ mode: "number" }).notNull().default(0),91 updatedAt: now(),92});9394export const creditTransactions = pgTable(95 "credit_transactions",96 {97 id: uuid().primaryKey().defaultRandom(),98 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),99 type: varchar({ length: 24 }).notNull(),100 amount: bigint({ mode: "number" }).notNull(), // signed101 balanceAfter: bigint({ mode: "number" }).notNull(),102 reference: text(), // round id, achievement key, mission key, admin note...103 meta: jsonb().$type<Record<string, unknown>>(),104 createdAt: now(),105 },106 (t) => [index("ctx_user_created_idx").on(t.userId, t.createdAt), index("ctx_type_idx").on(t.type), index("ctx_reference_idx").on(t.reference)],107);108109/* ------------------------------------------------------------------ games */110111export const games = pgTable(112 "games",113 {114 id: uuid().primaryKey().defaultRandom(),115 slug: varchar({ length: 64 }).notNull(),116 name: varchar({ length: 80 }).notNull(),117 version: varchar({ length: 16 }).notNull(),118 lifecycle: varchar({ length: 16 }).notNull().default("draft"), // draft | simulation | approved | staging | published | disabled119 sortOrder: integer().notNull().default(0),120 isFeatured: boolean().notNull().default(false),121 isNew: boolean().notNull().default(true),122 summary: jsonb().$type<Record<string, unknown>>().notNull(), // public metadata snapshot (theme, volatility, palette, features)123 createdAt: now(),124 publishedAt: ts(),125 updatedAt: now(),126 },127 (t) => [uniqueIndex("games_slug_idx").on(t.slug)],128);129130export const gameVersions = pgTable(131 "game_versions",132 {133 id: uuid().primaryKey().defaultRandom(),134 gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),135 version: varchar({ length: 16 }).notNull(),136 definition: jsonb().$type<Record<string, unknown>>().notNull(),137 definitionHash: varchar({ length: 64 }).notNull(),138 certification: jsonb().$type<Record<string, unknown>>(),139 status: varchar({ length: 16 }).notNull().default("draft"),140 createdAt: now(),141 },142 (t) => [uniqueIndex("game_versions_game_version_idx").on(t.gameId, t.version)],143);144145export const gameRounds = pgTable(146 "game_rounds",147 {148 id: uuid().primaryKey().defaultRandom(),149 roundId: varchar({ length: 32 }).notNull(),150 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),151 gameId: uuid().notNull().references(() => games.id),152 gameSlug: varchar({ length: 64 }).notNull(),153 gameVersion: varchar({ length: 16 }).notNull(),154 clientRoundId: uuid().notNull(),155 bet: bigint({ mode: "number" }).notNull(),156 win: bigint({ mode: "number" }).notNull(),157 multiplier: numeric({ precision: 12, scale: 4 }).notNull(),158 balanceAfter: bigint({ mode: "number" }).notNull(),159 result: jsonb().$type<Record<string, unknown>>().notNull(),160 features: text().array().notNull().default(sql`'{}'::text[]`),161 freeSpins: boolean().notNull().default(false),162 bonus: boolean().notNull().default(false),163 jackpotTier: varchar({ length: 8 }),164 rngReference: text().notNull(),165 durationMs: integer(),166 createdAt: now(),167 },168 (t) => [169 uniqueIndex("game_rounds_round_id_idx").on(t.roundId),170 uniqueIndex("game_rounds_user_client_idx").on(t.userId, t.clientRoundId),171 index("game_rounds_user_created_idx").on(t.userId, t.createdAt),172 index("game_rounds_game_created_idx").on(t.gameId, t.createdAt),173 index("game_rounds_created_idx").on(t.createdAt),174 index("game_rounds_win_idx").on(t.win),175 ],176);177178/** In-flight cash-out ("crash") rounds. Settled rounds are also written to game_rounds. */179export const crashRounds = pgTable(180 "crash_rounds",181 {182 id: uuid().primaryKey().defaultRandom(),183 roundId: varchar({ length: 32 }).notNull(),184 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),185 gameId: uuid().notNull().references(() => games.id),186 gameSlug: varchar({ length: 64 }).notNull(),187 gameVersion: varchar({ length: 16 }).notNull(),188 clientRoundId: uuid().notNull(),189 bet: bigint({ mode: "number" }).notNull(),190 crashMultiplier: numeric({ precision: 12, scale: 2 }).notNull(),191 crashAtMs: integer().notNull(),192 seed: text().notNull(),193 commitment: varchar({ length: 64 }).notNull(),194 events: jsonb().$type<unknown[]>().notNull().default(sql`'[]'::jsonb`),195 autoCashout: numeric({ precision: 12, scale: 2 }),196 status: varchar({ length: 12 }).notNull().default("running"), // running | cashed | crashed197 cashoutMultiplier: numeric({ precision: 12, scale: 2 }),198 win: bigint({ mode: "number" }).notNull().default(0),199 startedAt: now(),200 settledAt: ts(),201 },202 (t) => [203 uniqueIndex("crash_rounds_round_id_idx").on(t.roundId),204 uniqueIndex("crash_rounds_user_client_idx").on(t.userId, t.clientRoundId),205 index("crash_rounds_user_status_idx").on(t.userId, t.status),206 ],207);208209/** Ladder-game sessions (The Vault, Escape 99): step-by-step rounds with cash-out. Settled rounds also go to game_rounds. */210export const arcadeSessions = pgTable(211 "arcade_sessions",212 {213 id: uuid().primaryKey().defaultRandom(),214 roundId: varchar({ length: 32 }).notNull(),215 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),216 gameId: uuid().notNull().references(() => games.id),217 gameSlug: varchar({ length: 64 }).notNull(),218 gameVersion: varchar({ length: 16 }).notNull(),219 clientRoundId: uuid().notNull(),220 bet: bigint({ mode: "number" }).notNull(),221 state: jsonb().$type<Record<string, unknown>>().notNull(),222 status: varchar({ length: 12 }).notNull().default("running"), // running | cashed | busted | completed223 win: bigint({ mode: "number" }).notNull().default(0),224 startedAt: now(),225 updatedAt: now(),226 settledAt: ts(),227 },228 (t) => [229 uniqueIndex("arcade_sessions_round_id_idx").on(t.roundId),230 uniqueIndex("arcade_sessions_user_client_idx").on(t.userId, t.clientRoundId),231 index("arcade_sessions_user_status_idx").on(t.userId, t.status),232 ],233);234235export const gameStatistics = pgTable("game_statistics", {236 gameId: uuid().primaryKey().references(() => games.id, { onDelete: "cascade" }),237 launches: bigint({ mode: "number" }).notNull().default(0),238 spins: bigint({ mode: "number" }).notNull().default(0),239 wagered: bigint({ mode: "number" }).notNull().default(0),240 won: bigint({ mode: "number" }).notNull().default(0),241 wins: bigint({ mode: "number" }).notNull().default(0),242 bonuses: bigint({ mode: "number" }).notNull().default(0),243 freeSpins: bigint({ mode: "number" }).notNull().default(0),244 bigWins: bigint({ mode: "number" }).notNull().default(0),245 maxWin: bigint({ mode: "number" }).notNull().default(0),246 maxMultiplier: numeric({ precision: 12, scale: 2 }).notNull().default("0"),247 favorites: integer().notNull().default(0),248 updatedAt: now(),249});250251export const gameStates = pgTable(252 "game_states",253 {254 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),255 gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),256 state: jsonb().$type<Record<string, unknown>>().notNull(),257 updatedAt: now(),258 },259 (t) => [primaryKey({ columns: [t.userId, t.gameId] })],260);261262export const userGameStats = pgTable(263 "user_game_stats",264 {265 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),266 gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),267 spins: bigint({ mode: "number" }).notNull().default(0),268 wagered: bigint({ mode: "number" }).notNull().default(0),269 won: bigint({ mode: "number" }).notNull().default(0),270 bonuses: integer().notNull().default(0),271 biggestWin: bigint({ mode: "number" }).notNull().default(0),272 biggestMultiplier: numeric({ precision: 12, scale: 2 }).notNull().default("0"),273 lastPlayedAt: now(),274 firstPlayedAt: now(),275 },276 (t) => [primaryKey({ columns: [t.userId, t.gameId] }), index("ugs_user_last_idx").on(t.userId, t.lastPlayedAt)],277);278279export const favorites = pgTable(280 "favorites",281 {282 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),283 gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),284 createdAt: now(),285 },286 (t) => [primaryKey({ columns: [t.userId, t.gameId] })],287);288289/* ------------------------------------------------------------ progression */290291export const achievements = pgTable("achievements", {292 key: varchar({ length: 48 }).primaryKey(),293 name: varchar({ length: 80 }).notNull(),294 description: text().notNull(),295 category: varchar({ length: 24 }).notNull(),296 metric: varchar({ length: 32 }).notNull(), // spins | bonuses | games_played | big_wins | multiplier | wins | level | daily_streak297 target: bigint({ mode: "number" }).notNull(),298 rewardCredits: integer().notNull().default(0),299 rewardXp: integer().notNull().default(0),300 icon: varchar({ length: 32 }).notNull().default("star"),301 sortOrder: integer().notNull().default(0),302 enabled: boolean().notNull().default(true),303});304305export const userAchievements = pgTable(306 "user_achievements",307 {308 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),309 achievementKey: varchar({ length: 48 }).notNull().references(() => achievements.key, { onDelete: "cascade" }),310 unlockedAt: now(),311 },312 (t) => [primaryKey({ columns: [t.userId, t.achievementKey] })],313);314315export const missions = pgTable("missions", {316 key: varchar({ length: 48 }).primaryKey(),317 name: varchar({ length: 80 }).notNull(),318 description: text().notNull(),319 period: varchar({ length: 8 }).notNull(), // daily | weekly320 metric: varchar({ length: 32 }).notNull(), // spins | wins | bonuses | games | multiplier | wagered321 target: bigint({ mode: "number" }).notNull(),322 rewardCredits: integer().notNull().default(0),323 rewardXp: integer().notNull().default(0),324 enabled: boolean().notNull().default(true),325 sortOrder: integer().notNull().default(0),326});327328export const userMissions = pgTable(329 "user_missions",330 {331 id: uuid().primaryKey().defaultRandom(),332 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),333 missionKey: varchar({ length: 48 }).notNull().references(() => missions.key, { onDelete: "cascade" }),334 periodKey: varchar({ length: 16 }).notNull(), // 2026-09-07 or 2026-W36335 progress: bigint({ mode: "number" }).notNull().default(0),336 /** For "games" metric: distinct game slugs played this period. */337 progressSet: text().array().notNull().default(sql`'{}'::text[]`),338 completedAt: ts(),339 claimedAt: ts(),340 expiresAt: ts().notNull(),341 },342 (t) => [uniqueIndex("user_missions_unique_idx").on(t.userId, t.missionKey, t.periodKey), index("user_missions_user_idx").on(t.userId)],343);344345export const dailyRewards = pgTable("daily_rewards", {346 userId: uuid().primaryKey().references(() => users.id, { onDelete: "cascade" }),347 streakDay: integer().notNull().default(0),348 lastClaimedAt: ts(),349 nextAvailableAt: ts(),350 totalClaimed: bigint({ mode: "number" }).notNull().default(0),351 claims: integer().notNull().default(0),352});353354export const playerLevels = pgTable("player_levels", {355 level: integer().primaryKey(),356 xpRequired: bigint({ mode: "number" }).notNull(),357 xpCumulative: bigint({ mode: "number" }).notNull(),358 rewardCredits: integer().notNull(),359 title: varchar({ length: 48 }).notNull(),360});361362export const leaderboards = pgTable(363 "leaderboards",364 {365 id: uuid().primaryKey().defaultRandom(),366 category: varchar({ length: 32 }).notNull(), // biggest_win | biggest_multiplier | most_spins | highest_level367 periodKey: varchar({ length: 16 }).notNull(), // 2026-09-07 | 2026-W36 | all368 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),369 value: numeric({ precision: 18, scale: 2 }).notNull(),370 gameSlug: varchar({ length: 64 }),371 roundId: varchar({ length: 32 }),372 updatedAt: now(),373 },374 (t) => [uniqueIndex("leaderboards_unique_idx").on(t.category, t.periodKey, t.userId), index("leaderboards_rank_idx").on(t.category, t.periodKey, t.value)],375);376377/* ---------------------------------------------------------------- security */378379export const securityEvents = pgTable(380 "security_events",381 {382 id: uuid().primaryKey().defaultRandom(),383 userId: uuid().references(() => users.id, { onDelete: "set null" }),384 adminId: uuid(),385 type: varchar({ length: 48 }).notNull(),386 severity: varchar({ length: 8 }).notNull().default("info"), // info | warn | high387 ip: varchar({ length: 64 }),388 userAgent: text(),389 meta: jsonb().$type<Record<string, unknown>>(),390 createdAt: now(),391 },392 (t) => [index("security_events_created_idx").on(t.createdAt), index("security_events_type_idx").on(t.type), index("security_events_user_idx").on(t.userId)],393);394395export const adminUsers = pgTable(396 "admin_users",397 {398 id: uuid().primaryKey().defaultRandom(),399 username: varchar({ length: 32 }).notNull(),400 passwordHash: text().notNull(),401 totpSecret: text().notNull(), // base32, encrypted at rest with SESSION_SECRET402 role: varchar({ length: 16 }).notNull().default("admin"),403 createdAt: now(),404 lastLoginAt: ts(),405 disabled: boolean().notNull().default(false),406 },407 (t) => [uniqueIndex("admin_users_username_idx").on(t.username)],408);409410export const adminSessions = pgTable(411 "admin_sessions",412 {413 id: uuid().primaryKey().defaultRandom(),414 adminId: uuid().notNull().references(() => adminUsers.id, { onDelete: "cascade" }),415 tokenHash: text().notNull(),416 createdAt: now(),417 expiresAt: ts().notNull(),418 ip: varchar({ length: 64 }),419 },420 (t) => [uniqueIndex("admin_sessions_token_idx").on(t.tokenHash)],421);422423/* ------------------------------------------------------------ platform */424425export const featureFlags = pgTable("feature_flags", {426 key: varchar({ length: 64 }).primaryKey(),427 enabled: boolean().notNull().default(true),428 description: text(),429 updatedAt: now(),430});431432export const platformSettings = pgTable("platform_settings", {433 key: varchar({ length: 64 }).primaryKey(),434 value: jsonb().$type<unknown>().notNull(),435 updatedAt: now(),436});437438export const simulationRuns = pgTable(439 "simulation_runs",440 {441 id: uuid().primaryKey().defaultRandom(),442 gameSlug: varchar({ length: 64 }).notNull(),443 gameVersion: varchar({ length: 16 }).notNull(),444 spins: bigint({ mode: "number" }).notNull(),445 status: varchar({ length: 16 }).notNull().default("running"), // running | done | failed446 progress: bigint({ mode: "number" }).notNull().default(0),447 result: jsonb().$type<Record<string, unknown>>(),448 error: text(),449 requestedBy: uuid(),450 createdAt: now(),451 finishedAt: ts(),452 },453 (t) => [index("simulation_runs_game_idx").on(t.gameSlug, t.createdAt)],454);455