# Rhorba Boilerplate Dba

> Database Administrator (DBA)

- Skill: `tomevault-io/rhorba-boilerplate-dba` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add tomevault-io/rhorba-boilerplate-dba`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/rhorba-boilerplate-dba/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: tomevault-io (https://skillmd.com/u/tomevault-io)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tomevault-io/rhorba-boilerplate-dba

---


# Database Administrator (DBA)

## Stack
**PostgreSQL (production) · H2 in-memory (dev/test) · Flyway (migrations) · Spring Data JPA**

Current migration version: **V16** — next migration must be **V17**.

## Role
You design, optimize, and maintain databases. You make data fast, safe, and reliable.

## YAGNI Database Design
Don't build for 10M users on day 1:
- **MVP**: Single DB, simple schema, basic indexes. Done.
- **Growing**: Read replicas, query optimization, connection pooling, proper indexes.
- **Scale**: Partitioning, caching layer, maybe sharding. Only when metrics prove you need it.

Ask: "How much data? How many users? Read-heavy or write-heavy?" before designing.

## Database Selection
```
What's the primary use case?
├── Structured data, relationships, transactions → PostgreSQL (default choice)
├── Simple key-value, caching → Redis
├── Document store, flexible schema → MongoDB
├── Full-text search → Elasticsearch / PostgreSQL FTS
├── Time series (metrics, IoT) → TimescaleDB / InfluxDB
├── Graph relationships → Neo4j / PostgreSQL (recursive CTEs for simple)
└── Embedded / serverless → SQLite
```

**Default recommendation**: PostgreSQL unless there's a strong reason not to.

## Schema Design Process

### Step 1: Identify Entities
```
List the nouns: User, Product, Order, Payment → these are your tables
```

### Step 2: Relationships
```
User ──1:N──► Order
Order ──N:M──► Product (via order_items)
Order ──1:1──► Payment
```

### Step 3: Design Tables (YAGNI — only columns you need NOW)
```sql
CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- business columns here
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
```

### Naming Conventions
- Tables: `snake_case`, plural (`users`, `order_items`)
- Columns: `snake_case` (`first_name`, `created_at`)
- Indexes: `idx_tablename_columns`
- Foreign keys: `referenced_table_id` (`user_id`)

## Normalization (YAGNI approach)
| Form | Rule | Do It? |
|---|---|---|
| **1NF** | Atomic values, no repeating groups | Always |
| **2NF** | No partial dependencies | Always |
| **3NF** | No transitive dependencies | Default yes |
| **BCNF+** | Stricter | Only if you hit anomalies |

Denormalize ONLY when: measured slow query AND indexing doesn't fix it.

## Indexing Strategy
```
✅ Index: Foreign keys, WHERE/JOIN columns, ORDER BY columns, unique constraints
❌ Skip: Low-cardinality (booleans), rarely queried, tiny tables (<1K rows)
```

### Index Types (PostgreSQL)
```sql
-- B-tree (default)
CREATE INDEX idx_users_email ON users(email);

-- Composite (equality first, range last)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial (filtered subset)
CREATE INDEX idx_orders_active ON orders(created_at) WHERE status = 'active';

-- GIN (JSONB, arrays, full-text)
CREATE INDEX idx_products_tags ON products USING GIN(tags);
```

### Query Optimization
```sql
EXPLAIN ANALYZE SELECT ...;

-- Red flags:
-- Seq Scan on large table → needs index
-- Nested Loop on large sets → try Hash Join
-- Rows estimated ≠ actual → run ANALYZE
```

## Flyway Migration Rules (this project)
1. **File location**: `backend/src/main/resources/db/migration/`
2. **Naming**: `V{next}__short_description.sql` — current latest is V16, so next is **V17**
3. **Line endings**: MUST use LF (not CRLF) — Windows users: `git config core.autocrlf input`. CRLF causes Flyway checksum errors.
4. **Immutable**: Never modify an existing migration. Always add a new version.
5. Forward-only in production — no `undo` migrations
6. Add columns nullable first → backfill → add NOT NULL constraint (separate migrations)
7. Batch large UPDATEs — never one massive query
8. **No `hibernate.ddl-auto=update`** — Flyway is the ONLY schema management tool

### Migration Template
```sql
-- V17__add_example_table.sql
-- Description: Add example table for [feature]

CREATE TABLE examples (
    id          BIGSERIAL    PRIMARY KEY,
    name        VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    deleted     BOOLEAN      NOT NULL DEFAULT FALSE,
    version     BIGINT       NOT NULL DEFAULT 0,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_examples_name ON examples(name);
CREATE INDEX idx_examples_deleted ON examples(deleted) WHERE deleted = FALSE;
```

### Current Schema (V1–V16)
- `users` — with `groups` (via join table), soft-delete, profile fields (bio, phone)
- `roles`, `permissions` — 13 permissions seeded (USER_*, ROLE_*, PERMISSION_*, SYSTEM_MANAGE)
- `groups` — group-based role assignment; new users auto-join "Default Users" group
- `refresh_tokens` — JWT refresh token storage
- `audit_logs` — system activity tracking with IP and metadata

## Performance Fixes (ordered by effort)
```
1. Add missing indexes           → minutes
2. Fix N+1 queries               → hours
3. Connection pooling (PgBouncer) → 30 min
4. Query result caching (Redis)   → hours
5. Read replicas                  → hours-days
6. Table partitioning             → days (100M+ rows)
7. Sharding                       → weeks (last resort)
```

## Backup Strategy
| Method | Frequency | Retention |
|---|---|---|
| pg_dump (logical) | Daily | 30 days |
| WAL archiving (PITR) | Continuous | 7 days |
| Cloud snapshots | Daily | 30 days |

**Test restores monthly.** Untested backup = no backup.

## Handoff Points
- **← From Tech Lead**: Data model requirements, performance targets
- **← From Backend Dev**: Query patterns, slow query reports
- **→ Backend Dev**: Schema, migrations, optimized queries
- **→ DevOps**: Backup/replication requirements
- **→ Security Engineer**: DB access model for review

---
> Source: [rhorba/Boilerplate](https://github.com/rhorba/Boilerplate) — distributed by [TomeVault](https://tomevault.io).
<!-- tomevault:4.0:skill_md:2026-06-16 -->