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,
  "passiveIncomeBalance",
  "streakPoints", "streakPointsTotal",
  "dailyLoginStreak", "bestDailyLoginStreak",
  "lastDailyCase", "lastDailySpin", "lastLoginDate",
  "botStatus", "isBanned", "steamId", "steamVerified",
  "createdAt"
FROM users
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

-- 🟢 Сезонные балансы и активность
SELECT
  uss.level, uss.xp,
  uss.scrap, uss."scrapEarned", uss."scrapSpent",
  uss."quizzesCompleted", uss."correctAnswers", uss."incorrectAnswers",
  uss."tasksCompleted", uss."achievementsUnlocked", uss."friendsInvited",
  uss."casesOpened", uss."dailyCasesOpened", uss."dailySpinsUsed",
  uss."itemsCrafted", uss."itemsSalvaged"
FROM user_season_stats uss
WHERE uss."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY uss."createdAt" DESC;

-- 🟢 Детализация доходов Scrap (откуда заработано, по сезонам)
SELECT
  uss."scrapFromQuizzes", uss."scrapFromTasks", uss."scrapFromAchievements",
  uss."scrapFromReferrals", uss."scrapFromCases", uss."scrapFromSpins",
  uss."scrapSpentOnCases", uss."scrapSpentOnCraft", uss."scrapSpentOnSpins"
FROM user_season_stats uss
WHERE uss."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY uss."createdAt" DESC;

-- 🟢 Детализация доходов XP (по сезонам)
SELECT
  uss."xpFromTasks", uss."xpFromAchievements",
  uss."xpFromReferrals", uss."xpFromCases", uss."xpFromSpins",
  uss."xpFromSalvage"
FROM user_season_stats uss
WHERE uss."userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY uss."createdAt" DESC;

-- 🟢 Детализация доходов XP (сезонные, включая xpFromAdmin)
SELECT
  "xpFromTasks", "xpFromAchievements",
  "xpFromReferrals", "xpFromCases", "xpFromSpins",
  "xpFromSalvage", "xpFromAdmin", "xpFromRaffle", "xpFromPromoCodes"
FROM user_season_stats
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
ORDER BY "createdAt" DESC;

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;

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."referrerRewardClaimed", r."referrerRewardClaimedAt",
  r."referralRewardClaimed", r."referralRewardClaimedAt",
  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."scrapEarned",
  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.rewards,  -- rewards — JSON array с наградами
  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');

-- 🟡 Обнулить счётчики квизов в сезонной статистике
UPDATE user_season_stats SET
  "quizzesCompleted" = 0,
  "correctAnswers" = 0,
  "incorrectAnswers" = 0,
  "scrapFromQuizzes" = 0
