/cost-report                           # Current month summary
/cost-report --period=weekly           # Weekly breakdown
/cost-report --period=daily            # Daily breakdown
/cost-report --function=chat-orchestrator  # Specific function
/cost-report --compare                 # Compare to previous period
/cost-report --forecast                # Project end-of-month costs

-- Estimate costs from chat messages (if logged)
SELECT
  DATE(created_at) as date,
  COUNT(*) as requests,
  COUNT(*) * 0.10 as estimated_cost_usd
FROM chat_messages
WHERE role = 'assistant'
  AND created_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date DESC;

# Get recent function invocations
npx supabase functions logs chat-orchestrator --scroll 2>&1 | \
  grep "estimated_cost" | \
  tail -100

## Monthly Cost Estimate

| Function | Requests | Cost/Req | Total | % |
|----------|----------|----------|-------|---|
| chat-orchestrator | 500 | $0.10 | $50.00 | 65% |
| mandate-matcher | 1,000 | $0.015 | $15.00 | 19% |
| comps-generator | 200 | $0.05 | $10.00 | 13% |
| regenerate-embeddings | 500 | $0.0001 | $0.05 | 0% |
| Other | - | - | $2.00 | 3% |
|----------|----------|----------|-------|---|
| **TOTAL** | | | **$77.05** | |

## Cost by Feature

| Feature | Functions Used | Monthly Cost |
|---------|----------------|--------------|
| AI Chatbot | chat-orchestrator, vector-search | ~$52 |
| Mandate Matcher | mandate-matcher | ~$15 |
| Comps Generator | comps-generator | ~$10 |
| Embeddings | regenerate-embeddings | ~$0.50 |

-- Check cache hit rate
SELECT
  feature,
  COUNT(*) as total_queries,
  COUNT(*) FILTER (WHERE cache_hit) as cache_hits,
  ROUND(100.0 * COUNT(*) FILTER (WHERE cache_hit) / COUNT(*), 1) as hit_rate_pct
FROM search_cache_queries
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY feature;

const ALERT_THRESHOLDS = {
  daily: 10,     // Alert if > $10/day
  weekly: 50,    // Alert if > $50/week
  monthly: 150,  // Alert if > $150/month
  spike: 2.0     // Alert if 2x normal rate
};

-- Daily cost trend (estimate)
WITH daily_costs AS (
  SELECT
    DATE(created_at) as date,
    COUNT(*) * 0.10 as chat_cost,
    -- Add other function estimates
    0 as mandate_cost
  FROM chat_messages
  WHERE role = 'assistant'
    AND created_at >= NOW() - INTERVAL '30 days'
  GROUP BY DATE(created_at)
)
SELECT
  date,
  chat_cost + mandate_cost as total_cost,
  AVG(chat_cost + mandate_cost) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as rolling_7day_avg
FROM daily_costs
ORDER BY date DESC;

## Cost Report - December 2024

Period: Dec 1-25, 2024 (25 days)

### Summary

Total Estimated Cost: $64.25
Daily Average: $2.57
Projected Month-End: $79.50

### By Service

| Service | Cost | % of Total |
|---------|------|------------|
| OpenAI | $62.00 | 96.5% |
| Resend | $1.25 | 1.9% |
| Other | $1.00 | 1.6% |

### By Function

| Function | Requests | Cost |
|----------|----------|------|
| chat-orchestrator | 420 | $42.00 |
| mandate-matcher | 850 | $12.75 |
| comps-generator | 145 | $7.25 |

### Trends

vs Last Month: +12% ($57.25 → $64.25)
vs Last Week: -5% (trending down)

### Recommendations

1. Cache hit rate is 45% - consider warming cache
2. chat-orchestrator costs up 20% - review usage
3. Consider GPT-4 Turbo migration (est. 30% savings)

{
  "text": "Weekly Cost Report",
  "blocks": [
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "*Weekly Cost Report*\nDec 18-25, 2024"
      }
    },
    {
      "type": "section",
      "fields": [
        {"type": "mrkdwn", "text": "*Total Cost*\n$18.50"},
        {"type": "mrkdwn", "text": "*vs Last Week*\n-5%"},
        {"type": "mrkdwn", "text": "*Top Function*\nchat-orchestrator"},
        {"type": "mrkdwn", "text": "*Cache Hit Rate*\n48%"}
      ]
    }
  ]
}

# Via OpenAI CLI (if installed)
openai api usage

# Or via dashboard
open https://platform.openai.com/usage

-- Estimate Stripe fees from subscriptions
SELECT
  DATE_TRUNC('month', created_at) as month,
  COUNT(*) as transactions,
  SUM(amount) as gross_revenue,
  SUM(amount * 0.029 + 0.30) as stripe_fees,
  SUM(amount) - SUM(amount * 0.029 + 0.30) as net_revenue
FROM subscriptions
WHERE status = 'active'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;