Cost Analyzer: Automated Account Reconciliation

The problem

Frao runs on roughly a dozen paid services — model APIs, cloud, hosting, domains, infrastructure — and each one sends the company a different document in a different format at a different cadence: invoices, receipts, payment receipts, statements, and the occasional reimbursement. Reading each one, deciding what it is, filing it, and booking it is exactly the kind of task that quietly consumes a person once a month and then twice a month, and it is the kind of task where the errors stay invisible until they are not.

The Cost Analyzer is the answer. It is a Rust service that takes raw financial documents — PDFs, CSVs, provider exports — and turns them into a classified, deduplicated, hash-chained ledger, regenerates monthly reports, mirrors everything to Google Drive, and reconciles on-chain wallet movement against the books. It is exposed through the company portal under the operator role at /cost-analyzer, one of the fifteen services in the Frao stack. This post walks the whole pipeline and the data-integrity decisions baked into it.

What the operator does

For the person running the company, the pipeline is deliberately boring: documents land in an inbox, the operator runs Analyze, the operator reviews what the system flagged, and the reports update themselves. Everything else is the pipeline’s job.

The operator’s day reduces to three actions. Drop documents — or let them arrive from Drive and the billing APIs. Press Analyze. Review the short list of things that genuinely need a person: near-duplicates, low-confidence documents, and reconciliation candidates. The reports, the Drive mirror, and the Sheets tabs are all regenerated in the same run. No manual filing, renaming, or journal entry.

What the pipeline does

Under the hood, a single Analyze run moves each document through seven stages:

1. Ingest — three sources: a Google Drive inbox, a local inbox/ directory, and billing-provider APIs (DeepSeek balance, Google Cloud Billing) that synthesize receipts into the inbox.
2. Classify — a deterministic regex/keyword scorer, not a model. It reads filename and content markers to decide type (invoice, receipt, payment receipt, statement, deposit, charge, investment), legal person, date, amount, currency, and invoice number, then emits a confidence score — 0.4 type + 0.3 person + 0.15 date + 0.15 amount. Below 0.5 the document is filed but flagged for manual review.
3. Dedupe — a four-rung ladder: exact content hash → already processed; same provider + invoice + type → near-duplicate, quarantined for review; same invoice across complementary types (invoice ↔ receipt) → both booked as a net-zero pair; near-identical text → quarantined.
4. Organize — rename to a canonical YYYYMM_provider[_subprovider]_type_<hash12>.ext and file it under the vault.
5. Register — upsert into processed_documents keyed by content hash; re-seeing the same file increments seen_count instead of creating a second row.
6. Report — regenerate the affected month’s report from the ledger.
7. Settle — sync wallets, run reconciliation, mirror the vault to Drive, sync Sheets.

Every stage writes an event to the ledger, and the run streams live progress to the portal over SSE.

Architecture

