Database Schema Expert

Purpose: Provide instant access to PostgreSQL database schema information via grep-based lookups.

When to use: Whenever you need table structure, columns, constraints, indexes, or relationships before implementing queries or entities.

Schema Access Pattern

Before writing ANY database-related code:

1. Identify table name from task/context
2. Run lookup (see commands below)
3. Analyze structure (columns, types, constraints)
4. Implement based on actual schema

Never assume schema structure - always verify with grep.

Important: The database-schema.sql is here: .claude/skills/database-schema/database-schema.sql

Lookup Commands

Basic Table Structure

bash
1grep -A 20 "CREATE TABLE.*<table_name>" database-schema.sql

Find All Columns for Table

bash
1grep -A 50 "CREATE TABLE.*<table_name>" database-schema.sql | grep -E "^\s+[a-z_]+ (VARCHAR|INTEGER|BIGINT|TIMESTAMP|BOOLEAN|TEXT|NUMERIC|UUID)"

Find Primary Key

bash
1grep -A 30 "CREATE TABLE.*<table_name>" database-schema.sql | grep "PRIMARY KEY"

Find Foreign Keys (Outgoing)

bash
1grep -A 50 "CREATE TABLE.*<table_name>" database-schema.sql | grep "FOREIGN KEY"

Find References TO This Table (Incoming)

bash
1grep "REFERENCES <table_name>" database-schema.sql

Find Indexes

bash
1grep "CREATE INDEX.*ON <table_name>" database-schema.sql

Find Constraints

bash
1grep -A 50 "CREATE TABLE.*<table_name>" database-schema.sql | grep "CONSTRAINT"

Find ALL Tables

bash
1grep "CREATE TABLE" database-schema.sql

Typical Workflow

Example: Implementing UserRepository

bash
1# 1. Check if table exists
2grep "CREATE TABLE.*users" database-schema.sql
3
4# 2. Get full structure
5grep -A 30 "CREATE TABLE users" database-schema.sql
6
7# 3. Identify relationships
8grep "FOREIGN KEY" database-schema.sql | grep users
9grep "REFERENCES users" database-schema.sql
10
11# 4. Check indexes for query optimization
12grep "CREATE INDEX.*ON users" database-schema.sql
13
14# 5. Now implement entity/repository

Schema File Structure

The database-schema.sql in this folder contains:

• Table definitions (CREATE TABLE)
• Primary keys
• Foreign key relationships
• Indexes (CREATE INDEX)
• Constraints (CHECK, UNIQUE)
• Comments (COMMENT ON)

Format: Standard PostgreSQL DDL statements

Multi-Table Queries

Finding related tables:

bash
1# Find all tables that reference 'customers'
2grep "REFERENCES customers" database-schema.sql
3
4# Find all foreign keys FROM orders table
5grep -A 50 "CREATE TABLE orders" database-schema.sql | grep "FOREIGN KEY"

Join candidates:

bash
1# Tables connected to 'orders'
2grep -E "(CREATE TABLE orders|REFERENCES orders)" database-schema.sql

Common Patterns

Check Column Type Before Mapping

bash
1grep -A 20 "CREATE TABLE products" database-schema.sql | grep "price"
2# â†' price NUMERIC(10,2) â†' use BigDecimal in Java

Verify NOT NULL Constraints

bash
1grep -A 30 "CREATE TABLE users" database-schema.sql | grep "NOT NULL"
2# â†' Determines if Java field is @NonNull / Optional<T>

Find Enum-like Constraints

bash
1grep -A 40 "CREATE TABLE orders" database-schema.sql | grep "CHECK"
2# â†' status CHECK (status IN ('PENDING', 'SHIPPED', 'DELIVERED'))

Integration with Code

Spring Boot Entity Mapping:

1. grep table → get columns + types
2. Map PostgreSQL types → Java types:
   • VARCHAR → String
   • INTEGER/BIGINT → Integer/Long
   • TIMESTAMP → LocalDateTime
   • BOOLEAN → Boolean
   • UUID → UUID
   • NUMERIC(p,s) → BigDecimal
3. Map constraints → JPA annotations:
   • PRIMARY KEY → @Id
   • FOREIGN KEY → @ManyToOne / @OneToMany
   • NOT NULL → nullable = false
   • UNIQUE → @Column(unique = true)

Keeping Schema Updated

When schema changes:

1. Export from database:

   bash
   1pg_dump -h localhost -U user -d dbname --schema-only > database-schema.sql

2. Or update manually in database-schema.sql

3. Skill automatically uses latest version

Performance Tips

• Use specific table names in grep (faster than wildcards)
• Pipe multiple greps for complex filters
• Use -A (lines After) for full table definitions
• Use -B (lines Before) for context

Constraints

• Read-only: This skill provides schema information only
• No writes: Does not modify database or schema file
• Local scope: Schema file must be in same directory as SKILL.md

Example Session

User: "Implement CustomerRepository with pagination"

You:
1. grep -A 40 "CREATE TABLE customers" database-schema.sql
   â†' Identify: id (BIGINT), name (VARCHAR), email (VARCHAR), created_at (TIMESTAMP)
   
2. grep "CREATE INDEX.*ON customers" database-schema.sql  
   â†' Found: idx_customers_email, idx_customers_created_at
   
3. Implement:
   - Entity with @Id on id
   - Pageable query on created_at (indexed)
   - Email-based findBy (indexed)

Token Efficiency

This skill is token-light:

• No schema embedded in skill text
• On-demand grep loads only relevant tables
• Schema lookups: 50-200 tokens per query

Compared to embedding full schema: Significant reduction by loading only what's needed per query