Skip to content

Changelog

June 9, 2026

Reworked scripts/load_budgets.py to load the wide budget Excel workbook

The budget loader now ingests the single wide Excel workbook (one row per month, with cash, GAAP, and non-financial metrics side by side) instead of two pre-pivoted CSVs. For each month it pivots into the three long-format rows the UTILITIES_DB.FINANCE.BUDGET table expects (CASH, GAAP, NON_FINANCIAL) — each row populates only its recognition type's columns, leaving the rest NULL (the prior CSV path never emitted NON_FINANCIAL rows, so it had drifted from the live table). Columns are mapped by position because several headers repeat in the workbook (e.g. two Other CoGS, two Software CoGS); OTHER_COGS maps to the primary Other CoGS column. Month values are normalized from end-of-month to first-of-month. New flags: --excel (path), --version (label), and --year (load a single budget year). Added validation: a column-layout drift guard, duplicate-month and year/quarter consistency checks (hard errors), and non-fatal warnings when a revenue line doesn't foot (Total != Hardware + Software). The idempotent delete-by-version → insert → IS_CURRENT flip → recreate BUDGET_CURRENT view flow is unchanged.

Loaded budget version 2026-06-09_por_update

Imported the June 2026 budget for calendar year 2026 (36 rows: 12 months × 3 recognition types) and made it the current version. Loaded 2026 only to match the prior load's scope; the source workbook also contains 2025 and 2027–2028.

Added load-budget skill

New project skill at .claude/skills/load-budget/ that codifies the budget-load workflow (inspect the workbook → adapt the positional column maps → dry-run/validate → load → verify → compare versions). It captures the judgment needed when the Excel format drifts: use the xlsx not a lossy CSV export, map columns by position (headers repeat), normalize end-of-month dates, treat footing-check warnings as the source model's numbers (flag material discrepancies to the user, ignore rounding), and confirm ambiguous column choices. Bundles scripts/inspect_budget_excel.py (dumps the column layout by position + runs footing checks) and references/verification_queries.sql (post-load verification and prev-vs-new version comparison).

May 15, 2026

Added vw_abacum_inventory_balance view

New queries/supply_chain/supply_chain_tool/db_operations/create_vw_abacum_inventory_balance.sql defines UTILITIES_DB.FINANCE.VW_ABACUM_INVENTORY_BALANCE — per-month physical and available inventory totals for the 4 Tin Can SKUs (TCMOD1AQUA, TCMOD1LILAC, TCMOD1LEMON, TCMOD1WHITE), aggregated across SKUs. Sourced from EC_INVENTORY_HISTORY daily snapshots: *_MONTH_START_* columns sum physical/available on the first-of-month snapshot, *_MONTH_END_* on the last-day-of-month snapshot. NULL when the exact date has no snapshot (e.g. a partial first month, or the in-progress current month before month-end). Month spine spans the calendar months covered by the source table and rolls forward as new snapshots land. Granted SELECT to ABACUM_ROLE and REPORT_ROLE (mirroring the other VW_ABACUM_* views).

May 11, 2026

Added vw_abacum_po_cost_crossjoin view

New queries/supply_chain/supply_chain_tool/db_operations/create_vw_abacum_po_cost_crossjoin.sql defines UTILITIES_DB.FINANCE.VW_ABACUM_PO_COST_CROSSJOIN — a cross-join of every month from 2025-10-01 through 12 months past the current month with every PO from VW_ABACUM_UNIFIED_PO_ITEMS_WIDE, repeating each PO's total_cost on every month row. Designed for Abacum to pivot into a wide month × PO matrix of total costs. The month range is computed dynamically (<= dateadd(month, 12, date_trunc('month', current_date))) so the window rolls forward as time passes. Granted SELECT to ABACUM_ROLE and REPORT_ROLE (mirroring VW_ABACUM_UNIFIED_PO_ITEMS_WIDE); the existing future grants on UTILITIES_DB.FINANCE views also cover those roles.

