Supabase Schema Validator

Establish data integrity rules to ensure reliable database operations across the NABIP Association Management System.

When to Use

Activate this skill when:

• Creating or modifying Supabase migration files
• Designing database schemas for members, chapters, events, or courses
• Implementing Row Level Security (RLS) policies
• Validating relationships between hierarchical data (National → State → Local)
• Working with financial transaction tables
• Adding indexes for query optimization

Validation Checklist

Schema Design Principles

1. Member Tables

   • Ensure members table includes: id, email, member_type, status, chapter_id, joined_date
   • Validate membership tier relationships (National, State, Local)
   • Check duplicate detection logic (email uniqueness)
   • Verify engagement scoring fields

2. Chapter Hierarchy

   • Confirm parent_chapter_id for hierarchical structure
   • Validate level field (national, state, local)
   • Check revenue_sharing_percentage constraints
   • Ensure geographic data (region, state, timezone)

3. Event Management

   • Validate event registration capacity constraints
   • Check pricing tier relationships
   • Ensure virtual/hybrid event support fields
   • Verify check-in tracking (QR code support)

4. Financial Tables

   • Enforce transaction_type enums (dues, event, donation)
   • Validate payment_status workflows
   • Check revenue source tracking
   • Ensure audit trail timestamps

5. Row Level Security (RLS)

   • National admins: full access
   • State admins: state chapter + children
   • Chapter admins: specific chapter only
   • Members: own data only

Migration Best Practices

-- Example: Member table with proper constraints
CREATE TABLE IF NOT EXISTS members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  member_type TEXT NOT NULL CHECK (member_type IN ('national', 'state', 'local')),
  status TEXT NOT NULL CHECK (status IN ('active', 'pending', 'inactive', 'suspended')),
  chapter_id UUID REFERENCES chapters(id),
  engagement_score INTEGER DEFAULT 0 CHECK (engagement_score >= 0 AND engagement_score <= 100),
  joined_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  renewal_date TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE members ENABLE ROW LEVEL SECURITY;

-- Example policy for member self-access
CREATE POLICY "Members can view own data"
  ON members FOR SELECT
  USING (auth.uid() = id);

Performance Optimization

• Add indexes on frequently queried columns:
  • chapter_id for hierarchy queries
  • email for lookups
  • status for filtering
  • member_type for segmentation
  • created_at for temporal queries

Common Pitfalls to Avoid

❌ Avoid: Missing foreign key constraints ✅ Use: Explicit REFERENCES with ON DELETE CASCADE/SET NULL

❌ Avoid: Unrestricted RLS policies ✅ Use: Role-based policies tied to auth.jwt()

❌ Avoid: Missing updated_at triggers ✅ Use: Automatic timestamp updates via triggers

Output Format

When validating schemas, provide:

1. ✅ Validation summary (what's correct)
2. ⚠️ Warnings (potential issues)
3. ❌ Errors (must fix)
4. 💡 Optimization suggestions
5. 📝 Migration script (if needed)

Integration with Other Skills

• Works with member-workflow for data model alignment
• Supports rbac-validator for permission checks
• Complements analytics-helper for optimized queries

Best for: Developers working on backend data models, database migrations, or multi-tenant access control in the NABIP AMS.