Automation Engine Security

​

Overview

​

Security Architecture

​

1. Trigger Isolation

-- Triggers only fire for user's organization
CREATE TRIGGER after_form_submission_insert
AFTER INSERT ON fw_form_submissions
FOR EACH ROW EXECUTE FUNCTION trigger_automation_on_submission();

-- Function includes organization_id in notification payload
PERFORM pg_notify(
  'automation_trigger',
  json_build_object(
    'trigger_type', 'form_submitted',
    'organization_id', _form_record.organization_id,
    ...
  )::text
);

​

2. Row-Level Security (RLS)

-- Users can only create automations in their organization
CREATE POLICY "Users can manage automation rules"
ON fw_automation_rules FOR ALL
USING (has_org_access(auth.uid(), organization_id));

-- Actions inherit rule's organization context
CREATE POLICY "Users can manage automation actions"
ON fw_automation_actions FOR ALL
USING (EXISTS (
  SELECT 1 FROM fw_automation_rules
  WHERE fw_automation_rules.id = fw_automation_actions.rule_id
    AND has_org_access(auth.uid(), fw_automation_rules.organization_id)
));

​

3. Action Execution Security

​

Email Actions

• Provider: Email is sent via the shared email-provider (Entra or Gmail) using the organization’s configured provider.
• Secrets: ENTRA_CLIENT_SECRET or GMAIL_SERVICE_ACCOUNT_JSON stored as Supabase secrets; sender is org-configured.
• Rate Limiting: Max 100 emails per hour per organization (future)
• Content Sanitization: HTML templates are sanitized before sending

​

Record Update/Create Actions

• RLS Enforcement: All database operations respect Row-Level Security
• Schema Validation: Updates only allowed on whitelisted tables
• Field Whitelisting: Only approved fields can be updated

async function executeUpdateRecord(config: any, triggerData: TriggerData, supabase: any): Promise<any> {
  const { table, record_id, updates } = config;
  
  // Whitelist allowed tables
  const allowedTables = ['fw_form_submissions', 'hr_employee_credentials', 'pf_documents'];
  if (!allowedTables.includes(table)) {
    throw new Error(`Updates not allowed on table: ${table}`);
  }
  
  // Resolve dynamic values
  const resolvedUpdates = resolveDynamicValues(updates, triggerData);
  
  // RLS automatically enforces organization isolation
  const { data, error } = await supabase
    .from(table)
    .update(resolvedUpdates)
    .eq('id', record_id)
    .select()
    .single();

  if (error) throw error;
  return { success: true, updated_record: data };
}

​

Webhook Actions

• URL Validation: Only HTTPS URLs allowed
• Timeout Enforcement: 30 second timeout for webhook calls
• Header Sanitization: No sensitive headers allowed
• Response Size Limit: 1MB max response size

async function executeWebhook(config: any, triggerData: TriggerData): Promise<any> {
  const { url, method = 'POST', headers = {}, body } = config;
  
  // Validate URL
  if (!url.startsWith('https://')) {
    throw new Error('Only HTTPS URLs allowed for webhooks');
  }
  
  // Sanitize headers (no auth headers allowed)
  const sanitizedHeaders = {};
  for (const [key, value] of Object.entries(headers)) {
    if (!['authorization', 'x-api-key', 'api-key'].includes(key.toLowerCase())) {
      sanitizedHeaders[key] = value;
    }
  }
  
  const resolvedBody = resolveDynamicValues(body, triggerData);
  
  // Call webhook with timeout
  const controller = new AbortController();
  const timeout = setTimeout(() => controller.abort(), 30000);
  
  const response = await fetch(url, {
    method,
    headers: { 'Content-Type': 'application/json', ...sanitizedHeaders },
    body: JSON.stringify(resolvedBody),
    signal: controller.signal,
  });
  
  clearTimeout(timeout);

  if (!response.ok) {
    throw new Error(`Webhook failed: ${response.status} ${response.statusText}`);
  }

  return { success: true, status: response.status };
}

​

4. Condition Evaluation

