{"kind":"Skill","metadata":{"namespace":"community","name":"postgresql-code-review","version":"0.1.0"},"spec":{"description":"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).","files":{"SKILL.md":"---\nname: postgresql-code-review\ndescription: '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).'\n---\n\n# PostgreSQL Code Review Assistant\n\nExpert 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.\n\n## 🎯 PostgreSQL-Specific Review Areas\n\n### JSONB Best Practices\n```sql\n-- ❌ BAD: Inefficient JSONB usage\nSELECT * FROM orders WHERE data-\u003e\u003e'status' = 'shipped';  -- No index support\n\n-- ✅ GOOD: Indexable JSONB queries\nCREATE INDEX idx_orders_status ON orders USING gin((data-\u003e'status'));\nSELECT * FROM orders WHERE data @\u003e '{\"status\": \"shipped\"}';\n\n-- ❌ BAD: Deep nesting without consideration\nUPDATE orders SET data = data || '{\"shipping\":{\"tracking\":{\"number\":\"123\"}}}';\n\n-- ✅ GOOD: Structured JSONB with validation\nALTER TABLE orders ADD CONSTRAINT valid_status \nCHECK (data-\u003e\u003e'status' IN ('pending', 'shipped', 'delivered'));\n```\n\n### Array Operations Review\n```sql\n-- ❌ BAD: Inefficient array operations\nSELECT * FROM products WHERE 'electronics' = ANY(categories);  -- No index\n\n-- ✅ GOOD: GIN indexed array queries\nCREATE INDEX idx_products_categories ON products USING gin(categories);\nSELECT * FROM products WHERE categories @\u003e ARRAY['electronics'];\n\n-- ❌ BAD: Array concatenation in loops\n-- This would be inefficient in a function/procedure\n\n-- ✅ GOOD: Bulk array operations\nUPDATE products SET categories = categories || ARRAY['new_category']\nWHERE id IN (SELECT id FROM products WHERE condition);\n```\n\n### PostgreSQL Schema Design Review\n```sql\n-- ❌ BAD: Not using PostgreSQL features\nCREATE TABLE users (\n    id INTEGER,\n    email VARCHAR(255),\n    created_at TIMESTAMP\n);\n\n-- ✅ GOOD: PostgreSQL-optimized schema\nCREATE TABLE users (\n    id BIGSERIAL PRIMARY KEY,\n    email CITEXT UNIQUE NOT NULL,  -- Case-insensitive email\n    created_at TIMESTAMPTZ DEFAULT NOW(),\n    metadata JSONB DEFAULT '{}',\n    CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$')\n);\n\n-- Add JSONB GIN index for metadata queries\nCREATE INDEX idx_users_metadata ON users USING gin(metadata);\n```\n\n### Custom Types and Domains\n```sql\n-- ❌ BAD: Using generic types for specific data\nCREATE TABLE transactions (\n    amount DECIMAL(10,2),\n    currency VARCHAR(3),\n    status VARCHAR(20)\n);\n\n-- ✅ GOOD: PostgreSQL custom types\nCREATE TYPE currency_code AS ENUM ('USD', 'EUR', 'GBP', 'JPY');\nCREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'failed', 'cancelled');\nCREATE DOMAIN positive_amount AS DECIMAL(10,2) CHECK (VALUE \u003e 0);\n\nCREATE TABLE transactions (\n    amount positive_amount NOT NULL,\n    currency currency_code NOT NULL,\n    status transaction_status DEFAULT 'pending'\n);\n```\n\n## 🔍 PostgreSQL-Specific Anti-Patterns\n\n### Performance Anti-Patterns\n- **Avoiding PostgreSQL-specific indexes**: Not using GIN/GiST for appropriate data types\n- **Misusing JSONB**: Treating JSONB like a simple string field\n- **Ignoring array operators**: Using inefficient array operations\n- **Poor partition key selection**: Not leveraging PostgreSQL partitioning effectively\n\n### Schema Design Issues\n- **Not using ENUM types**: Using VARCHAR for limited value sets\n- **Ignoring constraints**: Missing CHECK constraints for data validation\n- **Wrong data types**: Using VARCHAR instead of TEXT or CITEXT\n- **Missing JSONB structure**: Unstructured JSONB without validation\n\n### Function and Trigger Issues\n```sql\n-- ❌ BAD: Inefficient trigger function\nCREATE OR REPLACE FUNCTION update_modified_time()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = NOW();  -- Should use TIMESTAMPTZ\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\n-- ✅ GOOD: Optimized trigger function\nCREATE OR REPLACE FUNCTION update_modified_time()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = CURRENT_TIMESTAMP;\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\n-- Set trigger to fire only when needed\nCREATE TRIGGER update_modified_time_trigger\n    BEFORE UPDATE ON table_name\n    FOR EACH ROW\n    WHEN (OLD.* IS DISTINCT FROM NEW.*)\n    EXECUTE FUNCTION update_modified_time();\n```\n\n## 📊 PostgreSQL Extension Usage Review\n\n### Extension Best Practices\n```sql\n-- ✅ Check if extension exists before creating\nCREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";\nCREATE EXTENSION IF NOT EXISTS \"pgcrypto\";\nCREATE EXTENSION IF NOT EXISTS \"pg_trgm\";\n\n-- ✅ Use extensions appropriately\n-- UUID generation\nSELECT uuid_generate_v4();\n\n-- Password hashing\nSELECT crypt('password', gen_salt('bf'));\n\n-- Fuzzy text matching\nSELECT word_similarity('postgres', 'postgre');\n```\n\n## 🛡️ PostgreSQL Security Review\n\n### Row Level Security (RLS)\n```sql\n-- ✅ GOOD: Implementing RLS\nALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;\n\nCREATE POLICY user_data_policy ON sensitive_data\n    FOR ALL TO application_role\n    USING (user_id = current_setting('app.current_user_id')::INTEGER);\n```\n\n### Privilege Management\n```sql\n-- ❌ BAD: Overly broad permissions\nGRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;\n\n-- ✅ GOOD: Granular permissions\nGRANT SELECT, INSERT, UPDATE ON specific_table TO app_user;\nGRANT USAGE ON SEQUENCE specific_table_id_seq TO app_user;\n```\n\n## 🎯 PostgreSQL Code Quality Checklist\n\n### Schema Design\n- [ ] Using appropriate PostgreSQL data types (CITEXT, JSONB, arrays)\n- [ ] Leveraging ENUM types for constrained values\n- [ ] Implementing proper CHECK constraints\n- [ ] Using TIMESTAMPTZ instead of TIMESTAMP\n- [ ] Defining custom domains for reusable constraints\n\n### Performance Considerations\n- [ ] Appropriate index types (GIN for JSONB/arrays, GiST for ranges)\n- [ ] JSONB queries using containment operators (@\u003e, ?)\n- [ ] Array operations using PostgreSQL-specific operators\n- [ ] Proper use of window functions and CTEs\n- [ ] Efficient use of PostgreSQL-specific functions\n\n### PostgreSQL Features Utilization\n- [ ] Using extensions where appropriate\n- [ ] Implementing stored procedures in PL/pgSQL when beneficial\n- [ ] Leveraging PostgreSQL's advanced SQL features\n- [ ] Using PostgreSQL-specific optimization techniques\n- [ ] Implementing proper error handling in functions\n\n### Security and Compliance\n- [ ] Row Level Security (RLS) implementation where needed\n- [ ] Proper role and privilege management\n- [ ] Using PostgreSQL's built-in encryption functions\n- [ ] Implementing audit trails with PostgreSQL features\n\n## 📝 PostgreSQL-Specific Review Guidelines\n\n1. **Data Type Optimization**: Ensure PostgreSQL-specific types are used appropriately\n2. **Index Strategy**: Review index types and ensure PostgreSQL-specific indexes are utilized\n3. **JSONB Structure**: Validate JSONB schema design and query patterns\n4. **Function Quality**: Review PL/pgSQL functions for efficiency and best practices\n5. **Extension Usage**: Verify appropriate use of PostgreSQL extensions\n6. **Performance Features**: Check utilization of PostgreSQL's advanced features\n7. **Security Implementation**: Review PostgreSQL-specific security features\n\nFocus on PostgreSQL's unique capabilities and ensure the code leverages what makes PostgreSQL special rather than treating it as a generic SQL database.\n"},"import":{"commit_sha":"541b7819d8c3545c6df122491af4fa1eae415779","imported_at":"2026-05-18T20:05:35Z","license_text":"MIT License\n\nCopyright GitHub, Inc.\n\nPermission is hereby granted, free of charge, to any person obtaining a copy\nof this software and associated documentation files (the \"Software\"), to deal\nin the Software without restriction, including without limitation the rights\nto use, copy, modify, merge, publish, distribute, sublicense, and/or sell\ncopies of the Software, and to permit persons to whom the Software is\nfurnished to do so, subject to the following conditions:\n\nThe above copyright notice and this permission notice shall be included in all\ncopies or substantial portions of the Software.\n\nTHE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\nIMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,\nFITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE\nAUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER\nLIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,\nOUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE\nSOFTWARE.","owner":"github","repo":"github/awesome-copilot","source_url":"https://github.com/github/awesome-copilot/tree/541b7819d8c3545c6df122491af4fa1eae415779/plugins/database-data-management/skills/postgresql-code-review"}},"content_hash":[100,7,201,182,63,204,48,255,42,25,51,206,254,109,232,6,185,221,231,161,81,176,141,249,184,11,113,183,147,30,54,144],"trust_level":"unsigned","yanked":false}