DEV Community: Son Do

For Review: Matching Plaid Bank Feeds Without Duplicate Ledger Entries

Son Do — Sun, 26 Jul 2026 05:04:28 +0000

Series -- When the Aggregate Looks Right: 0: Overview · 1: Chart of Accounts · 2: Three-Way Reconciliation · 3: The Aggregate Can Lie · 4: Bank Sync · 5: GL Integration

A bank feed line arrives from Plaid. Same amount, same day, same account as
a payment the firm already recorded in MyCase. If Accounting posts the feed
line as new activity, the ledger now has two entries for one real-world
movement of money.

That collision is the opening problem for bank sync in a practice-management
product. Firms are not starting from an empty book. Trust deposits,
operating payments, and invoice-linked transfers often exist in-system
before the bank feed catches up. Naive import is how you invent duplicates.
This post is about the For Review queue we built so feed lines could be
matched, categorized, split, or excluded -- and about the trust-side rule
that refuses to categorize at all.

For Review as the control plane

Unmatched bank transactions land in For Review. From there the firm can:

Categorize -- map the line to a GL account, including via firm-authored rules
Match -- link the line to an existing MyCase record
Split -- allocate across multiple accounts
Exclude -- leave it out of the books

When the action is done, the transaction moves to Reviewed. That queue is
the control plane for both Plaid feeds and the CSV import path for banks
Plaid does not cover. Same pipeline, different door: upload, preview,
column mapping, confirm, then For Review. The CSV path is a cameo here, not
a second product story. What mattered was not inventing a parallel matcher
for file imports.

On the operating side, user-defined categorization rules are firm-authored
mappings from feed attributes to GL accounts, applied when someone
categorizes (or when a rule can clear the obvious cases). They are
supporting machinery under Categorize, not the spine of the design.

The spine: trust skips categorization

The load-bearing decision is the trust exception.

The rejected alternative was symmetry: treat trust feed lines like
operating feed lines. Categorize them into GL accounts from the bank
description. It looks consistent in a UI mock. It is wrong under the model
in Parts 2 and
3.

Trust-side activity should already exist from firm-recorded deposits,
transfers, and billing-linked posts. The bank is confirming a movement the
firm should already know about. Categorizing trust lines from the feed
creates a second source of truth -- bank narrative inventing trust ledger
reality -- and fights three-way reconciliation and invoice-linked
safeguards.

So trust accounts skip categorization. Operating accounts get Categorize,
Match, Split, and Exclude. Trust gets Match or Exclude. If there is nothing
to match, that is a signal to investigate, not a prompt to invent a GL
category from a Plaid memo field.

Match without double-posting

Match is where the opening collision gets resolved. Linking a feed line to
an existing MyCase record is only allowed when the duplicate-prevention
checks agree: amount, payment method, and account. If they disagree, the
link does not go through. That contract blocks attaching a feed line to the
wrong record and blocks the "close enough" match that double-posts under a
different label.

I am not going to invent a near-miss war story for this post. The checks
are the story: three fields, all must agree, or you do not attach.

Why this matters for reconciliation

Reviewed feed lines are what make monthly
three-way reconciliation
runnable without someone typing the bank statement by hand. The matching
pipeline auto-matched about 75% of Plaid bank-feed transactions to trust
and operating ledger entries for adopting firms. The remaining queue is the
work -- and because trust cannot be casually categorized from the feed, the
work stays pointed at real exceptions instead of invented books.

Part 5 is the other half
of "the firm already recorded this": posting billing and LawPay events into
the GL at event time so the records Match is looking for exist in the first
place.

Series -- When the Aggregate Looks Right: 0: Overview · 1: Chart of Accounts · 2: Three-Way Reconciliation · 3: The Aggregate Can Lie · 4: Bank Sync · 5: GL Integration

Stop Re-Keying Invoices: Posting Billing and LawPay Events to the GL

Son Do — Sun, 26 Jul 2026 05:04:16 +0000

Series -- When the Aggregate Looks Right: 0: Overview · 1: Chart of Accounts · 2: Three-Way Reconciliation · 3: The Aggregate Can Lie · 4: Bank Sync · 5: GL Integration

The invoice already exists in MyCase. The LawPay settlement already hit the
payment ledger. Somewhere else -- a spreadsheet, a separate accounting tool,
a second set of books -- someone types the same numbers again.

That re-keying is not a one-off fat-finger story. It is the default
integration pattern when practice management and firm accounting are two
systems of truth. The concrete miss inside the pattern is familiar: a
payment shows as collected in billing, and the books disagree, or the books
never got the line at all. Someone forgot, or delayed, or rounded, or
posted to the wrong place on the second pass.

This post is about refusing that pattern. We integrated existing MyCase
billing, invoicing, and LawPay payment ledger events into MyCase Accounting
GL posting rules on Rails so time, invoices, and trust/operating payments
posted without re-keying.

The rejected alternative: export/import

The easy-sounding integration is a file. Export invoices and payments from
billing. Import them into Accounting on a schedule. Call it done.

Export/import keeps two systems of truth and reintroduces lag. Anything that
can lag can miss. Anything that can miss puts you back in the phone-call
failure mode from Part 0: the
aggregate looks fine until a human notices the gap.

What we shipped instead: auto-post at the billing and payment event. When
the billing layer records the fact, Accounting posting rules write the GL
entries into the
locked chart of accounts
under trust and operating rules. One event, one post, same Rails
transaction boundary where we could keep it honest.

The boundary: ledger-to-GL inside the monolith

There is no romantic microservice story here. Billing and Accounting lived
in the same Rails monolith. The seam was a ledger-to-GL bridge:

Billing owns the payment and invoice facts.
Accounting owns the posting rules onto the chart of accounts.
The contract is prosaic: when billing records X, Accounting posts Y, routed trust versus operating correctly.

That boring boundary matters. It means the invoice identity
Part 3 matches against is the
same invoice billing just wrote -- not a copied row from last night's CSV
that drifted. It means
Part 4 Match has real in-system
twins to link bank feed lines to, instead of an empty ledger waiting on
manual entry.

Firms activate Accounting onto the pre-populated locked chart; posting
rules hang on those system accounts from the first event.

Closing the series

The failure mode at the start of this series was simple and ugly: an
aggregate that reconciled to the cent while a client's money was in the
wrong place.

The path through it was cumulative:

Lock the chart so auto-post and matching have stable identity.
Auto-generate client trust ledgers so three-way is real, not optional.
Flag trust-to-operating movement with invoice-exact matching so reviewers trust every alert.
Match bank feeds without categorizing trust into a second source of truth.
Post billing and LawPay events into the GL at event time so the books and the practice stay one system of truth.

Watch closely without destroying the signal. Stop making a human retype the
signal into a second ledger. That is the work.

Series -- When the Aggregate Looks Right: 0: Overview · 1: Chart of Accounts · 2: Three-Way Reconciliation · 3: The Aggregate Can Lie · 4: Bank Sync · 5: GL Integration

Bank, Firm, Client: Why Two-Way Reconciliation Wasn't Enough

Son Do — Sun, 26 Jul 2026 05:03:46 +0000

