Skip to content

Reporting — reports.embeddedera.com

Angel, 2026-09-12: "a dashboard with different reports that I can just click… top customer, most sold item, what we have in stock, errors, audits to the SOPs… business side and quality side, finances."

Reuse, do not build

Grafana (open source, self-hosted, one container in the stack) is the fit. It reads the ERP's database directly, keeps its own dashboards, alerts on thresholds, and — the deciding point — accepts identity from a trusted proxy header, so it sits behind the same Google gate with no second login. Metabase is friendlier for ad-hoc questions but its single sign-on is a paid feature; Redash and Superset are heavier for two people. Grafana teams map onto our departments, so Finance sees the money dashboards and Production sees the floor.

Data sources, all read-only: - a read-only Postgres user on the InvenTree database (never the API — reports must not cost the ERP anything, and SQL is what a report is); - the assistant sqlite (turns, admin actions, corrections); - the audit history (dated JSON files already written by sop-audit); - Cloudflare Access logs (sign-ins) and Uptime Kuma (service history); - later, the accounting tool, when it exists.

The catalogue, by audience

Business (Sales, Administration) - Top customers by invoiced value, this month / quarter / year. - Best-selling parts and assemblies, by quantity and by value. - Open sales orders and their age; what shipped this week. - Quote pipeline: pending orders that never became confirmed, and how long they sat. - Margin per order: sale price against the ERP's cost pricing (honest until accounting exists).

Operations (Production, Purchasing) - Stock on hand by category, with value; what is below minimum; what is in Receiving or Quarantine and for how long. - Open purchase orders, target dates, and each supplier's real lead time (issued → received). - Builds in progress, completed per week, and what each one was short of. - Stock accuracy: count adjustments over time (the shelf disagreeing with the system).

Quality (Engineering, Administration) - SOP audit trend: fails and warns per week, per SOP, from the dated audit files. - Build yield: test results per serial, first-pass yield per assembly. - Receiving discrepancies: shortages, wrong parts, damage, per supplier. - Returns and repairs, once they exist. - Part risk: not-recommended or end-of-life parts in active BOMs, from the distributor data. - Data quality: parts without image, supplier or datasheet — the same numbers the audit reports.

People and security (Administration) - Sign-ins by person and application; anything denied. - Assistant use per department, drafts confirmed versus corrected, refused requests, daily cost. - Access review: who can reach what, changes since last month.

System (Administration) - Backup runs and the last verified restore; disk on the pool; service uptime; sandbox state.

Finance (Finance, Administration) — blocked - Invoiced versus paid, aged receivables, monthly revenue and cost. Every one of these needs the accounting decision in embeddedera-business. The dashboard can be laid out now with the ERP half; the paid half stays empty and says why.

What is missing that you did not ask for

  1. Definitions. "Top customer" by invoiced, ordered or paid gives three different answers. Every report gets a one-line definition on the dashboard and the SQL is kept in git — a report is a document, so it gets a version like an SOP.
  2. History. Most of these need a time axis. The ERP keeps stock history and order dates; the audit already writes dated files; but cycle counts, scrap reasons and test results are only reportable if the procedures capture them. SOPs must be written with the report in mind: the receiving SOP records the shortage on the PO line because the discrepancy report reads it from there. Same principle as the audit, applied to data capture.
  3. Data that does not exist yet. Yield needs test results posted per serial; returns need return orders; lead time needs POs actually issued and received through the ERP. The first months of reports will be mostly empty, and that is the honest state.
  4. Who sees what. Grafana teams from departments.yml, same as the tiles and the SOPs. Finance numbers are not for Production.
  5. Alerts are reports that come to you. Low stock below minimum, an audit FAIL, a backup that did not run, a build stuck a week: each is a Grafana alert to ntfy, not a page someone must remember to open.
  6. A weekly digest, one email on Monday with the five numbers that matter, generated from the same queries. Dashboards get opened when something is wrong; the digest is how you notice.
  7. Cost of the reporting itself. A read-only database user and indexed queries; never the API in a loop. The ERP should not feel the reports.
  8. Export. CSV from any panel, for the CPA and for customers who ask for a statement.

Phases

  1. Foundation — Grafana in the stack, gated at reports.embeddedera.com with proxy-header sign-in, read-only DB user, teams from departments, and three dashboards that have data today: Stock, Sales orders, SOP audit. One day.
  2. Operations and quality — purchasing lead time, builds, receiving discrepancies, data quality, sign-ins, assistant use. Alerts to ntfy. Two days, spread as data appears.
  3. Weekly digest email from the same queries. Half a day.
  4. Finance — after the accounting decision.

Related: roles-and-skills, mrp, sandbox-workflow.

Log

  • 2026-09-12: plan written.
  • 2026-09-12: Phase 1 built — Grafana in the stack, gated at reports.embeddedera.com with header sign-in, read-only DB role, reports database fed by sop-audit, home tile. Dashboards Stock / Sales orders / SOP audit provisioned from files (in progress). Card grafana.
  • 2026-09-12: Dashboards live at reports.embeddedera.com — Operations/Stock, Business/Sales orders, Quality/SOP audit (23 SQL panels, definitions panel on each, all verified error-free on today's mostly-empty data). Sales status codes documented in the SQL (PENDING 10, IN_PROGRESS 15, SHIPPED 20, ON_HOLD 25, COMPLETE 30). Next: phase 2 (purchasing lead time, builds, discrepancies, sign-ins, assistant use, alerts to ntfy) as data appears; weekly digest; finance after accounting.
  • 2026-09-12: Dashboards verified in a real browser as a signed-in viewer (the API path passed while every panel errored): fixed jsonData.database, panel type bar→barchart, and the string-valued grade stat (reduceOptions.fields: "/.*/" + value mappings). Lesson for the dashboard rule: verify in the browser as the identity people use, not only through the API as admin.

  • 2026-09-12: Drill-down and access. sop-audit now records every offending item (audit_items), and the SOP audit dashboard has a check variable and a "Failing items (latest run)" table; clicking a check id in the failing-checks table filters it. Access is by department: reports: list in roles/departments.yml → reports-sync (teams named after departments, folder permissions replaced so the org-wide Viewer role grants nothing, Administration = Grafana admin), called by onboard and hourly by cron. Proven: Yrosado sees Business + Operations only; Angel sees all six folders. Angel's earlier "No data" was the datasource field bug plus a single run in the history — both explained.

  • 2026-09-12: SOP audit dashboard made navigable — sop and chained check variables, click-through data links on SOP and check cells, three collapsible rows (Overview / By SOP / Details, the last folded by default), a "How to use this page" panel. Verified in the browser for SOP-002 and SOP-009 filters. Pattern for every dashboard: variables + data links + rows, verified as a viewer.