data-schema-design

What problem does it solve?

This Skill provides a comprehensive framework for designing, managing, and maintaining PostgreSQL schemas, ensuring data integrity, security, and performance.

Core Features & Use Cases

• Schema Design: Offers guidelines for table, column, and index naming conventions, as well as RLS policy patterns.
• Migration Strategy: Provides principles for idempotent, additive-only migrations, and strategies for handling enums and tier consistency.
• Retention Policy Design: Assists in defining and implementing retention policies for different tables based on their usage and regulatory requirements.
• Index Strategy: Offers guidelines for composite indexes, partial indexes, and index design rules for hot-path queries.
• Security and Safety: Ensures the use of SECURITY DEFINER functions with explicit search_path settings to prevent schema injection vulnerabilities.
• DDL Template: Provides a template for creating new tables with best practices for primary keys, foreign keys, timestamps, and uniqueness constraints.
• Disaster Recovery: Outlines a recovery procedure for schema and data loss scenarios.
• Anti-Patterns: Identifies common anti-patterns to avoid in database design and query optimization.
• Query Optimization Discipline: Emphasizes the use of EXPLAIN ANALYZE for query optimization and the importance of tracking plan regressions.
• ETL/ELT and Pipeline Schema Discipline: Offers a rubric for choosing between batch and streaming models and guidelines for schema discipline in pipeline tables.

Quick Start

Use the data-schema-design skill to review and optimize the schema for your PostgreSQL database.