Case 01
Reconciling a firm's data sources without asking it to change them
A firm ran its operations across shared spreadsheets, a CRM and a case-management system, reconciled by hand until the numbers drifted apart. The platform reads those sources as they are, starting with the spreadsheets, keeps personal detail out of the database, and puts one set of reconciled figures on boards that can be traced back to a workbook, a tab and a column.
- Problem
- The firm's trackers and systems disagreed about the same cases, and nobody could say which total was right.
- Built
- A multi-tenant platform that reads the firm's data sources as they are and reconciles them in Postgres, with personal detail removed at ingest.
- Result
- Running on staging with the spreadsheets connected first. Other sources are planned. Not live in production yet.
§1 Context & constraints
I joined a team building AI tooling for law firms, and this firm was the team’s first client. It ran its operations across shared Google Sheets, a CRM and a case-management system, reconciled by hand by whoever owned the tracker that week. The brief was one platform that tracks what is happening, reconciles the sources and, later, forecasts what is coming.
It had to be multi-tenant from the start, so a second firm could be onboarded without its data ever touching the first firm’s.
The firm tracked its cases across several spreadsheets, and the same cases also lived in its CRM and its case-management system. The spreadsheets disagreed with each other, because each one stopped recording at a different stage. None of them recorded approval at all, so a question as plain as “what has been approved” had no answer.
The spreadsheets are the first source connected, and the firm’s own staff maintain them. They were there before the platform. Headers are not always on row one, one tab can hold two tables, and some tabs state their own totals. The platform had to take them as they are, and the CRM, the case-management system and uploaded documents are planned behind the same landing contract.
Constraints
- Read-only: nothing is written back to any of the firm's systems.
- No sensitive personal detail may reach the database, and so none can reach a model, until the compliance questions are answered.
- The firm's own trackers and systems have to keep working as they are, because they are the tools its operations run on.
- A small fixed monthly cost, with model spend capped per firm.
- Targets set for the project: data no more than 24 hours behind its source, and totals that match the firm's own reconciled figures at least 98% of the time, spot-checked monthly.
§2 Architecture
Google Sheets first, read-only. Google Sheets is the first source, read with a read-only scope and shared as viewer on each workbook, so nothing is written back. A CRM, a case-management system and uploaded documents are planned behind the same fixed landing contract. The firm's sources stay as they are: the platform describes them, and they never have to conform to it.
De-identified before anything is stored. A Cloud Run job reads every workbook on a schedule. A case name becomes a keyed surrogate at ingest, so no personal detail reaches the database and none can reach a model. One pull runs per source at a time, a failed workbook is reported while the others still pull, and a failed job raises an alert.
One immutable copy per content. Each snapshot is kept in cloud storage and keyed on its checksum rather than the time of the pull, so a sheet that has not changed writes nothing new. Every figure can be traced back to a workbook, a tab and a column.
Asked only when the rules come up empty. The model sees a tab's title and a masked grid in which every cell is reduced to its shape, never a value. It is asked once per tab or column, and the per-tenant spend ceiling is checked before each call. What it proposes can be taken back.
Isolation enforced by the database. Every table carries a tenant id, and row-level security is keyed to a session variable set from the signed-in session. A bug in application code cannot reach another firm's rows.
Asked before a tab is read. A tab the pull has never seen is described and left alone until an administrator chooses: read it and report its figures, read it and keep its figures off the boards, or do not read it. A description that is declined or superseded withdraws the rows it landed on the next pull.
§3 Decisions
Decision 1
Where the model sits
- Considered
- Letting a model answer from the firm's data directly, by retrieval over the rows, or have it produce the figures.
- Chose
- Code produces every number. A model may explain one.
- Because
- A financial figure has to be reproducible: the same inputs, the same output, every time. A fluent answer built from whichever rows looked similar is worse than no answer, because it is wrong with confidence.
- Trade-off
- Every figure needs a written definition of its terms. Where the firm has not written one, the board says so instead of picking a meaning.
Decision 2
Where personal detail is stopped
- Considered
- Redacting names before anything is sent to a model.
- Chose
- Drawing the boundary at ingest, so a case name becomes a keyed surrogate before anything is stored.
- Because
- The model's reachable surface then has nothing to redact. A redaction pass can miss something, and a schema with no name in it cannot.
- Trade-off
- Records cannot be matched by name, for example to find duplicates between two sheets, because the platform deliberately does not hold the name.
Decision 3
How the first source, a spreadsheet, becomes a table
- Considered
- Asking the firm to restructure its sheets: the header on row one, one table per tab, a case id column.
- Chose
- The platform describes each sheet and the sheet never conforms. Rules read the layout and the meaning of each column first, and a model is asked only about what the rules and a dictionary cannot place.
- Because
- The tracker is what the firm runs on every Monday. A restructuring would be declined, and its cost would move onto them. It also makes a second firm a matter of configuration instead of a project.
- Trade-off
- A wrong reading can land before anyone sees it. So every description records what made it, and one that is declined or superseded withdraws the rows it landed on the next pull.
Decision 4
Where the boundaries are enforced
- Considered
- Checks in application code.
- Chose
- Checks in Postgres: a tenant id on every row with row-level security, and a read-only role for model-written SQL that sees only the reporting schema, one SELECT at a time, with a time limit and a row cap.
- Because
- A bug in application code can bypass a check that lives there. It cannot bypass the database.
- Trade-off
- Every new table needs its isolation policy written by hand, and the model can only see what has been modelled into the reporting schema.
§4 The hard part
The boards were first built on a generated seed, so they could be finished before the firm’s data arrived. When real pulls began, the risk was two populations added together. A figure that mixes invented rows with real ones is worse than either alone, because nothing on the screen says so.
So real data replaces the seed and never sums with it. A scoped delete command removes the seed, the pull refuses a firm that still holds it, and the seed refuses a firm that has pulled. One fact drives all three, and the badge on the screen: does this firm have a snapshot that reports what landed?
The rule had a second half that stopped being true. When the seed was purged, its figures went and its findings stayed, because only a person could produce a finding. Then the pull started raising its own. The demonstration database held 572 findings the pull had raised, while the board showed 29 that someone had typed in earlier.
A second command now takes the seeded findings, and it deletes only what the schema proves is seed: tables nothing else writes, columns only their owner writes, and rows no approval record points at. Four descriptions it removed from real tabs were the point rather than a side effect. A confirmed description is never re-derived, so a guess the seed had marked confirmed stood in front of a tab the rules would otherwise have re-read. Deleting it let them find three stacked yearly blocks where the seed had described one table.
§5 Outcome
The firm’s administrators can add their own workbooks and people, answer for each new tab whether it is read and reported, and trace a figure on a board back to its workbook, tab and column.
As of early October 2026 the platform runs on staging against the firm’s real workbooks. Six boards are served, and the others are staged. A firm is added in the app, its administrators manage their own people and workbooks, and a pull starts from a button, a command or a schedule.
Production belongs on the client’s own cloud project, which has not been provisioned, so it is not live. The analyst agent that would explain these figures is designed and partly built, with its tools, its SQL guard and a fast path that answers without a model, but its loop is not running yet.
What I'd do differently
- Price the model-spend ceiling from billed usage. It is computed from a hardcoded rate card that has never matched an invoice, and a model the card does not know is metered at zero, which silently switches the ceiling off.