xianwen-db-ops

name	xianwen-db-ops
description	Database operations for Xianwen Online: Neon PostgreSQL queries, migrations, data cleanup, player data deletion, and schema management. Use when user needs database operations, migration fixes, data cleanup, or says '清空資料庫', '刪除玩家', 'migration'.

Xianwen Database Operations

Database management workflow for Neon PostgreSQL, extracted from 50+ database operation sessions.

Database Connection

• Provider: Neon (managed PostgreSQL)
• Region: ap-southeast-1 (Singapore)
• Connection: Via pooler with SSL required
• ORM: SQLx (Rust, compile-time checked queries)

Migration Management

Location

server/migrations/ — Currently 136+ migration files

Creating New Migrations

# Format: YYYYMMDDHHMMSS_description.sql
# Example: 20260322120000_add_sect_features.sql

Migration Best Practices

1. Always use IF NOT EXISTS / IF EXISTS for safety
2. Add columns as nullable first, then backfill, then add constraints
3. Never drop columns in production without a deprecation period
4. Use ON CONFLICT for upsert operations

Common Migration Issues

Issue	Solution
Column already exists	Use ALTER TABLE ... ADD COLUMN IF NOT EXISTS
ON CONFLICT without unique constraint	Add unique/exclusion constraint first
Migration applied but data missing	Write separate data migration SQL
Production schema out of sync	SSH to server, check applied migrations

Common Operations

Delete Player Data

-- Delete all data for a specific player (by username or discord_id)
-- Order matters due to foreign keys
DELETE FROM player_inventory WHERE character_id IN (SELECT id FROM characters WHERE user_id = '<user_id>');
DELETE FROM player_techniques WHERE character_id IN (SELECT id FROM characters WHERE user_id = '<user_id>');
DELETE FROM player_quests WHERE character_id IN (SELECT id FROM characters WHERE user_id = '<user_id>');
-- ... (all player-related tables)
DELETE FROM characters WHERE user_id = '<user_id>';
DELETE FROM users WHERE id = '<user_id>';

Clear All Player Data (Development)

-- TRUNCATE all player-related tables (preserves game data like NPCs, items)
TRUNCATE characters, player_inventory, player_techniques, player_quests,
         player_equipment, player_pets, player_achievements CASCADE;
-- DO NOT truncate: npcs, items, techniques, locations, sects

Data Integrity Checks

-- Check orphaned records
SELECT * FROM player_techniques WHERE character_id NOT IN (SELECT id FROM characters);

-- Check sect member counts
SELECT s.name, COUNT(sm.id) FROM sects s LEFT JOIN sect_members sm ON s.id = sm.sect_id GROUP BY s.name;

-- Check NPC locations
SELECT name, current_location FROM npcs WHERE current_location IS NULL;

SQLx Workflow

After Schema Changes

1. Create migration file
2. Run cargo sqlx prepare to update offline query metadata
3. Commit the .sqlx/ directory changes
4. Deploy server (migration runs automatically on startup)

Query Patterns

// Always use sqlx::query_as! for type safety
let character = sqlx::query_as!(Character, "SELECT * FROM characters WHERE id = $1", id)
    .fetch_optional(&pool)
    .await?;

Safety Rules

1. NEVER run destructive queries without explicit user confirmation
2. ALWAYS use transactions for multi-table operations
3. ALWAYS backup before bulk operations (pg_dump)
4. NEVER expose connection strings in code — use environment variables
5. Check ON CONFLICT specs match existing unique constraints