WHERE "userId" = (SELECT id FROM users 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 user_season_stats SET
  "tasksCompleted" = 0,
  "scrapFromTasks" = 0,
  "xpFromTasks" = 0
WHERE "userId" = (SELECT id FROM users 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 user_season_stats SET
  "casesOpened" = 0,
  "dailyCasesOpened" = 0,
  "dailySpinsUsed" = 0,
  "scrapFromCases" = 0,
  "scrapFromSpins" = 0,
  "scrapSpentOnCases" = 0,
  "xpFromCases" = 0,
  "xpFromSpins" = 0
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Сбросить кулдауны кейсов/спинов в профиле
UPDATE users SET
  "lastDailyCase" = NULL,
  "lastDailySpin" = NULL
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 user_season_stats SET
  "itemsCrafted" = 0,
  "itemsSalvaged" = 0,
  "scrapSpentOnCraft" = 0,
  "xpFromSalvage" = 0
WHERE "userId" = (SELECT id FROM users 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 user_season_stats SET
  "achievementsUnlocked" = 0,
  "scrapFromAchievements" = 0,
  "xpFromAchievements" = 0
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

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

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

-- 🟡 Обнулить счётчик рефералов в сезонной статистике
UPDATE user_season_stats SET
  "friendsInvited" = 0,
  "scrapFromReferrals" = 0,
  "xpFromReferrals" = 0
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 🟡 Обнулить пассивный доход в профиле
UPDATE users SET
  "passiveIncomeBalance" = 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
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 user_season_stats SET
  scrap = scrap + 10000,
  "scrapEarned" = "scrapEarned" + 10000,
  "scrapFromAdmin" = "scrapFromAdmin" + 10000
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID')
  AND "seasonId" = (SELECT id FROM seasons WHERE status = 'ACTIVE' LIMIT 1);

-- 🟡 Выдать 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 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 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) Обнуляем сезонные счётчики (scrap/xp breakdown теперь в user_season_stats)
UPDATE user_season_stats SET
  scrap = 0, "scrapEarned" = 0, xp = 0, level = 1,
  "scrapFromQuizzes" = 0, "scrapFromTasks" = 0, "scrapFromAchievements" = 0,
  "scrapFromReferrals" = 0, "scrapFromCases" = 0, "scrapFromSpins" = 0,
  "scrapFromAdmin" = 0, "scrapFromPromoCodes" = 0, "scrapFromRaffle" = 0,
  "scrapFromConsolation" = 0, "scrapFromBoosts" = 0,
  "scrapSpent" = 0, "scrapSpentOnCases" = 0, "scrapSpentOnCraft" = 0, "scrapSpentOnSpins" = 0,
  "xpFromTasks" = 0, "xpFromAchievements" = 0,
  "xpFromReferrals" = 0, "xpFromCases" = 0, "xpFromSpins" = 0,
  "xpFromSalvage" = 0, "xpFromAdmin" = 0, "xpFromRaffle" = 0,
  "xpFromPromoCodes" = 0, "xpFromBoosts" = 0,
  "quizzesCompleted" = 0, "correctAnswers" = 0, "incorrectAnswers" = 0,
  "bestDailyLoginStreak" = 0,
  "tasksCompleted" = 0, "achievementsUnlocked" = 0, "friendsInvited" = 0,
  "casesOpened" = 0, "dailyCasesOpened" = 0, "dailySpinsUsed" = 0,
  "itemsCrafted" = 0, "itemsSalvaged" = 0,
  "streakPointsEarned" = 0,
  "streakPointsSpent" = 0
WHERE "userId" = (SELECT id FROM users WHERE "telegramId" = 'YOUR_TELEGRAM_ID');

-- 4) Обнуляем глобальные счётчики пользователя
UPDATE users SET
  "dailyLoginStreak" = 0, "bestDailyLoginStreak" = 0,
  "passiveIncomeBalance" = 0,
  "streakPoints" = 0, "streakPointsTotal" = 0, "streakPointsSpent" = 0,
  "lastDailyCase" = NULL, "lastDailySpin" = NULL,
  "lastStreakPointsClaim" = NULL, "lastActivityDate" = NULL,
  "lastLoginDate" = NOW()
WHERE "telegramId" = 'YOUR_TELEGRAM_ID';

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

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

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

Какие таблицы удаляются автоматически (CASCADE)?

При DELETE FROM users Prisma каскадно удалит записи из ~30 таблиц: user_settings, user_active_buffs, buff_events, user_bot_interactions, quiz_results, case_openings, user_case_stats, user_inventory, promo_code_redemptions, craft_history, withdrawals, feed_events, spin_results, user_achievements, user_quests, user_free_case_opens, referral_codes (→ referral_click_analytics), referrals (обе стороны), user_season_stats, raffle_tickets, streak_points_transactions, luck_pool_entries, craft_budget_logs, admin_grants, season_reward_claims, salvage_history, user_feedback (→ feedback_messages).

Скрипт ниже обрабатывает то, что CASCADE не покрывает: таблицы без FK, денормализованные счётчики и nullable ссылки без onDelete.

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

DO $$
DECLARE
  v_user_id TEXT;
  v_telegram_id TEXT := 'YOUR_TELEGRAM_ID';  -- ЗАМЕНИТЬ
  v_steam_id TEXT;
  v_referrer_id TEXT;
  v_promo_count INT;
  v_ticket_counts RECORD;
BEGIN
  -- ═══════════════════════════════════════════
  -- 0. Найти пользователя
  -- ═══════════════════════════════════════════
  SELECT id, "steamId", "referredById"
  INTO v_user_id, v_steam_id, v_referrer_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. Откат денормализованных счётчиков
  --    (до CASCADE, пока связи ещё существуют)
  -- ═══════════════════════════════════════════

  -- 1a) Referrer friendsInvited: если этот пользователь был приглашён
  IF v_referrer_id IS NOT NULL THEN
    UPDATE users
    SET "friendsInvited" = GREATEST(0, "friendsInvited" - 1)
    WHERE id = v_referrer_id;
    RAISE NOTICE '  → Referrer % friendsInvited decremented', v_referrer_id;
  END IF;

  -- 1b) PromoCode totalRedemptions: откатить использованные промокоды
  WITH redeemed AS (
    DELETE FROM promo_code_redemptions
    WHERE "userId" = v_user_id
    RETURNING "promoCodeId"
  )
  UPDATE promo_codes
  SET "totalRedemptions" = GREATEST(0, "totalRedemptions" - sub.cnt)
  FROM (
    SELECT "promoCodeId", COUNT(*)::INT as cnt
    FROM redeemed
    GROUP BY "promoCodeId"
  ) sub
  WHERE promo_codes.id = sub."promoCodeId";
  GET DIAGNOSTICS v_promo_count = ROW_COUNT;
  IF v_promo_count > 0 THEN
    RAISE NOTICE '  → Rolled back totalRedemptions for % promo codes', v_promo_count;
  END IF;

  -- 1c) Raffle totalTickets/totalParticipants: откатить билеты
  FOR v_ticket_counts IN
    SELECT "raffleId", COUNT(*)::INT as cnt
    FROM raffle_tickets
    WHERE "userId" = v_user_id
    GROUP BY "raffleId"
  LOOP
    UPDATE raffles
    SET "totalTickets" = GREATEST(0, "totalTickets" - v_ticket_counts.cnt),
        "totalParticipants" = GREATEST(0, "totalParticipants" - 1)
    WHERE id = v_ticket_counts."raffleId"
      AND status = 'ACTIVE';
    RAISE NOTICE '  → Raffle %: removed % tickets', v_ticket_counts."raffleId", v_ticket_counts.cnt;
  END LOOP;

  -- 1d) InviteSession счётчики: откатить UTM clicksCount и REFERRAL appOpensCount
  -- UTM сессии → utm_campaigns.clicksCount
  UPDATE utm_campaigns
  SET "clicksCount" = GREATEST(0, "clicksCount" - sub.cnt)
  FROM (
    SELECT (metadata->>'campaignId')::TEXT as campaign_id, COUNT(*)::INT as cnt
    FROM invite_sessions
    WHERE "telegramId" = v_telegram_id
      AND type = 'UTM'
      AND metadata->>'campaignId' IS NOT NULL
    GROUP BY metadata->>'campaignId'
  ) sub
  WHERE utm_campaigns.id = sub.campaign_id;

  -- REFERRAL сессии → referral_codes.appOpensCount
  -- NB: clicksCount инкрементируется redirect-service (анонимные клики), НЕ откатываем
  UPDATE referral_codes
  SET "appOpensCount" = GREATEST(0, "appOpensCount" - sub.cnt)
  FROM (
    SELECT (metadata->>'referralCodeId')::TEXT as ref_code_id, COUNT(*)::INT as cnt
    FROM invite_sessions
    WHERE "telegramId" = v_telegram_id
      AND type = 'REFERRAL'
      AND metadata->>'referralCodeId' IS NOT NULL
    GROUP BY metadata->>'referralCodeId'
  ) sub
  WHERE referral_codes.id = sub.ref_code_id;

  -- Удалить InviteSessions
  DELETE FROM invite_sessions WHERE "telegramId" = v_telegram_id;

  -- ═══════════════════════════════════════════
  -- 2. Таблицы с required userId БЕЗ каскада
  -- ═══════════════════════════════════════════
  DELETE FROM rust_player_sessions WHERE "userId" = v_user_id;

  -- ═══════════════════════════════════════════
  -- 3. Таблицы с 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;

  -- ═══════════════════════════════════════════
  -- 4. Self-referencing FK
  -- ═══════════════════════════════════════════
  UPDATE users SET "referredById" = NULL WHERE "referredById" = v_user_id;

  -- ═══════════════════════════════════════════
  -- 5. Таблицы по 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;

  -- ═══════════════════════════════════════════
  -- 6. Plain string references (не FK)
  -- ═══════════════════════════════════════════
  DELETE FROM banned_steam_ids WHERE "originalUserId" = v_user_id;

  -- ═══════════════════════════════════════════
  -- 7. Удалить пользователя
  --    CASCADE удалит ~30 связанных таблиц
  -- ═══════════════════════════════════════════
  DELETE FROM users WHERE id = v_user_id;

  RAISE NOTICE '✓ User % (telegramId=%) deleted successfully', v_user_id, v_telegram_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 u."firstName", u.username, uss.level, uss.xp, uss."scrapEarned", uss."quizzesCompleted"
FROM user_season_stats uss
JOIN users u ON uss."userId" = u.id
WHERE u."isBanned" = false AND u."isActive" = true
  AND uss."seasonId" = (SELECT id FROM seasons WHERE status = 'ACTIVE' LIMIT 1)
ORDER BY uss.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(uss.scrap) as total_in_circulation,
  SUM(uss."scrapEarned") as total_ever_earned,
  SUM(uss."scrapSpent") as total_spent,
  SUM(u."passiveIncomeBalance") as unclaimed_passive,
  ROUND(AVG(uss.scrap)) as avg_per_user
FROM user_season_stats uss
JOIN users u ON uss."userId" = u.id
WHERE u."isActive" = true AND u."isBanned" = false
  AND uss."seasonId" = (SELECT id FROM seasons WHERE status = 'ACTIVE' LIMIT 1);

-- 🟢 Крафты за последние 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';

---

Related

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