Substrate · Customer Health · leading-indicator scoring

6 leading signals + 1 placeholder — weights sum to 100

signal	weight	formula	data source
`order_velocity_decline`	25	max(0, 1 - last30 / baseline30) where baseline30 = prior90 / 3	`transactions` where type='SalesOrd'
`order_recency_gap`	20	min(1, max(0, (gap_days / baseline - 1) / 3)) · baseline = mean interval of last 12 SOs	`transactions` (window of last 12 SOs)
`dispute_frequency`	15	min(1, n / 3) where n = credit memos last 30d	`transactions` where type='CustCred'
`reflexion_friction`	15	min(1, n / 5) where n = reflexion_log entries last 60d, tagged friction OR status='needs_review'	`reflexion_log` (table-existence guarded)
`ar_aging_lagging`	10	min(1, 2 × over60 / total) · over60 = bucket_61_90 + bucket_90_plus	`v_customer_ar_aging` view (R560 existence guard)
`order_spread_narrowing`	10	max(0, 1 - recent_skus / baseline_skus) · recent = last 60d distinct, baseline = 60-240d distinct	`so_lines`
`email_response_latency`	5	(placeholder — not yet computed)	`inbound_email_log` + outbound (future)

Weights live in WEIGHTS object at src/lib/customer_health.ts:29. TOTAL_WEIGHT is the sum (100); composite score is (sumContribs / TOTAL_WEIGHT) * 100.

Tables written per run

table	cardinality per run	schema notes
`customer_health_signals`	N rows per customer (one per non-null signal)	append-only; index on (customer_id, computed_at DESC). `source='compute_signal_cron'`
`customer_health_scores`	1 row per customer (UPSERT on PK customer_id)	ON CONFLICT copies `health_score` → `prior_score` before update; `top_signals_json` = top-3 contributions
`customer_health_score_history`	1 row per customer per run	append-only for trend; index on (customer_id, computed_at DESC)

Cron behavior — rotation + concurrency

Cron expression: 45 5 * * * (05:45 UTC nightly) — wired at src/index.ts:33255; the same cron triggers the verify scheduler + health snapshot.
Limit: 500 customers per nightly pass (stays inside Worker CPU budget).
Rotation (R560 audit HIGH #8): ORDER BY COALESCE(chs.computed_at, '0000-00-00') ASC — stalest scores first so the 500-cap rotates instead of hitting the same arbitrary 500. New customers (no prior score) sort first via the NULL-equivalent '0000-00-00'.
Candidate set: active = customers with ≥1 SO in trailing 240 days. Inactive customers are intentionally excluded (no signal to compute).
Concurrency (R561): 10 parallel customers via batched Promise.all. Serial @ 50ms/query × 6 queries × 500 customers = 150s. Concurrency 10 = ~15s.
Per-customer try/catch (R560): a single bad row no longer aborts the batch — logged + counted in failed, batch continues.
Ad-hoc full sweep: POST /api/admin/compute-customer-health-now → recomputeAllCustomerHealth(env, { limit: 5000 }). Used for backfill + reweight tests. src/index.ts:14454.

Chat tools — palette-gated reads

tool	inputs	output	src
`get_customer_health_score`	`{ customer_name? \| customer_id? }`	composite + tier + top contributing signals + 14-day score history + 30 most-recent raw signals	`src/chat_tools/impls.ts:6593`
`list_at_risk_customers`	`{ min_tier? = 'orange', limit? }`	customers in min_tier or worse, ranked by composite DESC	`src/chat_tools/impls.ts:6659`

Both tools advertised in src/chat_tools/prompt.ts with explicit usage triggers ("how is <customer> doing", "is <customer> at risk", "show me churn risk"). UI surface: /customer-health.html.

Edge cases (R560 hardening)

case	guard	behavior
trandate string formats vary (M/D/YYYY, ISO, NULL)	`ISO_TRANDATE` helper from `sql_helpers`	normalizes inside SQL; null-safe
`v_customer_ar_aging` view dropped/renamed	existence guard: `SELECT name FROM sqlite_master WHERE type IN ('view','table') AND name='v_customer_ar_aging'`	R560: warns once + returns null AR signal (was silently dropping the 10%-weighted signal from every score)
`reflexion_log` table absent in older D1 envs	same sqlite_master existence guard	signal returns null gracefully
customer has no SOs in window	`last30 + prior90 === 0` check	velocity signal returns null; not all customers must score on every signal
baseline order interval ≤ 0 or undefined	`if (baseline <= 0) return null`	recency signal returns null
per-customer query throws inside recompute batch	R560: try/catch around `computeCustomerHealth + persist`	logs + increments `failed`, batch continues

Open punch list — honest gaps

Weights are hand-tuned, not learned. WEIGHTS object is a domain-expert prior. No regression against labeled churn yet.
email_response_latency is a placeholder. 5 weight allocated but signal not implemented. Source data (inbound_email_log + outbound timestamps) exists; the computation function is missing.
No backtest against known churn cases. Cal-Maine and Sysco are obvious historical anchors — the model has never been run against pre-event data to validate lead time.
No alert wiring. A red tier today only surfaces if Mike asks; there's no nightly "new red customers" digest or auto-staged proposed_action.
No per-signal drift detection. If computeAr consistently returns null because of a view rename, current behavior is a warn log + silent 10-point weight loss across all customers.

Source files

src/lib/customer_health.ts — 6 signal functions + computeCustomerHealth + persistCustomerHealth + recomputeAllCustomerHealth (359 LOC)
migrations/schema/126_customer_health_signals.sql — 3 tables + indexes
Cron: src/index.ts:33255 (the 45 5 * * * entrypoint) and :33349 (this signal's ctx.waitUntil block)
Ad-hoc endpoint: src/index.ts:14454
Chat tools: src/chat_tools/impls.ts:6593, :6659
UI: /customer-health.html

Customer Health — leading-indicator scoring