Manus에서 모든 스킬 실행
원클릭으로
원클릭으로
원클릭으로 Manus에서 모든 스킬 실행
시작하기databases
Game data persistence with player profiles, leaderboards, inventory systems using Redis and PostgreSQL
스타1
포크1
업데이트2025년 12월 30일 11:46
파일 탐색기
7 개 파일SKILL.md
readonly메뉴
Game data persistence with player profiles, leaderboards, inventory systems using Redis and PostgreSQL
Asynchronous programming models including coroutines, async/await, and reactive patterns
Game server communication protocols including gRPC, REST, and custom binary protocols
Efficient data serialization for game networking including Protobuf, FlatBuffers, and custom binary
Game server deployment with Docker, Kubernetes, and global distribution strategies
Game server design patterns including ECS, command pattern, and event sourcing
Server-side game loop implementation with fixed timestep, physics simulation, and tick rate optimization
| name | databases |
| description | Game data persistence with player profiles, leaderboards, inventory systems using Redis and PostgreSQL |
| sasmp_version | 1.3.0 |
| version | 2.0.0 |
| bonded_agent | 06-database-specialist |
| bond_type | PRIMARY_BOND |
| parameters | {"required":["database_type"],"optional":["connection_pool_size","cache_ttl_s","replication_mode"],"validation":{"database_type":{"type":"string","enum":["postgresql","redis","mongodb","dynamodb","cockroachdb"]},"connection_pool_size":{"type":"integer","min":5,"max":100,"default":20},"cache_ttl_s":{"type":"integer","min":60,"max":86400,"default":3600},"replication_mode":{"type":"string","enum":["single","replica","cluster"],"default":"single"}}} |
| retry_config | {"max_attempts":3,"backoff":"exponential","initial_delay_ms":100,"max_delay_ms":5000,"retryable_errors":["CONNECTION_TIMEOUT","DEADLOCK_DETECTED","REPLICA_LAG"]} |
| observability | {"logging":{"level":"info","fields":["query_type","table","duration_ms","rows_affected"]},"metrics":[{"name":"db_query_duration_seconds","type":"histogram"},{"name":"db_connections_active","type":"gauge"},{"name":"db_errors_total","type":"counter"},{"name":"cache_hit_ratio","type":"gauge"}]} |
Implement efficient data persistence for players, matches, and leaderboards.
| Database | Strength | Use Case |
|---|---|---|
| PostgreSQL | ACID, complex queries | Player data, transactions |
| Redis | Ultra-fast | Leaderboards, cache, sessions |
| MongoDB | Flexible schema | Game logs, analytics |
| DynamoDB | Infinite scale | Global leaderboards |
| CockroachDB | Distributed ACID | Multi-region |
-- Player profiles
CREATE TABLE players (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
username VARCHAR(32) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
mmr INTEGER DEFAULT 1000,
level INTEGER DEFAULT 1,
xp INTEGER DEFAULT 0,
coins BIGINT DEFAULT 0,
created_at TIMESTAMP DEFAULT NOW(),
last_login TIMESTAMP DEFAULT NOW()
);
-- Indexes for common queries
CREATE INDEX idx_players_mmr ON players(mmr DESC);
CREATE INDEX idx_players_level ON players(level DESC);
-- Inventory system
CREATE TABLE inventory (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
player_id UUID REFERENCES players(id) ON DELETE CASCADE,
item_type VARCHAR(50) NOT NULL,
item_id VARCHAR(100) NOT NULL,
quantity INTEGER DEFAULT 1,
metadata JSONB DEFAULT '{}',
acquired_at TIMESTAMP DEFAULT NOW(),
UNIQUE(player_id, item_type, item_id)
);
-- Match history
CREATE TABLE matches (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
game_mode VARCHAR(32) NOT NULL,
started_at TIMESTAMP NOT NULL,
ended_at TIMESTAMP,
winner_team INTEGER,
metadata JSONB DEFAULT '{}'
);
CREATE TABLE match_players (
match_id UUID REFERENCES matches(id),
player_id UUID REFERENCES players(id),
team INTEGER NOT NULL,
score INTEGER DEFAULT 0,
kills INTEGER DEFAULT 0,
deaths INTEGER DEFAULT 0,
mmr_change INTEGER DEFAULT 0,
PRIMARY KEY (match_id, player_id)
);
const Redis = require('ioredis');
const redis = new Redis({ host: 'localhost', port: 6379 });
class Leaderboard {
constructor(name) {
this.key = `leaderboard:${name}`;
}
async setScore(playerId, score) {
return redis.zadd(this.key, score, playerId);
}
async getScore(playerId) {
return redis.zscore(this.key, playerId);
}
async getRank(playerId) {
// 0-indexed, null if not found
const rank = await redis.zrevrank(this.key, playerId);
return rank !== null ? rank + 1 : null;
}
async getTop(count = 100) {
const results = await redis.zrevrange(
this.key, 0, count - 1, 'WITHSCORES'
);
// Convert to [{playerId, score, rank}]
const leaderboard = [];
for (let i = 0; i < results.length; i += 2) {
leaderboard.push({
playerId: results[i],
score: parseInt(results[i + 1]),
rank: i / 2 + 1
});
}
return leaderboard;
}
async getAroundPlayer(playerId, range = 5) {
const rank = await this.getRank(playerId);
if (!rank) return [];
const start = Math.max(0, rank - range - 1);
const end = rank + range - 1;
return redis.zrevrange(this.key, start, end, 'WITHSCORES');
}
}
const { Pool } = require('pg');
const pool = new Pool({
host: process.env.DB_HOST,
database: 'game',
max: 20, // Max connections
idleTimeoutMillis: 30000, // Close idle connections
connectionTimeoutMillis: 2000
});
// Transaction wrapper
async function withTransaction(callback) {
const client = await pool.connect();
try {
await client.query('BEGIN');
const result = await callback(client);
await client.query('COMMIT');
return result;
} catch (e) {
await client.query('ROLLBACK');
throw e;
} finally {
client.release();
}
}
// Usage: Atomic currency transfer
await withTransaction(async (client) => {
await client.query(
'UPDATE players SET coins = coins - $1 WHERE id = $2',
[amount, senderId]
);
await client.query(
'UPDATE players SET coins = coins + $1 WHERE id = $2',
[amount, receiverId]
);
});
class CachedPlayerService {
constructor(db, redis) {
this.db = db;
this.redis = redis;
this.ttl = 3600; // 1 hour
}
async getPlayer(playerId) {
const cacheKey = `player:${playerId}`;
// Try cache first
const cached = await this.redis.get(cacheKey);
if (cached) {
return JSON.parse(cached);
}
// Cache miss - load from DB
const player = await this.db.query(
'SELECT * FROM players WHERE id = $1',
[playerId]
);
if (player.rows[0]) {
// Cache for future requests
await this.redis.setex(
cacheKey,
this.ttl,
JSON.stringify(player.rows[0])
);
}
return player.rows[0];
}
async updatePlayer(playerId, updates) {
// Update DB
await this.db.query(
'UPDATE players SET mmr = $1 WHERE id = $2',
[updates.mmr, playerId]
);
// Invalidate cache
await this.redis.del(`player:${playerId}`);
}
}
| Error | Root Cause | Solution |
|---|---|---|
| Connection timeout | Pool exhausted | Increase pool size |
| Deadlock | Lock contention | Fix lock ordering |
| Slow queries | Missing index | Add indexes |
| Cache stampede | Mass expiration | Staggered TTL |
| Stale data | Cache inconsistency | Invalidation strategy |
-- Check active connections
SELECT count(*) FROM pg_stat_activity WHERE datname = 'game';
-- Find slow queries
SELECT query, mean_time, calls
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;
-- Check index usage
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan;
-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;
# Redis diagnostics
redis-cli INFO stats
redis-cli SLOWLOG GET 10
redis-cli MEMORY DOCTOR
const { Pool } = require('pg');
describe('Database Operations', () => {
let pool;
beforeAll(async () => {
pool = new Pool({ database: 'game_test' });
});
afterAll(async () => {
await pool.end();
});
test('creates player with default values', async () => {
const result = await pool.query(
`INSERT INTO players (username, email)
VALUES ($1, $2) RETURNING *`,
['testuser', 'test@example.com']
);
expect(result.rows[0].mmr).toBe(1000);
expect(result.rows[0].level).toBe(1);
});
test('leaderboard returns correct rank', async () => {
const lb = new Leaderboard('test');
await lb.setScore('player1', 1000);
await lb.setScore('player2', 2000);
await lb.setScore('player3', 1500);
expect(await lb.getRank('player2')).toBe(1);
expect(await lb.getRank('player3')).toBe(2);
expect(await lb.getRank('player1')).toBe(3);
});
test('transaction rollback on error', async () => {
const initialCoins = 1000;
try {
await withTransaction(async (client) => {
await client.query(
'UPDATE players SET coins = coins - 500 WHERE id = $1',
[playerId]
);
throw new Error('Simulated failure');
});
} catch (e) {
// Expected
}
// Verify rollback
const result = await pool.query(
'SELECT coins FROM players WHERE id = $1',
[playerId]
);
expect(result.rows[0].coins).toBe(initialCoins);
});
});
assets/ - Schema templatesreferences/ - Database optimization guides