engineering-postgres-game-schema
/engineering-postgres-game-schema
Use when designing game database schemas, player data models, inventory systems, or working with Drizzle ORM and PostgreSQL for games. Triggers: database, schema, Drizzle, players, inventory, leaderboards, game data.
- Author
- fcsouza ↗
- Repository
- agent-skills ↗
- Licence
- GPL-3.0
- Last repository change
- 18.10.2026
Assessment and security
- Files in this package
- 5
- Scripts
- 4
- HTML comments
- 0
- URLs in SKILL.md
- 3
No signals detected. Repository changed on 18.10.2026, without references to retired models or APIs.
GPL-3.0, otwarta.
- 4 scripts in this package. Review them before installing, as Claude may execute them.
Scripts: boilerplate/migrations.ts, boilerplate/schema.ts, templates/entity-template.ts, templates/query-patterns.ts
What it does and when to use it
Use when designing game database schemas, player data models, inventory systems, or working with Drizzle ORM and PostgreSQL for games. Triggers: database, schema, Drizzle, players, inventory, leaderboards, game data.
Original author description
Use when designing game database schemas, player data models, inventory systems, or working with Drizzle ORM and PostgreSQL for games. Triggers: database, schema, Drizzle, players, inventory, leaderboards, game data.
How it works
- Purpose
- When to Use
- Prerequisites
- Core Principles
- Step-by-Step Instructions
- Code Examples
- Cross-References
- Pitfalls & Anti-Patterns
- Designer Philosophy
- Sources
SKILL.md preview
---
name: engineering-postgres-game-schema
description: >-
Use when designing game database schemas, player data models, inventory systems, or working with Drizzle ORM and PostgreSQL for games. Triggers: database, schema, Drizzle, players, inventory, leaderboards, game data.
---
# 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
```bash
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
```bash
bunx drizzle-kit generate
```
### 5. Run Migrations
```bash
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
```typescript
import { players } from './schema';
await db.insert(players).values({
username: 'player1',
email: 'player1@example.com',
displayName: 'Player One',
stats: { health: 100, speed: 10 },
metadata: { tutorial_completed: true },
});
```
### Flexible item definitions
```typescript
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](https://orm.drizzle.team)
- [PostgreSQL JSONB Best Practices](https://www.postgresql.org/docs/current/datatype-json.html)
- Game Database Design Patterns (GDC talks on scalable game backends)
- [Neon Serverless PostgreSQL](https://neon.tech/docs)
Files in this package (5)
- SKILL.md 4 KB
- boilerplate/migrations.ts 2 KB
- boilerplate/schema.ts 11 KB
- templates/entity-template.ts 5 KB
- templates/query-patterns.ts 7 KB
Versions
The database has no other skills named engineering-postgres-game-schema.
Identical content appears in 1 other GitHub folders. We show one copy with the highest score.
Database snapshot: 13.11.2026 · package version 482d8418054d (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 90postgres-drizzle
Write or review PostgreSQL schemas, queries, migrations, and Drizzle ORM code. Use for Postgres/Drizzle relations, indexing, pooling, or query performance; not for unrelated databases or generic backend work. - 85Drizzle ORM
Expert guidance for Drizzle ORM including schema definition, queries, relations, migrations, TypeScript integration with SQLite/PostgreSQL, and Drizzle Studio. Use this when working with type-safe database operations, schema management, or ORM queries. - 100drizzle-orm
Apply Drizzle ORM patterns for schemas, migrations, queries, branded types, and Postgres or D1 boundaries. Use when working with Drizzle or typed SQL, not raw `bun:sqlite` or non-Drizzle engines. - 100drizzle
Expert Drizzle ORM assistance covering type-safe schema declaration, migrations, relations, and serverless database clients. Use when building performant TypeScript apps with PostgreSQL, MySQL, or SQLite. - 100drizzle
Use this skill when writing or modifying Drizzle ORM schemas, queries, or migrations in this repo — specifically the `@internal/dashboard-agent-db` package (the dashboard agent's conversation datastore). Covers pg-core schema definition, the postgres-js driver, drizzle-kit… - 90database-design
Use when designing database schemas, reviewing data models, planning migrations, optimizing slow queries, or establishing database patterns. Covers relational (PostgreSQL, MySQL, SQLite), document (MongoDB), and ORM-integrated (Prisma, Drizzle, TypeORM) projects.