The separation is clean because the frontend and backend are different services. The Svelte 5 portal lives in the company-portal app; nginx rewrites /api/cost/* to cost-analyzer-svc:3030/api/v1/*. The backend is axum on PostgreSQL, with distinct modules for the pipeline (analyzer), classification (classifier), filing (organizer), the ledger (ledger), reporting (reporter), and reconciliation (wallet/reconciler). The data model splits responsibilities across five tables: processed_documents (the canonical registry), cost_ledger (the immutable event chain), wallet_transactions (raw on-chain events), cost_reports (per-period snapshots), and investments (per-partner attribution).

Storage: one source of truth, two homes

The canonical copy is a local vault with a self-describing taxonomy:

vault/<year>/<month>/<legal_person>[/<sub_provider>]/<canonical filename>

The canonical filename embeds the period, provider, type, and a content-hash fragment, so the tree is sortable, greppable, and self-documenting. Supporting state lives beside it: .ledger/ for the chain log, cost-reports/, .processing/, and .quarantine/.

Google Drive is a mirror of the same taxonomy — a root folder containing inbox/ (with a .processed/ subfolder) and vault/<year>/<month>/<provider>/. A DRIVE_PROTOCOL toggle (gdrive:// vs local://) makes Drive an option rather than a dependency: when Drive is unreachable or times out, the service falls back to the local tree instead of failing. Uploads are deduplicated — files already present in the target folder are skipped — and each uploaded file’s Drive ID is written back to processed_documents.drive_file_id. Sheets attachments therefore link to drive.google.com/file/d/<id>, never to a local path a human could not open.

Data quality and integrity

The interesting work is not the pipeline; it is keeping the pipeline from quietly corrupting the books. The integrity measures are baked into the data model:

• Content-addressable everything. Documents are identified by SHA-256 over normalized text — lowercased, whitespace-collapsed, lines sorted so formatting differences do not matter. The same invoice dropped twice is one row, seen twice.
• An immutable ledger. Every event — discovered, classified, renamed, organized, reconciled — is chained to the previous event by hash, from a genesis entry onward. GET /api/v1/ledger/verify walks the chain. Reports record ledger_tail, the hash of the chain at generation time, so any report can be reproduced against the exact ledger state it was derived from.
• No silent guesswork. Unknown dates and currencies error rather than approximate — an unbookable EUR amount is skipped with a warning, never silently booked at 1:1. Declined payments and balance summaries are forced to the statement type, which carries no amount, so they cannot double-count. Labeled totals are preferred over largest-$ heuristics, with a guard against subtotals being misread as totals.
• Idempotency by construction. Every insert is an upsert: documents by content hash, wallet transactions by (chain, tx_hash, event), reconciliation pairs by (document, transaction), investments by partial-unique indexes. Re-runs cannot double-count; the only thing that changes on a re-run is seen_count and last_seen_at.
• Funding discipline. paid means company-wallet money out — and only that. Out-of-pocket advances are attributed personal_advance with a settled_by_reimb marker and excluded from paid until a reimbursement settles them. A partner-paid invoice is booked as both an expense and a capital inflow, never as paid. Net Outstanding = Invoiced − Paid; Balance Position = Investments − Net Outstanding.
• Stablecoin filtering. Report figures count only official stablecoins (USDT, USDC, BSC-USD). Meme coins, native transfers, gas rows, and homoglyph-spoofed symbols are excluded, and near-stablecoin lookalikes are logged with a warning.
• Bounded failures. Drive listing is time-boxed before falling back to local. Per-provider and per-wallet failures are recorded and surfaced without aborting the whole run. Errors auto-report to the resolution collector.

Reconciliation: matching when payments don’t line up

Banks have had a few centuries to make reconciliation boring. We have had a few months. The textbook method is pairing: every credit meets an equal debit, every invoice meets a matching payment, and the two sides must agree to the cent. That works when payments are complete and one-to-one.

For an early company, the ideal is the exception. Our first reimbursement is the canonical example: REIMB-2026-07-001 booked $144.51 in, the wallet paid $136.51 out, and the difference carried into the next reimbursement because the wallet ran dry. A partner pays the IONOS invoice directly from their own card — that is an investment, not a company payment. The operator covers most expenses personally, and those advances become company paid only when a reimbursement settles them. Insisting on perfect 1:1 matches in that environment means most of the ledger never reconciles.

So the reconciler scores matches instead of requiring them. Every unmatched wallet transaction is scored against every unmatched document on four signals — direction, amount proximity, date proximity, and token type — producing a confidence in [0,1]. Above 0.6 auto-matches; 0.3 to 0.6 becomes a candidate flagged for review; below that it stays unresolved. Manual matches bypass scoring at confidence 1.0. Pairs are idempotent: re-running keeps the higher confidence and never double-counts. A partial status and a matched_confidence column exist precisely so a match that is not exact is still representable.

What it does not yet do is allocate. A partial payment is still one document and one transaction — there is no splitting of a single $136.51 payment across the documents it settles, and no residual that rolls forward explicitly. The next step is percentage-based matching: a $136.51 payment against a $144.51 debt books 94.5% matched and leaves an explicit $8.00 residual that the next reimbursement settles, instead of carrying the difference invisibly. That is the direction the model is heading, and it is the honest way to reconcile a company still learning how it pays its own bills.

Reports: monthly detail and global position

Per-month reports carry the full accounting — total invoiced, total paid, net outstanding, wallet inflow, and balance position, broken down by provider and type with per-document detail. They are stored in cost_reports keyed by their ledger tail, and written to disk as JSON and Markdown. The Global Position tab is the all-period aggregate of the same numbers: total expenses, partner inflows, net outstanding, and balance position.

Four tabs make up the spreadsheet:

Sync is defensive: /sheets/sync writes to a throwaway test spreadsheet first; the Analyze run writes to the real one.

The bottom line

The Cost Analyzer exists because bookkeeping was about to consume the people it should be serving. It encodes the company’s financial rules as invariants — paid means company-wallet money out, advances are attributed and settled by reimbursement, partner capital is tracked as investment — so the numbers stay defensible while the company is still deciding how it operates. The pipeline is the boring part, and that is the point.