SQL Cookbook

Справочник SQL-команд для работы с БД goLoot через Beekeeper Studio.

Для тестирования: Диагностика состояния после действий во фронтенде + сброс данных для повторного тестирования.

Для production: Read-only аналитика и диагностика проблем.

Конвенции

Элемент	Описание
`YOUR_TELEGRAM_ID`	Заменить на свой telegramId перед выполнением
`"camelCase"`	camelCase колонки PostgreSQL требуют двойных кавычек
🟢	SELECT — безопасно, только чтение
🟡	DELETE / UPDATE — изменяет данные конкретного пользователя
🔴	CASCADE / BULK — массовые или каскадные операции

Перед выполнением 🟡 и 🔴

Всегда сначала выполни SELECT-версию команды, чтобы убедиться что затрагиваешь правильного пользователя.

0. Подготовка

-- 🟢 Найти userId по telegramId (нужен для всех остальных запросов)
SELECT id, "telegramId", "firstName", username, "steamId", "createdAt"
FROM users
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

1. Диагностика (SELECT)

1.1 Профиль пользователя

-- 🟢 Основные балансы и активность
SELECT
  id, "firstName", username, level, xp,
  scrap, "scrapTotalEarned", "scrapSpent",
  "passiveIncomeBalance",
  "streakPoints", "streakPointsTotal",
  "dailyLoginStreak", "bestDailyLoginStreak",
  "quizzesCompleted", "correctAnswers", "incorrectAnswers",
  "questsCompleted", "achievementsUnlocked", "friendsInvited",
  "casesOpened", "dailyCasesOpened", "dailySpinsUsed",
  "itemsCrafted", "itemsSalvaged",
  "lastDailyCase", "lastDailySpin", "lastLoginDate",
  "botStatus", "isBanned", "steamId", "steamVerified",
  "createdAt"
FROM users
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

-- 🟢 Детализация доходов Scrap (откуда заработано)
SELECT
  "scrapFromQuizzes", "scrapFromQuests", "scrapFromAchievements",
  "scrapFromReferrals", "scrapFromCases", "scrapFromSpins",
  "scrapSpentOnCases", "scrapSpentOnCraft"
FROM users
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

-- 🟢 Детализация доходов XP
SELECT
  "xpFromQuests", "xpFromAchievements", "xpFromStreaks",
  "xpFromReferrals", "xpFromCases", "xpFromSpins",
  "xpFromSalvage", "xpFromAdmin"
FROM users
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

1.2 Квизы

-- 🟢 Последние 20 результатов квизов
SELECT
  qr.id, qr.category, qr.subcategory, qr.slug,
  qr.score, qr."totalPoints", qr.percentage,
  qr."correctAnswers", qr."totalQuestions",
  qr."earnedScrap", qr."timeSpent",
  qr."completedAt"
FROM quiz_results qr
JOIN users u ON qr."userId" = u.id
WHERE u."telegramId" = 'YOUR_TELEGRAM_ID'
ORDER BY qr."completedAt" DESC
LIMIT 20;

-- 🟢 Статистика по категориям квизов
SELECT
  qr.category,
  COUNT(*) as total_quizzes,
  ROUND(AVG(qr.percentage)) as avg_percentage,
  SUM(qr."earnedScrap") as total_scrap
FROM quiz_results qr
JOIN users u ON qr."userId" = u.id
WHERE u."telegramId" = 'YOUR_TELEGRAM_ID'
GROUP BY qr.category
ORDER BY total_quizzes DESC;

-- 🟢 Стрики по категориям
SELECT
  category, "currentStreak", "bestStreak",
  "totalCorrect", "totalAnswers", "lastQuizDate"
FROM user_category_streaks
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

1.3 Кейсы и спины

-- 🟢 Последние 20 открытий кейсов
SELECT
  co.id, c.name as case_name,
  co."rewardType", co."rewardAmount",
  co."rewardSnapshot",
  co."paidScrap", co."openedAt"
