Developer Handover Pack

Developer Handover Pack

Quality Control Checklist Automation

The QC Review Agent is the core classification and routing engine. It fires on every new Google Forms submission, reads all checklist item responses, and applies the configured severity classification rules to identify which items have failed and at what priority level. For any inspection containing one or more critical or major failures, the agent creates a linked corrective action record in Airtable, posts a structured Slack alert to the operations manager channel, and assigns the corrective action to the appropriate team member based on the failure category mapping table. For inspections with no failures, it writes the inspection record to Airtable and exits cleanly. The agent does not make a final pass or fail decision on a batch; that authority stays with the operations manager. Estimated build time: 14 hours. Complexity: Moderate.

// Input
form_submission_id: string          // Google Forms response ID
inspector_name: string              // Form field: 'Inspector Name'
batch_or_unit_id: string            // Form field: 'Batch / Unit Reference'
product_category: string            // Form field: 'Product Category'
inspection_timestamp: ISO8601       // Form submission timestamp (UTC)
checklist_items: array[{
  item_label: string,               // Checklist criterion label
  result: enum[pass|fail|na],       // Inspector response
  notes: string|null               // Optional inspector note
}]

// Processing (internal)
failed_items = checklist_items.filter(result == 'fail')
severity_map = load_rules('severity_classification_table')  // Airtable lookup
classified_failures = failed_items.map(item => severity_map[item.item_label])
critical_present = classified_failures.any(severity == 'critical')
major_present    = classified_failures.any(severity == 'major')
ca_owner = load_rules('category_owner_map')[product_category]

// Output (all inspections)
airtable_inspection_record: {
  record_id: string,                // Airtable auto-generated
  inspector_name: string,
  batch_or_unit_id: string,
  product_category: string,
  inspection_timestamp: ISO8601,
  overall_result: enum[pass|fail],
  failed_item_count: integer,
  severity_level: enum[none|minor|major|critical]
}

// Output (critical or major failures only)
airtable_corrective_action_record: {
  linked_inspection_id: string,     // Linked to inspection record above
  defect_description: string,
  severity_level: enum[major|critical],
  assigned_to: string,              // ca_owner resolved from category map
  due_date: date,                   // inspection_date + SLA_days from rules table
  status: 'Open'
}
slack_alert_payload: {
  channel: '#qc-alerts',            // Must be confirmed before build
  text: 'Critical QC failure: {batch_or_unit_id} | Category: {product_category} | Failed items: {failed_item_count} | Assigned to: {ca_owner} | View: {airtable_ca_link}'
}

The Reporting Agent runs on a fixed weekly schedule every Monday at 07:00 in the process owner's local timezone. It queries the Airtable QC Inspections table for all records with an inspection_timestamp falling within the previous seven calendar days, aggregates pass and fail counts by product_category and by inspector_name, calculates overall pass rate, lists the top three failure categories by frequency, and retrieves all corrective action records with status 'Open' that are linked to inspections in the same period. It then formats these results into a plain-language summary email and sends it via Gmail to the management distribution list. The agent does not modify any Airtable records; it is read-only on the data layer. Estimated build time: 8 hours. Complexity: Simple.

// Input (Airtable query)
query_table: 'QC Inspections'
filter: inspection_timestamp >= [Monday 00:00 UTC -7 days] AND <= [Sunday 23:59 UTC]
fields_returned: [inspector_name, product_category, overall_result, failed_item_count, severity_level, inspection_timestamp]

query_table: 'Corrective Actions'
filter: status == 'Open' AND linked_inspection_id IN [records from above query]
fields_returned: [linked_inspection_id, defect_description, severity_level, assigned_to, due_date]

// Processing (internal)
total_inspections = count(inspection_records)
pass_count        = count(overall_result == 'pass')
fail_count        = count(overall_result == 'fail')
pass_rate_pct     = (pass_count / total_inspections) * 100
by_category       = group_by(product_category).count_pass_fail()
by_inspector      = group_by(inspector_name).count_pass_fail()
top_failure_cats  = by_category.sort_by(fail_count desc).slice(0,3)
open_ca_count     = count(corrective_action_records)

// Output
gmail_email: {
  to: [management_distribution_list],   // Confirm list before build
  from: ops-reports@[YourCompany.com],  // Confirm sender address
  subject: 'Weekly QC Summary: {date_range} | Pass rate: {pass_rate_pct}%',
  body_sections: [
    'Overview: {total_inspections} inspections | {pass_count} passed | {fail_count} failed | Pass rate: {pass_rate_pct}%',
    'Top failure categories: {top_failure_cats}',
    'By inspector: {by_inspector}',
    'Open corrective actions: {open_ca_count} ({list of CA summaries})'
  ]
}

// ============================================================
// TRIGGER: Inspector submits Google Forms inspection form
// ============================================================
google_forms.on_submit -> event: {
  form_submission_id: 'gf_resp_<uuid>',
  inspector_name: string,
  batch_or_unit_id: string,
  product_category: string,
  inspection_timestamp: ISO8601 (UTC),
  checklist_items: [
    { item_label: string, result: 'pass'|'fail'|'na', notes: string|null },
    ...
  ]
}

