W.E.B. — SQL Commands Reference

Caution: All commands below run directly against the production PostgreSQL database on Supabase. Always double-check WHERE clauses before executing UPDATE or DELETE statements. Read-only (SELECT) queries are always safe. Destructive queries are marked DANGER.

Schema Migrations

Command	What It Does
ALTER TABLE users ADD COLUMN school_account BOOLEAN NOT NULL DEFAULT FALSE;	WRITEAdds the `school_account` flag column to the users table. Required once after deploying the school-account feature. Safe to run — existing users default to `FALSE`.

School Accounts (@donboscoprep.org)

Command	What It Does
SELECT id, username, email, credits, school_account, created_at FROM users WHERE email LIKE '%@donboscoprep.org' ORDER BY created_at DESC;	READLists all Don Bosco Prep student accounts with their current credit balances and registration dates.
UPDATE users SET credits = credits + 5 WHERE email LIKE '%@donboscoprep.org';	WRITESCHOOLAdds 5 credits to every Don Bosco Prep student account. Use this whenever you want to top up the class with another round of play.
UPDATE users SET credits = 5 WHERE email LIKE '%@donboscoprep.org';	WRITESCHOOLResets every Don Bosco Prep student to exactly 5 credits (overwrites current balance). Use at the start of a new semester/unit.
UPDATE users SET school_account = TRUE WHERE email LIKE '%@donboscoprep.org' AND school_account = FALSE;	WRITEMarks any existing `@donboscoprep.org` users who registered before the feature was deployed as school accounts (blocking future purchases).

Credits & Billing

Command	What It Does
SELECT id, username, email, credits FROM users ORDER BY credits DESC LIMIT 50;	READShows the 50 users with the most credits — useful for spotting anomalies.
SELECT id, username, email, credits FROM users WHERE credits = 0;	READLists all users who have run out of credits.
UPDATE users SET credits = credits + 1 WHERE id = <user_id>;	WRITEManually adds 1 credit to a specific user (replace `<user_id>`). Use to comp a player whose game had a technical issue.
UPDATE users SET credits = -1 WHERE id = <user_id>;	WRITEGives a specific user unlimited credits (replaces numeric balance with `-1`). Use for admins, testers, or invited VIPs.
SELECT reason, SUM(amount) as total, COUNT(*) as count FROM credit_transactions GROUP BY reason ORDER BY total DESC;	READSummary of all credit transactions by reason — shows how many credits have been granted via registration, purchases, admin grants, etc.
SELECT u.username, u.email, ct.amount, ct.reason, ct.detail, ct.created_at FROM credit_transactions ct JOIN users u ON u.id = ct.user_id ORDER BY ct.created_at DESC LIMIT 100;	READRecent 100 credit transactions across all users with usernames — full audit log view.

User Management

Command	What It Does
SELECT id, username, email, email_verified, is_banned, credits, school_account, created_at FROM users ORDER BY created_at DESC LIMIT 50;	READRecent 50 registrations with key account flags. Good first look when checking on new sign-ups.
SELECT COUNT(*) FROM users;	READTotal number of registered users.
SELECT COUNT(*) FROM users WHERE email_verified = TRUE;	READNumber of users who have verified their email address.
UPDATE users SET is_banned = TRUE, ban_reason = 'reason here' WHERE id = <user_id>;	WRITEBans a user by ID. Their session will be rejected on next request. Replace `<user_id>` and fill in the ban reason.
UPDATE users SET is_banned = FALSE, ban_reason = NULL WHERE id = <user_id>;	WRITEUnbans a user.
SELECT id, username, email FROM users WHERE is_banned = TRUE;	READLists all currently banned accounts.
DELETE FROM user_sessions WHERE user_id = <user_id>;	DANGERForce-logs out a specific user by deleting all their sessions. They will need to log in again.

Games & Lobby

Command	What It Does
SELECT id, name, status, current_players, max_players, created_at FROM games ORDER BY created_at DESC LIMIT 30;	READRecent 30 games with player counts and status (lobby/active/finished).
SELECT COUNT(*) FROM games WHERE status = 'active';	READNumber of games currently in progress.
SELECT u.username, e.empire_name, e.is_ai, e.reliability_score FROM empires e JOIN users u ON u.id = e.user_id WHERE e.game_id = <game_id>;	READLists all players (and any AI takeovers) in a specific game. Replace `<game_id>`.

Advantage Cards

Command	What It Does
SELECT id, card_key, name, category, is_active FROM advantage_cards ORDER BY category, name;	READAll advantage cards with their active status. Use this to check why the admin "Active Cards" count may not match expectations.
SELECT is_active, COUNT(*) FROM advantage_cards GROUP BY is_active;	READQuick count of active vs inactive cards.
UPDATE advantage_cards SET is_active = TRUE WHERE id = <card_id>;	WRITEActivates a card so it appears in games and is counted on the leaderboard stats page. Replace `<card_id>`.

Player Feedback

Command	What It Does
SELECT id, feedback_type, name, email, message, status, created_at FROM player_feedback ORDER BY created_at DESC LIMIT 50;	READMost recent 50 feedback submissions across all types.
SELECT feedback_type, COUNT(*) as count FROM player_feedback GROUP BY feedback_type;	READBreakdown of feedback by type (bug, suggestion, other, etc.).