function resolveDynamicValues(obj: any, triggerData: TriggerData): any {
  // Only resolve {{path.to.value}} patterns
  if (typeof obj === 'string' && obj.startsWith('{{') && obj.endsWith('}}')) {
    const path = obj.slice(2, -2).trim();
    return getNestedValue(triggerData, path);
  }
  
  // Recursively resolve arrays and objects
  if (Array.isArray(obj)) {
    return obj.map(item => resolveDynamicValues(item, triggerData));
  }
  
  if (obj && typeof obj === 'object') {
    const resolved: any = {};
    for (const [key, value] of Object.entries(obj)) {
      resolved[key] = resolveDynamicValues(value, triggerData);
    }
    return resolved;
  }
  
  return obj;
}

​

Security Best Practices

​

For Automation Authors

1. Test in Dry-Run Mode First - Always test automations with dry_run: true before activating
2. Use Specific Conditions - Avoid broad conditions like status equals 'submitted'
3. Limit Webhook URLs - Only call trusted external services
4. Review Logs Regularly - Check fw_automation_logs for failed executions
5. Don’t Expose PHI/PII - Never send sensitive data to external webhooks

​

For Security Reviewers

1. Audit Active Automations - Review fw_automation_rules with status ‘active’
2. Check Email Recipients - Verify email actions don’t send to external addresses
3. Validate Webhook URLs - Ensure webhooks only call approved domains
4. Monitor Execution Logs - Look for patterns indicating abuse:
   • High failure rates
   • Excessive webhook calls
   • Unusual update patterns
5. Review Action Configurations - Flag automations that:
   • Update sensitive tables
   • Send data to external services
   • Execute frequently (> 100 times/day)

​

Known Limitations

​

1. Approval Workflow ✅ Implemented

• Automations with requires_approval=true cannot be activated without approval
• High-risk automations auto-flagged:
  • Call webhook actions (external data exposure)
  • Update critical tables (hr_employees, pf_documents, fw_form_submissions)
  • Created by non-admin users
• RLS policies prevent activation of unapproved rules
• Approval tracked: approved_by, approved_at, approval_notes

• Org admins can approve via Automation Detail page
• Approval history logged to fw_automation_logs
• Re-approval required after major config changes (future enhancement)

​

2. Circuit Breaker ✅ Implemented

• check_automation_circuit_breaker(rule_id, execution_status) function tracks failures
• Auto-pauses automation after 5 consecutive failures
• Sets circuit_breaker_tripped=true and circuit_breaker_tripped_at=now()
• Sends in-app notification to rule creator
• Manual reset required (via Automation Detail page)

• consecutive_failures - Resets to 0 on success
• last_execution_at - Timestamp of last execution attempt
• circuit_breaker_tripped - Boolean flag for UI indicators

• Prevents resource exhaustion from failing webhooks
• Reduces noise in error logs
• Alerts rule creator immediately when pattern detected

​

3. No Sandbox Environment

• Isolate test executions from production
• Use test email addresses
• Mock external webhooks

​

Incident Response

​

Automation Abuse Detected

1. Immediate Actions:
   • Pause affected automation(s)
   • Block user if malicious
   • Review execution logs
2. Investigation:
   • Check fw_automation_logs for execution patterns
   • Review triggered actions
   • Identify affected data
3. Remediation:
   • Fix automation configuration
   • Restore any corrupted data
   • Update RLS policies if needed

​

External Webhook Compromise

1. Immediate Actions:
   • Disable all webhook actions
   • Revoke any leaked credentials
   • Notify affected parties
2. Investigation:
   • Review webhook URLs in all automations
   • Check external service logs
   • Assess data exposure
3. Remediation:
   • Update webhook URL whitelist
   • Implement webhook signature verification
   • Re-enable after security review

​

Testing Checklist

• RLS prevents cross-organization automation execution
• Email actions respect rate limits
• Webhook actions enforce HTTPS
• Record updates respect RLS policies
• Dynamic value resolution prevents injection
• Dry-run mode works without side effects
• Audit logs capture all executions