Status: π’ Fully live, in-house. The native Reports module is real code end
to end β ~40 report specs, a uniform sortable/filterable/drill grid, native
Chart.js charts, CSV/XLSX/PDF + official-filing exports, snapshot trends, an AI
narrative panel, scheduled email bursts, and an owner-only governance audit β
all reading live org data throughapp/services/reporting.py(2,600+ lines of
SQLAlchemy roll-ups). It replaced Metabase, which was retired 2026-06-09. The
only π‘ edges are sub-capabilities that degrade gracefully (AI narrative needs an
LLM key; official filings need review before transmit).
Status badges: π’ Live Β· π‘ WIP / config-gated Β· βͺ Dormant Β· β Dead-end / vaporware
House terms: Job (a project), Client (the paying customer). Crew/Sub = a
subcontractor or crew. Every query is org-scoped (organization_id).
Cross-link: for how this module sits inside the hub shell alongside the folded
finance panes (Invoices, POs, Cost Inbox, Cost Codes, Lien Waivers, Signatures),
see Reports & Finance Command Center.
Audience: eng, product.
Reporting used to be a Metabase deployment: SQL "questions" run against
snapshot tables (mb_lead_sales, refreshed ~every 30 min) and an embedded
dashboard iframed onto /portal/reports via a signed JWT (metabase_dashboard_id
setting). That whole path is gone as of 2026-06-09 and lives only as history in
docs/metabase-lead-cards.md:1 (SQL for the old lead cards) β treat any
docs/metabase-*.md or docs/job-costing.md:52 Metabase reference as stale.
The replacement is a native, hub-owned module: no external BI server, no signed
embeds, no snapshot-table dependency. Every report is a declarative spec rendered
through one consistent viewer, reading the same live SQLAlchemy roll-ups the rest of
the app uses β so the numbers on a report always match the numbers on the page they
came from. What Metabase gave up (a fully sliceable ad-hoc query builder) is traded
for governance, consistency, and zero infra.
| Old (Metabase) | New (native Reports) | |
|---|---|---|
| Data source | mb_* snapshot tables (~30 min stale) |
Live roll-ups, real-time |
| Embed | Signed-JWT iframe on /portal/reports |
Native pages, no iframe |
| Auth | Metabase user + org param | require_portal_role(pm, office) |
| Add a report | New SQL question + dashboard | Add one ReportSpec |
| Export | Metabase CSV | CSV / XLSX / PDF + CRA/IRS filings |
| Governance | β | Per-view/drill/export audit log |
Audience: eng.
The declarative heart is the ReportSpec dataclass (report_specs.py:48). A
single spec ties together the catalog card, the viewer, the data grid, every export
format, the optional chart, the drill-down and the AI narrative β so adding a
report is adding a spec, not new UI (report_specs.py:1).
Every build() / drill() returns the uniform result shape (report_specs.py:9):
{ "columns": [{"key","label","type","align","cond"}],
"rows": [{<col>: value, ..., "_href"?, "_drill"?}],
"totals": {<col>: value} | None,
"kpis": [{"label","value","type","tone"}],
"group_by": <col key> | None,
"meta": {"title","as_of","currency", ...} }
type: text | money | pct | num | int | date.cond: overunder | posneg | invvar | days β drives red/green conditional cell_href makes a row click through (e.g. to the Job); _drill marks a row asThe registry: _SPECS list (report_specs.py:1489-1726) β REGISTRY dict
(report_specs.py:1728), looked up by get_spec(id) (report_specs.py:1731). The
engine each build calls is app/services/reporting.py β the roll-up functions
(job_costing, wip_report, ar_aging, pipeline, β¦). report_specs._build_*
functions are thin adapters that map engine output into the uniform shape.
Router: app/routers/reports.py, prefix /portal, gated
require_portal_role(pm, office) for the whole router (reports.py:29).
Audience: staff (pm/office), product, sales.
GET /portal/reports renders the catalog (reports_hub.html) inside the shell
(reports.py:60); ?classic=1 serves the old flat catalog (reports.py:67).
Reports are grouped into seven categories (report_specs.py:37): Executive,
Financial, Projects, Sales & Marketing, Field & Labor, Safety, Compliance. Reports
flagged featured=True surface in a top band.
The full registry (report_specs.py:1489), by category:
| ID | Title | What it computes |
|---|---|---|
wip β |
WIP & Holdback | % complete, earned revenue, over/under-billing, Canadian holdback (report_specs.py:1491) |
job-costing β |
Job Costing | Per-Job projected cost, profit, margin, fade β worst fade first (report_specs.py:1499) |
ar-aging |
AR Aging | Unpaid invoices bucketed by days past due (report_specs.py:1508) |
estimate-vs-actual |
Estimate vs Actual | Planned budget vs actual cost by category (report_specs.py:1514) |
cash-flow β |
Cash Flow | Billed vs collected by month, uncollected (report_specs.py:1520) |
holdback |
Holdback & Prompt-Payment | Statutory holdback, release dates, Ontario 28-day clock (report_specs.py:1577) |
t5018 |
T5018 Contract Payments | CRA sub-payment return + XML/slips [CA] (report_specs.py:1583) |
us-1099 |
1099-NEC | US twin of T5018 [US] (report_specs.py:1662) |
change-orders |
Change Order Log | Cost & schedule impact, % of contract (report_specs.py:1601) |
evm |
Earned Value (CPI/SPI) | BCWS/BCWP/ACWP, CV/SV, CPI/SPI per Job (report_specs.py:1630) |
margin-by-phase |
Margin by Phase | As-sold cost vs price by phase (report_specs.py:1640) |
aia-payapp |
Applications for Payment | AIA G702/G703 schedule of values (report_specs.py:1719) |
sub-commitment (:1606), schedule-variance (:1611), look-ahead (:1616),
submittals (:1651), rfis (:1656), punch (:1677), warranty (:1687),
selections (:1692), bids (:1698), vendor-scorecard (:1712).
pipeline (:1530), sales-source (:1536), sales-winloss (:1542),
sales-velocity β (:1548), mkt-email (:1558), mkt-attribution β (:1565).
These reproduce the old Metabase "Leads & Sales" cards natively via
reporting.lead_sales_rows (reporting.py:769).
crew-productivity (:1571), labor-productivity (:1624), daily-logs
(:1682), timecard (:1703).
portfolio-health (RAG, :1635) Β· safety (TRIR, :1645) Β· compliance-expiry
(:1596) Β· lien-waivers (:1671).
Audience: staff (pm/office), eng.
GET /portal/reports/r/{id} (reports.py:90) renders the viewer
(report_view.html, which extends reports_shell.html β no iframe, same rail
and theme as the rest of the hub). One report can point view_url at a rich
purpose-built page instead of the generic viewer (the draggable cash-flow graph
/portal/cashflow, the WIP/holdback dashboard /portal/reports/wip, the
job-costing grid /portal/job-costing) so folding Finance into Reports doesn't
flatten those (report_specs.py:66).
The viewer surfaces several endpoints:
.../data.json (reports.py:113) powers client-side.../data (reports.py:132)..../drill?row=<key> (reports.py:149) stays server-side so_drill key expands to aspec.drill(db, org_id, params, row_key). Example drills: WIP /_drill_job_costing, report_specs.py:203); AR Aging drills to invoice line.../chart (reports.py:186) returns Chart.js JSON for specschart config (bar/line over categorical rows)..../trends (reports.py:203) plots each KPI's history from.../insights (reports.py:164); see Β§8.resolve_params (report_specs.py:1775). UsersUser.report_prefsPOST /portal/reports/prefs (reports.py:253).Audience: eng, finance-minded staff.
Every roll-up filters by organization_id (or job.organization_id) β there is no
cross-org leakage path. The backbone is job_costing(db, job, prefs)
(reporting.py:49), which reconciles contract value against cost. The formula:
Contract value (reporting.py:64)
base_contract = contract_override (job.contract_value_cents / 100) # if set (fixed-price)
| Ξ£ estimate.total # else as-sold estimates
contract_value = base_contract + Ξ£ approved change-order cost_delta
Actual cost = labor + bills + equipment:
reporting.py:68): Ξ£ shift.hours Γ crew_rate(sub, default_rate),crew_rate (reporting.py:43) uses the crew's hourly_rate or the orglabor_rate pref.reporting.py:82): only approved/paid bills count_ACTUAL_BILL_STATUSES, reporting.py:40), bucketed material / subcontractor /cost_breakdown.reporting.py:96): owned-gear EquipmentJobAssignment.cost β rollsCommitted (reporting.py:104) = open POs not yet billed.
Projected cost (reporting.py:131):
projected_cost = max(planned_cost, total_cost + committed) + co_cost
where planned_cost = budget (if set) else estimate cost basis, and co_cost is
approved change-order builder cost not already carried by a non-draft PO (avoids
double-counting, reporting.py:107).
Margin & fade (reporting.py:128):
margin = contract_value β total_cost # cost-to-date margin
planned_margin = contract_value β planned_cost # as-sold
projected_margin = contract_value β projected_cost # at completion
fade = projected_margin% β planned_margin% # βve = profit fading
The per-cost-code grid job_costing_by_code (reporting.py:168) is the drill
target and the Buildertrend-style "Job Costing Budget" view: one row per cost code
reconciling original budget β revised budget (+ approved CO scope) β committed β
actual β forecast β projected β variance, with uncoded costs and time-clock labor in
their own rows. Portfolio views layer on top: org_reports (:396), wip_report
(:520), estimate_vs_actual (:607), ar_aging (:656), pipeline (:693),
monthly_cashflow (:843).
Audience: staff (pm/office), finance.
GET /portal/reports/r/{id}/export?fmt= (reports.py:219). One path
(app/services/report_export.py) turns any uniform result into an export, driven
entirely by the column schema β so every report exports consistently and new
reports get export for free:
to_csv (report_export.py:28), UTF-8 BOM so Excel reads it correctly.to_xlsx (openpyxl).report_pdf, reusing the WeasyPrint pipeline with a branded letterhead._EXPORT_MEDIA (reports.py:212).Custom / official exports take precedence over the generic formats via a spec's
extra_exports (report_specs.py:60). Today the T5018 report attaches a CRA XML
return and printable slips PDF (report_specs.py:1591, builders at
report_specs.py:661-668 β cra_t5018.py). These generate the file only β they do
not auto-transmit; validate against CRA's current-year .xsd before filing.
Every export is written to the audit log (reports.py:248).
Audience: eng, staff.
app/services/report_snapshots.py persists each report's numeric KPI headline per
org once a week (keyed to the Monday of the capture week, idempotent per
(org, period)). The viewer's trend endpoint (reports.py:203) then draws a line
per KPI plus the period-over-period delta. Capture is driven by the in-app scheduler
loop capture_report_snapshots (scheduler.py:78, called in the main loop at
scheduler.py:151). This is the native replacement for what a BI tool's historical
dashboards would have shown.
Audience: staff, eng.
The insights endpoint (reports.py:164) attaches a plain-English read of the report.
Numbers are always deterministic (grounded in the roll-up); the LLM only narrates
punchy headlines and prioritised cards on top:
costing_signals (rule-based, reporting.py:865) narrated bycosting_insights / portfolio_costing_insights (reporting.py:959, :1193).wip_insights (reporting.py:1078); everything else βgeneric_report_insights (reporting.py:1256).Degrades gracefully: with no LLM key (or on any AI error) it falls back to the
deterministic rules-only narrative β the endpoint swallows failures and returns empty
rather than breaking the report (reports.py:180, reporting.py:1006). Results are
cached ~15 min per Job. This is the only π‘ sub-capability in the module and it never
blocks a report.
?embed=1 π’Audience: eng, product.
There are no BI iframes left β the report viewer renders natively. The one live
iframe mechanism is the Home dashboard bare-render: GET /portal?embed=1
(app/portal/router.py:262) renders the dashboard through embed_base.html instead
of the full app.html shell, so it can be dropped into a command-center pane without
the app chrome. app.html also strips chrome client-side by adding the ks-embed
class when ?embed=1 is present (app.html:8-16) β topbar, sidebar, tabbar and AI
bar are hidden.
Note the honest nuance: the shell's Overview rail button currently links
directly to /portal (reports_shell.html:157) rather than iframing the
?embed=1 bare version β the embed render is a real, wired capability, but the
Overview entry is a plain link today. The dashboard KPI tiles themselves deep-link
into reports (/portal/reports/r/job-costing, .../cash-flow, .../ar-aging,
.../sales-velocity, .../sales-winloss β partials/dash/kpi_scoreboard.html).
Audience: staff (pm/office), eng.
app/services/report_digest.py sends a report on a cadence. Manage at
GET /portal/reports/subscriptions (reports.py:307); subscribe from a report via
POST /portal/reports/r/{id}/subscribe with cadence (daily/weekly/monthly) +
recipients + captured params (reports.py:323). Toggle / delete / send-now-test
at reports.py:337-373.
Delivery is a digest email β the KPI headline + Keystone's plain-English read +
a link to the live report β deliberately not a data attachment, so data stays
fresh and nothing sensitive sits in an inbox (report_digest.py:1). Driven by the
scheduler loop run_report_subscriptions / run_due_digests (scheduler.py,
report_digest.run_due), idempotent per day via last_sent_on (report_digest.py:_due).
Audience: owner, eng, compliance.
Every view / drill / export is recorded (best-effort, never breaks a request) by
app/services/report_audit.py β report_audit.log(db, user, spec.id, action, fmt, detail) at reports.py:123, 141, 157, 234, 248. The audit page
GET /portal/reports/audit is owner-only (reports.py:379, redirects non-owners
at reports.py:384) and lists who touched which report, when, and in what format.
Audience: eng, product, sales.
reports key inentitlements.FEATURES (entitlements.py:23), so the catalog is not hidden bypm or officerequire_portal_role(pm, office), reports.py:29). The field role isvisible_specs returns [] for fieldreport_specs.py:1739). Field workers only feed the system (time-clock,t5018 shows only for ca, us-1099 only for us (countries field checked invisible_specs, report_specs.py:1742).reports.py:384).safety/payroll features are tier-gated in navvisible_specs filters on role + country only. Sosafety| Role | Reports access |
|---|---|
| owner | Everything, incl. the audit log. |
| pm / office | Full catalog, viewer, drill, exports, subscriptions (no audit log). |
| field | β None β visible_specs returns []. |
| client / vendor | β None β no portal-role access to /portal/reports. |
org_reports / wip_report call job_costingsub_cache β has this been profiled on orgs withreporting.py:107)?report_digest) invisible_specs, or is role+country