Spreadsheet Recalculation Inside a Database: Derived Values That Track Their Own Dependencies
A line item’s quantity goes from 4 to 5. The order subtotal should change, and so should the tax, the order total, the customer’s lifetime value and the regional revenue figure on a dashboard. Which of those actually update depends on which code path made the edit, because each one gets recomputed somewhere different: the checkout handler, the invoice renderer, a nightly job, a SQL view somebody wrote for finance. Every copy of the formula is another chance for two screens to disagree.
The version of this worth building is a database that stores raw facts and formulas side by side, keeps every formula’s value current when a fact changes, and can say why a value is what it is. Spreadsheets have worked this way for decades: a dependency graph, dirty marking when a cell changes, recalculation in dependency order. Users write the formula and never write the code that keeps it current. A database can offer the same deal.
Databases cover one corner already. SQLite has had generated columns since 3.31.0 (January 2020), virtual or stored, and Postgres has stored ones since version 12 and virtual ones since 18. They compute total = price * qty inside a single row, which is the easy case. A generated column can’t see another row, so order.subtotal = sum(lines.total) is out of reach. Hand-written triggers drift from the formulas used elsewhere. A view recomputes on every read, and a materialized view goes stale between refreshes.
The reactive-programming world has better answers. Signals in Solid, Preact and Angular, MobX computed values, Jane Street’s Incremental, Salsa (which rust-analyzer uses) and the research system Adapton all track dependencies automatically and recompute only what an edit touched. Most skip downstream work when a recomputed value comes out unchanged. They prove the algorithms. What they lack is storage: the graph lives in process memory, nothing is transactional, and you can’t ask last week’s value why it was what it was. Mobile developers meet the front-end version in state management libraries. The version here has a durable graph and values that commit with their inputs.
Three Kinds of Formula
A first version needs three kinds of declaration. A row formula reads columns of its own row, which is what a generated column does. A child aggregate folds a column over the rows that point at this one: sum, count, min, max, average. A parent lookup reads a column from the row this one points at, like an order’s tax rate coming from its region. Anything that crosses tables is a chain of those, so the engine never needs a general join. Here’s the order example in invented syntax, for a tool that doesn’t exist yet.
ref line.order -> order
ref order.customer -> customer
ref order.region -> region
line.total = price * qty
order.subtotal = sum(line.total)
order.tax = round(subtotal * region.tax_rate, 2, half_up)
order.total = subtotal + tax
customer.lifetime = sum(order.total)
The engine builds the dependency graph when it loads these. Nodes are columns, edges run from inputs to the formulas that read them, and a topological sort rejects any cycle with a message that names it. Spreadsheets let you switch on iterative calculation for circular references. A database shouldn’t, because a value that depends on itself has no single answer a transaction can commit.
The graph is per column, but the work is per row. Update line:2.qty and the engine marks line:2.total dirty, follows the line.order reference to that order’s subtotal, then tax, then total, then follows order.customer to the lifetime value. It recomputes in graph order, which avoids what reactive libraries call a glitch: order.total built from a fresh subtotal and a stale tax. If a recomputed value matches the old one, propagation stops there. A write that changes a reference, like moving a line to another order, dirties two parents: the one it left and the one it joined.
Then comes the choice every reactive system makes: recompute eagerly, or on demand. Eager means the work happens inside the writing transaction. Reads become lookups, a formula that fails (divide by zero) rejects the write that caused it, and a reader never sees qty = 5 next to a total computed from 4. The price is write time that grows with fan-out. Lazy means mark dirty and compute on the next read: cheap writes, a slow first read, and errors that show up far from their cause. Default to eager.
Aggregates should apply deltas. A sum changes by new minus old, a count by one, and an average is stored as a sum and a count. Min and max are the awkward pair. Inserts are easy, but deleting the current minimum leaves you hunting for the next one. On SQLite the engine can keep an index on the parent reference and the value, which turns the hunt into a single index probe.
Explain Is the Feature
Spreadsheets let you trace a cell’s precedents. Databases have nothing like it, so a wrong number turns into an afternoon of reading code. The engine already holds the graph and the stored inputs, so it can print the chain:
$ derive explain order:1042.total
order:1042.total = 213.93 subtotal + tax
subtotal = 199.00 sum(line.total), 3 lines
line:1.total = 99.00 price 19.80 * qty 5
line:2.total = 60.00 price 30.00 * qty 2
line:3.total = 40.00 price 20.00 * qty 2
tax = 14.93 round(subtotal * region.tax_rate, 2, half_up)
region:north.tax_rate = 0.075
That output doubles as the design test: if the chain can’t be printed, the dependencies aren’t really tracked. Record each recomputation as a change with its cause attached, as a change-history database would, and the same command can answer last Tuesday’s version of the question.
Where It Gets Expensive
Write amplification comes first. One quantity edit touches about five values, which is fine. Change a region’s tax rate and every order in that region is dirty, plus every customer who ordered from it. Say that’s 40,000 orders: an eager engine recomputes tax and total for every one of them inside a single transaction, holding the write lock the whole time. Two defenses help: a dry run that reports the fan-out before you commit, and lazy mode for formulas with wide fan-out.
Money is second. SQLite has no decimal type, so the engine stores fixed-scale integers and carries the scale on each column. Every formula also has to state its rounding rule, because the answer depends on it: 7.5% of 199.00 is 14.925, which is 14.93 rounded half up and 14.92 rounded half to even. Tax computed per line and summed can differ from tax computed on the subtotal by a cent or two. Both are legitimate. The formula says which one it means, and explain prints it, as above.
Concurrency is milder than it looks on SQLite, which allows one writer at a time. On a multi-writer database it gets worse: two transactions editing different lines of one order both want that order’s subtotal. The safe form applies the delta in the update itself (SET subtotal = subtotal + :delta). Recomputing the sum and writing it back loses an update under read committed isolation.
Changing a formula means recomputing every row it touches, which is a backfill, so each stored value should carry the version of the formula that produced it. Formulas also have to be deterministic. now() is out; if a value depends on the date, the date becomes an input row that something updates on purpose. That’s where fact expiry plugs in: a derived value is only as fresh as its stalest input, so its expiry should be the earliest of its inputs'.
What v0.1 Leaves Out
The first version is a library over one SQLite file. It reads a formulas file, creates the stored columns, and generates the triggers that maintain them, using built-in functions only, so any program that opens the file keeps the values current. It supports the three formula kinds, integers and fixed-scale decimals, cycle detection at load time, eager recomputation, explain and the dry-run plan. It refuses joins that aren’t a chain of references, user-defined functions, floating-point money, volatile functions and lazy mode.
Two neighbors share machinery. Incremental view maintenance keeps the answer to a query current, where this keeps named values on entities current, and the child aggregates above use the same delta math. A feature store makes the same promise for machine learning: define a value once, and have training and serving read the same computation. The people most likely to pay are the ones whose pricing, quoting or budget model has outgrown its spreadsheet.
Build explain first. Numbers nobody can trace get recomputed by hand.