FROM case_openings co
JOIN cases c ON co."caseId" = c.id
JOIN users u ON co."userId" = u.id
WHERE u."telegramId" = 'YOUR_TELEGRAM_ID'
ORDER BY co."openedAt" DESC
LIMIT 20;

-- 🟢 Статистика открытий по кейсам
SELECT
  c.name as case_name, ucs."totalOpened",
  ucs."totalScrapSpent", ucs."lastOpenedAt"
FROM user_case_stats ucs
JOIN cases c ON ucs."caseId" = c.id
WHERE ucs."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY ucs."totalOpened" DESC;

-- 🟢 Последние 20 спинов рулетки
SELECT
  sr.id, ds.name as spin_name,
  sr."rewardType", sr."rewardAmount",
  sr."rewardSnapshot", sr."spunAt"
FROM spin_results sr
JOIN daily_spins ds ON sr."spinId" = ds.id
JOIN users u ON sr."userId" = u.id
WHERE u."telegramId" = 'YOUR_TELEGRAM_ID'
ORDER BY sr."spunAt" DESC
LIMIT 20;

-- 🟢 Бесплатные открытия кейсов (из промокодов/квестов)
SELECT ufco."caseId", c.name, ufco.count
FROM user_free_case_opens ufco
JOIN cases c ON ufco."caseId" = c.id
WHERE ufco."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

1.4 Квесты

-- 🟢 Все квесты пользователя с прогрессом
SELECT
  uq.id, q.title, q.type, q.category,
  uq."currentProgress", q."targetProgress",
  uq.status, uq."startedAt", uq."completedAt", uq."claimedAt",
  uq."rewardSnapshot"
FROM user_quests uq
JOIN quests q ON uq."questId" = q.id
JOIN users u ON uq."userId" = u.id
WHERE u."telegramId" = 'YOUR_TELEGRAM_ID'
ORDER BY
  CASE uq.status
    WHEN 'IN_PROGRESS' THEN 1
    WHEN 'COMPLETED' THEN 2
    WHEN 'CLAIMED' THEN 3
    WHEN 'EXPIRED' THEN 4
  END,
  uq."startedAt" DESC;

-- 🟢 Только активные (незавершённые) квесты
SELECT
  q.title, q.type, q.category,
  uq."currentProgress" || '/' || q."targetProgress" as progress,
  uq.status
FROM user_quests uq
JOIN quests q ON uq."questId" = q.id
WHERE uq."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
  AND uq.status = 'IN_PROGRESS';

-- 🟢 Rust-квесты с детальным прогрессом
SELECT
  q.title, q."rustEventType",
  uq."rustMinutesPlayed", uq."rustResourceAmount",
  uq."rustLootCount", uq."rustKillCount",
  uq."rustCraftAmount", uq."rustFishAmount",
  uq."rustRecycleAmount", uq."rustExplosiveAmount",
  uq."rustSkillUpgradeAmount", uq."rustTeaAmount",
  uq."rustHarvestAmount", uq."rustPieAmount",
  uq.status, uq."rustLastUpdateAt"
FROM user_quests uq
JOIN quests q ON uq."questId" = q.id
WHERE uq."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
  AND q."rustEventType" IS NOT NULL;

1.5 Инвентарь и крафт

-- 🟢 Инвентарь пользователя
SELECT
  ui.id, i.name, i."itemType", i.tier, i.category,
  ui.quantity, ui."sourceType", ui."acquiredAt",
  i."marketHashName"
FROM user_inventory ui
JOIN items i ON ui."itemId" = i.id
WHERE ui."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY ui."acquiredAt" DESC;

-- 🟢 История крафтов
SELECT
  ch.id, i.name, i.tier,
  ch."spentScrap", ch."materialsUsed", ch."gainedXP",
  ch."craftedAt"
FROM craft_history ch
JOIN items i ON ch."itemId" = i.id
WHERE ch."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY ch."craftedAt" DESC
LIMIT 20;

-- 🟢 История разборок (salvage)
SELECT
  sh.id, i.name, sh.quantity, sh."xpGained",
  sh."itemSnapshot", sh."createdAt"
