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);177178export const gameStatistics = pgTable("game_statistics", {179 gameId: uuid().primaryKey().references(() => games.id, { onDelete: "cascade" }),180 launches: bigint({ mode: "number" }).notNull().default(0),181 spins: bigint({ mode: "number" }).notNull().default(0),182 wagered: bigint({ mode: "number" }).notNull().default(0),183 won: bigint({ mode: "number" }).notNull().default(0),184 wins: bigint({ mode: "number" }).notNull().default(0),185 bonuses: bigint({ mode: "number" }).notNull().default(0),186 freeSpins: bigint({ mode: "number" }).notNull().default(0),187 bigWins: bigint({ mode: "number" }).notNull().default(0),188 maxWin: bigint({ mode: "number" }).notNull().default(0),189 maxMultiplier: numeric({ precision: 12, scale: 2 }).notNull().default("0"),190 favorites: integer().notNull().default(0),191 updatedAt: now(),192});193194export const gameStates = pgTable(195 "game_states",196 {197 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),198 gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),199 state: jsonb().$type<Record<string, unknown>>().notNull(),200 updatedAt: now(),201 },202 (t) => [primaryKey({ columns: [t.userId, t.gameId] })],203);204205export const userGameStats = pgTable(206 "user_game_stats",207 {208 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),209 gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),210 spins: bigint({ mode: "number" }).notNull().default(0),211 wagered: bigint({ mode: "number" }).notNull().default(0),212 won: bigint({ mode: "number" }).notNull().default(0),213 bonuses: integer().notNull().default(0),214 biggestWin: bigint({ mode: "number" }).notNull().default(0),215 biggestMultiplier: numeric({ precision: 12, scale: 2 }).notNull().default("0"),216 lastPlayedAt: now(),217 firstPlayedAt: now(),218 },219 (t) => [primaryKey({ columns: [t.userId, t.gameId] }), index("ugs_user_last_idx").on(t.userId, t.lastPlayedAt)],220);221222export const favorites = pgTable(223 "favorites",224 {225 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),226 gameId: uuid().notNull().references(() => games.id, { onDelete: "cascade" }),227 createdAt: now(),228 },229 (t) => [primaryKey({ columns: [t.userId, t.gameId] })],230);231232/* ------------------------------------------------------------ progression */233234export const achievements = pgTable("achievements", {235 key: varchar({ length: 48 }).primaryKey(),236 name: varchar({ length: 80 }).notNull(),237 description: text().notNull(),238 category: varchar({ length: 24 }).notNull(),239 metric: varchar({ length: 32 }).notNull(), // spins | bonuses | games_played | big_wins | multiplier | wins | level | daily_streak240 target: bigint({ mode: "number" }).notNull(),241 rewardCredits: integer().notNull().default(0),242 rewardXp: integer().notNull().default(0),243 icon: varchar({ length: 32 }).notNull().default("star"),244 sortOrder: integer().notNull().default(0),245 enabled: boolean().notNull().default(true),246});247248export const userAchievements = pgTable(249 "user_achievements",250 {251 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),252 achievementKey: varchar({ length: 48 }).notNull().references(() => achievements.key, { onDelete: "cascade" }),253 unlockedAt: now(),254 },255 (t) => [primaryKey({ columns: [t.userId, t.achievementKey] })],256);257258export const missions = pgTable("missions", {259 key: varchar({ length: 48 }).primaryKey(),260 name: varchar({ length: 80 }).notNull(),261 description: text().notNull(),262 period: varchar({ length: 8 }).notNull(), // daily | weekly263 metric: varchar({ length: 32 }).notNull(), // spins | wins | bonuses | games | multiplier | wagered264 target: bigint({ mode: "number" }).notNull(),265 rewardCredits: integer().notNull().default(0),266 rewardXp: integer().notNull().default(0),267 enabled: boolean().notNull().default(true),268 sortOrder: integer().notNull().default(0),269});270271export const userMissions = pgTable(272 "user_missions",273 {274 id: uuid().primaryKey().defaultRandom(),275 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),276 missionKey: varchar({ length: 48 }).notNull().references(() => missions.key, { onDelete: "cascade" }),277 periodKey: varchar({ length: 16 }).notNull(), // 2026-09-07 or 2026-W36278 progress: bigint({ mode: "number" }).notNull().default(0),279 /** For "games" metric: distinct game slugs played this period. */280 progressSet: text().array().notNull().default(sql`'{}'::text[]`),281 completedAt: ts(),282 claimedAt: ts(),283 expiresAt: ts().notNull(),284 },285 (t) => [uniqueIndex("user_missions_unique_idx").on(t.userId, t.missionKey, t.periodKey), index("user_missions_user_idx").on(t.userId)],286);287288export const dailyRewards = pgTable("daily_rewards", {289 userId: uuid().primaryKey().references(() => users.id, { onDelete: "cascade" }),290 streakDay: integer().notNull().default(0),291 lastClaimedAt: ts(),292 nextAvailableAt: ts(),293 totalClaimed: bigint({ mode: "number" }).notNull().default(0),294 claims: integer().notNull().default(0),295});296297export const playerLevels = pgTable("player_levels", {298 level: integer().primaryKey(),299 xpRequired: bigint({ mode: "number" }).notNull(),300 xpCumulative: bigint({ mode: "number" }).notNull(),301 rewardCredits: integer().notNull(),302 title: varchar({ length: 48 }).notNull(),303});304305export const leaderboards = pgTable(306 "leaderboards",307 {308 id: uuid().primaryKey().defaultRandom(),309 category: varchar({ length: 32 }).notNull(), // biggest_win | biggest_multiplier | most_spins | highest_level310 periodKey: varchar({ length: 16 }).notNull(), // 2026-09-07 | 2026-W36 | all311 userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),312 value: numeric({ precision: 18, scale: 2 }).notNull(),313 gameSlug: varchar({ length: 64 }),314 roundId: varchar({ length: 32 }),315 updatedAt: now(),316 },317 (t) => [uniqueIndex("leaderboards_unique_idx").on(t.category, t.periodKey, t.userId), index("leaderboards_rank_idx").on(t.category, t.periodKey, t.value)],318);319320/* ---------------------------------------------------------------- security */321322export const securityEvents = pgTable(323 "security_events",324 {325 id: uuid().primaryKey().defaultRandom(),326 userId: uuid().references(() => users.id, { onDelete: "set null" }),327 adminId: uuid(),328 type: varchar({ length: 48 }).notNull(),329 severity: varchar({ length: 8 }).notNull().default("info"), // info | warn | high330 ip: varchar({ length: 64 }),331 userAgent: text(),332 meta: jsonb().$type<Record<string, unknown>>(),333 createdAt: now(),334 },335 (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)],336);337338export const adminUsers = pgTable(339 "admin_users",340 {341 id: uuid().primaryKey().defaultRandom(),342 username: varchar({ length: 32 }).notNull(),343 passwordHash: text().notNull(),344 totpSecret: text().notNull(), // base32, encrypted at rest with SESSION_SECRET345 role: varchar({ length: 16 }).notNull().default("admin"),346 createdAt: now(),347 lastLoginAt: ts(),348 disabled: boolean().notNull().default(false),349 },350 (t) => [uniqueIndex("admin_users_username_idx").on(t.username)],351);352353export const adminSessions = pgTable(354 "admin_sessions",355 {356 id: uuid().primaryKey().defaultRandom(),357 adminId: uuid().notNull().references(() => adminUsers.id, { onDelete: "cascade" }),358 tokenHash: text().notNull(),359 createdAt: now(),360 expiresAt: ts().notNull(),361 ip: varchar({ length: 64 }),362 },363 (t) => [uniqueIndex("admin_sessions_token_idx").on(t.tokenHash)],364);365366/* ------------------------------------------------------------ platform */367368export const featureFlags = pgTable("feature_flags", {369 key: varchar({ length: 64 }).primaryKey(),370 enabled: boolean().notNull().default(true),371 description: text(),372 updatedAt: now(),373});374375export const platformSettings = pgTable("platform_settings", {376 key: varchar({ length: 64 }).primaryKey(),377 value: jsonb().$type<unknown>().notNull(),378 updatedAt: now(),379});380381export const simulationRuns = pgTable(382 "simulation_runs",383 {384 id: uuid().primaryKey().defaultRandom(),385 gameSlug: varchar({ length: 64 }).notNull(),386 gameVersion: varchar({ length: 16 }).notNull(),387 spins: bigint({ mode: "number" }).notNull(),388 status: varchar({ length: 16 }).notNull().default("running"), // running | done | failed389 progress: bigint({ mode: "number" }).notNull().default(0),390 result: jsonb().$type<Record<string, unknown>>(),391 error: text(),392 requestedBy: uuid(),393 createdAt: now(),394 finishedAt: ts(),395 },396 (t) => [index("simulation_runs_game_idx").on(t.gameSlug, t.createdAt)],397);398