Smart Repeat Caller Detection & Routing for ViciDial/Asterisk

Custom Perl AGI + MySQL Implementation Guide

Table of Contents

1. Introduction
2. Architecture Overview
3. Prerequisites
4. Database Schema
5. The Perl AGI Script
6. Asterisk Dialplan Integration
7. ViciDial Inbound Group Configuration
8. DID-to-Company Name Mapping
9. Blacklist Support
10. Testing Procedures
11. Monitoring & Analytics
12. Tuning & Edge Cases
13. Production Checklist

Introduction

The Problem

In a busy call center, every inbound call is treated identically. A customer who called yesterday about a billing dispute gets the same generic greeting and the same random agent as someone calling for the first time. This is a missed opportunity.

Returning callers have context. They have an open issue, an ongoing negotiation, or a half-completed sale. Routing them to a general-purpose agent who knows nothing about their history wastes time, frustrates the customer, and kills conversion rates.

The Solution

This guide walks you through building a repeat caller detection and routing system that sits between your SIP trunks and ViciDial's inbound engine. When a call arrives:

1. A custom Perl AGI script queries a MySQL table to check whether this caller (identified by CLI + DID combination) has called within a configurable time window (default: 7 days).
2. First-time callers are routed to a standard inbound group staffed by general agents.
3. Repeat callers are routed to a specialized inbound group staffed by experienced agents who handle returning customers, upsells, and issue resolution.
4. Optionally, the system looks up the company name associated with the DID and sets it as the caller ID name, so the agent immediately sees which brand the customer is calling about.

Why It Works

The numbers speak for themselves once you deploy this:

• Repeat callers convert 2-4x higher than first-time callers. They already know your product; they are calling back because they are interested. Sending them to your best closers instead of random agents dramatically improves close rates.
• Customer satisfaction rises because experienced agents can reference prior interactions and skip the "let me explain everything again" loop.
• Average handle time drops for repeat calls because agents in the specialized group are trained to handle follow-ups efficiently.
• Agent utilization improves because your general pool handles the high volume of first-time calls, while your smaller experienced team focuses on high-value repeat interactions.

This system has been running in production on a multi-site ViciDial deployment handling 1,400+ DIDs, processing tens of thousands of calls, and it works reliably with negligible overhead (the AGI lookup adds under 50ms to call setup time).

Architecture Overview

                    PSTN / SIP Trunk
                          |
                          v
                   +--------------+
                   |  Asterisk    |
                   |  DID Routing |
                   +--------------+
                          |
              DID matched -> Route to extension 999888777
                          |
                          v
                 +------------------+
                 | doppia_router.agi|
                 |   (Perl AGI)    |
                 +------------------+
                    |           |
          +--------+           +--------+
          v                             v
   Query doppia_calls            Query did_company_map
   (repeat detection)            (company name lookup)
          |                             |
          v                             v
   Set is_repeat=Y/N            Set CALLERID(name)
          |
          v
   +------+------+
   |             |
   v             v
 REPEAT       FIRST-TIME
   |             |
   v             v
 "doppia"     "normal"
 ingroup      ingroup
   |             |
   v             v
 Experienced   General
 agents        agents

How the Pieces Fit Together

Prerequisites

• ViciDial 2.14+ (SVN or newer) with a working inbound setup
• Asterisk 11+ (tested on 11.x through 20.x)
• MariaDB/MySQL 5.5+ (the ViciDial database server)
• Perl with DBI and DBD::mysql modules installed
• Root/sudo access to the ViciDial server
• At least one inbound DID already routing through ViciDial

Verify Perl Dependencies

# Check that the required Perl modules are available
perl -MDBI -e 'print "DBI version: $DBI::VERSION\n"'
perl -MDBD::mysql -e 'print "DBD::mysql OK\n"'

# If missing, install them:
# RHEL/CentOS:
yum install perl-DBI perl-DBD-MySQL

# Debian/Ubuntu:
apt-get install libdbi-perl libdbd-mysql-perl

# openSUSE (ViciBox):
zypper install perl-DBI perl-DBD-mysql

Database Schema

All tables are created in the same database ViciDial uses (typically asterisk). Connect with the credentials from /etc/astguiclient.conf.

doppia_calls Table

This is the core table. It stores one row per unique caller (identified by phone number + DID). When the same number calls the same DID again, the last_call_time is updated in place.

