uc-bq Configuration Reference

Overview

uc-bq reads one configuration file, .ultracart-bq.json, and a set of environment variables. The file holds everything that is safe to commit: merchant IDs, taxonomy levels, dataset names, output preferences, and cost limits. Credentials never appear in it. Secrets live in environment variables, listed at the end of this page.

Per-report settings such as parameters, alarms, and delivery live in each report's report.yaml manifest instead, and are documented on Report delivery and Report alarms.

File location

The config is looked up in two places, in order:

1. .ultracart-bq.json in the current working directory
2. .ultracart-bq.json in the user's home directory

The first match wins. uc-bq validate --config checks a file against the bundled JSON Schema, and uc-bq init writes one.

warning

Every uc-bq config subcommand reads and writes the current working directory only. A config in the home directory is loaded at runtime but cannot be edited through the CLI.

A legacy single-merchant config containing a top-level project_id is migrated in memory at load time and produces a [WARNING] Old config format detected line on standard error. The file on disk is unchanged until it is rewritten.

Config file fields

Only default_merchant and merchants are required. Unknown keys are rejected by the schema.

Field	Type	Default	Description
default_merchant	string		Merchant used when no -m flag is given. Required
merchants	object		Map of merchant ID to merchant settings. Required
default_output_dir	string	./reports	Root directory for report output
output_format	string	png	png, pdf, or both
chart_theme	string	default	ECharts theme applied to all charts
chart_defaults.width	integer	1200	Chart width in pixels, from 400 to 3840
chart_defaults.height	integer	600	Chart height in pixels, from 300 to 2160
max_query_bytes	integer	10737418240	Abort a query estimated above this many bytes. 0 disables the check
llm	object		Provider settings for analysis and schema filtering. Optional

Each entry under merchants takes these fields:

Field	Type	Default	Description
taxonomy_level	string		standard, low, medium, or high. Required
dataset	string	ultracart_dw	Dataset holding the streaming tables and views. Required
external_projects	object		Additional BigQuery projects, keyed by alias

A complete example:

{
  "default_merchant": "DEMO",
  "merchants": {
    "DEMO": {
      "taxonomy_level": "medium",
      "dataset": "ultracart_dw"
    },
    "DEMO2": {
      "taxonomy_level": "standard",
      "dataset": "ultracart_dw",
      "external_projects": {
        "marketing": {
          "project_id": "my-marketing-warehouse",
          "description": "Google Ads data via Funnel.io",
          "datasets": {
            "google_ads_data": ["funnel_data"]
          }
        }
      }
    }
  },
  "default_output_dir": "./reports",
  "output_format": "png",
  "chart_theme": "default",
  "chart_defaults": { "width": 1200, "height": 600 },
  "max_query_bytes": 10737418240,
  "llm": {
    "provider": "anthropic",
    "api_key_env": "ANTHROPIC_API_KEY",
    "analysis_model": "claude-sonnet-4-5-20250929",
    "schema_filter_model": "claude-haiku-4-5-20251001"
  }
}

External project entries require project_id and datasets; description is optional. See External data sources.

Derived values

Two values are computed rather than configured, so they never need to be pasted in.

Value	Derived from
BigQuery project ID	ultracart-dw- followed by the merchant ID in lowercase
Report directory	default_output_dir, then the merchant ID exactly as spelled in the config key

Taxonomy levels

Taxonomy level is assigned to each Google account by your UltraCart administrator, not chosen in this file. The value here has to match what UltraCart granted, otherwise queries fail with a dataset or permission error. Account setup is covered in Providing Users Access to the Data Warehouse.

Level	Access
standard	No personally identifiable information. Order totals, item data, analytics
low	Minimal personally identifiable information
medium	Adds email addresses, postal addresses, and customer detail
high	All fields

Datasets

Dataset	Contents	Available to
ultracart_dw	Standard tables, no personally identifiable information	All merchants
ultracart_dw_medium	Includes personally identifiable information	Medium and high taxonomy
ultracart_dw_streaming	Analytics sessions and screen recordings, separated because the tables are very large	All merchants
ultracart_dw_linked	Parent and child aggregated data	Parent accounts
ultracart_dw_linked_medium	Linked data including personally identifiable information	Parent accounts at medium or high

uc-bq schema --list surfaces the standard, medium, and streaming datasets. The two linked datasets are referenced explicitly in SQL rather than listed. The table inventory for each is in Data Warehouse (BigQuery).

Report parameters

Parameters are declared in each report's report.yaml and supplied to BigQuery as named query parameters:

parameters:
  - name: start_date
    type: date
    label: "Start Date"
    required: true
    default: "-90d"
  - name: end_date
    type: date
    label: "End Date"
    required: true
    default: "today"

Two parameter names are treated specially, matching the behaviour of UltraCart's own reporting engine: a start_date value is expanded to YYYY-MM-DD 00:00:00 and an end_date value to YYYY-MM-DD 23:59:59, so a range covers whole days at both ends.

Values are overridden per run with --param_name=value on run, run-all, and deck run, or changed permanently with uc-bq config set-param.

Relative date expressions

A parameter default can be a fixed date such as 2026-01-01, or one of the expressions below, which resolve every time the report runs. A report defaulting to -90d therefore always covers the most recent ninety days.