May 6, 2026

Added po_issue_date to vw_abacum_unified_po_items_wide

Added a new po_issue_date column to UTILITIES_DB.FINANCE.VW_ABACUM_UNIFIED_PO_ITEMS_WIDE. For PO numbers found in EC_PURCHASE_ORDERS, the value is coalesce(issue_date, confirmed_date)::date (cast from timestamp_tz). For input-only POs, it's expected_delivery_date - 4 months (a 4-month ocean lead-time assumption — the only date in INPUT_PURCHASE_ORDERS is expected_delivery_date). Spot-checked against several EC and INPUT POs; no NULL values across the view. The CREATE OR REPLACE preserved the prior ABACUM_ROLE/REPORT_ROLE SELECT grants.

May 5, 2026

Added vw_abacum_po_delivery_pct_by_month view

New queries/supply_chain/supply_chain_tool/db_operations/create_vw_abacum_po_delivery_pct_by_month.sql defines UTILITIES_DB.FINANCE.VW_ABACUM_PO_DELIVERY_PCT_BY_MONTH — a long-format matrix (one row per po_number × delivery_month) of each PO's expected unit deliveries by month, sourced from VW_UNIFIED_SHIPMENTS (real EC + synthetic shipments). pct_of_remaining = units_in_month / units_remaining_total is a true distribution that sums to 1.0 per PO across the rows shown. Any shipment dated in a prior month (judged purely by delivery date, regardless of EC status) is excluded from both the numerator and denominator — so a PO with 1,000 units and a 100-unit shipment last month uses 900 as the denominator, not 1,000. POs whose shipments are entirely in the past don't appear. Granted SELECT to ABACUM_ROLE.

Added vw_abacum_unified_po_items_wide view

New queries/supply_chain/supply_chain_tool/db_operations/create_vw_unified_po_items_wide.sql defines UTILITIES_DB.FINANCE.VW_ABACUM_UNIFIED_PO_ITEMS_WIDE — a PO-grain wide-SKU view that combines INPUT_PURCHASE_ORDERS with EC POs (via VW_EC_PO_ITEMS) using a FULL OUTER JOIN on po_number. EC data takes precedence when a PO exists in both. Adds two columns on top of the VW_EC_PO_ITEMS_WIDE shape: po_source (EC or INPUT) and total_cost (EC line-item cost preferred; INPUT unit_cost * qty_total as fallback). Created in the FINANCE schema (rather than SUPPLY_CHAIN) so the FP&A platform Abacum can read it; granted USAGE on UTILITIES_DB/UTILITIES_DB.FINANCE and SELECT on the view to ABACUM_ROLE.

Apr 24, 2026

Fix broken Markdown table row when a column description is a YAML block scalar

scripts/generate_dbt_docs.py now flattens newlines in column descriptions before emitting the Markdown table row for docs/dbt/sources.md / docs/dbt/models.md. A | block-scalar description loads into Python with literal newlines; those newlines previously ended the table row early on the docs site and made cells spill out as loose prose (first visible on the creative_unit_type row of combined_google_ad_data). The new _cell() helper is applied only to strings destined for a table cell — model-, table-, and source-level paragraph descriptions are untouched. Intra-line whitespace is preserved per line, so every existing single-line description renders byte-for-byte identical to before.

Added ads-level Google & Meta sources and combined_google_ad_data model

Documented three new ad-level source tables in dbt/models/_sources.yml: src_facebook_ads.custom_ad_performance (Meta ad-grain), src_google_ads.ad_performance (Google Search/Display/Shopping/Video, grained at ad_group_ad — excludes Performance Max), and src_google_ads.pmax_ad_performance (Performance Max, grained at asset_group). Added a new combined_google_ad_data model under dbt/models/analytics/ that UNION ALLs the two Google sources into a single ad-grain table: campaign fields and metrics are harmonized, and a creative_unit_type discriminator ('ad' vs 'asset_group') distinguishes the two feeds since PMax has no ads/ad_groups — asset_groups are the closest analog. Search-only (ad_group_*, ad_*) columns are null on PMax rows; PMax-only (asset_group_*) columns are null on search rows. Cost is converted from micros to dollars. Uniqueness is enforced on (date, creative_unit_type, creative_unit_id) with a 2-day recency check. daily_sales_blended_cac is unchanged for now; migrating it onto the combined table is a natural next step once AD_PERFORMANCE starts receiving data.

