Series -- From Ad-Hoc SQL to a Semantic Layer: 0: Overview · 1: Ten Answers · 2: Why Not Indexes · 3: Metrics Model · 4: View Pipeline · 5: Schema Design · 6: Authorization · 7: Migration Sequence · 8: Stakeholder Alignment
When a law firm pulled up a billing report at MyCase, the Rails monolith ran a
SQL query against the read replica. Every report was its own query, written by
whoever needed it at the time, against the production schema directly. By early
2024 there were more than 30 of these across case analytics, time and billing,
trust accounting, and financial summaries.
This post is the architecture overview for a series about what we changed and
why. It is not the story of the migration; that starts in Part 1. It is a map
of the system: what the ad-hoc approach handled, what moved to the semantic
layer, where the boundaries are, and one governance constraint that shaped
every other decision.
The ad-hoc SQL architecture
Each report was a standalone SQL query against a read replica, written to
answer a specific question and deployed directly to production. There was no
shared model, no shared caching, and no shared access layer. A domain owner
needed a number; an engineer wrote a query; the query shipped.
The problem with this design was not any single query. The problem was that
every query re-derived its own definition of shared business concepts: what
counts as billable time, how a write-off is categorized, which case statuses
are active. With one report that encoding is invisible. With thirty, the
definitions diverge. Reports that looked like they answered the same question
were actually answering thirty slightly different ones under the same label.
The operational cost compounded the consistency problem. Every report hit
the live read replica directly, so there was no shared caching and no way to
reason about aggregate load. Adding a report added unpredictable load. And
because every report was a one-off query, adding a new one meant writing SQL
from scratch against the production schema -- not composing from existing
building blocks. That is exactly why delivery had drifted from days to weeks.
What definition drift looked like
The clearest example was billable hours. One report joined through the billing
and invoice tables: it counted hours that had actually been invoiced. A second
report queried time_entries directly and counted every entry with a billable
status, regardless of whether it had reached an invoice. Both were correct.
Both returned different numbers for the same firm over the same period.
Neither report carried any indication that its definition differed from the
other.
The same pattern appeared everywhere the reports touched a concept with
business nuance. Active case count varied by which statuses each report
treated as active. Trust balances differed by which ledger each report
queried. Financial summaries produced discrepancies that domain owners noticed
and escalated. None of these were edge cases -- they were the core numbers
firms used to run their practices.
The semantic layer architecture
The replacement architecture has four layers and a frontend shell:
A semantic metrics model defines each business concept -- billable hours,
trust balance, active case count -- exactly once as a named, versioned Ruby
class with explicit business logic. Every downstream report consumes the same
definitions. This is the layer that solves the definition-drift problem;
everything else in the stack exists to make that layer fast and queryable.
Materialized views pre-compute those semantic definitions on a schedule
into purpose-built Postgres views, instead of every report re-deriving them
from OLTP tables on each request. Reports query the materialized views, not
live production tables.
A GraphQL API sits in front of the materialized views. Reports do not
query the views directly. They query a schema shaped around the semantic
model, with field-level authorization baked into each resolver. That is 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.
A caching layer (ElastiCache Redis, inherited infrastructure) sits in
front of the API. Hot report paths get cache hits regardless of which report
is asking. The cache invalidation decision is covered in its own section below.
A shared React/TypeScript report shell on the frontend consumed the
GraphQL schema. Reports composed existing filter and export components instead
of rebuilding them. Users got consistent behavior across domains that had
previously felt like different products.
The metrics model
The semantic model is a Ruby class hierarchy under Analytics::Metrics. Every
metric is a subclass of Analytics::Metrics::Base. The base class owns the
query interface, versioning, and the registration mechanism that lets the API
layer resolve a metric by name.
Each subclass encodes one business definition: which tables to query, which
joins to use, which filters apply. Analytics::Metrics::BillableHours has one
definition of billable. The invoiced-vs-logged distinction is a parameter of
the metric, not a divergence between two separate queries.
Metrics support versioning through the base class. When a business definition
changes, a new version is added as a subclass method, and the old version
remains resolvable until every report consuming it has been migrated to the
new one. This made definition changes a migration, not a breaking cut.
The materialized view refresh pipeline
Views refresh on a schedule driven by Sidekiq-Cron. Each view has a
corresponding job that runs REFRESH MATERIALIZED VIEW CONCURRENTLY, which
Postgres executes without locking the view against reads during the refresh.
The ViewRefreshJob is idempotent. If a refresh is still in progress when the
next scheduled run fires, the new job exits without triggering a second
concurrent refresh. Failures write to a dead-letter table; a separate monitor
job alerts when a view has not refreshed within its expected window.
The refresh pipeline does not attempt fine-grained invalidation. It refreshes
all views for a given domain on a schedule, not in response to individual
write events. That simplicity was a deliberate tradeoff: event-driven
invalidation was evaluated and rejected (see below).
The GraphQL API and authorization
The API layer is a Rails-mounted GraphQL schema (graphql-ruby). The schema is
shaped around the semantic model, not the underlying tables. A report queries
billableHours on a Firm type -- it does not write SQL against
time_entries or invoices.
Authorization lives at the resolver level. BaseResolver applies the firm
scope: every query is filtered to the requesting user's authorized firms before
any field resolution runs. Per-type Pundit policies enforce field-level access
on top of the firm scope. An unauthorized field returns null rather than
raising an error; the resolver does not expose whether the field exists but is
restricted.
This two-layer model meant authorization was tested once and applied uniformly
across all reports, rather than being re-implemented per query.
The cache invalidation decision
The cache sat in front of the GraphQL API. Two approaches were rejected before
settling on the design that shipped.
A fixed TTL does not know when the underlying materialized view last
refreshed. Set it longer than the refresh interval and a report can serve
data from before the last refresh. Set it shorter and most of the caching
benefit disappears.
Event-driven invalidation requires hooking every write path that could
affect a given report's metrics: case status updates, invoice events, payment
events, write-off entries. One missed write path means silently stale data
with no signal. This was a particularly bad fit during migration, when legacy
reports were still writing against the same tables the new model was reading.
The approach that shipped keys each cache entry to the materialized view's
own refresh timestamp. When a view refreshes, its timestamp changes, the
derived cache key changes, and outstanding cache entries become misses
automatically. No explicit invalidation logic is required. Freshness is a side
effect of the refresh pipeline rather than a second system that must stay in
sync with it.
The governance constraint
The governance constraint is the closest analog this system has to a PCI
boundary in the ACH architecture. It is not optional and it is not primarily
an engineering concern.
A semantic layer requires someone to own each metric definition. "Billable
hours" can mean invoiced hours or logged-but-unbilled hours; the semantic
model requires exactly one answer, and that answer has to be ratified by the
domain stakeholder before the metric can be committed. For most metrics, that
arbitration was straightforward. For trust accounting -- where three competing
ledger definitions were all legally valid under IOLTA -- the arbitration took
weeks and blocked trust report migration until it resolved.
This governance work happened iteratively, not upfront. The model was not
frozen before migration began. Each report that migrated forced its metric
definitions into the open, which is exactly what the ad-hoc approach had
avoided. Some definitions never resolved; those reports stayed on legacy
queries permanently.
What shipped
P95 latency on migrated reports dropped 55%. Replica load at peak dropped
because reports were no longer hitting live tables directly. Building a new
report went from writing SQL against the production schema from scratch to
composing existing metrics and UI components. Delivery dropped from 2-3 weeks
to about 4 days.
20-25 of the 30+ legacy reports migrated. The remaining 5-10 stayed on legacy
queries: some for low traffic volume where migration cost exceeded benefit,
some because their metric definitions never reached a canonical resolution.
What the series covers
The eight posts that follow each cover a specific decision in the build. They
assume the architecture above as context.
Part 1: Why ad-hoc SQL stops scaling: the specific divergence in the
billable-hours queries, and why index tuning turned out to be a two-week fix
for a structural problem.
Part 2: Why we did not just add more indexes or a data warehouse: the
evaluation of alternative approaches and why each one preserved the actual
problem.
Part 3: How the metrics model is designed: the Ruby class hierarchy, the
versioning mechanism, and how metric definitions are registered and resolved
by the API layer.
Part 4: The materialized view refresh pipeline in detail: scheduling,
concurrency, failure handling, and why the cache invalidation design is coupled
to the refresh mechanism.
Part 5: The GraphQL schema design: the type hierarchy, the anti-churn
rule for schema evolution, and how the schema stayed additive across 20-plus
report migrations.
Part 6: Authorization at the API layer: why field-level checks live in
GraphQL resolvers rather than in controllers or in the view queries, and how
the two-layer model composes.
Part 7: Migration sequencing: the two axes that determined which report
moved first, how parallel running and shadow comparison worked, and what stayed
behind.
Part 8: Stakeholder alignment: what it looked like to drive metric
definition arbitration across four domains, and why that governance work was
harder than any of the code.
The series assumes familiarity with Rails, Postgres materialized views, and
GraphQL at a conceptual level. Posts do not re-explain the metrics class
hierarchy, the GraphQL schema shape, or IOLTA trust accounting basics beyond
what each post requires. This post is the one to return to if any post
references "the semantic model" or "the refresh pipeline" and the context is
unclear.
Top comments (0)