Geo-gate a VICIdial campaign: local call time, state rules and ZIP validation

01 / 07

The gate runs on one column: gmt_offset_now

Fast answer: VICIdial does not know what time it is for a lead. It knows what time it is on the server, and it uses a per-lead offset stored in vicidial_list.gmt_offset_now, a DECIMAL(4,2) column defaulting to 0.00, to work out the lead's local clock. Every calling-hour and state-hour rule downstream, campaign-level local_call_time, per-state overrides, holidays, depends on that one number being correct. An offset of 0.00 is valid for a lead in a zero-offset zone; it is not evidence that a rule is disabled. Compare each lead’s offset with its actual time zone and the applicable campaign or list call-time window.

This is configuration guidance, not legal advice, and completing it does not establish that any call is lawful. Calling-hour restrictions and state-specific telemarketing rules are set by law in the United States and elsewhere, and they vary by state, province and country; confirm the actual rule text for every jurisdiction you call into with your own legal or compliance reviewer. Nothing in this article should be read as a citation to a specific statute or a specific permitted hour window.

See VICIdial terminology for complete beginners: users, phones, campaigns and leads for definitions of lead, list, campaign, agent, the hopper, disposition and carrier. For how leads get into vicidial_list in the first place, see the companion article “Load leads into VICIdial from a CSV file”; this guide picks up after that load, at the columns that decide whether a loaded lead may be dialed right now.

• Name every jurisdiction (state, province, country) this campaign calls into before you configure anything.
• Confirm gmt_offset_now is populated for every lead in the list, not just most of them.
• Treat a 0.00 offset as unverified, not as “lead is in GMT”, until you have checked why.

02 / 07

Step 1 — Understand how a lead's local time gets set

vicidial_list carries city, state VARCHAR(2), postal_code VARCHAR(10) and country_code VARCHAR(3) alongside gmt_offset_now, and the list itself carries a time_zone_setting ENUM with four values: COUNTRY_AND_AREA_CODE, POSTAL_CODE, NANPA_PREFIX and OWNER_TIME_ZONE_CODE, labeled Time Zone Setting on the Lists screen, defaulting to COUNTRY_AND_AREA_CODE. That setting decides which reference table a population job reads to fill in gmt_offset_now for every lead in that list: the area-code method reads vicidial_phone_codes (country_code, areacode, state, GMT_offset, DST, DST_range), the postal-code method reads vicidial_postal_codes (postal_code, state, GMT_offset, DST, DST_range, country, country_code), and the other two methods derive the offset from the phone number's NANPA prefix or from a fixed owner-assigned zone code instead of a lookup table.

The list stores an offset in gmt_offset_now, which can be used by dialer eligibility logic. Do not assume it is calculated exactly once or remains correct indefinitely: inspect the installed revision and offset-maintenance schedule, and verify the current offset and effective call-time rule on the target system, including after relevant daylight-saving or list changes.

The practical consequence: if area_code and postal_code disagree, because a lead moved, ported a number, or was entered with a typo, the method you chose decides which one wins, and the other is simply not consulted. Pick POSTAL_CODE when your source data trusts the ZIP (postal code) more than the phone number's area code, and confirm postal_code is actually populated before you do, since an empty postal_code may prevent the intended lookup; inspect the resulting offset against the lead’s actual time zone.

SHOW CREATE TABLE vicidial_list;SELECT list_id, list_name, time_zone_setting, local_call_timeFROM vicidial_listsWHERE list_id = '<LIST_ID>';SELECT COUNT(*) AS zero_or_null_offsetFROM vicidial_listWHERE list_id = '<LIST_ID>' AND (gmt_offset_now = 0.00 OR gmt_offset_now IS NULL);

03 / 07

Step 2 — Scope calling hours to a campaign, a state and a holiday

vicidial_campaigns.local_call_time is a VARCHAR(10) defaulting to '9am-9pm', labeled Local Call Time on the campaign screen. That default value is not a literal window; it is the call_time_id of the stock record VICIdial ships in vicidial_call_times, which stores ct_default_start and ct_default_stop as SMALLINT values in 24-hour HHMM form (900 and 2100 for the stock record), plus a start and stop pair for every day of the week that default to 0, meaning fall back to the default pair unless a specific day is overridden. A list can override its campaign's call time individually through vicidial_lists.local_call_time, which itself defaults to the literal string 'campaign', meaning inherit whatever the campaign says.