Apr 23, 2026

Tolerate leading tabs in dbt YAML files for docs build

scripts/generate_dbt_docs.py now normalizes leading tabs to spaces (tab stop 8) before passing _sources.yml / _models.yml to yaml.safe_load, and prints a WARNING: <path>:<lineno> — leading tab normalized to spaces line to stderr for each fix. Keeps the Cloudflare Pages docs build green when a stray tab slips into a YAML description block while still surfacing the issue so the underlying YAML gets cleaned up. Scope is leading whitespace only — tabs after the first non-whitespace character are still an error.

Apr 21, 2026

Added update_inventory_tool_demand_forecast.sql to refresh supply-chain demand forecast

New queries/supply_chain/supply_chain_tool/db_operations/update_inventory_tool_demand_forecast.sql (with companion .md) MERGEs UTILITIES_DB.SUPPLY_CHAIN.INPUT_FORECAST_DEMAND from two live sources so the forecast stays in sync with the company plan and actual mix. Volume: current month uses Shopify MTD units / completed days (falls back to budget if no data yet); future months use BUDGET_CURRENT.units_sold from the NON_FINANCIAL row divided by days in month. Color mix: trailing-90-day SKU split from RAW_DB.SHOPIFY.orders, applied uniformly to every month. Idempotent — safe to rerun when the budget or trailing mix shifts.

Added Hex CLI agent skill

Added .claude/skills/hex/SKILL.md, a project-level agent skill that documents the hex CLI for programmatic Hex workflows — authenticating, listing projects/connections, creating and updating code/sql/markdown cells, running cells/projects, and troubleshooting failed runs. Also includes a note on a clap parsing gotcha: when a cell source starts with -- (e.g. a SQL comment line), the short -s "$SQL" form must be replaced with --source="$SQL" to avoid being interpreted as an end-of-options marker.

Apr 13, 2026

Documented BUDGET tables as a dbt source

