How Do You Route IVR Calls Using Snowflake Data?

The private key lives outside the web root with restricted file permissions. Passphrases come from environment variables, not hardcoded in scripts.

Querying Snowflake from PHP

Snowflake's REST API accepts SQL queries via POST. The JWT goes in the Authorization header, the query goes as JSON, results come back as JSON. Parameterized queries handle the ZIP input.

<?php
function querySnowflake($zip) {
    $jwt = generateJWT();
    $account = 'xy12345.us-east-1.snowflakecomputing.com';
    $warehouse = 'IVR_LOOKUP_WH';
    $database = 'CUSTOMER_DATA';
    $schema = 'PUBLIC';
    
    $sql = "SELECT region_code FROM zip_to_region WHERE zip = ?";
    
    $ch = curl_init("https://$account/api/v2/statements");
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
        "Authorization: Bearer $jwt",
        "Content-Type: application/json",
        "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT"
    ]);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode([
        'statement' => $sql,
        'bindings' => [['type' => 'TEXT', 'value' => $zip]],
        'warehouse' => $warehouse,
        'database' => $database,
        'schema' => $schema,
        'timeout' => 30
    ]));
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    
    $response = curl_exec($ch);
    $result = json_decode($response, true);
    curl_close($ch);
    
    if (isset($result['data'][0][0])) {
        return $result['data'][0][0];
    }
    return null;
}
?>

NetSapiens XML Response

NetSapiens expects XML responses describing what to do with the call. A <Forward> element routes to a SIP address. A <Gather> element collects DTMF input. A <Play> element plays audio to the caller.

<?php
header('Content-Type: application/xml');

$zip = $_GET['Digits'] ?? null;
$callerNumber = $_GET['NmsAni'] ?? null;

if ($zip && strlen($zip) === 5) {
    $region = querySnowflake($zip);
    
    if ($region) {
        echo "<?xml version='1.0'?>";
        echo "<Response>";
        echo "<Forward>{$region}@yourdomain.com</Forward>";
        echo "</Response>";
    } else {
        echo "<?xml version='1.0'?>";
        echo "<Response>";
        echo "<Play>https://ivr.yourdomain.com/audio/no-match.wav</Play>";
        echo "<Forward>[email protected]</Forward>";
        echo "</Response>";
    }
} else {
    echo "<?xml version='1.0'?>";
    echo "<Response>";
    echo "<Gather numDigits='5' timeout='10'>";
    echo "<Play>https://ivr.yourdomain.com/audio/enter-zip.wav</Play>";
    echo "</Gather>";
    echo "</Response>";
}
?>

When the caller enters digits, NetSapiens calls the script again with the Digits parameter populated, creating a conversational IVR flow.

Phone Number Lookups

If the caller doesn't enter a ZIP, the script can fall back to a phone number lookup. NetSapiens passes the caller ID automatically via NmsAni. Normalize it to 10 digits and query Snowflake for a match. New callers or unlisted numbers fall through to the ZIP prompt.

<?php
function normalizePhoneNumber($number) {
    $digits = preg_replace('/[^0-9]/', '', $number);
    return substr($digits, -10);
}

$callerNumber = normalizePhoneNumber($_GET['NmsAni']);
$region = querySnowflakeByPhone($callerNumber);

if ($region) {
    echo "<Forward>{$region}@yourdomain.com</Forward>";
} else {
    echo "<Gather numDigits='5'>";
    echo "<Play>https://ivr.yourdomain.com/audio/enter-zip.wav</Play>";
    echo "</Gather>";
}
?>

Failure Handling

Snowflake queries can time out. Network failures happen. The script needs fallback logic so calls don't drop into silence. A 5-10 second timeout on the query keeps things bounded. If anything fails, route to a default destination. Better to send every call to general support than to leave someone listening to dead air.

<?php
try {
    $region = querySnowflake($zip);
    if ($region) {
        echo "<Forward>{$region}@yourdomain.com</Forward>";
    } else {
        echo "<Forward>[email protected]</Forward>";
    }
} catch (Exception $e) {
    error_log("Snowflake query failed: " . $e->getMessage());
    echo "<Play>https://ivr.yourdomain.com/audio/technical-difficulty.wav</Play>";
    echo "<Forward>[email protected]</Forward>";
}
?>

Caching for Performance

ZIP-to-region mappings don't change often. Caching results in Redis with a 24-hour TTL means most lookups resolve in milliseconds instead of round-tripping to Snowflake. Check cache first, query on miss. IVR routing needs to respond in under a second, and cached lookups easily hit that.

<?php
function getRegionCached($zip) {
    $redis = new Redis();
    $redis->connect('127.0.0.1', 6379);
    
    $cacheKey = "zip_region:$zip";
    $cached = $redis->get($cacheKey);
    
    if ($cached !== false) {
        return $cached;
    }
    
    $region = querySnowflake($zip);
    if ($region) {
        $redis->setex($cacheKey, 86400, $region);
    }
    return $region;
}
?>

NetSapiens Configuration

In NetSapiens, a dedicated user (e.g., ivrlookup) gets its Call Forwarding pointed at the PHP script URL. Call Routing Rules direct incoming calls to that user. When a call arrives, NetSapiens invokes the script automatically.

What makes this useful: routing logic changes happen in the database. Marketing wants to shift coverage areas? Update the table. No IVR reprogramming needed.

Security

The PHP script is publicly accessible since NetSapiens calls it from the internet. Source IP validation ensures only requests from known NetSapiens servers get through. Caller data stays out of plain-text logs — hashed or truncated identifiers only.

<?php
$allowedIPs = ['1.2.3.4', '5.6.7.8'];
$clientIP = $_SERVER['REMOTE_ADDR'];

if (!in_array($clientIP, $allowedIPs)) {
    http_response_code(403);
    die('Unauthorized');
}
?>

When to Use This Pattern

Dynamic database lookups work well when:

• Routing logic changes frequently — regional coverage, account assignments, business rules
• Structured caller data exists — ZIP codes, account numbers, phone number databases
• Static menus can't handle the complexity — too many branches or conditional logic
• Cached response time is acceptable — sub-500ms with Redis

For simple routing like after-hours calls to voicemail, built-in time conditions work fine. Dynamic lookups add complexity. Use them when static configuration can't solve the problem.