WIP, billing, and matter profitability —
unified into one partner view.
A mid-sized UK law firm running matters in Clio, invoicing in Xero, and WIP reconciliation manually in Excel — with no clear view of matter profitability, billing delays, or write-off risk.
We centralised all three into a single Postgres data model and built a partner-facing insight layer covering WIP, matter margin, timekeeper utilisation, and billing performance.
Note: Lex & Co LLP is a fictionalised example. All data is generated and illustrative. The architecture and approach reflect a realistic engagement.
Built using real-world system architecture — Clio API · Xero API · Postgres · Supabase
Client
Lex & Co LLP (sample)
Revenue
~£5–10m / year
Sector
Legal Services · LLP
Stack
Clio · Xero · Postgres · Supabase
Approach
API extraction · SQL modelling · partner reporting layer
100% custom-built
No off-the-shelf dashboards or generic reporting tools. Every pipeline, schema, and view was designed around Lex & Co's specific systems, matter structure, and fee-earner model.
Specific integrations
Clio and Xero APIs connected using the firm's exact data models — matter IDs, timekeeper rates, invoice structures, and payment terms mapped precisely into the schema.
Logic built to their rules
Recovery rate thresholds, WIP aging bands, utilisation targets, and write-off alert rules were defined by the partners — then automated. Their rules, running automatically.
The Problem
Three systems.
No view of profitability.
As the firm grew in headcount and matter volume, the reporting problem compounded. Time was recorded in Clio, invoicing handled in Xero, and WIP reconciliation done manually in Excel every week. Partners had no joined-up view of matter margin, billing delays, or which matters were at risk of write-off — until it was too late to act.
Systems in use — before
All matter records, time entries, and fee-earner data lived in Clio. API available but only accessed manually for periodic exports. No live link to billing or financial data.
Time recorded but not reconciled against budget — no visibility over recovery
All invoicing, payment tracking, and accounting ran through Xero. Not linked to matter records or timekeeper data — revenue and time data lived in completely separate systems.
No way to reconcile billing to time recorded — matter margin unknown
WIP reconciliation, billing delay tracking, and the weekly management report were all built in Excel — pulling exports from Clio and Xero, reconciling manually, formatting for partners.
Rebuilt weekly, inconsistent month-to-month, no version control
~4 hours per week
consumed by manual WIP reconciliation and reporting — pulling exports, reconciling figures, and formatting for partner review before a single number could be discussed.
Zero matter profitability visibility
Time and billing were not joined — partners had no way to see whether a matter was delivering on margin until weeks after the work was complete.
Architecture
The data architecture
A structured pipeline connects every source system — practice management, invoicing, and manual WIP records — into a single Postgres database, with a partner-facing insight layer built on top.
Source systems
Clio
REST API
Xero
API / OAuth
Excel
Structured upload
Ingestion Layer
Clio and Xero are polled on a scheduled basis. 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 — matters, timekeepers, time entries, invoices, payments, WIP, and adjustments — joined by consistent keys.
lc_matters
lc_time_entries
lc_invoices
lc_payments
lc_timekeepers
lc_wip
lc_write_offs
views
Transformation Layer
Raw tables are transformed into clean, analysis-ready views. Calculated fields include WIP age, matter margin, realisation rate, billing delay, timekeeper utilisation, and write-off risk flags — all computed as SQL views.
Partner Insight Layer
The partner layer surfaces matter status, WIP aging, billing delays, write-off risk, and timekeeper utilisation — giving leadership full visibility over delivery and financial performance from one consistent source.
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 WIP, billing, profitability, and utilisation.
API-connected data extraction
Clio and Xero data is pulled on a scheduled basis via their respective APIs. Time entries, matter records, invoices, and payment data are ingested automatically — no manual exports.
Centralised Postgres data model
All extracted data lands in a structured Postgres schema (Supabase-hosted). Tables are designed around the firm's entities — matters, timekeepers, time entries, invoices, payments, and WIP — with foreign key relationships throughout.
Transformation & financial logic
Raw data is cleaned, deduplicated, and enriched with calculated fields: realisation rate, WIP aging buckets, matter margin, billing delays, and utilisation by timekeeper — all computed as SQL views ready for reporting.
Partner reporting layer
The reporting layer surfaces KPI views, matter-level profitability, WIP aging, and billing performance — always current, drawn from one consistent source with no manual assembly.
Operational insight layer
Built on the same data model, the operational layer gives partners real-time visibility over matter status, timekeeper utilisation, billing delays, and write-off risk — before problems compound.
Partner Portal
What the firm 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, no end-of-week reconciliation.
Financial Performance
current monthFees Billed
£540k
February 2025
Est. Cost
£351k
Derived from matter margins
Est. Profit
£189k
Gross profit this month
Margin
35.0%
Realisation 87.4%
Total WIP
£1.22m
unbilled across all matters
Billable Hours
3,420h
recorded this month
Write-offs YTD
£62k
revenue not recovered
At-Risk WIP
£179k
across 4 flagged matters
Monthly Fees Billed vs Target
Financial Insights
£62k written off this year — equivalent to 11% of a month's billings.
Identify the matters driving write-offs and address recovery rate issues before they compound.
£300k WIP in aging buckets (61+ days) — at elevated write-off risk if matters stall further.
Billing cycle review needed: flag stalled matters and issue interim invoices to convert WIP to recognised revenue.
Realisation rate of 87.4% — for every £100 of time recorded, £87 is actually billed. Below the 90% target.
Improving realisation by 5 percentage points on current billing volumes would recover approximately £27k per month.
This is the level of financial and billing visibility we build for professional services firms — matter profitability, WIP exposure, and revenue per fee earner in one unified view.
Identify write-off risk before it crystallises
WIP aging is tracked automatically. Partners see which matters are approaching the write-off threshold — days before they would have surfaced in a manual review.
Understand matter profitability as work is delivered
Realisation rates and margin are tracked matter-by-matter in real time — not reconstructed weeks after the invoice is raised.
Manage utilisation and capacity with confidence
Timekeeper hours, billable vs non-billable split, and capacity are tracked automatically — no manual tally, no reliance on memory to manage workload.
The Transformation
Before vs After
Before
WIP tracking
Manual Excel reconciliation, rebuilt weekly
Matter margin
Unknown until invoice raised — weeks after work
Billing delays
Not tracked — work sat unbilled unnoticed
Write-off risk
Only surfaced in monthly manual review
Utilisation
Estimated from timesheets — no live data
Data source
3 disconnected systems — Clio, Xero, Excel
After
WIP tracking
Automated — updated on every data refresh, no manual work
Matter margin
Live — tracked as time is recorded and invoices raised
Billing delays
Flagged automatically — WIP aging visible by matter
Write-off risk
Surfaced in real time — 60- and 90-day aging alerts automated
Utilisation
Live by timekeeper — billable vs non-billable tracked automatically
Data source
One Postgres model — every report from the same source
Operating Model — After
One version of the truth
One central data model
Clio, Xero, and Excel all feed into one Postgres database. Every report, KPI, and alert 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.
WIP transparency
WIP is tracked by matter, timekeeper, and aging band — always current within the refresh cycle. No weekly reconciliation required.
Matter profitability — live
Time entries and invoices are joined to matters. Partners see margin per matter as work is delivered — not weeks after the final bill.
Billing delay alerts
WIP aging triggers automatic alerts at 30, 60, and 90 days. Matters at write-off risk are surfaced before the damage compounds.
Scalable architecture
The schema is designed to accommodate additional matter types, departments, 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 ~4 hours manual
100%
Matter margin visibility
Previously unavailable
Live
WIP & billing tracking
Was weekly manual reconcile
1
Source of truth
Was 3+ disconnected systems
Reporting time: 4 hours → 30 minutes
Weekly WIP reconciliation and partner reporting now runs automatically — no exports, no manual reconciliation, no formatting.
Matter margin visibility: unknown → live
Time entries and invoices are joined to matters in real time. Partners see realisation rates and margin per matter as work is delivered.
Write-off risk: discovered late → flagged early
WIP aging alerts surface at-risk matters at 60 and 90 days — giving partners time to act before the exposure compounds.
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 firm has different systems, different matter structures, and different decisions to make. That’s why nothing here was adapted from a template — it was designed specifically for how Lex & Co operates, across both their financial and operational 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.