Developer Handover Pack

Developer Handover Pack

Compliance and Certification Tracking

Runs on a daily scheduled trigger at 07:00. Reads the full compliance register from Google Sheets, evaluates the days-remaining value for every active row, and classifies each item into an urgency tier (90-day, 60-day, or 30-day window). For each item within a threshold window, it resolves the responsible owner's email address from the register, composes a personalised reminder using the approved template, dispatches it via Gmail, and posts a structured alert to the designated Slack compliance channel. Also generates and distributes the weekly compliance status report via Gmail every Monday at 08:00. This agent eliminates steps 1, 2, 3, 4, and 10 from the current-state process.

// Input
Google Sheets row fields: item_name (string), owner_email (string), expiry_date (ISO 8601 date),
  renewal_status (enum: pending | in_progress | complete | escalated), doc_link (url | null),
  manager_email (string), urgency_tier (computed: 90d | 60d | 30d | overdue | ok)

// Processing
days_remaining = expiry_date - today()
IF days_remaining <= 30  -> urgency_tier = '30d'
IF days_remaining <= 60  -> urgency_tier = '60d'
IF days_remaining <= 90  -> urgency_tier = '90d'
IF days_remaining <= 0   -> urgency_tier = 'overdue'
FILTER rows WHERE urgency_tier IN [30d, 60d, 90d, overdue]
  AND renewal_status NOT IN [complete]

// Output
Gmail: personalised reminder email -> owner_email
  subject: 'Action required: [item_name] expires on [expiry_date]'
  body fields: item_name, expiry_date, urgency_tier, renewal_instructions_url
Slack: POST to #compliance-alerts channel
  message fields: item_name, owner_email, days_remaining, urgency_tier
Google Sheets: write last_reminder_sent_at (ISO 8601 timestamp) to row

// Weekly report output (Monday 08:00)
Gmail: compliance_status_report.csv attached -> operations_manager_email, stakeholder_emails[]
  report fields: item_name, owner_email, expiry_date, renewal_status, days_remaining, doc_link

Monitors every active register row for items where a reminder was sent but no renewal_status update to 'in_progress' or 'complete' has been recorded within five business days. When the threshold is breached, the agent sends a structured escalation email via Gmail to both the item owner and their manager, and delivers a Slack direct message to the owner's Slack user ID. A timestamp is written back to the register row to create an audit trail. This agent eliminates step 5 from the current-state process.

// Input
Google Sheets row fields: item_name, owner_email, manager_email, expiry_date,
  renewal_status, last_reminder_sent_at, escalation_sent_at (date | null),
  owner_slack_user_id (string | null)

// Processing
business_days_since_reminder = businessDaysBetween(last_reminder_sent_at, today())
FILTER rows WHERE business_days_since_reminder >= 5
  AND renewal_status IN [pending]
  AND escalation_sent_at IS NULL

// Output
Gmail: escalation email -> owner_email
  subject: 'ESCALATION: [item_name] renewal overdue - action required by [expiry_date]'
  body fields: item_name, expiry_date, days_remaining, renewal_instructions_url
Gmail: escalation copy email -> manager_email
  subject: 'FYI: [owner_email] has not acknowledged [item_name] renewal'
  body fields: item_name, owner_email, expiry_date, days_since_reminder
