-- Check the postal code table
SELECT postal_code, state, GMT_offset, DST, DST_range, country
FROM vicidial_postal_codes
WHERE postal_code = '10001' -- Manhattan, NY
LIMIT 1;
-- Check the postal code table
SELECT postal_code, state, GMT_offset, DST, DST_range, country
FROM vicidial_postal_codes
WHERE postal_code = '10001' -- Manhattan, NY
LIMIT 1;
-- Check the postal code table
SELECT postal_code, state, GMT_offset, DST, DST_range, country
FROM vicidial_postal_codes
WHERE postal_code = '10001' -- Manhattan, NY
LIMIT 1;
postal_code: 10001
state: NY
GMT_offset: -5.00
DST: Y
DST_range: SSM-FSN
country: USA
postal_code: 10001
state: NY
GMT_offset: -5.00
DST: Y
DST_range: SSM-FSN
country: USA
postal_code: 10001
state: NY
GMT_offset: -5.00
DST: Y
DST_range: SSM-FSN
country: USA
-- Check a lead's current timezone data
SELECT lead_id, phone_number, postal_code, state, gmt_offset_now, called_since_last_reset
FROM vicidial_list
WHERE lead_id = 12345;
-- Check a lead's current timezone data
SELECT lead_id, phone_number, postal_code, state, gmt_offset_now, called_since_last_reset
FROM vicidial_list
WHERE lead_id = 12345;
-- Check a lead's current timezone data
SELECT lead_id, phone_number, postal_code, state, gmt_offset_now, called_since_last_reset
FROM vicidial_list
WHERE lead_id = 12345;
-- Find leads with missing or suspicious timezone data
SELECT COUNT(*) AS total, SUM(CASE WHEN postal_code = '' OR postal_code IS NULL THEN 1 ELSE 0 END) AS no_zip, SUM(CASE WHEN gmt_offset_now IS NULL OR gmt_offset_now = 0 THEN 1 ELSE 0 END) AS no_offset, SUM(CASE WHEN state = '' OR state IS NULL THEN 1 ELSE 0 END) AS no_state
FROM vicidial_list
WHERE list_id IN (SELECT list_id FROM vicidial_lists WHERE active = 'Y');
-- Find leads with missing or suspicious timezone data
SELECT COUNT(*) AS total, SUM(CASE WHEN postal_code = '' OR postal_code IS NULL THEN 1 ELSE 0 END) AS no_zip, SUM(CASE WHEN gmt_offset_now IS NULL OR gmt_offset_now = 0 THEN 1 ELSE 0 END) AS no_offset, SUM(CASE WHEN state = '' OR state IS NULL THEN 1 ELSE 0 END) AS no_state
FROM vicidial_list
WHERE list_id IN (SELECT list_id FROM vicidial_lists WHERE active = 'Y');
-- Find leads with missing or suspicious timezone data
SELECT COUNT(*) AS total, SUM(CASE WHEN postal_code = '' OR postal_code IS NULL THEN 1 ELSE 0 END) AS no_zip, SUM(CASE WHEN gmt_offset_now IS NULL OR gmt_offset_now = 0 THEN 1 ELSE 0 END) AS no_offset, SUM(CASE WHEN state = '' OR state IS NULL THEN 1 ELSE 0 END) AS no_state
FROM vicidial_list
WHERE list_id IN (SELECT list_id FROM vicidial_lists WHERE active = 'Y');
Call Time ID: TCPA_STANDARD
Call Time Name: TCPA Compliant 8am-9pm
Default Start: 800 (8:00 AM)
Default Stop: 2100 (9:00 PM) Sunday Start: 800
Sunday Stop: 2100 Monday Start: 800
Monday Stop: 2100 Tuesday Start: 800
Tuesday Stop: 2100 Wednesday Start: 800
Wednesday Stop: 2100 Thursday Start: 800
Thursday Stop: 2100 Friday Start: 800
Friday Stop: 2100 Saturday Start: 800
Saturday Stop: 2100
Call Time ID: TCPA_STANDARD
Call Time Name: TCPA Compliant 8am-9pm
Default Start: 800 (8:00 AM)
Default Stop: 2100 (9:00 PM) Sunday Start: 800
Sunday Stop: 2100 Monday Start: 800
Monday Stop: 2100 Tuesday Start: 800
Tuesday Stop: 2100 Wednesday Start: 800
Wednesday Stop: 2100 Thursday Start: 800
Thursday Stop: 2100 Friday Start: 800
Friday Stop: 2100 Saturday Start: 800
Saturday Stop: 2100
Call Time ID: TCPA_STANDARD
Call Time Name: TCPA Compliant 8am-9pm
Default Start: 800 (8:00 AM)
Default Stop: 2100 (9:00 PM) Sunday Start: 800
Sunday Stop: 2100 Monday Start: 800
Monday Stop: 2100 Tuesday Start: 800
Tuesday Stop: 2100 Wednesday Start: 800
Wednesday Stop: 2100 Thursday Start: 800
Thursday Stop: 2100 Friday Start: 800
Friday Stop: 2100 Saturday Start: 800
Saturday Stop: 2100
Sunday Start: 0
Sunday Stop: 0 (No dialing on Sunday)
Sunday Start: 0
Sunday Stop: 0 (No dialing on Sunday)
Sunday Start: 0
Sunday Stop: 0 (No dialing on Sunday)
Local Call Time: TCPA_STANDARD
Local Call Time: TCPA_STANDARD
Local Call Time: TCPA_STANDARD
State Call Time ID: TCPA_STATES
State Call Time Name: State-Specific TCPA Overrides Oklahoma: Start: 800 Stop: 2000 (8:00 PM, not 9:00 PM) Washington: Start: 800 Stop: 2000 (8:00 PM) Texas (Sunday): Sunday Start: 1200 (Noon) Sunday Stop: 2100 (9:00 PM)
State Call Time ID: TCPA_STATES
State Call Time Name: State-Specific TCPA Overrides Oklahoma: Start: 800 Stop: 2000 (8:00 PM, not 9:00 PM) Washington: Start: 800 Stop: 2000 (8:00 PM) Texas (Sunday): Sunday Start: 1200 (Noon) Sunday Stop: 2100 (9:00 PM)
State Call Time ID: TCPA_STATES
State Call Time Name: State-Specific TCPA Overrides Oklahoma: Start: 800 Stop: 2000 (8:00 PM, not 9:00 PM) Washington: Start: 800 Stop: 2000 (8:00 PM) Texas (Sunday): Sunday Start: 1200 (Noon) Sunday Stop: 2100 (9:00 PM)
Campaigns > [Campaign] > Detail: State Call Time: TCPA_STATES
Campaigns > [Campaign] > Detail: State Call Time: TCPA_STATES
Campaigns > [Campaign] > Detail: State Call Time: TCPA_STATES
-- Simulate timezone filtering for your active campaign
-- This shows how many leads are currently dialable by timezone
SELECT gmt_offset_now, COUNT(*) AS leads, TIME_FORMAT( ADDTIME(NOW(), SEC_TO_TIME(gmt_offset_now * 3600)), '%H:%i' ) AS local_time_now, CASE WHEN TIME_FORMAT(ADDTIME(NOW(), SEC_TO_TIME(gmt_offset_now * 3600)), '%H%i') BETWEEN '0800' AND '2100' THEN 'DIALABLE' ELSE 'BLOCKED' END AS status
FROM vicidial_list
WHERE list_id IN ( SELECT list_id FROM vicidial_lists WHERE campaign_id = 'YOUR_CAMPAIGN' AND active = 'Y'
)
AND status IN ('NEW', 'CALLBK', 'A', 'B', 'NA')
GROUP BY gmt_offset_now
ORDER BY gmt_offset_now;
-- Simulate timezone filtering for your active campaign
-- This shows how many leads are currently dialable by timezone
SELECT gmt_offset_now, COUNT(*) AS leads, TIME_FORMAT( ADDTIME(NOW(), SEC_TO_TIME(gmt_offset_now * 3600)), '%H:%i' ) AS local_time_now, CASE WHEN TIME_FORMAT(ADDTIME(NOW(), SEC_TO_TIME(gmt_offset_now * 3600)), '%H%i') BETWEEN '0800' AND '2100' THEN 'DIALABLE' ELSE 'BLOCKED' END AS status
FROM vicidial_list
WHERE list_id IN ( SELECT list_id FROM vicidial_lists WHERE campaign_id = 'YOUR_CAMPAIGN' AND active = 'Y'
)
AND status IN ('NEW', 'CALLBK', 'A', 'B', 'NA')
GROUP BY gmt_offset_now
ORDER BY gmt_offset_now;
-- Simulate timezone filtering for your active campaign
-- This shows how many leads are currently dialable by timezone
SELECT gmt_offset_now, COUNT(*) AS leads, TIME_FORMAT( ADDTIME(NOW(), SEC_TO_TIME(gmt_offset_now * 3600)), '%H:%i' ) AS local_time_now, CASE WHEN TIME_FORMAT(ADDTIME(NOW(), SEC_TO_TIME(gmt_offset_now * 3600)), '%H%i') BETWEEN '0800' AND '2100' THEN 'DIALABLE' ELSE 'BLOCKED' END AS status
FROM vicidial_list
WHERE list_id IN ( SELECT list_id FROM vicidial_lists WHERE campaign_id = 'YOUR_CAMPAIGN' AND active = 'Y'
)
AND status IN ('NEW', 'CALLBK', 'A', 'B', 'NA')
GROUP BY gmt_offset_now
ORDER BY gmt_offset_now;
# Instead of 8:00 AM - 9:00 PM, use:
Call Time: 8:15 AM - 8:45 PM # This gives a 15-minute buffer on each end
# Cost: ~30 minutes of reduced dialing per day
# Benefit: protection during DST transitions and clock skew
# Instead of 8:00 AM - 9:00 PM, use:
Call Time: 8:15 AM - 8:45 PM # This gives a 15-minute buffer on each end
# Cost: ~30 minutes of reduced dialing per day
# Benefit: protection during DST transitions and clock skew
# Instead of 8:00 AM - 9:00 PM, use:
Call Time: 8:15 AM - 8:45 PM # This gives a 15-minute buffer on each end
# Cost: ~30 minutes of reduced dialing per day
# Benefit: protection during DST transitions and clock skew
#!/bin/bash
# /opt/vicistack/dst_check.sh
# Run before DST transitions (March and November) echo "=== DST Transition Verification ==="
echo "Current server time: $(date)"
echo "Server timezone: $(cat /etc/timezone 2>/dev/null || timedatectl | grep 'Time zone')" echo ""
echo "=== VICIdial Postal Code DST Flags ==="
mysql asterisk -e "
SELECT DST, COUNT(*) AS zip_codes
FROM vicidial_postal_codes
WHERE country = 'USA'
GROUP BY DST;
" echo ""
echo "=== Sample DST=N States (should be AZ, HI) ==="
mysql asterisk -e "
SELECT DISTINCT state
FROM vicidial_postal_codes
WHERE DST = 'N' AND country = 'USA';
" echo ""
echo "=== Leads with DST=Y that should be N (Arizona check) ==="
mysql asterisk -e "
SELECT vl.lead_id, vl.phone_number, vl.postal_code, vl.state, vl.gmt_offset_now, vpc.DST
FROM vicidial_list vl
JOIN vicidial_postal_codes vpc ON vl.postal_code = vpc.postal_code
WHERE vl.state = 'AZ' AND vpc.DST = 'Y'
LIMIT 10;
" echo ""
echo "=== Current Active Campaigns Call Times ==="
mysql asterisk -e "
SELECT campaign_id, local_call_time
FROM vicidial_campaigns
WHERE active = 'Y';
"
#!/bin/bash
# /opt/vicistack/dst_check.sh
# Run before DST transitions (March and November) echo "=== DST Transition Verification ==="
echo "Current server time: $(date)"
echo "Server timezone: $(cat /etc/timezone 2>/dev/null || timedatectl | grep 'Time zone')" echo ""
echo "=== VICIdial Postal Code DST Flags ==="
mysql asterisk -e "
SELECT DST, COUNT(*) AS zip_codes
FROM vicidial_postal_codes
WHERE country = 'USA'
GROUP BY DST;
" echo ""
echo "=== Sample DST=N States (should be AZ, HI) ==="
mysql asterisk -e "
SELECT DISTINCT state
FROM vicidial_postal_codes
WHERE DST = 'N' AND country = 'USA';
" echo ""
echo "=== Leads with DST=Y that should be N (Arizona check) ==="
mysql asterisk -e "
SELECT vl.lead_id, vl.phone_number, vl.postal_code, vl.state, vl.gmt_offset_now, vpc.DST
FROM vicidial_list vl
JOIN vicidial_postal_codes vpc ON vl.postal_code = vpc.postal_code
WHERE vl.state = 'AZ' AND vpc.DST = 'Y'
LIMIT 10;
" echo ""
echo "=== Current Active Campaigns Call Times ==="
mysql asterisk -e "
SELECT campaign_id, local_call_time
FROM vicidial_campaigns
WHERE active = 'Y';
"
#!/bin/bash
# /opt/vicistack/dst_check.sh
# Run before DST transitions (March and November) echo "=== DST Transition Verification ==="
echo "Current server time: $(date)"
echo "Server timezone: $(cat /etc/timezone 2>/dev/null || timedatectl | grep 'Time zone')" echo ""
echo "=== VICIdial Postal Code DST Flags ==="
mysql asterisk -e "
SELECT DST, COUNT(*) AS zip_codes
FROM vicidial_postal_codes
WHERE country = 'USA'
GROUP BY DST;
" echo ""
echo "=== Sample DST=N States (should be AZ, HI) ==="
mysql asterisk -e "
SELECT DISTINCT state
FROM vicidial_postal_codes
WHERE DST = 'N' AND country = 'USA';
" echo ""
echo "=== Leads with DST=Y that should be N (Arizona check) ==="
mysql asterisk -e "
SELECT vl.lead_id, vl.phone_number, vl.postal_code, vl.state, vl.gmt_offset_now, vpc.DST
FROM vicidial_list vl
JOIN vicidial_postal_codes vpc ON vl.postal_code = vpc.postal_code
WHERE vl.state = 'AZ' AND vpc.DST = 'Y'
LIMIT 10;
" echo ""
echo "=== Current Active Campaigns Call Times ==="
mysql asterisk -e "
SELECT campaign_id, local_call_time
FROM vicidial_campaigns
WHERE active = 'Y';
"
# Restart the hopper script (it recalculates on startup)
# On the VICIdial server:
screen -r� AST_VDhopper
# Or restart the VICIdial services:
/usr/share/astguiclient/ADMIN_keepalive_ALL.pl --cu3way
# Restart the hopper script (it recalculates on startup)
# On the VICIdial server:
screen -r� AST_VDhopper
# Or restart the VICIdial services:
/usr/share/astguiclient/ADMIN_keepalive_ALL.pl --cu3way
# Restart the hopper script (it recalculates on startup)
# On the VICIdial server:
screen -r� AST_VDhopper
# Or restart the VICIdial services:
/usr/share/astguiclient/ADMIN_keepalive_ALL.pl --cu3way
-- Recalculate gmt_offset_now for all leads in list 1001
UPDATE vicidial_list vl
JOIN vicidial_postal_codes vpc ON vl.postal_code = vpc.postal_code
SET vl.gmt_offset_now = CASE WHEN vpc.DST = 'Y' AND CURDATE() BETWEEN -- Approximate DST start: second Sunday of March DATE_ADD(MAKEDATE(YEAR(CURDATE()), 1), INTERVAL (13 - DAYOFWEEK(MAKEDATE(YEAR(CURDATE()), 1)) + 7 * 1) DAY + INTERVAL 2 MONTH) AND -- Approximate DST end: first Sunday of November DATE_ADD(MAKEDATE(YEAR(CURDATE()), 1), INTERVAL (7 - DAYOFWEEK(MAKEDATE(YEAR(CURDATE()), 1)) + 7 * 0) DAY + INTERVAL 10 MONTH) THEN vpc.GMT_offset + 1 ELSE vpc.GMT_offset
END
WHERE vl.list_id = 1001;
-- Recalculate gmt_offset_now for all leads in list 1001
UPDATE vicidial_list vl
JOIN vicidial_postal_codes vpc ON vl.postal_code = vpc.postal_code
SET vl.gmt_offset_now = CASE WHEN vpc.DST = 'Y' AND CURDATE() BETWEEN -- Approximate DST start: second Sunday of March DATE_ADD(MAKEDATE(YEAR(CURDATE()), 1), INTERVAL (13 - DAYOFWEEK(MAKEDATE(YEAR(CURDATE()), 1)) + 7 * 1) DAY + INTERVAL 2 MONTH) AND -- Approximate DST end: first Sunday of November DATE_ADD(MAKEDATE(YEAR(CURDATE()), 1), INTERVAL (7 - DAYOFWEEK(MAKEDATE(YEAR(CURDATE()), 1)) + 7 * 0) DAY + INTERVAL 10 MONTH) THEN vpc.GMT_offset + 1 ELSE vpc.GMT_offset
END
WHERE vl.list_id = 1001;
-- Recalculate gmt_offset_now for all leads in list 1001
UPDATE vicidial_list vl
JOIN vicidial_postal_codes vpc ON vl.postal_code = vpc.postal_code
SET vl.gmt_offset_now = CASE WHEN vpc.DST = 'Y' AND CURDATE() BETWEEN -- Approximate DST start: second Sunday of March DATE_ADD(MAKEDATE(YEAR(CURDATE()), 1), INTERVAL (13 - DAYOFWEEK(MAKEDATE(YEAR(CURDATE()), 1)) + 7 * 1) DAY + INTERVAL 2 MONTH) AND -- Approximate DST end: first Sunday of November DATE_ADD(MAKEDATE(YEAR(CURDATE()), 1), INTERVAL (7 - DAYOFWEEK(MAKEDATE(YEAR(CURDATE()), 1)) + 7 * 0) DAY + INTERVAL 10 MONTH) THEN vpc.GMT_offset + 1 ELSE vpc.GMT_offset
END
WHERE vl.list_id = 1001;
Oklahoma: Start: 800 Stop: 2000
Oklahoma: Start: 800 Stop: 2000
Oklahoma: Start: 800 Stop: 2000
-- Verify Indiana timezone assignments
SELECT postal_code, state, GMT_offset, DST
FROM vicidial_postal_codes
WHERE state = 'IN'
AND GMT_offset = -6.00 -- Central time
ORDER BY postal_code;
-- Verify Indiana timezone assignments
SELECT postal_code, state, GMT_offset, DST
FROM vicidial_postal_codes
WHERE state = 'IN'
AND GMT_offset = -6.00 -- Central time
ORDER BY postal_code;
-- Verify Indiana timezone assignments
SELECT postal_code, state, GMT_offset, DST
FROM vicidial_postal_codes
WHERE state = 'IN'
AND GMT_offset = -6.00 -- Central time
ORDER BY postal_code;
-- Check that Hawaiian leads are properly classified
SELECT postal_code, state, GMT_offset, DST
FROM vicidial_postal_codes
WHERE state = 'HI'
LIMIT 5; -- Should show GMT_offset = -10.00, DST = N
-- Check that Hawaiian leads are properly classified
SELECT postal_code, state, GMT_offset, DST
FROM vicidial_postal_codes
WHERE state = 'HI'
LIMIT 5; -- Should show GMT_offset = -10.00, DST = N
-- Check that Hawaiian leads are properly classified
SELECT postal_code, state, GMT_offset, DST
FROM vicidial_postal_codes
WHERE state = 'HI'
LIMIT 5; -- Should show GMT_offset = -10.00, DST = N
#!/bin/bash
# /opt/vicistack/compliance_audit.sh
# Run daily via cron. Archive output for legal review. AUDIT_DIR="/var/log/vicistack/compliance"
mkdir -p ${AUDIT_DIR}
AUDIT_FILE="${AUDIT_DIR}/audit_$(date +%Y%m%d_%H%M).txt" {
echo "=========================================="
echo "TCPA Compliance Audit - $(date)"
echo "==========================================" echo ""
echo "--- Server Time Verification ---"
echo "System time: $(date)"
echo "NTP sync status:"
chronyc tracking 2>/dev/null || ntpstat 2>/dev/null || echo "NTP status unavailable" echo ""
echo "--- Call Time Definitions ---"
mysql asterisk -e "
SELECT call_time_id, call_time_name, ct_default_start, ct_default_stop, ct_sunday_start, ct_sunday_stop
FROM vicidial_call_times;" echo ""
echo "--- State Call Time Overrides ---"
mysql asterisk -e "
SELECT state_call_time_id, state_call_time_state, sct_default_start, sct_default_stop
FROM vicidial_state_call_times
WHERE state_call_time_state != '';" echo ""
echo "--- Active Campaign Call Time Assignments ---"
mysql asterisk -e "
SELECT campaign_id, campaign_name, local_call_time
FROM vicidial_campaigns
WHERE active = 'Y';" echo ""
echo "--- Calls Made Outside 8am-9pm Local Time (Past 24h) ---"
echo "(These should be zero. Any results indicate a compliance issue.)"
mysql asterisk -e "
SELECT vl.uniqueid, vl.call_date, vl.phone_number, vl.campaign_id, vl.user, vlist.state, vlist.postal_code, vlist.gmt_offset_now, TIME_FORMAT( ADDTIME(vl.call_date, SEC_TO_TIME(vlist.gmt_offset_now * 3600)), '%H:%i' ) AS local_call_time
FROM vicidial_log vl
JOIN vicidial_list vlist ON vl.lead_id = vlist.lead_id
WHERE vl.call_date >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
AND ( TIME_FORMAT(ADDTIME(vl.call_date, SEC_TO_TIME(vlist.gmt_offset_now * 3600)), '%H%i') < '0800' OR TIME_FORMAT(ADDTIME(vl.call_date, SEC_TO_TIME(vlist.gmt_offset_now * 3600)), '%H%i') > '2100'
)
LIMIT 50;" echo ""
echo "--- Leads with Missing Timezone Data ---"
mysql asterisk -e "
SELECT list_id, COUNT(*) AS leads_no_tz
FROM vicidial_list
WHERE (postal_code = '' OR postal_code IS NULL)
AND status IN ('NEW', 'CALLBK', 'A', 'B', 'NA')
AND list_id IN (SELECT list_id FROM vicidial_lists WHERE active = 'Y')
GROUP BY list_id;" echo ""
echo "=========================================="
echo "Audit complete."
echo "==========================================" } > ${AUDIT_FILE} 2>&1 # Compress audits older than 30 days
find ${AUDIT_DIR} -name "audit_*.txt" -mtime +30 -exec gzip {} \; # Alert if any out-of-window calls were found
OUT_OF_WINDOW=$(grep -c "rows in set" ${AUDIT_FILE} 2>/dev/null)
if echo "${AUDIT_FILE}" | grep -q "Calls Made Outside"; then # More precise check would parse the SQL output echo "Compliance audit saved to ${AUDIT_FILE}"
fi
#!/bin/bash
# /opt/vicistack/compliance_audit.sh
# Run daily via cron. Archive output for legal review. AUDIT_DIR="/var/log/vicistack/compliance"
mkdir -p ${AUDIT_DIR}
AUDIT_FILE="${AUDIT_DIR}/audit_$(date +%Y%m%d_%H%M).txt" {
echo "=========================================="
echo "TCPA Compliance Audit - $(date)"
echo "==========================================" echo ""
echo "--- Server Time Verification ---"
echo "System time: $(date)"
echo "NTP sync status:"
chronyc tracking 2>/dev/null || ntpstat 2>/dev/null || echo "NTP status unavailable" echo ""
echo "--- Call Time Definitions ---"
mysql asterisk -e "
SELECT call_time_id, call_time_name, ct_default_start, ct_default_stop, ct_sunday_start, ct_sunday_stop
FROM vicidial_call_times;" echo ""
echo "--- State Call Time Overrides ---"
mysql asterisk -e "
SELECT state_call_time_id, state_call_time_state, sct_default_start, sct_default_stop
FROM vicidial_state_call_times
WHERE state_call_time_state != '';" echo ""
echo "--- Active Campaign Call Time Assignments ---"
mysql asterisk -e "
SELECT campaign_id, campaign_name, local_call_time
FROM vicidial_campaigns
WHERE active = 'Y';" echo ""
echo "--- Calls Made Outside 8am-9pm Local Time (Past 24h) ---"
echo "(These should be zero. Any results indicate a compliance issue.)"
mysql asterisk -e "
SELECT vl.uniqueid, vl.call_date, vl.phone_number, vl.campaign_id, vl.user, vlist.state, vlist.postal_code, vlist.gmt_offset_now, TIME_FORMAT( ADDTIME(vl.call_date, SEC_TO_TIME(vlist.gmt_offset_now * 3600)), '%H:%i' ) AS local_call_time
FROM vicidial_log vl
JOIN vicidial_list vlist ON vl.lead_id = vlist.lead_id
WHERE vl.call_date >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
AND ( TIME_FORMAT(ADDTIME(vl.call_date, SEC_TO_TIME(vlist.gmt_offset_now * 3600)), '%H%i') < '0800' OR TIME_FORMAT(ADDTIME(vl.call_date, SEC_TO_TIME(vlist.gmt_offset_now * 3600)), '%H%i') > '2100'
)
LIMIT 50;" echo ""
echo "--- Leads with Missing Timezone Data ---"
mysql asterisk -e "
SELECT list_id, COUNT(*) AS leads_no_tz
FROM vicidial_list
WHERE (postal_code = '' OR postal_code IS NULL)
AND status IN ('NEW', 'CALLBK', 'A', 'B', 'NA')
AND list_id IN (SELECT list_id FROM vicidial_lists WHERE active = 'Y')
GROUP BY list_id;" echo ""
echo "=========================================="
echo "Audit complete."
echo "==========================================" } > ${AUDIT_FILE} 2>&1 # Compress audits older than 30 days
find ${AUDIT_DIR} -name "audit_*.txt" -mtime +30 -exec gzip {} \; # Alert if any out-of-window calls were found
OUT_OF_WINDOW=$(grep -c "rows in set" ${AUDIT_FILE} 2>/dev/null)
if echo "${AUDIT_FILE}" | grep -q "Calls Made Outside"; then # More precise check would parse the SQL output echo "Compliance audit saved to ${AUDIT_FILE}"
fi
#!/bin/bash
# /opt/vicistack/compliance_audit.sh
# Run daily via cron. Archive output for legal review. AUDIT_DIR="/var/log/vicistack/compliance"
mkdir -p ${AUDIT_DIR}
AUDIT_FILE="${AUDIT_DIR}/audit_$(date +%Y%m%d_%H%M).txt" {
echo "=========================================="
echo "TCPA Compliance Audit - $(date)"
echo "==========================================" echo ""
echo "--- Server Time Verification ---"
echo "System time: $(date)"
echo "NTP sync status:"
chronyc tracking 2>/dev/null || ntpstat 2>/dev/null || echo "NTP status unavailable" echo ""
echo "--- Call Time Definitions ---"
mysql asterisk -e "
SELECT call_time_id, call_time_name, ct_default_start, ct_default_stop, ct_sunday_start, ct_sunday_stop
FROM vicidial_call_times;" echo ""
echo "--- State Call Time Overrides ---"
mysql asterisk -e "
SELECT state_call_time_id, state_call_time_state, sct_default_start, sct_default_stop
FROM vicidial_state_call_times
WHERE state_call_time_state != '';" echo ""
echo "--- Active Campaign Call Time Assignments ---"
mysql asterisk -e "
SELECT campaign_id, campaign_name, local_call_time
FROM vicidial_campaigns
WHERE active = 'Y';" echo ""
echo "--- Calls Made Outside 8am-9pm Local Time (Past 24h) ---"
echo "(These should be zero. Any results indicate a compliance issue.)"
mysql asterisk -e "
SELECT vl.uniqueid, vl.call_date, vl.phone_number, vl.campaign_id, vl.user, vlist.state, vlist.postal_code, vlist.gmt_offset_now, TIME_FORMAT( ADDTIME(vl.call_date, SEC_TO_TIME(vlist.gmt_offset_now * 3600)), '%H:%i' ) AS local_call_time
FROM vicidial_log vl
JOIN vicidial_list vlist ON vl.lead_id = vlist.lead_id
WHERE vl.call_date >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
AND ( TIME_FORMAT(ADDTIME(vl.call_date, SEC_TO_TIME(vlist.gmt_offset_now * 3600)), '%H%i') < '0800' OR TIME_FORMAT(ADDTIME(vl.call_date, SEC_TO_TIME(vlist.gmt_offset_now * 3600)), '%H%i') > '2100'
)
LIMIT 50;" echo ""
echo "--- Leads with Missing Timezone Data ---"
mysql asterisk -e "
SELECT list_id, COUNT(*) AS leads_no_tz
FROM vicidial_list
WHERE (postal_code = '' OR postal_code IS NULL)
AND status IN ('NEW', 'CALLBK', 'A', 'B', 'NA')
AND list_id IN (SELECT list_id FROM vicidial_lists WHERE active = 'Y')
GROUP BY list_id;" echo ""
echo "=========================================="
echo "Audit complete."
echo "==========================================" } > ${AUDIT_FILE} 2>&1 # Compress audits older than 30 days
find ${AUDIT_DIR} -name "audit_*.txt" -mtime +30 -exec gzip {} \; # Alert if any out-of-window calls were found
OUT_OF_WINDOW=$(grep -c "rows in set" ${AUDIT_FILE} 2>/dev/null)
if echo "${AUDIT_FILE}" | grep -q "Calls Made Outside"; then # More precise check would parse the SQL output echo "Compliance audit saved to ${AUDIT_FILE}"
fi
0 22 * * * /opt/vicistack/compliance_audit.sh
0 22 * * * /opt/vicistack/compliance_audit.sh
0 22 * * * /opt/vicistack/compliance_audit.sh
# Monthly archive
tar -czf /backup/compliance/compliance_$(date +%Y%m).tar.gz \ /var/log/vicistack/compliance/audit_$(date +%Y%m)*.txt.gz
# Monthly archive
tar -czf /backup/compliance/compliance_$(date +%Y%m).tar.gz \ /var/log/vicistack/compliance/audit_$(date +%Y%m)*.txt.gz
# Monthly archive
tar -czf /backup/compliance/compliance_$(date +%Y%m).tar.gz \ /var/log/vicistack/compliance/audit_$(date +%Y%m)*.txt.gz
# Verify NTP sync
chronyc tracking
# Should show: "Leap status: Normal" and "System time: 0.000... seconds" # Or with ntpd
ntpq -p
# Should show at least one server with * (active sync)
# Verify NTP sync
chronyc tracking
# Should show: "Leap status: Normal" and "System time: 0.000... seconds" # Or with ntpd
ntpq -p
# Should show at least one server with * (active sync)
# Verify NTP sync
chronyc tracking
# Should show: "Leap status: Normal" and "System time: 0.000... seconds" # Or with ntpd
ntpq -p
# Should show at least one server with * (active sync) - Permitted calling hours: 8:00 AM to 9:00 PM in the called party's local time
- This applies to: Telemarketing calls, prerecorded messages, and autodial calls
- The critical detail: The time zone is the recipient's time zone, not the caller's - Most of Indiana: Eastern time (observes DST)
- Northwest Indiana (near Chicago): Central time (observes DST)
- Southwest Indiana (near Evansville): Central time (observes DST) - Standard time: Arizona is MST year-round (same as Mountain Standard)
- During DST months: Arizona is the same as Pacific Daylight Time (PDT), not MDT
- Navajo Nation: Observes DST (MDT during summer) - GMT_offset: The standard (non-DST) offset from GMT. New York is GMT-5 (Eastern Standard Time).
- DST: Whether this postal code observes daylight saving time (Y/N).
- DST_range: When DST is in effect. SSM-FSN means Second Sunday of March to First Sunday of November. - Read the lead's postal code from vicidial_list
- Look up GMT_offset and DST flag from vicidial_postal_codes
- Calculate the lead's current local time: If DST=Y and current date is within DST_range: local_time = server_GMT + GMT_offset + 1
If DST=N or outside DST_range: local_time = server_GMT + GMT_offset
- If DST=Y and current date is within DST_range: local_time = server_GMT + GMT_offset + 1
- If DST=N or outside DST_range: local_time = server_GMT + GMT_offset
- Compare local_time against the campaign's call_time definition
- Only add the lead to the hopper if local_time is within the permitted window - If DST=Y and current date is within DST_range: local_time = server_GMT + GMT_offset + 1
- If DST=N or outside DST_range: local_time = server_GMT + GMT_offset - Call time configuration: Screenshot or export your call_time and state_call_time settings
- Timezone data accuracy: Periodic audit of vicidial_postal_codes table
- Hopper logs: VICIdial logs which leads were added to the hopper and when
- Call detail records: vicidial_log records the exact time of every dial attempt
- System time accuracy: NTP sync verification - Never rely solely on VICIdial defaults. Configure call_time and state_call_time explicitly for every active campaign.
- Add a time buffer. Use 8:05 AM - 8:55 PM instead of 8:00 AM - 9:00 PM. The 10 minutes of lost dialing is insignificant compared to one violation.
- Validate postal code data on lead import. Reject or flag leads with missing or invalid zip codes. No zip code means no timezone protection.
- Audit weekly. Run the compliance audit script and review the "calls outside window" section. Any non-zero result needs investigation.
- Track DST transitions. Mark the second Sunday of March and the first Sunday of November on your calendar. Verify timezone data on the Monday after each transition.
- Keep state restrictions current. State telemarketing laws change. Review at least quarterly with legal counsel.
- Use NTP. If your server clock drifts even 5 minutes, you could dial early or late. Ensure NTP is running and synced: - Document everything. If you cannot prove compliance, you were not compliant. Auditors and courts want records, not verbal assurances. - Complete call_time and state_call_time configuration covering all 50 states plus territories
- Postal code data validation on every lead import, with automatic timezone enrichment
- DST transition management with pre- and post-transition verification
- Daily compliance auditing with automated alerting for any out-of-window dials
- Quarterly compliance reviews incorporating state law changes
- Audit-ready documentation that satisfies legal teams and regulatory inquiries - VICIdial Quality Assurance Scoring with Call Recordings -- compliance extends to recording disclosures and agent behavior
- VICIdial CNAM Lookup Integration for Inbound Routing -- handle the callbacks generated by your compliant outbound dialing
- VICIdial Database Partitioning for High-Volume Call Centers -- manage the vicidial_log data that documents your compliance
