schema.sql

-- ============================================================
-- Task Tracker - database schema for Supabase
-- Run this ONCE: Supabase Dashboard > SQL Editor > New query >
-- paste everything > Run.
-- ============================================================

-- ---- Tables --------------------------------------------------

-- One row per signed-up person
create table if not exists public.profiles (
  id         uuid primary key references auth.users(id) on delete cascade,
  full_name  text not null,
  email      text,
  created_at timestamptz not null default now()
);

-- People who have been invited but may not have signed in yet
create table if not exists public.invitations (
  id          uuid primary key default gen_random_uuid(),
  email       text not null unique,
  full_name   text not null,
  status      text not null default 'pending'
              check (status in ('pending','accepted')),
  invited_by  uuid references public.profiles(id) on delete set null,
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now()
);

-- Tickets (the tasks people raise)
create table if not exists public.tickets (
  id           uuid primary key default gen_random_uuid(),
  title        text not null,
  description  text default '',
  priority     text not null default 'medium'
               check (priority in ('low','medium','high','urgent')),
  status       text not null default 'open'
               check (status in ('open','done')),
  assignee_id  uuid references public.profiles(id) on delete set null,
  assignee_email text,
  assignee_name  text,
  created_by   uuid references public.profiles(id) on delete set null,
  reopen_count int  not null default 0,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now()
);

-- Upgrade existing projects that already ran an older schema.sql
alter table public.tickets
  add column if not exists assignee_email text,
  add column if not exists assignee_name text;

-- Multiple people can be responsible for one ticket.
create table if not exists public.ticket_assignees (
  id             uuid primary key default gen_random_uuid(),
  ticket_id      uuid not null references public.tickets(id) on delete cascade,
  assignee_id    uuid references public.profiles(id) on delete set null,
  assignee_email text,
  assignee_name  text,
  part_status    text not null default 'open'
                 check (part_status in ('open','done')),
  completed_at   timestamptz,
  created_at     timestamptz not null default now()
);

alter table public.ticket_assignees
  add column if not exists part_status text not null default 'open',
  add column if not exists completed_at timestamptz;

do $$
begin
  alter table public.ticket_assignees
    add constraint ticket_assignees_part_status_check
    check (part_status in ('open','done'));
exception when duplicate_object then null;
end $$;

create unique index if not exists ticket_assignees_ticket_user_idx
  on public.ticket_assignees (ticket_id, assignee_id)
  where assignee_id is not null;

create unique index if not exists ticket_assignees_ticket_email_idx
  on public.ticket_assignees (ticket_id, lower(assignee_email))
  where assignee_email is not null;

-- Backfill older single-assignee tickets into the new multi-assignee table.
insert into public.ticket_assignees (ticket_id, assignee_id, assignee_email, assignee_name, part_status, completed_at)
select t.id, t.assignee_id, t.assignee_email, t.assignee_name,
       case when t.status = 'done' then 'done' else 'open' end,
       case when t.status = 'done' then t.updated_at else null end
from public.tickets t
where (t.assignee_id is not null or t.assignee_email is not null)
  and not exists (
    select 1
    from public.ticket_assignees ta
    where ta.ticket_id = t.id
      and (
        (t.assignee_id is not null and ta.assignee_id = t.assignee_id)
        or (t.assignee_email is not null and lower(ta.assignee_email) = lower(t.assignee_email))
      )
  );

-- Comments on a ticket
create table if not exists public.comments (
  id         uuid primary key default gen_random_uuid(),
  ticket_id  uuid not null references public.tickets(id) on delete cascade,
  author_id  uuid references public.profiles(id) on delete set null,
  body       text not null,
  created_at timestamptz not null default now()
);

-- Screenshot metadata (the image files live in Storage)
create table if not exists public.attachments (
  id           uuid primary key default gen_random_uuid(),
  ticket_id    uuid not null references public.tickets(id) on delete cascade,
  storage_path text not null,
  file_name    text,
  created_at   timestamptz not null default now()
);

