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
The overview post ended on a governance insight: ten reports meant ten definitions of "billable hours," and fixing that meant someone had to arbitrate the disagreements. That is true, but it is only half the story. An agreement that lives in a meeting recording or a Confluence page decays the moment the next engineer writes the next report and quietly encodes their own interpretation again. The arbitration only sticks if it is embedded in something the code physically cannot route around.
So the design question for this post is narrower than "how do we align stakeholders." It is: once you decide to fix ten reports and ten definitions, what does "one definition" actually look like as software? What is the artifact you build that makes the agreement load-bearing instead of aspirational?
What a metric definition has to contain
The naive answer is that a metric is a SQL query. Define the query once, point every report at it, done. That is not wrong, but it undersells what the definition needs to carry to survive in a real codebase across a multi-month migration.
A query alone has no name that a human reading Ruby can reason about. It has no version, which matters enormously the moment you need an old definition and a new definition to coexist while reports migrate one at a time. And it has no place to record the business meaning in plain language, which is the single most valuable thing a metric definition can carry, because the whole reason the old world produced ten answers was that the meaning lived in nobody's head consistently.
So a metric definition, as we built it, had to contain four things. A name, so the definition is a first-class thing code refers to rather than an anonymous string of SQL. A version, so migration can happen incrementally without a flag-day cutover. A description, the business meaning stated in a sentence a domain owner could read and confirm. And a query, expressed not as raw SQL but as something reports could compose against rather than copy.
The class shape
Here is what that turned into. Every named metric became a Ruby class in an Analytics::Metrics namespace inside the Rails application:
module Analytics
module Metrics
class BillableHours < Base
VERSION = 2
DESCRIPTION = "Billable-status time entries, write-offs excluded"
def self.view_name = :analytics_billable_hours_v2
def self.query
TimeEntry.joins(:matter).where(billable: true, written_off: false)
end
end
end
end
The query method returns an ActiveRecord scope, not a string. That choice matters more than it looks. A scope is composable: a report that needs billable hours for a single firm over a date range starts from BillableHours.query and adds its own where clauses on top, rather than pasting the join-and-filter logic into yet another bespoke query. The definition of what "billable" means lives in exactly one method, and everything downstream builds from there. Reports call the metric class. Reports never write the billable: true, written_off: false predicate themselves, because the entire point is that they no longer get to decide what that predicate is.
The view_name is where the definition meets the physical database. The materialized views that the overview post described are not hand-written SQL files maintained separately from these classes. They are generated from the class definitions. analytics_billable_hours_v2 exists because BillableHours at VERSION = 2 declares it. The class is the source of truth, and the Postgres view is a materialized projection of what the class says. This is the mechanism that makes the canonical definition genuinely canonical: there is no second place where the meaning of billable hours could drift, because the view cannot exist without the class that defines it.
Versioning as a migration tool
The VERSION = 2 on that class is not decoration. It is what let this whole thing migrate incrementally instead of as a rewrite.
When a metric definition changed, we did not edit the existing class in place and break every report reading from it. We wrote a new class at a new version, which generated a new view, and left the old class and its view running untouched. Reports migrated off the old version onto the new one at their own pace, one at a time, and the old definition kept serving its remaining consumers correctly the entire time. When the last report reading v1 finally moved to v2, we dropped the v1 class and its view.
The cost of this is honest: view accumulation. For a while you have two materialized views representing two versions of the same metric, both refreshing, both taking up space, and you are carrying that duplication until the last laggard report cuts over. We accepted that cost deliberately. The alternative was coordinating a synchronized cutover of every report that touched a metric, at the same moment, which is exactly the kind of flag-day migration that turns a multi-month project into a single terrifying deploy. Coexistence bought us the ability to be incremental, and being incremental was the only way a team our size was going to migrate 20-plus reports without stopping the world.
Where the model got hard
If every metric had been as clean as billable hours, this would be a tidy engineering story and not much else. The hard ones were the metrics where more than one definition was legitimately correct.
Trust balance was the sharpest example. A law firm holds client money in trust, and "how much is in trust" has at least three legally meaningful answers. There is the bank statement balance, the actual cash sitting in the trust account. There is the firm ledger balance, what the firm's own books say is owed to clients in aggregate. And there is the per-client sub-ledger balance, what a specific client has in trust right now. Under IOLTA rules governing client trust accounting, all three are real, all three get reported in different contexts, and they can legitimately differ from one another at any given moment because of timing, uncleared transactions, and reconciliation lag.
This was not a case where one definition was right and the others were sloppy. It was a compliance-stakes alignment problem that no amount of reading the code could resolve, because the code was not where the answer lived. Getting to a single canonical TrustBalance metric, or deciding we actually needed three distinct named metrics, required pulling in the people who owned the compliance obligation and having them state which balance a given report was legally supposed to show. The class shape did not solve that. What the class shape did was give the resolution a permanent home: once the stakeholders agreed, the agreement became a DESCRIPTION string and a query method that could not be quietly reinterpreted later.
The model evolved as reality forced it to
The metrics model was not designed once, up front, and then frozen. It grew as the migration surfaced definitions nobody had ever written down.
The write-off case from the overview is the cleanest illustration. Write-offs were excluded from billable hours in one billing report and included in another. Neither was a bug. They were two undocumented decisions, made by different people at different times, that had coexisted for years precisely because there was no single place where the treatment of write-offs had to be stated. Migration forced the question into the open. You cannot write where(written_off: false) in a shared BillableHours.query without someone confirming that write-offs are, in fact, excluded, and confirming it as a decision that now applies everywhere.
That reframed what a migration actually was. Moving a report onto the semantic layer was never just a technical port. Each one was a governance artifact, proof that a definition had been explicitly agreed on, written into a class, and made binding on every future report that touched the same metric. The model got more complete with every report we moved, not because we planned each addition, but because each report dragged one more buried assumption into the light.
The payoff in the shape
The reason all of this was worth building as a class hierarchy rather than a folder of SQL files shows up in what the Base class bought us for free.
Because every metric is a subclass of Base, every metric automatically participates in the machinery around it without any per-metric wiring. The refresh pipeline that keeps the materialized views current, which is the subject of the next post, iterates over the registered subclasses and refreshes each one's view. Nobody adds a metric to a refresh schedule by hand. The GraphQL schema that reports actually query, which is the post after that, is generated from the same registry. A new metric becomes a queryable field the moment its class exists.
That is the quiet win of putting the definition in a class. You are not just recording what "billable hours" means in one place. You are making every metric a citizen of a system that already knows how to refresh it, expose it, and authorize access to it, so that adding the thirty-first metric costs almost nothing beyond agreeing on what it means. And agreeing on what it means, as the trust-balance story showed, was always the expensive part.
Top comments (0)