Replacing 30+ Ad-Hoc SQL Reports With a Semantic Layer

A domain owner pulled me into a call in early 2024 with two reports open
side by side. Both claimed to show billable hours for the same firm over the
same period. The numbers were different, and the question on the call was
simple: which one is right. I couldn't answer it. Nobody on the call could.
Each report had its own SQL, written by someone else at some other time, and
each had quietly decided for itself what "billable" meant. That was the
moment the problem stopped being a backlog item and became undeniable.

At MyCase, a legal practice-management platform used by tens of thousands of
law firms, every report was its own SQL query. Case analytics, time and
billing, trust accounting, financial summaries. Each one had been built by
whoever needed it, whenever they needed it, directly against a read replica.
By early 2024 there were more than 30 of these, and the pattern had stopped
working.

I tech-led the effort to replace them with a unified analytics platform: a
shared semantic metrics model, materialized views, an authorization-aware
GraphQL API, and a caching layer in front of it. We migrated 20-25 of the
legacy reports onto it, cut p95 report latency by 55%, and got new-report
delivery down from 2-3 weeks to about 4 days. This post is about the design,
not the rollout. Specifically, why "one query per report" breaks down at
scale and what replacing it with a semantic layer actually involves.

The problem with one query per report

Ad-hoc SQL reports are the fastest way to ship a report and the slowest way
to ship the tenth one. Every query re-derives its own definition of things
that should mean the same thing everywhere: what counts as "billable time,"
how a write-off is categorized, which case statuses are "active." When ten
reports each encode that logic slightly differently, you get ten slightly
different answers to the same question, and nobody can say with confidence
which one is right.

The operational cost compounds this. Every report is a bespoke query against
a live read replica, so there's no shared caching, no shared indexing
strategy, and no way to reason about load except per-query. Add a report,
add unpredictable load. And because each report is a one-off, adding a new
one means writing SQL from scratch against the production schema, not
composing from existing building blocks. That's exactly why "add a new
report" had drifted from a days-long task to a multi-week one.

We didn't jump straight to a semantic layer. The first thing we tried was
adding indexes to the worst-offending queries and tuning them one at a time.
It bought us about two weeks of breathing room before the next report landed
and the same latency and load problems came back. That was the tell: we were
treating symptoms of a query we'd re-derive again next time, not the fact
that the same definitions kept getting re-derived at all.

What a semantic layer actually buys you

I chose to build a semantic layer rather than keep optimizing queries. A
faster database or a caching proxy in front of the existing queries would
both have preserved the actual problem, which was that the meaning of the
data lived in 30 different places instead of one. The fix was to pull that
meaning out into a layer of its own:

A shared semantic metrics model. Define "billable hours," "trust
balance," and "active case" exactly once, as named, versioned metrics with
explicit business logic, not as inline SQL expressions duplicated across
queries. Every report downstream consumes the same definitions. This is the
layer that actually solves the "ten reports, ten answers" problem; everything
else in this stack exists to make that layer fast and queryable.

Materialized views underneath it. The semantic definitions get
pre-computed into materialized views on a schedule, instead of every report
touching live production tables. This is what makes read-heavy analytics
workloads survive contact with a replica that's also serving the rest of the
application. You're querying a purpose-built shape of the data, not
reverse-engineering one out of OLTP tables on every request.

A GraphQL API in front of the model, not the raw views. Reports don't
write SQL against the materialized views directly. They query a schema
shaped around the semantic model, with field-level authorization baked in.
That's what let 20-plus reports across case, billing, trust, and financial
domains share one access layer instead of each hand-rolling its own
permission checks against raw tables.

Caching on top of that. Once queries are going through one API against
one semantic model, caching stops being a per-report decision and becomes a
property of the layer. Hot paths get cache hits regardless of which report
is asking.

A shared report-rendering framework on the frontend. Reports had also
each built their own filters, layouts, loading states, and export flows. A
common React/TypeScript report shell consuming the GraphQL schema meant new
reports composed existing UI instead of rebuilding it, and users got
consistent behavior across domains that used to feel like different
products.

The trade-off that made this hard

The honest complexity here isn't the tech stack. Materialized views,
GraphQL, and a caching layer are all well-understood tools individually.
It's that a semantic layer is a governance problem before it's an
engineering problem. Getting case, billing, trust, and financial
stakeholders to agree on one definition of "billable hours" means someone
has to arbitrate the disagreements that let 30 different SQL queries coexist
in the first place. I drove that alignment across the four domains, and it
was harder than any of the code. Migrating 20-25 reports onto shared
definitions forces those conflicts into the open instead of letting each
report quietly keep its own answer.

That's also why this wasn't a rewrite-everything-at-once project. Reports
migrated in order of how load-bearing and how contested their metrics were,
which meant the semantic model itself evolved as edge cases surfaced. A
write-off that's excluded from one billing report but not another isn't a
bug, it's a definition that had never been made explicit before.

The caching decision inside infrastructure we didn't choose

MyCase already ran on RDS Postgres and ElastiCache Redis long before this
project started. That infrastructure was inherited, not selected. The
decision that actually belonged to this project was narrower: once
materialized views became the source of truth for report data, how does the
cache in front of them know when to give up a stale answer.

Two approaches were rejected, both for reasons that only show up once you
look at how the materialized views actually refresh.

A plain fixed TTL, cache each result for some number of minutes regardless
of what's happening underneath, doesn't know anything about the refresh
schedule. Set it too long and a report can serve data from before the last
refresh; set it short enough to guarantee freshness and you've thrown away
most of the caching benefit waiting on a timer that has no relationship to
when the underlying data actually changed.

Event-driven invalidation, busting specific cache keys the moment underlying
data changes, is more precise, but it means hooking every write
path across case, billing, trust, and financial domains so each one knows to
invalidate the right downstream report keys. That's an N-domain problem: one
missed write path and a report silently serves stale data with no signal
that anything is wrong. It was a particularly bad fit mid-migration, while
some legacy reports were still writing directly against the same tables the
new model was trying to supersede.

I decided against both and keyed the cache to each materialized view's own
refresh timestamp instead of a timer or an event bus. A stale view produces
a stale key, which produces a cache miss on its own, no explicit
invalidation logic required. Freshness becomes a side effect of the refresh
pipeline instead of a second system that has to be kept in sync with it.

What shipped

By the time the migration reached its current state, p95 latency on
migrated reports was down 55%, replica load at peak had dropped meaningfully
because reports were no longer hitting live tables directly, and building a
new report was a matter of composing existing metrics and UI components
instead of writing a new query against production schema from scratch.

I remember the first report we delivered on the new platform. A domain owner
asked for it on a Monday and had it in front of users that Friday, four days
instead of the three weeks the same request would have taken a quarter
earlier. The engineer who built it never wrote a line of SQL against
production. A few weeks later the same domain owner who'd cornered me with
two conflicting billable-hours reports checked the new one against a second
report that touched the same metric. They matched. That was the moment the
project felt done, well before the migration was.

The biggest win wasn't the latency number. It was that "add a report"
stopped being a multi-week SQL archaeology exercise and became routine.

If you're looking at a pile of ad-hoc reports that have quietly become a
maintenance burden, the questions worth asking aren't "which database" or
"which caching layer." They're: where does the meaning of your metrics
actually live right now, and how many places would you have to change it if
the business redefined one of them tomorrow. If the answer is "more than
one," that's the layer to build first.