Funder Report Builder

Build a funder report by filling Sheet 1 Column B of a report template from a
parameterized SELECT against the precomputed Postgres tables.

When to use

A team member asks to "build the funder report" and references a template
workbook. The prompt supplies filter overrides for the period being reported.

Prompt contract

The team member's prompt should include:

• Report / template: the workbook name (e.g. scp_mch.xlsx)
• Period + overrides: any filter values that differ from Sheet 3 defaults
  (e.g. time_grain=Q1, month_start_date=2025-04-01, month_end_date=2025-06-30)
• Output: desired output filename

Anything not overridden falls back to the Sheet 3 default. Any value outside
the declared type / allowed_values is rejected before SQL runs.

Procedure (in order)

1. Identify the report id from the template name (e.g. scp_mch.xlsx ->
   report scp_mch, at templates/scp_mch.xlsx). The template is
   self-contained: Sheet 3 "Data Source Spec" holds the filters/types/defaults,
   the "Template" sheet's Column F holds the per-indicator formula that computes
   each Column B value (e.g. 1.02 + 1.08, (1.01 + 1.07) - (1.03 + 1.09),
   2.03.N, round(5.01, 0)), and Sheet 4 "Report Logic" holds report metadata
   plus the optional fingerprint used for the drift check.

2. Parse the prompt into filter overrides. You do NOT validate or compute by
   hand — you only translate the prompt into --set key=value pairs. The
   engine merges them over Sheet 3 defaults and validates every value against
   the declared type / allowed_values, halting with a clear message on failure.

3. Invoke the deterministic engine via the CLI (from the project root):

   python -m engine.build_report \
     --report <id> \
     --set time_grain=Q1 \
     --set month_start_date=2025-04-01 \
     --set month_end_date=2025-06-30 \
     --output <output path>

   The engine (engine/build_report.py + engine/core.py, reading the
   template's own Sheet 3, Sheet 4, and the Template-sheet Column F formulas):

   • validates the merged filters,
   • builds a parameterized SELECT from Sheet 3 (identifiers allowlisted,
     values bound — never string-concatenated),
   • runs it against the read-only DB role when DATABASE_URL (or --dsn)
     is set; otherwise falls back to the Sheet 2 sample data (offline mode),
   • runs the drift check (live data + Column E vs the Sheet 4 fingerprint),
   • evaluates the Column F formulas and writes Column B,
   • drops the fresh query result into Sheet 2 and saves the output.

4. If the drift check raises issues, the engine exits without writing (code 2).
   Do not publish. Produce a plain-language discrepancy report and a proposed
   change to the template (a Column F formula and/or the Sheet 3 spec) for human
   approval. Only after a human commits the change does the engine run against it.

5. Return the engine's validation summary (computed values + any flags) to the
   requester verbatim.

Mapping a prompt to the command

Prompt:

Build the SCP funder report. Template: scp_mch.xlsx. Period: Q1 FY25-26.
Overrides: time_grain=Q1, month_start_date=2025-04-01, month_end_date=2025-06-30.
Output: scp_q1_fy2526.xlsx

Becomes:

python -m engine.build_report --report scp_mch \
  --set time_grain=Q1 \
  --set month_start_date=2025-04-01 \
  --set month_end_date=2025-06-30 \
  --output reports/scp_mch/out/scp_q1_fy2526.xlsx

Filters not mentioned in the prompt fall back to Sheet 3 defaults automatically.

Fingerprint (drift baseline) --- auto-generated, never hand-edited

The [FINGERPRINT] block in Sheet 4 is the drift-check baseline: build_report
compares each live sr's indicator name against it and refuses to publish if an sr
the formulas rely on was renamed or removed upstream. It is regenerated from the
DB, not maintained by hand. When the upstream names legitimately change (and you
have confirmed they are correct), re-bless the baseline:

python -m engine.refresh_fingerprint --report scp_mch \
  --set time_grain=monthly \
  --set month_start_date=2026-03-01 \
  --set month_end_date=2026-03-31

This reads the Column F formulas to learn which sr's are used, pulls their current
names from the DB, and rewrites the [FINGERPRINT] block. Use a period that has
data, then review/commit the updated template.

Hard rules

• The LLM only does: prompt -> validated filter dict, and (on drift) propose a
  change to the template's Sheet 3 spec or a Column F formula. SQL and
  arithmetic are always deterministic Python. Two runs of the same prompt must
  produce an identical workbook.
• Never introduce a table/column/filter not declared in Sheet 3.
• Never write to the database; connect read-only.