tasq/supabase/migrations/20260927120000_add_programmer_tasks.sql

-- Programmer Tasks — a separate work-log / time-tracking module for the
-- `programmer` role. Distinct from the IT help-desk `tasks` feature: no office/
-- ticket/it_staff coupling, its own dev-flavored category vocabulary, a single
-- assignee, and pause/resume tracked as activity-log events for time attribution.
-- Designed as the seed of a future Project Management workflow (projects/subtasks
-- can be added later without touching the help-desk `tasks` feature).

-- Category vocabulary (TitleCase display strings, matching the existing enum
-- convention used by request_type / request_category).
create type programmer_task_category as enum (
  'Software Support',
  'Software Development',
  'Software Enhancement',
  'Bug Fix',
  'Meeting',
  'Facilitate Event',
  'Others'
);

create table if not exists programmer_tasks (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  description text,
  category programmer_task_category not null,
  status text not null default 'queued',
  priority int not null default 1,
  assignee_id uuid references profiles(id),
  creator_id uuid references profiles(id),
  created_at timestamptz not null default now(),
  started_at timestamptz,
  completed_at timestamptz,
  cancelled_at timestamptz,
  cancellation_reason text,
  updated_at timestamptz not null default now()
);

create index if not exists idx_programmer_tasks_assignee_id on programmer_tasks(assignee_id);
create index if not exists idx_programmer_tasks_status on programmer_tasks(status);

-- Keep updated_at fresh on every write (dedicated function to avoid clashing
-- with any generic trigger function elsewhere).
create or replace function set_programmer_tasks_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

drop trigger if exists trg_programmer_tasks_updated_at on programmer_tasks;
create trigger trg_programmer_tasks_updated_at
  before update on programmer_tasks
  for each row execute function set_programmer_tasks_updated_at();

-- Activity log — mirrors task_activity_logs. Records created/started/paused/
-- resumed/completed/cancelled/assigned/reassigned events. Pause/resume events
-- drive the effective-worked-duration math (see lib/utils/task_duration.dart).
create table if not exists programmer_task_activity_logs (
  id uuid primary key default gen_random_uuid(),
  task_id uuid not null references programmer_tasks(id) on delete cascade,
  actor_id uuid references profiles(id),
  action_type text not null,
  meta jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_programmer_task_activity_logs_task_id
  on programmer_task_activity_logs(task_id);

-- ---------------------------------------------------------------------------
-- Row Level Security
-- ---------------------------------------------------------------------------
-- Programmer tasks are a programmer-team tool: admins and programmers have full
-- access; a row's assignee or creator can always see/act on their own task.

alter table programmer_tasks enable row level security;
alter table programmer_task_activity_logs enable row level security;

drop policy if exists "programmer_tasks_select" on programmer_tasks;
create policy "programmer_tasks_select" on programmer_tasks
  for select to authenticated
  using (
    assignee_id = auth.uid()
    or creator_id = auth.uid()
    or exists (
      select 1 from profiles p where p.id = auth.uid() and p.role in ('admin', 'programmer')
    )
  );

drop policy if exists "programmer_tasks_insert" on programmer_tasks;
create policy "programmer_tasks_insert" on programmer_tasks
  for insert to authenticated
  with check (
    creator_id = auth.uid()
    and exists (
      select 1 from profiles p where p.id = auth.uid() and p.role in ('admin', 'programmer')
    )
  );

drop policy if exists "programmer_tasks_update" on programmer_tasks;
create policy "programmer_tasks_update" on programmer_tasks
  for update to authenticated
  using (
    assignee_id = auth.uid()
    or creator_id = auth.uid()
    or exists (
      select 1 from profiles p where p.id = auth.uid() and p.role in ('admin', 'programmer')
    )
  )
  with check (
    assignee_id = auth.uid()
    or creator_id = auth.uid()
    or exists (
      select 1 from profiles p where p.id = auth.uid() and p.role in ('admin', 'programmer')
    )
  );

drop policy if exists "programmer_tasks_delete" on programmer_tasks;
create policy "programmer_tasks_delete" on programmer_tasks
  for delete to authenticated
  using (
    creator_id = auth.uid()
    or exists (
      select 1 from profiles p where p.id = auth.uid() and p.role in ('admin', 'programmer')
    )
  );

-- Activity logs: visible/writable to anyone who can see the parent task.
drop policy if exists "programmer_task_activity_logs_select" on programmer_task_activity_logs;
create policy "programmer_task_activity_logs_select" on programmer_task_activity_logs
  for select to authenticated
  using (
    exists (
      select 1 from programmer_tasks t
      where t.id = task_id
        and (
          t.assignee_id = auth.uid()
          or t.creator_id = auth.uid()
          or exists (
            select 1 from profiles p where p.id = auth.uid() and p.role in ('admin', 'programmer')
          )
        )
    )
  );

drop policy if exists "programmer_task_activity_logs_insert" on programmer_task_activity_logs;
create policy "programmer_task_activity_logs_insert" on programmer_task_activity_logs
  for insert to authenticated
  with check (
    exists (
      select 1 from programmer_tasks t
      where t.id = task_id
        and (
          t.assignee_id = auth.uid()
          or t.creator_id = auth.uid()
          or exists (
            select 1 from profiles p where p.id = auth.uid() and p.role in ('admin', 'programmer')
          )
        )
    )
  );

-- ---------------------------------------------------------------------------
-- Realtime — the Riverpod .stream() subscriptions require these tables in the
-- supabase_realtime publication.
-- ---------------------------------------------------------------------------
alter publication supabase_realtime add table programmer_tasks;
alter publication supabase_realtime add table programmer_task_activity_logs;