Database

Database Architecture

TactiHub uses PostgreSQL 16 as its primary database with Drizzle ORM as a type-safe ORM layer. The database is provided via Docker Compose.

Schema (15 Tables)

The database contains 15 tables:

• users — User accounts
• games — Supported games (R6, Valorant, etc.)
• game_maps — Maps per game
• map_floors — Floors per map (with Blueprint/Darkprint/Whiteprint images)
• operators — Operators/agents per game
• gadgets — Gadgets/abilities per game
• operator_gadgets — Many-to-many junction for operator-gadget assignments
• battleplans — Saved battle plans
• battleplan_floors — Floors per battle plan
• draws — Drawings (JSONB data column)
• operator_slots — Operator assignments per plan
• rooms — Active rooms
• votes — Up/downvotes for public plans
• settings — App-wide key-value settings
• registration_tokens — Invite tokens

---

Reset the Database

If you want to start completely fresh (deletes all data — users, battle plans, everything):

Option A — Automatic (recommended for development)

bash dev-reset.sh

The script handles everything in one step:

1. Switches to the dev branch and pulls the latest changes
2. Installs dependencies (pnpm install)
3. Stops Docker containers and deletes all volumes
4. Restarts PostgreSQL + Redis
5. Waits until PostgreSQL is ready
6. Builds the shared package
7. Deletes old migration files (prevents conflicts)
8. Generates, migrates, and seeds the database

Note: The script asks for confirmation before starting. It is only for development — do not use on production systems.

Option B — Manual

# Stop containers and delete volumes
docker compose down -v

# Restart containers
docker compose up -d

# Wait for PostgreSQL to be ready
sleep 3

# Delete old migration files (prevents conflicts!)
rm -rf packages/server/drizzle/*

# Re-run migrations and seed
pnpm db:generate
pnpm db:migrate
pnpm db:seed

Why rm -rf packages/server/drizzle/*? Old migration files can conflict after a DB reset — e.g. one migration creates a column, another tries to add it again. This causes the typical "column already exists" error. Deleting old files generates a single, clean migration from the current schema. See also Troubleshooting.

---

Drizzle Studio

pnpm db:studio

Opens Drizzle Studio at https://local.drizzle.studio. There you can:

• Browse all tables visually
• Edit data directly
• Run queries
• Inspect relationships between tables

---

Apply Schema Changes

When you modify the Drizzle schema in packages/server/src/db/schema/:

# 1. Generate migration files
pnpm db:generate

# 2. Apply migrations
pnpm db:migrate

---

What Happens with docker compose down -v?

Volume	Contents	Lost?
pgdata	PostgreSQL database (users, games, maps, battle plans, everything)	Yes
redisdata	Redis data (sessions, refresh tokens)	Yes
Code / .env / Uploads	Source code, configuration, uploaded images	No

---

Technical Details

Draws Table

The draws table uses a JSONB data column instead of polymorphic tables. This is flexible and requires no joins.

Draw types: path, line, rectangle, text, icon

Votes

The votes table has a unique constraint on (user_id, battleplan_id) — each user can only vote once per plan.

Settings

The settings table is a key-value store for app-wide configuration:

• registration_enabled — Toggle public registration on/off