Series -- When the Aggregate Looks Right: 0: Overview · 1: Chart of Accounts · 2: Three-Way Reconciliation · 3: The Aggregate Can Lie · 4: Bank Sync · 5: GL Integration

Picture a clean close. The bank statement lines are checked off against the
firm's trust ledger. The difference is $0.00. Everyone is ready to move on.

Then a client calls about a retainer, and one client's sub-ledger is wrong
by a few hundred dollars. Money that should have stayed in their slice was
drawn against another matter. The bank never saw it. The aggregate never
flagged it. Two-way reconciliation had succeeded at the only thing two-way
can see.

That is why bank-versus-firm matching is not enough for IOLTA trust
accounts. This post is about the third leg -- per-client Client Trust
Ledgers -- and the product decision that made the third leg exist by
default instead of as optional setup.

Why two-way fails structurally

A pooled trust account is one bank balance and many client slices. The bank
statement is authoritative for the pool. The firm's trust ledger is
authoritative for what the firm recorded. Neither one, alone or together,
proves that each client's slice is correct.

Two-way can tell you the pool matches. It cannot tell you whose money moved
inside the pool. Under IOLTA, whose money moved is the whole point.
Three-way reconciliation -- Trust Account Bank Statement, Firm Trust
Ledger, and Client Trust Ledger (balance by matter) -- is not a MyCase
invention. It is what the rules already require. The engineering question is
whether the product makes the third ledger inevitable or optional.

The decision: auto-generate the third leg

The rejected alternative was manual setup. Ask the firm to create
per-client sub-ledgers, then reconcile against them once they exist.

That alternative fails quietly. Three-way on paper with a missing third leg
is two-way in practice. Firms under time pressure skip optional
configuration. The aggregate-lie failure mode stays invisible until a
client phones in.

What we shipped: Client Trust Ledgers are auto-generated per client as
matters and trust activity require them. "Balance by matter" is the default
path, not a checklist item. If you are recording trust activity in MyCase
Accounting, the third ledger is already there when reconciliation starts.

That was the load-bearing product decision inside an otherwise mandated
model. IOLTA says you need three-way. Auto-generation says you will still
have three-way on a Thursday afternoon when nobody wants to configure
anything.

The workflow

Reconciliation itself is deliberately boring. Compare bank statement lines
against logged journal entries. Check off matches. Target a $0.00
difference -- no variance band. A tolerance band is the crack a real error
slips through; the same philosophy shows up again in
Part 3 on invoice-exact matching.

The module supports checking, savings, credit card, and trust accounts on
that check-off path. Trust is the hard case: it adds the client sub-ledger
leg and the IOLTA constraints. Operating account types reuse the same
statement-versus-journal workflow without client sub-ledgers. Supporting
four account types was real work, but it is not the spine of this story.
The spine is making the third trust ledger unavoidable.

What comes next

Once client-level balances are visible by default, you can finally watch
the failure mode two-way cannot see. Watching introduces a new problem:
how do you flag real trust-to-operating mistakes without burying reviewers
in routine, correct transfers?

That is Part 3 -- the aggregate
can lie, and a naive safeguard can lie louder.

Series -- When the Aggregate Looks Right: 0: Overview · 1: Chart of Accounts · 2: Three-Way Reconciliation · 3: The Aggregate Can Lie · 4: Bank Sync · 5: GL Integration

Six Categories, Some Locked: Designing a Legal Chart of Accounts

Son Do — Sat, 25 Jul 2026 02:10:14 +0000

Series -- When the Aggregate Looks Right: 0: Overview · 1: Chart of Accounts · 2: Three-Way Reconciliation · 3: The Aggregate Can Lie · 4: Bank Sync · 5: GL Integration

The tempting chart of accounts design for a law-firm product is the
QuickBooks-shaped one: ship a starter tree, then let firms rename, move, and
delete whatever they want. Firms know their own books. Flexibility feels
like respect.

We did not ship that.

I designed MyCase Accounting's chart of accounts as a legal-specific,
pre-populated structure with six top-level categories -- Assets, Liabilities
(including segregated trust), Equity, Revenue, Expenses, and Compensation --
and with a hard rule underneath: some system accounts are locked. Billing,
invoicing, and related auto-posting write to those accounts. Firms customize
under them with sub-accounts. They do not get to mutate the roots the
product depends on.

This post is about that lock decision: the rejected alternative, what firms
can still change, and the downstream matching that only works because the
identity stayed stable.

The rejected alternative

A fully customizable chart looks customer-friendly until the product has to
post on the firm's behalf.

MyCase already had billing, invoicing, and payment flows. Accounting had to
record those events into the general ledger without a bookkeeper retyping
amounts into a second system. That means the product, not the firm, chooses
which account receives an invoice post or a trust-to-operating movement.
If the firm can rename, merge, or delete those accounts, every auto-post
becomes a moving target -- and every compliance check that needs "the trust
liability account" or "the operating revenue path for this invoice" loses
its handle.

The alternative we rejected was full editability with a shrug: "firms will
configure it correctly." That is not a design. It is hope. Hope is a bad
dependency when the money is client trust funds.

Six categories, customization underneath

The shape we shipped is deliberately legal, not a generic GAAP starter kit
with trust bolted on later.

Liabilities include segregated trust. That is not a cosmetic label. Trust
money is not the firm's money, and the chart has to say so in a place the
rest of the system can rely on. Assets, equity, revenue, expenses, and
compensation fill out the operating side firms already understand from
practice management.

Pre-population mattered for adoption. Accounting activates onto that
starter chart so a firm is not staring at a blank tree on day one.
Customization is real -- sub-accounts under the locked roots -- but it does
not include deleting the system accounts billing posts into.

When a firm wanted to rename a locked account, the answer was a hard stop
and a redirect: create a sub-account for the label you want. The roots stay
put.

Why the lock pays off downstream

Part 3 covers the safeguard that
flags trust-to-operating transfers that do not exactly match an unpaid
invoice. That safeguard only works if a posted invoice and its
corresponding trust transaction share a stable link through the chart.

Without locked, billing-linked accounts, there is nothing precise to match
a transfer against. You get amount-ish heuristics, tolerance bands, and the
false-positive flood that kills reviewer trust. The chart of accounts is
not a setup screen you finish and forget. It is the identity layer the rest
of the module hangs on.

Part 5 closes the other
half of the same decision: when billing and LawPay ledger events fire,
posting rules write into those locked accounts under trust and operating
rules. Event-time auto-post into a moving chart is how you reintroduce
re-keying with extra steps. Event-time auto-post into a locked chart is how
you eliminate it.

What this post is not

This is not a tour of every system account name we locked. The rule matters
more than the roster: if billing or compliance matching depends on an
account, it is locked; firm-facing labels live in sub-accounts.

It is also not the three-way reconciliation story. The chart makes precise
matching possible. Part 2
is about why the third ledger has to exist by default.

Series -- When the Aggregate Looks Right: 0: Overview · 1: Chart of Accounts · 2: Three-Way Reconciliation · 3: The Aggregate Can Lie · 4: Bank Sync · 5: GL Integration

Building MyCase Accounting: Trust Books That Can Look Right While Being Wrong

