Rebuilding Margus: A Workflow Where Partial Data Can't Exist
I spent a long time figuring out a system that had never been fully written down anywhere, and every time I thought I understood it, a piece of real data proved me wrong.
The business logic lived in spreadsheet formulas, not documentation
JL Consulting Engineers, a fire protection engineering firm, ran their entire business out of one Excel workbook. Projects, invoices, cheques, an archive of old invoices, all of it in sheets that had grown organically over years. There was no separate document anywhere describing how a project's status was actually determined, or what counted as urgent, or how a fee got calculated. The rules existed, but they existed as formulas sitting in cells, and formulas don't explain themselves.
So the first real engineering work on this project wasn't writing a schema. It was reading formulas and writing down, in plain English, what I thought they meant, then checking that against real rows of real data until I was confident enough to encode it permanently. Here's an actual comment from that process, kept in the migration script on purpose as a record of the reverse engineering itself:
// progress: string; //progress =IF(L3=0, "Quote", IF(AC3=1,"Waiting",IF(AB3=1,"n/a", // IF(AA3>0,IF(AI3>0,"WIP","Done"))))))) where 3 denotes row_number // urgent: boolean; // =IF(C3="Inspect","No",IF(C3="Not Urgent","No",IF(C3="Wait D","No", // IF(C3="Done","No",IF((AP3+BE3)<0,"Yes",IF(C3="Amend","Yes",IF(C3="Form 4","Yes", // IF(C3="New Rev","Yes", "No")))))))) // work_required: number; // =ROUNDDOWN(IF(AA3=0,0,AA3-V3*AE3-W3*AF3-X3*AG3-Y3*AH3),3) // where denotes rownumber I think
I left that last "I think" in on purpose. That's not a confident engineer explaining a system. That's someone doing archaeology on nested IF statements and being honest about where the confidence ran out. A formula that references a column by its spreadsheet letter is a formula that will not tell you what it means, only what it computes, and those aren't the same thing when the person who wrote it originally isn't in the room to ask.
The messiness wasn't only in the formulas, it was in the data itself, and it showed up in ways that would have been invisible if I'd trusted the spreadsheet's shape instead of the actual values inside it. Invoice numbers repeated, so I had to invent a deterministic scheme to make every one unique without losing the original number (originalNumber + (count + 1) * 100000, so a second 18038 became 118038, still traceable back to the source). Some rows had no invoice number at all in the column meant to hold one, and fell back to a completely different column instead. Credit note numbers were sometimes stored as numbers and sometimes as text like "CN0217", which meant the migration script had to sniff the type of every cell rather than assume one. Some sheets had duplicate header names, so columns had to be read by index instead of by name. And where a customer or architect name in a row didn't match any canonical entity I could find, I didn't guess and I didn't silently drop the row, I wrote it to an audit list instead: these names were not inserted, go check the source data.
None of that is exotic. It's the ordinary, unglamorous texture of a real spreadsheet that real people typed into by hand for years. But it's exactly the kind of thing that never shows up in a requirements document, because nobody writes a requirements document for a system that's already running. You only find it by trying to migrate the real data and watching it fight back.
A workflow where partial data can't exist
Once I understood what the old system actually did, the design goal for the new one was specific: make it structurally impossible for a record to end up in a state that's neither one thing nor the other. Not "validate against that," prevent it from being reachable at all.
The old process had an entire side sheet in Excel just for this problem. It logged things like "no client data on file," "no fee proposal on file," "missing client details," cases where someone had started something without everything it needed and a human had to notice and flag it later. That sheet was a symptom. The actual fix was making those states unreachable in the first place: a proposal can't be generated without a linked customer and calculated fees already existing, and a project can never be created until a proposal has actually been accepted. There's no code path that produces a project with a missing customer, because there's no code path that produces a project before a customer exists.
The clearest example of "unreachable by construction" is the payment confirmation function that turns an accepted proposal into a live project. It doesn't try to gracefully handle the case where the quote or project it's supposed to attach to can't be found. It refuses:
if v_vat.project_id is null then raise exception 'Cannot confirm payment: no quote or project found for this invoice. It stays awaiting confirm.'; end if;
If neither a quote nor an existing project can be resolved, the whole operation fails, on purpose, and the invoice stays in its "awaiting confirmation" state rather than getting nudged forward into something ambiguous. Fail closed, not open. I'd rather have a payment sitting visibly unresolved than a project that quietly exists without the paperwork that's supposed to justify it.
The other structural gate governs the physical sequence of the work itself: design and construction can happen in parallel, but Form 4 cannot be signed off until construction is recorded as fully complete, and the final inspection can't be entered until Form 4 is complete. That's not a UI rule someone could route around by hitting an API directly. It's a database trigger:
CREATE OR REPLACE FUNCTION public.enforce_project_phase_gates() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NEW.form_4_progress IS DISTINCT FROM old_form4 AND COALESCE(NEW.form_4_progress, 0) <> 0 AND NOT public.project_progress_is_complete(NEW.construct_progress) THEN RAISE EXCEPTION 'Form 4 progress cannot be entered until Construct is 100%% complete.'; END IF; IF NEW.inspect_progress IS DISTINCT FROM old_inspect AND COALESCE(NEW.inspect_progress, 0) <> 0 AND NOT public.project_progress_is_complete(NEW.form_4_progress) THEN RAISE EXCEPTION 'Inspect progress cannot be entered until Form 4 is 100%% complete.'; END IF; RETURN NEW; END; $$;
Enforcing this in the database, not just in the frontend form, matters more than it might look like it does. A frontend rule stops someone clicking the wrong button. A trigger stops the wrong data from ever landing in the table at all, regardless of which piece of code, or which future piece of code nobody's written yet, tries to write it.
The quote to proposal handoff has its own version of the same discipline. A quote is either a draft, editable freely, or active, which means a proposal has been sent and the quote is now locked. Unlocking it isn't a toggle, it's an action restricted to admins that explicitly invalidates the live proposal rather than deleting it, and a partial unique index guarantees at most one live proposal can exist per quote at any moment:
CREATE UNIQUE INDEX idx_proposals_one_live_per_quote ON public.proposals (quote_id) WHERE invalidated_at IS NULL;
There's no window where two proposals for the same quote are both considered live. The constraint makes that state impossible, rather than trusting every future code path to remember not to create it.
Billing as the work gets earned, not just at the start
The deposit invoice only covers the first 30%. The rest of the fee gets earned gradually, as design, construct, form 4, and inspect each progress, weighted 80/10/5/5 in that order, since drawing up the design is where most of the actual engineering effort sits, and inspection at the end is comparatively quick. That weighting raised a question the old spreadsheet already had an answer to: how much of the total fee has actually been earned but not yet invoiced, at any given moment.
The Excel workbook computed this as one of its more important formulas, an "AP" column: the fee pool times how much of the weighted phase work is done, minus whatever's already been invoiced. I ported that calculation directly rather than reinvent it, and it now runs live in the app, recomputed every time any of the four phases' progress changes, not just the last one.
What I didn't do was wire a phase crossing 100% straight into Xero. Automatic milestone invoicing looked appealing at first and I looked hard at it, but the fee is split across five disciplines while progress is tracked across four phases, and there's no clean, honest mapping between the two that holds in every case. Firing an invoice automatically off a number that doesn't cleanly correspond to the fee structure underneath it would have meant occasionally invoicing the wrong amount with total confidence, which is worse than not automating it at all.
Instead, the live AP number feeds a dedicated "To Invoice" tab, sitting right alongside Urgent, All, the four phase tabs, Done, and Archived, one worklist instead of four separate phase tabs someone would otherwise have to check for a number greater than zero. From there, sending an invoice is a deliberate action taken with a single click, not an automatic one, built on the same Xero invoice creation path the deposit invoice already uses. It doesn't happen only once either. Every time another phase clears, the AP number climbs again, the project reappears in that worklist, and the same action fires again, on repeat, all the way through to inspect. One edge case mattered enough to handle explicitly: if prior payments or credits already cover more than what's currently earned, the action still goes through, it just records the invoice as already paid, purely for the client's tax records, rather than blocking it because there's nothing left to collect.
Making sure the same money couldn't be spent twice
Once real payments started flowing through Xero, "prevent partial data" turned into a sharper problem: prevent the same payment, invoice, or webhook event from ever being processed more than once, even under concurrency, even under retries, even when the network in between fails in an ambiguous way that doesn't tell you whether the first attempt actually succeeded.
The two writes that matter most, locking a quote into "active" and recording a proposal against it, are split into what I ended up calling two atomic transactions, and the first one is written to win a race deliberately rather than assume there won't be one:
update public.quotes set status = 'active', updated_at = now() where id = p_quote_id and status = 'draft' returning * into v_quote; if not found then raise exception 'Quote not found or not in draft status'; end if;
If two admins click approve on the same quote within the same second, one of these updates wins and the other gets a clean, honest failure instead of both silently succeeding and producing two active states for one quote.
Payments get the same treatment, with a unique index that makes a specific failure mode structurally impossible: vat_invoices.xero_payment_id is unique, so a single Xero payment can never be recorded against two different invoices. And when a second full payment lands on an invoice that's already marked Paid, and I don't know why, the system doesn't guess and doesn't quietly file it as a duplicate. It routes to a human review queue with a reason attached, already_paid_match, because a duplicate payment on paper might not be a duplicate at all, it might be a real second payment against a real mistake somewhere upstream, and that's not a decision software should make silently on someone else's money.
Creating a new invoice in Xero carries its own idempotency key, generated once and persisted, then sent on every retry of that same logical attempt using Xero's native Idempotency-Key header. But the more interesting case is what happens when the create call itself times out and I genuinely don't know if it went through:
} catch (fetchErr) { console.warn('[deposit-invoice] Xero create ambiguous, reconciling…', fetchErr) created = await reconcileByReference(tenantId, reference) if (!created) throw fetchErr }
Rather than blindly retrying and risking a duplicate invoice, it goes and asks Xero directly whether an invoice with that reference already exists before deciding what to do next. Concurrent confirmations on the same payment are blocked at an even lower level, with an explicit row lock taken on both the invoice and the project before either gets touched, classic pessimistic locking, because two admins confirming the same payment within the same second is exactly the kind of coincidence that will eventually happen if the system runs long enough.
Every inbound webhook event gets the same discipline applied to itself. Each one is hashed into a stable key from its tenant, category, type, resource, date, and URL, and inserted under a unique constraint. A conflict on that insert means one of two things: this exact event was already fully processed, in which case it's skipped, or a previous attempt started and never finished, in which case it's retried rather than silently ignored. And underneath all of it, there's a decision I think matters more than any single guard: the app keeps no local ledger of a customer's available credit at all. Credit is read live from Xero every time it matters, never cached, never trusted as a local number that could quietly drift out of sync with the one source of truth that actually holds the money.
Webhooks fail quietly unless you assume they will
Xero signs every webhook payload with HMAC-SHA256, and that signature gets checked with a comparison designed to run in constant time rather than a plain string equals, specifically so a failed check can't leak timing information about how close a forged signature got to the real one. But even a request that fails that check doesn't just get a 401 and vanish. It gets logged to a deliveries table first, with the specific reason recorded, because a rejected webhook is still a fact worth having on record, not a fact worth throwing away.
Every event inside a delivery is tracked individually, processed or not, with its own error if it failed, and the response status for the whole delivery flips to 500 if even one event inside it failed to process. That's deliberate: a 500 tells Xero's own retry mechanism to try again later, so a failure on my end doesn't quietly become Xero's problem to never revisit.
Xero doesn't actually have a payment webhook category at all. Applying a payment to an invoice emits an invoice update event instead, so the system treats invoice update as a proxy signal and does its own filtering downstream to decide whether that update actually represents a payment landing.
None of that is trusted as sufficient on its own, which is the real lesson underneath all of it. Xero silently disables a webhook subscription after 24 hours of failed delivery, with no visible status API per subscription to check, which means a webhook can simply stop arriving and nothing about the webhook system itself will tell you that happened. So there's a separate daily reconciliation job that doesn't wait to be told anything is wrong. It pages through everything Xero has recorded as changed in a lookback window (with an overlap of five minutes so nothing falls exactly on a boundary), compares that against the resource IDs the webhook already claimed to have processed, and reports a count of anything that changed in Xero but was never seen by the webhook at all. On top of that, a scheduled health check sends a synthetic test event, signed by itself, to the live webhook endpoint, on a schedule, and alerts if the endpoint doesn't answer the way it should. The system never treats "the webhook fired" as proof that the system's state is actually correct. It treats it as a claim, and checks the claim on a schedule regardless of whether anything seemed to go wrong.
Supabase underneath all of it
The database side runs on Supabase Postgres with row level security doing the real access control, and the pattern that makes the RLS policies workable at all is a small security definer function that reads a user's own permission level without recursively triggering RLS on the very table it's reading from:
create or replace function public.current_user_permission() returns public.user_permissions language sql stable security definer set search_path = public as $$ select user_permissions from public.users where user_id = auth.uid() and is_active = true limit 1; $$;
Three roles exist, admin, editor, viewer, and the split isn't uniform across every table. Editors can read and write most operational data, but invoices and cheques can only be written by admins, even for editors, because that's the one category of data where a mistake costs real money rather than just needing a correction later.
Authentication itself gets verified again on every edge function call rather than trusted from a decoded token, because a token that looks structurally valid isn't the same as a token Supabase's own auth service still considers live. 23 edge functions carry the actual business logic: quote and proposal emails, PDF generation and signed document uploads, the entire Xero integration (auth, webhook receipt, more than six reconciliation and sync jobs, contact and invoice management), and a set of end to end test fixtures scoped to a dedicated demo tenant so testing the payment flow never risks touching a real client's real accounting data.
Writing straight to OneDrive, and the bug when signing in that taught me not to trust my own notes
Quote drawings and signed proposal PDFs used to sit in Supabase Storage, backed up to OneDrive after the fact through a scheduled job. That was always meant to be temporary, and it's since been replaced with the real thing: both file types now write directly to OneDrive from the server, over Microsoft Graph, using one token shared at the app level rather than anything tied to a person's own login. The reference kept on each row is the Graph driveItem.id, not the folder path, because staff rename and reorganize these project folders by hand constantly, and a path breaks the moment someone does. The path sticks around only as a display label. A failed write gets logged to its own table and surfaced in Settings rather than disappearing silently into a cloud to cloud call nobody happens to be watching.
Getting the authentication behind that reliable enough to trust, first for the backup job and now for the app's own live uploads, produced the most humbling bug of the whole project, and it's worth telling honestly because the mistake wasn't a typo, it was a wrong belief I'd written down as if it were a fact.
I'd documented, confidently, that the Microsoft tenant ID used for this OAuth flow and the generic "consumers" alias were interchangeable for this purpose. They are not. Using the specific tenant ID instead of the correct standard alias meant that any account which wasn't already a member of that exact Microsoft tenant got rejected outright, with an error saying the account didn't exist in that directory and would need to be added as an external user. That's exactly what happened the first time someone outside the tenant tried to sign in for real. It wasn't a theoretical edge case I found by reading the docs more carefully. It was a real failure, on a real attempt to sign in, that I only understood once I'd watched it happen and gone back to figure out why my own note had been wrong.
The fix was using the actual correct standard alias for "any organizational or personal Microsoft account," not the narrower one I'd assumed was equivalent. I corrected the internal documentation the same day, and I left the correction visible rather than just quietly fixing the value, because the useful part of that story isn't the single line of code that fixed it, it's that I'd written something down with confidence that turned out to be wrong, and the only way I found out was a real person hitting it.
One file still genuinely lives in Supabase Storage rather than OneDrive: the outbound proposal PDF, cached purely so a retried send resends identical bytes instead of risking a second render producing something subtly different. That one still gets backed up on the same schedule, and the restore side of it gets treated with the same caution the payment logic does. There's a drill for restoring a single file that never overwrites live data by default, a restore of the whole bucket for a bigger recovery, and a genuinely destructive full recovery procedure that requires explicitly flipping an overwrite flag on, checking the result by hand, and then explicitly flipping that flag back off again afterward. Restoring from a backup is not a button I want to be able to press by accident, and the runbook is written so that it can't be.
Not disturbing an ecosystem that already worked
None of this got built in isolation. The client's accountant already had a chart of accounts and tax rates set up in Xero, so the system reads those live and picks a sensible match rather than hardcoding an account code that would only be right until someone reorganized their books. Xero's own invoice numbering sequence was left completely alone, the new system's own reference numbers ride alongside it rather than replacing it, specifically so the accountant's existing numbering continues exactly as it always had. A legacy invoices view still exists in the app today, restricted to admins, purely so the history from before this system, from the spreadsheet era, stays visible and usable instead of getting orphaned the moment the new system takes over. The scanned PDF archive of old invoices in OneDrive was left exactly where it was too, untouched, because the numeric data needed for the new system came from the spreadsheet, not from reprocessing decades of scanned documents nobody asked me to touch.
Even the formulas that calculate fees were kept bit for bit identical to their Excel originals, including a genuine spreadsheet quirk where one running total was never clamped to zero. I could have "fixed" that. I didn't, because the client's bookkeeper had years of numbers she was used to seeing come out a certain way, and quietly changing the math to what I thought was more correct would have meant her trusted numbers stopped matching reality the moment she checked them, for a discrepancy she never asked me to solve. And to this day, the system's write access into Xero itself is still scoped to a separate demo company, not the client's real accounting tenant, until every one of these guarantees has been proven safe enough to earn the right to touch the real books.
What I'd tell myself at the start
If I'm honest about the single biggest miscalculation on this project, it wasn't a technical one. It was underestimating how long it would take to find the actual rules a working system was already running on, because they were never written down anywhere I could read them in advance. The technical work, the phase gates, the idempotency keys, the reconciliation jobs, all of that was tractable once I understood what I was building. Finding out what I was building, one spreadsheet formula and one inconsistent row at a time, was the part that took the longest, and it's the part that's easy to leave out of the story entirely once everything's finished and working.