Live computation, not stored

ARCHITECTURE: LIVE COMPUTATION:

  Read a budget
    ↓
  SELECT SUM(costColumn(hasBilling))
  FROM cost_records
  WHERE (scope matches budget's filter)
    AND (date in current budget period)
    ↓
  Return the sum

ARCHITECTURE NOT USED: STORED CURRENT_SPEND:
  Cron writes current_spend_usd column periodically
  Read budget → return stored value
  (the original design; the column was dropped in April 2026)

1. ACCURACY
   Always current. No stale data. New cost records show up in the
   budget reading immediately (next read).
   Stored values lag by however long the last cron ran.

2. SIMPLICITY
   One source of truth (cost_records). No sync logic.
   Stored values require maintenance:
     - When cost records change, recompute
     - When a group's membership changes, recompute
     - Handle race conditions during recompute
     - Detect and reconcile drift
     None of this exists in the live-compute model.

3. FLEXIBILITY
   Every target type (resource, group, account, AI provider) is
   just a different filter on the same query. Stored would need
   a maintained total per target, kept in step with every
   membership change.

4. CORRECTNESS
   No drift. The computation matches what reports show.
   Stored values can diverge from actuals (rare, but possible
   bugs cause this; we eliminate the class of bug entirely).

TYPICAL BUDGET READ COSTS (with proper indexing):

  Per-resource budget:    ~5 ms   (single resource_uid filter)
  Per-group budget:       ~10-25 ms (small to medium group)
  Per-account budget:      ~10-50 ms (one account filter)
  Org-wide rollup:         ~50-150 ms (full table scan with date filter)

BUDGET HEALTH WIDGET listing 12 budgets: ~200-400 ms total
  Fast enough for sub-second page renders.

STORED-COLUMN READ COSTS:
  Per budget read: 1-2 ms (single row read)

  But: more complex sync logic
       Stored values lag behind actuals by minutes-to-hours
       Re-computation on membership change adds latency to write

costColumn():
  always  →  COALESCE(actual_cost_usd, cost_usd)

1. SPEND UPDATES IMMEDIATELY when new cost records land
   - Daily: the cost-and-rollup job writes new rack-rate cost
     records (19:55 UTC)
   - Daily: billing sync updates actual_cost_usd (20:20 UTC)
   - In both cases, next budget read picks up the new value
   - Budget transitions from green to yellow to red as data arrives;
     no batch processing delay

2. ONE NUMBER, NO COST-SOURCE LABEL
   - The Budgets page shows a single spend figure per budget
   - It does not say which days are billed and which are rack rate
   - If billing sync has not run for recent days, those days are
     rack rate (T0.M0.4); check the sync before arguing about cents

3. BUDGET CHANGES TAKE EFFECT IMMEDIATELY
   - Threshold changed: next read evaluates status against the new
     threshold
   - Amount changed: next read re-evaluates % used and status
   - No stored spend to "recompute": there is nothing to migrate
   - The target is fixed: to watch a different resource, group or
     account, create a new budget on it (one target per budget);
     its spend is there on the first read

DAILY COST-AND-ROLLUP JOB:

  For each active budget:
    spend = live_compute_spend(budget)
    level = exceeded  if spend >= 100% of the budget
            warning   if spend >= the budget's alert threshold
            none      otherwise

    If a channel subscribes to budget.warning / budget.exceeded:
      notify that channel (again on each daily run while the
      level holds)
    Else (no channel configured):
      email once per level per month, recorded in
      budget_alert_state.alerted_level; re-armed if spend
      falls back below the level

MIGRATION (2026-04-30):
  1. Move budget definitions to the configuration service and
     convert all budget reads to live computation
              Validate against the stored value (parity check)
              No customer-visible change

  2. Drop the old budgets table, and its stored column
              The daily job stays: it still computes spend live
              to fire alerts, but no longer writes spend back

CUSTOMER IMPACT: zero
  Reads use the same underlying cost data, just computed live.
  Performance is comparable (within 50 ms).
  Accuracy improved (no stale-data window).
  Audit log is unchanged.

LIVE COMPUTE BREAKS DOWN if:
  - Underlying data is very expensive to query (billion-row scans
    without indexing)
  - Aggregation is recursive or graph-walking
  - Realtime requirement is sub-millisecond (it's not for budgets)

For ZopNight's cost_records table:
  - Indexed appropriately; queries hit indices
  - Aggregation is straightforward SUM with date + scope filter
  - 50 ms is fine for budgets

So live compute is the right answer here.

SCENARIO: Customer tunes a budget mid-month.

T+0       Budgets page, dev-platform-eu group budget
          Spend: $4,200 of $4,800 (87.5%, live computed)
          Alert threshold: 90%  → status green

T+30 s    Edit the budget; set the alert threshold to 80%
          Save

T+31 s    Budgets page refreshes
          Same spend, $4,200 (87.5%)
          87.5% is now above 80%  → status yellow

T+60 s    Edit again; raise the amount to $6,000
          Save

T+61 s    Budgets page refreshes
          $4,200 of $6,000 = 70%  → status green

T+2 min   The team also wants staging-eu watched
          A budget's target cannot be edited, so create a new
          budget on the staging-eu group: $2,600/month
          First read: spend $1,950 (75%), computed on the spot

NO BATCH delay. NO cron wait. NO job to rerun.

This works because:
  - The budget definition stores the target, amount and threshold
  - Every read sums cost_records for that target
  - Status is derived on the read from spend, amount and threshold
  - No stored value to refresh

CONTRAST with stored-column architecture:
  - Save would trigger a recompute job
  - User refreshes; sees stale value until job completes
  - User wonders if the change took effect
  - Possible race condition between recompute and read

1. Open the Budgets page in your org; pick one budget
   Current spend (note value): $__________
   % used and status: __________

2. Come back after the next daily cost run (after 20:20 UTC)
   Refresh the Budgets page
   Has the spend value changed? Yes / No
   New value: $__________

3. Edit the budget's alert threshold to just below its current
   % used. Did the status turn yellow on the next read?
   Yes / No
   Set the threshold back afterwards.

4. Audit observation:
   Did the budget transition green/yellow/red during the month?
   __________

5. Architectural check:
   Call the API: GET /orgs/{orgID}/budgets
   Your budget's entry should show the same computed spend
   API response: $__________  (matches UI? yes / no)