-- Tickets a user has picked into their personal bucket.
-- bucket_date is kept for older databases, but the app treats this as persistent.
create table if not exists public.today_buckets (
  id          uuid primary key default gen_random_uuid(),
  ticket_id   uuid not null references public.tickets(id) on delete cascade,
  user_id     uuid not null references public.profiles(id) on delete cascade,
  bucket_date date not null default current_date,
  created_at  timestamptz not null default now(),
  unique (ticket_id, user_id, bucket_date)
);

-- ---- Triggers ------------------------------------------------

-- Create a profile row automatically whenever someone signs up
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.profiles (id, full_name, email)
  values (
    new.id,
    coalesce(new.raw_user_meta_data->>'full_name', split_part(new.email, '@', 1)),
    new.email
  )
  on conflict (id) do nothing;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

-- Keep tickets.updated_at fresh on every edit
create or replace function public.touch_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists tickets_touch on public.tickets;
create trigger tickets_touch
  before update on public.tickets
  for each row execute function public.touch_updated_at();

drop trigger if exists invitations_touch on public.invitations;
create trigger invitations_touch
  before update on public.invitations
  for each row execute function public.touch_updated_at();

-- Browser inserts cannot create tickets on behalf of another person.
create or replace function public.set_ticket_creator()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  if auth.uid() is not null then
    new.created_by = auth.uid();
  end if;
  return new;
end;
$$;

drop trigger if exists tickets_set_creator on public.tickets;
create trigger tickets_set_creator
  before insert on public.tickets
  for each row execute function public.set_ticket_creator();

create or replace function public.can_assign_ticket(ticket_uuid uuid)
returns boolean
language sql
stable
security definer set search_path = public
as $$
  select exists (
    select 1
    from public.tickets
    where id = ticket_uuid
      and created_by = auth.uid()
  );
$$;

create or replace function public.can_act_on_ticket(ticket_uuid uuid)
returns boolean
language sql
stable
security definer set search_path = public
as $$
  select exists (
    select 1
    from public.tickets
    where id = ticket_uuid
      and created_by = auth.uid()
  )
  or exists (
    select 1
    from public.ticket_assignees
    where ticket_id = ticket_uuid
      and assignee_id = auth.uid()
  );
$$;

create or replace function public.can_update_ticket_assignee(assignee_row uuid)
returns boolean
language sql
stable
security definer set search_path = public
as $$
  select exists (
    select 1
    from public.ticket_assignees ta
    join public.tickets t on t.id = ta.ticket_id
    where ta.id = assignee_row
      and (t.created_by = auth.uid() or ta.assignee_id = auth.uid())
  );
$$;

create or replace function public.is_ticket_assignee(ticket_uuid uuid, user_uuid uuid)
returns boolean
language sql
stable
security definer set search_path = public
as $$
  select exists (
    select 1
    from public.ticket_assignees
    where ticket_id = ticket_uuid
      and assignee_id = user_uuid
  );
$$;

create or replace function public.protect_ticket_update()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  -- Supabase SQL Editor, service-role jobs, and trigger-driven maintenance
  -- do not have an auth.uid(). Let those database-level operations run.
  -- Browser users are still controlled by RLS plus the checks below.
  if auth.uid() is null then
    return new;
  end if;

  if new.status = 'done'
     and exists (
       select 1
       from public.ticket_assignees
       where ticket_id = old.id
     )
     and exists (
       select 1
       from public.ticket_assignees
       where ticket_id = old.id
         and part_status <> 'done'
     ) then
    raise exception 'All assignees must complete their work before the ticket can be marked done';
  end if;

  if old.created_by = auth.uid() then
    return new;
  end if;

  if public.can_act_on_ticket(old.id)
     and new.title = old.title
     and coalesce(new.description, '') = coalesce(old.description, '')
     and new.priority = old.priority
     and coalesce(new.assignee_id::text, '') = coalesce(old.assignee_id::text, '')
     and coalesce(new.assignee_email, '') = coalesce(old.assignee_email, '')
     and coalesce(new.assignee_name, '') = coalesce(old.assignee_name, '')
     and new.created_by is not distinct from old.created_by then
    return new;
  end if;

  raise exception 'Only the ticket creator can edit ticket details or assignees';
