Developer Handover Pack

Developer Handover Pack

Risk Register Management

This agent is the entry point for all new risk submissions. It listens for new Google Forms responses, writes a structured row to the Google Sheets risk register, and then applies the agreed likelihood-by-impact risk matrix to the submission text to propose initial scores. The scored row is flagged for owner assignment and, if the proposed score lands in the critical band, the row is held and routed to the risk manager for manual review before any further action is taken. Estimated build time: 14 hours. Complexity: Moderate.

// Input
form_response_id       : string   // Google Forms response ID
submitter_email        : string   // From form metadata
risk_title             : string   // Form field: 'Risk name or summary'
risk_description       : string   // Form field: 'Describe the risk in detail'
risk_category          : string   // Form field: dropdown (Strategic, Operational, Financial, Compliance, Reputational)
suggested_owner_email  : string   // Form field: optional
submission_timestamp   : ISO8601

// Output (written to Google Sheets register row)
risk_id                : string   // Auto-generated, format RISK-YYYYMM-NNN
risk_title             : string
risk_category          : string
risk_description       : string
submitter_email        : string
assigned_owner_email   : string   // Resolved from suggested_owner or routing rules
likelihood_score       : integer  // 1-5, proposed by agent
impact_score           : integer  // 1-5, proposed by agent
risk_rating            : string   // Low | Medium | High | Critical (likelihood x impact matrix)
date_raised            : date
next_review_date       : date     // date_raised + configured review cadence
score_status           : string   // 'Proposed' | 'Awaiting human review' | 'Confirmed'
row_timestamp          : ISO8601

// On critical flag
critical_hold          : boolean  // true when risk_rating == 'Critical'
manager_review_url     : string   // Deep link to the register row for the risk manager

This agent runs on a scheduled basis and on event-based triggers to monitor the risk register for items approaching or past their review date. It sends personalised Slack nudges to the assigned owner and, if no response is recorded within 48 hours, sends a formatted escalation email via Gmail to the risk manager and the relevant department head. It also monitors the register continuously for any row where risk_rating is 'Critical' and score_status is 'Confirmed', triggering an immediate escalation email regardless of review date. Estimated build time: 12 hours. Complexity: Moderate.

// Input (read from Google Sheets register)
risk_id                : string
risk_title             : string
risk_rating            : string
assigned_owner_email   : string
next_review_date       : date
score_status           : string
last_nudge_sent_at     : ISO8601  // null if no nudge sent yet
escalation_sent_at     : ISO8601  // null if no escalation sent yet

// Output: Slack nudge (owner)
slack_channel          : string   // Owner's DM, resolved via Slack user lookup by email
slack_message_text     : string   // Includes risk_id, risk_title, review link, proposed scores
nudge_timestamp        : ISO8601  // Written back to last_nudge_sent_at in the register

// Output: Gmail escalation (overdue or critical)
to_address             : string   // risk_manager@[YourCompany.com]
cc_address             : string   // dept_head@[YourCompany.com], resolved from category-to-dept lookup
subject                : string   // 'Risk Escalation: [risk_id] [risk_title] - Action Required'
body_html              : string   // Formatted summary with scores, owner, days overdue, register link
escalation_timestamp   : ISO8601  // Written back to escalation_sent_at in the register

// Register write-back
last_nudge_sent_at     : ISO8601
escalation_sent_at     : ISO8601
escalation_reason      : string   // 'Overdue 48h' | 'Critical rating confirmed'

This agent fires on a scheduled trigger on the last business day of each calendar month. It reads the full current state of the Google Sheets risk register, calculates summary statistics and the risk heat map distribution, writes a structured leadership summary to a pre-configured Notion page template, and then distributes the completed report via Gmail to all stakeholders on the distribution list and posts a summary excerpt to the designated Slack channel. The agent also saves a dated snapshot of the register tab to the archive sheet for audit continuity. Estimated build time: 10 hours. Complexity: Moderate.

// Input (read from Google Sheets 'Risk Register' tab)
all_register_rows      : array    // Full snapshot of all rows at trigger time
report_month           : string   // YYYY-MM derived from trigger date
stakeholder_list       : array    // Emails, read from 'Config' tab row 'report_recipients'
notion_page_id         : string   // Target Notion page, stored in credential store
slack_report_channel   : string   // Channel ID, stored in credential store

// Computed summary statistics
total_risks            : integer
critical_count         : integer
high_count             : integer
medium_count           : integer
low_count              : integer
overdue_count          : integer
new_this_month         : integer
closed_this_month      : integer
top_risks              : array    // Up to 5 rows where risk_rating IN ('Critical','High'), sorted by likelihood_score DESC

// Output: Notion page
notion_page_title      : string   // 'Risk Summary Report - [Month] [Year]'
notion_page_content    : string   // Structured blocks: summary stats, heat map table, top risks list, narrative
notion_page_url        : string   // Returned by Notion API after write

// Output: Gmail distribution
to_addresses           : array    // stakeholder_list
subject                : string   // 'Monthly Risk Report - [Month] [Year] - [YourCompany.com]'
body_html              : string   // Includes notion_page_url and key stats inline

// Output: Slack excerpt
slack_channel          : string   // slack_report_channel
slack_message_text     : string   // Key headline stats and Notion link

// Output: Archive write-back
archive_tab_name       : string   // Format 'Archive-YYYY-MM'
archive_timestamp      : ISO8601

// ─────────────────────────────────────────────────────────────────────
// TRIGGER A: New risk submission via Google Forms
// ─────────────────────────────────────────────────────────────────────
Google Forms (webhook / poll ≤5 min)
  → form_response_id, submitter_email, risk_title, risk_description,
    risk_category, suggested_owner_email, submission_timestamp