// ============================================================
// HANDOFF 1: Forms event arrives at orchestration layer
// Orchestration layer receives webhook payload from Google Forms
// ============================================================
orchestration_layer.receive(google_forms.event)
  -> validate: form_submission_id not in airtable['QC Inspections'].submission_id  // dedupe check
  -> if duplicate: log_error(airtable['Error Log'], reason='duplicate_submission') -> EXIT

// ============================================================
// AGENT: QC Review Agent begins
// ============================================================

// Step A: Write inspection record to Airtable
failed_items = checklist_items.filter(result == 'fail')
airtable['QC Inspections'].create({
  submission_id: form_submission_id,
  inspector_name: inspector_name,
  batch_or_unit_id: batch_or_unit_id,
  product_category: product_category,
  inspection_timestamp: inspection_timestamp,
  overall_result: failed_items.length > 0 ? 'fail' : 'pass',
  failed_item_count: failed_items.length,
  severity_level: max(classified_failures[].severity) | 'none'
}) -> inspection_record_id: string

// Step B: Classify failures against severity rules table
severity_rules = airtable['Severity Rules'].fetch_all()   // {item_label, severity_level}
category_owner_map = airtable['Category Owner Map'].fetch_all() // {product_category, owner_name, slack_user_id}
classified_failures = failed_items.map(item -> {
  item_label: item.item_label,
  severity: severity_rules.lookup(item.item_label) | 'minor',
  notes: item.notes
})
max_severity = max(classified_failures[].severity)  // critical > major > minor > none

// Step C: Decision gate
if max_severity in ['critical', 'major']:

  // Step D: Create corrective action record in Airtable
  sla_days = { critical: 1, major: 3, minor: 7 }[max_severity]
  ca_owner = category_owner_map.lookup(product_category)
  airtable['Corrective Actions'].create({
    linked_inspection_id: inspection_record_id,
    defect_description: classified_failures.filter(severity in ['critical','major']).format_list(),
    severity_level: max_severity,
    assigned_to: ca_owner.owner_name,
    assigned_slack_user_id: ca_owner.slack_user_id,
    due_date: inspection_timestamp + sla_days (business days),
    status: 'Open'
  }) -> corrective_action_record_id: string
       corrective_action_airtable_url: string

  // Step E: Post Slack alert to ops channel
  slack.post_message({
    channel: '#qc-alerts',
    text: 'QC FAILURE [{max_severity}] | Batch: {batch_or_unit_id} | Category: {product_category} | Inspector: {inspector_name} | Failed items: {failed_item_count} | Assigned to: {ca_owner.owner_name} | Due: {due_date} | {corrective_action_airtable_url}'
  })

  // Step F: Send Slack DM to corrective action owner
  slack.post_dm({
    user_id: ca_owner.slack_user_id,
    text: 'You have been assigned a [{max_severity}] corrective action for batch {batch_or_unit_id}. Due: {due_date}. View details: {corrective_action_airtable_url}'
  })

else:
  // No critical or major failures: inspection record already written, no CA created
  EXIT  // QC Review Agent complete

// ============================================================
// HUMAN STEP: Operations Manager confirms corrective action resolved
// Manager reviews physical resolution and updates Airtable record
// airtable['Corrective Actions'][corrective_action_record_id].status -> 'Closed'
// ============================================================

// ============================================================
// HANDOFF 2: Scheduled trigger fires (Monday 07:00 local time)
// Orchestration layer activates Reporting Agent
// ============================================================

// ============================================================
// AGENT: Reporting Agent begins
// ============================================================

// Step G: Query Airtable for previous 7 days of inspections
week_start = last_monday_00:00 (UTC)
week_end   = last_sunday_23:59 (UTC)
inspection_records = airtable['QC Inspections'].filter(
  inspection_timestamp >= week_start AND inspection_timestamp <= week_end
)  // paginated, pageSize: 100

// Step H: Query open corrective actions linked to those inspections
open_cas = airtable['Corrective Actions'].filter(
  status == 'Open' AND linked_inspection_id IN inspection_records[].record_id
)

// Step I: Aggregate metrics
total   = inspection_records.count()
passed  = inspection_records.filter(overall_result == 'pass').count()
failed  = inspection_records.filter(overall_result == 'fail').count()
pass_rate_pct = (passed / total) * 100  // rounded to 1 decimal place
by_category   = group_by(product_category) -> { category, pass_count, fail_count }
by_inspector  = group_by(inspector_name)   -> { inspector, pass_count, fail_count }
top_failures  = by_category.sort(fail_count desc).slice(0, 3)

// Step J: Send summary email via Gmail
gmail.send({
  from: 'ops-reports@[YourCompany.com]',
  to: [management_distribution_list],
  subject: 'Weekly QC Summary: {week_start_display} to {week_end_display} | Pass rate {pass_rate_pct}%',
  body: format_plain_text({
    overview: { total, passed, failed, pass_rate_pct },
    top_failure_categories: top_failures,
    by_inspector: by_inspector,
    open_corrective_actions: open_cas[].{ defect_description, severity_level, assigned_to, due_date }
  })
})  // Reporting Agent complete