FROM salvage_history sh
JOIN items i ON sh."itemId" = i.id
WHERE sh."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY sh."createdAt" DESC
LIMIT 20;

-- 🟢 История выводов
SELECT
  w.id, i.name, i.tier,
  w.status, w."tradeOfferId",
  w."failureReason", w."requestedAt", w."completedAt"
FROM withdrawals w
JOIN items i ON w."itemId" = i.id
WHERE w."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY w."requestedAt" DESC;

1.6 Достижения

-- 🟢 Все достижения пользователя
SELECT
  a.title, a.category, a.difficulty,
  ua."currentProgress", a."targetProgress",
  ua.status, ua."unlockedAt", ua."completedAt", ua."claimedAt"
FROM user_achievements ua
JOIN achievements a ON ua."achievementId" = a.id
WHERE ua."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY
  CASE ua.status
    WHEN 'IN_PROGRESS' THEN 1
    WHEN 'COMPLETED' THEN 2
    WHEN 'CLAIMED' THEN 3
    WHEN 'LOCKED' THEN 4
  END;

-- 🟢 Сводка достижений по статусам
SELECT ua.status, COUNT(*) as count
FROM user_achievements ua
WHERE ua."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
GROUP BY ua.status;

1.7 Рефералы и пассивный доход

-- 🟢 Реферальный код пользователя
SELECT rc.code, rc."clicksCount", rc."isActive"
FROM referral_codes rc
JOIN users u ON rc."userId" = u.id
WHERE u."telegramId" = 'YOUR_TELEGRAM_ID';

-- 🟢 Список приглашённых друзей
SELECT
  r.id, u_ref."firstName" as referred_name,
  r."bonusXpClaimed", r."bonusScrapClaimed",
  r."passiveScrapEarned", r."createdAt"
FROM referrals r
JOIN users u_ref ON r."referredId" = u_ref.id
WHERE r."referrerId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY r."createdAt" DESC;

-- 🟢 Кто пригласил тебя
SELECT
  u_ref."firstName" as referrer_name,
  r."passiveScrapEarned", r."createdAt"
FROM referrals r
JOIN users u_ref ON r."referrerId" = u_ref.id
WHERE r."referredId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

1.8 Сезон и рейтинг

-- 🟢 Текущий активный сезон
SELECT id, number, name, status, "startDate", "endDate"
FROM seasons
WHERE status = 'ACTIVE'
LIMIT 1;

-- 🟢 Сезонная статистика пользователя
SELECT
  s.name as season_name, uss.level, uss.xp,
  uss.scrap, uss."scrapTotalEarned",
  uss."quizzesCompleted", uss."casesOpened",
  uss."tasksCompleted", uss."achievementsUnlocked",
  uss."friendsInvited", uss."itemsCrafted",
  uss.rank, uss."rankUpdAt"
FROM user_season_stats uss
JOIN seasons s ON uss."seasonId" = s.id
WHERE uss."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY s.number DESC;

1.9 Баффы

-- 🟢 Активные баффы
SELECT
  uab.id, uab."buffType", uab.multiplier,
  uab."activatedAt", uab."expiresAt", uab."usesLeft",
  i.name as source_item
FROM user_active_buffs uab
LEFT JOIN items i ON uab."sourceItemId" = i.id
WHERE uab."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY uab."activatedAt" DESC;

-- 🟢 История событий баффов (последние 20)
SELECT
  be."buffType", be."eventType",
  be.multiplier, be."baseAmount", be."bonusAmount",
  be."sourceType", be."daysProtected",
  be."createdAt"
FROM buff_events be
WHERE be."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY be."createdAt" DESC
LIMIT 20;

1.10 Промокоды

-- 🟢 Использованные промокоды
SELECT
  pc.code, pc."rewardType", pc."rewardAmount",
  pcr."rewardSnapshot", pcr."redeemedAt"
FROM promo_code_redemptions pcr
JOIN promo_codes pc ON pcr."promoCodeId" = pc.id
WHERE pcr."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY pcr."redeemedAt" DESC;

