Understanding Database Migrations and Seed Data

After setting up Supabase locally, it's important to understand the database structure that's already in place. The Supabase TDD Boilerplate comes with pre-configured migrations and seed data that set up a solid foundation for organization and team management.

Important Note

There is NO need to copy and paste these migrations and seed data into a production Supabase project. We will show you how to properly manage and apply these changes using the Supabase CLI later in the documentation.

Project Structure

The database migrations and seed data are located in the packages/supabase directory:

packages/supabase/
├── migrations/          # Database migrations
│   ├── 00000000000000_initial_schema.sql
│   ├── 00000000000001_organization_schema.sql
│   └── 00000000000002_team_schema.sql
├── seed.sql            # Seed data for development
└── types/             # Generated TypeScript types
    └── supabase.ts

Existing Migrations

When you run npx supabase start, it applies several migrations automatically. These migrations create tables and set up security rules. Let's look at some key aspects:

Organization and Team Management

The following SQL statements set up the core organization and team management functionality:

---

1. Create Organizations Table

This creates the main organizations table:

CREATE TABLE public.organizations (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

2. Create Organization Members Table

This creates the table for managing organization members and their roles:

CREATE TABLE public.organization_members (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  organization_id uuid REFERENCES public.organizations(id) ON DELETE CASCADE,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(organization_id, user_id)
);

---

3. Create Teams Table

This creates the table for managing teams within organizations:

CREATE TABLE public.teams (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  organization_id uuid REFERENCES public.organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

4. Create Team Members Table

This creates the table for managing team members:

CREATE TABLE public.team_members (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  team_id uuid REFERENCES public.teams(id) ON DELETE CASCADE,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(team_id, user_id)
);

---

5. Row Level Security Policies

These statements enable row level security and create policies for organization and team access:

-- Enable RLS on all tables
ALTER TABLE public.organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.organization_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.team_members ENABLE ROW LEVEL SECURITY;

-- Organization policies
CREATE POLICY "Users can view organizations they are members of"
  ON public.organizations FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.organization_members
      WHERE organization_members.organization_id = organizations.id
      AND organization_members.user_id = auth.uid()
    )
  );

CREATE POLICY "Organization owners can create organizations"
  ON public.organizations FOR INSERT
  WITH CHECK (true);

-- Organization member policies
CREATE POLICY "Users can view organization members"
  ON public.organization_members FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.organization_members
      WHERE organization_members.organization_id = organization_members.organization_id
      AND organization_members.user_id = auth.uid()
    )
  );

-- Team policies
CREATE POLICY "Users can view teams in their organizations"
  ON public.teams FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.organization_members
      WHERE organization_members.organization_id = teams.organization_id
      AND organization_members.user_id = auth.uid()
    )
  );

-- Team member policies
CREATE POLICY "Users can view team members"
  ON public.team_members FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.teams
      JOIN public.organization_members ON organization_members.organization_id = teams.organization_id
      WHERE teams.id = team_members.team_id
      AND organization_members.user_id = auth.uid()
    )
  );

Applying Migrations

To apply migrations to your local Supabase instance:

# Navigate to the Supabase package
cd packages/supabase

# Start Supabase
npx supabase start

# Apply migrations
npx supabase db reset

Generating Types

After applying migrations, generate TypeScript types:

# Generate types
npx supabase gen types typescript --local > types/supabase.ts

Next Steps

1. Set up Supabase locally for development
2. Configure Google Sign-In
3. Run tests to verify your setup