Sources

src_shopify

E-commerce order and store data from Shopify.

orders

Shopify order transactions.

Column	Description
id	Unique Shopify order id.
order_number	Human-readable order number (e.g. 1001) shown in admin and to customers.
name	Order display name (e.g.
email	Customer email address for the order.
phone	Customer phone number for the order.
created_at	Timestamp when the order was created (UTC).
updated_at	Timestamp when the order was last updated (UTC).
processed_at	Timestamp when the order was processed; null if not yet processed.
closed_at	Timestamp when the order was closed; null if still open.
cancelled_at	Timestamp when the order was cancelled; null if not cancelled.
financial_status	Payment status (e.g. pending, paid, refunded, partially_refunded).
fulfillment_status	Fulfillment status (e.g. null, fulfilled, partial, restocked).
total_price	Total price of the order (string, in shop currency).
subtotal_price	Subtotal before shipping, tax, and discounts (string).
total_tax	Total tax amount for the order (string).
total_discounts	Total discount amount applied to the order (string).
total_weight	Total weight of the order in grams.
currency	Three-letter ISO 4217 currency code for the order.
line_items	Array of line item objects (quantity, price, title, etc.) for products in the order.
refunds	Array of refund objects, including refund_line_items and amounts.
billing_address	Billing address object (address1, city, province, country, zip, etc.).
shipping_address	Shipping address object (address1, city, province, country, zip, etc.).
customer	Customer object (id, email, etc.) when associated with a customer record.
note	Optional note attached to the order.
tags	Comma-separated tags applied to the order.
source_name	Order source (e.g. web, pos, shopify_draft_order).
confirmation_number	Confirmation number or code for the order.
_airbyte_extracted_at	Extraction timestamp from Airbyte for this Shopify order snapshot.

src_stripe

Stripe payment and customer data.

customers

Stripe customer records.

Column	Description
id	Stripe customer id.
email	Customer email address.
phone	Customer phone number.
created	Unix timestamp when the Stripe customer was created.
is_deleted	True if the Stripe customer record is marked as deleted.
_airbyte_extracted_at	Extraction timestamp from Airbyte for this Stripe customer snapshot.

src_google_ads

Google Ads campaign and performance data.

campaign

Google Ads campaign-level metrics and attributes.

Column	Description
_airbyte_extracted_at	Extraction timestamp from Airbyte for this Google Ads campaign snapshot.

src_facebook_ads

Meta (Facebook/Instagram) Ads campaign data.

customcampaign_stats

Custom campaign statistics from Meta Ads.

Column	Description
_airbyte_extracted_at	Extraction timestamp from Airbyte for this Meta Ads custom campaign snapshot.

src_tincan

Tincan application data (devices and customers).

legacy_devices

Device records from Tincan.

Column	Description
device_id	Unique device identifier in Tincan.
customer_id	Owning Tincan customer id for the device.
device_mac	Device MAC address.
extension	Internal extension number assigned to the device.
did_number	Direct inward dial (DID) or outbound caller id number.
did_number_normalized	Normalized version of the DID number used for joins.
caller_id	Caller id display name for this device.
enabled_911	Flag indicating whether 911 emergency calling is enabled.
is_discoverable	Flag indicating whether the device is discoverable in the app.
device_name	Human-readable device name.
device_meta	JSON metadata about the device.
device_avatar	URL or identifier for the device avatar.
external_network	Flag indicating whether the device is on an external network.
status	Raw device status value in Tincan.
created_at	Timestamp when the device record was created.
updated_at	Timestamp when the device record was last updated.
subscription_type	Subscription type associated with the device.
dnd_until	Timestamp until which do-not-disturb is active.
dnd_status	Current do-not-disturb status for the device.
onboarding_complete	Flag indicating whether device onboarding is complete.
device_status	Operational device status (for example, online or offline).
device_type	Device type (for example, desk phone or softphone).
country	Country associated with the device address.
city	City associated with the device address.
zip_postal_code	Zip or postal code for the device address.
state_province	State or province for the device address.
street	Primary street address for the device.
street_secondary	Secondary street address line for the device.
twilio_sid	Twilio SID for the associated phone number.
twilio_emergency_address_sid	Twilio emergency address SID for the device.
emergency_address_confirmed_date	Date when the emergency address was confirmed.
emergency_address_validated_date	Date when the emergency address was validated.

legacy_customers

Customer records from Tincan.

Column	Description
customer_id	Tincan customer id.
stripe_customer_id	Stripe customer id associated with the Tincan customer, when present.
email	Primary email address for the customer.
phone	Primary phone number for the customer.
updated_at	Timestamp when the customer record was last updated.

legacy_contacts

Contact records from Tincan.

Column	Description
contact_id	Tincan contact id.

legacy_device_mac_addresses

Device mac address records from Tincan.

Column	Description
id	Tincan device mac address id.

legacy_cdr

Call detail records from Asterisk.

Column	Description
calldate	Timestamp when the call started.
clid	Caller id string from the raw CDR.
src	Raw source identifier for the call.
dst	Raw destination identifier for the call.
dcontext	Dialplan context for the call.
channel	Asterisk source channel name.
dstchannel	Asterisk destination channel name.
lastapp	Last Asterisk application executed for the call.
lastdata	Arguments passed to the last Asterisk application.
duration	Total call duration in seconds.
billsec	Billable seconds for the call.
disposition	Raw call disposition (for example, ANSWERED or NO ANSWER).
amaflags	Automatic Message Accounting flags.
accountcode	Billing account code for the call.
uniqueid	Unique call identifier from the CDR.
userfield	Free-form user field from the CDR.
peeraccount	Peer account associated with the call.
linkedid	Identifier used to group related CDR rows for the same call.
sequence	Sequence number of the CDR row.
src_normalized	Normalized version of src used for joins.
dst_normalized_new	Normalized version of dst used for joins.
cdr_key_concat	Composite key used as a stable identifier for the CDR row.

src_history

Historical call logs and participant data from FreePBX, Twilio, and related systems.

bi_call_logs_fusion_freepbx

Fused call logs from FreePBX for BI.

Column	Description
uniqueid	Unique call identifier from the fused FreePBX logs.
call_date	Timestamp of the call.
call_from	Source identifier for the call.
tc_call_from_name	Human-readable label for call_from.
call_is_from_tin_can	True if call_from corresponds to a Tincan device or DID.
dest_id_raw	Raw destination identifier used for routing.
dest_number_raw	Normalized destination phone number.
call_to	Destination identifier for the call.
tc_call_to_name	Human-readable label for call_to.
call_is_to_tin_can	True if call_to corresponds to a Tincan device or DID.
call_classification	Type of call (for example, can_to_can, can_to_external, or external_to_can).
is_voicemail_check	True if the call was a voicemail check.
disposition_cleaned	Cleaned call disposition.
call_was_answered	True if the call had a human conversation.
talk_time_corrected	Billable talk time for the call in seconds.
left_voicemail	True if the caller left a voicemail.
from_shopify_user_id	Shopify user id for the caller, when available.
to_shopify_user_id	Shopify user id for the callee, when available.
from_customer_email	Customer email address for the caller, when available.
to_customer_email	Customer email address for the callee, when available.
cdr_key_concat	Composite key used as a stable identifier for the fused CDR row.

formatted_twilio_external_to_can_calls

Formatted Twilio external-to-Canada call records.

Column	Description
uniqueid	Unique call identifier for the Twilio call.
call_date	Timestamp of the call.
call_from	Source phone number for the call.
tc_call_from_name	Human-readable label for call_from.
call_is_from_tin_can	True if call_from corresponds to a Tincan device or DID.
dest_id_raw	Raw destination identifier used for routing.
dest_number_raw	Normalized destination phone number.
call_to	Destination phone number for the call.
tc_call_to_name	Human-readable label for call_to.
call_is_to_tin_can	True if call_to corresponds to a Tincan device or DID.
call_classification	Type of call (for example, can_to_can, can_to_external, or external_to_can).
is_voicemail_check	True if the call was a voicemail check.
disposition_cleaned	Cleaned call disposition.
call_was_answered	True if the call had a human conversation.
talk_time_corrected	Billable talk time for the call in seconds.
left_voicemail	True if the caller left a voicemail.
from_shopify_user_id	Shopify user id for the caller, when available.
to_shopify_user_id	Shopify user id for the callee, when available.
from_customer_email	Customer email address for the caller, when available.
to_customer_email	Customer email address for the callee, when available.
cdr_key_concat	Composite key used as a stable identifier for the Twilio CDR row.

all_dial_numbers

All dialed phone numbers.

Column	Description
dial_number	Historical dial number used as a device identifier.
domain_number	Domain or customer number associated with the dial number.
extension_number	Internal extension number mapped to the dial number.
caller_id_name	Display name associated with the dial number.
install_date	Date the dial number was first installed or became active.
created_at	Timestamp when the dial number record was created.
outbound_caller_id_number	Outbound caller id number associated with the dial number.

view_tc_participant_slice_daily_summary

Daily summary of Tincan participant slice metrics.

Column	Description
call_day	Date of the calls included in the daily summary.
day_of_week	Day of week for call_day.
this_participant_name	Participant name for the device or dial number.
this_participant_dial_number	Participant dial number identifier.
total_talk_duration	Total talk time for the participant on the given day.
successful_calls_participated_in	Count of answered calls the participant took part in on the given day.
calls_made	Outbound calls placed by the participant on the given day.
successful_calls_made	Answered outbound calls by the participant on the given day.
calls_received	Inbound calls received by the participant on the given day.
successful_calls_received	Answered inbound calls received by the participant on the given day.
num_interlocutors	Distinct counterparties the participant interacted with on the given day.
num_external_calls	Number of calls between the participant and external numbers on the given day.
num_successful_external_calls	Answered calls between the participant and external numbers on the given day.
num_voicemails_left	Number of voicemails left by the participant on the given day.
num_voicemails_received	Number of voicemails received by the participant on the given day.
diff_from_first_call_day	Days since the participant’s first call day.

participant_slice_underlying

Underlying participant slice detail for aggregations.

Column	Description
this_participant_dial_number	Participant dial number identifier.
this_participant_name	Participant name for the dial number.
call_day	Date of the call.
num_entries_forcall	Number of participant entries associated with the call.
uniqueid	Unique call identifier.
call_date	Timestamp of the call.
call_from	Source identifier for the call.
tc_call_from_name	Human-readable label for call_from.
call_is_from_tin_can	True if call_from corresponds to a Tincan device or DID.
dest_id_raw	Raw destination identifier used for routing.
dest_number_raw	Normalized destination phone number.
call_to	Destination identifier for the call.
tc_call_to_name	Human-readable label for call_to.
call_is_to_tin_can	True if call_to corresponds to a Tincan device or DID.
call_classification	Type of call (for example, can_to_can, can_to_external, or external_to_can).
is_voicemail_check	True if the call was a voicemail check.
disposition_cleaned	Cleaned call disposition.
call_was_answered	True if the call had a human conversation.
talk_time_corrected	Billable talk time for the call in seconds.
left_voicemail	True if the caller left a voicemail.
cdr_key_concat	Composite key used as a stable identifier for the underlying CDR row.

src_utilities_finance

Finance / FP&A data manually loaded into UTILITIES_DB.FINANCE. See docs/utilities/budget_table.md for the full narrative.

budget

Monthly budget projections under three recognition types — GAAP, CASH, and NON_FINANCIAL — in a single wide table. Each (version, month, recognition_type) is a unique row. The IS_CURRENT flag marks the active version; prior versions are retained as history.

Column	Description
version	Budget version identifier (e.g. 2026-04-13_por_update). Each import carries a distinct version.
year	Budget year.
quarter	Quarter (1–4) corresponding to the month.
month	First day of the budgeted month (DATE).
recognition_type	One of GAAP, CASH, or NON_FINANCIAL. Determines which columns are populated on the row.
is_current	TRUE for rows belonging to the latest imported version. Filter on this (or use budget_current) for the active budget.
loaded_at	Timestamp when the row was imported.
hardware_revenue	Hardware revenue. On GAAP rows, recognized on fulfillment; on CASH rows, cash received from hardware sales.
software_revenue	Subscription revenue. On GAAP rows, recognized as earned; on CASH rows, cash received.
total_revenue	Hardware Revenue + Software Revenue (populated on both GAAP and CASH rows with values matching that recognition basis).
software_cogs	Software cost of goods sold. Populated on both GAAP and CASH rows (accrual vs cash basis).
software_gp	Software gross profit = Software Revenue − Software CoGS. Populated on both GAAP and CASH rows.
software_gm	Software gross margin = Software GP ÷ Software Revenue (decimal, e.g. 0.72 = 72%). Populated on both GAAP and CASH rows.
opex	Total operating expenses (S&M, Personnel, R&D, Professional Services, G&A, Depreciation). Populated on both GAAP (accrual) and CASH (paid) rows.
hardware_cogs	Cost of goods sold for hardware (GAAP rows only).
hardware_gp	Hardware Revenue − Hardware CoGS (GAAP rows only).
hardware_gm	Hardware GP ÷ Hardware Revenue (decimal; GAAP rows only).
total_gp	Hardware GP + Software GP (GAAP rows only).
total_gm	Total GP ÷ Total Revenue (decimal; GAAP rows only).
ebit	Earnings Before Interest & Taxes = Total GP − OpEx (GAAP rows only).
inventory_cash_outlay	Cash paid to purchase hardware inventory (CASH rows only).
other_cogs	Other cost of goods sold paid in cash (CASH rows only).
taxes_financing_other	Taxes, financing costs, and other cash outflows (CASH rows only).
change_in_cash_from_operations	Cash flow from operating activities (CASH rows only).
change_in_cash_from_investing	Cash flow from investing activities (CASH rows only).
change_in_cash_from_financing	Cash flow from financing activities (CASH rows only).
net_change_in_cash	Sum of Operations + Investing + Financing (CASH rows only).
starting_cash	Cash balance at start of month (CASH rows only).
cash_balance_at_eom	Starting Cash + Net Change in Cash (CASH rows only).
units_sold	Devices sold in the month — drives CASH hardware revenue (NON_FINANCIAL rows only).
units_fulfilled	Devices delivered to customers in the month — drives GAAP hardware revenue (NON_FINANCIAL rows only).
paying_subscribers_monthly	Monthly plan subscribers at end of month (NON_FINANCIAL rows only).
paying_subscribers_annual	Annual plan subscribers at end of month (NON_FINANCIAL rows only).
paying_subscribers_total	Monthly + Annual subscribers (NON_FINANCIAL rows only).

budget_current

Convenience view over budget filtered to IS_CURRENT = TRUE. Monthly budget projections for the active version only, under three recognition types — GAAP, CASH, and NON_FINANCIAL. Each (month, recognition_type) is a unique row. Column definitions mirror budget (kept in sync manually — see CLAUDE.local.md).

Column	Description
version	Budget version identifier (e.g. 2026-04-13_por_update). Each import carries a distinct version.
year	Budget year.
quarter	Quarter (1–4) corresponding to the month.
month	First day of the budgeted month (DATE).
recognition_type	One of GAAP, CASH, or NON_FINANCIAL. Determines which columns are populated on the row.
is_current	Always TRUE in this view (it filters on IS_CURRENT = TRUE).
loaded_at	Timestamp when the row was imported.
hardware_revenue	Hardware revenue. On GAAP rows, recognized on fulfillment; on CASH rows, cash received from hardware sales.
software_revenue	Subscription revenue. On GAAP rows, recognized as earned; on CASH rows, cash received.
total_revenue	Hardware Revenue + Software Revenue (populated on both GAAP and CASH rows with values matching that recognition basis).
software_cogs	Software cost of goods sold. Populated on both GAAP and CASH rows (accrual vs cash basis).
software_gp	Software gross profit = Software Revenue − Software CoGS. Populated on both GAAP and CASH rows.
software_gm	Software gross margin = Software GP ÷ Software Revenue (decimal, e.g. 0.72 = 72%). Populated on both GAAP and CASH rows.
opex	Total operating expenses (S&M, Personnel, R&D, Professional Services, G&A, Depreciation). Populated on both GAAP (accrual) and CASH (paid) rows.
hardware_cogs	Cost of goods sold for hardware (GAAP rows only).
hardware_gp	Hardware Revenue − Hardware CoGS (GAAP rows only).
hardware_gm	Hardware GP ÷ Hardware Revenue (decimal; GAAP rows only).
total_gp	Hardware GP + Software GP (GAAP rows only).
total_gm	Total GP ÷ Total Revenue (decimal; GAAP rows only).
ebit	Earnings Before Interest & Taxes = Total GP − OpEx (GAAP rows only).
inventory_cash_outlay	Cash paid to purchase hardware inventory (CASH rows only).
other_cogs	Other cost of goods sold paid in cash (CASH rows only).
taxes_financing_other	Taxes, financing costs, and other cash outflows (CASH rows only).
change_in_cash_from_operations	Cash flow from operating activities (CASH rows only).
change_in_cash_from_investing	Cash flow from investing activities (CASH rows only).
change_in_cash_from_financing	Cash flow from financing activities (CASH rows only).
net_change_in_cash	Sum of Operations + Investing + Financing (CASH rows only).
starting_cash	Cash balance at start of month (CASH rows only).
cash_balance_at_eom	Starting Cash + Net Change in Cash (CASH rows only).
units_sold	Devices sold in the month — drives CASH hardware revenue (NON_FINANCIAL rows only).
units_fulfilled	Devices delivered to customers in the month — drives GAAP hardware revenue (NON_FINANCIAL rows only).
paying_subscribers_monthly	Monthly plan subscribers at end of month (NON_FINANCIAL rows only).
paying_subscribers_annual	Annual plan subscribers at end of month (NON_FINANCIAL rows only).
paying_subscribers_total	Monthly + Annual subscribers (NON_FINANCIAL rows only).