This Claude skill provides comprehensive support for PostgreSQL 14+ database engineering, specializing in schema design, Flyway migration management, and Spring Data JPA query optimization. It streamlines the development of robust data layers by ensuring high-precision financial data handling, efficient entity mapping, and optimized repository queries tailored for complex, high-concurrency platforms.

When should I use postgresql?

postgresql is useful in the following scenarios: • Automating the generation of Flyway migration scripts for schema updates, ensuring out-of-order support and data integrity. • Designing Kotlin or Java entities with Hibernate annotations to ensure seamless mapping between the application layer and PostgreSQL tables. • Optimizing database performance by identifying and resolving N+1 query issues using JOIN FETCH and specialized JPA query patterns. • Implementing high-precision financial calculations and reporting using PostgreSQL-specific types like NUMERIC(19,4) and COALESCE aggregations. • Troubleshooting complex data layer issues, including indexing strategies, constraint violations, and timezone-aware timestamp management.

name	postgresql
description	\|
Use when	Creating or modifying database tables, writing migrations, designing entities,
allowed-tools	Read, Edit, Write, Glob, Grep, Bash

PostgreSQL Skill

PostgreSQL 14+ is the primary database for this casino platform, accessed via Spring Data JPA with Hibernate. All financial operations use NUMERIC(19,4) for precision, IDs are BIGSERIAL, and timestamps use TIMESTAMP WITH TIME ZONE. Flyway manages migrations with out-of-order support enabled.

Quick Start

Create a Migration

-- V20260110120000__add_player_preferences.sql
CREATE TABLE player_preferences (
    id BIGSERIAL PRIMARY KEY,
    player_id BIGINT NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    notification_email BOOLEAN NOT NULL DEFAULT true,
    notification_sms BOOLEAN NOT NULL DEFAULT false,
    preferred_currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_player_preferences_player_id ON player_preferences(player_id);

Map Entity to Table

@Entity
@Table(name = "player_preferences")
data class PlayerPreferences(
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Long? = null,
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "player_id", nullable = false)
    val player: Player,
    
    @Column(name = "preferred_currency", nullable = false, length = 3)
    var preferredCurrency: String = "EUR",
    
    @Column(name = "created_at", nullable = false)
    val createdAt: LocalDateTime = LocalDateTime.now()
)

Key Concepts

SQL Type	Kotlin Type	Usage
`BIGSERIAL`	`Long`	All primary keys
`NUMERIC(19,4)`	`BigDecimal`	Money, balances, amounts
`TIMESTAMP WITH TIME ZONE`	`LocalDateTime`	All datetime fields
`UUID`	`UUID`	External references, tokens
`TEXT`	`String`	Long content, descriptions
`JSONB`	`String`/Custom	Structured flexible data

Common Patterns

Prevent N+1 with JOIN FETCH

When: Loading entities with relationships

@Query("""
    SELECT DISTINCT p FROM Player p
    LEFT JOIN FETCH p.wallet
    LEFT JOIN FETCH p.addresses
    WHERE p.id = :id
""")
fun findByIdWithDetails(@Param("id") id: Long): Optional<Player>

Aggregate with COALESCE

When: Summing values that might be null

@Query("""
    SELECT COALESCE(SUM(t.amount), 0)
    FROM Transaction t
    WHERE t.wallet.player.id = :playerId
    AND t.type = :type
    AND t.status = 'COMPLETED'
""")
fun sumByPlayerIdAndType(
    @Param("playerId") playerId: Long,
    @Param("type") type: TransactionType
): BigDecimal

Related Skills

See the jpa skill for entity mapping and repository patterns
See the spring-boot skill for transaction management and configuration
See the kotlin skill for data class patterns with JPA

postgresql

When & Why to Use This Skill

Use Cases