Changelog

September 18, 2026

Declared the GrowthBook schema as a dbt source

dbt/models/_sources.yml. raw_db.growthbook has fed the experimentation platform since July with no documentation. Now documented, and deliberately closed to Hex.

• user_id is null on every row of both tables. anonymous_id — a browser, not a person — is the only unit of analysis. The identity_map view created to hold that mapping is permanently empty, because our tracking never captures a user id.
• Timestamps are TIMESTAMP_NTZ holding UTC against a Pacific session. Called out because it fails in the direction that hides itself: the newest row looks like the future, so a staleness check passes trivially instead of failing.
• The checkout funnel is not a funnel. checkout_completed outnumbers checkout_started, because Shop Pay and Apple Pay bypass the checkout page on overwhelmingly mobile traffic, so a completion rate computed from them exceeds 100%. Verified independently against page_viewed rows on checkout URLs, which agree.
• Two traps and one bridge in properties. value is a number on the checkout events and a quoted string on add-to-cart; the shopper's own currency is recorded on international orders without conversion, so Shopify is the source of truth for money. order_id matches shopify.orders.id, which is what lets a website experiment reach refund-aware revenue.
• Freshness on events only, deliberately. Exposures arrive only while an experiment is live, so a quiet table is the normal state and an alert there would cry wolf. The loaded_at_field converts UTC to Pacific; without that the check could never fail.
• Closed to Hex, deliberately. REPORT_ROLE picked this schema up automatically in June when the database-level future grants fired at creation — nobody chose it. GrowthBook computes the metrics, statistics and verdicts, so Hex has no use for the raw tables, and these traps are easier to avoid than to steer an AI around. The source description states this, because dbt docs reach Hex's context even for tables it cannot query.
• Tests as tripwires. not_null on the identifiers and timestamps, and accepted_values on event_name at warn severity, so a fifth event type surfaces as news to go document rather than failing a build — nothing here breaks when one appears, unlike the currency guards elsewhere in this file. Deliberately no uniqueness test on event_id or exposure_id; those repeat.

September 17, 2026

Documented that creator cost is not additive across Meta campaigns

docs/utilities/lumanu_creator_payments.md, dbt/models/_sources.yml. A trap question asked for the creator cost behind one named Meta campaign; Hex returned the right number with no warning that the same cost also sits under three other campaigns.

• The gap. Both docs warned that one payable becomes many ads and must not be attached to an ad-level row. Neither said the fan-out crosses campaign boundaries, so a per-Meta-campaign figure silently overlaps every other campaign running the same creative. Asking the question once per campaign and summing overstates spend several times over.
• Now documented in the guide, with a query that counts how many Meta campaigns run each utm_lpid, and on facebook_ads.ad_creatives.url_tags. Grouping by the Lumanu campaign label is additive; grouping by the Meta campaign is an allocation, and the docs now say to label it as one.
• Not a tagging fault. Usage rights are bought so content can run wherever it performs, and the tag stays correct wherever the ad ends up. Stated explicitly so nobody opens a ticket against Marketing.
• Retrieval fix. The guide's front matter described only the Lumanu side, so a Meta-flavored question would not have retrieved it even though the Meta join lives in this guide and nowhere else. The description and tags now cover utm_lpid, paid media, and combined campaign cost.
• Spelling. Three normalised → normalized in the Lumanu descriptions.

Corrected the Lumanu funding documentation and fixed the true-cost query

docs/utilities/lumanu_creator_payments.md, dbt/models/_sources.yml. Both said funding could not be attributed to creators or campaigns. That was wrong, and a trap question in Hex caught it.

• The claim was false. Invoice-method funding records name what they cover in description, and their base_amount reconciles exactly to groups of paid payables. The link is not a key, but it is recoverable by arithmetic. The guide now shows how, with the caveat that one record can cover several campaigns, so a per-campaign fee is an apportionment rather than a recorded fact.
• The true-cost query had a bug. Its fees CTE filtered method = 'invoice' but not status, so it counted the fee on an invoice that had been raised and not settled. Added and status = 'funded'. The query was correct when written on September 10 and went wrong when an opened invoice landed afterward — a reminder that SQL verified against today's data is not verified against the cases it has not seen yet.
• Reconcile on base_amount, not amount. amount is the gross transfer including the fee. base_amount is also robust to is_fee_additive, which subtracting the fee by hand is not.
• Guardrails where they get read. The schema-level "READ BEFORE USING THIS SCHEMA" header told readers to add the fee without saying to filter it; it now states both filters. Column descriptions for base_amount and fee_amount carry the same rules.

September 16, 2026

Moved the customer-scores retraining pipeline into the repo (ml/customer_scores/)

New top-level folder ml/customer_scores/ (branch feat/customer-scores-retrain-pipeline). No model or dbt output changes; this brings the code that produces the coefficients in fct_customer_scores.sql under version control instead of living in an analyst's local folder.

• Pipeline: ces_forward_build_snapshot.py (builds one training snapshot as-of a reference date), forward_train.py ces|churn (fits, validates out-of-time against the deployed coefficients, calibrates, emits the dbt paste blocks), and retrain_check.py (the monthly check: builds newly usable snapshots, retrains both models, prints HOLD / DEPLOY / DRIFT / ERROR; never deploys). Plus the device-liveness test rig for the January 2027 revisit.
• Documentation: RETRAINING_RUNBOOK.md — the end-to-end procedure written for a non-engineer owner (monthly check, deploy via PR, bookkeeping, rules, quirks); README.md; requirements.txt; TIN_CAN_HEALTH_FRAMEWORK.md (the framework document, forward section first).
• Records: the training reports and results for the coefficients currently in production (*_deployed_2026-09-15/16.*) and the exploration reports that informed the design.
• Not committed (.gitignore): the snapshot CSVs (~5 MB per reference date, regenerable in ~15 s each), latest-run reports, and the monthly-check history. A fresh clone rebuilds its training data on the first run.
• Pointers updated: the RETRAINING section of the model header and the Hex guide customer_scores.md now point at ml/customer_scores/. A Claude scheduled task runs the monthly check from this folder on the 1st of each month.
• Verification: retrain_check.py --no-build run from the new location reproduced the 2026-09-16 result (HOLD for both models; first fair out-of-time date 2026-08-01 usable 2026-09-28).

Rebuilt the Party Line churn score in the forward framework and retired the base-rate normalization

dbt/models/analytics/fct_customer_scores.sql, dbt/models/_models.yml, new docs/utilities/customer_scores.md (branch feat/churn-forward-model, stacked on feat/ces-forward-model). Completes the move begun on September 15: both customer scores are now forward-labelled, calibrated probabilities from the same snapshot pipeline, and nothing in the model is rescaled.

