Developer Handover Pack

Developer Handover Pack

Project Milestone Tracking

Runs on a daily schedule at 8:00 am, querying Asana via its REST API for all tasks across the configured project scope whose due date is on or before today and whose completion status is not marked complete. For each overdue item the agent extracts the task name, assignee, project name, original due date, days overdue, and current custom field values. It applies the configured escalation threshold rules, for example a milestone more than three days overdue or with no assignee response within 24 hours of the prior reminder triggers an escalation flag. All results, both flagged and unflagged, are written or updated in the central Google Sheets tracking log. This agent is the upstream dependency for both the Owner Reminder Agent and the Report Builder Agent; neither should fire until this agent has completed its run and confirmed a write to the sheet. Complexity: Moderate.

// Input
Asana project GIDs (array, configured per workspace scope)
Escalation threshold: days_overdue_threshold (integer, default 3)
Escalation threshold: no_response_hours (integer, default 24)
Google Sheets spreadsheet_id, sheet_name (tracking log)

// Asana task fields extracted per overdue item
task.gid, task.name, task.due_on, task.assignee.name,
task.assignee.email, task.projects[].name,
task.custom_fields[status], task.modified_at

// Output (written to Google Sheets tracking log)
row.task_gid           // Asana task GID (primary key for deduplication)
row.task_name          // Milestone display name
row.project_name       // Parent project name
row.owner_name         // Assignee full name
row.owner_email        // Assignee email address
row.due_date           // Original ISO due date (YYYY-MM-DD)
row.days_overdue       // Calculated integer: today minus due_date
row.escalation_flag    // Boolean: true if threshold breached
row.last_reminder_sent // ISO datetime of most recent reminder
row.status             // Current status string from Asana custom field
row.last_updated       // ISO datetime of this agent run

Fires immediately after the Milestone Monitor Agent confirms its daily write to the tracking sheet. Reads all rows in the tracking log where escalation_flag is false and last_reminder_sent is either null or more than 24 hours ago, then sends a personalised Gmail reminder to each milestone owner. For rows where escalation_flag is true, the agent constructs a structured Slack message and posts it to the configured escalation channel, tagging the relevant stakeholder and including a direct hyperlink to the Asana task. The agent then updates last_reminder_sent in the sheet for each processed row. It does not make any decisions about blocked milestones: it routes according to the flag value written by the Milestone Monitor Agent. Complexity: Moderate.

// Input (read from Google Sheets tracking log)
row.task_gid, row.task_name, row.project_name,
row.owner_name, row.owner_email, row.due_date,
row.days_overdue, row.escalation_flag, row.last_reminder_sent,
row.status

// Input (environment config)
gmail_sender_address      // Authenticated sender (e.g. ops@[YourCompany.com])
slack_escalation_channel  // Channel ID for escalation alerts
slack_stakeholder_map     // JSON map: project_name -> Slack user ID to tag
reminder_email_template   // Inline template string with merge fields

