skills.rest
Canhada-Labs
Canhada-LabsOFFICIAL
Explore all skills by this author →
GitHub

data-schema-design

Design and manage PostgreSQL schemas with migration and index strategies.

2|Updated Jun 30, 2026
Search Tags:#database design#schema management#PostgreSQL#retention policy#migration strategy#index strategy
One-click install
npx skills add https://github.com/Canhada-Labs/ceo-orchestration --skill data-schema-design
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: data-schema-design
Source: https://github.com/Canhada-Labs/ceo-orchestration/tree/main/.claude/skills/core/data-schema-design
Command: npx skills add https://github.com/Canhada-Labs/ceo-orchestration --skill data-schema-design

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill requires postgres, prisma, drizzle, kysely, django, golang-migrate, supabase, and includes scripts (resource) and references (resource) and assets (resource) components.

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.

Frequently Asked Questions about data-schema-design

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I design a PostgreSQL schema with proper retention policies and RLS?▼

PostgreSQL schema design requires establishing table naming conventions, defining RLS policy patterns, and implementing retention policies based on usage and regulatory requirements to ensure data integrity. This framework provides guidelines for all three.

What's the best way to manage additive-only PostgreSQL migrations with Drizzle or Prisma?▼

PostgreSQL migration strategy should use idempotent, additive-only migrations to handle enums and tier consistency. This approach works with tools like Drizzle, Prisma, golang-migrate, and Supabase to maintain schema integrity during transitions.

Does this PostgreSQL schema design approach work with Django and Supabase?▼

Yes, the PostgreSQL schema design framework supports execution across Django and Supabase, alongside Prisma, Drizzle, Kysely, and golang-migrate, providing DDL templates and security guidelines compatible with these environments.

How do I prevent schema injection vulnerabilities in PostgreSQL security functions?▼

Preventing PostgreSQL schema injection requires using SECURITY DEFINER functions with explicit search_path settings. This security discipline ensures that database functions execute safely without exposing vulnerability to malicious schema manipulation.

When should I use partial indexes versus composite indexes for PostgreSQL hot-path queries?▼

PostgreSQL index strategy dictates using composite indexes and partial indexes specifically for hot-path queries. Query optimization discipline requires running EXPLAIN ANALYZE to track plan regressions and validate index effectiveness.

How do I create a disaster recovery procedure for PostgreSQL schema and data loss?▼

PostgreSQL disaster recovery procedures outline specific steps for schema and data loss scenarios. The framework defines recovery protocols alongside ETL and ELT pipeline schema discipline to ensure database restoration.

Related Skills

Explore More Software Engineering Skills→
thangvawnthangvawn

database-migrations

Guide database migrations across PostgreSQL, MySQL, and common ORMs.

Community
Advanced
francanetefrancanete
1

postgres-query-optimization

Analyzes slow PostgreSQL queries using EXPLAIN ANALYZE to recommend index and query optimizations.

Community
Advanced
simotasimota
68

Stream

Design batch, streaming, and hybrid data pipelines with quality gates and recovery paths.

Community
Advanced
zero3041zero3041
1

database-migrations

Guide database migrations across PostgreSQL, MySQL, and ORMs.

Community
Advanced