1.11 Streak Points и розыгрыши

-- 🟢 Последние транзакции Streak Points
SELECT
  spt.type, spt.amount, spt.balance,
  spt.description, spt."referenceType",
  spt."createdAt"
FROM streak_points_transactions spt
WHERE spt."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY spt."createdAt" DESC
LIMIT 20;

-- 🟢 Билеты розыгрышей
SELECT
  r."prizeTitle", r.status,
  rt."ticketNumber", rt."pricePaid",
  r."startsAt", r."endsAt",
  rt."createdAt"
FROM raffle_tickets rt
JOIN raffles r ON rt."raffleId" = r.id
WHERE rt."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY rt."createdAt" DESC;

1.12 Экономика и бюджет

-- 🟢 Текущий бюджетный период
SELECT
  bp.id, bp."monthNumber", bp."periodNumber",
  bp."baseBudgetRub", bp."carriedOverRub", bp."spentRub",
  ROUND(bp."baseBudgetRub" + bp."carriedOverRub" - bp."spentRub", 2) as remaining_rub,
  bp."startDate", bp."endDate", bp."isActive"
FROM budget_periods bp
WHERE bp."isActive" = true
LIMIT 1;

-- 🟢 Luck Pool статус пользователя
SELECT
  lpe."activePeriods", lpe."boostMultiplier",
  lpe."isActive", lpe."enteredAt", lpe."lastActiveAt",
  lpe."lastCraftAt", lpe."canReenterAfter",
  lpe."blockedSkinIds"
FROM luck_pool_entries lpe
WHERE lpe."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

2. Сброс для тестирования

Только для dev/staging!

Все команды ниже изменяют данные. Убедись что работаешь с правильным окружением.

2.1 Сброс квизов

-- 🟡 Удалить результаты квизов
DELETE FROM quiz_results
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Сбросить стрики по категориям
DELETE FROM user_category_streaks
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Обнулить счётчики квизов в профиле
UPDATE users SET
  "quizzesCompleted" = 0,
  "correctAnswers" = 0,
  "incorrectAnswers" = 0,
  "correctAnswersStreak" = 0,
  "bestCorrectAnswersStreak" = 0,
  "diverseCategoryStreak" = 0,
  "bestDiverseCategoryStreak" = 0,
  "scrapFromQuizzes" = 0
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

2.2 Сброс квестов

-- 🟡 Удалить прогресс квестов
DELETE FROM user_quests
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Удалить исключения квестов (для isUnique)
DELETE FROM user_quest_exclusions
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

-- 🟡 Удалить claimed unique quests (для повторного прохождения)
DELETE FROM claimed_unique_quests
WHERE telegram_id = 'YOUR_TELEGRAM_ID';

-- 🟡 Удалить бесплатные открытия кейсов (награды квестов)
DELETE FROM user_free_case_opens
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Обнулить счётчики квестов в профиле
UPDATE users SET
  "questsCompleted" = 0,
  "scrapFromQuests" = 0,
  "xpFromQuests" = 0
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

2.3 Сброс кейсов и спинов

-- 🟡 Удалить историю открытий кейсов
DELETE FROM case_openings
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Удалить статистику кейсов
DELETE FROM user_case_stats
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Удалить историю спинов
DELETE FROM spin_results
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Обнулить счётчики кейсов/спинов + сбросить кулдауны
UPDATE users SET
  "casesOpened" = 0,
  "dailyCasesOpened" = 0,
  "dailySpinsUsed" = 0,
  "lastDailyCase" = NULL,
  "lastDailySpin" = NULL,
  "scrapFromCases" = 0,
  "scrapFromSpins" = 0,
  "scrapSpentOnCases" = 0,
  "xpFromCases" = 0,
  "xpFromSpins" = 0
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

2.4 Сброс инвентаря и крафта

