# Database

> Use when designing PostgreSQL schemas, Flyway migrations, query-plan driven B-Tree indexes, batch operations, and HikariCP connection tuning.

- Skill: `sahilkhan30/database` (Agent Skill)
- Install (CLI): `npx skillmds@latest add sahilkhan30/database`
- Raw SKILL.md: https://api.skillmd.com/api/skills/sahilkhan30/database/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: SahilKhan30 (https://skillmd.com/u/sahilkhan30)
- Updated: 2026-09-21
- Page: https://skillmd.com/skills/sahilkhan30/database

---


# PostgreSQL Database & Indexing Skill

## Purpose
Guide database schema design, Flyway migrations, concurrency controls, query-plan-driven indexing, and batch operations.

---

## 1. Rules of Engagement

### MANDATORY
- Externalize database credentials (`${DB_PASSWORD}`). Never hardcode credentials in application properties or skills.
- Use Flyway SQL migrations (`V1__init_schema.sql`) for schema DDL management.
- Derive composite indexes from actual access patterns, selectivity, and query sort orders (e.g. `WHERE status = ? ORDER BY created_at DESC` -> `CREATE INDEX idx_tx_status_created ON transactions(status, created_at DESC)`).

### CONDITIONAL
- **JDBC Batching**: Configure JDBC batching (`hibernate.jdbc.batch_size=100`, `re-write-batched-inserts=true`) when bulk write workloads exist and are validated by measurements.

---

## 2. Standard Schema Template

```sql
CREATE TABLE transactions (
    id VARCHAR(64) PRIMARY KEY,
    idempotency_key VARCHAR(128),
    merchant_id VARCHAR(64) NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'INR',
    status VARCHAR(32) NOT NULL,
    version INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_merchant_idempotency UNIQUE (merchant_id, idempotency_key)
);

CREATE INDEX idx_tx_merchant_status ON transactions (merchant_id, status, created_at DESC);
```