Quickstart

Build Your First Admin Interface

Let's create a simple task management interface to demonstrate Supasheet's SQL-first approach.

Create a Migration

npx supabase migration new create_tasks_table

This creates a new migration file in /supabase/migrations/.

Define Your Schema

Edit the migration file and add:

begin;
-- Step 1: Create custom types
create type task_status as enum ('pending', 'in_progress', 'completed');
create type task_priority as enum ('low', 'medium', 'high');

-- Step 2: Add permissions to the system
alter type supasheet.app_permission add value 'public.tasks:select';
alter type supasheet.app_permission add value 'public.tasks:insert';
alter type supasheet.app_permission add value 'public.tasks:update';
alter type supasheet.app_permission add value 'public.tasks:delete';
commit;

-- Step 3: Create tasks table
CREATE TABLE public.tasks (
  id UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
  title TEXT NOT NULL,
  description TEXT,
  status task_status DEFAULT 'pending',
  priority task_priority DEFAULT 'medium',
  due_date TIMESTAMPTZ,

  -- User association
  account_id UUID REFERENCES supasheet.accounts(id) ON DELETE CASCADE,

  -- Audit fields
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes
create index idx_tasks_account_id on tasks (account_id);
create index idx_tasks_status on tasks (status);

-- Step 4: Enable Row Level Security with permission checks
ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;

-- Revoke default permissions
revoke all on table tasks from authenticated, service_role;

-- Grant basic permissions
grant select, insert, update, delete on table tasks to authenticated;

-- Create RLS policies with permission checks
create policy tasks_select on tasks
  for select
  to authenticated
  using (account_id = auth.uid() and supasheet.has_permission('public.tasks:select'));

create policy tasks_insert on tasks
  for insert
  to authenticated
  with check (supasheet.has_permission('public.tasks:insert'));

create policy tasks_update on tasks
  for update
  to authenticated
  using (account_id = auth.uid() and supasheet.has_permission('public.tasks:update'))
  with check (account_id = auth.uid() and supasheet.has_permission('public.tasks:update'));

create policy tasks_delete on tasks
  for delete
  to authenticated
  using (account_id = auth.uid() and supasheet.has_permission('public.tasks:delete'));

-- Step 5: Grant permissions to the 'user' role
insert into supasheet.role_permissions (role, permission) values
  ('user', 'public.tasks:select'),
  ('user', 'public.tasks:insert'),
  ('user', 'public.tasks:update'),
  ('user', 'public.tasks:delete');