Tax Calculation Engine Low-Level Design
Tax calculation sits at the intersection of accuracy and auditability: a one-cent rounding error multiplied across millions of transactions becomes a regulatory liability, and a calculation that cannot be reproduced exactly six months later fails an audit. This article covers the complete low-level design of a tax calculation engine: nexus determination, jurisdiction and rate lookup, line-item computation, compound vs. parallel tax stacking, IEEE 754-safe rounding, and immutable snapshot storage.
Core Concepts
- Nexus: Legal obligation to collect tax in a jurisdiction, triggered by physical presence, economic activity thresholds, or marketplace facilitator laws.
- Tax Code: A product classification (e.g., TaxJar category 20010 = “Clothing”) that determines taxability and rate in each jurisdiction.
- Compound stacking: Tax-on-tax — state tax is applied to (price + county tax). Used in Quebec, Canada.
- Parallel stacking: Independent taxes each applied to the pre-tax price. Used in most US states.
- ROUND_HALF_EVEN (banker’s rounding): Rounds 2.5 to 2, 3.5 to 4. Minimizes cumulative rounding bias across large transaction volumes.
SQL Schema
CREATE TABLE tax_jurisdiction (
id BIGSERIAL PRIMARY KEY,
country CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2
state CHAR(2), -- US state abbreviation
county TEXT,
city TEXT,
zip_code TEXT,
jurisdiction_type TEXT NOT NULL
CHECK (jurisdiction_type IN ('country','state','county','city','special')),
UNIQUE (country, state, county, city, zip_code)
);
CREATE INDEX idx_tj_lookup ON tax_jurisdiction (country, state, zip_code);
CREATE TABLE tax_rate (
id BIGSERIAL PRIMARY KEY,
jurisdiction_id BIGINT NOT NULL REFERENCES tax_jurisdiction(id),
tax_code TEXT NOT NULL, -- product category, '*' = all
rate NUMERIC(8,6) NOT NULL, -- e.g. 0.085000 = 8.5%
tax_name TEXT NOT NULL, -- e.g. "CA State Sales Tax"
stacking_type TEXT NOT NULL DEFAULT 'parallel'
CHECK (stacking_type IN ('parallel','compound')),
stacking_order INT NOT NULL DEFAULT 0, -- for compound chains
valid_from DATE NOT NULL,
valid_to DATE, -- NULL = currently active
UNIQUE (jurisdiction_id, tax_code, tax_name, valid_from)
);
CREATE INDEX idx_tr_jurisdiction ON tax_rate (jurisdiction_id, valid_from, valid_to);
CREATE TABLE tax_calculation (
id BIGSERIAL PRIMARY KEY,
calc_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
order_id BIGINT NOT NULL,
buyer_address JSONB NOT NULL, -- immutable snapshot of address
subtotal NUMERIC(12,4) NOT NULL,
total_tax NUMERIC(12,4) NOT NULL,
grand_total NUMERIC(12,4) NOT NULL,
currency CHAR(3) NOT NULL DEFAULT 'USD',
calculated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
engine_version TEXT NOT NULL -- semver, for audit reproducibility
);
CREATE TABLE tax_line_item (
id BIGSERIAL PRIMARY KEY,
calc_id UUID NOT NULL REFERENCES tax_calculation(calc_id),
product_id BIGINT NOT NULL,
tax_code TEXT NOT NULL,
line_amount NUMERIC(12,4) NOT NULL, -- pre-tax
rate_id BIGINT NOT NULL REFERENCES tax_rate(id),
rate NUMERIC(8,6) NOT NULL, -- snapshot of rate at calculation time
tax_amount NUMERIC(12,4) NOT NULL,
jurisdiction TEXT NOT NULL,
stacking_type TEXT NOT NULL
);
Jurisdiction Rate Lookup
from decimal import Decimal, ROUND_HALF_EVEN
from typing import List, Dict, Any
from datetime import date
def lookup_jurisdiction_rates(
db,
country: str,
state: str,
zip_code: str,
tax_code: str,
as_of: date = None
) -> List[Dict[str, Any]]:
"""
Returns all applicable tax rates for the given address and product tax code,
ordered by stacking_order for compound calculation.
"""
as_of = as_of or date.today()
rows = db.fetchall(
"""
SELECT tr.*, tj.jurisdiction_type, tj.state, tj.county, tj.city
FROM tax_rate tr
JOIN tax_jurisdiction tj ON tj.id = tr.jurisdiction_id
WHERE tj.country = %s
AND (tj.state = %s OR tj.state IS NULL)
AND (tj.zip_code = %s OR tj.zip_code IS NULL)
AND (tr.tax_code = %s OR tr.tax_code = '*')
AND tr.valid_from = %s)
ORDER BY tr.stacking_order ASC, tj.jurisdiction_type ASC
""",
(country, state, zip_code, tax_code, as_of, as_of)
)
return [dict(r) for r in rows]
Line-Item Tax Computation
from decimal import Decimal, ROUND_HALF_EVEN
from typing import List, NamedTuple
CENT = Decimal('0.01')
class LineItemInput(NamedTuple):
product_id: int
tax_code: str
amount: Decimal # pre-tax line amount
class TaxLineResult(NamedTuple):
product_id: int
tax_code: str
line_amount: Decimal
rate_id: int
rate: Decimal
tax_amount: Decimal
jurisdiction: str
stacking_type: str
def calculate_tax(
db,
country: str,
state: str,
zip_code: str,
line_items: List[LineItemInput]
) -> Dict[str, Any]:
"""
Computes tax for all line items. Returns a result dict ready to store
in tax_calculation + tax_line_item.
"""
all_lines: List[TaxLineResult] = []
total_tax = Decimal('0')
for item in line_items:
rates = lookup_jurisdiction_rates(db, country, state, zip_code, item.tax_code)
taxable_base = item.amount
item_tax = Decimal('0')
parallel_rates = [r for r in rates if r['stacking_type'] == 'parallel']
compound_rates = sorted(
[r for r in rates if r['stacking_type'] == 'compound'],
key=lambda r: r['stacking_order']
)
# Parallel taxes: each applied to original pre-tax amount
for rate_row in parallel_rates:
rate = Decimal(str(rate_row['rate']))
tax_amt = (item.amount * rate).quantize(CENT, rounding=ROUND_HALF_EVEN)
item_tax += tax_amt
all_lines.append(TaxLineResult(
product_id = item.product_id,
tax_code = item.tax_code,
line_amount = item.amount,
rate_id = rate_row['id'],
rate = rate,
tax_amount = tax_amt,
jurisdiction = f"{rate_row['state'] or ''} {rate_row['jurisdiction_type']}".strip(),
stacking_type = 'parallel'
))
# Compound taxes: each applied to (amount + all prior compound taxes)
compound_base = item.amount
for rate_row in compound_rates:
rate = Decimal(str(rate_row['rate']))
tax_amt = (compound_base * rate).quantize(CENT, rounding=ROUND_HALF_EVEN)
item_tax += tax_amt
compound_base += tax_amt # next tier taxes this tier's tax too
all_lines.append(TaxLineResult(
product_id = item.product_id,
tax_code = item.tax_code,
line_amount = item.amount,
rate_id = rate_row['id'],
rate = rate,
tax_amount = tax_amt,
jurisdiction = f"{rate_row['state'] or ''} {rate_row['jurisdiction_type']}".strip(),
stacking_type = 'compound'
))
total_tax += item_tax
subtotal = sum(i.amount for i in line_items)
grand_total = subtotal + total_tax
return {
'subtotal': subtotal,
'total_tax': total_tax,
'grand_total': grand_total,
'line_items': all_lines,
}
Immutable Snapshot Storage
Tax calculations must be reproducible. Store the buyer address as JSONB on the tax_calculation row — never a foreign key to an address that can later be edited. Store the rate as a column on tax_line_item alongside the rate_id reference so the exact value used is always readable without joining to a rate table that may have changed:
import json
ENGINE_VERSION = '2.1.0'
def persist_tax_calculation(db, order_id: int, buyer_address: dict, result: dict) -> str:
calc_id = str(uuid.uuid4())
db.execute(
"""INSERT INTO tax_calculation
(calc_id, order_id, buyer_address, subtotal, total_tax, grand_total, engine_version)
VALUES (%s,%s,%s,%s,%s,%s,%s)""",
(calc_id, order_id, json.dumps(buyer_address),
result['subtotal'], result['total_tax'], result['grand_total'], ENGINE_VERSION)
)
for li in result['line_items']:
db.execute(
"""INSERT INTO tax_line_item
(calc_id, product_id, tax_code, line_amount, rate_id, rate,
tax_amount, jurisdiction, stacking_type)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
(calc_id, li.product_id, li.tax_code, li.line_amount,
li.rate_id, li.rate, li.tax_amount, li.jurisdiction, li.stacking_type)
)
db.commit()
return calc_id
Nexus Determination
Before running the calculation, check whether you have nexus in the destination state. If no nexus, return zero tax. Store nexus registrations in a simple table:
def has_nexus(db, seller_id: int, country: str, state: str) -> bool:
row = db.fetchone(
"""SELECT 1 FROM nexus_registration
WHERE seller_id=%s AND country=%s AND (state=%s OR state IS NULL)
AND active=TRUE LIMIT 1""",
(seller_id, country, state)
)
return row is not None
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “Why use ROUND_HALF_EVEN instead of ROUND_HALF_UP for tax calculations?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “ROUND_HALF_EVEN (banker’s rounding) rounds ties to the nearest even digit, eliminating systematic bias when rounding thousands of tax amounts. ROUND_HALF_UP consistently rounds ties upward, which produces a small but measurable inflation of total tax collected across large volumes. Tax authorities in the US and EU both accept banker’s rounding for this reason.”
}
},
{
“@type”: “Question”,
“name”: “What is the difference between compound and parallel tax stacking?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Parallel stacking applies each tax rate independently to the original pre-tax price. Compound stacking (tax-on-tax) applies each successive rate to the running total including previously calculated taxes. Quebec’s QST is calculated on price plus GST, making it a compound tax. Most US state and local taxes are parallel.”
}
},
{
“@type”: “Question”,
“name”: “How do you handle tax rate changes without breaking historical recalculations?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The tax_rate table has valid_from and valid_to columns. Historical lookups pass the order date as as_of, retrieving the exact rate in effect at that time. The tax_line_item table also stores the rate value as a column snapshot, so auditors can verify the calculation without needing to reconstruct the historical rate.”
}
},
{
“@type”: “Question”,
“name”: “When should a tax calculation be stored vs. computed on the fly?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Always store the calculation at checkout and order confirmation. Never recompute at invoice generation or refund time — tax rates may have changed. The immutable tax_calculation snapshot tied to the order_id is the authoritative record. Recomputing from current rates would produce a different (incorrect) value for historical orders.”
}
}
]
}
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How is tax jurisdiction determined for a transaction?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The billing address state/country is matched against a TaxJurisdiction lookup table; nexus rules determine which jurisdictions apply based on seller registration.”}},{“@type”:”Question”,”name”:”How are product taxability rules applied?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Each product has a tax_code (e.g., TPP for tangible personal property, SaaS for software); the rate lookup joins on jurisdiction + tax_code to find the applicable rate.”}},{“@type”:”Question”,”name”:”How is rounding handled to avoid penny discrepancies?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Python’s decimal.ROUND_HALF_EVEN (banker’s rounding) is applied per line item; the order total is the sum of rounded line items, not a rounded sum.”}},{“@type”:”Question”,”name”:”How are tax calculations stored for audit?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”TaxCalculation records are immutable snapshots; they store rates, amounts, and jurisdiction IDs at the time of calculation and are never updated after creation.”}}]}
See also: Stripe Interview Guide 2026: Process, Bug Bash Round, and Payment Systems
See also: Shopify Interview Guide
See also: Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering