Setting Up the Backend

What You'll Learn

Supabase is your backend in a box. It provides a database for storing data, authentication for user login, and Row-Level Security for data protection.

What Supabase Provides

Database - Like a spreadsheet but more powerful. Stores users, form submissions, content.

Authentication - User sign up, login, password reset, social logins.

Row-Level Security - Controls who can read/write what data. Enforced at the database level.

Deliverable: Design Database Schema

Purpose: Plan your database structure before creating it.

Expert Role: Database Architect

Tools to Use: Supabase MCP (for reference), File system (write schema docs)

Expected Output

• Table schemas with columns and types
• Explanation of each field

Prompt Template

Act as a database architect. Help me design a database for my website.
I need to store:

1. **Contact form submissions**
   - Name, email, message
   - Timestamp
   - Whether I've responded

2. **Newsletter subscribers**
   - Email
   - When they subscribed
   - Whether they've confirmed

3. **Admin users** (for myself and team)
   - Email, name, role
   - When they last logged in

Show me the table structure with data types and explain each field's
purpose.

Deliverable: Create Database Tables

Purpose: Create the actual database tables.

Expert Role: Backend Developer

Tools to Use: Supabase MCP (apply_migration)

Expected Output

• Migration files created
• Tables created in Supabase

Prompt Template

Act as a backend developer. Create the database tables we designed:
- contact_submissions
- newsletter_subscribers
- users (for admin access)

Use Supabase MCP's apply_migration tool so changes are tracked.
Add appropriate data types, constraints, and timestamps.

Show me the migration SQL and confirm when tables are created.

Deliverable: Add RLS Policies

Purpose: Protect your data with Row-Level Security.

Expert Role: Security Engineer

Tools to Use: Supabase MCP (apply_migration, get_advisors)

Expected Output

• RLS enabled on all tables
• Policies created with explanations

Prompt Template

Act as a security engineer. Add Row-Level Security to our tables:

1. **contact_submissions**
   - Anyone can INSERT (submit a form)
   - Only authenticated admins can SELECT (read)
   - Only admins can UPDATE or DELETE

2. **users**
   - Users can read their own row
   - Only admins can modify any row

Use Supabase MCP to apply the policies. Explain each policy in plain
English so I understand what it protects against.