fcsouza/engineering-postgres-game-schema

PostgreSQL Game Schema

Purpose

Genre-agnostic database schema design for games using Drizzle ORM + PostgreSQL.

When to Use

Trigger: database schema, game database, Drizzle schema, players table, inventory, leaderboards, sessions, events, entity system, game data model

Prerequisites

None — this is a foundational skill.

Core Principles

Sid Meier: "A game is a series of interesting decisions." Richard Garfield: "The best game systems are elegant rule systems — minimal components that compose into complex behaviors."

1. Schema must be game-type agnostic — never assume RPG, MMO, or idle
2. Use JSONB for extensible data (stats, properties, metadata)
3. Timestamps everywhere (createdAt, updatedAt) for audit trails
4. Soft deletes over hard deletes for game data
5. Enum types for fixed categories (rarity, status, role)
6. Composite indexes for common query patterns
7. Foreign keys with cascading deletes where appropriate
8. Versioned schema migrations with drizzle-kit

Step-by-Step Instructions

1. Install Dependencies

bun add drizzle-orm @neondatabase/serverless
bun add -d drizzle-kit

2. Configure Drizzle

Create drizzle.config.ts at the project root. See boilerplate/migrations.ts for the full config pattern.

3. Define Schemas

Start with the core tables in boilerplate/schema.ts. These cover players, sessions, inventory, items, events, leaderboards, achievements, currencies, social relations, guilds, and guild members.

Extend with custom entities using templates/entity-template.ts.

4. Generate Migrations

bunx drizzle-kit generate

5. Run Migrations

bunx drizzle-kit migrate

6. Query Data

See templates/query-patterns.ts for common query patterns including inventory lookups, leaderboard queries, event aggregation, and JSONB filtering.

Code Examples

Player with JSONB stats

import { players } from './schema';

await db.insert(players).values({
  username: 'player1',
  email: '[email protected]',
  displayName: 'Player One',
  stats: { health: 100, speed: 10 },
  metadata: { tutorial_completed: true },
});

Flexible item definitions

await db.insert(itemDefinitions).values({
  name: 'Rare Artifact',
  type: 'equipment',
  rarity: 'rare',
  baseStats: { power: 25, durability: 100 },
  metadata: { description: 'A mysterious artifact', tradeable: true },
  stackable: false,
});

See boilerplate/schema.ts for full table definitions and templates/query-patterns.ts for advanced queries.

Cross-References

• bullmq-game-queues for async event processing
• redis-game-patterns for cached queries
• betterauth-integration for auth tables
• game-economy-design for economy schemas

Pitfalls & Anti-Patterns

• Don't store game state as a single JSON blob — use normalized tables with JSONB for extensible fields only
• Don't use auto-increment IDs for player-facing identifiers — use UUIDs or nanoid to prevent enumeration
• Don't skip indexes on frequently queried columns — especially playerId, category, type, and createdAt
• Don't hardcode genre-specific columns in core tables — use JSONB metadata instead of adding swordDamage or spellPower columns
• Don't use hard deletes — always soft delete with deletedAt timestamps so game history is preserved
• Don't forget composite indexes — queries like "player's inventory filtered by type" need (playerId, type) indexes

Designer Philosophy

Sid Meier: Schema should enable "interesting decisions" — flexible enough that any game mechanic can be modeled without schema changes. A well-designed JSONB column lets designers iterate on game mechanics without migrations.

Richard Garfield: Elegant schemas have minimal tables that compose into complex behaviors. Eleven core tables can model inventory, progression, social, economy, and analytics for any genre.

Sources

• Drizzle ORM Documentation
• PostgreSQL JSONB Best Practices
• Game Database Design Patterns (GDC talks on scalable game backends)
• Neon Serverless PostgreSQL