Son Do — Sat, 25 Jul 2026 02:10:09 +0000

Series -- When the Aggregate Looks Right: 0: Overview · 1: Chart of Accounts · 2: Three-Way Reconciliation · 3: The Aggregate Can Lie · 4: Bank Sync · 5: GL Integration

A law firm's trust account holds other people's money. Under IOLTA rules in
most US states, every client with an open matter owns a slice of one pooled
bank account, and mixing up whose money is whose is not a bookkeeping quirk.
It is the kind of mistake that gets a lawyer disbarred.

Here is the failure mode that shaped everything we built: the pooled
account's aggregate balance can match the bank exactly, down to the cent,
while one client's individual slice is wrong. The bank does not know about
client-level sub-ledgers. It only sees one account. A reconciliation that
stops at "does the aggregate match" will pass that check every time, right
up until a client asks where their money went.

We watched that happen during early testing. One firm's trust account
reconciled clean against the bank. Everyone would have moved on. Then a
client called about their retainer, and the firm went looking: that one
client's sub-ledger was off by a few hundred dollars. Funds that should have
stayed in their slice had been drawn against another matter. The aggregate
had absorbed the error and reported nothing. A phone call was the only
reason anyone found it.

I was the hands-on tech lead on a 6-8 engineer squad for MyCase Accounting,
the legal bookkeeping module we built on Rails from late 2021 through early

This post is the series map, not the deep dives. The deep dives start in Part 1.

The thesis

Three-way reconciliation is not the engineering invention. IOLTA already
requires matching the bank statement, the firm's trust ledger, and every
client's sub-ledger against each other. The real work is building a system
firms can actually run while watching closely enough to catch client-level
errors -- without drowning reviewers in false positives, and without
re-keying the same invoices and payments across two systems of truth.

Watch closely without destroying the signal. That is the series in one line.

What this series covers

Five deep dives walk the path through that problem:

Chart of accounts -- six legal-specific categories, with system accounts locked so billing can auto-post and compliance matching has a stable identity to hang on.
Three-way reconciliation -- why bank-vs-firm two-way is not enough, and why per-client sub-ledgers have to be auto-generated or the third leg never exists in practice.
IOLTA safeguards -- how we flagged real trust-to-operating problems without flooding the review queue (the already-written deep dive).
Plaid bank sync -- the For Review queue that matches feed lines to existing MyCase records without double-posting, including the trust-side rule that skips categorization entirely.
Billing to GL -- posting invoice and LawPay payment events into the chart of accounts at event time, so firms stop re-keying.

Expense tracking, matter allocation, and financial reporting shipped in the
same module. Those were a teammate's primary work, not mine, and they are
out of scope for this series.

What shipped

Firms using the reconciliation module cut trust reconciliation from roughly
6 hours of manual work a month to under 30 minutes per cycle. Within twelve
months of 2022 general availability, 18% of eligible firms enabled the
module.

Those numbers matter, but they are not the thesis. The thesis is that an
aggregate can look perfect while a client's money is in the wrong place, and
that the engineering problem is how to watch for that without building a
safeguard nobody trusts.

Part 1 starts where the precision has to live first: the chart of accounts.

Series -- When the Aggregate Looks Right: 0: Overview · 1: Chart of Accounts · 2: Three-Way Reconciliation · 3: The Aggregate Can Lie · 4: Bank Sync · 5: GL Integration

The Hardest Part Wasn't the Code: Aligning Stakeholders on Metric Definitions

Son Do — Thu, 23 Jul 2026 09:50:29 +0000

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

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 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.

I have opened this series with that scene once already, because it is the
moment the whole project became undeniable. But I want to come back to it
now, at the end, because the scene contains a trap. It looks like an
engineering problem, and the engineering answer is obvious: build one place
where "billable hours" is defined, and point every report at it. That is what
a semantic layer is. Seven posts of this series are about how we built it.

This post is about the part that the layer did not solve. Building one
definition does not settle a disagreement. It forces you to put exactly one
definition in the model, and the moment you do, every domain that had been
running a different implicit definition is now wrong by decree. The layer
does not end the argument. It makes the argument mandatory.

The easy conflict and the hard one

The billable-hours discrepancy had a root cause you could find. I sat down
with both queries and read them. One of them joined through invoices, so it
was counting only hours that had actually been invoiced. The other queried
time entries directly, so it counted every hour logged with a billable
status, invoiced or not. Same firm, same period, two honest numbers
answering two slightly different questions that both got called "billable
hours." Once you see the join, you understand the whole thing. The
disagreement was an accident of implementation, and the fix was to decide
which question we meant and write it down.

Trust balance was not like that. Under IOLTA, the rules governing how law
firms hold client money in trust, there were three definitions of "trust
balance" in play, and all three were correct. The bank statement definition
was the actual cash position, what the bank said was in the account right
now. The firm ledger definition was what the firm's own books showed it owed
across all clients. The per-client sub-ledger definition was what one
specific client had in trust at that moment. These are not three
implementations of one number. They are three different numbers that answer
three different questions, and a firm needs all three, for reconciliation,
for accounting, and for client-level compliance respectively.

There was no SQL to read that would resolve it. Nobody had made a mistake.
The disagreement was legitimate, and the only way through it was to decide
which question the analytics layer was going to answer, and to be explicit
that it was not answering the other two. That is not a debugging task. That
is a governance decision, and it needed someone with the authority to make
it and the willingness to be held to it.

Who decides what "billable" means

I drove the alignment across the four domains, but I was not the one who got
to decide what the metrics meant. The decisions belonged to the domain
product managers, one each for billing, trust, case, and financial. They
were the people who owned the consequences of getting a definition wrong,
and so they were the ones who had to own the definition.

What I built was the process that forced the decision to happen and made it
stick. Every contested metric got a written definition doc. The doc named
the competing definitions explicitly, laid out which report or which domain
had been using each one, and stated the canonical resolution: this is what
"billable hours" means in the semantic model, this is the question it
answers, and here is what it deliberately excludes. The relevant domain PM
signed off on the doc before the metric was committed to the model.

That sign-off was the whole point. Not a verbal agreement on a call, which
evaporates the moment someone's report shows a number they do not like. Not
an escalation to a VP, which turns a definition question into a political
one and teaches everyone to escalate next time. A written doc, with a named
decision, and a name on the approval. When a report later showed a number
someone disputed, the answer was not "let me check the query." The answer
was "here is the definition your domain signed off on, and here is the doc."
The disagreement had already been had. We were just looking up the result.

The weeks that were not engineering time

The honest cost of this shows up in the trust domain. The trust accounting
reports were built. The materialized views were ready, the GraphQL schema
could express them, the caching worked. The reports could have shipped. They
did not ship, for weeks, because the three-ledger trust balance question was
still live and nobody had signed a doc.

Those weeks were not engineering time. Nothing was being coded. The
infrastructure sat finished and waiting while the billing PM, the trust PM,
and the financial PM worked out which trust balance the platform would
report and how to scope the other two so the reports that genuinely needed
them could still get them. When people talk about how long a migration took,
they usually mean how long the code took. On this project, a real fraction
of the calendar was alignment time, and no amount of engineering velocity
would have shortened it, because the bottleneck was agreement, not
implementation.