-- 🟡 Удалить инвентарь
DELETE FROM user_inventory
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Удалить историю крафтов
DELETE FROM craft_history
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Удалить историю разборок (salvage)
DELETE FROM salvage_history
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Обнулить счётчики крафта/разборки
UPDATE users SET
  "itemsCrafted" = 0,
  "itemsSalvaged" = 0,
  "scrapSpentOnCraft" = 0,
  "xpFromSalvage" = 0
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

2.5 Сброс достижений

-- 🟡 Удалить достижения
DELETE FROM user_achievements
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Удалить claimed unique rewards (для повторного получения isUnique)
DELETE FROM claimed_unique_rewards
WHERE telegram_id = 'YOUR_TELEGRAM_ID';

-- 🟡 Обнулить счётчик достижений
UPDATE users SET
  "achievementsUnlocked" = 0,
  "scrapFromAchievements" = 0,
  "xpFromAchievements" = 0
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

2.6 Сброс рефералов

-- 🟡 Удалить рефералов (приглашённых)
DELETE FROM referrals
WHERE "referrerId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Обнулить пассивный доход и счётчик рефералов
UPDATE users SET
  "friendsInvited" = 0,
  "passiveIncomeBalance" = 0,
  "scrapFromReferrals" = 0,
  "xpFromReferrals" = 0
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

2.7 Сброс сезонной статистики

-- 🟡 Удалить сезонную статистику
DELETE FROM user_season_stats
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

2.8 Сброс стриков и Streak Points

-- 🟡 Обнулить стрики и Streak Points
UPDATE users SET
  "dailyLoginStreak" = 0,
  "bestDailyLoginStreak" = 0,
  "lastActivityDate" = NULL,
  "streakPoints" = 0,
  "streakPointsTotal" = 0,
  "streakPointsSpent" = 0,
  "lastStreakPointsClaim" = NULL,
  "xpFromStreaks" = 0
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

-- 🟡 Удалить транзакции Streak Points
DELETE FROM streak_points_transactions
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

2.9 Сброс промокодов

-- 🟡 Удалить использования промокодов
DELETE FROM promo_code_redemptions
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

2.10 Сброс баффов

-- 🟡 Удалить активные баффы
DELETE FROM user_active_buffs
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Удалить историю событий баффов
DELETE FROM buff_events
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

2.11 Сброс Luck Pool и бюджета

-- 🟡 Удалить записи Luck Pool
DELETE FROM luck_pool_entries
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Удалить логи крафтов для бюджета
DELETE FROM craft_budget_logs
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

2.12 Выводы

-- 🟡 Отменить незавершённые выводы
UPDATE withdrawals SET
  status = 'CANCELLED',
  "failureReason" = 'Отменено для тестирования'
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
  AND status IN ('PENDING', 'PROCESSING', 'SENT');

2.13 Выдать себе ресурсы

-- 🟡 Выдать 10000 Scrap
UPDATE users SET
  scrap = scrap + 10000,
  "scrapTotalEarned" = "scrapTotalEarned" + 10000
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

-- 🟡 Выдать 5000 XP
UPDATE users SET
  xp = xp + 5000
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

-- 🟡 Выдать 500 Streak Points
UPDATE users SET
  "streakPoints" = "streakPoints" + 500,
  "streakPointsTotal" = "streakPointsTotal" + 500
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

-- 🟡 Сбросить кулдаун ежедневного кейса
UPDATE users SET "lastDailyCase" = NULL
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

-- 🟡 Сбросить кулдаун ежедневного спина
UPDATE users SET "lastDailySpin" = NULL
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

2.14 Полный сброс данных (без удаления аккаунта)

Удаляет ВСЕ данные пользователя, кроме самого профиля

Аккаунт останется, но все балансы, история и прогресс будут обнулены.

-- 🔴 ПОЛНЫЙ СБРОС (выполнять все команды последовательно)

-- 1) Отменяем выводы
UPDATE withdrawals SET status = 'CANCELLED', "failureReason" = 'Full reset'
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
  AND status IN ('PENDING', 'PROCESSING', 'SENT');