// ─────────────────────────────────────────────────────────────────────
// AGENT 1: Risk Intake and Scoring Agent
// ─────────────────────────────────────────────────────────────────────
  [1a] Deduplication check
       Google Sheets read → filter rows WHERE risk_title MATCH
       AND date_raised > (submission_timestamp - 7d)
       AND submitter_email == incoming.submitter_email
       IF match found → set duplicate_flag = true, hold for manager review → EXIT

  [1b] Owner resolution
       suggested_owner_email != NULL → assigned_owner_email = suggested_owner_email
       suggested_owner_email == NULL → lookup category_to_owner_table[risk_category]
       IF no match → assigned_owner_email = NULL, alert_slack(risk_manager), continue

  [1c] Scoring logic
       risk_description + risk_category → risk_matrix[likelihood][impact]
       → likelihood_score (1-5), impact_score (1-5)
       risk_rating = matrix_cell[likelihood_score][impact_score]
       // e.g. likelihood=4, impact=5 → 'Critical'

  [1d] Register write
       Google Sheets append row →
         risk_id, risk_title, risk_category, risk_description,
         submitter_email, assigned_owner_email,
         likelihood_score, impact_score, risk_rating,
         date_raised, next_review_date, score_status='Proposed',
         row_timestamp

  [1e] Critical hold branch
       IF risk_rating == 'Critical'
         → set score_status = 'Awaiting human review'
         → Gmail to risk_manager: subject='Critical Risk Hold: [risk_id]',
           body includes manager_review_url (deep link to register row)
         → WAIT for manual score_status update to 'Confirmed' or 'Overridden'
       ELSE
         → score_status = 'Proposed', continue to Agent 2

// ──────────────────────────────────────────────────���──────────────────
// HANDOFF A→B: Register row written with score_status = 'Proposed'
//              or manager sets score_status = 'Confirmed' after review
// ─────────────────────────────────────────────────────────────────────

// TRIGGER B: Scheduled poll every 60 min OR score_status transition event
// ─────────────────────────────────────────────────────────────────────
// AGENT 2: Review Chase and Escalation Agent
// ─────────────────────────────────────────────────────────────────────
  [2a] Register scan
       Google Sheets read all rows →
         filter WHERE next_review_date <= TODAY() + 1d
         AND score_status NOT IN ('Awaiting human review')
         AND (last_nudge_sent_at == NULL OR last_nudge_sent_at < NOW() - 24h)

  [2b] Slack nudge
       FOR each matched row:
         Slack user lookup: assigned_owner_email → slack_user_id
         IF slack_user_id found → DM owner:
           text includes risk_id, risk_title, likelihood_score,
           impact_score, risk_rating, register_deep_link
         ELSE → post to risk_alerts_channel, notify risk_manager
         Google Sheets write → last_nudge_sent_at = NOW()

  [2c] 48-hour escalation check
       Google Sheets read →
         filter WHERE last_nudge_sent_at < NOW() - 48h
         AND score_status == 'Proposed'
         AND escalation_sent_at == NULL
       FOR each matched row:
         Gmail send →
           to: risk_manager_email
           cc: dept_head_email (from category_to_dept_table[risk_category])
           subject: 'Risk Escalation: [risk_id] [risk_title] - Action Required'
           body_html: risk_id, risk_title, risk_rating, assigned_owner_email,
                      days_overdue, register_link
         Google Sheets write → escalation_sent_at = NOW(),
                               escalation_reason = 'Overdue 48h'

  [2d] Immediate critical escalation
       Google Sheets read →
         filter WHERE risk_rating == 'Critical'
         AND score_status == 'Confirmed'
         AND escalation_sent_at == NULL
       FOR each matched row:
         Gmail send (same format as 2c)
         Google Sheets write → escalation_sent_at = NOW(),
                               escalation_reason = 'Critical rating confirmed'

// ─────────────────────────────────────────────────────────────────────
// HANDOFF B→C: Register continuously updated; Agent 3 fires on schedule
// ─────────────────────────────────────────────────────────────────────

// TRIGGER C: Scheduled cron, 08:00 last business day of month
// ─────────────────────────────────────────────────────────────────────
// AGENT 3: Risk Reporting Agent
// ─────────────────────────────────────────────────────────────────────
  [3a] Register snapshot read
       Google Sheets read all rows →
         all_register_rows (full current state at trigger timestamp)
         report_month = YYYY-MM from trigger date

  [3b] Summary statistics computed
       total_risks, critical_count, high_count, medium_count, low_count,
       overdue_count (next_review_date < TODAY AND score_status != 'Closed'),
       new_this_month (date_raised within report_month),
       closed_this_month (status='Closed' within report_month)
       top_risks = rows WHERE risk_rating IN ('Critical','High')
                   ORDER BY likelihood_score DESC LIMIT 5

  [3c] Notion report write
       Notion API PATCH page_id →
         page_title: 'Risk Summary Report - [Month] [Year]'
         blocks: heading (stats), heat_map_table, top_risks_table, narrative_text
       → returns notion_page_url

  [3d] Gmail distribution
       Gmail send →
         to: stakeholder_list (from Config tab 'report_recipients')
         subject: 'Monthly Risk Report - [Month] [Year] - [YourCompany.com]'
         body_html: key stats inline + notion_page_url

  [3e] Slack excerpt
       Slack post →
         channel: slack_report_channel
         text: headline stats + notion_page_url

  [3f] Archive write-back
       Google Sheets duplicate 'Risk Register' tab →
         new tab name: 'Archive-YYYY-MM' (append '-v2' if exists)
         archive_timestamp written to Config tab 'last_archived'

// ─────────────────────────────────────────────────────────────────────
// END: Leadership report published, archived, and distributed
// ─────────────────────────────────────────────────────────────────────