I found that clarifying rather than frustrating, eventually. The reports were
blocked by exactly the thing they should have been blocked by. Shipping them
faster would have meant shipping them on a definition nobody had agreed to,
which is how you end up with two reports showing different billable-hours
numbers in the first place.

What never resolved

Not every definition got settled. Some metrics stayed domain-specific,
permanently. In the previous post I mentioned the 5 to 10 reports out of the
original 30-plus that never migrated onto the new platform. A subset of those
were not stuck on traffic or effort. They were stuck on definitions that
never got sign-off, because the domains could not agree and the use case was
too narrow to force the question to a conclusion.

Those reports stayed on their legacy queries indefinitely. Each one kept its
own private definition of whatever it measured, the same way all 30 reports
used to. From an engineering standpoint that is unfinished work. From a
different standpoint it is the most honest output of the project. You migrate
most of a thing like this, and the parts you cannot migrate tell you
something true about your organization's actual ability to agree on shared
meaning. Some questions did not have one right answer that four teams could
live with, and the platform was better for admitting that than for forcing a
false consensus into the model.

Every migration was a governance event

Here is the insight I did not have going in. Forcing reports onto shared
definitions was progress, and it was the entire point of the semantic layer.
But it also meant that every single report that migrated required a decision.
Someone had to say, officially and in writing, what "billable" or "active"
or "trust balance" meant for the platform, and then be accountable for that
decision when a number moved.

That is a fundamentally different kind of work than building infrastructure.
Materialized views and GraphQL schema evolution are hard in a way that yields
to engineering: you can think harder, test more, refactor. Getting four
teams to agree that they had been measuring the same thing differently, and
that the platform was going to pick one definition and hold it against
everyone's objections, does not yield to any of that. It yields to a written
doc, a named owner, and the patience to let the argument happen instead of
routing around it.

The day the domain owner checked two reports on the unified platform and the
billable-hours numbers matched, the project felt done to me, well before the
migration actually was. Not because the code was finished. Because the two
numbers were finally answering the same question, and there was a doc, with a
name on it, that said why.

Which Report Goes First: The Migration Sequencing Logic

Son Do — Thu, 23 Jul 2026 09:50:25 +0000

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 engineering problem was solved. The semantic model worked, the
materialized views were refreshing on schedule, and the authorization-aware
GraphQL API was live and returning correct results. I remember the specific
relief of watching a query come back through the new layer and matching it
by hand against the old read replica. The numbers agreed.

And then I looked at the list. Thirty-plus reports were still pointing at
the old read replica, none of them aware that a better path existed. The
migration problem was just starting, and it opened with a question that had
nothing to do with materialized views or schema design: in what order do you
move them?

Two axes, not one

It is tempting to sequence a migration by traffic alone. Move the heaviest
queries first, get the biggest latency win, declare progress. That instinct
is half right, and the missing half is what made this migration interesting.

There were two axes that mattered, and the Part 0 overview names both of
them: how load-bearing a report was, and how contested its metric
definitions were.

"Load-bearing" was not just query volume. It was a combination of replica
traffic and business consequence. A report that firms used to make billing
or collections decisions carried more weight than a case-summary report that
someone glanced at occasionally, even if the two had similar query counts. A
report could be low-traffic and still high-stakes, because the cost of it
being wrong was measured in a firm's money rather than in database load.
Load-bearing meant "how much breaks, and how badly, if this migration goes
wrong."

"Contested" was a different thing entirely. It measured whether the metric
definitions behind a report required stakeholder alignment before migration
was even technically possible. A report built on a disputed definition could
not move, because there was nothing for it to move onto. The semantic model
demanded exactly one canonical version of each metric, and until the
stakeholders agreed on what "trust balance" meant, the model had no version
of it to offer. Contested meant "how much agreement has to happen before the
new layer can accept this at all."

How the two axes interacted

Plot every report on those two axes and the sequence writes itself, mostly.

Low-load, low-contested reports went first. These were the proof-of-concept
migrations, the ones where the goal was not the latency win but the
demonstration: prove the pattern works, validate that the new layer returns
correct results, build confidence before touching anything that a firm would
notice. If something went wrong here, the blast radius was small.

High-load, low-contested reports were the compelling early wins. Once the
pattern was proven, these were where the latency numbers came from. Their
definitions were settled, so there was nothing blocking them, and their
traffic meant moving them mattered. This is the group that made the project
look successful to anyone watching the dashboards.

High-contested reports went last, and here is the part that traffic-first
sequencing gets wrong: they went last regardless of load. A high-traffic,
high-contested report could not jump the queue on the strength of its
traffic, because the semantic model physically could not accept it until its
definitions were settled. Contention was a hard gate, not a soft
preference. You can defer a low-priority migration; you cannot migrate a
report whose central metric has three competing definitions and no agreed
winner.

Starting with case analytics

The first domain we migrated was case analytics: active case count, case
status, and the reports built on them. On both axes, these scored lowest.

"Active case" is close to an enum-level concept. A case is open or it is
not, and while there are edge cases, they are the kind you can resolve by
reading a status column rather than by convening four teams. There were no
IOLTA implications, no invoicing dependencies, no money riding on the exact
boundary of the definition. And these reports were lower-traffic than the
billing reports that firms hit constantly.

That made case analytics the right proof-of-concept. We migrated these
reports first not because they were urgent but because they were safe. They
let us exercise the whole pipeline -- semantic model, materialized view,
GraphQL query, cache, frontend shell -- against real reports without
gambling on a definition that might still be under dispute. By the time we
moved to anything financial, the pattern had already survived contact with
production.

Why trust accounting was last

At the far corner of the grid sat trust accounting, and it stayed there
until the end.

Trust balance has three legitimate answers under IOLTA, the rules governing
how law firms hold client money. There is the bank statement figure, the
firm ledger figure, and the per-client sub-ledger figure, and each is
legally valid in a different reporting context. This is not a bug or a
disagreement to be resolved by picking the smartest engineer's preference.
All three are simultaneously correct, and choosing which one a given report
should show is a compliance decision, not a technical one.

No trust report could migrate until that question was resolved and the
domain PM signed off on a canonical definition, or on an explicit scoping of
which definition belonged to which report. So the trust reports sat in the
queue while the alignment work ran alongside the migration. That alignment
work is the subject of Part 8; for sequencing purposes, the relevant fact is
that trust was gated on a conversation, not on engineering effort, and the
conversation took time.

Parallel running and shadow comparison

Each report migrated one at a time, and each one went through a shadow
comparison period before cutover. The legacy query and the new layer ran
simultaneously, both producing output, and we compared the two before
decommissioning the legacy path. A migration was not "done" when the new
query returned results; it was done when the new query had agreed with the
old one across a validation window.

For billing and trust reports, that window was longer. When a report drives
a firm's collections decisions, or touches client trust money, the bar for
"we are confident these match" is higher than it is for a case-count report.
More confidence required a longer comparison period, which is another way
the load-bearing axis expressed itself: it did not just influence order, it
influenced how carefully each cutover happened.

What stayed behind

