Skip to content

Changelog

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