vanman2024 Schema Validation
Comprehensive database schema validation for Supabase/PostgreSQL projects. Validates SQL syntax, naming conventions, constraints, indexes, and RLS policies before deployment.
Instructions
1. Run Full Validation
cd /path/to/project
bash /home/vanman2025/Projects/ai-dev-marketplace/plugins/supabase/skills/schema-validation/scripts/full-validation.sh <schema-file-or-directory>
2. Run Individual Validations
SQL Syntax Validation:
bash scripts/validate-sql-syntax.sh <sql-file>
Naming Convention Validation:
bash scripts/validate-naming.sh <sql-file>
Constraint Validation:
bash scripts/validate-constraints.sh <sql-file>
Index Validation:
bash scripts/validate-indexes.sh <sql-file>
RLS Policy Validation:
bash scripts/validate-rls.sh <sql-file>
3. Generate Validation Report
The full validation script generates a detailed markdown report showing:
- Validation results for each check
- Issues found with severity levels (ERROR, WARNING, INFO)
- Recommendations for fixes
- Summary statistics
4. Configure Validation Rules
Customize validation rules by editing:
templates/validation-rules.json
templates/naming-conventions.json
Examples
Example 1: Validate Migration Before Deployment
# Validate a new migration file
bash scripts/full-validation.sh supabase/migrations/20250126_add_users_table.sql
# Review the generated report
cat validation-report.md
Example 2: Validate Entire Schema Directory
# Validate all migration files
bash scripts/full-validation.sh supabase/migrations/
# Check for common issues
grep "ERROR" validation-report.md
Example 3: CI/CD Integration
# Add to .github/workflows/validate-schema.yml
- name: Validate Database Schema
run: |
bash plugins/supabase/skills/schema-validation/scripts/full-validation.sh supabase/migrations/
if grep -q "ERROR" validation-report.md; then
exit 1
fi
Validation Checks
SQL Syntax (validate-sql-syntax.sh)
- PostgreSQL syntax compliance
- Reserved keyword usage
- Statement termination
- Data type validity
Naming Conventions (validate-naming.sh)
- Lowercase with underscores (snake_case)
- Plural table names
- Singular column names
- Constraint naming patterns (fk_, uq_, ck_, pk_)
- Index naming patterns (idx_, uidx_)
Constraints (validate-constraints.sh)
- Primary key existence
- Foreign key references
- Unique constraint usage
- Check constraint validity
- NOT NULL enforcement
Indexes (validate-indexes.sh)
- Index coverage for foreign keys
- Index coverage for RLS policy columns
- Duplicate index detection
- Index naming conventions
RLS Policies (validate-rls.sh)
- RLS enabled on public tables
- Policy existence for CRUD operations
- Role specification (authenticated, anon)
- Performance optimization (proper indexing)
Requirements
- PostgreSQL client tools (psql) for syntax validation
- Bash 4.0+ for script execution
- Read access to schema files
- Write access for generating reports
Best Practices
- Run validation before every migration
- Fix ERRORs immediately - these will cause deployment failures
- Address WARNINGs - these indicate potential issues
- Review INFO items - these are suggestions for improvement
- Keep validation rules updated as your schema evolves
- Integrate into CI/CD to prevent invalid schemas from being deployed
Plugin: supabase Version: 1.0.0 Last Updated: 2025-01-26