Teamwork Projects Integration

How the account view’s Projects panel sources, filters, and displays Teamwork project delivery state for a subscriber.

Data flow

Teamwork API ──(ingest)──▶ BigQuery: fcr_operations.teamwork_project_tasks
                                     fcr_operations.teamwork_project_milestones
                                        │
                                        ▼
                          Worker: /dashboard-teamwork-projects
                          (worker/src/handlers/teamwork-projects.js)
                                        │
                                        ▼
                          Frontend: ProjectsPanel.jsx
                          (src/components/panels/ProjectsPanel.jsx)

Teamwork is no longer called live — everything is served from BigQuery tables that are refreshed by an ingestion job. The worker composes two parallel BQ queries per request and shapes the flat rows into a project → tasklist → task hierarchy the UI can render.

BigQuery source tables

fcr_operations.teamwork_project_tasks

One row per task, or a project-only row if the project has no tasklists yet. Currently ~47.5k rows spanning 3,788 distinct subscribers / 3,788 projects / 2,717 tasklists / 45,164 tasks.

Key columns used:

• Project: project_id, project_name, project_display_name, project_url, project_status, project_sub_status, project_company_name
• Tasklist: tasklist_id, tasklist_name, tasklist_description, tasklist_status, tasklist_complete
• Task: task_id, task_name, task_completed, task_due_date, task_completed_at, task_progress, task_priority, task_assignees, task_updated_at
• Scope: subscriber_id

fcr_operations.teamwork_project_milestones

One row per milestone, joined on project_id. ~10,866 rows across 3,672 projects. Only 64 milestones are marked complete in the source — the staging job keeps the current open set plus a trailing completed tail. Columns used: project_id, milestone_id, milestone_name, milestone_completed, milestone_deadline.

Worker endpoint — /dashboard-teamwork-projects

Request: GET /dashboard-teamwork-projects?subscriberId=<id> Auth: x-api-key header (same as every other dashboard endpoint).

What it pulls

The handler runs both queries in parallel (Promise.allSettled) so a milestones failure does not break the projects payload:

-- tasks
SELECT project_id, project_name, project_display_name, project_status,
       project_sub_status, project_url, project_company_name,
       tasklist_id, tasklist_name, tasklist_description, tasklist_status, tasklist_complete,
       task_id, task_name, task_completed, task_due_date, task_completed_at,
       task_progress, task_priority, task_assignees, task_updated_at
FROM fcr_operations.teamwork_project_tasks
WHERE subscriber_id = @subscriberId
ORDER BY project_id, tasklist_id, task_due_date;

-- milestones
SELECT project_id, milestone_id, milestone_name, milestone_completed, milestone_deadline
FROM fcr_operations.teamwork_project_milestones
WHERE subscriber_id = @subscriberId;

Note: no status filter is applied at the SQL layer — the subscriber’s entire Teamwork footprint is returned. In practice the upstream BQ staging job already excludes archived projects, so 99% of rows are project_status='active' (sub-statuses: current for 3,353 projects, late for 433; inactive/current carries 2 edge cases today).

What it returns

The worker groups the flat rows into a nested structure:

{
  "count": 2,
  "projects": [
    {
      "id": 12345,
      "name": "Client Delivery Project",
      "displayName": "Acme Ltd — Current Program",
      "projectUrl": "https://fcrmedia.teamwork.com/#/projects/12345",
      "status": "active",
      "subStatus": "current",     // 'current' or 'late'
      "companyName": "Acme Ltd",
      "tasklists": [
        {
          "id": 98765,
          "name": "Website Build",
          "status": "new",        // 'new' | 'reopened' | null
          "description": "**Product:** ... **Price:** ... **Rep Name:** ...",
          "totalTasks": 14,
          "completedTasks": 9,
          "overdueTasks": 1,
          "progressPct": 64,
          "lastActivity": "2026-04-02T00:00:00.000Z",
          "tasks": [
            {
              "id": 555,
              "name": "Go-live approval",
              "completed": false,
              "isOverdue": true,
              "dueDate": "2026-04-01",
              "assignees": ["Jane Doe"]
            }
          ]
        }
      ],
      "taskStats": {
        "totalTasks": 23,
        "completedTasks": 14,
        "progressPct": 61,
        "overdueTasks": 1
      },
      "milestones": [
        { "name": "Go-live", "isLate": true }
      ]
    }
  ]
}

