Skip to content

ERP fee reporting & analytics: let the fee desk answer its own questions

A fee clerk asked how much came in by UPI yesterday versus cash, and the ERP has no screen that answers it. The Collections tab is a flat register — newest first, filterable by text/mode/date, capped at 100 rows a page, showing no totals. So the clerk pages through and adds it up by hand, or keeps a parallel spreadsheet. This doc covers a report builder over fee collections and dues (any combination of period, mode, cohort, structure and head, with CSV and PDF export) and a fee Overview dashboard in the same module.

Status: in progress — Product Mindset Gate passed 2026-08-01. Design accepted 2026-08-01 after an interactive mockup review. PRs 1 and 2 of 5 implemented: kwiloai_webapp#1470 (paid_on) and kwiloai_webapp#1472 (report engine, stacked on it). PRs 3–5 not started.

Interactive design mockup (live filters, both tenant types, light and dark): https://claude.ai/code/artifact/9ac22d2b-b2c7-4656-a2ae-021a0fac46bb

Related: erp-fee-payments-per-institution-razorpay.md (the collection ledger this reports on, and the source of mode = "online" rows), erp-higher-ed-scheduling.md (why cohort dimensions must not be Class→Section only), erp-branch-transfer.md (a transfer re-attributes a student's cohort, which is why prior-year reports drift — see Risks).

Why this exists

  1. Who asked: Fee-section faculty at live tenants, relayed through the founder. Not an internal hypothesis. (Institution names to be pinned in this header before the implementation PR.)
  2. User pain: The clerk who counts the cash drawer at 5pm cannot get the number the drawer should contain. GET /erp/fees/payments returns rows, not sums; GET /erp/fees/summary returns fixed KPI tiles that cannot be sliced. There is no by-class, by-structure or by-head figure anywhere in the product. Month-end and audit reconciliation are done in Excel, from a register the clerk pages through by hand.
  3. Cost of not doing it: Fee staff maintain a shadow spreadsheet, which means the ERP is not the system of record for the one module where being the system of record matters most. Institutions arriving from Tally-grade reporting read the absence as the product being unfinished. Deal-relevant during evaluation.
  4. Validated or guess: Validated for collection and dues reporting — a direct customer request with a named workflow (day-close). Weaker for the PDF-with-charts requirement: it reads like "something to hand the principal or trustee," which is a different job from the clerk's day-close, and nobody has described that hand-off concretely. Flagged, not blocking; PDF is deliberately sequenced late (PR 4) and depends on nothing downstream, so it can be dropped if the pull never materialises.
  5. How we will know it worked: The clerk closes the day from the Reports tab without opening Excel, and the shadow spreadsheet stops being updated. Sean Ellis counterfactual: remove it a week after shipping and the fee desk complains by name for "the daily collection report" — specific, because it replaced a daily manual ritual.

Personas

Mr. Suresh, fee-desk clerk. Collects at the counter all day, reconciles the drawer at close. Needs one number — gross cash received today — and a mode-wise split to tally against the UPI settlement report. Uses the same view every single day.

Mrs. Prema, fee-section head. Answers the principal's "where are we on collection this term" and chases defaulters. Needs outstanding by class or branch, ageing, and collection efficiency. Slices by structure when the management-quota cohort behaves differently from merit.

The Principal / Trustee, who receives a monthly figure. Does not log in weekly; consumes the PDF. This is the persona whose need is least validated.

ICP exclusions

  • Learners and guardians never see these surfaces. The portal has its own narrower fee views; nothing here changes them.
  • Cross-org-unit consolidated reporting (a trust comparing three colleges) is out of scope. Every report is scoped to one org_unit_id.
  • Statutory/tax reporting (GST, 12A/80G, Tally export) is out of scope. This is operational reporting, not accounting output.

Outcomes

  • The fee desk answers its own ad-hoc question without engineering or a DB export.
  • Day-close reconciliation happens in the product, against a number the clerk trusts because it keys off when money was received, not when it was keyed in.
  • The section head sees where collection is lagging by cohort, early enough to act.

Non-goals

  1. No scheduled or emailed reports in phase 1. The server-rendered PDF is designed so this becomes possible later, but nothing is scheduled.
  2. No saved-view records. Report state lives entirely in URL search params, so bookmarking and sharing a link already covers most of the need without a table, CRUD, or sharing semantics.
  3. No exact per-head allocation at collection time. Head figures are apportioned (see Design decisions). No fee_payment_allocations table.
  4. No new permission primitive. Reports sit at the existing fees staff tier.
  5. No change to how payments are recorded, beyond adding a date field.
  6. No cohort snapshotting onto the payment row.

Metrics

  • North star: fee-desk sessions that reach the Reports tab and export or read a total ÷ working days. Target: the desk lands there daily, unprompted.
  • Guardrail: zero reconciliation discrepancies attributed to the report — specifically, the head-wise columns must always sum to the gross total, and the gross total must match what the register shows for the same filters.

Risks & mitigations

  • Apportioned head figures get mistaken for a cash record. Someone reads "Tuition ₹40,00,000" as forty lakh of tuition physically collected, when it is a derived share. Mitigation: the head dimension is labelled Apportioned in the table footer and the PDF whenever a head filter or grouping is active, with the basis stated inline. If a tenant ever needs it exact, that is the allocation table — a known, costed follow-up, not a surprise.
  • Prior-year reports drift after promotion. Cohort is joined live off the student, so re-running an AY2025-26 class-wise report today attributes the money to this year's class. Branch transfers (erp-branch-transfer.md) do the same mid-year. Mitigation: state it on the Overview tab and in the PDF footer. Accepted deliberately — the dominant use is the current year, and dues reporting is inherently about the present.
  • Back-dating is a fraud surface. A clerk who can set paid_on can move a collection into a closed period. Mitigation: future dates rejected by validator; created_at is retained and unchanged, so entry-vs-collection lag is always auditable; online captures set paid_on server-side and never accept it from the client.
  • WeasyPrint blocks the event loop. It parses on HTML.__init__, not just at write_pdf. Mitigation: asyncio.to_thread, exactly as erp/exam_ops/hall_ticket_pdf.py already does, plus that module's _no_remote_fetch url_fetcher so the renderer cannot be induced to fetch anything.
  • Unbounded exports. A year × two group-by levels can produce a very large result. Mitigation: CSV streams with a 50k-row cap, PDF caps at 2k rows, and both caps are disclosed in the UI and in the file — a silently truncated financial report is worse than a refused one.
  • Existing chart palette is broken (see Found while designing). Building the Overview tab on it would ship a dashboard where two series share a colour.

Success signals

  • The shadow spreadsheet stops being updated — ask the fee desk directly at 2 weeks.
  • Support stops receiving "can you send me a fee export" requests.
  • Sean Ellis: removal draws a named complaint about the daily collection report, not a vague "the ERP feels limited."

Mental model

Grounded in apps/backend/src/erp/fees/models.py, service.py, and src/models/academic.py.

  • Money is integer paise, everywhere. Every amount is a *_paise BigInteger. Apportionment must therefore be exact integer arithmetic, not float division.
  • A payment is a lump sum against a structure, not against heads. FeePayment has amount_paise, mode, and an optional assignment_id. There is no head linkage and never has been. Head-level reporting is therefore derived.
  • A payment has no cohort and no collection date. Cohort comes from StudentProfile → Student: class_id/section_id for K-12, or StudentLevel → AcademicLevel → Branch → Program for higher ed. Date, until now, came only from created_at — the moment the row was keyed in.
  • Three money states, not one. status = cancelled payments are void. FeeRefund rows with status = approved reduce revenue but do not reduce what the drawer received. Gross, refunds, and net are three different questions and the report answers all three separately.
  • Charged = sum of assigned structures' lines − discount + unwaived fines; outstanding = charged − completed payments + approved refunds. This is already implemented in _financials_by_profile.

Design decisions

1. Head figures are apportioned pro-rata, using largest-remainder. Basis is the assigned structure's line composition. A ₹50,000 payment against a structure that is 80/16/4 Tuition/Lab/Exam contributes ₹40,000/₹8,000/₹2,000. Every share is floored to integer paise, then the leftover paise are handed to the heads with the largest fractional parts, so shares sum exactly to the payment. Without this the head columns silently fail to reconcile and someone loses an afternoon to a two-paise gap. apportion() is a pure function with a property test.

Payments with assignment_id IS NULL cannot be apportioned and land in an explicit Unassigned bucket that is always rendered. Fines are not heads and fall in the same bucket. The bucket exists so the head breakdown always reconciles to gross.

2. Add paid_on, and let the clerk back-date. One DATE column on erp.fee_payments, backfilled from created_at, NOT NULL. Reports key off it. Cash taken on the 31st and entered on the 1st otherwise lands in the wrong month, which makes every daily/monthly figure untrustworthy for reconciliation — the exact job this feature exists to do. created_at is untouched, so the lag stays auditable.

Every date here is institution-local, via src/erp/clock.py — institution_today() in the app, (now() AT TIME ZONE 'Asia/Kolkata')::date as the column default and in the backfill. Never date.today() or CURRENT_DATE: the server and database run in UTC, which names the previous day for the whole 00:00–05:30 IST window. That window is not theoretical for this column — parents pay online late at night, and the backfill hits it for every row written after 18:30 UTC. A test pins the rule so a regression fails loudly.

3. Cohort is joined live; the drift is disclosed, not hidden. No snapshot columns. Rationale in Risks.

4. Reports sit at the existing fees staff tier. require_erp_module(FeatureKey.ERP_FEES, "fees") — the same dependency as the register. A clerk with the grant can already read every underlying payment row one page at a time, so an aggregate of data they can already see discloses nothing new, and the people who asked for it are the clerks. Every query still filters org_unit_id explicitly despite RLS context being set by the dep — defence in depth, per the backend guide.

5. One builder, no presets. Period + filters + up to two group-by levels, in one form. Two levels, not unlimited: past two the table stops being readable and the SQL stops being indexable. Every dimension works as both filter and group-by, which is what makes "tuition from VIII-B by UPI in July, by day" a configuration rather than a feature request.

6. Report state lives in URL search params. Bookmarkable and shareable. This is why saved views are a non-goal.

7. PDF renders server-side from server-drawn SVG. Backend generates the chart SVG from the same aggregate it returns as JSON. The alternative — accepting the browser's recharts SVG and rendering it — would have WeasyPrint render client-supplied SVG, which can reference external URLs. That is an SSRF surface on a financial endpoint and was rejected. Server-side also makes a report reproducible from a URL alone, which is the precondition for scheduled reports later.

8. Tab reorder, Overview as the landing tab. Today: heads · structures · collections · dues · settings, landing on Fee heads — a setup screen configured once a year. New order puts daily work first and setup last:

Overview | Collections | Dues | Reports | Structures | Fee heads | Settings
(landing)                                └──── setup, touched rarely ────┘

Settings stays manager-gated exactly as it is. This is an IA change under the CLAUDE.md trigger and was explicitly accepted.

User flows

Day close (Mr. Suresh, daily). Opens Fees → lands on Overview → reads "Collected today". Needs the split: Reports tab → period defaults to this month, he sets Today → Group by Mode → four rows, gross/refunds/net → tallies against the drawer and the UPI settlement → done. Bookmarks the URL; tomorrow it is one click.

Chasing a cohort (Mrs. Prema, weekly). Overview → "Outstanding by class" shows VIII lagging → Reports → filter Class VIII → Group by Section, then Structure → sees the management-quota cohort is the gap → Download CSV → hands the list to the counsellor.

Month end. Reports → period = last month → Group by Head → Download PDF → mails it up. Head figures carry the Apportioned label.

Implementation

Schema — alembic_erp/versions/erp0026_fee_payment_paid_on.py, chaining off erp0025. One column and one index. The composite index is not optional: every report is org_unit_id = ? AND paid_on BETWEEN ? AND ? and the table only has a plain org_unit_id index today. Both upgrade() and downgrade().

ALTER TABLE erp.fee_payments ADD COLUMN paid_on DATE;
UPDATE erp.fee_payments SET paid_on = (created_at AT TIME ZONE 'UTC')::date;
ALTER TABLE erp.fee_payments ALTER COLUMN paid_on SET NOT NULL,
                             ALTER COLUMN paid_on SET DEFAULT CURRENT_DATE;
CREATE INDEX ix_erp_fee_payments_org_paid_on ON erp.fee_payments (org_unit_id, paid_on);

Backend — service.py is already 1058 lines; reporting goes in a sibling package, not into it:

src/erp/fees/reporting/
├── query.py        ReportQuery: period, granularity, filters, group_by
├── collection.py   collection aggregate (SQL)
├── dues.py         outstanding + ageing aggregate
├── apportion.py    pro-rata head split — pure
├── csv_export.py   StreamingResponse row generator
├── charts.py       svg_bar() / svg_pie() — pure
└── pdf.py          Jinja + WeasyPrint, mirrors hall_ticket_pdf.py

apportion.py and charts.py are pure functions deliberately: the two parts most likely to be wrong are the two testable without a database or a browser.

Endpoints — all behind FeesStaff:

GET /erp/fees/reports/dimensions      dropdown options in one call
GET /erp/fees/reports/collection      JSON aggregate
GET /erp/fees/reports/collection.csv  StreamingResponse, cap 50k rows
GET /erp/fees/reports/collection.pdf  WeasyPrint, cap 2k rows
GET /erp/fees/reports/dues            + .csv / .pdf
GET /erp/fees/analytics               Overview payload

dimensions returns only the dimensions the tenant has data for, so an engineering college never sees an empty Class dropdown — the failure mode recorded in erp-higher-ed-scheduling.md.

Frontend — ErpFeesPage/ReportsTab/ (index, FilterPanel, ResultTable, ResultChart, ExportButtons, useReportQuery) and ErpFeesPage/AnalyticsTab/. Overview reuses components/analytics/ (KPICard, RAreaChart, RBarChart) so it reads as the same product as the org-admin dashboard. Exports reuse the existing, tested apps/web/src/lib/download.ts.

Overview content. KPIs: collected today · collected this month · outstanding · collection efficiency · fully-paid/partial/unpaid counts. Charts: collection trend (area) · students by payment status (donut) · collection by mode (sorted bars) · outstanding by cohort (bars) · ageing 0–30/31–60/61–90/90+ (bars). Ageing keys off FeeInstallment.due_date, the only thing in the schema that knows when money was owed.

Chart colour. Mode and cohort charts are single-hue sorted bars — magnitude reads by length, so no categorical palette is needed and the 60-30-10 rule holds. The one multi-colour mark is the payment-status donut, which uses a status trio where colour carries meaning: #15803D / #F59E0B / #DC2626 in light and #16A34A / #FBBF24 / #F87171 in dark, both validated for colour-vision-deficient separation and surface contrast. Segments are direct-labelled.

Tests — apps/backend/tests/erp/test_fee_reports.py: apportionment sums exactly (property test); Unassigned bucket reconciles to gross; date range inclusive on paid_on, with a payment whose paid_on and created_at fall in different months; cancelled excluded; refunds netted; cross-tenant isolation; 403 without the grant and 404 with the flag off; CSV header and row shape; PDF returns real application/pdf bytes; svg_pie with zero total and with one slice. ReportsTab.test.tsx MSW-backed, per the generate-integration-test skill.

Rollout plan

Five PRs, each independently shippable, all branched from and targeting staging.

# Branch Contents
1 feat/erp-fee-paid-on Migration, paid_on through create/list, dialog date field
2 feat/erp-fee-report-engine reporting/ package, JSON + CSV + dimensions endpoints, tests
3 feat/erp-fee-reports-tab Reports tab UI + tab reorder
4 feat/erp-fee-report-pdf charts.py + pdf.py + export buttons
5 feat/erp-fee-analytics-tab Overview dashboard

PR 1 ships first and alone: every report number depends on paid_on being right, and it is the only irreversible piece. This doc lands with PR 1 and is cross-linked from it.

Post-deploy verification on staging, per environment: confirm the backfill populated paid_on for all existing rows, and that a report over June 2026 reconciles against the register for the same filters.

Found while designing

Two pre-existing defects surfaced while building the mockup. Neither belongs to this feature; both should be separate small PRs.

  1. apps/web/src/constants/chart-colors.ts — CHART_SERIES has duplicate colours. It declares 8 series slots, but info-500, warning-500 and success-500 are token aliases of primary-500, accent-500 and secondary-500. Only 5 distinct colours exist across the 8 slots, so any 6-series chart paints series 4 identically to series 1 — today, on every dashboard using it. Separately, its amber↔green adjacency fails protanopia separation (ΔE 5.7 against a floor of 8).
  2. Currency short-formatting rounds into the wrong unit. ₹99,99,500 renders as "100.0 L" instead of "1.00 Cr". Any money formatter used by the report, the PDF, or the existing dashboards needs a promote-on-round guard.

Open items for later PRs

  • WeasyPrint cannot run locally on macOS without its native libs (pango/cairo); the four existing test_exam_ops_hall_ticket_pdf.py tests fail with a 503 on a clean checkout. PR 4 needs either brew install pango locally or CI-only verification. Not a regression — confirmed pre-existing on an unrelated branch.
  • RecordPaymentDialog still uses useState for its fields, which the apps/web guide forbids (RHF + Zod is mandatory). PR 1 added the date field in the file's existing idiom rather than rewrite payment collection inside a migration PR. The RHF migration is owed as its own change.

Changelog

  • 2026-08-01 — PR 2 implemented (webapp#1472): reporting/ package with the collection and dues aggregates, pro-rata head apportionment (largest-remainder, 2000-case property test), cohort dimensions for both tenant shapes, the dimensions endpoint, and CSV export. Two corrections the code forced on this doc: registration-fee payments have no student_profile_id at all since webapp#1469, so they needed the Unassigned bucket the doc only anticipated for assignment-less payments; and CSV refuses an oversized export rather than truncating it, which is stricter than the "cap and disclose" the doc specified. Ageing buckets moved to PR 5, where the Overview dashboard is their only consumer.
  • 2026-08-01 — PR 1 implemented: erp0023 migration (column, backfill, composite index, up/down verified against a real database), paid_on through create/list/response, register date filter switched off created_at, "Date received" in the payment dialog, 5 new backend tests. Corrected during implementation: all date math moved to institution-local via src/erp/clock.py after the first pass used date.today() / CURRENT_DATE, which misdate the 00:00–05:30 IST window.
  • 2026-08-01 — Doc created. Gate passed; design accepted after interactive mockup review. Decisions locked: pro-rata head apportionment, paid_on column, live cohort join, fees staff tier, server-side PDF, builder without presets, tab reorder with Overview as landing.