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

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);