Reporting & Analytics

SeedTrust has two layers of reporting: an internal Flask reporting module (operational and financial reports) and PostHog (user analytics and session replay).

Flask Reports

All reports live in seedtrust_flask/seedtrust/views/reports.py (~2,500 lines). They are protected Flask routes returning JSON or CSV data. Most use raw SQL via pandas for performance — they bypass the SQLAlchemy ORM entirely.

Do not add new reports to reports.py without understanding the full query context. This file contains complex aggregations against financial data. A poorly written query can cause slow page loads or incorrect financial figures.

Report Endpoints

Endpoint	What It Reports
`/reports/sales`	Sales performance metrics by agency and admin
`/reports/sales/noadmin`	Sales data without admin segmentation
`/reports/sales/agency/cases`	Case counts grouped by sales agency
`/reports/omega-insurance`	Transactions filtered to Omega Insurance payees
`/reports/cases/life-span`	Duration metrics for each case (open to close)
`/reports/cases/monthly-balance`	Case account balances grouped by month
`/reports/case/case-monthly-balance`	All cases’ monthly balance detail
`/reports/cases/monthly`	Monthly case activity metrics
`/reports/cases/deposits`	Monthly deposit transactions
`/reports/cases/deductions`	Monthly deduction transactions
`/reports/cases/debits`	Monthly debit transactions
`/reports/cases/trends/monthly-allowance`	Trend analysis of monthly allowances
`/reports/cases/trends/base-comp`	Trend analysis of base compensation totals
`/reports/agency/state`	Agency distribution by US state
`/reports/surrogate-ip/state`	Surrogate and IP distribution by state
`/reports/cases/never-funded`	Cases that were created but never received funding
`/reports/cases/all-time-life-span`	Historical case duration statistics
`/reports/case/starting-fund-amount`	Initial funding amounts per case
`/reports/case/deposits-at-contract-upload`	Deposits at the time of escrow agreement signing
`/reports/case/deposits-at-heartbeat`	Deposits at heartbeat confirmation milestones
`/reports/case/deposits-at-base-comps`	Deposits at base compensation milestones
`/reports/case/total-funds-at-end`	Total funds remaining at case close
`/reports/case/case-averages`	Average financial metrics across all cases
`/reports/agency/agencies-using-agency-corprate-card`	Agencies paying via corporate card
`/reports/cases/monthly-inflow-outflow`	Cash flow: deposits vs. disbursements by month
`/reports/vendor/transactions`	Transaction totals by vendor/payee
`/reports/case/idaho-monthly-balance`	Idaho-specific monthly balance report
`/reports/case/incoming-deposits`	Deposits through end of 2025
`/reports/case/outgoing-disbursements`	Disbursements through end of 2025
`/reports/cases/number-of-surrogacy-egg-donation-cases`	Case type breakdown by month
`/reports/cases/active-as-of-5-30-2024`	Point-in-time snapshot of active cases (May 2024)

Async Report Generation

For heavy reports, SeedTrust uses an async queue system:

Admin requests a report → ReportRequest record created with status = 1 (pending)
Flask cron job (job_report_generation) polls every minute for pending requests
Job picks the oldest pending request → sets status = 2 (in progress)
Report generated (may involve complex queries or external API calls)
Report emailed to the requesting Admin → status = 3 (complete)

This prevents long-running report queries from timing out HTTP requests.

Statistics Module (`seedtruststats.py`)

A separate module that powers dashboard widgets and analytics views. It uses pandas with raw SQL for performance and supports parametrized queries with list/set expanding.

Key functions:

load_disbursement_requests_for_agency() — DRs by agency (last 32 days by default)
load_disbursement_requests_for_admin() — DRs by admin role
load_disbursement_requests_for_all_admins() — DRs across all admin roles

All functions include both current-state and historical queries (via disbursement_request_history table) for cases that predate the history tracking system.

PostHog Analytics

PostHog provides user behavior analytics, session replay, and error tracking across all three services.

Event tracking: User actions (case views, DR submissions, payment completions) are captured as PostHog events
Session replay: Records user sessions for debugging and UX analysis. Privacy settings: all inputs masked, no images/videos/iframes recorded. Session recording only enabled with user consent.
Error tracking: Uncaught exceptions in FastAPI and Next.js are captured and sent to PostHog
Feature flags: PostHog is used for some feature flag evaluations (e.g., routing-number-verification-disabled)

Config: POSTHOG_PROJECT_API_KEY env var, seedtrustapi/src/seedtrust/core/posthog.py, app/src/posthogLogs.ts

Gotchas for Developers

reports.py uses raw SQL, not the ORM. This means changes to the database schema (column renames, table changes) will silently break report queries — they won’t raise model validation errors. Always check reports.py after a schema change.

Several report endpoints have no explicit auth check. Most use @api_authcheck() but some do not. This is a known issue — these endpoints should be considered internal-only until properly secured.

Some reports are point-in-time snapshots. Reports like active_cases_as_of_5_30_2024 are hardcoded queries for a specific historical date. They are not dynamic — they exist for one-off business analysis and should not be used as live operational data.

Open Questions

1. Report permissions Several report endpoints appear to have no explicit permission check (only basic auth). Which admin roles should have access to financial reports like financial_stats and monthly-inflow-outflow? This should be enforced before the Flask-to-FastAPI migration.

2. Report format Do the report endpoints return JSON, CSV, or both depending on query params? A consistent output format spec would help when building report consumers.