end;
$$;

drop trigger if exists tickets_protect_update on public.tickets;
create trigger tickets_protect_update
  before update on public.tickets
  for each row execute function public.protect_ticket_update();

create or replace function public.protect_ticket_assignee_update()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  if public.can_assign_ticket(old.ticket_id) then
    return new;
  end if;

  if old.assignee_id = auth.uid()
     and new.ticket_id = old.ticket_id
     and new.assignee_id is not distinct from old.assignee_id
     and coalesce(new.assignee_email, '') = coalesce(old.assignee_email, '')
     and coalesce(new.assignee_name, '') = coalesce(old.assignee_name, '') then
    return new;
  end if;

  raise exception 'Only the ticket creator can edit assignees';
end;
$$;

drop trigger if exists ticket_assignees_protect_update on public.ticket_assignees;
create trigger ticket_assignees_protect_update
  before update on public.ticket_assignees
  for each row execute function public.protect_ticket_assignee_update();

-- The ticket status follows assignee parts:
-- any pending assignee keeps the ticket open; all done moves it to done.
create or replace function public.sync_ticket_status_from_parts()
returns trigger
language plpgsql
security definer set search_path = public
as $$
declare
  affected_ticket uuid;
  next_status text;
begin
  affected_ticket := coalesce(new.ticket_id, old.ticket_id);

  if affected_ticket is null then
    return null;
  end if;

  if not exists (
    select 1 from public.ticket_assignees where ticket_id = affected_ticket
  ) then
    return null;
  end if;

  if exists (
    select 1
    from public.ticket_assignees
    where ticket_id = affected_ticket
      and part_status <> 'done'
  ) then
    next_status := 'open';
  else
    next_status := 'done';
  end if;

  update public.tickets
     set status = next_status
   where id = affected_ticket
     and status <> next_status;

  return null;
end;
$$;

drop trigger if exists ticket_assignees_sync_ticket_status on public.ticket_assignees;
create trigger ticket_assignees_sync_ticket_status
  after insert or update or delete on public.ticket_assignees
  for each row execute function public.sync_ticket_status_from_parts();

-- Repair existing data if any multi-assignee ticket was previously closed early.
update public.tickets t
   set status = 'open'
 where status = 'done'
   and exists (
     select 1 from public.ticket_assignees ta where ta.ticket_id = t.id
   )
   and exists (
     select 1
     from public.ticket_assignees ta
     where ta.ticket_id = t.id
       and ta.part_status <> 'done'
   );

-- When an invited person signs in for the first time, attach all tickets
-- previously assigned to their email address to their real profile.
create or replace function public.accept_pending_assignments()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  update public.tickets
     set assignee_id = new.id,
         assignee_email = lower(new.email),
         assignee_name = new.full_name
   where assignee_email is not null
     and lower(assignee_email) = lower(new.email)
     and (assignee_id is null or assignee_id = new.id);

  update public.ticket_assignees
     set assignee_id = new.id,
         assignee_email = lower(new.email),
         assignee_name = new.full_name
   where assignee_email is not null
     and lower(assignee_email) = lower(new.email)
     and (assignee_id is null or assignee_id = new.id);

  update public.invitations
     set status = 'accepted',
         full_name = new.full_name
   where lower(email) = lower(new.email);

  return new;
end;
$$;

drop trigger if exists profiles_accept_pending_assignments on public.profiles;
create trigger profiles_accept_pending_assignments
  after insert or update of email, full_name on public.profiles
  for each row execute function public.accept_pending_assignments();

