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, membertype, status, chapterid, joined_date - Validate membership tier relationships (National, State, Local) - Check duplicate detection logic (email uniqueness) - Verify engagement scoring fields

1. Chapter Hierarchy

- Confirm parentchapterid for hierarchical structure - Validate level field (national, state, local) - Check revenuesharingpercentage constraints - Ensure geographic data (region, state, timezone)

1. Event Management

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

1. Financial Tables

- Enforce transactiontype enums (dues, event, donation) - Validate paymentstatus workflows - Check revenue source tracking - Ensure audit trail timestamps

1. 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:

- chapterid for hierarchy queries - email for lookups - status for filtering - membertype 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.