-- 2) Удаляем данные из дочерних таблиц
DELETE FROM user_inventory WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM case_openings WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM user_case_stats WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM quiz_results WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM user_category_streaks WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM spin_results WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM craft_history WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM salvage_history WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM user_achievements WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM user_season_stats WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM feed_events WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM user_bot_interactions WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM user_quests WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM user_free_case_opens WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM user_active_buffs WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM buff_events WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM streak_points_transactions WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM luck_pool_entries WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM craft_budget_logs WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM raffle_tickets WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');
DELETE FROM promo_code_redemptions WHERE "telegramId" = 'YOUR_TELEGRAM_ID';
DELETE FROM user_quest_exclusions WHERE "telegramId" = 'YOUR_TELEGRAM_ID';
DELETE FROM claimed_unique_quests WHERE telegram_id = 'YOUR_TELEGRAM_ID';
DELETE FROM claimed_unique_rewards WHERE telegram_id = 'YOUR_TELEGRAM_ID';

-- 3) Обнуляем все счётчики пользователя
UPDATE users SET
  scrap = 0, "scrapTotalEarned" = 0, xp = 0, level = 1,
  "scrapFromQuizzes" = 0, "scrapFromQuests" = 0, "scrapFromAchievements" = 0,
  "scrapFromReferrals" = 0, "scrapFromCases" = 0, "scrapFromSpins" = 0,
  "scrapSpent" = 0, "scrapSpentOnCases" = 0, "scrapSpentOnCraft" = 0,
  "xpFromQuests" = 0, "xpFromAchievements" = 0, "xpFromStreaks" = 0,
  "xpFromReferrals" = 0, "xpFromCases" = 0, "xpFromSpins" = 0,
  "xpFromSalvage" = 0, "xpFromAdmin" = 0,
  "quizzesCompleted" = 0, "correctAnswers" = 0, "incorrectAnswers" = 0,
  "correctAnswersStreak" = 0, "bestCorrectAnswersStreak" = 0,
  "diverseCategoryStreak" = 0, "bestDiverseCategoryStreak" = 0,
  "dailyLoginStreak" = 0, "bestDailyLoginStreak" = 0,
  "questsCompleted" = 0, "achievementsUnlocked" = 0,
  "casesOpened" = 0, "dailyCasesOpened" = 0, "dailySpinsUsed" = 0,
  "itemsCrafted" = 0, "itemsSalvaged" = 0,
  "passiveIncomeBalance" = 0,
  "streakPoints" = 0, "streakPointsTotal" = 0, "streakPointsSpent" = 0,
  "lastDailyCase" = NULL, "lastDailySpin" = NULL,
  "lastStreakPointsClaim" = NULL, "lastActivityDate" = NULL
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

2.15 Полное каскадное удаление пользователя

Полностью удаляет пользователя и все связанные данные

Восстановить невозможно. Используй только для тестовых аккаунтов.

-- 🔴 ПОЛНОЕ КАСКАДНОЕ УДАЛЕНИЕ ПОЛЬЗОВАТЕЛЯ
-- Заменить 'YOUR_TELEGRAM_ID' на реальный и выполнить

DO $$
DECLARE
  v_user_id TEXT;
  v_telegram_id TEXT := 'YOUR_TELEGRAM_ID';  -- ЗАМЕНИТЬ
  v_steam_id TEXT;
