Project Management Use Case

Build a project management backend with Backflow.

Overview

This guide demonstrates building a project management system with:

• Projects and workspaces
• Tasks with assignments
• Time tracking
• Comments and activity
• Notifications

Route Configuration

{
  "routeAliases": {
    "projects": "/api/v1/projects",
    "tasks": "/api/v1/tasks",
    "time": "/api/v1/time-entries"
  }
}

Projects

Create Project

{
  "path": "/projects",
  "method": "post",
  "requireAuth": true,
  "validation": {
    "body": {
      "name": { "type": "string", "required": true },
      "description": { "type": "string" },
      "workspace_id": { "type": "string", "required": true },
      "start_date": { "type": "string", "format": "date" },
      "due_date": { "type": "string", "format": "date" }
    }
  },
  "supabaseQueries": [{
    "table": "projects",
    "operation": "insert",
    "data": {
      "name": "{{body.name}}",
      "description": "{{body.description}}",
      "workspace_id": "{{body.workspace_id}}",
      "owner_id": "{{auth.sub}}",
      "start_date": "{{body.start_date}}",
      "due_date": "{{body.due_date}}",
      "status": "active"
    },
    "select": "*"
  }]
}

List Projects

{
  "path": "/workspaces/:workspaceId/projects",
  "method": "get",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "projects",
    "operation": "select",
    "select": "*, tasks(count), project_members(user_id)",
    "filters": [
      { "column": "workspace_id", "operator": "eq", "value": "{{params.workspaceId}}" },
      { "column": "status", "operator": "neq", "value": "archived" }
    ],
    "order": { "column": "updated_at", "ascending": false }
  }]
}

Get Project with Stats

{
  "path": "/projects/:id",
  "method": "get",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "projects",
    "operation": "select",
    "select": "*, project_members(users(id, name, avatar)), tasks(id, status)",
    "filters": [
      { "column": "id", "operator": "eq", "value": "{{params.id}}" }
    ],
    "single": true
  }],
  "transform": {
    "operations": [{
      "type": "compute",
      "fields": {
        "task_stats": {
          "total": "{{result.tasks.length}}",
          "completed": "{{result.tasks.filter(t => t.status === 'done').length}}",
          "in_progress": "{{result.tasks.filter(t => t.status === 'in_progress').length}}"
        }
      }
    }]
  }
}

Tasks

Create Task

{
  "path": "/projects/:projectId/tasks",
  "method": "post",
  "requireAuth": true,
  "validation": {
    "body": {
      "title": { "type": "string", "required": true },
      "description": { "type": "string" },
      "assignee_id": { "type": "string" },
      "priority": { "type": "string", "enum": ["low", "medium", "high", "urgent"] },
      "due_date": { "type": "string", "format": "date" },
      "labels": { "type": "array", "items": { "type": "string" } }
    }
  },
  "workflow": {
    "steps": [
      {
        "id": "create-task",
        "type": "database",
        "params": {
          "table": "tasks",
          "operation": "insert",
          "data": {
            "project_id": "{{params.projectId}}",
            "title": "{{body.title}}",
            "description": "{{body.description}}",
            "assignee_id": "{{body.assignee_id}}",
            "reporter_id": "{{auth.sub}}",
            "priority": "{{body.priority || 'medium'}}",
            "due_date": "{{body.due_date}}",
            "status": "todo"
          },
          "select": "*, assignee:users!assignee_id(name, email)"
        }
      },
      {
        "id": "notify-assignee",
        "type": "integration",
        "condition": "{{body.assignee_id}}",
        "integration": "sendgrid",
        "action": "sendTemplate",
        "params": {
          "to": "{{steps.create-task.result.assignee.email}}",
          "templateId": "task-assigned",
          "dynamicTemplateData": {
            "taskTitle": "{{steps.create-task.result.title}}",
            "taskUrl": "{{env.APP_URL}}/tasks/{{steps.create-task.result.id}}"
          }
        }
      },
      {
        "id": "log-activity",
        "type": "database",
        "params": {
          "table": "activity_log",
          "operation": "insert",
          "data": {
            "entity_type": "task",
            "entity_id": "{{steps.create-task.result.id}}",
            "action": "created",
            "user_id": "{{auth.sub}}"
          }
        }
      }
    ]
  }
}

Update Task Status

{
  "path": "/tasks/:id/status",
  "method": "patch",
  "requireAuth": true,
  "validation": {
    "body": {
      "status": { "type": "string", "enum": ["todo", "in_progress", "review", "done"], "required": true }
    }
  },
  "supabaseQueries": [
    {
      "table": "tasks",
      "operation": "update",
      "data": {
        "status": "{{body.status}}",
        "completed_at": "{{body.status === 'done' ? 'now()' : null}}"
      },
      "filters": [
        { "column": "id", "operator": "eq", "value": "{{params.id}}" }
      ],
      "select": "*"
    },
    {
      "table": "activity_log",
      "operation": "insert",
      "data": {
        "entity_type": "task",
        "entity_id": "{{params.id}}",
        "action": "status_changed",
        "user_id": "{{auth.sub}}",
        "metadata": { "new_status": "{{body.status}}" }
      }
    }
  ]
}

List Tasks

