Financial, stock, and operations —
unified into one operating layer.
A commercial electrical contractor running invoicing in Xero, stock in Cin7, and jobs, crews, and materials tracked manually in Excel — with no central view of job profitability, crew utilisation, or delivery risk.
We centralised all systems into a single Postgres data model and built two layers on top: a commercial reporting layer and a live operational layer covering jobs, labour, crews, and material cost exposure.
Note: InventoryCo Ltd is a fictionalised example. All data is generated and illustrative. The architecture and approach reflect a realistic engagement.
Built using real-world system architecture — Xero API · Cin7 API · Postgres · Supabase
Client
InventoryCo Ltd (sample)
Revenue
~£3–5m / year
Sector
Commercial Electrical / Lighting Installation
Stack
Xero · Cin7 · Postgres · Supabase
Approach
API extraction · SQL modelling · operational reporting layer
100% custom-built
No templates, no off-the-shelf tools. Every pipeline, schema, and view was designed around InventoryCo's specific systems and workflows.
Specific integrations
Xero and Cin7 APIs connected using InventoryCo's exact data models — product IDs, invoice structures, and inventory schema mapped precisely.
Logic built to their rules
Reorder thresholds, margin calculations, and alert triggers were defined by the client — then automated. Their rules, running automatically.
The Problem
Three systems.
No operational visibility.
As the business grew from supply-only to supply-and-install, the reporting problem compounded. Financial data lived in Xero, stock in Cin7, and every operational record — jobs, crews, labour hours, material allocations — sat in Excel. Leadership had no joined-up view of whether projects were delivering on margin, whether crews were overcommitted, or where costs were running over.
Systems in use — before
Invoice and revenue data. API available but only used as manual CSV exports in practice. No linkage to job or delivery data.
Exports triggered manually — already days old before anyone read them
Product catalogue, SKU-level stock positions, purchase orders, and supplier data. Not linked to jobs or install projects.
Completely siloed — stock not tied to job material requirements
Used for everything not covered by Xero or Cin7: job tracking, crew allocation, labour hours, material usage, and the weekly management report — all in separate spreadsheets.
No single Excel model — multiple files, no version control, rebuilt weekly
~5 hours per week
consumed by manual reporting — pulling exports, reconciling figures, and formatting before a single number could be shared.
Zero job cost visibility
Labour and material costs were not linked to jobs — management had no way to know whether a project was delivering on margin until weeks after completion.
Architecture
The data architecture
A structured pipeline connects every source system — financial, inventory, and operational — into a single Postgres database, with commercial and operational reporting layers built on top.
Source systems
Xero
API / OAuth
Cin7
REST API
Excel
Structured upload
Ingestion Layer
Each source system is polled on a defined schedule. Raw data lands in staging tables before any transformation — preserving a full audit trail of every ingestion run.
Centralised Database
Postgres · SupabaseThe single source of truth. All source data stored in a structured relational schema — jobs, crews, labour, materials, invoices, products, and inventory — joined by consistent keys.
ic_jobs
ic_labour_entries
ic_materials
ic_sales
ic_products
ic_inventory
ic_crews
views
Transformation Layer
Raw tables are transformed into clean, analysis-ready views. Calculated fields include gross margin per SKU, job profitability, labour variance, crew utilisation, material cost exposure, and inventory cover — all computed as SQL views.
Commercial Reporting Layer
Commercial reporting outputs — revenue, profit, margin, inventory — served from transformed views. Always current, no manual assembly.
Operational Insight Layer
Built on the same model, the operational layer surfaces job status, crew capacity, labour budget variance, and material cost exposure — giving management delivery control alongside financial visibility.
Deliverables
What was built
Five components — each removing a layer of manual dependency and replacing it with an automated, reliable process. The result is a single operating layer covering both commercial and delivery performance.
API-connected data extraction
Xero and Cin7 data is pulled on a scheduled basis via their respective APIs. Job, labour, and material data is ingested via structured uploads from operational spreadsheets. All ingestion is logged and auditable.
Centralised Postgres database
All extracted data lands in a structured Postgres schema (Supabase-hosted). Tables are designed around business entities — jobs, crews, labour entries, materials, invoices, products, and inventory — with foreign key relationships defined throughout.
Transformation & data modelling
Raw ingested data is cleaned, deduplicated, and standardised. Calculated fields — job margin, crew utilisation, labour variance, material cost exposure, inventory cover days — computed and stored as SQL views ready for reporting.
Commercial reporting layer
The commercial layer provides KPI views, product-level margin breakdowns, revenue trend, and inventory alert logic — all from one consistent source, always current.
Operational insight layer
Built on the same data model, the operational layer surfaces job status, crew utilisation, labour overruns, material cost variance, and delivery risk — giving management real-time visibility over delivery as well as financials.
Insight Layer
What the business can now see
These outputs are served from the centralised data model. Every figure is computed from the same underlying source — no manual assembly, no version conflicts.
InventoryCo · Financial Operating Layer
Revenue
£487,700
6-month total
Cost of Sales
£349,404
direct costs, 6 months
Gross Profit
£138,296
after direct costs
Gross Margin
28.4%
blended, all products
Revenue & Profit Trend
Oct 2025 – Mar 2026 · auto-generated from data model
Financial Insights
Revenue grew 83% over 6 months — from £54k to £100k per month.
Growth is real and tracked from the same data model. No manual reporting needed to see this trend.
Gross margin at 28.4% — Emergency Exit Sign (17.8%) and Commercial Batten (22.4%) are pulling the blended margin down.
Two product categories are underperforming on margin. Pricing or mix adjustment would improve blended profitability.
Material cost variance of +6.8% across active jobs is reducing job-level profit on current portfolio.
Two jobs are tracking over labour and material estimates. Margin erosion is visible now — not at job close.
This is the level of financial and operational visibility we build for our clients.
Revenue, margin, cost, and inventory — from one centralised data model. Updated automatically. No manual reporting. No version conflicts.
Identify margin issues before they impact profitability
Labour overruns and material cost variance are surfaced as they happen — not discovered weeks after job close.
Spot operational bottlenecks early
Crew capacity, job risk, and delivery timelines are visible in real time — giving management time to act.
Make staffing and inventory decisions with confidence
Crew utilisation and material availability are tracked automatically — no phone calls, no manual tally.
The Transformation
Before vs After
Before
Reporting
5+ hours per week, built manually in Excel
Job visibility
None — profitability only known weeks after completion
Cost tracking
Labour and materials not linked to jobs
Crew status
Managed by memory and phone calls
Data source
3 disconnected systems — Xero, Cin7, Excel
Alerts
Stock and delivery issues surfaced in manual reviews
After
Reporting
Automated — ready in under 30 minutes, no manual assembly
Job visibility
Live — margin tracked in real time as labour and materials are logged
Cost tracking
Labour and materials joined to jobs — variance visible immediately
Crew status
Live utilisation, capacity, and allocation tracked automatically
Data source
One centralised Postgres model — every report from the same source
Alerts
Automated flags for at-risk jobs, overruns, and low stock
Operating Model — After
One version of the truth
One central data model
Xero, Cin7, and Excel all feed into one Postgres database. Every report, KPI, and alert — commercial and operational — draws from the same source.
Automated pipelines
Scheduled API pulls replace all manual exports. Data arrives, is staged, cleaned, and loaded without anyone needing to trigger it.
Commercial visibility
Revenue, margin, and stock data is current within the refresh cycle. No more manually compiled weekly reports.
Operational control
Job status, crew utilisation, labour budget variance, and material cost exposure are visible in real time — days before they would have surfaced manually.
Job profitability — live
Labour entries and material allocations are now joined to job budgets. Management sees margin per job as it is earned, not weeks after completion.
Scalable architecture
The schema is designed to accommodate additional crews, job types, or reporting requirements without a structural rebuild.
Results
Indicative outcomes
Figures are illustrative, based on typical engagements of this type.
< 30 min
Weekly reporting time
Was ~5 hours manual
100%
Job margin visibility
Previously unavailable
Live
Crew & labour tracking
Was weekly manual timesheet
1
Source of truth
Was 3+ disconnected systems
Reporting time: 5 hours → 30 minutes
Weekly management reporting now runs automatically — no exports, no copy-paste, no manual reconciliation.
Job margin visibility: unknown → live
Labour and material costs are now joined to jobs in real time. Management sees margin per project as it is earned, not weeks after completion.
Crew utilisation: tracked manually → live
Crew hours, capacity, and allocation are now visible at any point — no more reliance on phone calls or memory to manage workload.
One consistent source of truth
Every report, KPI, and alert draws from the same Postgres model. No version conflicts, no figures that don't reconcile.
Summary
“Every business has different systems, different operations, and different decisions to make. That’s why nothing here was adapted from a template — it was designed specifically for how InventoryCo operates, across both their commercial and delivery model.”
Quantyx Advisory · Sample engagement · Illustrative
Custom build
We'll build something this specific for you.
Every engagement starts from scratch — your systems, your data, your rules. In a free 30-minute call we'll map your setup and show you exactly what a bespoke solution would look like.