Madisons - Reporting Solution Framework
A comprehensive reporting solution framework supporting flexible data sources, configurable report definitions, and scalable rendering for business teams.
The challenge
Deliver a specialised reporting framework for the lumber industry that consolidates fragmented pricing data, product catalogs, and analyst commentary into a unified, drillable dashboard. The client needed flexible data ingestion across multiple file formats, role-gated access, and the ability to trend pricing by product taxonomy without hard-coding every report variant. Imports of large historical files had to run reliably without dropping rows or timing out.
What we built
A two-tier web application - an Angular 9 SPA paired with a Node.js + Express API on MySQL - with a powerful ETL backbone. The backend ingests product metadata, historical and weekly prices, and analyst commentary via structured XLSX / CSV imports, normalising each into separate Sequelize models with type-specific validation. Nine core entities support real-time price queries and aggregations, while the frontend renders responsive dashboards with Chart.js for trending and ag-Grid for deep exploration, with toggleable filters by species and category. Admin users drive ingestion, provisioning, and bulk email; end users query trends and read analyst notes in context.
Key features
Data ingestion & admin
- Multi-type XLSX / CSV import in a single endpoint: product catalog refresh, weekly price delta, narrative commentary
- Commentary import does cross-table lookups to resolve species / category / keyword names to IDs before bulk-insert
- Bulk user provisioning with role assignment (Admin, User) and subscription tier (Basic, Premium)
- Admin-gated content management tied to product taxonomy (species, category, keyword)
- Bulk email dispatch with i18n template support for multi-region customers
Pricing data model & queries
- Weekly price aggregation with week-over-week delta, percentage change, and dual unit support (USD/MBF, CAD/MSF)
- Product attributes enriched with 25+ metadata fields (dimensions, grades, source/destination, price tiers)
- Single-product price lookup, bulk multi-product queries, and category-scoped drill-downs
- Two-year historical trending with YoY comparisons baked into the API response
Dashboard & reporting (Angular)
- Top-6 movers widget by price change percentage
- Interactive line charts with dual y-axes for unit and currency comparisons
- Real-time search and filter by product name, category, and species with type-ahead
- Commentary view showing analyst notes keyed to product taxonomy and time period
- ag-Grid power-tables for deep exploration of large datasets
Access control & usage
- JWT-based sessions with bcrypt password hashing and per-endpoint auth guards
- Dual-axis authorisation: subscription tier (feature gates) plus role (endpoint guards)
- User geolocation logging on login (city, state) for usage analytics
- Paranoid soft-delete on every table for audit trails
Challenges solved
Flexible ETL pipeline, three modes, one endpoint
History load, weekly price update, and product master refresh share one import surface with mode-specific field mapping and cross-table reference resolution - preventing FK errors on commentary that references species or categories not yet imported.
Decimal-based pricing precision
DECIMAL(10,4) storage with Sequelize getters returning parsed floats; week-over-week delta, percentage change, and dual currency / unit systems live side by side for global market alignment.
Paranoid soft-delete with full-reload semantics
Paranoid timestamps give audit history by default, but product-master refresh switches to truncate + force to fully wipe and reload without orphaning linked data - imports stay idempotent.
Timeout-resilient imports
Heavy XLSX history files override the Express request timeout (req.setTimeout(0)) so multi-thousand-row imports don't get killed mid-parse.
Composable price aggregations
Top-6 movers, two-year aggregates, and per-category breakdowns come back from a single product_prices query; client-side filtering with lodash avoids redundant API calls for interactive drill-downs.
Subscription + role-driven access
subscription_type gates feature access while role gates endpoints - business logic stays decoupled from auth middleware, which keeps tier-based upsells simple to ship.
i18n templates for multi-region rollout
Email content and error messages load from translation.en.json via jm-ez-l10n, so adding a locale is a config change instead of a code change.
Outcomes
Configurable
Report builder
Pluggable
Data sources
Production
Scale
Have a product idea or a system to scale?
Tell us what you're building. You'll hear back within one business day - from a senior engineer, not a sales rep.
- Free 30-min discovery call
- Fixed-scope or T&M engagements
- NDA on request - first reply within 24h