tasq/supabase/migrations/20260927140000_add_programmer_projects_worklogs_comments.sql

-- Programmer Tasks PM extension: projects, work logs (per-contributor time
-- ledger), and comments with attachments. All tables start hardened (author
-- bound to auth.uid(); logs/work-logs append-only; immutable creator/created_at).

-- Visibility helper: true iff the caller can see the task. SECURITY INVOKER so
-- it runs under the caller's own programmer_tasks RLS (no SECURITY DEFINER
-- exposure); reused by work-log and comment policies.
create or replace function programmer_task_visible(t_id uuid)
returns boolean
language sql
stable
security invoker
set search_path = public
as $$
  select exists (select 1 from programmer_tasks where id = t_id);
$$;

-- ---------------------------------------------------------------------------
-- Projects
-- ---------------------------------------------------------------------------
create table if not exists programmer_projects (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  description text,
  status text not null default 'active',  -- active | archived
  creator_id uuid references profiles(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index if not exists idx_programmer_projects_status on programmer_projects(status);

-- Reuse the table-agnostic updated_at + immutability trigger functions from the
-- base/harden migrations.
drop trigger if exists trg_programmer_projects_updated_at on programmer_projects;
create trigger trg_programmer_projects_updated_at
  before update on programmer_projects
  for each row execute function set_programmer_tasks_updated_at();

drop trigger if exists trg_programmer_projects_guard_immutable on programmer_projects;
create trigger trg_programmer_projects_guard_immutable
  before update on programmer_projects
  for each row execute function programmer_tasks_guard_immutable();

alter table programmer_projects enable row level security;

drop policy if exists "programmer_projects_select" on programmer_projects;
create policy "programmer_projects_select" on programmer_projects
  for select 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'))
  );

drop policy if exists "programmer_projects_insert" on programmer_projects;
create policy "programmer_projects_insert" on programmer_projects
  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_projects_update" on programmer_projects;
create policy "programmer_projects_update" on programmer_projects
  for update 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'))
  )
  with check (
    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_projects_delete" on programmer_projects;
create policy "programmer_projects_delete" on programmer_projects
  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'))
  );

-- Link tasks to a project (optional; unset on project delete).
alter table programmer_tasks add column if not exists project_id uuid
  references programmer_projects(id) on delete set null;
create index if not exists idx_programmer_tasks_project_id on programmer_tasks(project_id);

-- ---------------------------------------------------------------------------
-- Work logs (per-contributor time ledger)
-- ---------------------------------------------------------------------------
create table if not exists programmer_task_work_logs (
  id uuid primary key default gen_random_uuid(),
  task_id uuid not null references programmer_tasks(id) on delete cascade,
  author_id uuid not null references profiles(id),
  description text not null,
  minutes int check (minutes is null or minutes > 0),  -- null = assignee progress note
  created_at timestamptz not null default now()
);
create index if not exists idx_programmer_task_work_logs_task_id on programmer_task_work_logs(task_id);

alter table programmer_task_work_logs enable row level security;

-- Append-only: select + insert only (no update/delete policies).
drop policy if exists "programmer_task_work_logs_select" on programmer_task_work_logs;
create policy "programmer_task_work_logs_select" on programmer_task_work_logs
  for select to authenticated
  using (programmer_task_visible(task_id));

drop policy if exists "programmer_task_work_logs_insert" on programmer_task_work_logs;
create policy "programmer_task_work_logs_insert" on programmer_task_work_logs
  for insert to authenticated
  with check (author_id = auth.uid() and programmer_task_visible(task_id));

-- ---------------------------------------------------------------------------
-- Comments (+ attachments stored in the task_attachments bucket)
-- ---------------------------------------------------------------------------
create table if not exists programmer_task_comments (
  id uuid primary key default gen_random_uuid(),
  task_id uuid not null references programmer_tasks(id) on delete cascade,
  author_id uuid not null references profiles(id),
  body text not null default '',
  attachments jsonb not null default '[]'::jsonb,  -- [{path, name}]
  created_at timestamptz not null default now()
);
create index if not exists idx_programmer_task_comments_task_id on programmer_task_comments(task_id);

alter table programmer_task_comments enable row level security;

drop policy if exists "programmer_task_comments_select" on programmer_task_comments;
create policy "programmer_task_comments_select" on programmer_task_comments
  for select to authenticated
  using (programmer_task_visible(task_id));

drop policy if exists "programmer_task_comments_insert" on programmer_task_comments;
create policy "programmer_task_comments_insert" on programmer_task_comments
  for insert to authenticated
  with check (author_id = auth.uid() and programmer_task_visible(task_id));

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

-- ---------------------------------------------------------------------------
-- Realtime
-- ---------------------------------------------------------------------------
alter publication supabase_realtime add table programmer_projects;
alter publication supabase_realtime add table programmer_task_work_logs;
alter publication supabase_realtime add table programmer_task_comments;