Slack: direct message -> owner_slack_user_id (fallback: post to #compliance-alerts if null)
  message fields: item_name, expiry_date, days_remaining, manager_email
Google Sheets: write escalation_sent_at (ISO 8601 timestamp) to row
  write renewal_status = 'escalated'

Triggered when the responsible owner marks a register row as complete or uploads a new certificate document. The agent reads the updated row, writes the new expiry date and certificate reference to Google Sheets, files the uploaded document in the correct Notion database page using the agreed naming convention, and forwards any associated renewal invoice to Xero for payment processing. The Operations Manager retains a manual review step before the record is marked fully complete: the agent sets renewal_status to 'pending_review' after filing, and the manager's manual approval updates it to 'complete'. This agent eliminates steps 7, 8, and 9 from the current-state process.

// Input
Google Sheets row fields: item_name, owner_email, expiry_date (old), new_expiry_date (string),
  cert_reference (string | null), invoice_attached (boolean), invoice_file_url (url | null),
  uploaded_doc_url (url | null), notion_page_id (string | null), renewal_status

// Processing
IF uploaded_doc_url IS NOT NULL:
  notion_file_name = '[item_name]_[owner_email]_[new_expiry_date].pdf'
  POST document to Notion database page (notion_page_id OR create new page under parent_db_id)
  Update notion_doc_link in Google Sheets row
IF invoice_attached = true AND invoice_file_url IS NOT NULL:
  POST invoice to Xero Invoices endpoint as ACCPAY type
  xero_invoice_id returned -> write to Google Sheets row xero_ref column
Update Google Sheets row: expiry_date = new_expiry_date, cert_reference, renewal_status = 'pending_review'
  last_updated_at = now()

// Output
Google Sheets: expiry_date updated, cert_reference updated, renewal_status = 'pending_review',
  notion_doc_link updated, xero_ref written (if invoice present), last_updated_at written
Notion: new database page OR updated page with filed document, fields:
  Title: notion_file_name, Owner: owner_email, Expiry: new_expiry_date,
  Item: item_name, Source: 'automated_renewal', Status: 'filed'
Xero: ACCPAY invoice created with fields:
  Contact: owner_email or vendor_name, DueDate: invoice_due_date,
  LineItems: [{ Description: item_name + ' renewal', UnitAmount: invoice_amount }]

// On approval (manager marks review complete in Google Sheets)
renewal_status = 'complete'
completed_at = now()
Slack: POST to #compliance-alerts: '[item_name] renewal confirmed and filed by [manager_email]'

// ============================================================
// TRIGGER: Daily cron 07:00
// ============================================================
schedule.trigger({ time: '07:00', days: 'MON-SUN' })
  -> orchestration_layer.start('compliance_monitoring_agent')

// ============================================================
// AGENT 1: Compliance Monitoring Agent
// ============================================================
// Step 1: Read full register
google_sheets.getRows({
  sheet_id: SHEETS_REGISTER_ID,
  tab:      'compliance_register',
  fields:   ['item_name','owner_email','manager_email','expiry_date',
             'renewal_status','last_reminder_sent_at','escalation_sent_at',
             'owner_slack_user_id','doc_link','notion_page_id','error_flag']
})
  -> rows[]

// Step 2: Classify urgency
FOR EACH row IN rows:
  days_remaining = dateDiff(row.expiry_date, today())  // integer
  IF days_remaining <= 0   -> row.urgency_tier = 'overdue'
  ELSE IF days_remaining <= 30  -> row.urgency_tier = '30d'
  ELSE IF days_remaining <= 60  -> row.urgency_tier = '60d'
  ELSE IF days_remaining <= 90  -> row.urgency_tier = '90d'
  ELSE -> SKIP (row is outside all threshold windows)

// Step 3: Deduplication check
  IF row.owner_email IS NULL OR row.owner_email == '':
    google_sheets.updateCell(row.id, 'error_flag', 'OWNER_MISSING')
    slack.postMessage({ channel: '#compliance-alerts',
      text: 'OWNER_MISSING for item: ' + row.item_name })
    SKIP row
  IF withinReminderCooldown(row.last_reminder_sent_at, row.urgency_tier):
    SKIP row  // avoid duplicate reminders within cooldown window

// Step 4: Send reminder email
  gmail.sendEmail({
    to:      row.owner_email,
    subject: 'Action required: ' + row.item_name + ' expires on ' + row.expiry_date,
    body:    template('reminder', {
               item_name:             row.item_name,
               expiry_date:           row.expiry_date,
               urgency_tier:          row.urgency_tier,
               renewal_instructions:  RENEWAL_INSTRUCTIONS_URL
             })
  })

// Step 5: Post Slack alert
  slack.postMessage({
    channel: '#compliance-alerts',
    text:    row.item_name + ' | Owner: ' + row.owner_email
             + ' | Due: ' + row.expiry_date + ' | Tier: ' + row.urgency_tier
  })

// Step 6: Write reminder timestamp back to register
  google_sheets.updateCell(row.id, 'last_reminder_sent_at', now())

// ============================================================
// AGENT 1 -> AGENT 2 HANDOFF
// Handoff fields: item_name, owner_email, manager_email,
// expiry_date, renewal_status, last_reminder_sent_at,
// escalation_sent_at, owner_slack_user_id
// ============================================================

// TRIGGER: Daily cron 09:00 (business days only)
schedule.trigger({ time: '09:00', days: 'MON-FRI', excludeHolidays: HOLIDAY_LIST })
  -> orchestration_layer.start('escalation_agent')

// ============================================================
// AGENT 2: Escalation Agent
// ============================================================
google_sheets.getRows({ ... same fields as Agent 1 ... })
  -> rows[]

FOR EACH row IN rows:
  biz_days = businessDaysBetween(row.last_reminder_sent_at, today())
  IF biz_days >= 5 AND row.renewal_status == 'pending' AND row.escalation_sent_at IS NULL:

    // Escalation email to owner
    gmail.sendEmail({
      to:      row.owner_email,
      subject: 'ESCALATION: ' + row.item_name + ' renewal overdue',
      body:    template('escalation_owner', {
                 item_name:    row.item_name,
                 expiry_date:  row.expiry_date,
                 days_remaining: dateDiff(row.expiry_date, today())
               })
    })

    // Escalation copy to manager
    gmail.sendEmail({
      to:      row.manager_email,
      subject: 'FYI: ' + row.owner_email + ' has not actioned ' + row.item_name,
      body:    template('escalation_manager', {
                 item_name:         row.item_name,
                 owner_email:       row.owner_email,
                 expiry_date:       row.expiry_date,
                 days_since_reminder: biz_days
               })
    })

    // Slack DM to owner (fallback to channel if slack_user_id absent)
    IF row.owner_slack_user_id IS NOT NULL:
      slack.postDirectMessage({ user_id: row.owner_slack_user_id, ... })
    ELSE:
      slack.postMessage({ channel: '#compliance-alerts',
        text: '@' + row.owner_email + ' ESCALATION: ' + row.item_name })

    // Write audit fields
    google_sheets.updateCell(row.id, 'escalation_sent_at', now())
    google_sheets.updateCell(row.id, 'renewal_status', 'escalated')

// ============================================================
// AGENT 2 -> MANUAL STEP HANDOFF
// Operations Manager receives escalation and reviews renewal
// document. Manager sets renewal_status = 'in_progress' in sheet
// OR uploads document via intake mechanism.
// Handoff fields: item_name, owner_email, expiry_date,
// renewal_status, uploaded_doc_url, new_expiry_date,
// cert_reference, invoice_file_url, invoice_amount
// ============================================================

// TRIGGER: Google Sheets onChange OR form/webhook document intake
google_sheets.onChange({ field: 'renewal_status', newValue: 'in_progress' })
  OR webhook.receive({ source: 'document_intake_form' })
  -> orchestration_layer.start('renewal_processing_agent')

// ============================================================
// AGENT 3: Renewal Processing Agent
// ============================================================
row = google_sheets.getRow({ row_id: trigger.row_id })

// File document in Notion
IF row.uploaded_doc_url IS NOT NULL:
  notion_file_name = row.item_name + '_' + row.owner_email + '_'
                     + row.new_expiry_date.replace('-','') + '.pdf'
  IF row.notion_page_id IS NOT NULL:
    notion.updatePage({ page_id: row.notion_page_id,
      properties: { Title: notion_file_name, Expiry: row.new_expiry_date,
                    Status: 'filed', Owner: row.owner_email }})
  ELSE:
    notion_page = notion.createPage({ parent_db_id: NOTION_COMPLIANCE_DB_ID,
      properties: { Title: notion_file_name, Item: row.item_name,
                    Owner: row.owner_email, Expiry: row.new_expiry_date,
                    Source: 'automated_renewal', Status: 'filed' }})
    row.notion_page_id = notion_page.id
  google_sheets.updateCell(row.id, 'notion_doc_link', notion_page.url)

// Route invoice to Xero if present
IF row.invoice_attached == true AND row.invoice_file_url IS NOT NULL:
  xero_invoice = xero.createInvoice({
    type:    'ACCPAY',
    status:  'DRAFT',
    contact: { emailAddress: row.owner_email },
    dueDate: row.invoice_due_date,
    lineItems: [{ description: row.item_name + ' renewal',
                  unitAmount:  row.invoice_amount,
                  accountCode: XERO_COMPLIANCE_ACCOUNT_CODE }]
  })
  google_sheets.updateCell(row.id, 'xero_ref', xero_invoice.invoiceID)

// Update register - set pending_review for manager approval gate
google_sheets.updateCells(row.id, {
  expiry_date:    row.new_expiry_date,
  cert_reference: row.cert_reference,
  notion_doc_link: notion_page.url,
  renewal_status: 'pending_review',
  last_updated_at: now()
})

// ============================================================
// MANUAL REVIEW GATE
// Operations Manager opens Notion page, inspects document,
// then sets renewal_status = 'complete' in Google Sheets.
// ============================================================

// On manager approval (onChange listener)
google_sheets.onChange({ field: 'renewal_status', newValue: 'complete' })
  -> slack.postMessage({ channel: '#compliance-alerts',
       text: row.item_name + ' renewal confirmed and filed.',
       completed_at: now() })

// ============================================================
// WEEKLY REPORT TRIGGER (Agent 1 secondary schedule)
// Monday 08:00 - Compliance Monitoring Agent generates report
// ============================================================
report_rows = google_sheets.getRows({ fields: ['item_name','owner_email',
  'expiry_date','renewal_status','days_remaining','doc_link'] })
gmail.sendEmailWithAttachment({
  to:      [OPERATIONS_MANAGER_EMAIL, ...STAKEHOLDER_EMAILS],
  subject: 'Weekly Compliance Status Report - ' + today(),
  attachment: csv(report_rows, filename='compliance_report_' + today() + '.csv')
})