CREATE TABLE IF NOT EXISTS doppia_calls (
    id            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    phone_number  VARCHAR(32)     NOT NULL COMMENT 'Caller CLI (E.164 or national format)',
    did           VARCHAR(50)     NOT NULL COMMENT 'The DID that was called',
    last_call_time DATETIME       NOT NULL COMMENT 'Timestamp of most recent call',
    PRIMARY KEY (id),
    UNIQUE KEY idx_phone_did (phone_number, did),
    KEY idx_last_call_time (last_call_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  COMMENT='Tracks unique callers per DID for repeat detection';

Design notes:

• The UNIQUE KEY on (phone_number, did) ensures one row per caller-DID pair. The AGI script uses an UPDATE-then-INSERT pattern (upsert) to maintain this.
• The idx_last_call_time index supports efficient cleanup of old records and analytics queries.
• phone_number is VARCHAR(32) to accommodate international formats with country codes and any leading + signs.
• At ~29K records after 3.5 months of production use across 1,400+ DIDs, this table stays small and fast. Even at 500K rows, the indexed lookups take under 1ms.

did_company_map Table

Maps each DID to a company or brand name. When an agent receives the call, they see the company name on their screen instead of a raw phone number.

CREATE TABLE IF NOT EXISTS did_company_map (
    did           VARCHAR(50)     NOT NULL,
    company_name  VARCHAR(100)    NOT NULL COMMENT 'Displayed as CALLERID(name) to agents',
    PRIMARY KEY (did)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  COMMENT='Maps DIDs to company names for agent display';

Populate it with your DID inventory:

-- Example: map your DIDs to the brands they represent
INSERT INTO did_company_map (did, company_name) VALUES
    ('441234567890', 'Acme Plumbing UK'),
    ('441234567891', 'Acme Heating UK'),
    ('390212345678', 'Solar Italia'),
    ('33145678901',  'CleanTech France');

-- Bulk-load from CSV:
-- LOAD DATA LOCAL INFILE '/tmp/did_map.csv'
--   INTO TABLE did_company_map
--   FIELDS TERMINATED BY ',' ENCLOSED BY '"'
--   LINES TERMINATED BY '\n'
--   (did, company_name);

doppia_blacklist Table (Optional)

Blocks specific callers from reaching agents. The AGI checks this table before routing.

CREATE TABLE IF NOT EXISTS doppia_blacklist (
    id            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    phone_number  VARCHAR(32)     NOT NULL,
    did           VARCHAR(50)     NOT NULL DEFAULT '' COMMENT 'Empty = blocked on all DIDs',
    reason        VARCHAR(255)    DEFAULT NULL,
    date_added    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_phone (phone_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  COMMENT='Blacklisted callers - blocked before reaching agents';

-- Block a specific number on all DIDs
INSERT INTO doppia_blacklist (phone_number, reason)
VALUES ('+441234000000', 'Repeated abusive calls - blocked per manager request');

-- Block a number on a specific DID only
INSERT INTO doppia_blacklist (phone_number, did, reason)
VALUES ('+441234000001', '441234567890', 'Competitor probing this DID');

The Perl AGI Script

Save this as /var/lib/asterisk/agi-bin/doppia_router.agi and make it executable.

Complete Script

#!/usr/bin/perl
#
# doppia_router.agi - Repeat Caller Detection & Routing for ViciDial
#
# Called from Asterisk dialplan via AGI().
# Checks whether the caller (CLI + DID) has called within a configurable
# time window. Sets channel variables that the dialplan uses to route
# the call to the appropriate ViciDial inbound group.
#
# Arguments:
#   $ARGV[0] = Time window in seconds (default: 604800 = 7 days)
#   $ARGV[1] = Logical DID override (default: uses EXTEN)
#
# Channel variables set:
#   ${did}         - The resolved DID
#   ${is_repeat}   - "Y" if caller has called within window, "N" otherwise
#   ${is_blocked}  - "Y" if caller is blacklisted, "N" otherwise
#   ${company}     - Company name from did_company_map (if found)
#
# Usage in dialplan:
#   AGI(doppia_router.agi,604800,${EXTEN})
#

use strict;
use warnings;
use DBI;

$|++;    # Unbuffered output (required for AGI)

# ============================================================
# AGI Helper Functions
# ============================================================

my %AGI;

sub agi_verbose {
    my ($msg, $level) = @_;
    $level ||= 1;
    print "VERBOSE \"$msg\" $level\n";
}

sub agi_get_variable {
    my ($var) = @_;
    print "GET VARIABLE $var\n";
    my $response = <STDIN>;
    return '' unless defined $response;
    if ($response =~ /result=1\s*\((.*)\)/) {
        return $1;
    }
    return '';
}

sub agi_set_variable {
    my ($var, $val) = @_;
    print "SET VARIABLE $var $val\n";
    my $response = <STDIN>;    # consume the response
}

# ============================================================
# Read AGI Environment (Asterisk sends this on STDIN)
# ============================================================

while (<STDIN>) {
    chomp;
    last if $_ eq '';
    my ($k, $v) = split(/:\s*/, $_, 2);
    $AGI{$k} = $v if defined $k;
}

# ============================================================
# Parse Arguments
# ============================================================

my $TIME_WIN   = $ARGV[0] || 604800;     # Default: 7 days in seconds
my $DOPPIA_DID = $ARGV[1] || '';          # Logical DID for this rule

my $uniqueid   = $AGI{'agi_uniqueid'} || '';
my $chan        = $AGI{'agi_channel'}  || '';

# Retrieve caller and dialed number info from the channel
my $CLI   = agi_get_variable('CALLERID(num)');
my $EXTEN = agi_get_variable('EXTEN');
my $DNID  = agi_get_variable('DNID');

# If no explicit DID argument, use the dialed extension
$DOPPIA_DID ||= $EXTEN;

agi_verbose("DOPPIA: CLI=$CLI EXTEN=$EXTEN DNID=$DNID "
          . "TIME_WIN=$TIME_WIN DOPPIA_DID=$DOPPIA_DID", 1);

# ============================================================
# Read Database Config from ViciDial's astguiclient.conf
# ============================================================

my $conf_file = '/etc/astguiclient.conf';
my %dbconf;

if (open my $CFG, '<', $conf_file) {
    while (<$CFG>) {
        chomp;
        next if /^\s*#/;
        if (/^VARDB_server\s*=>\s*(.+)$/)   { $dbconf{server}   = $1; }
        if (/^VARDB_database\s*=>\s*(.+)$/) { $dbconf{database} = $1; }
        if (/^VARDB_user\s*=>\s*(.+)$/)     { $dbconf{user}     = $1; }
        if (/^VARDB_pass\s*=>\s*(.+)$/)     { $dbconf{pass}     = $1; }
        if (/^VARDB_port\s*=>\s*(.+)$/)     { $dbconf{port}     = $1; }
    }
    close $CFG;
} else {
    agi_verbose("DOPPIA ERROR: cannot open $conf_file: $!", 1);
}

# Defaults matching standard ViciDial install
$dbconf{server}   ||= 'localhost';
$dbconf{database} ||= 'asterisk';
$dbconf{user}     ||= 'cron';
$dbconf{pass}     ||= '';
$dbconf{port}     ||= 3306;

# ============================================================
# Initialize result variables
# ============================================================

my ($is_repeat, $is_blocked) = ('N', 'N');
my $RESOLVED_DID = $DOPPIA_DID;
my $company_name = '';

# ============================================================
# Connect to Database
# ============================================================

my $dbh;
eval {
    my $dsn = "DBI:mysql:database=$dbconf{database};"
            . "host=$dbconf{server};port=$dbconf{port}";
    $dbh = DBI->connect(
        $dsn,
        $dbconf{user},
        $dbconf{pass},
        {
            PrintError => 0,
            RaiseError => 0,
            AutoCommit => 1,
            mysql_connect_timeout => 3,    # Don't hang if DB is slow
        }
    );
};

if (!$dbh) {
    agi_verbose("DOPPIA ERROR: DB connect failed: "
              . ($DBI::errstr || 'unknown'), 1);
    goto RESULT;
}

# ============================================================
# Ensure tables exist (safe for first run; can remove later)
# ============================================================

eval {
    $dbh->do(q{
        CREATE TABLE IF NOT EXISTS doppia_calls (
            id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
            phone_number  VARCHAR(32)  NOT NULL,
            did           VARCHAR(50)  NOT NULL,
            last_call_time DATETIME    NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY idx_phone_did (phone_number, did),
            KEY idx_last_call_time (last_call_time)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    });
};
if ($@) {
    agi_verbose("DOPPIA ERROR: table creation failed: $@", 1);
    goto RESULT;
}

# ============================================================
# Step 1: Check Blacklist
# ============================================================

eval {
    my $sth = $dbh->prepare(q{
        SELECT 1 FROM doppia_blacklist
        WHERE phone_number = ?
          AND (did = ? OR did = '' OR did IS NULL)
        LIMIT 1
    });
    if ($sth && $sth->execute($CLI, $DOPPIA_DID)) {
        my ($found) = $sth->fetchrow_array;
        $is_blocked = 'Y' if $found;
        $sth->finish;
    }
};
if ($is_blocked eq 'Y') {
    agi_verbose("DOPPIA BLOCKED: CLI=$CLI DID=$DOPPIA_DID", 1);
    goto RESULT;
}

# ============================================================
# Step 2: Check Repeat Caller (within time window)
# ============================================================

my $cutoff_epoch = time() - $TIME_WIN;

eval {
    my $sth = $dbh->prepare(q{
        SELECT last_call_time
        FROM doppia_calls
        WHERE phone_number = ? AND did = ?
          AND last_call_time >= FROM_UNIXTIME(?)
        ORDER BY last_call_time DESC
        LIMIT 1
    });
    if ($sth && $sth->execute($CLI, $DOPPIA_DID, $cutoff_epoch)) {
        my ($last_call_time) = $sth->fetchrow_array;
        if ($last_call_time) {
            $is_repeat = 'Y';
            agi_verbose("DOPPIA REPEAT: CLI=$CLI last_call=$last_call_time", 1);
        }
        $sth->finish;
    } else {
        agi_verbose("DOPPIA ERROR: SELECT failed: "
                  . ($dbh->errstr || 'unknown'), 1);
    }
};

# ============================================================
# Step 3: Upsert - Update existing record or insert new one
# ============================================================

eval {
    my $sth_upd = $dbh->prepare(q{
        UPDATE doppia_calls
           SET last_call_time = NOW()
         WHERE phone_number = ? AND did = ?
    });
    if ($sth_upd && $sth_upd->execute($CLI, $DOPPIA_DID)) {
        if ($sth_upd->rows == 0) {
            # No existing row - insert a new one
            my $sth_ins = $dbh->prepare(q{
                INSERT INTO doppia_calls (phone_number, did, last_call_time)
                VALUES (?, ?, NOW())
            });
            if (!$sth_ins || !$sth_ins->execute($CLI, $DOPPIA_DID)) {
                agi_verbose("DOPPIA ERROR: INSERT failed: "
                          . ($dbh->errstr || 'unknown'), 1);
            }
        }
    } else {
        agi_verbose("DOPPIA ERROR: UPDATE failed: "
                  . ($dbh->errstr || 'unknown'), 1);
    }
};

# ============================================================
# Step 4: Look up company name from did_company_map
# ============================================================

eval {
    my $sth = $dbh->prepare(q{
        SELECT company_name FROM did_company_map WHERE did = ? LIMIT 1
    });
    if ($sth && $sth->execute($DOPPIA_DID)) {
        ($company_name) = $sth->fetchrow_array;
        $company_name ||= '';
        $sth->finish;
    }
};

if ($company_name) {
    agi_verbose("DOPPIA COMPANY: $company_name for DID $DOPPIA_DID", 1);
}

# ============================================================
# Disconnect and set results
# ============================================================

$dbh->disconnect if $dbh;

RESULT:

agi_verbose("DOPPIA RESULT: did=$RESOLVED_DID is_repeat=$is_repeat "
          . "is_blocked=$is_blocked company=$company_name", 1);

# Set channel variables for the dialplan to consume
agi_set_variable('did',        $RESOLVED_DID);
agi_set_variable('is_repeat',  $is_repeat);
agi_set_variable('is_blocked', $is_blocked);

# Set caller ID name to the company name so agents see it on screen
if ($company_name) {
    agi_set_variable('CALLERID(name)', $company_name);
}

exit 0;

Install the Script

# Copy to the AGI directory
cp doppia_router.agi /var/lib/asterisk/agi-bin/doppia_router.agi

# Set ownership and permissions
chown asterisk:asterisk /var/lib/asterisk/agi-bin/doppia_router.agi
chmod 755 /var/lib/asterisk/agi-bin/doppia_router.agi

# Verify it parses without errors
perl -c /var/lib/asterisk/agi-bin/doppia_router.agi
# Expected output: doppia_router.agi syntax OK

How the Upsert Logic Works

The script uses an UPDATE-first, INSERT-if-needed pattern rather than INSERT ... ON DUPLICATE KEY UPDATE. This is intentional:

1. UPDATE the last_call_time for the given (phone_number, did) pair.
2. Check $sth_upd->rows. If zero rows were affected, the caller is new.
3. INSERT a new row only if the UPDATE matched nothing.

This approach is safe under concurrent calls because the worst case is a duplicate key error on INSERT (which the error handling catches gracefully). The call still routes correctly because the repeat detection query ran before the upsert.

Asterisk Dialplan Integration

The Routing Extension

Add this to your extensions.conf (or extensions_custom.conf depending on your setup). This is the entry point that all doppia-enabled DIDs route to.

; ================================================================
; DOPPIA REPEAT CALLER ROUTING
; All DIDs configured for repeat detection route here.
; In ViciDial DID admin: set DID Route = EXTEN, Extension = 999888777
; ================================================================

exten => 999888777,1,NoOp(DOPPIA ENTRY: CLI=${CALLERID(num)} EXTEN=${EXTEN})

; Answer the channel so we can run AGI
same => n,Answer()

; Optional: brief ring-back delay (2-5 seconds)
; This simulates normal business phone behavior so callers
; hear ringing rather than dead air while the AGI runs.
; Adjust the Ringing() + Wait() to taste.
same => n,Ringing()
same => n,Wait(${RAND(2,5)})

; Run the AGI script
; Arg 1: time window in seconds (604800 = 7 days)
; Arg 2: the DID (passed via ${EXTEN} or hardcoded)
same => n,AGI(doppia_router.agi,604800,${EXTEN})

; Read back the AGI decisions
same => n,NoOp(DOPPIA DECISION: DID=${did} REPEAT=${is_repeat} BLOCKED=${is_blocked})

; If blacklisted, send to blocked handler
same => n,GotoIf($["${is_blocked}"="Y"]?doppia-blocked,1)

; Route based on repeat status:
;   Repeat caller  -> "doppia" inbound group (experienced agents)
;   First-time     -> "normal" inbound group (general agents)
same => n,Set(target_ingroup=${IF($["${is_repeat}"="Y"]?doppia:normal)})
same => n,NoOp(DOPPIA TARGET INGROUP=${target_ingroup})

; Hand off to ViciDial's inbound engine
same => n,Set(__VICIDIAL_INBOUND=Y)
same => n,Set(__CALL_LOG_OVERRIDE=INBOUND)
same => n,AGI(agi-VDAD_ALL_inbound.agi,CIDLOOKUP-----LB-----${target_ingroup}-----${did}-----${CALLERID(num)}-----park----------999-----1)

; FALLBACK: if the ViciDial AGI exits unexpectedly, send to a ring group
; so the call is never silently dropped
same => n,NoOp(AGI FALLBACK: VDAD AGI exited abnormally)
same => n,Goto(default,your-fallback-extension,1)

; ----------------------------------------------------------------
; Blacklisted caller handler
; ----------------------------------------------------------------
exten => doppia-blocked,1,NoOp(DOPPIA BLOCKED: CLI=${CALLERID(num)} DID=${did})
same => n,Playback(ss-noservice)
same => n,Hangup(21)

About the Ring-Back Delay

The Ringing() + Wait(${RAND(2,5)}) block serves two purposes:

1. User experience: Callers hear standard ringing tones rather than silence while the AGI script executes its database queries. This feels like a normal phone call.
2. Timing variation: The randomized 2-5 second delay prevents the routing from feeling "robotic" (instant answer followed by immediate transfer). It mimics how a real receptionist would pick up and transfer.

You can remove this block entirely if you prefer instant routing, or increase the range for campaigns where a longer ring feels more natural.

Reload After Changes

# Reload the dialplan without restarting Asterisk
asterisk -rx "dialplan reload"

# Verify the extension loaded
asterisk -rx "dialplan show 999888777@default"

ViciDial Inbound Group Configuration

You need two inbound groups: one for first-time callers and one for repeat callers.

Create the "normal" Inbound Group

In the ViciDial admin panel:

1. Navigate to Admin > Inbound Groups > Add New Group
2. Configure:

3. Assign your general agents to this group under Closer Campaigns.

Create the "doppia" Inbound Group

1. Navigate to Admin > Inbound Groups > Add New Group
2. Configure:

3. Assign your experienced/senior agents to this group.

Agent Assignment Strategy

The power of this system comes from deliberate agent assignment:

• Normal group: All agents. This is your general pool. High volume, standard scripts.
• Doppia group: Senior agents only. These agents should:
  • Have access to customer history/CRM
  • Be trained in retention and upsell techniques
  • Have authority to make decisions (discounts, escalations)
  • Optionally, also be in the Normal group as overflow

Tip: You can assign senior agents to BOTH groups. ViciDial's load balancing will route repeat callers to them preferentially via the doppia group, and they will also take normal calls when the doppia queue is empty.

Configuring the DID in ViciDial

For each DID you want to use with repeat caller detection:

1. Go to Admin > Inbound DIDs
2. Find or create the DID entry
3. Set:
   • DID Route: EXTEN
   • Extension: 999888777
   • Extension Context: default

This tells ViciDial to send the call to extension 999888777 in the default context, which is where our doppia routing logic lives.

You can apply this to individual DIDs or set it as the routing for a DID range. Every DID routed to 999888777 will automatically get repeat caller detection.

DID-to-Company Name Mapping

Why Map DIDs to Company Names

If your call center handles multiple brands or campaigns through different DIDs, agents need to know which brand the caller is reaching out to. Without this, an agent answering a call on a plumbing DID might greet them with a solar energy script.

The did_company_map table solves this. The AGI script looks up the DID and sets CALLERID(name) to the company name. The agent sees this on their ViciDial agent screen, Zoiper softphone, or any SIP client.

Populating the Map

-- One at a time
INSERT INTO did_company_map (did, company_name)
VALUES ('441234567890', 'Acme Plumbing London');

-- Bulk insert
INSERT INTO did_company_map (did, company_name) VALUES
    ('441234567890', 'Acme Plumbing London'),
    ('441234567891', 'Acme Plumbing Manchester'),
    ('441234567892', 'Acme Heating UK'),
    ('390212345678', 'Solar Italia Milano'),
    ('390212345679', 'Solar Italia Roma'),
    ('33145678901',  'CleanTech Paris');

Generating the Map from Existing DIDs

If you already have DIDs in ViciDial with descriptive names, you can bootstrap the map:

-- Generate INSERT statements from your existing DID configuration
SELECT CONCAT(
    'INSERT IGNORE INTO did_company_map (did, company_name) VALUES (''',
    did_pattern, ''', ''',
    did_description, ''');'
)
FROM vicidial_inbound_dids
WHERE did_route = 'EXTEN'
  AND extension = '999888777'
  AND did_description != '';

Verifying the Mapping

-- Check how many DIDs are mapped
SELECT COUNT(*) AS mapped_dids FROM did_company_map;

-- Find DIDs that are routed through doppia but NOT mapped to a company
SELECT d.did_pattern
FROM vicidial_inbound_dids d
LEFT JOIN did_company_map m ON d.did_pattern = m.did
WHERE d.extension = '999888777'
  AND m.did IS NULL;

Blacklist Support

The optional doppia_blacklist table lets you block specific callers before they reach any agent. This is checked before the repeat caller logic.

Use Cases

• Abusive callers: Block numbers that repeatedly harass agents.
• Competitors: Block known competitor numbers from probing your DIDs.
• Test numbers: Block internal test numbers from polluting your repeat caller data.

Managing the Blacklist

-- Add a number to the blacklist (blocked on ALL DIDs)
INSERT INTO doppia_blacklist (phone_number, reason)
VALUES ('+441234000000', 'Abusive caller - 3 incidents logged');

-- Add a number blocked on a SPECIFIC DID only
INSERT INTO doppia_blacklist (phone_number, did, reason)
VALUES ('+441234000001', '441234567890', 'Competitor probing this campaign');

-- View the blacklist
SELECT phone_number, did, reason, date_added
FROM doppia_blacklist
ORDER BY date_added DESC;

-- Remove a number from the blacklist
DELETE FROM doppia_blacklist WHERE phone_number = '+441234000000';

When a blacklisted caller dials in, they hear the ss-noservice prompt and the call is hung up with cause code 21 (Call Rejected). You can customize the prompt or route them to a voicemail box instead.

Testing Procedures

Step 1: Verify Database Connectivity

# Test from the command line using the same credentials the AGI will use
# (read these from /etc/astguiclient.conf)
mysql -u <DBUSER> -p<DBPASS> <DBNAME> -e "SELECT 1 AS db_ok;"

Step 2: Test the AGI Script Manually

You can simulate an AGI call from the command line. The script reads its environment from STDIN in key: value format, followed by a blank line.

# Simulate an AGI invocation
echo -e "agi_uniqueid: test123\nagi_channel: SIP/test\n\n" | \
    /var/lib/asterisk/agi-bin/doppia_router.agi 604800 441234567890

Expected output (first call -- new caller):

VERBOSE "DOPPIA: CLI= EXTEN= DNID= TIME_WIN=604800 DOPPIA_DID=441234567890" 1
GET VARIABLE CALLERID(num)
GET VARIABLE EXTEN
GET VARIABLE DNID
VERBOSE "DOPPIA RESULT: did=441234567890 is_repeat=N is_blocked=N company=" 1
SET VARIABLE did 441234567890
SET VARIABLE is_repeat N
SET VARIABLE is_blocked N

Note: When run from the command line, GET VARIABLE commands will not return values (no Asterisk channel to query). The script handles this gracefully by using empty strings. In production, Asterisk provides the actual values.

Step 3: Verify Database Records

After a test call (or the manual test above), check that the record was created:

SELECT * FROM doppia_calls ORDER BY last_call_time DESC LIMIT 5;

Step 4: Test with a Real Call

1. Place an inbound call to one of your doppia-enabled DIDs.
2. Watch the Asterisk CLI in real time:

asterisk -rx "core set verbose 5"
# Then in another terminal, or use:
asterisk -rvvvvv

3. Look for the DOPPIA log lines:

DOPPIA ENTRY: CLI=441234000123 EXTEN=999888777
DOPPIA: CLI=441234000123 EXTEN=999888777 DNID=441234567890 TIME_WIN=604800 DOPPIA_DID=999888777
DOPPIA RESULT: did=999888777 is_repeat=N is_blocked=N company=Acme Plumbing London
DOPPIA TARGET INGROUP=normal

4. Hang up, wait a moment, and call again from the same number. The second call should show:

DOPPIA RESULT: did=999888777 is_repeat=Y is_blocked=N company=Acme Plumbing London
DOPPIA TARGET INGROUP=doppia

Step 5: Test Blacklist

-- Add your test number to the blacklist
INSERT INTO doppia_blacklist (phone_number, reason)
VALUES ('+441234000123', 'Testing blacklist');

Call again. You should see:

DOPPIA BLOCKED: CLI=+441234000123 DID=999888777

And hear the rejection prompt. Remove the test entry when done:

DELETE FROM doppia_blacklist WHERE phone_number = '+441234000123';

Step 6: Test Company Name Display

INSERT INTO did_company_map (did, company_name)
VALUES ('999888777', 'Test Company');

Call in and check the agent screen -- the caller ID name should show "Test Company" instead of the raw number.

Monitoring & Analytics

Once the system is running, you will want to track how it performs. Here are production-tested queries.

Repeat Caller Rate

The most important metric: what percentage of your calls are repeat callers?

-- Repeat rate over the last 30 days
-- A caller is "repeat" if their record existed before today
SELECT
    DATE(cl.call_date) AS call_date,
    COUNT(*) AS total_calls,
    SUM(CASE WHEN dc.last_call_time < DATE(cl.call_date) THEN 1 ELSE 0 END) AS repeat_calls,
    ROUND(
        SUM(CASE WHEN dc.last_call_time < DATE(cl.call_date) THEN 1 ELSE 0 END)
        / COUNT(*) * 100, 1
    ) AS repeat_pct
FROM vicidial_closer_log cl
LEFT JOIN doppia_calls dc
    ON cl.phone_number = dc.phone_number
WHERE cl.call_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(cl.call_date)
ORDER BY call_date;

Total Unique Callers Tracked

SELECT
    COUNT(*) AS total_tracked,
    COUNT(CASE WHEN last_call_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END)
        AS active_last_7d,
    COUNT(CASE WHEN last_call_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END)
        AS active_last_30d,
    MIN(last_call_time) AS oldest_record,
    MAX(last_call_time) AS newest_record
FROM doppia_calls;

Calls Per DID (Top DIDs by Volume)

SELECT
    dc.did,
    COALESCE(m.company_name, '(unmapped)') AS company,
    COUNT(*) AS unique_callers,
    COUNT(CASE WHEN dc.last_call_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END)
        AS callers_last_7d
FROM doppia_calls dc
LEFT JOIN did_company_map m ON dc.did = m.did
GROUP BY dc.did, m.company_name
ORDER BY unique_callers DESC
LIMIT 20;

Top Frequent Callers

Identify callers who call back the most (useful for VIP treatment or abuse detection).

Since the doppia_calls table stores only the last call time per phone+DID pair, to track call frequency you should cross-reference with ViciDial's vicidial_closer_log:

SELECT
    cl.phone_number,
    COUNT(*) AS call_count,
    COUNT(DISTINCT DATE(cl.call_date)) AS distinct_days,
    MIN(cl.call_date) AS first_call,
    MAX(cl.call_date) AS last_call
FROM vicidial_closer_log cl
WHERE cl.call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY cl.phone_number
HAVING call_count >= 3
ORDER BY call_count DESC
LIMIT 20;

Inbound Group Performance Comparison

Compare how repeat callers (doppia group) perform vs. first-time callers (normal group):

SELECT
    campaign_id AS ingroup,
    COUNT(*) AS total_calls,
    SUM(CASE WHEN status = 'SALE' THEN 1 ELSE 0 END) AS sales,
    ROUND(
        SUM(CASE WHEN status = 'SALE' THEN 1 ELSE 0 END) / COUNT(*) * 100, 1
    ) AS conversion_pct,
    ROUND(AVG(length_in_sec), 0) AS avg_duration_sec,
    ROUND(AVG(queue_seconds), 0) AS avg_queue_sec
FROM vicidial_closer_log
WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND campaign_id IN ('doppia', 'normal')
GROUP BY campaign_id;

Note: Adjust the status = 'SALE' condition to match your ViciDial disposition codes.

Table Size Monitoring

-- Check table growth
SELECT
    table_name,
    table_rows AS approx_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name IN ('doppia_calls', 'did_company_map', 'doppia_blacklist');

Automated Cleanup (Optional)

If you want to purge records older than a certain age to keep the table lean:

-- Remove records not updated in 90 days
-- Run via cron weekly or monthly
DELETE FROM doppia_calls
WHERE last_call_time < DATE_SUB(NOW(), INTERVAL 90 DAY);

Add to crontab:

# Purge stale doppia records every Sunday at 3 AM
0 3 * * 0 mysql -u <DBUSER> -p<DBPASS> <DBNAME> -e "DELETE FROM doppia_calls WHERE last_call_time < DATE_SUB(NOW(), INTERVAL 90 DAY);" >> /var/log/doppia_cleanup.log 2>&1

Tuning & Edge Cases

Adjusting the Time Window

The default 7-day (604800 seconds) window works well for most call centers, but you should tune it based on your business:

To change the window, modify the first argument in the dialplan:

; Example: 14-day window
same => n,AGI(doppia_router.agi,1209600,${EXTEN})

You can even use different windows for different DIDs by creating separate extensions:

; Sales DIDs: 7-day window
exten => 999888777,1,NoOp(DOPPIA SALES)
same => n,AGI(doppia_router.agi,604800,${EXTEN})
; ... rest of routing ...

; Support DIDs: 30-day window
exten => 999888778,1,NoOp(DOPPIA SUPPORT)
same => n,AGI(doppia_router.agi,2592000,${EXTEN})
; ... rest of routing ...

Edge Cases to Handle

Anonymous / Withheld Numbers

Callers with no CLI (anonymous, withheld, or "0000000000") will always appear as first-time callers because their phone number is empty or a placeholder. This is correct behavior -- you cannot track what you cannot identify.

If you want to explicitly handle this:

# Add after retrieving $CLI
if (!$CLI || $CLI =~ /^(0+|anonymous|unavailable|private|unknown)$/i) {
    agi_verbose("DOPPIA: Anonymous caller, treating as first-time", 1);
    $is_repeat = 'N';
    goto RESULT;
}

International Format Variations

The same caller might appear as +441234567890, 441234567890, 01234567890, or 1234567890 depending on your SIP trunk's CLI formatting. If your trunks are inconsistent, normalize the number in the AGI:

# Normalize CLI: strip leading +, 00, or national prefix
$CLI =~ s/^\+//;            # Remove leading +
$CLI =~ s/^00//;            # Remove international prefix 00
$CLI =~ s/^0(\d{9,})/$1/;  # Remove national leading 0 (adjust regex for your country)

Warning: Be careful with normalization. +44 and +39 prefixes are different countries. Only strip prefixes if all your traffic is from one country, or implement proper country-aware normalization.

Database Unavailability

The AGI script is designed to fail open: if the database is unreachable, it sets is_repeat=N and the call routes to the normal group. No calls are lost. The mysql_connect_timeout => 3 setting prevents the AGI from hanging if MySQL is slow.

High Concurrency

Under heavy load (hundreds of simultaneous inbound calls), the UPDATE-then-INSERT pattern handles concurrency well. The worst case is a duplicate key error on INSERT, which is caught by the error handler. The call still routes correctly.

If you process thousands of concurrent calls, consider:

• Using INSERT ... ON DUPLICATE KEY UPDATE instead of the two-step pattern
• Adding a connection pool (though for AGI scripts, a fresh connection per call is standard practice)

Caller ID Spoofing

This system relies on CLI (Caller Line Identification) which can be spoofed. This is acceptable for routing purposes because:

• A spoofed repeat caller gets routed to your experienced agents (minor misroute)
• A spoofed first-time caller gets routed to the general pool (correct default)
• Neither case causes a lost call or security breach

If spoofing is a concern for security reasons, implement additional verification at the agent level.

Performance Considerations

The system adds virtually no overhead to call processing. The random ring-back delay (2-5 seconds) masks any AGI execution time completely.

Production Checklist

Before going live, verify each item:

• Perl dependencies installed: DBI and DBD::mysql modules present
• AGI script deployed: /var/lib/asterisk/agi-bin/doppia_router.agi exists, is executable, owned by asterisk
• Script syntax valid: perl -c doppia_router.agi returns "syntax OK"
• Database tables created: doppia_calls, did_company_map, doppia_blacklist
• Dialplan entry added: Extension 999888777 in extensions.conf
• Dialplan reloaded: asterisk -rx "dialplan reload"
• Inbound groups created: doppia and normal groups in ViciDial
• Agents assigned: Experienced agents in doppia group, all agents in normal group
• DIDs configured: Target DIDs set to route to extension 999888777
• DID-to-company map populated: did_company_map has entries for your DIDs
• Fallback extension defined: Ring group or Dial() as last resort if AGI fails
• Test call placed: First call routes to normal, second call routes to doppia
• Blacklist tested: Blocked number hears rejection prompt
• Asterisk CLI monitored: DOPPIA verbose lines appear correctly
• Cleanup cron scheduled: Old records purged on a schedule

Backup Before Deploying

Always back up your dialplan before making changes:

DATE=$(date +%Y%m%d)
cp /etc/asterisk/extensions.conf /etc/asterisk/extensions.conf.bak.$DATE

Troubleshooting

AGI Script Not Executing

# Check permissions
ls -la /var/lib/asterisk/agi-bin/doppia_router.agi
# Should show: -rwxr-xr-x asterisk asterisk

# Check Asterisk can find it
asterisk -rx "agi show" | grep doppia

# Check for Perl errors
perl -c /var/lib/asterisk/agi-bin/doppia_router.agi

Database Connection Failing

# Test with the same credentials the AGI uses
grep VARDB /etc/astguiclient.conf
mysql -u <DBUSER> -p<DBPASS> <DBNAME> -e "SELECT 1;"

Calls Always Routing to "normal"

1. Check that the doppia_calls table has records for the test number:

   SELECT * FROM doppia_calls WHERE phone_number LIKE '%YOUR_TEST_NUMBER%';
   

2. Verify the time window has not expired (check last_call_time vs. current time).
3. Check CLI format matching: the phone_number stored might differ from the CLI received (e.g., with/without country code).

Channel Variables Not Set

If ${is_repeat} is empty in the dialplan, the AGI script may have exited early. Check the Asterisk CLI for DOPPIA ERROR messages and review /var/log/asterisk/messages for AGI-related errors.

Summary

This system gives you intelligent call routing with minimal complexity:

• One Perl script (~150 lines) handles all the logic.
• Three small MySQL tables store the data.
• One dialplan extension ties it all together.
• Two ViciDial inbound groups separate first-time and repeat callers.

The result is that your best agents handle your most valuable callers, your general pool handles new leads efficiently, and every agent sees the company name on their screen before they say a word. Deploy it on one DID first, watch the metrics for a week, and then roll it out to your full DID inventory.

Built for ViciDial + Asterisk production environments. Tested across 1,400+ DIDs processing tens of thousands of calls.