Supabase Project: Task Management App with Teams

-- User profiles
create table profiles (
  id uuid references auth.users on delete cascade primary key,
  email text unique not null,
  full_name text,
  avatar_url text,
  created_at timestamp with time zone default now()
);

-- Workspaces (organizations)
create table workspaces (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  slug text unique not null,
  description text,
  owner_id uuid references profiles(id) on delete cascade not null,
  created_at timestamp with time zone default now()
);

-- Workspace members
create table workspace_members (
  workspace_id uuid references workspaces(id) on delete cascade,
  user_id uuid references profiles(id) on delete cascade,
  role text default 'member' check (role in ('owner', 'admin', 'member', 'guest')),
  joined_at timestamp with time zone default now(),
  primary key (workspace_id, user_id)
);

-- Projects
create table projects (
  id uuid default gen_random_uuid() primary key,
  workspace_id uuid references workspaces(id) on delete cascade not null,
  name text not null,
  description text,
  color text default '#3B82F6',
  status text default 'active' check (status in ('active', 'archived', 'completed')),
  owner_id uuid references profiles(id) on delete set null,
  start_date date,
  due_date date,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Tasks
create table tasks (
  id uuid default gen_random_uuid() primary key,
  project_id uuid references projects(id) on delete cascade not null,
  parent_task_id uuid references tasks(id) on delete cascade, -- For subtasks
  title text not null,
  description text,
  status text default 'todo' check (status in ('todo', 'in_progress', 'review', 'done', 'cancelled')),
  priority text default 'medium' check (priority in ('low', 'medium', 'high', 'urgent')),
  assigned_to uuid references profiles(id) on delete set null,
  created_by uuid references profiles(id) on delete set null not null,
  start_date timestamp with time zone,
  due_date timestamp with time zone,
  completed_at timestamp with time zone,
  estimated_hours numeric(10, 2),
  position int, -- For ordering within project/status
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Task labels/tags
create table labels (
  id uuid default gen_random_uuid() primary key,
  workspace_id uuid references workspaces(id) on delete cascade not null,
  name text not null,
  color text default '#gray',
  created_at timestamp with time zone default now(),
  unique(workspace_id, name)
);

-- Task labels junction
create table task_labels (
  task_id uuid references tasks(id) on delete cascade,
  label_id uuid references labels(id) on delete cascade,
  primary key (task_id, label_id)
);

-- Task comments
create table task_comments (
  id uuid default gen_random_uuid() primary key,
  task_id uuid references tasks(id) on delete cascade not null,
  user_id uuid references profiles(id) on delete cascade not null,
  content text not null,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Task attachments
create table task_attachments (
  id uuid default gen_random_uuid() primary key,
  task_id uuid references tasks(id) on delete cascade not null,
  user_id uuid references profiles(id) on delete cascade not null,
  file_name text not null,
  file_url text not null,
  file_size int,
  mime_type text,
  created_at timestamp with time zone default now()
);

-- Time entries
create table time_entries (
  id uuid default gen_random_uuid() primary key,
  task_id uuid references tasks(id) on delete cascade not null,
  user_id uuid references profiles(id) on delete cascade not null,
  start_time timestamp with time zone not null,
  end_time timestamp with time zone,
  duration_minutes int,
  description text,
  created_at timestamp with time zone default now()
);

-- Activity log
create table activities (
  id uuid default gen_random_uuid() primary key,
  workspace_id uuid references workspaces(id) on delete cascade not null,
  user_id uuid references profiles(id) on delete cascade,
  entity_type text not null check (entity_type in ('task', 'project', 'comment')),
  entity_id uuid not null,
  action text not null check (action in ('created', 'updated', 'deleted', 'completed', 'assigned', 'commented')),
  changes jsonb, -- Store what changed
  created_at timestamp with time zone default now()
);

-- Indexes
create index idx_tasks_project on tasks(project_id, status, position);
create index idx_tasks_assigned on tasks(assigned_to);
create index idx_tasks_due_date on tasks(due_date) where due_date is not null;
create index idx_projects_workspace on projects(workspace_id);
create index idx_workspace_members on workspace_members(user_id);
create index idx_activities_workspace on activities(workspace_id, created_at desc);
create index idx_time_entries_task on time_entries(task_id);
create index idx_time_entries_user on time_entries(user_id, start_time desc);

-- Enable RLS
alter table workspaces enable row level security;
alter table workspace_members enable row level security;
alter table projects enable row level security;
alter table tasks enable row level security;
alter table task_comments enable row level security;
alter table activities enable row level security;

-- Helper function to check workspace membership
create or replace function is_workspace_member(workspace_id uuid)
returns boolean as $$
begin
  return exists(
    select 1 from workspace_members
    where workspace_members.workspace_id = $1
    and workspace_members.user_id = auth.uid()
  );
end;
$$ language plpgsql security definer;

-- Helper function to check workspace role
create or replace function get_workspace_role(workspace_id uuid)
returns text as $$
begin
  return (
    select role from workspace_members
    where workspace_members.workspace_id = $1
    and workspace_members.user_id = auth.uid()
  );
end;
$$ language plpgsql security definer;

-- Workspace policies
create policy "Users can view workspaces they're members of"
  on workspaces for select
  using (is_workspace_member(id));

create policy "Users can create workspaces"
  on workspaces for insert
  with check (auth.uid() = owner_id);

create policy "Workspace owners and admins can update"
  on workspaces for update
  using (get_workspace_role(id) in ('owner', 'admin'));

-- Project policies
create policy "Users can view projects in their workspaces"
  on projects for select
  using (is_workspace_member(workspace_id));

create policy "Workspace members can create projects"
  on projects for insert
  with check (is_workspace_member(workspace_id));

create policy "Project owners and workspace admins can update"
  on projects for update
  using (
    is_workspace_member(workspace_id) and
    (owner_id = auth.uid() or get_workspace_role(workspace_id) in ('owner', 'admin'))
  );

-- Task policies
create policy "Users can view tasks in their workspace projects"
  on tasks for select
  using (
    exists (
      select 1 from projects
      where projects.id = tasks.project_id
      and is_workspace_member(projects.workspace_id)
    )
  );

create policy "Workspace members can create tasks"
  on tasks for insert
  with check (
    exists (
      select 1 from projects
      where projects.id = tasks.project_id
      and is_workspace_member(projects.workspace_id)
    )
  );

create policy "Assigned users and creators can update tasks"
  on tasks for update
  using (
    auth.uid() in (assigned_to, created_by) or
    exists (
      select 1 from projects
      where projects.id = tasks.project_id
      and get_workspace_role(projects.workspace_id) in ('owner', 'admin')
    )
  );

-- Comment policies
create policy "Users can view comments on accessible tasks"
  on task_comments for select
  using (
    exists (
      select 1 from tasks
      join projects on projects.id = tasks.project_id
      where tasks.id = task_comments.task_id
      and is_workspace_member(projects.workspace_id)
    )
  );

create policy "Users can add comments to accessible tasks"
  on task_comments for insert
  with check (
    exists (
      select 1 from tasks
      join projects on projects.id = tasks.project_id
      where tasks.id = task_comments.task_id
      and is_workspace_member(projects.workspace_id)
    )
    and auth.uid() = user_id
  );

// hooks/useTasks.ts
import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'

interface Task {
  id: string
  title: string
  description: string
  status: string
  priority: string
  assigned_to: string
  due_date: string
  position: number
  assignee?: {
    full_name: string
    avatar_url: string
  }
}

export function useTasks(projectId: string) {
  const [tasks, setTasks] = useState<Task[]>([])
  const [loading, setLoading] = useState(true)
  const supabase = createClient()

  useEffect(() => {
    loadTasks()

    // Subscribe to task changes
    const channel = supabase
      .channel(`project:${projectId}:tasks`)
      .on(
        'postgres_changes',
        {
          event: '*',
          schema: 'public',
          table: 'tasks',
          filter: `project_id=eq.${projectId}`,
        },
        () => loadTasks()
      )
      .subscribe()

    return () => {
      supabase.removeChannel(channel)
    }
  }, [projectId])

  async function loadTasks() {
    const { data } = await supabase
      .from('tasks')
      .select(`
        *,
        assignee:profiles!assigned_to(full_name, avatar_url)
      `)
      .eq('project_id', projectId)
      .is('parent_task_id', null) // Only root tasks, not subtasks
      .order('position')

    if (data) {
      setTasks(data)
    }
    setLoading(false)
  }

  async function updateTaskStatus(
    taskId: string,
    newStatus: string,
    newPosition: number
  ) {
    await supabase
      .from('tasks')
      .update({
        status: newStatus,
        position: newPosition,
        updated_at: new Date().toISOString(),
      })
      .eq('id', taskId)
  }

  async function createTask(task: Partial<Task>) {
    const { data: { user } } = await supabase.auth.getUser()
    
    const { error } = await supabase.from('tasks').insert({
      ...task,
      project_id: projectId,
      created_by: user?.id,
    })

    if (error) throw error
  }

  return {
    tasks,
    loading,
    updateTaskStatus,
    createTask,
    refresh: loadTasks,
  }
}

// components/KanbanBoard.tsx
import { useTasks } from '@/hooks/useTasks'
import { DragDropContext, Droppable, Draggable } from '@hello-pangea/dnd'

const STATUSES = [
  { id: 'todo', name: 'To Do', color: 'bg-gray-100' },
  { id: 'in_progress', name: 'In Progress', color: 'bg-blue-100' },
  { id: 'review', name: 'Review', color: 'bg-yellow-100' },
  { id: 'done', name: 'Done', color: 'bg-green-100' },
]

export function KanbanBoard({ projectId }: { projectId: string }) {
  const { tasks, updateTaskStatus } = useTasks(projectId)

  function handleDragEnd(result: any) {
    if (!result.destination) return

    const { draggableId, source, destination } = result
    
    if (
      source.droppableId === destination.droppableId &&
      source.index === destination.index
    ) {
      return
    }

    updateTaskStatus(
      draggableId,
      destination.droppableId,
      destination.index
    )
  }

  function getTasksByStatus(status: string) {
    return tasks
      .filter((task) => task.status === status)
      .sort((a, b) => a.position - b.position)
  }

  return (
    <DragDropContext onDragEnd={handleDragEnd}>
      <div className="flex gap-4 h-full overflow-x-auto">
        {STATUSES.map((status) => (
          <Droppable key={status.id} droppableId={status.id}>
            {(provided) => (
              <div
                ref={provided.innerRef}
                {...provided.droppableProps}
                className="flex-shrink-0 w-80"
              >
                <div className={`${status.color} rounded-t-lg p-4`}>
                  <h3 className="font-semibold">
                    {status.name}
                    <span className="ml-2 text-gray-500">
                      {getTasksByStatus(status.id).length}
                    </span>
                  </h3>
                </div>

                <div className="bg-gray-50 p-4 space-y-2 min-h-[500px]">
                  {getTasksByStatus(status.id).map((task, index) => (
                    <Draggable
                      key={task.id}
                      draggableId={task.id}
                      index={index}
                    >
                      {(provided) => (
                        <div
                          ref={provided.innerRef}
                          {...provided.draggableProps}
                          {...provided.dragHandleProps}
                        >
                          <TaskCard task={task} />
                        </div>
                      )}
                    </Draggable>
                  ))}
                  {provided.placeholder}
                </div>
              </div>
            )}
          </Droppable>
        ))}
      </div>
    </DragDropContext>
  )
}

function TaskCard({ task }: { task: Task }) {
  const priorityColors = {
    low: 'bg-gray-200',
    medium: 'bg-blue-200',
    high: 'bg-orange-200',
    urgent: 'bg-red-200',
  }

  return (
    <div className="bg-white rounded-lg shadow p-4 hover:shadow-md transition-shadow">
      <div className="flex items-start justify-between mb-2">
        <h4 className="font-medium">{task.title}</h4>
        <span
          className={`text-xs px-2 py-1 rounded ${priorityColors[task.priority as keyof typeof priorityColors]}`}
        >
          {task.priority}
        </span>
      </div>

      {task.description && (
        <p className="text-sm text-gray-600 mb-3 line-clamp-2">
          {task.description}
        </p>
      )}

      <div className="flex items-center justify-between">
        {task.assignee && (
          <div className="flex items-center gap-2">
            <img
              src={task.assignee.avatar_url || '/default-avatar.png'}
              alt={task.assignee.full_name}
              className="w-6 h-6 rounded-full"
            />
            <span className="text-sm text-gray-600">
              {task.assignee.full_name}
            </span>
          </div>
        )}

        {task.due_date && (
          <span className="text-xs text-gray-500">
            Due: {new Date(task.due_date).toLocaleDateString()}
          </span>
        )}
      </div>
    </div>
  )
}

-- Function to start time entry
create or replace function start_time_entry(p_task_id uuid, p_description text default null)
returns uuid as $$
declare
  entry_id uuid;
begin
  -- End any running entries for this user
  update time_entries
  set
    end_time = now(),
    duration_minutes = extract(epoch from (now() - start_time)) / 60
  where user_id = auth.uid()
    and end_time is null;

  -- Start new entry
  insert into time_entries (task_id, user_id, start_time, description)
  values (p_task_id, auth.uid(), now(), p_description)
  returning id into entry_id;

  return entry_id;
end;
$$ language plpgsql security definer;

-- Function to stop time entry
create or replace function stop_time_entry(p_entry_id uuid)
returns void as $$
begin
  update time_entries
  set
    end_time = now(),
    duration_minutes = extract(epoch from (now() - start_time)) / 60
  where id = p_entry_id
    and user_id = auth.uid()
    and end_time is null;
end;
$$ language plpgsql security definer;

// hooks/useTimeTracking.ts
import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'

interface TimeEntry {
  id: string
  task_id: string
  start_time: string
  end_time?: string
  duration_minutes?: number
  description?: string
}

export function useTimeTracking(taskId?: string) {
  const [activeEntry, setActiveEntry] = useState<TimeEntry | null>(null)
  const [entries, setEntries] = useState<TimeEntry[]>([])
  const [elapsedSeconds, setElapsedSeconds] = useState(0)
  const supabase = createClient()

  useEffect(() => {
    loadActiveEntry()
    if (taskId) {
      loadEntries()
    }
  }, [taskId])

  // Timer for active entry
  useEffect(() => {
    if (!activeEntry) return

    const interval = setInterval(() => {
      const start = new Date(activeEntry.start_time).getTime()
      const now = Date.now()
      setElapsedSeconds(Math.floor((now - start) / 1000))
    }, 1000)

    return () => clearInterval(interval)
  }, [activeEntry])

  async function loadActiveEntry() {
    const { data } = await supabase
      .from('time_entries')
      .select('*')
      .is('end_time', null)
      .single()

    if (data) {
      setActiveEntry(data)
    }
  }

  async function loadEntries() {
    if (!taskId) return

    const { data } = await supabase
      .from('time_entries')
      .select('*')
      .eq('task_id', taskId)
      .not('end_time', 'is', null)
      .order('start_time', { ascending: false })

    if (data) {
      setEntries(data)
    }
  }

  async function startTimer(taskId: string, description?: string) {
    const { data } = await supabase.rpc('start_time_entry', {
      p_task_id: taskId,
      p_description: description,
    })

    await loadActiveEntry()
  }

  async function stopTimer() {
    if (!activeEntry) return

    await supabase.rpc('stop_time_entry', {
      p_entry_id: activeEntry.id,
    })

    setActiveEntry(null)
    setElapsedSeconds(0)
    if (taskId) {
      await loadEntries()
    }
  }

  function formatDuration(seconds: number): string {
    const hours = Math.floor(seconds / 3600)
    const minutes = Math.floor((seconds % 3600) / 60)
    const secs = seconds % 60
    return `${hours.toString().padStart(2, '0')}:${minutes.toString().padStart(2, '0')}:${secs.toString().padStart(2, '0')}`
  }

  const totalMinutes = entries.reduce(
    (sum, entry) => sum + (entry.duration_minutes || 0),
    0
  )

  return {
    activeEntry,
    entries,
    elapsedSeconds,
    totalMinutes,
    isTracking: !!activeEntry,
    startTimer,
    stopTimer,
    formatDuration,
  }
}

// components/TimeTracker.tsx
import { useTimeTracking } from '@/hooks/useTimeTracking'

export function TimeTracker({ taskId }: { taskId: string }) {
  const {
    isTracking,
    elapsedSeconds,
    totalMinutes,
    startTimer,
    stopTimer,
    formatDuration,
  } = useTimeTracking(taskId)

  return (
    <div className="bg-white rounded-lg shadow p-6">
      <h3 className="text-lg font-semibold mb-4">Time Tracking</h3>

      <div className="text-center mb-6">
        <div className="text-4xl font-mono font-bold mb-4">
          {formatDuration(elapsedSeconds)}
        </div>

        {isTracking ? (
          <button
            onClick={stopTimer}
            className="px-6 py-3 bg-red-500 text-white rounded-lg hover:bg-red-600"
          >
            ⏸ Stop Timer
          </button>
        ) : (
          <button
            onClick={() => startTimer(taskId)}
            className="px-6 py-3 bg-green-500 text-white rounded-lg hover:bg-green-600"
          >
            ▶ Start Timer
          </button>
        )}
      </div>

      <div className="border-t pt-4">
        <p className="text-sm text-gray-600">
          Total time tracked: <strong>{Math.round(totalMinutes / 60 * 10) / 10}h</strong>
        </p>
      </div>
    </div>
  )
}

-- Trigger to log task activities
create or replace function log_task_activity()
returns trigger as $$
begin
  if (TG_OP = 'INSERT') then
    insert into activities (workspace_id, user_id, entity_type, entity_id, action)
    select workspace_id, new.created_by, 'task', new.id, 'created'
    from projects where id = new.project_id;
  elsif (TG_OP = 'UPDATE') then
    -- Log status changes
    if old.status != new.status then
      insert into activities (workspace_id, user_id, entity_type, entity_id, action, changes)
      select
        workspace_id,
        auth.uid(),
        'task',
        new.id,
        'updated',
        jsonb_build_object('field', 'status', 'from', old.status, 'to', new.status)
      from projects where id = new.project_id;
    end if;

    -- Log assignment changes
    if old.assigned_to is distinct from new.assigned_to then
      insert into activities (workspace_id, user_id, entity_type, entity_id, action, changes)
      select
        workspace_id,
        auth.uid(),
        'task',
        new.id,
        'assigned',
        jsonb_build_object('assigned_to', new.assigned_to)
      from projects where id = new.project_id;
    end if;

    -- Log completion
    if old.completed_at is null and new.completed_at is not null then
      insert into activities (workspace_id, user_id, entity_type, entity_id, action)
      select workspace_id, auth.uid(), 'task', new.id, 'completed'
      from projects where id = new.project_id;
    end if;
  end if;

  return new;
end;
$$ language plpgsql security definer;

create trigger task_activity_trigger
  after insert or update on tasks
  for each row
  execute function log_task_activity();

// components/ActivityFeed.tsx
import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'
import { formatDistanceToNow } from 'date-fns'

interface Activity {
  id: string
  user: {
    full_name: string
    avatar_url: string
  }
  entity_type: string
  action: string
  changes?: any
  created_at: string
}

export function ActivityFeed({ workspaceId }: { workspaceId: string }) {
  const [activities, setActivities] = useState<Activity[]>([])
  const supabase = createClient()

  useEffect(() => {
    loadActivities()

    const channel = supabase
      .channel(`workspace:${workspaceId}:activities`)
      .on(
        'postgres_changes',
        {
          event: 'INSERT',
          schema: 'public',
          table: 'activities',
          filter: `workspace_id=eq.${workspaceId}`,
        },
        () => loadActivities()
      )
      .subscribe()

    return () => {
      supabase.removeChannel(channel)
    }
  }, [workspaceId])

  async function loadActivities() {
    const { data } = await supabase
      .from('activities')
      .select(`
        *,
        user:profiles!user_id(full_name, avatar_url)
      `)
      .eq('workspace_id', workspaceId)
      .order('created_at', { ascending: false })
      .limit(50)

    if (data) {
      setActivities(data)
    }
  }

  return (
    <div className="space-y-4">
      <h3 className="text-lg font-semibold">Recent Activity</h3>

      <div className="space-y-3">
        {activities.map((activity) => (
          <div key={activity.id} className="flex gap-3">
            <img
              src={activity.user.avatar_url || '/default-avatar.png'}
              alt={activity.user.full_name}
              className="w-8 h-8 rounded-full"
            />
            <div className="flex-1">
              <p className="text-sm">
                <strong>{activity.user.full_name}</strong>{' '}
                {getActivityText(activity)}
              </p>
              <p className="text-xs text-gray-500">
                {formatDistanceToNow(new Date(activity.created_at), {
                  addSuffix: true,
                })}
              </p>
            </div>
          </div>
        ))}
      </div>
    </div>
  )
}

function getActivityText(activity: Activity): string {
  const { entity_type, action, changes } = activity

  if (entity_type === 'task') {
    switch (action) {
      case 'created':
        return 'created a task'
      case 'updated':
        if (changes?.field === 'status') {
          return `moved task from ${changes.from} to ${changes.to}`
        }
        return 'updated a task'
      case 'completed':
        return 'completed a task'
      case 'assigned':
        return 'assigned a task'
      default:
        return 'modified a task'
    }
  }

  return `${action} a ${entity_type}`
}