BEGIN
  -- Найти пользователя
  SELECT id, "steamId"
  INTO v_user_id, v_steam_id
  FROM users
  WHERE "telegramId" = v_telegram_id;

  IF v_user_id IS NULL THEN
    RAISE NOTICE 'User with telegramId=% not found', v_telegram_id;
    RETURN;
  END IF;

  RAISE NOTICE 'Deleting user: id=%, telegramId=%, steamId=%',
    v_user_id, v_telegram_id, COALESCE(v_steam_id, 'null');

  -- 1) Таблицы с required userId БЕЗ каскада — удалить вручную
  DELETE FROM rust_player_sessions WHERE "userId" = v_user_id;

  -- 2) Таблицы с nullable userId БЕЗ каскада — обнулить
  UPDATE banner_analytics SET "user_id" = NULL WHERE "user_id" = v_user_id;
  UPDATE push_analytics SET "user_id" = NULL WHERE "user_id" = v_user_id;
  UPDATE rust_webhook_logs SET "userId" = NULL WHERE "userId" = v_user_id;
  UPDATE raffles SET "winnerId" = NULL WHERE "winnerId" = v_user_id;

  -- 3) Self-referencing FK (чтобы другие users не блокировали удаление)
  UPDATE users SET "referredById" = NULL WHERE "referredById" = v_user_id;

  -- 4) Таблицы по telegramId (не FK, нужна ручная очистка)
  DELETE FROM user_quest_exclusions WHERE "telegramId" = v_telegram_id;
  DELETE FROM claimed_unique_rewards WHERE "telegram_id" = v_telegram_id;
  DELETE FROM claimed_unique_quests WHERE "telegram_id" = v_telegram_id;

  -- 5) Plain string reference (не FK)
  DELETE FROM banned_steam_ids WHERE "originalUserId" = v_user_id;

  -- 6) Удалить пользователя — CASCADE удалит ~30 связанных таблиц автоматически
  DELETE FROM users WHERE id = v_user_id;

  RAISE NOTICE 'User % deleted successfully', v_user_id;
END $$;

3. Production-диагностика (READ-ONLY)

3.1 Общая статистика

-- 🟢 Пользователи: общие цифры
SELECT
  COUNT(*) as total,
  COUNT(CASE WHEN "isActive" = true AND "isBanned" = false THEN 1 END) as active,
  COUNT(CASE WHEN "isBanned" = true THEN 1 END) as banned,
  COUNT(CASE WHEN "deletedAt" IS NOT NULL THEN 1 END) as deleted,
  COUNT(CASE WHEN "createdAt" > NOW() - INTERVAL '7 days' THEN 1 END) as new_7d,
  COUNT(CASE WHEN "lastLoginDate" > NOW() - INTERVAL '24 hours' THEN 1 END) as dau
FROM users;

-- 🟢 Активный сезон: масштаб участия
SELECT
  s.name, s.status,
  s."startDate", s."endDate",
  COUNT(DISTINCT uss."userId") as participants,
  MAX(uss.xp) as top_xp,
  ROUND(AVG(uss.xp)) as avg_xp
FROM seasons s
LEFT JOIN user_season_stats uss ON uss."seasonId" = s.id
WHERE s.status = 'ACTIVE'
GROUP BY s.id;

3.2 Топы

-- 🟢 Топ-10 по XP
SELECT "firstName", username, level, xp, "scrapTotalEarned", "quizzesCompleted"
FROM users
WHERE "isBanned" = false AND "isActive" = true
ORDER BY xp DESC
LIMIT 10;

-- 🟢 Топ-10 рефереров
SELECT u."firstName", u.username, u."friendsInvited", rc."clicksCount"
FROM users u
LEFT JOIN referral_codes rc ON rc."userId" = u.id
WHERE u."isBanned" = false AND u."friendsInvited" > 0
ORDER BY u."friendsInvited" DESC
LIMIT 10;

-- 🟢 Топ-10 по Streak Points
SELECT "firstName", username, "streakPoints", "streakPointsTotal", "dailyLoginStreak"
FROM users
WHERE "isBanned" = false AND "streakPoints" > 0
ORDER BY "streakPoints" DESC
LIMIT 10;

3.3 Здоровье экономики

-- 🟢 Scrap в обороте: общие балансы
SELECT
  SUM(scrap) as total_in_circulation,
  SUM("scrapTotalEarned") as total_ever_earned,
  SUM("scrapSpent") as total_spent,
  SUM("passiveIncomeBalance") as unclaimed_passive,
  ROUND(AVG(scrap)) as avg_per_user
FROM users
WHERE "isActive" = true AND "isBanned" = false;

-- 🟢 Крафты за последние 7 дней (расходы бюджета)
SELECT
  DATE(cbl."craftedAt") as day,
  COUNT(*) as crafts,
  ROUND(SUM(cbl."costRub")::numeric, 2) as total_rub