Added a new src_utilities_finance source to dbt/models/_sources.yml pointing at UTILITIES_DB.FINANCE, with entries for budget (full column descriptions, noting which recognition type each column is populated on) and budget_current (description-only — it's a view over budget with identical columns). No freshness config, since the budget is loaded manually a few times a year. The new source now renders on the site's dbt Sources page via scripts/generate_dbt_docs.py, and makes the tables available for future source() references in dbt models.

Updated 2026 budget and added NON_FINANCIAL recognition type

  • Loaded a new 2026 budget version 2026-04-13_por_update into UTILITIES_DB.FINANCE.BUDGET from tmp/upload-to-budget.xlsx. The prior version 2026-03-23_por_locked was demoted to is_current = FALSE and preserved as history.
  • Added a third recognition type, NON_FINANCIAL, to the budget table. Unit counts and subscriber counts (units_sold, units_fulfilled, paying_subscribers_monthly/annual/total) no longer live on the CASH or GAAP rows — they're now on dedicated NON_FINANCIAL rows. This removes the awkwardness of units_sold being CASH-only and units_fulfilled being GAAP-only, and prevents double-counting when summing across recognition types. Backfilled NON_FINANCIAL rows for both the new and historical budget versions.
  • Widened RECOGNITION_TYPE from VARCHAR(10) to VARCHAR(20) to fit NON_FINANCIAL.
  • Updated docs/utilities/budget_table.md to document the new recognition type, rework the column references, and add a NON_FINANCIAL example query.
  • Added a note to .claude/skills/bi-query/SKILL.md about Snowflake's non-rollback behavior inside BEGIN…COMMIT blocks — snowsql -o exit_on_error=true is required for multi-statement scripts to abort cleanly on failure.

Apr 9, 2026

Fix build script skipping most query docs

  • Changed find -mindepth 2 to find -mindepth 1 in build_docs.sh so that .md files placed directly in a section directory (not in a subdirectory) are included in the docs build
  • Previously only 2 of 16 query docs were being copied; now all are included
  • Updated documentation_system.md to reflect that both flat and subdirectory layouts are supported

Fix snippet path in sales_rev_all_data_join docs

  • Fixed incorrect --8<-- snippet reference that pointed to all_data_join.sql instead of sales_rev_all_data_join.sql, which was causing resolve_snippets.py to fail in CI

Apr 8, 2026

Migrate supply chain tool from DEV_MJB_DB to UTILITIES_DB

Migrated all supply chain objects (8 tables, 7 views) from DEV_MJB_DB.SUPPLY_CHAIN to UTILITIES_DB.SUPPLY_CHAIN. All code files updated with new database references. Original dev code archived in dev_code/ subdirectory. Created migrate_to_utilities_db.sql one-time migration script that handles schema creation, table DDL, data copy (preserving current state), view creation in dependency order, and REPORT_ROLE grants. Retool upsert/insert files now use fully-qualified UTILITIES_DB.SUPPLY_CHAIN references.

Mar 30, 2026

Changed synthetic shipment parameters to 25,056 units every 7 days

Updated synthetic shipment logic from 50K units every 14 days to 25,056 units every 7 days across both projection queries and all unified shipment views (baseline + sandbox).

Added ec_shipment_number to unified shipments views

Added ec_shipment_number column to VW_UNIFIED_SHIPMENTS, VW_UNIFIED_SHIPMENTS_BY_SKU, and their sandbox counterparts. Actual shipments pull the shipment number from EC_SHIPMENTS via listagg(distinct ...) (comma-separated if multiple shipments share a PO + arrival date); synthetic shipments get NULL.

Added EC inventory history snapshot

Added EC_INVENTORY_HISTORY table and supporting scripts for daily snapshots of all Endless Commerce inventory items (not just the 4 Tin Can SKUs — includes boxes, parts, etc. for accounting). Fetch script (fetch_inventory_snapshot.js) pulls all items from the listInventory API, and the insert SQL (insert_ec_inventory_history.sql) uses MERGE keyed on (insert_date, id) to allow safe re-runs. Timestamps are in Pacific time.

Mar 23, 2026

Hex guides now include query SQL

  • Added scripts/resolve_snippets.py to inline --8<-- snippet directives before uploading guides to Hex
  • Updated the GitHub Action to run the resolver before the Hex upload step
  • Hex guides now contain the full SQL query text, giving Hex's AI access to the actual code
  • Added query code snippet to the subscription revenue forecast model documentation
  • Updated documentation_system.md and how_to_document.md with Hex guides documentation
  • Moved subscription revenue forecast model files into their own subdirectory so MkDocs picks them up

Added budget metrics table and loading script

  • Created scripts/load_budgets.py to load budget CSV data into UTILITIES_DB.FINANCE.BUDGET
  • Imports both CASH and GAAP recognition types into a single wide table with cleaned column names
  • Supports versioned imports with an IS_CURRENT flag and a BUDGET_CURRENT convenience view
  • Script is idempotent (safe to re-run) and accepts --cash-csv/--gaap-csv args for future versions
  • Added documentation at docs/utilities/budget.md covering table structure, column relationships, and example queries

Mar 19, 2026

Added sandbox environment for supply chain projection tool

Created a sandbox that copies the two user-editable input tables (INPUT_PURCHASE_ORDERS and INPUT_FORECAST_DEMAND) so alternate demand/PO scenarios can be tested without overwriting the baseline plan. Also created sandbox versions of the two views that depend on input POs (VW_UNIFIED_SHIPMENTS and VW_UNIFIED_SHIPMENTS_BY_SKU). All EC data, inventory, and Shopify data are shared with the baseline. Includes table DDL (create_sandbox_tables.sql), view DDL (create_sandbox_views.sql), a sandbox projection query (sandbox_daily_inventory_projection.sql), and a reset script (reset_sandbox.sql).

Fixed missing shipments for PENDING POs with no arrival dates

VW_EC_SHIPMENT_ITEMS now falls back to the PO's expected_date when both actual_arrival_date and expected_arrival_date are NULL on a shipment. This was causing PO-011826-1_Rev02 (~200k units across 8 PENDING shipments) to be completely invisible in the projection and unified shipments views. Also fixed the purchaseOrder→purchaseOrders API change in fetch_shipments.js and added a filter to exclude shipments with no linked PO.

Mar 16, 2026

Replaced starting balance with EC_INVENTORY.available

Simplified the daily inventory projection's starting balance from 3 CTEs (~80 lines of Shopify unfulfilled-order and stock-count logic) down to a single read from EC_INVENTORY.available. The available column already accounts for outstanding orders, making the manual stock count, unfulfilled orders, and fulfilled-since-count adjustments redundant. Updated supply_chain_tool.md to reflect the new source and reorganized TODOs.

Added supply chain convenience views

Added VW_EC_PO_ITEMS_WIDE (pivoted EC POs, one row per PO with SKU qty columns), VW_UNIFIED_SHIPMENTS_BY_SKU (normalized version of unified shipments, one row per PO/date/SKU).

Added VW_UNIFIED_SHIPMENTS view

New Snowflake view combining actual EC shipments with synthetic shipments in a single wide-format output (one row per PO + arrival date with per-SKU unit columns). Mirrors the reconciliation and synthetic shipment logic from daily_inventory_projection.sql. Also updated VW_EC_SHIPMENT_ITEMS to exclude null-PO rows (returns/defectives) at the view level, and removed the now-redundant where po_number is not null filters from the projection query.

Cleaned up deprecated supply chain files

Deleted unfulfilled_shopify_orders.sql (no longer used since starting balance reads from EC) and create_stock_level_v2.sql (DDL for the now-superseded manual stock count table). Removed corresponding sections from supply_chain_tool.md. The Snowflake tables themselves are retained for historical reference.

Mar 13, 2026

EC inventory fetch script

Added fetch_inventory.js and upsert_ec_inventory.sql to populate the EC_INVENTORY table from the Endless Commerce listInventory API (auth issue now resolved). Fetches all inventory items, filters to the 4 Tin Can SKUs, and maps to the existing table schema. The physical count is the key field for the projection — outstanding orders are subtracted separately downstream.

Supply chain tool v2: SKU-level overhaul

Rebuilt the supply chain inventory projection tool from aggregate product-level to per-SKU tracking (TCMOD1AQUA, TCMOD1LEMON, TCMOD1WHITE, TCMOD1LILAC). Key changes:

  • New tables: INPUT_PURCHASE_ORDERS (wide format with per-SKU qty columns), INPUT_FORECAST_DEMAND (monthly demand with SKU percentage splits), STOCK_LEVEL_V2 (per-SKU stock counts with STOCK_LEVEL_V2_CURRENT view), EC_INVENTORY (schema-only placeholder for EC inventory API)
  • New views: VW_EC_PO_ITEMS and VW_EC_SHIPMENT_ITEMS flatten EC variant/JSON columns into one row per (PO/shipment, SKU)
  • PO reconciliation: Full outer join between manual input POs and EC POs on po_number. EC data wins when both exist. POs classified into scenarios A–D based on shipment coverage; unshipped quantities generate synthetic 50K-unit shipments at 2-week intervals
  • SKU-level unfulfilled orders: Shopify query now groups by item.value:sku instead of product_id
  • Daily projection: Output is now one row per (date, SKU) with independent per-SKU running totals via partition by sku
  • Moved v1 files to legacy/ subdirectory

Endless Commerce integration: JS fetch scripts with pagination

Replaced the separate .graphql + transform_purchase_orders.js files with self-contained JS scripts (fetch_purchase_orders.js, fetch_shipments.js) that handle pagination and return Snowflake-ready objects. Moved the API access token to the EC_API_ACCESS_TOKEN env var. Cleaned up old auth debugging files. Updated endless_commerce_api_notes.md with shipment schema quirks and X-Company-Id: tin-can requirement.

Mar 11, 2026

Use FULFILLMENTS table for fulfilled-since-count in supply chain tool

Switched the fulfilled_since_count CTE in estimated_available_stock.sql and daily_inventory_projection.sql from flattening the nested fulfillments array on RAW_DB.SHOPIFY.ORDERS to querying the dedicated RAW_DB.SHOPIFY.FULFILLMENTS table. This is more reliable — the fulfillments table has a proper status column (filtered to 'success') and a first-class created_at timestamp. Joins back to orders to maintain the test = false filter.

Mar 10, 2026

Added estimated available stock query

New standalone query (estimated_available_stock.sql) that returns a single row with the estimated current available Tin Can units, breaking out each component: last stock count, unfulfilled orders, and units fulfilled since the count. Uses the same starting balance logic as the projection query.

Fixed PO_NUMBER auto-increment

Replaced the autoincrement column (which was incrementing by 100) with an explicit sequence (PO_NUMBER_SEQ, start 21, increment 1). Recreated the table to apply the fix; re-granted REPORT_ROLE access.

Made PO_VALUE a computed column

Changed PO_VALUE in the PURCHASE_ORDER table from a manually-entered column to a virtual computed column (UNIT_QUANTITY * UNIT_COST_HW_ONLY). Value now auto-updates when quantity or cost changes. Updated DDL and sample INSERT in docs.

Fixed projection logic gaps in supply chain tool

Three fixes to improve starting balance accuracy in daily_inventory_projection.sql: - Fulfilled-since-count gap: Added fulfilled_since_count CTE to subtract units shipped since the last stock count — previously these were invisible to both the on-hand count and unfulfilled orders, overstating inventory - Test order filter: Added AND ord.test = false to all Shopify order queries (projection + standalone unfulfilled query) - Column rename: AVG_ORDERS_PER_DAY → AVG_UNITS_PER_DAY in FUTURE_DEMAND DDL and all references (data represents units, not orders) - Noted partial fulfillment overcounting as a known limitation (low volume, partially mitigated by fulfilled-since-count fix)

Supply chain inventory projection tool

Added SQL scripts for a Retool + Snowflake inventory projection tool that forecasts when Tin Can will run out of stock: - create_stock_level_table.sql — STOCK_LEVEL table with append-only inserts and a STOCK_LEVEL_CURRENT view (latest row via QUALIFY) - create_future_demand_table.sql — FUTURE_DEMAND table seeded with Mar-Dec 2026 daily demand rates - unfulfilled_shopify_orders.sql — Standalone query for net unfulfilled Tin Can units from Shopify - daily_inventory_projection.sql — Full daily projection combining on-hand stock, unfulfilled orders, demand forecasts, and PO arrivals. Outputs projected_on_hand and stockout_flag per day - Fixed typo in create_purchase_order_table.sql (INSERT targeted wrong table name)

Mar 9, 2026

Added changelog system

  • Added docs/changelog.md to track project changes over time
  • Added CLAUDE.md with instructions for maintaining the changelog automatically
  • Moved changelog to appear higher in the docs site navigation
  • Removed CLAUDE.md from .gitignore so it's tracked in the repo
  • Added CLAUDE.local.md to .gitignore (private project context, not tracked)

Mar 4, 2026

PR #4

  • Update the bi-query skill to add:
    • dev account support
    • grabbing credentials from ~/.claude/CLAUDE.md