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);208209export const gameStatistics = pgTable("game_statistics", {210 gameId: uuid().primaryKey().references(() => games.id, { onDelete: "cascade" }),211 launches: bigint({ mode: "number" }).notNull().default(0),212 spins: bigint({ mode: "number" }).notNull().default(0),213 wagered: bigint({ mode: "number" }).notNull().default(0),214 won: bigint({ mode: "number" }).notNull().default(0),215 wins: bigint({ mode: "number" }).notNull().default(0),216 bonuses: bigint({ mode: "number" }).notNull().default(0),217 freeSpins: bigint({ mode: "number" }).notNull().default(0),218 bigWins: bigint({ mode: "number" }).notNull().default(0),219 maxWin: bigint({ mode: "number" }).notNull().default(0),220 maxMultiplier: numeric({ precision: 12, scale: 2 }).notNull().default("0"),221 favorites: integer().notNull().default(0),222 updatedAt: now(),223});224225export const gameStates = pgTable(226 "game_states",227 {228 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),229 gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),230 state: jsonb().$type<Record<string, unknown>>().notNull(),231 updatedAt: now(),232 },233 (t) => [primaryKey({ columns: [t.userId, t.gameId] })],234);235236export const userGameStats = pgTable(237 "user_game_stats",238 {239 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),240 gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),241 spins: bigint({ mode: "number" }).notNull().default(0),242 wagered: bigint({ mode: "number" }).notNull().default(0),243 won: bigint({ mode: "number" }).notNull().default(0),244 bonuses: integer().notNull().default(0),245 biggestWin: bigint({ mode: "number" }).notNull().default(0),246 biggestMultiplier: numeric({ precision: 12, scale: 2 }).notNull().default("0"),247 lastPlayedAt: now(),248 firstPlayedAt: now(),249 },250 (t) => [primaryKey({ columns: [t.userId, t.gameId] }), index("ugs_user_last_idx").on(t.userId, t.lastPlayedAt)],251);252253export const favorites = pgTable(254 "favorites",255 {256 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),257 gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),258 createdAt: now(),259 },260 (t) => [primaryKey({ columns: [t.userId, t.gameId] })],261);262263/* ------------------------------------------------------------ progression */264265export const achievements = pgTable("achievements", {266 key: varchar({ length: 48 }).primaryKey(),267 name: varchar({ length: 80 }).notNull(),268 description: text().notNull(),269 category: varchar({ length: 24 }).notNull(),270 metric: varchar({ length: 32 }).notNull(), // spins | bonuses | games_played | big_wins | multiplier | wins | level | daily_streak271 target: bigint({ mode: "number" }).notNull(),272 rewardCredits: integer().notNull().default(0),273 rewardXp: integer().notNull().default(0),274 icon: varchar({ length: 32 }).notNull().default("star"),275 sortOrder: integer().notNull().default(0),276 enabled: boolean().notNull().default(true),277});278279export const userAchievements = pgTable(280 "user_achievements",281 {282 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),283 achievementKey: varchar({ length: 48 }).notNull().references(() => achievements.key, { onDelete: "cascade" }),284 unlockedAt: now(),285 },286 (t) => [primaryKey({ columns: [t.userId, t.achievementKey] })],287);288289export const missions = pgTable("missions", {290 key: varchar({ length: 48 }).primaryKey(),291 name: varchar({ length: 80 }).notNull(),292 description: text().notNull(),293 period: varchar({ length: 8 }).notNull(), // daily | weekly294 metric: varchar({ length: 32 }).notNull(), // spins | wins | bonuses | games | multiplier | wagered295 target: bigint({ mode: "number" }).notNull(),296 rewardCredits: integer().notNull().default(0),297 rewardXp: integer().notNull().default(0),298 enabled: boolean().notNull().default(true),299 sortOrder: integer().notNull().default(0),300});301302export const userMissions = pgTable(303 "user_missions",304 {305 id: uuid().primaryKey().defaultRandom(),306 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),307 missionKey: varchar({ length: 48 }).notNull().references(() => missions.key, { onDelete: "cascade" }),308 periodKey: varchar({ length: 16 }).notNull(), // 2026-09-07 or 2026-W36309 progress: bigint({ mode: "number" }).notNull().default(0),310 /** For "games" metric: distinct game slugs played this period. */311 progressSet: text().array().notNull().default(sql`'{}'::text[]`),312 completedAt: ts(),313 claimedAt: ts(),314 expiresAt: ts().notNull(),315 },316 (t) => [uniqueIndex("user_missions_unique_idx").on(t.userId, t.missionKey, t.periodKey), index("user_missions_user_idx").on(t.userId)],317);318319export const dailyRewards = pgTable("daily_rewards", {320 userId: uuid().primaryKey().references(() => users.id, { onDelete: "cascade" }),321 streakDay: integer().notNull().default(0),322 lastClaimedAt: ts(),323 nextAvailableAt: ts(),324 totalClaimed: bigint({ mode: "number" }).notNull().default(0),325 claims: integer().notNull().default(0),326});327328export const playerLevels = pgTable("player_levels", {329 level: integer().primaryKey(),330 xpRequired: bigint({ mode: "number" }).notNull(),331 xpCumulative: bigint({ mode: "number" }).notNull(),332 rewardCredits: integer().notNull(),333 title: varchar({ length: 48 }).notNull(),334});335336export const leaderboards = pgTable(337 "leaderboards",338 {339 id: uuid().primaryKey().defaultRandom(),340 category: varchar({ length: 32 }).notNull(), // biggest_win | biggest_multiplier | most_spins | highest_level341 periodKey: varchar({ length: 16 }).notNull(), // 2026-09-07 | 2026-W36 | all342 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),343 value: numeric({ precision: 18, scale: 2 }).notNull(),344 gameSlug: varchar({ length: 64 }),345 roundId: varchar({ length: 32 }),346 updatedAt: now(),347 },348 (t) => [uniqueIndex("leaderboards_unique_idx").on(t.category, t.periodKey, t.userId), index("leaderboards_rank_idx").on(t.category, t.periodKey, t.value)],349);350351/* ---------------------------------------------------------------- security */352353export const securityEvents = pgTable(354 "security_events",355 {356 id: uuid().primaryKey().defaultRandom(),357 userId: uuid().references(() => users.id, { onDelete: "set null" }),358 adminId: uuid(),359 type: varchar({ length: 48 }).notNull(),360 severity: varchar({ length: 8 }).notNull().default("info"), // info | warn | high361 ip: varchar({ length: 64 }),362 userAgent: text(),363 meta: jsonb().$type<Record<string, unknown>>(),364 createdAt: now(),365 },366 (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)],367);368369export const adminUsers = pgTable(370 "admin_users",371 {372 id: uuid().primaryKey().defaultRandom(),373 username: varchar({ length: 32 }).notNull(),374 passwordHash: text().notNull(),375 totpSecret: text().notNull(), // base32, encrypted at rest with SESSION_SECRET376 role: varchar({ length: 16 }).notNull().default("admin"),377 createdAt: now(),378 lastLoginAt: ts(),379 disabled: boolean().notNull().default(false),380 },381 (t) => [uniqueIndex("admin_users_username_idx").on(t.username)],382);383384export const adminSessions = pgTable(385 "admin_sessions",386 {387 id: uuid().primaryKey().defaultRandom(),388 adminId: uuid().notNull().references(() => adminUsers.id, { onDelete: "cascade" }),389 tokenHash: text().notNull(),390 createdAt: now(),391 expiresAt: ts().notNull(),392 ip: varchar({ length: 64 }),393 },394 (t) => [uniqueIndex("admin_sessions_token_idx").on(t.tokenHash)],395);396397/* ------------------------------------------------------------ platform */398399export const featureFlags = pgTable("feature_flags", {400 key: varchar({ length: 64 }).primaryKey(),401 enabled: boolean().notNull().default(true),402 description: text(),403 updatedAt: now(),404});405406export const platformSettings = pgTable("platform_settings", {407 key: varchar({ length: 64 }).primaryKey(),408 value: jsonb().$type<unknown>().notNull(),409 updatedAt: now(),410});411412export const simulationRuns = pgTable(413 "simulation_runs",414 {415 id: uuid().primaryKey().defaultRandom(),416 gameSlug: varchar({ length: 64 }).notNull(),417 gameVersion: varchar({ length: 16 }).notNull(),418 spins: bigint({ mode: "number" }).notNull(),419 status: varchar({ length: 16 }).notNull().default("running"), // running | done | failed420 progress: bigint({ mode: "number" }).notNull().default(0),421 result: jsonb().$type<Record<string, unknown>>(),422 error: text(),423 requestedBy: uuid(),424 createdAt: now(),425 finishedAt: ts(),426 },427 (t) => [index("simulation_runs_game_idx").on(t.gameSlug, t.createdAt)],428);429