Supabase Schema Validator

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

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.