Building a VAT Return System with Claude Code from Your Invoices and Expenses Database

This is the long-form deep-dive. For a quicker, skim-readable version, see the companion blog post.

1. Why I built this

Every UK small business with taxable turnover over the VAT-registration threshold has to compute and submit a VAT return - usually quarterly - through HMRC's Making Tax Digital (MTD) service. Nine numbered boxes. Output VAT minus input VAT. Pay the net amount, or claim the refund. Easy in theory; punishing in practice when your invoices live in Postgres, your expense receipts are PDFs from twenty different suppliers, your bank statements are CSV from Wise and Revolut, and your foreign-currency lines have to be re-stated in pounds at the right monthly rate.

For a few years I muddled through with a giant spreadsheet, a calendar reminder, and a small leap of faith. Each quarter took roughly half a day, most of which was data wrangling - export a CSV from the invoices DB, paste into Excel, hand-classify a few hundred rows, double-check formulas, copy nine numbers into a third-party MTD bridge, click Submit. The audit trail was "trust me, I had a spreadsheet". Mid-quarter rate changes broke it. Receipts went missing. One misplaced classification could push Box 5 off by hundreds of pounds.

I rebuilt the pipeline this year as a small Claude Code project that talks to my invoices and expenses database directly, classifies each line, computes Box 1 to Box 9 with a deterministic Python engine, dry-runs the return as a Markdown report, and only after a human signs off submits to HMRC MTD. The whole quarter now takes me about an hour, most of which is reviewing the dry-run with my accountant. The audit trail is a git repo. The maths is unit-tested. The classifier is a documented Markdown file. The submission is gated.

This article is the long version. Prompts, Python, schema, the HMRC OAuth flow, the edge cases. If you want the short version first, read the companion blog.

2. HMRC MTD primer (and disclaimer)

Making Tax Digital for VAT is HMRC's requirement that VAT-registered businesses keep digital records and submit returns through compatible software via a documented API rather than typing numbers into the gov.uk web form. The API is OAuth2, requires fraud-prevention HTTP headers identifying the originating device, and is available in a free sandbox before you ever touch live production.

The return itself is nine boxes: Box 1 is VAT due on sales and other outputs; Box 2 is VAT due on acquisitions of goods from EU member states (relevant for Northern Ireland businesses under the NI protocol); Box 3 is the total VAT due (Box 1 + Box 2); Box 4 is VAT reclaimable on purchases (input VAT); Box 5 is the net VAT to pay or reclaim (Box 3 - Box 4); Box 6 is total sales excluding VAT; Box 7 is total purchases excluding VAT; Box 8 is total value of goods supplied to EU member states (again NI-only); Box 9 is the total value of acquisitions from EU member states (NI-only). There are scheme variants - flat-rate scheme, cash accounting, annual accounting - that change how you arrive at the numbers but not the box shape.

Disclaimer. I am a software engineer, not a chartered accountant. This article is not tax advice. The exact rules for VAT classification, partial exemption, the flat-rate scheme, the place-of-supply rules for digital services, the post-Brexit treatment of EU goods and services, and the NI protocol all change over time and depend on your specific circumstances. Before you run anything like this against a real HMRC account, get a UK-registered accountant to review your first return end-to-end. For the authoritative rule on any specific edge case, see HMRC VAT Notice 700 and the related notices on the gov.uk website. The cost of one hour of your accountant's time is much smaller than the cost of a bad submission.

3. The data model

Everything downstream depends on the shape of two tables: invoices issued (sales) and expenses recorded (purchases). I keep mine in Postgres but the same shape works on MySQL or SQLite. The minimum useful schema:

CREATE TABLE invoices (
  id              BIGSERIAL PRIMARY KEY,
  invoice_number  TEXT NOT NULL UNIQUE,
  issued_at       DATE NOT NULL,
  customer_name   TEXT NOT NULL,
  customer_vat_no TEXT,
  customer_country CHAR(2) NOT NULL,
  net_amount      NUMERIC(14,2) NOT NULL,
  vat_amount      NUMERIC(14,2) NOT NULL,
  gross_amount    NUMERIC(14,2) NOT NULL,
  currency        CHAR(3) NOT NULL DEFAULT 'GBP',
  fx_rate_to_gbp  NUMERIC(18,8) NOT NULL DEFAULT 1,
  vat_rate        NUMERIC(5,2) NOT NULL,
  vat_treatment   TEXT NOT NULL,
  status          TEXT NOT NULL,
  paid_at         DATE,
  notes           TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE expenses (
  id              BIGSERIAL PRIMARY KEY,
  supplier_name   TEXT NOT NULL,
  supplier_vat_no TEXT,
  supplier_country CHAR(2) NOT NULL,
  incurred_at     DATE NOT NULL,
  net_amount      NUMERIC(14,2) NOT NULL,
  vat_amount      NUMERIC(14,2) NOT NULL,
  gross_amount    NUMERIC(14,2) NOT NULL,
  currency        CHAR(3) NOT NULL DEFAULT 'GBP',
  fx_rate_to_gbp  NUMERIC(18,8) NOT NULL DEFAULT 1,
  vat_rate        NUMERIC(5,2) NOT NULL,
  vat_treatment   TEXT NOT NULL,
  category        TEXT NOT NULL,
  receipt_url     TEXT,
  notes           TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

The non-obvious columns are vat_treatment, fx_rate_to_gbp, and the country codes. Treatment is a small enum used by both the classifier and the engine; country code drives reverse-charge and EU-acquisition logic; fx rate lets you keep foreign-currency invoices in their original currency and still re-state in GBP for the box totals.

Backfilling those columns onto a legacy invoices table is the slowest part of the project. Claude Code can generate the migration; you still need to hand-classify the awkward edge cases.

4. Why Claude Code (and not Xero / QuickBooks / FreeAgent)

For most UK small businesses the answer is - genuinely - use packaged software. Xero, QuickBooks, FreeAgent, Sage all have HMRC-certified MTD submission, decent UIs, and accountants who already know them. You should not roll your own VAT system unless you have a clear reason.

My reasons were narrow but real. I already use Claude Code every day for software work, so the marginal effort to also use it for VAT was small. My invoices DB lives on my own machine; I prefer it not be mirrored into a SaaS tenant. My supplier mix is awkward (EU services with reverse charge, postponed import VAT, occasional zero-rated edge cases) and encoding the rules in a single Markdown file plus a Python module turned out to be faster than configuring a SaaS to handle them. And the audit trail I get from a git repo - every classifier prompt, every diff, every signed period snapshot - is exactly the audit trail I want.

Claude Code is well suited to this for three reasons. First, it works directly against a project on disk: it can read my schema, the prior-quarter dry-run reports, the rules file, and the Python engine, and propose edits in context. Second, it is good at the boring scaffolding - migration scripts, CSV importers, reconciliation reports, pytest fixtures. Third, when it does classify a line, it explains its reasoning in plain English, which is exactly what I want to review before signing the return. The decision matrix at the bottom of the workflow diagram below is the honest version of this trade-off.

5. Architecture and workflow

The pipeline has five swim lanes. Source is read-only access to the invoices and expenses tables plus a drop folder for CSV from Stripe, Wise, and bank feeds. Ingest + Normalise is a small Claude-generated Python script that maps each source schema into one canonical view, converts foreign currency to GBP at the HMRC monthly rate, applies a date-range filter, and stamps every row with an idempotent run_key so re-runs are safe. Categorise + Reconcile is the Claude Code classifier: it walks each canonical row, picks a vat_treatment from the documented enum using rules in vat-rules.md, reconciles invoices against payments, and flags anomalies (negative net, missing supplier VAT number, country-code mismatch, suppliers that should be VAT-registered but are not). VAT engine is a pure Python module that reads the classified rows and computes Box 1 to Box 9 deterministically; the flat-rate, cash and annual scheme branches are explicit code paths. Submit + Audit is the HMRC MTD API call (OAuth2, fraud-prevention headers, sandbox first), gated behind a human approval step, with the signed period snapshot and full classifier log written to an append-only audit folder retained for six years.

6. Step-by-step build with Claude Code

This is the workflow I actually used. Five prompts. I cleaned them up for this article; the originals are messier.

Prompt 1 - normaliser. "Look at the invoices and expenses tables in schema.sql. Generate a Python module normalise.py that reads both tables using psycopg (read-only role vat_reader), restricts to the date range 2026-01-01 to 2026-03-31, and emits a list of canonical dicts with keys: id, source, date, party, gross, net, vat_amount, vat_rate, vat_treatment, country, currency, fx_rate, run_key. Use Decimal, not float. Include a docstring and a __main__ block that prints a summary."

Claude returned a clean module with Decimal, parameterised SQL, a build_run_key() that hashes (period_start, period_end, scheme, schema_version) into a stable hex string, and a Click-based CLI. I edited two field names and accepted the rest.

Prompt 2 - classifier. "Read vat-rules.md and write classify.py. For each canonical row from normalise.py, set vat_treatment to one of: standard, reduced, zero, exempt, outside-scope, reverse-charge, eu-goods, eu-services, postponed-import. If the row already has a non-empty vat_treatment, leave it alone and record it as preserved. Otherwise, decide from country code, supplier VAT number, category, and amount, and write a one-sentence reason on each row. Anomalies (negative net, missing supplier VAT for a UK supplier above the registration threshold, country mismatch) go to a separate anomalies.json for human review. Do not mutate the source DB."

The classifier itself is a small, mostly rules-based function with one LLM-backed branch for ambiguous expense categories. Critically, it never writes to the source DB - it emits a classified JSON file that the engine reads. If I disagree with a classification, I edit the JSON; the next dry-run picks it up.

Prompt 3 - engine. "Write engine.py as a pure function: def compute_boxes(rows: list[Row], scheme: Scheme) -> BoxTotals. No I/O. No LLM. BoxTotals is a frozen dataclass with Box 1 to Box 9 as Decimal values to 2 dp. Add explicit branches for standard, flat_rate, cash, and annual schemes. Standard scheme is accrual: sales VAT contributes to Box 1, purchase VAT to Box 4. Reverse-charge invoices add to both Box 1 and Box 4. Postponed import VAT adds to both Box 1 and Box 4. EU acquisitions for NI businesses go in Box 2 and Box 9. Round at the box totals, never per row. Write a doctring linking each branch to its rule."

Prompt 4 - dry-run report. "Write dryrun.py that calls compute_boxes() and emits a Markdown report: header with period and scheme, a table of Box 1 to Box 9 with the canonical labels, a section per classifier bucket listing top 10 rows by gross, an anomalies section, and a footer with the run_key and a SHA-256 of the classified JSON. Save to ./audit/<period>/dryrun.md."

Prompt 5 - submitter. "Write submit.py against the HMRC MTD VAT API v1.0. Use the OAuth2 authorisation_code flow with PKCE. Read the access token from ~/.vat/token.json; refresh if expired. Required fraud-prevention headers as documented by HMRC; pull them from fraud_headers.py. Default to the sandbox https://test-api.service.hmrc.gov.uk. Steps: GET /organisations/vat/{vrn}/obligations?status=O, find the matching period, prompt the operator to confirm by displaying the dry-run summary, then POST /organisations/vat/{vrn}/returns with finalised: true. Print the receiptId. Never call live unless HMRC_ENV=live is set and the operator types SUBMIT."

Five prompts, four files, and a small Markdown rules document. Total build time was maybe a week of focused evenings, most of which was the classifier rules and the HMRC OAuth dance.

7. The VAT engine

The engine is the deliberately boring part. It is a pure function with unit tests and no AI. This is the actual shape:

from dataclasses import dataclass
from decimal import Decimal, ROUND_HALF_UP

D2 = Decimal("0.01")

@dataclass(frozen=True)
class BoxTotals:
    box1: Decimal
    box2: Decimal
    box3: Decimal
    box4: Decimal
    box5: Decimal
    box6: Decimal
    box7: Decimal
    box8: Decimal
    box9: Decimal

def _q(x: Decimal) -> Decimal:
    return x.quantize(D2, rounding=ROUND_HALF_UP)

def compute_boxes(rows, scheme="standard", is_ni_business=False) -> BoxTotals:
    box1 = box2 = box4 = Decimal(0)
    box6 = box7 = box8 = box9 = Decimal(0)

    for r in rows:
        gbp_net = r.net * r.fx_rate
        gbp_vat = r.vat_amount * r.fx_rate

        if r.source == "invoice":
            box6 += gbp_net
            if r.vat_treatment in ("standard", "reduced"):
                box1 += gbp_vat
            elif r.vat_treatment == "eu-goods" and is_ni_business:
                box8 += gbp_net
        else:
            box7 += gbp_net
            if r.vat_treatment in ("standard", "reduced"):
                box4 += gbp_vat
            elif r.vat_treatment == "reverse-charge":
                notional = gbp_net * (r.vat_rate / Decimal(100))
                box1 += notional
                box4 += notional
            elif r.vat_treatment == "postponed-import":
                notional = gbp_net * (r.vat_rate / Decimal(100))
                box1 += notional
                box4 += notional
            elif r.vat_treatment == "eu-goods" and is_ni_business:
                notional = gbp_net * (r.vat_rate / Decimal(100))
                box2 += notional
                box4 += notional
                box9 += gbp_net

    box3 = box1 + box2
    box5 = box3 - box4
    return BoxTotals(
        _q(box1), _q(box2), _q(box3), _q(box4), _q(box5),
        _q(box6), _q(box7), _q(box8), _q(box9),
    )

Two things to note. First, every box total is quantised once at the end. Per-row rounding leaks pennies and HMRC are unforgiving about totals that do not reconcile. Second, the engine is fully deterministic: same input, same output, every time. That is the property that lets me unit-test it and trust the dry-run.

def test_simple_quarter():
    rows = [
        Row("invoice", net=Decimal("24500.00"), vat_amount=Decimal("4900.00"),
            vat_rate=Decimal("20"), vat_treatment="standard", fx_rate=Decimal(1), source="invoice"),
        Row("expense", net=Decimal("3000.00"), vat_amount=Decimal("600.00"),
            vat_rate=Decimal("20"), vat_treatment="standard", fx_rate=Decimal(1), source="expense"),
    ]
    t = compute_boxes(rows)
    assert t.box1 == Decimal("4900.00")
    assert t.box4 == Decimal("600.00")
    assert t.box5 == Decimal("4300.00")
    assert t.box6 == Decimal("24500.00")
    assert t.box7 == Decimal("3000.00")

With this in place, every regression is caught in CI before it ever touches the dry-run report. Claude can help me write more tests; it cannot change the engine without those tests passing.

8. Demo video

Below is a short walkthrough of the pipeline running end-to-end against a real (anonymised) invoices and expenses database for one quarter: normalise, classify, dry-run, then a sandbox MTD submission. The whole loop is a couple of minutes.

9. HMRC MTD submission

This is the part most write-ups gloss over. The MTD VAT API itself is well documented and free, but there are three things you need to get right before you ever call POST returns.

OAuth2 and tokens. Register an application in the HMRC developer hub; you get a client ID, a client secret, and a redirect URI. The flow is authorisation_code with PKCE: open the browser to the HMRC consent page, the user logs in with their Government Gateway credentials and authorises your application against their VAT registration number (VRN), HMRC redirects back with an authorisation code, you exchange it for an access token + refresh token. Cache them with sensible permissions on disk. The access token is short-lived; the refresh token is much longer.

Fraud-prevention headers. HMRC require a documented set of HTTP headers identifying the originating device on every API call: time zone, screen geometry, user IDs, public IP, MAC addresses, the client connection method, etc. The exact list and format are in the HMRC developer documentation (see HMRC VAT Notice 700 and the related fraud-prevention specifications for the authoritative rule). Get them wrong and you get a 403 with a vague error. Get them right and the call goes through. I keep them in fraud_headers.py and assert their presence in tests.

Sandbox first. HMRC provide a free sandbox at test-api.service.hmrc.gov.uk with stub VRNs and stub obligations. Run your entire pipeline end-to-end against the sandbox until you have submitted a fake return, received a fake receipt ID, and confirmed your obligations move. Only then do you flip HMRC_ENV=live. My submitter refuses to call live unless the environment variable is set and the operator types SUBMIT at the prompt. There is no automatic execution path to live. The cost of a single bad live submission is much higher than the cost of one more sandbox iteration.

The actual happy-path call is short: GET obligations with status=O to find the open period, display the dry-run report, prompt the operator to confirm, then POST returns with the nine box numbers, the periodKey from the obligation, and finalised: true. Save the response, write the receipt ID into the audit folder, and you are done.

10. Safety, audit, and compliance

Three rules I will not move on. First, Claude is not in the arithmetic. The classifier suggests treatments and the LLM can edit Python and rules, but the box totals are produced by a pure function with unit tests. "The AI did it" is not a defence under MTD. Second, every submission is gated by a human. The dry-run report is the source of truth for sign-off. If I cannot explain a number from the classified rows and the engine code, I do not submit. Third, the audit trail is append-only. Every classified JSON, every dry-run report, every prompt, every diff, and the final HMRC receipt ID for the period are written into ./audit/<period>/ and committed to a private git repo. HMRC require six years of records; mine are signed snapshots.

Two more practices I would recommend even if you do not adopt the rest. Run the source-DB connection as a least-privilege read-only role: no UPDATE, no DELETE, no DROP. Keep the HMRC client secret and the access tokens out of the repo using a secret manager or a 0600 file in your home directory. The point is to make accidents impossible by construction, not just by policy.

11. Edge cases

VAT is a regulated tax that has been edited by every UK government since 1973, with a post-Brexit overlay and a Northern Ireland protocol on top. The edge cases are real. A non-exhaustive list of the ones my engine handles:

• Brexit and Northern Ireland. Great-Britain businesses no longer fill in Box 8 or Box 9. NI businesses still do for goods (not services) under the NI protocol. The engine takes is_ni_business as a parameter and branches accordingly. See HMRC VAT Notice 700 for the authoritative rule.
• Reverse charge. Services bought from a supplier outside the UK (and certain domestic construction-industry services) are accounted for under reverse charge: you add a notional output VAT to Box 1 and reclaim the same amount in Box 4. Net effect on cash is zero; net effect on the return is two boxes that move together.
• Postponed import VAT. For goods imported from outside the UK, you defer the import VAT to your next return rather than paying it at the border. The treatment looks similar to reverse charge on the return.
• Partial exemption. If you make both taxable and exempt supplies, you can only reclaim input VAT to the extent it relates to taxable supplies. There are standard and special methods; this is the corner that most often needs an accountant. Do not try to encode partial exemption rules in a Markdown file.
• Flat-rate scheme. You charge VAT at the standard rate but pay HMRC a fixed percentage of gross turnover, and you generally cannot reclaim input VAT. The engine has a separate branch for this and ignores most of the per-row classification.
• Multi-currency invoices. Convert each row to GBP at the rate in effect on the relevant date. HMRC publish monthly average rates; you can also use the spot rate. Be consistent. The fx_rate_to_gbp column stores the actual rate applied per row.
• Credit notes. A credit note is a negative invoice. It reduces Box 1 and Box 6 (or Box 4 and Box 7 if it is a purchase credit). Make sure your net_amount and vat_amount can go negative and that the engine sums them correctly.
• Deposits and stage payments. The tax point may be when the deposit is received, not when the final invoice is issued. Cash accounting can simplify this.
• Mid-quarter rate changes. Historically rare, but possible. Store the rate per row, not as a global constant.

12. Cost

Honest numbers. Build time was about a week of focused evenings - call it 25 to 30 hours. Most of that was schema normalisation, the classifier rules, and writing the engine tests; the HMRC OAuth dance was the second-largest chunk. Running cost per quarter is small: my existing Claude Code subscription (already paid for other software work), a few pennies in classifier token usage, and zero pounds for the HMRC API. The engine and the audit log run locally on my Mac. Reviewer time per quarter is about an hour, mostly spent reading the dry-run with my accountant. Compare that to roughly half a day of spreadsheet wrangling under the old workflow and the time saved pays back the build cost inside the first year.

If you priced this as a project you were paying someone else to build, you would be in the four-figure ballpark on consultancy rates - which is why a packaged SaaS at twenty to fifty pounds a month is almost always the right answer for most businesses. The interesting question is not "is this cheaper than Xero?" - it usually is not - but "is it more accurate, more auditable, and easier for me to reason about?". For me the answer is yes.

13. What is next, and conclusion

The next pieces I want to add are: automatic bank-feed import (Open Banking AISP), OCR for supplier PDFs so the receipt URL becomes an actual scanned line, a scheduled cron + Slack approval flow so I do not have to remember the deadlines, and a self-billing extension for marketplace sellers. None of those change the core shape of the pipeline. They are all decorations on the same five-lane diagram.

The bigger lesson, for me at least, is how well a small Claude Code project handles the boring half of a regulated workflow. The boring half is most of the work: schema normalisation, CSV importers, reconciliation reports, fixtures, edge-case rules, audit logging. Claude is excellent at all of that. The deterministic half - the actual maths, the actual submission - stays in plain Python with unit tests and a human approval gate, exactly where it belongs.

Disclaimer, again. This article is not tax advice. Before you point anything like this at a real HMRC account, get a UK-registered accountant to review your first return end-to-end. Run against the HMRC MTD sandbox until you and your accountant are both confident. For the authoritative rule on any specific VAT treatment, see HMRC VAT Notice 700 and the related notices on the gov.uk website. If you would like the short version of all of this, the companion blog post is a five-minute read; otherwise, subscribe and I will keep writing as the stack matures.

SEO snapshot for this article

• SEO title: Build a VAT Return System with Claude Code
• Meta description: Use Claude Code to read your invoices and expenses DB, classify VAT, compute HMRC Box 1 to 9, dry-run, then submit to MTD - deterministic engine, human approval.
• Primary keywords: claude-code, vat, hmrc, mtd, accounting, automation, python, ai-agent, finops, small-business
• Twitter / X (under 280 chars): Rebuilt my UK VAT return as a Claude Code project: Postgres invoices + expenses DB to HMRC Box 1 to 9, deterministic Python engine, sandbox-first MTD submit, human approval gate. Full write-up + workflow SVG + demo video. Not tax advice. #ClaudeCode #VAT #HMRC #MTD
• LinkedIn post: I rebuilt my quarterly UK VAT return as a small Claude Code project this year. It reads my invoices and expenses database read-only, classifies each line by VAT treatment using a documented rules file, computes HMRC Box 1 to 9 with a deterministic Python engine (no LLM in the arithmetic), and only after I sign off the dry-run does it submit through the HMRC MTD API - sandbox first, fraud-prevention headers, the full OAuth2 flow. The whole quarter now takes about an hour. Full write-up with prompts, schema, the engine code, edge cases, and the honest cost story is on Workstation. Not tax advice; get an accountant to review your first return.