Database Schema

Copy this database sechma to quickly generate the required database tables.

HINT
If you are using Supabase, you can quickly generate the tables by pasting this into the Supabase SQL Editor.
Log into Supabase > Project > SQL Editor > + New query > paste schema > Run

-- Drop all existing tables
drop table if exists profiles, courses, modules, academic;

-- Create 'profiles' table
create table profiles (
      user_id uuid references auth.users primary key,
      first_name text,
      last_name text,
      course text,
      cohort text,
      role smallint not null default 0,
      updated_at timestamp with time zone not null default now()
);

-- set RLS for 'profiles' table
alter table profiles enable row level security;
create policy "Public profiles are viewable by everyone." on profiles for
      select using (true);
create policy "Users can insert their own profile." on profiles for
      insert with check (auth.uid() = user_id);
create policy "Users can update own profile" on profiles for
      update using (auth.uid() = user_id);

-- Create 'modules' table
create table modules (
      id uuid default uuid_generate_v4() primary key,
      acad_year text not null,
      code text not null,
      name text not null,
      description text not null,
      credit smallint not null,
      pre_req text array,
      preclusion text,
      updated_at timestamp with time zone not null default now()
);

-- Create 'courses' table
create table courses (
      id bigint generated by default as identity primary key,
      course_name text not null,
      cohort text not null,
      grad_requirement text array not null,
      position text array
);

-- set RLS for 'courses' table
alter table courses enable row level security;
create policy "Enable read access for all users" on courses for
      select using (true);

-- Create 'academic' table
create table academic (
      id uuid default uuid_generate_v4() primary key,
      user_id uuid references auth.users not null,
      module uuid references public.modules not null,
      completed boolean not null default false
);

-- set RLS for 'academic' table
alter table academic enable row level security;
create policy "Users can insert their own modules" on academic for
      insert with check (auth.uid() = user_id);
create policy "Users can update their own modules" on academic for
      update using (auth.uid() = user_id);