State-specific hours live in a separate table, vicidial_state_call_times, one row per named state rule (state_call_time_state VARCHAR(2)) with its own default and per-day start/stop pairs. A state rule only takes effect once it is attached to a call time record through vicidial_call_times.ct_state_call_times, a pipe-delimited TEXT column listing every state_call_time_id linked to that record, added and removed from the Admin → Call Times screen labeled ADD NEW STATE CALL TIME. Attach a state rule and the campaign's dial-eligibility check for a lead in that state uses the state's window instead of the record's own default; leave a state unlisted and it falls back to the parent call time's default hours.

Two things get confused with state-hour gating and are not the same mechanism. vicidial_campaigns.state_descriptions, a VARCHAR(40) defaulting to '---DISABLED---', is labeled State Descriptions Banner on the campaign screen; it points at a settings container of per-state text shown to the agent, useful for a state-mandated disclosure or script note, but it does not block or permit a call by itself. The actual per-state hour block is vicidial_state_call_times attached through ct_state_call_times, described above. Do not assume enabling one gives you the other.

SELECT campaign_id, local_call_time, state_descriptions FROM vicidial_campaigns WHERE campaign_id = '<CAMPAIGN_ID>';SELECT list_id, local_call_time FROM vicidial_lists WHERE campaign_id = '<CAMPAIGN_ID>';SELECT call_time_id, call_time_name, ct_default_start, ct_default_stop, ct_state_call_times, ct_holidaysFROM vicidial_call_timesWHERE call_time_id = (SELECT local_call_time FROM vicidial_campaigns WHERE campaign_id = '<CAMPAIGN_ID>');

• Confirm local_call_time on the campaign resolves to a real call_time_id, not an ad hoc string that happens to look like one.
• List every state you call into, and confirm whether each one needs its own row in vicidial_state_call_times or can safely use the parent default.
• Do not mistake state_descriptions (an agent-facing banner) for a calling-hour or calling-permission control.

04 / 07

Step 3 — Validate postal code and state before the load, not after

Gating only works if the value it depends on is real. A blank state, malformed postal code, or missing reference-table match can leave the lead with an incorrect or unverified offset. A zero offset can also be correct; compare it with the lead’s time zone and the call-time window. Validate before the load lands in vicidial_list, comparing the file with the installed VICIdial lookup tables (vicidial_postal_codes for postal_code and vicidial_phone_codes for area code and state). Those tables describe this installation’s lookup data; verify jurisdiction and current calling rules with an appropriate current authoritative source.

Normalize state and postal values to your agreed source format before import, then verify real lookup behavior on the installed revision. Case, trailing spaces and comparison behavior depend on the database collation and the application lookup; formatting alone does not prove a match or mismatch. A shape check does not establish a real jurisdiction or correct local-time rule.

Run the check as a pre-load gate against the file you are about to import, and again as a post-load audit against what actually landed, because a value can pass the first check and still fail to resolve if the list's time_zone_setting points at a different reference table than the one you validated against.

#!/usr/bin/env bashset -euo pipefailSRC_CSV="$1"STATE_COL=6ZIP_COL=7 bad_state=$(awk -F',' -v c="$STATE_COL" 'NR>1 && $c !~ /^[A-Z]{2}$/ {n++} END{print n+0}' "$SRC_CSV")bad_zip=$(awk -F',' -v c="$ZIP_COL" 'NR>1 && $c !~ /^[0-9]{5}(-[0-9]{4})?$/ {n++} END{print n+0}' "$SRC_CSV") echo "Rows with a bad state column: $bad_state"echo "Rows with a bad ZIP column: $bad_zip" if [ "$bad_state" -gt 0 ] || [ "$bad_zip" -gt 0 ]; then  echo "Fix or drop the flagged rows before loading; do not load and clean up after."  exit 1fiecho "State and ZIP columns pass basic shape validation."

05 / 07

Step 4 — Audit gmt_offset_now, state and postal_code after the load lands