The honest version of "we migrated the system" is that we migrated most of
it. Twenty to twenty-five of the thirty-plus reports moved. The remaining
five to ten did not, and they fell into two groups.

The first group was low-traffic, edge-case reports: one-firm customizations,
rarely-accessed summaries, reports where the cost of migration plainly
exceeded the benefit. Nobody was going to reap a latency win on a report run
twice a quarter, and rebuilding it on the new layer was effort spent for
almost no return.

The second group was blocked on metric definitions that were never fully
resolved. Some disagreements did not converge. Where a definition fight
stalled and the report was not important enough to force the issue, the
report stayed on the legacy query path permanently. Those reports are still
there, as far as I know, quietly running their own SQL against the replica,
each with its own private answer.

Every migrated report was a governance artifact

Here is what the sequencing revealed that the design work had obscured.
Every report that successfully migrated was also proof that a definition had
been agreed on. You could not move a report onto the semantic model without
first settling what its metrics meant, so a migrated report was a
governance artifact as much as an engineering one.

The write-off case from Part 0 makes this concrete. A write-off was excluded
from one billing report and included in another, and that was not a bug. It
was a definition that had never been made explicit, and migration forced it
into the open. Someone had to decide, on the record, which treatment was
correct, and that decision became part of the model.

This is why sequencing was not a purely mechanical exercise. The order in
which reports migrated determined which definitions got forced into the open
in which order, and that in turn determined which stakeholder conversations
were live at any given moment. Migrating the billing reports meant the
write-off conversation had to happen then; deferring trust meant the IOLTA
conversation could wait. The migration schedule was, in effect, a schedule
for the arguments.

That is also why the sequencing decisions were made jointly with the program
PM, not purely by engineering. Which reports moved first was not only a
technical call. The PM had visibility I did not into which firm-facing
reports were politically sensitive or tied to external stakeholder
commitments, and that visibility fed directly into the order. Co-owning the
sequence with someone who understood the business stakes was not a courtesy.
It was the only way to sequence a migration whose real constraint was
agreement rather than code.

Authorization at the API Layer: Why Field-Level Checks Live in GraphQL

Son Do — Thu, 23 Jul 2026 09:50:21 +0000

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

Part 1 of this series described a call where two reports claimed to show
billable hours for the same firm over the same period and disagreed, because
each report had quietly decided for itself what "billable" meant. There was a
second version of that same problem that nobody put on a call, because it
never surfaced as a visible contradiction. It surfaced as a compliance
question instead.

Each of the 30-plus legacy reports was also hand-rolling its own permission
checks against raw tables. One report decided for itself whether the person
running it was allowed to see a firm's trust balance. Another report, pulling
from the same underlying data, made its own separate decision. Thirty reports
meant 30 different answers to "who can see this data," scattered across 30
different SQL queries, with no single place that said what the rule actually
was. For a legal SaaS handling trust accounting and payment data, that
fragmentation was not just a maintenance burden. It was a compliance surface.
Every one of those 30 decisions was a place where the rule could be wrong, and
no way to audit whether it was.

When I designed the unified analytics platform, authorization was not a
feature I added to the GraphQL API after the fact. It was the constraint that
decided where the API layer had to sit at all.

Three places the check could live

There were three honest candidates for where authorization could be enforced,
and picking the wrong one was easy to do because two of the three look
reasonable until you push on them.

The first was the controller layer: Rails, before the GraphQL resolver ever
runs. This is the instinct most Rails engineers reach for first, because
that is where authorization usually lives. The problem is that it does not
actually centralize anything. Each report route still has to know which
fields it is about to expose and gate each one. You have moved the check
upstream of the query, but you still need a check per route per field. Thirty
reports still means thirty places that each have to remember the rule. I would
have rebuilt the exact fragmentation I was trying to eliminate, just one layer
higher up the stack.

The second was the view layer: the shared React report framework deciding
what to render. A report component could check whether the current user holds
view_trust_accounting and simply not draw the trust balance column if they
do not. This looks clean, and for a lot of applications it would be fine. It
was not fine here, and the reason is the distinction that ends up being the
whole point of this post: by the time the React framework decides not to
render a field, the data has already been fetched. The GraphQL query ran, the
resolver returned the trust balance, it traveled over the wire, and it is
sitting in the browser's memory. The UI is choosing not to paint it. For
IOLTA-governed trust data and PCI-relevant payment fields, withholding the
field in the UI is not the same thing as withholding the data. The data left
the server. That is the event that matters, and the view layer happens after
it.

That leaves the API layer: the GraphQL field itself. This is the only boundary
in the entire stack where declining to include a field in the response is the
same act as declining to send the data. If the resolver does not return the
trust balance, the trust balance does not enter the query result, does not
cross the network, and never reaches a browser that could be inspected. There
is no gap between "the user cannot see this" and "the data did not move."

That property is what settled it. Not convenience, not consolidation for its
own sake. The GraphQL field is the only place where "who can see this field"
and "does this data leave the server" are the same decision.

Two layers that compose without merging

The implementation splits authorization into two boundaries that stack rather
than blend into each other.

The first boundary is firm isolation, and it lives in the query scope layer. A
shared BaseResolver sits under every resolver in the schema, and before any
field logic runs, it scopes the query to current_firm. MyCase is
multi-tenant with law firms as the tenant, so this is the foundational rule:
no query, from any report, ever returns data belonging to a firm other than
the one making the request. Because it lives in the base resolver rather than
in individual resolvers, there is no report and no field that can opt out of
it, forget it, or get it subtly wrong. Firm isolation is not something a
resolver remembers to do. It is the ground every resolver stands on.

The second boundary is field-level access, and it lives in per-type Pundit
policy objects. A trust analytics type has a policy that checks the
view_trust_accounting permission. A financial analytics type has its own
policy, with stricter rules on the PCI-relevant fields, because payment data
carries obligations that a case-count metric does not. The BaseResolver
invokes the appropriate Pundit policy for each type. The authorization logic
lives in the policy object, not inline in the resolver, which means the rule
for a given type is written in exactly one file and read from exactly one
place.

These two boundaries compose without merging. Firm isolation runs first, at
the query scope, and establishes the tenant. Then, inside that already-scoped
query, the field-level Pundit policies run. A user has to clear firm context
before any field-level check is even asked, and clearing firm context tells
you nothing about whether they can see trust balances. The BaseResolver
guarantees the first; the policies guarantee the second. Keeping them separate
is deliberate: firm isolation is a tenancy invariant that must never have an
exception, while field-level access is a permission model that legitimately
varies by field and by domain. Collapsing them into one check would have
meant re-deriving the tenancy invariant every time I touched a field rule.

Why IOLTA and PCI drove the design

It would be easy to present field-level authorization as a nice-to-have that
happened to also satisfy compliance. That reverses the actual causality.
Compliance is why the model looks the way it does.

IOLTA governs how law firms hold client trust funds, and access to trust
account data has to be controlled in a way that can be audited. PCI compliance
for payment-adjacent fields carries a parallel requirement: you have to be
able to demonstrate that cardholder-adjacent data is gated at the data layer,
not merely hidden in the interface. Both of these are questions an auditor can
ask, and both of them demand a specific kind of answer. Not "we filter it in
the UI." Not "each report handles it." The answer that satisfies the question
is "this field is protected by this Pundit policy, and here is the policy
definition."

