Skip to content
Skills Library
Data & analytics · Building & coding

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.

Licence
GPL-3.0
Last repository change
18.10.2026

Assessment and security

95/ 100Class: good
Format20 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
5
Scripts
4
HTML comments
0
URLs in SKILL.md
3
Deprecation

No signals detected. Repository changed on 18.10.2026, without references to retired models or APIs.

Licence

GPL-3.0, otwarta.

What to check before installing
  • 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

  1. Purpose
  2. When to Use
  3. Prerequisites
  4. Core Principles
  5. Step-by-Step Instructions
  6. Code Examples
  7. Cross-References
  8. Pitfalls & Anti-Patterns
  9. Designer Philosophy
  10. Sources
Original SKILL.md headings · 124 lines, 556 words
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.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.