Lesson 10.5 · 55 minAutomations: work that happens without you

Purpose: every Monday morning the CRM part of my weekly report should be
ready and in my inbox without my doing anything.

Context: read docs/reports/TEMPLATE.md and PROMPT.md (8.1), docs/crm-spec.md
(stages, business time zone), the dashboard queries from 5.6 and 7.8, the
scheduled job at /api/cron/daily-summary (5.5), the Claude API code from 8.3
and the email and Telegram code from 4.5. CRON_SECRET, ANTHROPIC_API_KEY and
LEAD_AI_MODEL are set; never print or log their values.

Before writing code: read Vercel's current Cron Jobs documentation and the
current Messages API documentation at platform.claude.com/docs. Do not rely
on memory. Tell me which pages you read.

Build:
1. Table weekly_reports: id, created_at, period_start (unique), period_end,
   numbers, findings (may be empty), decisions, status. No anonymous access.
2. One server function that counts, for a Monday-to-Sunday week in the
   business time zone: new leads; leads by utm_source and utm_campaign;
   leads that reached each stage; spend and cost per qualified lead by
   campaign; and the previous week's totals. Reuse the dashboard's
   definitions.
3. Findings: send only that table of numbers (no names, emails, phones or
   messages) to the Claude API with the PROMPT.md rules that apply to CRM
   data; at most five findings, no decisions. On any failure store the
   report with the note "Findings not available".
4. GET /api/cron/weekly-report, protected by CRON_SECRET exactly like the
   5.5 job, for the most recent complete week. If a report for that
   period_start exists it does nothing and says so. Otherwise it counts,
   gets findings, stores the report, emails it to OWNER_EMAIL and sends one
   Telegram line with the lead total and a link. It returns counts only.
5. Schedule it in vercel.json for Mondays at 06:00 UTC; keep existing jobs.
6. /admin/reports lists reports. /admin/reports/<id> shows the numbers with
   counts beside every rate, the findings under "AI findings - check against
   the numbers", a "Decisions" box I can fill and save, and a "Generate for
   last week" button that runs the same function.

Constraints: no personal data in the email, the Telegram line or the API
call. No new environment variables. Do not change the 8.1 files.

Verify before reporting: run the build and tests; call the endpoint locally
without the header (401), with it (one report) and again (no second row,
email or message); run once with ANTHROPIC_API_KEY removed and show
"Findings not available"; print the numbers so I can compare three with the
CRM. Say what you could not verify.

Purpose: I want to send my Telegram bot "/today" and get today's numbers from
my CRM. Only I may get an answer.

Context: the bot and sending code from 4.5. TELEGRAM_BOT_TOKEN,
TELEGRAM_CHAT_ID and the new TELEGRAM_WEBHOOK_SECRET are set locally and in
Vercel; never print or log them. "Today" must mean the same as on
/admin/today.

Before writing code: read the current Telegram Bot API documentation for
setWebhook (including secret_token), getWebhookInfo, deleteWebhook, the
Update and Message objects and sendMessage. Tell me what you read.

Build:
1. POST /api/telegram/webhook. If the X-Telegram-Bot-Api-Secret-Token header
   does not equal TELEGRAM_WEBHOOK_SECRET, or the variable is missing,
   return 401 and do nothing.
2. If the message's chat id is not TELEGRAM_CHAT_ID: no database query;
   reply "This bot is private." and return success.
3. For the owner, "/today" replies with new leads today by source and the
   number of tasks due and overdue, with a link to /admin/today. Counts
   only. "/help" and anything else list the commands.
4. Once the header is valid, always return success to Telegram, even if
   replying fails, and log the failure, so the message is not resent.
5. Scripts: "npm run telegram:set-webhook" registers
   https://<production domain>/api/telegram/webhook with the secret token
   for message updates only, then prints getWebhookInfo without secrets;
   "npm run telegram:delete-webhook" removes it. Ask me for the domain.

Constraints: read-only commands; the notification code stays unchanged.

Verify before reporting: run the build; post a sample message update to the
local endpoint without the header (401), with the header and another chat id
(no data), and with the header and my chat id (I should receive the reply);
tell me the counts sent. Say what you could not verify.