-- Public ticket links use this read-only function.
-- It returns only the ticket whose id is in the link; normal writes still require login.
create or replace function public.get_public_ticket(ticket_uuid uuid)
returns jsonb
language sql
stable
security definer set search_path = public
as $$
  select jsonb_build_object(
    'ticket',
      to_jsonb(t) ||
      jsonb_build_object(
        'comments', jsonb_build_array(jsonb_build_object(
          'count', (select count(*) from public.comments c where c.ticket_id = t.id)
        )),
        'attachments', coalesce((
          select jsonb_agg(to_jsonb(a) order by a.created_at)
          from public.attachments a
          where a.ticket_id = t.id
        ), '[]'::jsonb)
      ),
    'assignees', coalesce((
      select jsonb_agg(to_jsonb(ta) order by ta.created_at)
      from public.ticket_assignees ta
      where ta.ticket_id = t.id
    ), '[]'::jsonb),
    'comments', coalesce((
      select jsonb_agg(to_jsonb(c) order by c.created_at)
      from public.comments c
      where c.ticket_id = t.id
    ), '[]'::jsonb),
    'profiles', coalesce((
      select jsonb_agg(jsonb_build_object(
        'id', p.id,
        'full_name', p.full_name
      ))
      from public.profiles p
      where p.id in (
        select t.created_by
        union
        select ta.assignee_id
        from public.ticket_assignees ta
        where ta.ticket_id = t.id
        union
        select c.author_id
        from public.comments c
        where c.ticket_id = t.id
      )
    ), '[]'::jsonb)
  )
  from public.tickets t
  where t.id = ticket_uuid;
$$;

grant execute on function public.get_public_ticket(uuid) to anon, authenticated;

-- ---- Row Level Security -------------------------------------
-- Any signed-in teammate can see every ticket in this organization.
-- Ticket creators can edit details, assignees, screenshots, and delete tickets.
-- Ticket creators and assignees can comment and update status.
-- Nobody who is not signed in can see anything.

alter table public.profiles    enable row level security;
alter table public.invitations enable row level security;
alter table public.tickets     enable row level security;
alter table public.ticket_assignees enable row level security;
alter table public.comments    enable row level security;
alter table public.attachments enable row level security;
alter table public.today_buckets enable row level security;

drop policy if exists "profiles read"        on public.profiles;
drop policy if exists "profiles insert self" on public.profiles;
drop policy if exists "profiles update self" on public.profiles;
create policy "profiles read"        on public.profiles for select to authenticated using (true);
create policy "profiles insert self" on public.profiles for insert to authenticated with check (id = auth.uid());
create policy "profiles update self" on public.profiles for update to authenticated using (id = auth.uid());

drop policy if exists "invitations all" on public.invitations;
create policy "invitations all" on public.invitations for all to authenticated using (true) with check (true);

drop policy if exists "tickets all" on public.tickets;
drop policy if exists "tickets read" on public.tickets;
drop policy if exists "tickets insert" on public.tickets;
drop policy if exists "tickets update creator or assignee status" on public.tickets;
drop policy if exists "tickets delete creator" on public.tickets;
create policy "tickets read" on public.tickets for select to authenticated using (true);
create policy "tickets insert" on public.tickets for insert to authenticated with check (created_by = auth.uid());
create policy "tickets update creator or assignee status"
  on public.tickets for update to authenticated
  using (public.can_act_on_ticket(id))
  with check (public.can_act_on_ticket(id));
create policy "tickets delete creator"
  on public.tickets for delete to authenticated
  using (created_by = auth.uid());

drop policy if exists "ticket assignees all" on public.ticket_assignees;
drop policy if exists "ticket assignees read" on public.ticket_assignees;
drop policy if exists "ticket assignees insert creator" on public.ticket_assignees;
drop policy if exists "ticket assignees delete creator" on public.ticket_assignees;
create policy "ticket assignees read" on public.ticket_assignees for select to authenticated using (true);
create policy "ticket assignees insert creator"
  on public.ticket_assignees for insert to authenticated
  with check (public.can_assign_ticket(ticket_id));