That answer is only possible because the checks are consolidated. Thirty
reports each making their own access decision against raw tables cannot
produce an audit trail, because there is no single artifact to point at. The
field-level model gives both IOLTA and PCI a concrete, inspectable boundary: a
named policy object, attached to a named type, checking a named permission.
The compliance story is a byproduct of putting the rule in one place, and
putting the rule in one place was only worth doing at the layer where the rule
also controls the data.

The payoff for every report that came after

The operational return showed up in reports I never touched. As described in
Part 5, new reports get composed from an L7 report-definition config rather
than written as fresh SQL. Because authorization lives in the schema and the
policy layer, a new report defined through that config inherits the entire
authorization model automatically. Firm isolation, trust-accounting gating,
payment-field rules: all of it is already there, underneath the field the new
report is consuming.

The important part is what a report author cannot do. They cannot forget to
add a permission check, because the check was never theirs to add. It is not
in the report config at all. It lives one layer down, in the schema, attached
to the type. A new report is a new arrangement of fields that are already
governed. There is no path through the config where a report author writes an
access rule, which means there is no path where they write it wrong.

This is the difference between "authorization-aware" as an adjective bolted
onto a finished GraphQL API and authorization-aware as a property the schema
was designed to have from the start. I did not build the analytics API and
then add access control. I put field-level access in the schema as a
first-class constraint, and that constraint is what made it safe to open the
same API to four separate report domains without each one auditing its own
access logic. The reports did not have to be trusted to enforce the rules.
The layer they query does it for them, once, in the only place where getting
it right and keeping the data on the server are the same thing.

Schema Without Churn: GraphQL Design for an Evolving Metrics Model

Son Do — Thu, 23 Jul 2026 09:50:18 +0000

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

"Semantic layer" sounds like a foundation. Something you pour once, let cure, and build on top of with confidence that it will not move. That framing is exactly backwards for the state we were actually in. During the migration, the semantic model was the least stable thing in the system. Domain owners were seeing, for the first time, what "billable hours" had meant across four different report histories, and they were renegotiating the definition in real time. A write-off excluded from one billing report but counted in another was not a bug to fix; it was a decision nobody had ever been forced to make out loud.

So the GraphQL schema had a genuinely awkward job. It was the authoritative interface that live, in-production reports queried against, and it sat on top of a model whose definitions were still being finalized underneath it. A schema designed the obvious way would have needed a type change every time a metric definition shifted, and every one of those changes risked breaking a report that was already serving law firms. The whole post is about how we shaped the schema so those changes had somewhere to go that was not "break the consumers."

What the schema's boundary is for

In the stack, GraphQL is L3. It sits between the materialized views at L2 (covered in Part 4) and the React report framework at L4. The rule that made everything else tractable is a single sentence: reports query the schema, and reports never write SQL against the materialized views directly.

That boundary is the entire point of having an API layer at all. If reports could reach the views, then authorization, versioning, and consistency would each have to be re-solved by every report author, which is the exact failure mode the project existed to kill. Putting one access surface in front of the model means those three concerns live in one place. When the definition of a metric moves, it moves behind the schema. When a firm's permissions decide what a report can see, that decision is enforced at the schema. The report on the other side of the boundary does not know or care that anything underneath it changed.

The type hierarchy: domain-scoped roots, metrics-specific types

The first real design decision was the shape of the type hierarchy, and I landed on a hybrid. The query roots are domain-scoped, and each one returns a metrics-specific type. The required context lives as arguments on the domain root field, not nested somewhere inside.

type Query {
  billingAnalytics(firmId: ID!, dateRange: DateRangeInput!): BillingAnalytics
  trustAnalytics(firmId: ID!, dateRange: DateRangeInput!): TrustAnalytics
  caseAnalytics(firmId: ID!, dateRange: DateRangeInput!): CaseAnalytics
  financialAnalytics(firmId: ID!, dateRange: DateRangeInput!): FinancialAnalytics
}

type BillingAnalytics {
  billableHours: Float
  invoicedHours: Float
  writeOffAmount: Float
}

A billing report queries billingAnalytics. It does not query cases or timeEntries. That is deliberate: the domain objects that the transactional side of the product is built from do not leak into the reporting layer at all. The reporting layer sees metrics, grouped by domain, and nothing else.

Why this hierarchy and not the other two

Two alternatives were on the table, and rejecting them is what makes the hybrid a decision rather than a default.

The first was to make domain objects the query root and hang metrics off them through entity relationships, so a billing report would traverse something like cases { billingMetrics }. That reads naturally right up until you notice what it does to coupling. It ties the reporting layer to the structure of the domain model. Every report would have to know how to compose through the entity graph to reach a number, and any refactor of how cases relate to matters or invoices would ripple straight into report queries. The reporting layer has no business knowing the domain model's join topology. Separate metrics types sever that dependency: a metric is a field on an analytics type, full stop, and how it is computed underneath is invisible.

The second question was where the context arguments go. Putting firmId and dateRange on the root domain field, rather than nesting them on individual metric fields, means the firm context and the reporting window are always required and always explicit. There is no way to ask for billableHours without having already declared which firm and which date range you mean. In a multi-tenant legal SaaS that is not a convenience; it is the property that keeps firm A's numbers from ever being computable in a query that was scoped to firm B.

The anti-churn rule: additive-only, deprecate, then remove

The type hierarchy solved coupling. It did not, on its own, solve the moving-model problem. That took a rule about how the schema is allowed to change while reports are live on it, and the rule is additive-only.

A new metric is a new field on the relevant analytics type. Existing fields are never removed and never renamed while any report is still reading them. When a metric genuinely needs to go away or change meaning, the old field gets GraphQL's @deprecated directive with a reason, and it stays in the schema, still resolving, until every consumer has moved off it. Only then does it get removed.

type BillingAnalytics {
  billableHours: Float
  invoicedHours: Float
  writeOffAmount: Float

  "Use billableHours; this excluded write-offs inconsistently across firms."
  billableHoursLegacy: Float @deprecated(reason: "Superseded by billableHours as of the unified write-off definition. Remove after all reports cut over.")
}

This is the same lifecycle the metric classes use underneath. Where the Ruby side keeps a VERSION and lets an old and new definition of a metric coexist so history is not silently rewritten, the schema keeps the old field and the new field coexisting so no live report reads a definition that moved out from under it. It is one pattern applied at two layers: a definition change is always a coexistence period, never a hard cutover. When a domain owner renegotiated what "billable" meant, that change landed as a new field with a new definition, the old field went deprecated, and the reports that depended on the old meaning kept working until they were deliberately migrated. Nothing broke on the day the definition changed, which is the only way a moving model and live consumers can share a schema at all.

The payoff at L7: reports as config, not SQL

All of this pointed at one concrete result, and it lives at L7, the report-definition config layer. Because the schema is a stable, composable surface of metrics grouped by domain, a new report is not a new query written against production. It is a config entry that selects fields from one or more analytics types.