FROM craft_budget_logs cbl
WHERE cbl."craftedAt" > NOW() - INTERVAL '7 days'
GROUP BY DATE(cbl."craftedAt")
ORDER BY day DESC;

-- 🟢 Бюджетный период: остаток
SELECT
  bp."monthNumber", bp."periodNumber",
  bp."baseBudgetRub", bp."carriedOverRub", bp."spentRub",
  ROUND((bp."baseBudgetRub" + bp."carriedOverRub" - bp."spentRub")::numeric, 2) as remaining,
  bp."startDate", bp."endDate"
FROM budget_periods bp
WHERE bp."isActive" = true;

3.4 Rust-интеграция

-- 🟢 Последние 20 webhook-ов
SELECT
  rwl."eventType", rwl.status, rwl."steamId",
  rwl."errorMessage", rwl."createdAt"
FROM rust_webhook_logs rwl
ORDER BY rwl."createdAt" DESC
LIMIT 20;

-- 🟢 Failed webhook-и (проблемы)
SELECT
  rwl."eventType", rwl."steamId", rwl."errorMessage",
  rwl.payload, rwl."createdAt"
FROM rust_webhook_logs rwl
WHERE rwl.status = 'FAILED'
ORDER BY rwl."createdAt" DESC
LIMIT 20;

4. Полезные однострочники

-- 🟢 Обзор заполненности ключевых таблиц
SELECT 'users' as tbl, COUNT(*) as cnt FROM users
UNION ALL SELECT 'quiz_results', COUNT(*) FROM quiz_results
UNION ALL SELECT 'case_openings', COUNT(*) FROM case_openings
UNION ALL SELECT 'spin_results', COUNT(*) FROM spin_results
UNION ALL SELECT 'user_quests', COUNT(*) FROM user_quests
UNION ALL SELECT 'user_achievements', COUNT(*) FROM user_achievements
UNION ALL SELECT 'user_inventory', COUNT(*) FROM user_inventory
UNION ALL SELECT 'referrals', COUNT(*) FROM referrals
UNION ALL SELECT 'withdrawals', COUNT(*) FROM withdrawals
UNION ALL SELECT 'feed_events', COUNT(*) FROM feed_events
ORDER BY cnt DESC;

-- 🟡 Быстрый сброс кулдауна кейса
UPDATE users SET "lastDailyCase" = NULL WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

-- 🟡 Быстрый сброс кулдауна спина
UPDATE users SET "lastDailySpin" = NULL WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

SCHEMA_GUIDE — карта Prisma-моделей с номерами строк
Architecture Overview — общая архитектура системы

Конвенции​

0. Подготовка​

1. Диагностика (SELECT)​

1.1 Профиль пользователя​

1.2 Квизы​

1.3 Кейсы и спины​

1.4 Квесты​

1.5 Инвентарь и крафт​

1.6 Достижения​

1.7 Рефералы и пассивный доход​

1.8 Сезон и рейтинг​

1.9 Баффы​

1.10 Промокоды​

1.11 Streak Points и розыгрыши​

1.12 Экономика и бюджет​

2. Сброс для тестирования​

2.1 Сброс квизов​

2.2 Сброс квестов​

2.3 Сброс кейсов и спинов​

2.4 Сброс инвентаря и крафта​

2.5 Сброс достижений​

2.6 Сброс рефералов​

2.7 Сброс сезонной статистики​

2.8 Сброс стриков и Streak Points​

2.9 Сброс промокодов​

2.10 Сброс баффов​

2.11 Сброс Luck Pool и бюджета​

2.12 Выводы​

2.13 Выдать себе ресурсы​

2.14 Полный сброс данных (без удаления аккаунта)​

2.15 Полное каскадное удаление пользователя​

3. Production-диагностика (READ-ONLY)​

3.1 Общая статистика​

3.2 Топы​

3.3 Здоровье экономики​

3.4 Rust-интеграция​

4. Полезные однострочники​

Related​