Quickstart

npx supabase migration new create_tasks_table

-- 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: 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
  user_id UUID REFERENCES supasheet.users(id) ON DELETE CASCADE,

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

-- Create indexes
create index idx_tasks_user_id on tasks (user_id);
create index idx_tasks_status on tasks (status);

-- Step 3: Grant access, then enable Row Level Security
-- There's no permission enum to register first — access is a direct
-- GRANT on the table itself, to the native Postgres role(s) that should
-- have it. Revoke the default grants, then grant exactly what the
-- 'user' role needs.
revoke all on table tasks from authenticated, service_role;

grant select, insert, update, delete on table tasks to "user";

ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;

-- Step 4: Create RLS policies
-- The grant above already decided *whether* the 'user' role can attempt
-- an operation; RLS decides *which rows*. Tasks are owned by a single
-- user, so ownership is the only row-level rule needed here.
create policy tasks_select on tasks
  for select
  to authenticated
  using (user_id = auth.uid());

create policy tasks_insert on tasks
  for insert
  to authenticated
  with check (user_id = auth.uid());

create policy tasks_update on tasks
  for update
  to authenticated
  using (user_id = auth.uid())
  with check (user_id = auth.uid());

create policy tasks_delete on tasks
  for delete
  to authenticated
  using (user_id = auth.uid());

-- Step 5: Refresh the meta layer so Supasheet picks up the new table
select supasheet.refresh_metadata();

npx supabase db push

npx supabase gen types typescript --local \
  --schema public --schema supasheet \
  > src/lib/database.types.ts