That is the mechanism behind the number I care about most from this project: new-report delivery going from two to three weeks down to about four days. A new report required no schema change and no new SQL. It composed fields that already existed on types that already existed, through the same authorized access surface every other report used. The engineer who built the first report on the new platform never wrote a line of SQL against production, and the reason they did not have to is that the schema had been designed so that composing a report was a config problem, not a query-authoring problem.

The insight I would carry to the next system is that the schema was designed for evolution, not for stability. Those are not the same goal, and confusing them is how you end up with a schema that is either brittle or frozen. Designing for evolution meant treating the anti-churn rules as load-bearing constraints from the first line of SDL, not as a governance policy bolted on after the schema was already in production. Additive-only, deprecate-then-remove, context on the root, metrics as fields: those were not conventions we adopted once churn became painful. They were the assumptions the schema was built on, precisely because we knew going in that the model underneath it was going to move.

Part 6 goes into the authorization model that this access surface made possible: why field-level, firm-scoped checks belong at the GraphQL layer and nowhere else.

Never Miss a Refresh: Building the Materialized View Pipeline

Son Do — Thu, 23 Jul 2026 09:50:14 +0000

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 metrics model from the last post gave us one place to define what a business word means. Materialized views gave us a fast place to read it from. But a materialized view is a photograph, not a mirror. It shows the data as it was the last time something took the picture, and the whole speed advantage of the semantic layer rested on a fact that was easy to underestimate: those pictures only stay useful if something keeps retaking them.

I want to invert the Part 0 framing here, because the inversion is the point. The interesting problem was not how to invalidate the cache. It was how to design the refresh pipeline so that cache invalidation stopped being a separate problem at all. Once I saw that the refresh schedule and the cache freshness were the same question wearing two hats, most of the design fell out on its own. Getting there meant being honest about the failure modes of the word "schedule," which sounds solved right up until you ask what happens on the run that doesn't finish.

The refresh pipeline

The scheduler was Sidekiq-Cron, chosen for the least glamorous reason available: Sidekiq was already in the stack. We had workers, a Redis instance behind them, retry semantics, and a web UI that on-call already knew how to read. A dedicated scheduling process, or pg_cron inside the database, would have meant a new operational surface to solve a problem the existing one already covered.

The job itself was deliberately dumb. A single ViewRefreshJob iterated every registered Analytics::Metrics subclass and, for each one, ran REFRESH MATERIALIZED VIEW CONCURRENTLY against that metric's backing view. Because the metrics model already knew every view name (that was the whole point of the registry from Part 3), the refresh job did not maintain its own list of what to refresh. It asked the model, so a newly added metric showed up in the next refresh cycle for free.

The CONCURRENTLY flag was not decoration. A plain REFRESH MATERIALIZED VIEW takes an exclusive lock for the duration of the rebuild, so every report reading that view blocks until the refresh finishes. For a reporting layer that firms hit during business hours, that is a self-inflicted outage on a timer. CONCURRENTLY rebuilds the view alongside the old one and swaps it in, so reads keep hitting the last good copy the entire time. It costs more and requires a unique index on the view, but it buys the one property this layer could not live without: the refresh is invisible to the person reading a report.

Every run also logged its per-view duration to Datadog, which is what the monitoring later hung off of.

Why the background was the right place for the cost

The trade the whole architecture made was to move expense off the read path and onto the refresh path. A report reading from a materialized view answered in single-digit milliseconds, because it was reading a purpose-built table, not reconstructing an answer out of the OLTP schema on every request. The bespoke queries these views replaced took seconds, and they took those seconds while a user waited. Rebuilding a view is not free, but that cost landed in a background worker with nobody watching a spinner. The reports felt instant because the slow part had already happened.

What happens when a refresh fails

Here is the question that separates a schedule from a pipeline: what does the system do on the run that does not finish. A worker gets OOM-killed, a rebuild deadlocks, the database is briefly unreachable. The refresh fails. Then what.

The behavior we designed for, and got, was stale-and-silent. Postgres does not blank out a materialized view when a refresh fails; it keeps the last successful state. So a failed ViewRefreshJob left the view exactly as it was after the previous good refresh. The job failed loudly in Sidekiq, where the failure belonged, but no exception reached the report consumer. The report kept returning the last good data as if nothing had happened.

For this domain, that was the correct failure mode, on purpose. The consumers were billing reports, trust accounting, financial summaries. If a refresh fails at 6am, a firm looking at yesterday's numbers a few minutes into the delay is seeing data that is slightly stale but internally consistent and still trustworthy. Handing that same firm a stack trace where a number should be is strictly worse. Stale data a person can reason about beats an error a person cannot, so the pipeline was built to degrade toward the last known good answer rather than a visible break.

Silent staleness is only safe if it is not actually silent to the people running the system, and that is what the monitoring was for. Two signals, not one. The first was obvious: a Datadog alert on ViewRefreshJob failure, so a failed run paged someone. The second was subtler and more important. We tracked view age directly and alerted if any view had not refreshed within its expected cadence. That caught the failure modes the first one missed: the runs that never threw an exception because they never started, and the ones that hung past the window without technically failing. A job that fails is easy to see. A job that quietly stops running is the one that lets data rot, and the view-age check was the trap for exactly that.

The cache invalidation insight

Everything above sets up the decision I am most attached to on this project, because it is where two problems collapsed into one.

Sitting in front of the views was Redis, and the question was how a cached report answer knew when to stop trusting itself. I looked hard at two standard approaches and turned both down, for reasons that only become visible once you understand how the views actually refresh.

A fixed TTL, cache each answer for N minutes and then evict, has a blind spot that is structural, not tunable. A TTL knows how long it has been since the answer was cached. It knows nothing about when the underlying view last refreshed. Those are different clocks. A freshly refreshed view and a badly stale one, cached at the same moment, expire at the same moment, because the TTL cannot tell them apart. To guarantee freshness you would have to set the TTL short enough to chase the fastest possible refresh, throwing away most of the caching benefit to wait on a timer with no relationship to when the data actually changed.

Event-driven invalidation, bust the specific keys the instant the underlying data changes, is more precise on paper and was a worse fit in practice. Being precise about "when the data changes" means hooking every write path that could change it, across case, billing, trust, and financial domains. That is an N-domain coordination problem, and one missed write path means a report serving stale data with no signal that anything is wrong. It was at its most dangerous during the migration, exactly the window this whole series lives inside, because some legacy reports were still writing directly against the same tables the new model was reading. The domain boundaries were the least stable they would ever be, and this approach demanded I coordinate across all of them at precisely that moment. It was the right tool aimed at the wrong week.

So I keyed the cache to each view's own refresh timestamp instead. The cache key for a report answer included the refresh time of the view it was computed from, and the mechanism that follows is almost too simple to feel like a decision. When a view refreshes, its timestamp changes, so every key derived from it changes, so the next read is a cache miss that recomputes against fresh data. When a view does not refresh, whether on schedule or because a run failed, its timestamp does not move, the key stays valid, and the cache keeps serving the last good answer. A stale view produces a stale key produces a miss, with no invalidation code anywhere in the system. Freshness stopped being a second system I had to keep in sync with the refresh pipeline and became a side effect of the pipeline itself. Updating the data is what invalidates the cache, because the data's own freshness marker is baked into the key. The problem was never how to invalidate the cache; it was how to design the refresh so that invalidation was something the refresh already did.

