PaperclipOrg
Claude Agent Skill · by Github

Postgresql Code Review

The postgresql-code-review skill performs expert code reviews specifically for PostgreSQL projects, evaluating code against PostgreSQL best practices and identi

Install
Terminal · npx
$npx skills add https://github.com/github/awesome-copilot --skill postgresql-code-review
Works with Paperclip

How Postgresql Code Review fits into a Paperclip company.

Postgresql Code Review drops into any Paperclip agent that handles this kind of work. Assign it to a specialist inside a pre-configured PaperclipOrg company and the skill becomes available on every heartbeat — no prompt engineering, no tool wiring.

S
SaaS FactoryPaired

Pre-configured AI company — 18 agents, 18 skills, one-time purchase.

$27$59
Explore pack
Source file
SKILL.md212 lines
Expand
---name: postgresql-code-reviewdescription: 'PostgreSQL-specific code review assistant focusing on PostgreSQL best practices, anti-patterns, and unique quality standards. Covers JSONB operations, array usage, custom types, schema design, function optimization, and PostgreSQL-exclusive security features like Row Level Security (RLS).'--- # PostgreSQL Code Review Assistant Expert PostgreSQL code review for ${selection} (or entire project if no selection). Focus on PostgreSQL-specific best practices, anti-patterns, and quality standards that are unique to PostgreSQL. ## 🎯 PostgreSQL-Specific Review Areas ### JSONB Best Practices```sql-- ❌ BAD: Inefficient JSONB usageSELECT * FROM orders WHERE data->>'status' = 'shipped';  -- No index support -- ✅ GOOD: Indexable JSONB queriesCREATE INDEX idx_orders_status ON orders USING gin((data->'status'));SELECT * FROM orders WHERE data @> '{"status": "shipped"}'; -- ❌ BAD: Deep nesting without considerationUPDATE orders SET data = data || '{"shipping":{"tracking":{"number":"123"}}}'; -- ✅ GOOD: Structured JSONB with validationALTER TABLE orders ADD CONSTRAINT valid_status CHECK (data->>'status' IN ('pending', 'shipped', 'delivered'));``` ### Array Operations Review```sql-- ❌ BAD: Inefficient array operationsSELECT * FROM products WHERE 'electronics' = ANY(categories);  -- No index -- ✅ GOOD: GIN indexed array queriesCREATE INDEX idx_products_categories ON products USING gin(categories);SELECT * FROM products WHERE categories @> ARRAY['electronics']; -- ❌ BAD: Array concatenation in loops-- This would be inefficient in a function/procedure -- ✅ GOOD: Bulk array operationsUPDATE products SET categories = categories || ARRAY['new_category']WHERE id IN (SELECT id FROM products WHERE condition);``` ### PostgreSQL Schema Design Review```sql-- ❌ BAD: Not using PostgreSQL featuresCREATE TABLE users (    id INTEGER,    email VARCHAR(255),    created_at TIMESTAMP); -- ✅ GOOD: PostgreSQL-optimized schemaCREATE TABLE users (    id BIGSERIAL PRIMARY KEY,    email CITEXT UNIQUE NOT NULL,  -- Case-insensitive email    created_at TIMESTAMPTZ DEFAULT NOW(),    metadata JSONB DEFAULT '{}',    CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')); -- Add JSONB GIN index for metadata queriesCREATE INDEX idx_users_metadata ON users USING gin(metadata);``` ### Custom Types and Domains```sql-- ❌ BAD: Using generic types for specific dataCREATE TABLE transactions (    amount DECIMAL(10,2),    currency VARCHAR(3),    status VARCHAR(20)); -- ✅ GOOD: PostgreSQL custom typesCREATE TYPE currency_code AS ENUM ('USD', 'EUR', 'GBP', 'JPY');CREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'failed', 'cancelled');CREATE DOMAIN positive_amount AS DECIMAL(10,2) CHECK (VALUE > 0); CREATE TABLE transactions (    amount positive_amount NOT NULL,    currency currency_code NOT NULL,    status transaction_status DEFAULT 'pending');``` ## 🔍 PostgreSQL-Specific Anti-Patterns ### Performance Anti-Patterns- **Avoiding PostgreSQL-specific indexes**: Not using GIN/GiST for appropriate data types- **Misusing JSONB**: Treating JSONB like a simple string field- **Ignoring array operators**: Using inefficient array operations- **Poor partition key selection**: Not leveraging PostgreSQL partitioning effectively ### Schema Design Issues- **Not using ENUM types**: Using VARCHAR for limited value sets- **Ignoring constraints**: Missing CHECK constraints for data validation- **Wrong data types**: Using VARCHAR instead of TEXT or CITEXT- **Missing JSONB structure**: Unstructured JSONB without validation ### Function and Trigger Issues```sql-- ❌ BAD: Inefficient trigger functionCREATE OR REPLACE FUNCTION update_modified_time()RETURNS TRIGGER AS $$BEGIN    NEW.updated_at = NOW();  -- Should use TIMESTAMPTZ    RETURN NEW;END;$$ LANGUAGE plpgsql; -- ✅ GOOD: Optimized trigger functionCREATE OR REPLACE FUNCTION update_modified_time()RETURNS TRIGGER AS $$BEGIN    NEW.updated_at = CURRENT_TIMESTAMP;    RETURN NEW;END;$$ LANGUAGE plpgsql; -- Set trigger to fire only when neededCREATE TRIGGER update_modified_time_trigger    BEFORE UPDATE ON table_name    FOR EACH ROW    WHEN (OLD.* IS DISTINCT FROM NEW.*)    EXECUTE FUNCTION update_modified_time();``` ## 📊 PostgreSQL Extension Usage Review ### Extension Best Practices```sql-- ✅ Check if extension exists before creatingCREATE EXTENSION IF NOT EXISTS "uuid-ossp";CREATE EXTENSION IF NOT EXISTS "pgcrypto";CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- ✅ Use extensions appropriately-- UUID generationSELECT uuid_generate_v4(); -- Password hashingSELECT crypt('password', gen_salt('bf')); -- Fuzzy text matchingSELECT word_similarity('postgres', 'postgre');``` ## 🛡️ PostgreSQL Security Review ### Row Level Security (RLS)```sql-- ✅ GOOD: Implementing RLSALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY; CREATE POLICY user_data_policy ON sensitive_data    FOR ALL TO application_role    USING (user_id = current_setting('app.current_user_id')::INTEGER);``` ### Privilege Management```sql-- ❌ BAD: Overly broad permissionsGRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user; -- ✅ GOOD: Granular permissionsGRANT SELECT, INSERT, UPDATE ON specific_table TO app_user;GRANT USAGE ON SEQUENCE specific_table_id_seq TO app_user;``` ## 🎯 PostgreSQL Code Quality Checklist ### Schema Design- [ ] Using appropriate PostgreSQL data types (CITEXT, JSONB, arrays)- [ ] Leveraging ENUM types for constrained values- [ ] Implementing proper CHECK constraints- [ ] Using TIMESTAMPTZ instead of TIMESTAMP- [ ] Defining custom domains for reusable constraints ### Performance Considerations- [ ] Appropriate index types (GIN for JSONB/arrays, GiST for ranges)- [ ] JSONB queries using containment operators (@>, ?)- [ ] Array operations using PostgreSQL-specific operators- [ ] Proper use of window functions and CTEs- [ ] Efficient use of PostgreSQL-specific functions ### PostgreSQL Features Utilization- [ ] Using extensions where appropriate- [ ] Implementing stored procedures in PL/pgSQL when beneficial- [ ] Leveraging PostgreSQL's advanced SQL features- [ ] Using PostgreSQL-specific optimization techniques- [ ] Implementing proper error handling in functions ### Security and Compliance- [ ] Row Level Security (RLS) implementation where needed- [ ] Proper role and privilege management- [ ] Using PostgreSQL's built-in encryption functions- [ ] Implementing audit trails with PostgreSQL features ## 📝 PostgreSQL-Specific Review Guidelines 1. **Data Type Optimization**: Ensure PostgreSQL-specific types are used appropriately2. **Index Strategy**: Review index types and ensure PostgreSQL-specific indexes are utilized3. **JSONB Structure**: Validate JSONB schema design and query patterns4. **Function Quality**: Review PL/pgSQL functions for efficiency and best practices5. **Extension Usage**: Verify appropriate use of PostgreSQL extensions6. **Performance Features**: Check utilization of PostgreSQL's advanced features7. **Security Implementation**: Review PostgreSQL-specific security features Focus on PostgreSQL's unique capabilities and ensure the code leverages what makes PostgreSQL special rather than treating it as a generic SQL database.