• Design: identical to the CES rebuild — one row per customer per semi-monthly reference date (12 snapshots 2026-02-01 … 2026-07-15, rebuilt on this date to add the Party Line inputs: 325,800 customer-snapshots, 43,338 monthly-only paid customers ≥35 days in, alive and not pending-cancel; the September 15 CES fit used the pre-rebuild files, 325,674 / 43,351, and its record is archived as ces_forward_train_report_deployed_2026-09-15.md), features from data before the date only, label = Stripe cancellation request within 56 days (28 for monthly_churn_score), unpenalised logistic regression with customer-clustered SEs. The snapshot builder gained the three Party Line inputs (ext_no_answer_rate, ever_ext_call, r28_can_call_pct, with production's exact definitions) and the trainer was generalised to forward_train.py ces|churn.
• Finding that shaped the model: a 14-candidate exploration (the nine legacy CES features, the three Party Line features, tenure, and a zero-activity flag) produced a model that beat the deployed churn score decisively out-of-time (+0.013 to +0.039 AUC, paired CIs clear of zero) but, in the out-of-time refit comparison, was statistically indistinguishable from the forward CES scored on the same rows (differences −0.001 to +0.002, every CI spanning zero). The deployed nine-feature formula edges the deployed CES by about +0.003 AUC pooled over the Jun–Jul snapshots (CI clear of zero) — a real but practically negligible gap consistent with r28_can_call_pct carrying a small effect. ever_ext_call fell out as non-significant and ext_no_answer_rate survived only weakly; the −0.024 AUC gap the old framework attributed to the Party Line features was a product of the cross-sectional design. churn_score is therefore the forward CES feature set plus the one robust Party Line effect, r28_can_call_pct (z = 8): nine features, all significant at p ≤ 0.001. Review of this PR surfaced that r28_can_call_pct is not the clean "share of outgoing calls that are can-to-can" the legacy documentation described: its numerator subtracts all-direction external successes from outgoing-only successes, so the greatest(0, …) guard floors it at 0 for ~45% of active paid rows and it behaves as a "mostly can-to-can household" indicator. Training and production compute it identically (the fitted model is valid for the feature as computed); the yml, header, and Hex guide now describe the actual computation, and a consistent-basis redefinition is a follow-up that would require a retrain.
• churn_score is now P(cancellation request within 56 days) for paid subscribers; monthly_churn_score is the same features fitted to a 28-day label. Both null for free subscribers, as before. Out-of-time AUC 0.670 / 0.692 / 0.701 / 0.692 on the Jun 1 – Jul 15 holdouts (legacy hotfixed churn score: 0.646–0.679); calibration on holdout 3.68% predicted vs 3.74% observed, deciles tracking from 1.1% to 9.8%. Because the two models share eight of nine features, churn_score and ces_score rank paid customers almost identically by construction.
• Removed the population-mean normalization CTE and the 0.0154 monthly base-rate constant (and the header section explaining how to recompute it): every one of the four scores is now a direct model output. The final select is select * from all_scored. Exposed r28_can_call_pct as a raw feature column (null for free) so future parity checks against production columns can cover the full churn feature set.
• Validation: dbt build --select fct_customer_scores in dev: table rebuilt (250,312 rows), 7/7 tests pass; churn scores null for exactly the free rows, populated for every paid row. Dev upstream tables remain stale, so calibration was taken from the out-of-time holdout rather than dev distributions (see September 15 method note).
• Documentation: added docs/utilities/customer_scores.md, a Hex guide to reading and using the four scores (horizons, populations, defaults, and what not to compare them to). Rewrote the model header's MODELS and RETRAINING sections for the two-model forward procedure and generalised the FEATURE-SCALE RULE. This PR also carries the changelog entries for the two September 15 PRs, which shipped without them.

September 15, 2026

Rebuilt the Customer Engagement Score as a forward-labelled, calibrated 56-day cancellation-risk model

dbt/models/analytics/fct_customer_scores.sql and dbt/models/_models.yml (branch feat/ces-forward-model). The CES had been a cross-sectional logistic model: for churned customers every feature was measured in the 28 days ending on their cancellation date, for retained customers in the 28 days ending on the run date, and the raw output was a relative ranking that monthly_ces_score rescaled to a monthly-looking number. That design measures what a customer looks like at the moment they cancel and flatters any feature that co-moves with cancelling. Scored on a date and evaluated forward, the deployed CES predicted 56-day cancellation requests at AUC 0.62–0.66 across five reference dates versus its documented 0.664.

• New design: one training row per customer per reference date (12 semi-monthly snapshots, 2026-02-01 … 2026-07-15; 325,674 customer-snapshots, 43,351 monthly-only paid customers ≥35 days in, alive and not pending-cancel at the reference date). Features use only data before the reference date; the label is a Stripe cancellation request (canceled_at) within the following 56 days. Unpenalised, unweighted logistic regression with customer-clustered standard errors, so the output is a calibrated probability. Snapshot builder, trainer, and reports live in the analyst's Claude Playground (ces_forward_build_snapshot.py, forward_train.py); the retrain procedure is documented in the model header.
• ces_score is now P(cancellation request within 56 days). monthly_ces_score is the same eight features fitted to a 28-day label — a directly calibrated 28-day probability — and is no longer a rescaling of ces_score by the monthly-population mean. Both are trained on monthly paid subscribers and applied to annual and free subscribers as an as-if-monthly engagement index, as before.
• Features: kept pct_weeks_healthy, r28_active_days, r28_calls_per_active_day, log_r28_max_call_sec, ever_had_ticket, r28_vm_backlog_5plus; added log(1 + tenure_days) and an r28_active_days = 0 flag; dropped w1_active_days, num_devices, and lt_no_answer_rate as not significant forward (cluster-robust p ≥ 0.05). Two production signs reversed under the forward design: ever_had_ticket is a risk factor (+0.32; the old protective sign was survivorship — long-lived customers had more time to file a ticket), and w1_active_days carries no forward weight once recency and tenure are present. Exposed raw feature columns are unchanged.
• Validation: customer-grouped 5-fold CV AUC 0.685; out-of-time holdouts (train ≤ May 15, test Jun 1 / Jun 15 / Jul 1 / Jul 15) 0.668 / 0.689 / 0.697 / 0.689 versus 0.637–0.666 for the hotfixed prior model and 0.653–0.684 for r28_active_days alone, paired-bootstrap CIs excluding zero throughout. Calibration on holdout 3.69% predicted vs 3.74% observed (decile 1 → 1.3%, decile 10 → 9.8%). Applied to production feature columns on 2026-09-15 (monthly, n = 62,004): mean ces_score 3.53%, monthly_ces_score 1.80%; Spearman 0.59 against the prior CES, 52% top-decile overlap — Hex views keyed on CES will visibly reshuffle.
• Also: refreshed _models.yml text that still said 1.28%, June-22 training dates, and "refreshed as a view" (the model has been a table since July). churn_score / monthly_churn_score were left on the old design pending their own rebuild (see September 16).
• Method note for future work: dbt build --select fct_customer_scores in the dev target reuses whatever copy of the upstream tables sits in DEV_PFZ_DB — three weeks stale on this date — so every trailing-28-day feature collapsed and the new score averaged 5.3% in dev versus 3.5% on production columns. Dev builds prove compilation and tests; calibration must be checked against production feature columns.

Fixed a feature-scale mismatch that silenced pct_weeks_healthy in both customer scores

dbt/models/analytics/fct_customer_scores.sql (branch fix/customer-scores-pct-weeks-healthy-scale). The training data behind both the churn model and the CES (model_data_fresh_paid_anchor.csv) carries pct_weeks_healthy on a 0–100 scale, but the *_weekly_health CTEs in the model compute a 0–1 fraction. The fitted coefficient (−0.0052 per percentage point) was therefore applied to an input one hundredth the intended size, and the feature — the second-strongest single forward predictor in the set, AUC 0.65–0.67 on its own — contributed essentially nothing to either score from the June 2026 launch until this fix.

• Fix: multiply the column by 100 inside the three score formulas. The exposed pct_weeks_healthy column is unchanged (still 0–1), so Hex drill-downs and downstream consumers are unaffected.
• Validation: forward test at five reference dates (2026-03-15 … 2026-07-15; n = 18k–40k; outcome = cancellation request within 56 days): CES AUC improves +0.017 to +0.020 at every date (e.g. 0.631 → 0.652 at Jul 15). dbt build --select fct_customer_scores in dev: table rebuilt (248,997 rows), 7/7 tests pass. Displayed averages are unchanged because the monthly_* normalization self-calibrates to the population mean; rankings shift, with customers who have few healthy weeks moving up in risk.
• Guard: added a FEATURE-SCALE RULE to the model header — verify at every retrain that each training column's range equals the dbt column's range. The rule was superseded for CES the same day by the forward rebuild (trained on the 0–1 column, no ×100); it remains binding for the churn model until its rebuild.

Fixed the guides that were sending Hex to dropped tables and a retracted warning

A Hex session reported that our own guide called ACCOUNT/TINCAN_USER "sparse", and separately tried to query a table dropped in August. Both were our doing, not Hex's.

• Deleted the "sparse" retraction note in customer_identity.md. It was the only occurrence of the word in the whole corpus, and Hex retrieved the retraction and reported the retracted claim as current. Replaced with a plain positive statement, since a correction that restates the false claim keeps it retrievable.
• Dropped tables now read as dropped, not deprecated. LEGACY_CUSTOMERS, LEGACY_CONTACTS, and DEVICES_JOINED_FULL_HISTORY get an explicit section in customer_identity.md and reference_schema.md; inline mentions elsewhere are stripped so the names appear only inside the warning. LEGACY_DEVICES and LEGACY_CDR are called out as retained and still queryable.
• Bounded the migration-timestamp trap to DEVICE.CREATED_AT. Hex generalized it to ACCOUNT.CREATED_AT and suspected the March 2025 floor hid earlier history. It does not: ACCOUNT.CREATED_AT is never null and its single largest day is 2025-12-25.
• Stated that ANALYTICS_DB has no customer dimension, naming CUSTOMERS_TO_STRIPE and FCT_CUSTOMER_SCORES as the near-misses so nothing latches onto them by mistake.
• Removed three stale figures: "~254k customers" (now 316k), "~45%" Shopify coverage (now ~36%), and a legacy-only join dropping "roughly 90% of current-era devices" (actually 21.2%, and irreconcilable under any reading). The last is now the structural fact instead — every device created after the 2026-08-06 freeze has a null LEGACY_DEVICE_ID.

Routed between the two call-quality sources and fixed ThingsBoard query guidance

Asking Hex "how's our call quality?" as a release check, twice, exposed that nothing tells anyone the two call-quality sources exist alongside each other. The first run could not yet see the telemetry table and answered from CDR.AUDIO_IN_MOS alone; the second, after the connection refreshed, used telemetry alone and never mentioned MOS. Both answers were defensible and both were half the picture.

• cdr.audio_in_mos now points at its companion table. It is the headline metric — perceptual, present on every connected leg — but it saturates near 4.5, so it detects bad calls rather than ranking good ones. The description now says so and names call_telemetry as where the causes live.
• The call telemetry guide leads with a routing section. MOS answers how good were the calls; telemetry answers why were they bad. They join on call_id (96% match) and agree monotonically: packet loss runs 0.23% at the MOS ceiling and 7.07% below 3.0, with jitter and Wi-Fi moving in step.
• New cross-source query listing households by share of calls under 3.5 MOS with their telemetry. It separates the two populations that matter — poor scores with weak Wi-Fi and high loss, versus poor scores with clean telemetry, which are the ones to escalate upstream.
• thingsboard_reports.extracted_at was documented as a TIMESTAMP. It is TEXT. That one wrong sentence was pushing readers to cast it, which blocks partition pruning on our largest table. ISO 8601 sorts lexicographically, so string comparison does the same job for free.
• Both sources now require a bound before a dedupe. Measured with EXPLAIN over 363M rows: an unbounded = (select max(extracted_at) ...) reads 63.9 GB, an unbounded qualify row_number() 33.6 GB, a cast 30.3 GB — while a literal string bound reads 62 MB. The guide gains a "Filtering this table cheaply" section with the bounded pattern.

September 14, 2026

Declared call telemetry as a source and documented the traps in it

A new Airbyte connection lands per-call VoIP measurements in RAW_DB.THINGSBOARD.CALL_TELEMETRY — RTP counts, jitter buffer, echo cancellation and Wi-Fi signal, one row per call leg. Profiling it before publishing caught two columns that would have produced confidently wrong answers.

• New source table src_thingsboard.call_telemetry, 28 columns documented. Incremental append, synced hourly, with freshness checked against the S3 file's own last-modified time so a stalled export is caught even while Airbyte keeps running.
• call_quality is not a measure of call quality and is documented as do-not-use. It labels 87% of all calls poor, but the label tracks duration rather than network: 99% of calls over fifteen seconds are poor while median packet loss is zero in every duration band.
• rtp_lost is corrupt on roughly a hundred calls a day, reporting values in the tens of millions that exceed rtp_pkts_total. One row can carry an entire day — unguarded, daily loss swings between 0.27% and 98%; filtered to rtp_lost <= rtp_pkts_total it is a flat 0.26% to 0.32%.
• The table is not unique on call_id. At-least-once delivery repeats about 1.6% of records byte-identically. Counts and sums tolerate it; an average of packet_loss_pct runs ~12% high, because the duplicates skew toward lossy calls.
• device_id is the Tin Can device key, not a ThingsBoard id. It matches zero rows in thingsboard_reports and in dim_device.thingsboard_device_id, but joins to raw_db.tincan.device.key and reaches a customer for 99.99% of devices via routing_id.
• New Hex guide docs/utilities/call_telemetry.md covering fleet quality, daily trend, per-household diagnosis and the non-loss audio failure modes. Every SQL block was executed against Snowflake before shipping.

September 10, 2026

Declared Meta ad_creatives as a source and documented the Lumanu link

Enabling Airbyte's ad_creatives stream surfaced the field that connects creator payments to ad spend. Marketing had already been setting it; it just was not in the warehouse.

• New source table src_facebook_ads.ad_creatives, 37 columns documented. Full Refresh / Overwrite — it is a dimension, one row per creative, no history to preserve. Inherits the existing source-level freshness check.
• url_tags is the analytically important column. It holds the query-string parameters Meta appends to destination URLs, and ads built from paid creator content carry utm_lpid there, containing the Lumanu payable's invoice_number. That is the join between creator cost and media spend, and it is exact rather than a fuzzy name match.
• ads.creative now points somewhere useful. Airbyte returns only {"id": ...}, so the description explains the hop to ad_creatives.id rather than describing a dead end.
• Corrected src_lumanu.payable.invoice_number, which said "not a join key". It is one — the cross-system key to Meta, and the same parameter reaches GA4 on click, so it also links creator cost to sessions and orders.
• The Lumanu Hex guide gained a linking section with a tested query producing creator cost, Meta spend and combined campaign cost per payable. Warns that one payable becomes many ads, so creator cost must be aggregated to the invoice rather than attached per ad.

September 9, 2026

Sharpened the Lumanu source docs and added a creator-payments guide

The src_lumanu descriptions were written from the API schema before any data existed. A first look at the real rows, hours after publishing to Hex, sharpened several of them and caught one that was inaccurate — before anyone had queried it.

• funding holds two structurally different kinds of record, distinguished by method. invoice is real money transferred into the Lumanu wallet; balance is an internal earmark of funds already held, where nothing moves and no fee applies. Summing amount across both double-counts the same dollars — currently a 2.3x overstatement against actual creator spend. The old description called the whole table "money moving INTO the wallet", which is true of only one method and would have misled Hex once someone reached for it.
• Four smaller metadata corrections: funding.status now names its observed values (funded, opened); project_id no longer implies project attribution is usually absent; payee_lumanu_id is stated as the verified join to partner; and the creator-name mismatch against partner.name is called out rather than hedged.
• New Hex guide docs/utilities/lumanu_creator_payments.md with the correct query patterns — paid-only spend, true cost including Lumanu's fee, the creator join, try_cast on usage duration, and a wallet-to-payables reconciliation check. All six SQL blocks were executed against Snowflake before shipping.
• Campaign name is documented as unreliable for grouping. It is free text with no validation in Lumanu and no cleansing layer here; the same campaign already appears under a misspelling and under two different naming conventions, so a naive GROUP BY splits it and understates each label. Marketing has been told, but the field will keep drifting, so the guide instructs readers to reconcile labels by hand before reporting campaign totals.

September 8, 2026

Upgraded dbt packages

dbt_project_evaluator 1.3.4 to 1.3.5, which pulls dbt_date 0.20.0 to 0.21.0 in the lock file. Bundled with the Lumanu source work; dbt build clean afterwards.

Documented the Lumanu creator-payments source

New src_lumanu source covering all seven tables the nightly Retool Workflow loads into raw_db.lumanu: payable, custom_field_policy, partner, funding, project, wallet_transaction and workspace.

• Freshness is source-level and anchored on _loaded_at, not Snowflake LAST_ALTERED. The workflow rewrites every table on each run, so metadata would say "the loader touched it" rather than "this run landed". Thresholds are 24 hours against a 1:00 AM daily load.
• 41 tests across 101 documented columns — primary keys, workspace_id, and the loader's _loaded_at / _loaded_by. amount_denomination carries an accepted_values test on us_cents so a move to multi-currency fails the build instead of silently changing what every dollar calculation means.
• The money-in-cents trap is called out on every money column. Amounts are integers in minor units; 100 means $1.00.
• payable has two status columns and will_pay does not mean paid — it pairs with awaiting_payee, so summing across paid and will_pay overstates cash disbursed. Neither column carries an accepted_values test: the observed values are a subset of Lumanu's documented enums, and their documentation has been wrong on enums before.
• payable.custom_fields keys are dynamic, rebuilt each run from whatever Marketing has configured, so the description points readers at custom_field_policy rather than listing today's fields. Also documents that Lumanu's fee appears only on funding, so payables alone understate true creator cost.

August 27, 2026

Made source freshness config say what it actually does

Almost entirely a legibility change -- the same 114 sources, checked the same way -- with two deliberate exceptions: seven re-enabled Shopify checks, and ThingsBoard, which now measures its own data.

• Removed all 24 inert loaded_at_field lines (10 source-level, 14 table-level). Every one sat nested inside freshness, where dbt 1.11 does not recognise it, so all of them were already falling back to Snowflake LAST_ALTERED rather than reading the column they named. Verified with dbt source freshness before and after.
• Metadata is the right signal here, so the fallback stays — now stated in a comment rather than left to accident. LAST_ALTERED moves on every Airbyte sync regardless of what the sync brought, which answers "did the ELT run". _airbyte_extracted_at answers "did new data arrive", and on full-refresh streams it is the same number anyway.
• Consolidated src_tincan's 13 per-table freshness blocks into one source-level block, matching every other Airbyte source (all 13 already shared the same 24-hour threshold). legacy_devices and legacy_cdr now carry an explicit freshness: null: both are frozen, and would otherwise inherit the check and fail immediately. Coverage is unchanged at 13 tables.
• Re-enabled freshness on the seven metafield_* tables that carried freshness: null. The stated reason — a NULL loaded_at_field on an empty table — described column-based behaviour that was never happening: Airbyte swaps those tables every sync, so LAST_ALTERED is current even at zero rows. Takes the suite from 107 to 114 monitored sources.
• thingsboard_reports now reads the report's own timestamp instead of warehouse metadata. Its CSV export has stalled for days at a time while Airbyte kept syncing the same stale file, and metadata freshness stays green through exactly that failure. Uses loaded_at_field: try_to_timestamp_tz(extracted_at), which hands dbt an explicit instant rather than relying on its "a naive timestamp is UTC" default. Config moved from source to table level now that it names a column.
• referral_candy.referrals now measures referral_timestamp rather than warehouse metadata. It is Retool-loaded, and that workflow stages into tmp_referrals then merges -- a merge that changes nothing need not bump LAST_ALTERED, so metadata can read stale on a perfectly healthy run. Threshold widened 24h to 48h: referrals land every day (max gap of 1 day across the last 120), but intraday timing drifts enough that 24h would flag ordinary quiet stretches. campaigns is a static lookup the workflow does not reload, so it now carries an explicit freshness: null instead of silently having none.
• Exempted src_front.events and src_front.messages, matching how the frozen legacy_* tables are handled. Front was retired and support data moved to Zendesk, so both tables are inactive feeds retained for historical reporting; each now carries an explicit freshness: null and the source description says so and points current support reporting at src_zendesk. Previously they simply had no freshness config, which read as an oversight rather than a decision.
• Standardised the last three sources that still had a warn/error split. src_zendesk, src_tiktok_ads and src_klaviyo errored at 2 days while every other Airbyte source errored at 1; all three sync in the same morning window, so the wider threshold was inherited inconsistency rather than a decision. Now 1 day warn and error, matching the rest -- 21 sources tightened. src_big_query deliberately keeps its 2-day threshold: the GA4 to BigQuery to Airbyte chain runs roughly 9h + 17-24h against a 72h SLA, and 1 day would fire on a healthy pipeline.
• Corrected two descriptions that had gone stale: metafield_customers still claimed to be empty (it now holds Klaviyo profile attributes, one row per customer + namespace + key, covering a small share of customers), and cdr.ingested_at still called itself "the freshness anchor for this table", which it no longer is.

Added three columns to dim_device, plus a uniqueness test that protects its grain

Additive. No existing column changes value, and the grain stays one row per routing_id.

• stripe_subscription_id — coalesced from subscription, falling back to legacy_devices. Bridges dim_device to Stripe for plan and billing status, and removes the reason BI had to reach into LEGACY_DEVICES for it. Not unique: households share one subscription, so it looks up billing attributes and must not be counted.
• emergency_address_status — emergency_address_status.status (registered / unregistered / failed / pending_removal). That table was already joined but only its created_at was used, so a confirmed date read as "E911 is set up" when a couple of thousand lines are actually unregistered or failed.
• thingsboard_device_id — pass-through join key to thingsboard_reports.id. Documented with the caveat that a non-null id does not imply telemetry: 9,728 devices carry an id ThingsBoard has never reported on, and 4,529 of those have call history.
• unique and not_null on emergency_address_status.subscription_id. It is the only join in the model not made on a primary key, and nothing in the schema enforced one row per subscription. A second row would have silently doubled every device on that line.
• Verified before writing: every join key in the model is duplicate-free; the 1,636 legacy-only rows are genuinely absent from device rather than casualties of the ::varchar cast; and no subscription active in the last 30 days of cdr is missing from the replica.

Declared raw_db.shopify.metaobject_community as a source and added a Hex guide for the Communities program

Documentation-only. The table is created and loaded by a Retool Workflow outside this repo.

• Retool rather than Airbyte, permanently. No ELT vendor's Shopify connector exposes metaobjects — they are GraphQL-only, with no REST endpoint. Each run replaces the table with a full snapshot.
• Freshness anchors on _loaded_at, not updated_at. updated_at goes stale whenever organisers stop editing the roster, so it would alarm on merchant behaviour rather than pipeline health.
• loaded_at_field belongs inside config, as a sibling of freshness. Every other block in this file nests it inside freshness, where dbt 1.11 ignores it and silently falls back to warehouse-metadata freshness. Confirmed, but left alone — fixing it is behaviour-changing and needs its own PR.
• New Hex guide, docs/utilities/shopify_communities_program.md. Shipped with the table, since the moment the source lands a BI tool can query it and get confidently wrong answers. Covers the orders join — not inferable from schema, as the key sits inside VARIANT columns on both sides and orders needs a lateral flatten — plus the aggregation traps: discount_code is not unique (a district runs one campaign per code with an entry per school), so campaign figures must not be summed across rows and a per-community order breakdown will exceed the true order total.
• Disambiguated the two unrelated things named "community", in both directions. src_tincan.community and src_tincan.community_member (the in-app calling feature) now point at metaobject_community and vice versa. Previously only the new table carried the warning, so a question steering a BI tool toward the in-app tables would never have surfaced it — and the old wording ("a group, e.g. school class") made the confusion likelier. These two are the only pre-existing lines this PR touches.
• Verified: documented columns match information_schema; dbt parse clean; 5 source tests pass in a full dbt build; dbt source freshness PASSes reading _loaded_at directly; every SQL example in the guide was executed against Snowflake; the guide resolves through scripts/resolve_snippets.py.

August 26, 2026

Added a Hex guide steering BI users off zzz_ and tmp_ prefixed objects

New docs/utilities/deprecated_and_temporary_objects.md. Documentation-only; it publishes to Hex automatically via the existing hex_context_toolkit workflow, which picks up docs/utilities/**/*.md on every push to main.

• States one rule: never query an object whose name begins with zzz_ (deliberately retired) or tmp_ (transient loader artifact), in any database or schema, as a source, a join, or a dashboard reference.
• Written as a separate guide rather than folded into reference_schema.md. Hex's AI retrieves on the front-matter description, and reference_schema.md's description is scoped to RAW_DB.REFERENCE archive tables — a question about a specific zzz_ object would not match it. This guide's description names both prefixes explicitly so it surfaces on prefix-related questions. Cross-linked via relatedGuides.
• No hardcoded list of current objects. The set changes as tables are retired and dropped, and a stale list is worse than none, so the guide gives a prefix-matching query instead. That query uses an escape clause because _ is a single-character wildcard in SQL LIKE — an unescaped 'zzz_%' also matches names like zzza.
• Explains why these objects are dangerous rather than only prohibiting them. They do not error: a retired table returns plausible rows with sensible column names and joins cleanly. The failure is silent staleness, so a consumer that understands the mechanism is likelier to refuse one than a consumer following a bare rule.
• Verified: front matter parses; the guide resolves cleanly through scripts/resolve_snippets.py alongside the other 12 guides; the prefix query runs and currently catches 12 objects in raw_db.

August 25, 2026

Documented 14 new Shopify source tables and corrected the fct_ga_events description

Documentation-only — no model logic, no compiled SQL, nothing that can regress.

Shopify sources. Declared every previously undeclared table in raw_db.shopify: the 13 metafield_* tables (loaded 2026-08-20) and price_rules (2026-08-25). Per-table and per-column detail lives in the descriptions themselves; the decisions worth recording here:

• Freshness disabled on the seven empty metafield_* tables via config: freshness: null. src_shopify sets a source-level 1-day rule and those tables have no rows, so each would have errored the moment it was declared. Documented rather than omitted — they share the same schema and will populate on their own if those metafields are ever defined.
• Grain is one row per owner_id + namespace + key, not one per owning resource — metafield_orders is 75,760 rows across 72,352 orders. Every description says so, because joining to orders/products without pivoting first silently fans out the parent.
• discount_codes_sync deliberately left undocumented. It was enabled alongside price_rules to test whether Shopify discount metadata could identify which Communities program an order belongs to. It can't — the modern community codes are absent from it, while 98.2% of all other redeemed codes are present — so the stream is being disabled rather than declared as a source that would go stale.
• Two existing orders columns annotated. tags now names community_order (3,640 orders), the only reliable flag for the Communities program — previously undiscoverable without knowing it existed. discount_codes now warns that its code member is not always a real code: staff-entered manual discounts are recorded there by reason text (replacement, 754 orders; custom discount, 312; failed delivery, 69), so promotional-code analysis must exclude them or it conflates promotions with ops write-offs.
• Tests: unique + not_null on id across all 14 tables, plus not_null on owner_id for the metafields. No relationships tests, matching the rest of this file; referential integrity was instead checked ad hoc against orders, products, product_variants and pages — zero orphans.

fct_ga_events description. Corrected a source reference to src_google_analytics.events_all, an object that does not exist — the model reads src_big_query.events_all. Free text rather than a source() call, so nothing was broken at build time, but dbt docs and the BI platform's AI both surfaced a nonexistent object to anyone tracing lineage. Also documented the three non-interchangeable attribution scopes (first_click_* user-scoped, event_* event-scoped UTMs, last_click_* session-scoped), where the summary had mentioned only last-click. The event_* gotcha is now explicit: it populates only on the event carrying the UTMs and is NULL on purchase (35 of 97,553), so conversions must be attributed by joining back to session_start — as fct_marketing_attributed_orders already does.

Verified across the Shopify sources: documented columns match information_schema exactly on every table; dbt parse clean; 55 source tests pass; 14 dbt source freshness nodes pass, with the seven empty tables correctly skipped.

August 21, 2026

Added this_participant_cdr_version to participant_logs_full_history

participant_logs_full_history was the only participant_* model in the analytics schema without a CDR-version column, so consumers had no correct way to join dim_device — the two participant_daily_activity models and participant_logs all already expose one. Two lines, one per union all branch: the src_history.participant_slice_underlying branch is hardcoded 'version 1' (it spans 2024-10-02 to 2025-05-07, entirely pre-V2), and the participant_logs branch passes through its existing cdr_version. Same pattern participant_daily_activity_full_history already uses.

Named this_participant_cdr_version to match the two participant_daily_activity models rather than participant_logs' bare cdr_version, since it qualifies this_participant_device_id. No existing column, test, or row count changes.

August 19, 2026

Removed devices_joined_full_history

Retired devices_joined_full_history (DJFH) now that dim_device is in production, and updated the documentation that referenced it.

• dbt/models/analytics/participant_daily_activity.sql: repointed the two cdr_version = 'version 1' branches from DJFH to src_tincan.legacy_devices directly (alias dn, matching participant_logs). DJFH was its only code consumer and used just two of its columns (device_id, caller_id), both native to legacy_devices. The version 2 branches already join src_tincan.device and are untouched. Pointed at legacy_devices rather than dim_device because legacy_devices is retained permanently, so a direct reference accrues no future cleanup and keeps the two participant models consistent.
• Removed devices_joined_full_history.sql and its 80-line _models.yml block, and refreshed three descriptions that still referenced it — including participant_daily_activity's, which claimed it was built from DJFH.
• Documentation: repointed customer_identity.md and customer_call_history_and_usage.md to DIM_DEVICE, and named it the preferred device entry point in reference_schema.md and .claude/skills/bi-query/SKILL.md. The device→customer join in both guides is now keyed by CDR era rather than on a single id column: THIS_PARTICIPANT_DEVICE_ID holds a legacy id for version 1 rows and a current DEVICE.ID for version 2, so the old single-key recipe resolved only 225,229 of 472,515 endpoint/era pairs — dropping ~90% of current-era devices and mis-attributing the rest via id collision. The corrected two-equi-join pattern resolves 472,464. Also removed three stale FIRST_ONLINE references and one to "device status", neither of which dim_device carries.
• docs/utilities/cohort_retention_methodology.md: added THIS_PARTICIPANT_CDR_VERSION to the groupings, cohort join, and COUNT(DISTINCT ...) in the reference cohort SQL. Without it, ids appearing under both eras (6.04%, averaging a 6.85-month gap) merged two unrelated devices into one cohort member. This file never referenced DJFH and was found by auditing device-join advice generally.
• Follow-up: dbt does not drop a table when its model is deleted, so ANALYTICS_DB.ANALYTICS.DEVICES_JOINED_FULL_HISTORY (256,622 rows) persists as a frozen orphan until dropped manually — gated on the BI Engineer confirming no Hex report reads it.

August 18, 2026

Added dim_device: a consolidated device dimension spanning the legacy and current device systems

New model dbt/models/analytics/dim_device.sql (plus its dbt/models/_models.yml block). Additive only — no existing model was changed, so nothing can regress. All counts in this entry are as of 2026-08-17, re-checked 2026-08-18, and describe shape rather than targets — device is live and grew ~1,900 rows in that single day, so every device-derived figure drifts upward over time; only the legacy_devices side is fixed. Intended replacement for devices_joined_full_history, which is built solely on the frozen legacy_devices table (last write 2026-08-06 09:45) and is therefore blind to the ~21,200 devices created since, a gap that grows daily.

• Grain is one row per routing_id — 284,384 rows on 2026-08-17, 286,267 a day later. routing_id is the dial identifier that both raw transaction tables actually use (legacy_cdr.src/dst and cdr.src/dst) and is the same number space as legacy_devices.device_id — the only device identifier spanning both systems, and so the intended foreign key for transaction-derived models. Built from a union spine of device.routing_id and legacy_devices.device_id followed by all left joins, rather than a full outer join, so the grain is stated explicitly rather than emerging from the join. Population on 2026-08-17: 254,862 in both systems, 27,822 device-only (post-freeze, plus non-numeric routing ids), 1,700 legacy-only. The legacy-only rows are deliberately retained — 935 of them still carry pre-cutover call history (31,540 rows of participant_daily_activity, 2025-05 to 2026-06). Note that the legacy-only bucket shrinks slowly as households that had no device row finally get one through late provisioning (1,689 by the next day), even though legacy_devices itself stays frozen.
• Three id columns, deliberately: routing_id joins the raw CDR tables; device_id (= device.id) joins call_logs rows where cdr_version = 'version 2'; legacy_device_id (= legacy_devices.device_id) joins rows where cdr_version = 'version 1'. This is not redundancy — the device.id and legacy_devices.device_id ranges overlap (27,731 collisions) and no device kept its number across the migration, so joining on a bare device id without knowing its system returns plausible wrong rows rather than zero rows. The collision count grows as device grows. Note that device_id here means device.id, whereas elsewhere in the project the bare name still means the legacy id; the model header and yml description both flag this.
• Date handling is asymmetric, and it matters. created_at prefers legacy_devices.created_at because device.created_at is a migration timestamp — every pre-migration device was stamped ~May 2026 (170,334 of them), so it cannot carry historical device-age trends; device.created_at is used only for genuinely new post-freeze devices. updated_at inverts this and prefers device.updated_at, since legacy_devices is frozen. Verified legacy_devices.created_at is TIMESTAMP_NTZ in UTC (median offset 0 against device.created_at read as UTC; only 7 rows agree under Pacific), so current-system timestamps are converted to UTC and cast to NTZ for one consistent type.
• Attributes come from the normalised current-system tables, with legacy_devices backfilling the 1,700 rows that have no device row: subscription (caller_id, phone_number, emergency_enabled, dnd_enabled, dnd_until, onboarding_complete, twilio_phone_id), account (customer_id, via subscription.account_id), address (street/city/state/zip/country, via subscription.emergency_address_id), and emergency_address_status (twilio_address_sid, and created_at as the emergency-address confirmed date). For the 254,862 devices in both systems the two sources agree ~99.95%, so the coalesces almost always take the current value. Verified the address reached via subscription is the address legacy_devices carried (city/zip/street agree on 99.95% of 207,887 populated rows), and that emergency_address_status.created_at matches the legacy confirmed date on 125,175 of 125,318 rows (99.9%).
• Dropped nine columns carried by devices_joined_full_history as dead weight: status, subscription_type, is_discoverable and external_network are single-valued constants; device_meta is 100% null; emergency_address_validated_date is populated on 27 of 256,562 rows; first_online is byte-identical to created_at on all rows; device_status is an online/offline flag frozen at the 2026-08-06 cutoff and so actively misleading; and extension is dropped pending confirmation that it ever carried meaning (100% populated but only 10 distinct values across 256k rows). device_mac is a material gain in the other direction — populated on 5 legacy rows versus 282,677 in device.
• routing_id is always VARCHAR, never coerced. 6,837 devices carry a routing id of the form '<base>x<n>' (e.g. '1243230x4'). The suffix marks a second device on the same household — a sibling's phone or a replacement handset — not a number recycled to a different customer: all 6,521 suffixed devices whose base also exists as a device share the same customer as that base, with zero cross-customer cases, and 5,762 of those bases are still live alongside the suffixed device. This is why joining in routing space is safe across both eras — of the 254,873 routing ids present in both device and legacy_devices, 254,872 agree on customer (the lone exception, '563472', appears to be a test device). Note that this is a different pair of columns from the device.id / legacy_devices.device_id collision above: that one is the surrogate space against the routing space, and this model's spine never touches device.id. cdr records the suffixed form (220,686 src / 237,763 dst rows, with 5,280 of 5,282 distinct suffixed src values matching device.routing_id exactly), so the join is unambiguous — but try_to_number(routing_id) silently drops all 6,837.
• Join safety verified against Snowflake before commit, on two separate days so the drift is visible: 2026-08-17 gave 284,384 rows / 284,384 distinct routing_id against 282,684 device rows, and 2026-08-18 gave 286,267 / 286,267 against 284,578 — unique key holding as the source grows. All 256,562 legacy_devices rows present, customer_id resolved for every current device, and created_at spanning 2025-03-20 to present. Both spine keys are unique on their own side and the remaining joins are many-to-one with zero orphans, so the model cannot fan out.

August 17, 2026

dbt maintenance: removed obsolete source-freshness checks, upgraded packages, and fixed deprecated test syntax

A housekeeping pass across dbt/. Full dbt parse --no-partial-parse and dbt build run clean, with zero warnings (previously two deprecation warnings plus an intermittent cycle warning).

• Removed obsolete source-freshness checks (dbt/models/_sources.yml): dropped the config.freshness blocks from legacy_cdr and legacy_devices in src_tincan, and the source-level config.freshness on src_front (which covered both events and messages). All are frozen/static tables whose freshness checks were failing on every run — legacy_cdr ran ~78h stale against a 2h threshold (superseded by the V2 cdr table), src_front ~14 days stale against a 2-day threshold, and legacy_devices is being retired now that its population logic moved to V2 device. Table declarations, columns, and unique/not_null data_tests are all retained; two message_date column descriptions that referenced the now-removed "freshness anchor" were cleaned up.
• Fixed an intermittent ref() cycle warning (dbt/dbt_project.yml): renamed the behavior-change flag require_ref_prefers_node_package_to_root → require_ref_searches_node_package_before_root. dbt renamed this flag (without updating its own warning text), so the old key was silently ignored and the behavior reverted to its default — surfacing the fct_direct_join_to_source → root dbt_project_evaluator_exceptions cycle warning on full parses (it stayed quiet under partial parsing, hence the intermittency).
• Upgraded dbt packages (dbt/packages.yml, dbt/package-lock.yml): dbt_utils 1.3.3 → 1.4.1 and dbt_project_evaluator 1.3.0 → 1.3.4; dbt_date also moved 0.18.0 → 0.20.0 in the lock as a transitive dependency of dbt_expectations.
• Fixed deprecated generic-test syntax (dbt/models/_sources.yml): the four TikTok *_REPORTS_DAILY sources (CAMPAIGNS, AD_GROUPS, ADS, ADVERTISERS) now nest combination_of_columns under an arguments: key for dbt_utils.unique_combination_of_columns, resolving the MissingArgumentsPropertyInGenericTestDeprecation warning introduced in dbt 1.10.

August 6, 2026

Migrated device-population logic off frozen legacy_devices to V2 device

raw_db.tincan.legacy_devices was frozen (daily load disabled) and is retained as a historical table; V2 raw_db.tincan.device now carries all new devices. Repointed the device-population logic in fct_customer_scores and four BI queries to device, while keeping legacy_devices (read-only) as the source of truth for historical device dates.

• dbt/models/analytics/fct_customer_scores.sql: reworked the p_dev / f_dev / free_with_devices spines to source device identity from device via the subscription chain (stripe.subscriptions → subscription → device), emitting both keys (device_id = try_to_number(routing_id) for V1 activity, v2_device_id = device.id for V2 activity) so the ~10 downstream feature CTEs are unchanged. Removed the dependency on devices_joined_full_history / customers_to_stripe. The dead routing_id is null V2-native branch (matched 0 rows — every device has a routing_id) would otherwise have dropped every new device post-freeze. Device dates (first_device_online) use legacy_devices.created_at via the routing_id bridge, falling back to device.created_at only for genuinely-new devices, because device.created_at is a migration timestamp (~May 2026 backfill). Also fixed p_r28_max_dur / f_r28_max_dur, which joined call_logs on the legacy device_id only and had been silently dropping all V2 call durations since the June cutover; they now capture both eras. Measured stability: paid population unchanged (110,855); free population 99.8% retained (−183 no-live-device, +10); paid num_devices 96.3% identical (3.4% higher = completeness gain); r28_max_call_sec recovers upward (bug fix).
• queries/.../network_size.sql (×2, network_growth + call_lengths): the devices / activations CTEs now UNION the retained legacy_devices (historical, true dates — unchanged) with device for devices created after the freeze (deduped via the routing_id bridge). Historical trends are byte-identical; adds a ~6.3k go-forward device supplement.
• queries/user_retention/retention_time_series.sql and retention_grid.sql: eligible_devices now unions legacy_devices + new device rows and carries both id-spaces; a new canonical_activity step attributes V1 activity (legacy id) and V2 activity (device.id) to a stable canonical device id. This restores V2 activity that the old legacy-id-only join had been dropping since the June cutover, which had made the dashboards falsely report ~100% device inactivity for July/August (true is ~24%/partial-month). Pre-cutover history is byte-identical (validated: monthly % inactive matches to the decimal through May 2026).
• Docs: updated docs/utilities/reference_schema.md and .claude/skills/bi-query/SKILL.md to point device reporting at device (+ subscription/account), noting legacy_devices is frozen/retained and that device.created_at is a migration timestamp.

legacy_devices is deliberately retained in place (like legacy_cdr) — the call_logs / participant_logs / participant_daily_activity V1 branches and the dim_legacy_devices snapshot are unchanged. Last of the five raw_db.tincan.legacy_* tables to be addressed.

August 5, 2026

Migrated network_size new-contacts metric off the deprecated legacy_contacts

Repointed the new_contacts CTE in queries/network_growth_and_health/network_size.sql from raw_db.tincan.legacy_contacts to the V2 platform tables raw_db.tincan.contact joined to raw_db.tincan.contact_number. Per engineering, contact approval status moved to the number grain in V2 (contact_number.request_status = 'approved'), so a contact is counted as approved when it has ≥1 approved number, and count(distinct contact.id) preserves contact grain (some contacts have multiple numbers). Also switched to the 2-arg convert_timezone because contact.created_at is TIMESTAMP_TZ (the legacy column was TIMESTAMP_NTZ). A parity check showed monthly counts match the legacy source within ~1% through April 2026; from ~May 2026 new_contacts_created steps up (~+60% on recent days) because the V2 source is more complete — legacy had ~319k approved contacts with a NULL created_at and under-captured contacts after the V1→V2 cutover. This is a documented data-quality correction, not a real surge. Updated queries/network_growth_and_health/network_size.md (source, CTE description, and a discontinuity note), docs/utilities/reference_schema.md, and .claude/skills/bi-query/SKILL.md to point contact reporting at the V2 tables. Second of the five raw_db.tincan.legacy_* tables being deprecated.

Repointed customers_to_stripe off the deprecated legacy_customers

Migrated the customers_to_stripe model (dbt/models/analytics/customers_to_stripe.sql) from raw_db.tincan.legacy_customers to the V2 platform tables: a new tincan_customers CTE takes customer_id + stripe_customer_id from raw_db.tincan.account joined to raw_db.tincan.tincan_user (for email/phone) on account_id = account.id, then feeds the existing base/email/phone match logic unchanged. account is 1:1 on customer_id (same id space as legacy) and each account has exactly one tincan_user, so there is no row fan-out and the downstream fct_customer_scores join on customer_id is unaffected. Verified end-to-end parity vs the legacy source: match_type 100%, stripe_id 99.999%, email 99.996%, with account covering ~1.6k additional newer customers. Also rewrote docs/utilities/customer_identity.md to make account + tincan_user the canonical customer-profile source — reversing a now-stale "sparse" warning (a grant-bug-era artifact; verified ~100% field completeness across the 2025 and 2026 cohorts), documenting the fields that do not carry over cleanly (address now lives in raw_db.tincan.address via subscription.emergency_address_id; language/firestore_uid/ref_code have no V2 equivalent), and updating the Klaviyo join recipe — and updated .claude/skills/bi-query/SKILL.md. The legacy_customers dbt source declaration is also removed (see below), ahead of a planned rename of the underlying table to zzz_legacy_customers. Fourth of the five raw_db.tincan.legacy_* tables to be addressed (legacy_cdr, the third, was retained in place rather than migrated).

Removed the legacy_contacts dbt source declaration

Deleted the legacy_contacts table declaration (freshness config + contact_id unique/not-null data_tests) from src_tincan in dbt/models/_sources.yml. The underlying table was renamed to zzz_legacy_contacts as part of its deprecation, so the source tests had begun failing against the now-missing table; the earlier network_size migration already moved the only query consumer off it. Bundled into this PR to keep dbt build green. (_models.yml had no legacy_contacts references — it was only ever a source.)

Removed the legacy_customers dbt source declaration

Deleted the legacy_customers table declaration (freshness config + customer_id unique/not-null data_tests) from src_tincan in dbt/models/_sources.yml, now that the customers_to_stripe repoint (above) moved the only dbt consumer onto account/tincan_user. Done proactively: the underlying table will be renamed to zzz_legacy_customers after this PR deploys, and removing the declaration first prevents the source freshness check and customer_id tests from failing against the renamed table. legacy_customers had no _models.yml references.

July 29, 2026

Removed the legacy_device_mac_addresses dbt source

Deleted the legacy_device_mac_addresses table declaration (and its id unique/not_null data_tests) from src_tincan in dbt/models/_sources.yml. The table had no model, snapshot, test, or BI-query dependencies. Its underlying source raw_db.tincan.legacy_device_mac_addresses has had its import disabled and was renamed to zzz_legacy_device_mac_addresses ahead of being dropped later in Q3 2026; removing the declaration keeps dbt build clean (the source tests would otherwise fail against the renamed table). First of the five raw_db.tincan.legacy_* tables being deprecated.

July 1, 2026

Rewrote the V2 CDR branch of call_logs to mirror V1 business logic

Superseded the June 29–30 announcement-leg fixes with a full rewrite of the V2 (FreeSWITCH, raw_db.tincan.cdr) branch of dbt/models/analytics/call_logs.sql so V2 mirrors V1 (Asterisk) per-call logic and V1/V2 trends stay continuous. V1 logic and participant_daily_activity are unchanged. Components: - cdr_v2 CTE + base filters: keep sip_user_agent <> 'Asterisk PBX 21.5.0' (#1, excludes bridge legs already in v1 cdr) and lastapp is not null (#2, ~1 row per call). Dropped the prior UNALLOCATED_NUMBER filter (#3) as a no-op non-grain filter. - dst_resolved: recover the routed destination from sip_req_uri (vr-/vm-/b2bua-/users- tokens) when dst is NULL, so voicemail/routing legs classify by their true destination instead of defaulting to can_to_external. Directional joins now use dst_resolved. - leg_type taxonomy (mirrors V1's semantic categories via sip_req_uri+lastapp): vmadm-*97/play→voicemail_check; vr-→vr_failed; record/vm-→voicemail; else→call. Classification special-cases only voicemail_check and failed_call; voicemail and everything else are directional, with is_voicemail as a flag (as in V1). Folds in the earlier record-voicemail misclassification. - vr- blocked-call handling (the one tunable lever): drop FreeSWITCH-only partial keypress (1–3 digit) and malformed/* voice-response artifacts that Asterisk never logged; keep vr- to a complete destination (E.164 or 4–8 digit routing-id) classified failed_call — mirroring V1's inv01/ext01/dnd Playback failures. - Service-code exception (PR review): valid 3-digit service codes (911, 988, 933, 211/311/411/511/611/711/811) also route via the vr- path but are real outbound service calls, not partial-dial noise. Added a third keep-branch so they are not dropped, and classified them can_to_external (matching the direct NNN@ legs and V1), not failed_call. Without this, ~929 service-code legs — including 398 vr-911 — were being silently dropped. These arrive as playback legs and are treated as valid-but-unanswered calls (NO ANSWER); consequently V2's blended 911 answer rate (~13%) is legitimately lower than V1's (~21%) because V2 captures unanswered 911 attempts Asterisk never logged (the connected/bridged 911 legs still answer at ~21%, matching V1). - Disposition + is_call_answered: reconstruct disposition from billsec/lastapp/hangup_cause (V2's raw disposition is 100% NULL) — NORMAL_CLEARING connected bridges → ANSWERED, routing/never-connected → NO ANSWER (eliminates a 26% UNKNOWN bucket). is_call_answered decoupled to ANSWERED AND billsec>15, mirroring V1's human-conversation gate.

Validated V1 vs V2 on an identical June-2026 window (call_logs and pda): outbound:inbound 1.31 vs 1.39, classification mix within ~2pts, disposition aligned, talk-time/conversation 115.0 vs 115.3s, made-success 53.5% vs 53.1%, external share 33.2% vs 33.1%, num_interlocutors 2.13 vs 1.98. Known limitations (V2 source-data, not logic): BUSY is unrecoverable in V2 (no busy signal → folds into NO ANSWER); external-caller voicemails are captured by V2 but were not by V1 (V2 is the more accurate figure, so vm_left/vm_received are not continuous across the cutover); cross-system V1/V2 overlap dedup relies on the pre-existing #1 filter (unchanged here). Compiled; validated against live source data (dev build), not yet promoted to prod.

June 30, 2026

Reclassified V2 CDR announcement legs as failed_call instead of dropping them

The June 29 fix removed destination-less dialplan/announcement legs (playback, say, lua, sleep, bridge orphans — src = a Tin Can, NULL dst) from dbt/models/analytics/call_logs.sql to stop them inflating can_to_external. But that deletion also pulled 853k legs (~36% of V2 calls) out of the call count, while talk_time_seconds was unchanged (those legs never carried talk time) — so the calls : talk_time_seconds ratio jumped (~41s → ~65s per call) and diverged from historical norms. Root cause: V1 keeps the equivalent standalone Playback legs as zero-talk-time calls (it routes ~99% of them to failed_call via the has_vr_failed_call/lastdata patterns and grouping by linkedid), so deleting them in V2 broke parity with V1's call-counting definition. Fix: stop deleting them — removed the (cdr.dst is not null or lastapp in (...)) WHERE guard — and instead reclassify NULL-dst non-voicemail legs to failed_call (with FAILED disposition, NULL talk time) in a branch placed before the directional buckets. This restores V2 call volume to ~2.38M (parity with V1's treatment), leaves total talk_time_seconds byte-for-byte unchanged (98,705,596), and keeps can_to_external free of announcement-leg inflation. Verified the simulation against live source data; not yet run through dbt run. (Voicemail record legs are deliberately left as-is here — their separate misclassification is tracked independently.)

June 29, 2026

Fixed V2 CDR misclassifying announcement legs as can_to_external

The V2 branch of dbt/models/analytics/call_logs.sql was emitting a row for every dialplan application leg (playback, say, lua, sleep, bridge orphans, etc.), not just real calls. Those legs have src = a Tin Can device but a NULL dst, so the destination join missed and the classifier bucketed them as can_to_external — inflating outbound-external volume by ~573k calls / 10 days (the V2 can_to_external ÷ can_to_can ratio had jumped to ~2.7× vs the long-standing ~0.6× in V1). This propagated into participant_daily_activity.num_external_calls / calls_made. Added a WHERE guard — cdr.dst is not null or lastapp in ('play_and_get_digits','play','record') — that drops the destination-less announcement legs while preserving the intentionally-included voicemail-check (play_and_get_digits/play) and voicemail (record) rows. The existing lastapp is not null "one row per call" filter was too weak to catch these. Verified against live data: the new filter drops ~597k phantom NULL-dst legs over the trailing 10 days and keeps every dst-bearing call plus all voicemail rows.

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