drop policy if exists "ticket assignees update creator or self" on public.ticket_assignees;
create policy "ticket assignees update creator or self"
  on public.ticket_assignees for update to authenticated
  using (public.can_update_ticket_assignee(id))
  with check (public.can_update_ticket_assignee(id));
create policy "ticket assignees delete creator"
  on public.ticket_assignees for delete to authenticated
  using (public.can_assign_ticket(ticket_id));

drop policy if exists "comments read"       on public.comments;
drop policy if exists "comments insert"     on public.comments;
drop policy if exists "comments delete own" on public.comments;
create policy "comments read" on public.comments for select to authenticated using (true);
create policy "comments insert"
  on public.comments for insert to authenticated
  with check (author_id = auth.uid() and public.can_act_on_ticket(ticket_id));
create policy "comments delete own" on public.comments for delete to authenticated using (author_id = auth.uid());

drop policy if exists "attachments all" on public.attachments;
drop policy if exists "attachments read" on public.attachments;
drop policy if exists "attachments insert creator" on public.attachments;
drop policy if exists "attachments delete creator" on public.attachments;
create policy "attachments read" on public.attachments for select to authenticated using (true);
create policy "attachments insert creator"
  on public.attachments for insert to authenticated
  with check (public.can_assign_ticket(ticket_id));
create policy "attachments delete creator"
  on public.attachments for delete to authenticated
  using (public.can_assign_ticket(ticket_id));

drop policy if exists "today buckets read" on public.today_buckets;
drop policy if exists "today buckets insert own assignee" on public.today_buckets;
drop policy if exists "today buckets update own assignee" on public.today_buckets;
drop policy if exists "today buckets delete own" on public.today_buckets;
create policy "today buckets read" on public.today_buckets for select to authenticated using (true);
create policy "today buckets insert own assignee"
  on public.today_buckets for insert to authenticated
  with check (
    user_id = auth.uid()
    and public.is_ticket_assignee(ticket_id, auth.uid())
  );
create policy "today buckets update own assignee"
  on public.today_buckets for update to authenticated
  using (user_id = auth.uid())
  with check (
    user_id = auth.uid()
    and public.is_ticket_assignee(ticket_id, auth.uid())
  );
create policy "today buckets delete own"
  on public.today_buckets for delete to authenticated
  using (user_id = auth.uid());

-- ---- Live updates -------------------------------------------
-- Lets every open browser refresh the board instantly.

do $$
begin
  alter publication supabase_realtime add table public.tickets;
exception when duplicate_object then null;
end $$;

do $$
begin
  alter publication supabase_realtime add table public.ticket_assignees;
exception when duplicate_object then null;
end $$;

do $$
begin
  alter publication supabase_realtime add table public.invitations;
exception when duplicate_object then null;
end $$;

do $$
begin
  alter publication supabase_realtime add table public.comments;
exception when duplicate_object then null;
end $$;

do $$
begin
  alter publication supabase_realtime add table public.attachments;
exception when duplicate_object then null;
end $$;

do $$
begin
  alter publication supabase_realtime add table public.today_buckets;
exception when duplicate_object then null;
end $$;

-- ---- Storage bucket for screenshots -------------------------

insert into storage.buckets (id, name, public)
values ('screenshots', 'screenshots', true)
on conflict (id) do nothing;

drop policy if exists "screenshots read"   on storage.objects;
drop policy if exists "screenshots write"  on storage.objects;
drop policy if exists "screenshots delete" on storage.objects;
create policy "screenshots read"   on storage.objects for select using (bucket_id = 'screenshots');
create policy "screenshots write"  on storage.objects for insert to authenticated with check (bucket_id = 'screenshots');
create policy "screenshots delete" on storage.objects for delete to authenticated using (bucket_id = 'screenshots');

-- Done. You can close the SQL editor.