A shape check on the source file catches malformed values, but it cannot catch a well-formed value that VICIdial's reference tables do not recognize, or a population job that has not run yet. The query below finds up to 1,000 leads with a zero or missing offset or blank state or postal code. Inspect each returned row against the lead’s actual time zone; zero may be correct. The query does not detect incorrect nonzero offsets or prove a call-time rule is effective.

Run this audit every time you load a new list, and again on a schedule against active lists, since a lead can arrive with good data and still lose it later through a manual edit, an API update, or a merge from a source that did not carry the same fields.

Use this access context for the installed postal-reference schema checks below. If the linked option file is root-owned mode 600, an authorized administrator can start the client with sudo mysql --defaults-file=/etc/vicidial-readonly.cnf, then run SELECT CURRENT_USER(), DATABASE(); and confirm the intended read-only account and schema before these queries. Do not expose or copy its credentials.

#!/usr/bin/env bashset -euo pipefailREADONLY_CNF=/etc/vicidial-readonly.cnfLIST_ID='<LIST_ID>'case "$LIST_ID" in ''|*[!0-9]*) printf '%s\n' 'LIST_ID must contain digits only' >&2; exit 1 ;; esacsudo mysql --defaults-file=$READONLY_CNF -e "SELECT lead_id, list_id, state, postal_code, country_code, gmt_offset_nowFROM vicidial_listWHERE list_id='$LIST_ID'  AND (gmt_offset_now = 0.00 OR gmt_offset_now IS NULL OR state = '' OR state IS NULL OR postal_code = '' OR postal_code IS NULL)ORDER BY lead_idLIMIT 1000;"

06 / 07

Step 5 — Add a holiday without breaking the rest of the schedule

vicidial_call_time_holidays holds one row per holiday: holiday_date, a holiday_status ENUM of ACTIVE, INACTIVE or EXPIRED, its own ct_default_start and ct_default_stop override pair, and a holiday_method VARCHAR(40) defaulting to REPLACE, with REPLACE and ADDITION_REVERSE as the two values offered on the Admin → Call Times screen under the label Holiday Inbound Method. A holiday only affects a given call time or state call time once its holiday_id is added to that record's ct_holidays pipe-delimited column; defining a holiday in the table does nothing on its own.

REPLACE is the documented default value in this schema. A schema default alone does not prove its runtime effect; confirm the behavior on your build with documentation and a controlled test. ADDITION_REVERSE is the alternate mode; confirm its exact effect on a test call time in your own environment before relying on it for a live campaign, since the label alone does not fully specify the behavior and this article will not guess at it.

Set holiday_status to INACTIVE well before the date if you decide a holiday should not apply this year, rather than deleting the row; EXPIRED is the status VICIdial uses for a holiday whose date has already passed, and keeping the row around with a clear status is cheaper than reconstructing it next year.

07 / 07

Troubleshoot a gate that is not holding, and how to stop safely

For a call outside the expected hours, compare the lead’s gmt_offset_now with its actual time zone, then inspect the campaign and list call-time settings and applicable state and holiday rules. A zero offset is valid for some time zones. A state you believed was gated but is not usually means the state's rule exists in vicidial_state_call_times but was never added to ct_state_call_times on the call time the campaign actually uses, so it is silently falling back to the parent default. A holiday that did not take effect usually means the holiday_id was never added to ct_holidays on that specific call time record, even though the holiday row itself looks correctly configured.

If you find leads dialing on a bad offset, stop that list from feeding the hopper first, then use the audit query as a starting point; it finds only the listed conditions and at most 1,000 rows, so separately identify all affected lead IDs before deciding whether to fix the value in place or pull the batch and reload it with corrected postal_code and state values.

Do not fix an urgent gating failure by hand-editing gmt_offset_now on individual rows under pressure. Correct the underlying data through the supported interface for your build, then follow its documented offset-recalculation procedure. Verify the resulting offset rather than assuming an edit automatically recomputed it. Re-run the audit query on the affected list before you consider the incident closed; a fix you have not re-verified is not a fix yet.

• Compare the affected lead’s gmt_offset_now with its actual time zone and inspect the campaign and list call-time settings.
• Confirm ct_state_call_times and ct_holidays actually list the state or holiday you expect, not just that the state or holiday row exists somewhere.
• Stop a list from feeding the hopper before investigating, not after.
• Re-run the post-load audit on the affected list before closing the incident.