On the hot paths, the billing and case reports that firms hit on essentially every login, this landed at a 65% cache hit rate. Those are the queries where the same handful of views get read over and over between refreshes, which is exactly the shape timestamp keying rewards: a stable key between refreshes means repeated hits, and the one guaranteed miss per refresh is the miss you want.

The shape of the whole thing

What I keep coming back to is that the failure behavior was layered on purpose. A failure in the refresh pipeline degrades gracefully: the view holds its last good state, the timestamp-keyed cache keeps serving from it, the view-age check catches the staleness before a user does, and the reader never sees an error in a report a firm is using to move client money.

None of the individual pieces here are exotic: Sidekiq, REFRESH MATERIALIZED VIEW CONCURRENTLY, a Redis key with a timestamp in it. The design was in how they compose. Put the expense in the background, let failure fall toward stale rather than broken, and derive the cache key from the same freshness signal the refresh already produces, and the pieces stop being three systems that have to agree with each other and become one system that only has to be right once.

One Definition, 30 Reports: How We Designed the Metrics Model

Son Do — Thu, 23 Jul 2026 09:50:10 +0000

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.

Why We Didn't Just Add More Indexes: Choosing a Semantic Layer

Son Do — Thu, 23 Jul 2026 09:50:07 +0000

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 first thing we tried was the obvious thing. We took the worst-offending
report queries, looked at their execution plans, and added indexes. We tuned
them one at a time, the way you would tune any slow query against a
production replica. It worked. For about two weeks the reports were fast
again and the load on the replica settled down, and it felt like we had
handled it.

Then the next report landed, and the same latency and load problems came
back. That was the tell. When a fix buys you two weeks and then the problem
returns wearing slightly different clothes, you are not looking at a
performance problem. As I covered in Part 1,
what actually broke at MyCase was that thirty-plus reports had each quietly
decided for themselves what "billable hours" meant, and no two of them agreed.
Index tuning treats the symptom of a query we would re-derive again next time.
It does nothing about the fact that the same definitions kept getting
re-derived at all. You cannot index your way out of a governance problem.

So we stopped tuning queries and started asking a harder question: what is
the right level of abstraction to put underneath thirty reports so that
"billable hours" gets defined once. That question had several plausible
answers, and most of this post is about the ones we ruled out, because the
reasons we ruled them out are what pointed us at the semantic layer.

The BI tool that solved the wrong three problems

The most tempting off-ramp was a business-intelligence tool. Something in the
Metabase or Looker family, where analysts build reports against a modeled
data source and you never write raw SQL for a report again. On paper it
addresses exactly what we were complaining about.

We evaluated one and rejected it on three blockers that all applied at the
same time. First, it could not embed natively in the MyCase product UI. These
reports are a product feature that law firms use inside the application, not
an internal dashboard for our own analysts, and a bolted-on iframe to a
separate BI product is not that. Second, it could not enforce firm-scoped,
field-level authorization. MyCase is a multi-tenant legal SaaS, and a trust
accounting figure that firm A can see is a figure firm B must never see, down
to the individual field. General-purpose BI tools are not built to enforce
that kind of per-tenant, per-field rule as a hard invariant. Third, it could
not express our business logic. What counts as billable, how a write-off is
categorized, how trust balances reconcile across three ledgers: that is domain
logic, and encoding it inside a BI tool's modeling layer would have just
moved the thirty-definitions problem into a tool that was worse at owning it
than our own codebase was.

Any one of those might have been workable on its own. All three at once meant
the BI tool was solving a problem we did not have while failing at the three
we did.

The data warehouse that was a later step, not an alternative

The other reflexive answer was a data warehouse. Move the analytics workload
off the transactional replica entirely, into a system built for it. I want to
be precise here, because a warehouse is a genuinely good idea and we did not
reject it on its merits. We rejected it as an answer to this problem.

A warehouse moves data. It does not decide what the data means. If you take
thirty ad-hoc queries and point them at a warehouse, you have thirty ad-hoc
warehouse queries, and each one still gets to invent its own definition of
billable hours on the way through. The consistency problem survives the move
completely intact, now with a bigger bill attached. The semantic layer is the
prerequisite, not the alternative. Once the definitions are canonical and live
in one place, a warehouse can absolutely sit underneath as a performance and
scale decision. But it has to sit under the definitions, not instead of them.

That reframing mattered for sequencing too. Treating the warehouse as a future
step meant we did not block the fix that actually addressed trust erosion on a
much larger infrastructure migration.

The faster database that would have preserved the problem

The last one is the subtlest, and it is the one I have to talk teams out of
most often. If reports are slow and hammering the replica, why not just make
the reads faster: a beefier database, or a caching proxy in front of the
existing queries.

Both of those preserve the actual problem exactly. The meaning of the data
would still live in thirty different places. You would have made thirty
inconsistent answers arrive more quickly. A cache in front of divergent
queries caches divergent results. Part 0 documents this reasoning directly,
and it is the load-bearing insight of the whole project: performance tooling
downstream of competing definitions locks in the mess instead of clearing it.
The fix had to move the definition upstream, and no amount of read throughput
does that.

Why the semantic layer, and why not just materialized views

Here is the distinction that took me the longest to articulate to
stakeholders, because materialized views were part of the eventual design and
it is easy to conflate the two.

The materialized views are a performance optimization. They pre-compute the
shape of the data so a report is not reverse-engineering it out of OLTP tables
on every request. What they do not do is decide what "billable" means. A
report querying a view directly still has to make that decision at query time,
which means we would be right back to thirty query authors making thirty local
choices, just against a faster source.

The semantic layer is what moves that decision upstream and locks it down. In
our case that layer was a Ruby module namespace, one class per named metric,
with the canonical definition living in the class and versioned by convention.

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 class is the single source of truth. Its query method defines the metric,
its view_name points at the materialized view that pre-computes it, and its
VERSION lets a definition change without silently rewriting history under
reports that depended on the old meaning. Reports never write SQL. They call
the metric class. When the business redefines billable hours, we change one
class, bump the version, and every downstream report inherits the new
definition at once. That is the property none of the alternatives could give
us, because none of them had a single place for the definition to live.

The authorization insight that picked the API

One decision was left over. Reports could have queried the semantic model by
running SQL against its materialized views. We put a GraphQL API in front
instead, and the reason was authorization.

Field-level, firm-scoped authorization in a multi-tenant legal SaaS is not a
thing you want thirty report authors each implementing against raw tables.
Get it wrong in one place and a firm sees another firm's trust balance. If
every report hand-rolls its own permission checks, you have thirty chances to
leak data and no single place to audit. The API layer was the one place to
enforce those rules once, for every consumer, as an invariant rather than a
convention. That is the same logic that ruled out the BI tool, arriving from
the other direction: authorization was non-trivial enough that it wanted to
own an interface, and that interface was the API.

Each layer in the eventual stack earned its place by solving a specific
constraint the alternatives could not. The next post gets concrete about the
metrics model itself: how a named, versioned metric actually behaves, and what
changed the day reports stopped writing SQL.