{
  "path": "/projects/:projectId/tasks",
  "method": "get",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "tasks",
    "operation": "select",
    "select": "*, assignee:users!assignee_id(id, name, avatar), labels(*)",
    "filters": [
      { "column": "project_id", "operator": "eq", "value": "{{params.projectId}}" },
      { "column": "status", "operator": "in", "value": "{{query.status ? query.status.split(',') : ['todo', 'in_progress', 'review', 'done']}}" }
    ],
    "order": [
      { "column": "priority", "ascending": false },
      { "column": "due_date", "ascending": true }
    ]
  }]
}

My Tasks

{
  "path": "/my/tasks",
  "method": "get",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "tasks",
    "operation": "select",
    "select": "*, projects(id, name)",
    "filters": [
      { "column": "assignee_id", "operator": "eq", "value": "{{auth.sub}}" },
      { "column": "status", "operator": "neq", "value": "done" }
    ],
    "order": { "column": "due_date", "ascending": true }
  }]
}

Time Tracking

Start Timer

{
  "path": "/tasks/:taskId/time/start",
  "method": "post",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "time_entries",
    "operation": "insert",
    "data": {
      "task_id": "{{params.taskId}}",
      "user_id": "{{auth.sub}}",
      "started_at": "now()"
    },
    "select": "*"
  }]
}

Stop Timer

{
  "path": "/time-entries/:id/stop",
  "method": "post",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "time_entries",
    "operation": "update",
    "data": {
      "ended_at": "now()",
      "duration": "EXTRACT(EPOCH FROM (now() - started_at))::integer"
    },
    "filters": [
      { "column": "id", "operator": "eq", "value": "{{params.id}}" },
      { "column": "user_id", "operator": "eq", "value": "{{auth.sub}}" }
    ],
    "select": "*"
  }]
}

Log Time Entry

{
  "path": "/tasks/:taskId/time",
  "method": "post",
  "requireAuth": true,
  "validation": {
    "body": {
      "duration": { "type": "number", "required": true },
      "description": { "type": "string" },
      "date": { "type": "string", "format": "date" }
    }
  },
  "supabaseQueries": [{
    "table": "time_entries",
    "operation": "insert",
    "data": {
      "task_id": "{{params.taskId}}",
      "user_id": "{{auth.sub}}",
      "duration": "{{body.duration}}",
      "description": "{{body.description}}",
      "started_at": "{{body.date || 'now()'}}"
    },
    "select": "*"
  }]
}

Comments

Add Comment

{
  "path": "/tasks/:taskId/comments",
  "method": "post",
  "requireAuth": true,
  "validation": {
    "body": {
      "content": { "type": "string", "required": true }
    }
  },
  "workflow": {
    "steps": [
      {
        "id": "add-comment",
        "type": "database",
        "params": {
          "table": "comments",
          "operation": "insert",
          "data": {
            "task_id": "{{params.taskId}}",
            "user_id": "{{auth.sub}}",
            "content": "{{body.content}}"
          },
          "select": "*, users(name, avatar)"
        }
      },
      {
        "id": "get-watchers",
        "type": "database",
        "params": {
          "table": "task_watchers",
          "operation": "select",
          "select": "users(id, email)",
          "filters": [
            { "column": "task_id", "operator": "eq", "value": "{{params.taskId}}" },
            { "column": "user_id", "operator": "neq", "value": "{{auth.sub}}" }
          ]
        }
      },
      {
        "id": "notify-watchers",
        "type": "forEach",
        "items": "{{steps.get-watchers.result}}",
        "step": {
          "type": "integration",
          "integration": "sendgrid",
          "action": "send",
          "params": {
            "to": "{{item.users.email}}",
            "subject": "New comment on task",
            "text": "{{steps.add-comment.result.content}}"
          }
        }
      }
    ]
  }
}

List Comments

{
  "path": "/tasks/:taskId/comments",
  "method": "get",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "comments",
    "operation": "select",
    "select": "*, users(id, name, avatar)",
    "filters": [
      { "column": "task_id", "operator": "eq", "value": "{{params.taskId}}" }
    ],
    "order": { "column": "created_at", "ascending": true }
  }]
}

Database Schema

CREATE TABLE workspaces (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  owner_id UUID NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID REFERENCES workspaces(id),
  name TEXT NOT NULL,
  description TEXT,
  owner_id UUID NOT NULL,
  status TEXT DEFAULT 'active',
  start_date DATE,
  due_date DATE,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id),
  title TEXT NOT NULL,
  description TEXT,
  assignee_id UUID,
  reporter_id UUID NOT NULL,
  status TEXT DEFAULT 'todo',
  priority TEXT DEFAULT 'medium',
  due_date DATE,
  completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE time_entries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id UUID REFERENCES tasks(id),
  user_id UUID NOT NULL,
  started_at TIMESTAMPTZ NOT NULL,
  ended_at TIMESTAMPTZ,
  duration INTEGER,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id UUID REFERENCES tasks(id),
  user_id UUID NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE activity_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_type TEXT NOT NULL,
  entity_id UUID NOT NULL,
  action TEXT NOT NULL,
  user_id UUID NOT NULL,
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE task_watchers (
  task_id UUID REFERENCES tasks(id),
  user_id UUID NOT NULL,
  PRIMARY KEY (task_id, user_id)
);