Expression	Resolves to
today	The current date
yesterday	The previous day
-Nd	N days ago, for example -90d
-Nw	N weeks ago
-Nm	N months ago
-Ny	N years ago
start_of_week	The most recent Sunday
start_of_month	First day of the current month
start_of_quarter	First day of the current quarter
start_of_year	January 1 of the current year
start_of_last_month	First day of the previous month
start_of_last_quarter	First day of the previous quarter
start_of_last_year	January 1 of the previous year
end_of_last_month	Last day of the previous month
end_of_last_quarter	Last day of the previous quarter
end_of_last_year	December 31 of the previous year

start_of_week is Sunday rather than the ISO-8601 Monday, and that is deliberate. The UltraCart warehouse tables are partitioned on Sunday week boundaries, so a Sunday-aligned range matches the partitions and prunes cleanly. A Monday-aligned range would straddle every partition boundary, scanning more data than the query needs and shifting the reported week by a day.

All expressions resolve against the local time zone of the machine running the command, not UTC. A scheduled runner in a different time zone from your storefront can therefore produce a different range, which matters most around midnight.

Cost protection

Every command that executes a query (query, run, run-all, deck run) performs a BigQuery dry run first and compares the estimate against a limit. Over the limit, the query is abandoned before it costs anything:

Query would process 45.2 GB (estimated cost: $0.2825), which exceeds the safety limit of
10.0 GB. Use --force to execute anyway, or set a higher limit with --max-bytes.

The default limit is 10 GB, roughly $0.06 at BigQuery's on-demand rate of $6.25 per TB.

Method	Scope	Example
--force	One command	uc-bq run revenue-by-category --force
--max-bytes <bytes>	One command	uc-bq run revenue-by-category --max-bytes=53687091200
max_query_bytes	All commands	Set in .ultracart-bq.json. 0 disables the check entirely

tip

A query that trips the limit usually has a partition problem rather than a size problem. Tighten the date filter or add a partition_date predicate before reaching for --force.

LLM providers

The llm section applies to executive analysis on headless runs and to LLM-backed schema filtering. It has no effect while you work inside Claude Code, because Claude Code is itself the model doing the work.

Provider	API key variable	Default analysis model	Default filter model
anthropic	ANTHROPIC_API_KEY	claude-sonnet-4-5-20250929	claude-haiku-4-5-20251001
openai	OPENAI_API_KEY	gpt-4o	gpt-4o-mini
grok	XAI_API_KEY	grok-2	grok-2
bedrock	AWS credential chain	anthropic.claude-sonnet-4-5-20250929-v1:0	anthropic.claude-haiku-4-5-20251001-v1:0
gemini	GOOGLE_API_KEY	gemini-2.0-flash	gemini-2.0-flash-lite

Every field in the section is optional, and omitting the section entirely selects anthropic. The region field applies only to bedrock. All five provider SDKs ship with the package, so no extra installs are needed.

{
  "llm": {
    "provider": "openai",
    "api_key_env": "OPENAI_API_KEY",
    "analysis_model": "gpt-4o",
    "schema_filter_model": "gpt-4o-mini"
  }
}

The configured provider is overridden for a single command with --llm-provider:

uc-bq run revenue-by-category --llm-provider=openai --analysis-api-key=$OPENAI_API_KEY

When no API key is available, analysis is skipped and the run continues, producing the chart, data, and PDF without written commentary.

Environment variables

Variable	Used by	Description
GOOGLE_APPLICATION_CREDENTIALS	BigQuery	Path to a service account JSON key, as an alternative to gcloud credentials
ANTHROPIC_API_KEY	Analysis, schema filtering	Anthropic API key
OPENAI_API_KEY	Analysis, schema filtering	OpenAI API key
XAI_API_KEY	Analysis, schema filtering	xAI Grok API key
GOOGLE_API_KEY	Analysis, schema filtering	Google Gemini API key
SLACK_BOT_TOKEN	Slack delivery and alarms	Bot token beginning xoxb-
EMAIL_FROM	All email delivery	Sender address. Required for any email delivery
SENDGRID_API_KEY	Email via SendGrid
POSTMARK_API_KEY	Email via Postmark
MAILGUN_API_KEY	Email via Mailgun
MAILGUN_DOMAIN	Email via Mailgun	Sending domain
RESEND_API_KEY	Email via Resend
AWS_REGION or AWS_DEFAULT_REGION	Email via SES	Defaults to us-east-1. SES also uses the standard AWS credential chain

AWS Bedrock uses the standard AWS credential chain and needs no API key variable. SES delivery additionally requires the @aws-sdk/client-sesv2 package, which is the only dependency not bundled.

Other files on disk

Path	Written by	Contents
.ultracart-bq-cache/{project}/{dataset}/{table}.json	uc-bq schema	Cached external table schemas, cleared by --refresh
reports/{merchant}/{report}/alarm_state.json	uc-bq run	Metric history, capped at 30 entries, plus active alarm suppressions
reports/{merchant}/decks/*.yaml	uc-bq deck create	Deck definitions

Related Documentation

• CLI reference - the commands that read and write these settings
• External data sources - registering additional BigQuery projects
• Report delivery - the manifest fields the delivery variables support
• Data Warehouse (BigQuery) - warehouse access, pricing, and the table inventory