Per-tasklist rollups computed by the worker

For each tasklist the handler computes:

• totalTasks, completedTasks, overdueTasks
• progressPct = round(completed / total * 100)
• lastActivity — max task due_date in the tasklist (used as a proxy for “latest scheduled work”)
• isOverdue per task — !completed && due_date < now()

Project-level taskStats is the sum of the tasklist rollups. Milestones are attached via a {project_id → milestone[]} lookup and each gets an isLate flag (!completed && deadline < now()).

Population of tasks pulled per project

The worker does not thin the task list — every task row returned by BQ for the subscriber is included in the response. Filtering is the UI’s job:

• Backend scope: all tasks on all tasklists of all active projects for the subscriber, regardless of task status (open, completed, overdue, reopened).
• Overdue detection: computed at request time against now(), not materialised in BQ, so the flag is always current.
• Tasklist without tasks: the project-only row still produces a project entry with an empty tasklists array — this is how we surface newly created projects that haven’t been broken down yet.

Observed tasklist status distribution today: new ≈ 28,949, reopened ≈ 16,492, null ≈ 2,128.

Frontend — ProjectsPanel.jsx

The panel renders one ProjectCard per project. Relevance decisions live here:

Headline stats (top-right of each project card)

• Products — count of tasklists on the project
• Tasks — completed/total across all tasklists
• Progress — overall progressPct
• Overdue — only shown (and highlighted red) when overdueTasks > 0

Milestone summary

Only late milestones are surfaced (isLate === true). The panel shows a red pill with a count and the first 3 late milestone names (+N more if there are more). Completed / on-track milestones are intentionally hidden — the goal is a “what’s at risk” view, not a full milestone log.

Tasklist rows

Each tasklist renders as a row with:

• Left border colour keyed off tasklist.status (new=blue, reopened=amber, completed/closed=grey) — or red if any task is overdue, which overrides status colour
• Status pill, product / rep / price / billing parsed out of the markdown tasklist.description
• Completed/total counts, overdue badge, “Last: Nd ago” relative time from lastActivity
• Mini progress bar

Clicking a row expands the individual task list (status dot, strikethrough for completed, red overdue badge, assignees, due date).

Filter pills

If the project has more than one distinct tasklist status, a pill row lets the user filter to new / reopened / etc. Default is “All”. The filter is purely client-side against tasklist.status.

What is intentionally not shown

• Completed milestones — only late ones surface in the summary strip (full list is still in the payload if we ever want a “history” view)
• Archived / closed projects — the upstream staging job already excludes them
• Task descriptions / comments — not fetched; the panel is a status dashboard, not a task editor
• Time logs / budget burn — out of scope for this build
• Priority / progress % — present in the payload (task_priority, task_progress) but not rendered; room to add if asked

Caching

Not in TTL_CONFIG — falls back to the worker’s DEFAULT_TTL of 6h in KV. Cache key is SHA-256(subscriberId=<id>), prefixed dash:dashboard-teamwork-projects:. To force a refresh after a backfill, either append a dummy query param or purge the KV key directly (see docs/HANDOFF.md for the wrangler KV commands).

Files

• worker/src/handlers/teamwork-projects.js — endpoint + shaping
• src/components/panels/ProjectsPanel.jsx — UI + filtering + description parsing
• src/screens/AccountView.jsx — panel mount point
• src/hooks/useDashboardData.js — fetch wiring