// Output: Gmail reminder (non-escalated rows)
to: row.owner_email
subject: 'Action needed: [row.task_name] is [row.days_overdue] days overdue'
body: personalised template merging task_name, project_name, due_date, days_overdue,
      direct Asana task URL (https://app.asana.com/0/{project_gid}/{task_gid})

// Output: Slack escalation post (escalation_flag = true rows)
channel: slack_escalation_channel
text: ':red_circle: Escalation | [task_name] | [project_name] | [days_overdue] days overdue'
blocks: structured message with task link, owner name, days overdue, stakeholder mention

// Output: Google Sheets update
row.last_reminder_sent = ISO datetime of send
row.status updated to 'Reminder Sent' or 'Escalated' accordingly

Fires every Monday at 07:30, after confirming the Milestone Monitor Agent has completed its first run of the week. Reads the full current state of the Google Sheets tracking log, groups milestones by project and status (on track, at risk, overdue, escalated), and writes a structured, formatted status report to a designated Notion page using the Notion API. The page is either created fresh each week under a parent page or appended as a new child page with the Monday date as the title, depending on the configuration agreed with the process owner. Once the Notion write is confirmed, the agent posts a summary digest to the leadership Slack channel containing the headline counts and a hyperlink to the full Notion page. Complexity: Simple.

// Input (read from Google Sheets tracking log, full data pull)
All rows where row.status != 'Complete'
Fields used: task_name, project_name, owner_name, due_date,
             days_overdue, escalation_flag, status, last_reminder_sent

// Input (environment config)
notion_parent_page_id       // ID of the parent Notion page for weekly reports
notion_database_id          // Optional: if reports are stored in a Notion DB
slack_leadership_channel    // Channel ID for leadership digest
report_title_template       // e.g. 'Milestone Status Report - {YYYY-MM-DD}'

// Computed aggregates before write
count_on_track    // rows where days_overdue <= 0 and escalation_flag = false
count_at_risk     // rows where days_overdue in [1,3] and escalation_flag = false
count_overdue     // rows where days_overdue > 0
count_escalated   // rows where escalation_flag = true

// Output: Notion page structure
Page title: report_title_template with Monday date substituted
Heading 1: 'Weekly Milestone Status Report'
Callout block: headline counts (on track, at risk, overdue, escalated)
Heading 2 per project_name group
Table block per group: columns task_name, owner_name, due_date, days_overdue, status
Notion page URL returned after create for use in Slack digest

// Output: Slack digest message
channel: slack_leadership_channel
text: 'Weekly Milestone Report | On track: {n} | At risk: {n} | Overdue: {n} | Escalated: {n}'
blocks: button linking to Notion page URL

// ============================================================
// TRIGGER
// ============================================================
// Automation platform cron fires at 08:00 daily (Monday-Friday)
// Milestone Monitor Agent begins execution

// ============================================================
// AGENT 1: Milestone Monitor Agent
// ============================================================

// Step 1: Query Asana for overdue tasks
GET https://app.asana.com/api/1.0/tasks
  params:
    project: <project_gid>          // iterated per configured project list
    completed_since: 'now'          // filters to incomplete tasks only
    opt_fields: gid, name, due_on, assignee.name, assignee.email,
                projects.name, custom_fields, modified_at
  filter (post-response):
    task.due_on <= today            // only overdue or due-today items
    task.completed == false

// Step 2: Apply escalation threshold rules
for each overdue_task:
  days_overdue = today - task.due_on           // integer
  escalation_flag = (days_overdue >= days_overdue_threshold)  // default: 3
               OR  (hours_since_last_reminder >= no_response_hours)  // default: 24

// Step 3: Deduplicate and write to Google Sheets
// Dedup key: task.gid matched against existing sheet column 'task_gid'
// If row exists: PATCH (update) existing row
// If row is new: POST (append) new row
Google Sheets row written:
  task_gid, task_name, project_name, owner_name, owner_email,
  due_date, days_overdue, escalation_flag,
  last_reminder_sent, status, last_updated

// Step 4: Write run completion timestamp
Agent Run Log sheet: run_completed_at = ISO datetime (UTC)
                     agent_name = 'Milestone Monitor Agent'
                     rows_written = integer count

// ============================================================
// AGENT HANDOFF 1 -> 2
// Owner Reminder Agent polls Agent Run Log for today's run_completed_at
// Condition: run_completed_at date part == today AND agent_name == 'Milestone Monitor Agent'
// Poll interval: every 5 minutes from 08:00 to 08:30
// If not confirmed by 08:30: log error, halt, alert monitoring channel
// ============================================================

// ============================================================
// AGENT 2: Owner Reminder Agent
// ============================================================

// Step 5: Read tracking log -- non-escalated rows needing reminder
Google Sheets read filter:
  escalation_flag == false
  AND (last_reminder_sent IS NULL
       OR last_reminder_sent date part < today)

// Step 6: Send Gmail reminder per qualifying row
Gmail API: users.messages.send
  to:      row.owner_email
  subject: 'Action needed: {row.task_name} is {row.days_overdue} days overdue'
  body:    template merged with task_name, project_name, due_date,
           days_overdue, asana_task_url
  asana_task_url = 'https://app.asana.com/0/{project_gid}/{task_gid}'

// Step 7: Read tracking log -- escalated rows
Google Sheets read filter:
  escalation_flag == true

// Step 8: Post Slack escalation alert per escalated row
Slack API: chat.postMessage
  channel: slack_escalation_channel
  text:    ':red_circle: Escalation | {task_name} | {project_name} | {days_overdue} days overdue'
  blocks:  rich text with owner_name, days_overdue, stakeholder_mention (@user_id from map),
           button linking to asana_task_url

// Step 9: Update Google Sheets rows
For each processed row:
  row.last_reminder_sent = ISO datetime (UTC)
  row.status = 'Reminder Sent'    // non-escalated
            OR 'Escalated'         // escalation_flag = true

// ============================================================
// MONDAY-ONLY BRANCH: AGENT HANDOFF 2 -> 3
// Report Builder Agent cron fires at 07:30 every Monday
// Condition: Agent Run Log has run_completed_at for today (Monday)
//            AND agent_name == 'Milestone Monitor Agent'
// Poll interval: every 5 minutes from 07:30 to 08:00
// If not confirmed by 08:00: skip report, alert monitoring channel
// ============================================================

// ============================================================
// AGENT 3: Report Builder Agent (Monday only)
// ============================================================

// Step 10: Full read of Google Sheets tracking log
Google Sheets read: all rows where status != 'Complete'
Fields consumed: task_name, project_name, owner_name, due_date,
                 days_overdue, escalation_flag, status, last_reminder_sent

// Step 11: Compute aggregates
count_on_track  = rows where days_overdue <= 0 AND escalation_flag = false
count_at_risk   = rows where days_overdue in [1, 3] AND escalation_flag = false
count_overdue   = rows where days_overdue > 0
count_escalated = rows where escalation_flag = true

// Step 12: Create Notion page
Notion API: pages.create
  parent.page_id: notion_parent_page_id
  properties.title: 'Milestone Status Report - {YYYY-MM-DD}'
  // Idempotency check: search existing pages first;
  // if title exists, append timestamp suffix
Notion blocks appended (blocks.children.append):
  heading_1:  'Weekly Milestone Status Report'
  callout:    'On track: {n} | At risk: {n} | Overdue: {n} | Escalated: {n}'
  for each project_name group:
    heading_2: project_name
    table:     columns [task_name, owner_name, due_date, days_overdue, status]
notion_page_url = response.url   // captured for Slack digest

// Step 13: Post Slack leadership digest
Slack API: chat.postMessage
  channel: slack_leadership_channel
  text:    'Weekly Milestone Report | On track: {n} | At risk: {n} | Overdue: {n} | Escalated: {n}'
  blocks:  button with label 'View full report' linking to notion_page_url

// ============================================================
// ERROR HANDLING (all agents)
// ============================================================
// On any unrecoverable API error after max retries:
//   Write to Supabase table: agent_errors
//   Fields: agent_name, task_gid (if applicable), error_code,
//           error_message, timestamp, retry_count
//   Post alert to Slack monitoring channel: #fullspec-alerts
// ============================================================