Tax Computation Engine

A design reference for resolving and computing per-line tax amounts across sales and purchase documents

1. Requirements

1.1 Functional requirements

• Apply a reusable, Legal-Entity-scoped Charge Template (an ordered list of tax/charge rows) to a sales or purchase document automatically, or let a user pick one explicitly.
• Support conditional, filter-based routing so the correct Charge Template is chosen without user input — by party, party group, geography (billing/shipping city, county, state, zipcode, country), item, item group, tax category, a validity window, and a priority tiebreaker.
• Compute each row’s contribution using one of five charge-type modes: a flat entered amount, a percentage of the merchandise total, a percentage of a specific earlier row’s per-item tax amount, a percentage of a specific earlier row’s running total, or a flat amount per unit of quantity — and let later rows reference earlier rows so charges can cascade/compound.
• Support tax-inclusive pricing: back out the tax-exclusive net amount from a tax-inclusive selling/buying price, correctly handling compounding (a row that is itself computed on a previous row’s amount or total).
• Let an individual item override the document-level rate for a specific ledger account, or mark a specific ledger account “not applicable” to that item entirely (distinct from a zero rate) — falling back from the item’s own configuration to its item group’s configuration when the item itself defines nothing.
• Scope an item’s own tax configuration to a validity date range and/or a net-rate band, so the same item can carry different applicable rates as of different dates or price bands, with a documented tie-break when several are simultaneously valid.
• On the purchase side, let a charge line affect item valuation (landed cost) only, the payable total only, or both — and let a charge line add to or deduct from the running total.
• Distribute an entered discount either against the pre-tax net total or against the final grand total, in the latter case keeping charges that are fixed regardless of item pricing (flat-amount and per-quantity charges, and anything that cascades from them) out of the discount base.
• Produce a per-item, per-charge-line breakdown suitable for a printed or reported tax summary.

1.2 Non-functional requirements

• Numeric conservation: whatever rounding strategy is used, the sum of the rounded per-item pieces must always tie out exactly to the rounded whole; a genuine, unreconcilable discrepancy beyond a small tolerance must stop the document rather than silently absorb it.
• Determinism with bounded self-correction: recomputing from unchanged inputs must be idempotent; when an item’s own configuration turns out to be stale mid-computation, the engine may silently correct it and re-run once, but must never loop unboundedly.
• Backward compatibility of history: editing a Charge Template later must never retroactively change the computed taxes on a document that already copied that template’s rows.
• Currency awareness: every rate/amount is computed once in the document’s transaction currency and once in the Legal Entity’s base currency, using each Ledger Account’s own fixed currency and the document’s conversion rate; zero-decimal currencies get their own, wider rounding tolerances.
• Extensibility without forking the core loop: region-specific rounding behavior and region-specific report formatting are pluggable hook points rather than conditionals baked into the shared computation path.

1.3 Constraints

• A charge row’s “reference row” mechanism only points at an earlier row inside the same table — it cannot reference another document or another Charge Template.
• Only five charge-type computation modes exist; adding a sixth requires a new branch in the shared engine, not a configuration change.
• An item’s override is expressed as a flat rate (or “not applicable”) keyed by ledger account — it cannot express an arbitrary formula independent of the charge line that already targets that account.
• Some transaction types intentionally bypass parts of the pipeline: a consolidated (already-aggregated) invoice trusts its item amounts as final and skips re-deriving them from pricing, and a Quotation excludes rows flagged as alternative-item options from the totals entirely.

2. High-Level Design

2.1 Component diagram

2.2 Resolution and cascade flow

3. Deep Dive

3.1 Data model

Charge Template A named, Legal-Entity-scoped, ordered list of Charge Lines. It ships in a sales flavor and a purchase flavor with an identical row shape; the purchase flavor adds two purchase-only fields (below). At most one Charge Template per Legal Entity may be marked default, and at most one non-disabled template per (Legal Entity, Tax Category) pair is allowed — a disabled template cannot simultaneously be the default. Selecting a template copies its rows onto the document by value; editing the template afterward never touches documents that already copied it.

Charge Line One row, either inside a Charge Template or inside a document’s own copy of one. Key fields: charge_type (one of the five modes below), account_head (a Ledger Account), rate, row_id (a 1-based reference to an earlier row, required only for the two cascading charge types and forbidden on the first row), cost_center, included_in_print_rate (the inclusive-tax flag), and a set_by_item_tax_template marker distinguishing rows a person entered from rows the engine auto-appended for an item-level override account that had no matching line yet. The purchase-only fields are category (Valuation / Total / Valuation and Total) and add_deduct_tax (Add / Deduct). A dont_recompute_tax flag can freeze a row’s already-computed breakup so a later recomputation leaves it untouched.

Tax Routing Rule A standing, priority-ordered condition set that resolves to a Charge Template without a user picking one. Its filter fields are: customer/supplier (and their groups, matched hierarchically up the group ancestry), item, item group, billing/shipping city/county/state/zipcode/country, tax category, a use_for_shopping_cart flag, an optional validity window, and a Legal Entity. Any filter field left blank on the rule acts as a wildcard that matches anything — except tax category, which is compared by exact equality only (a rule with no tax category set matches only a request that also carries none). A save-time guard rejects inserting a second rule whose filters, validity window, and priority are all identical to an existing one.

Item Tax Profile A named, Legal-Entity-scoped set of per-Ledger-Account override rows. Each row names a Ledger Account (restricted to account types Tax, Chargeable, Income Account, Expense Account, or Expenses Included In Valuation, and must belong to the same Legal Entity), a rate, and a “not applicable” flag; the same account cannot appear twice in one profile, and marking a row “not applicable” forces its stored rate to zero as a cosmetic side-effect (the actual runtime signal is a distinct sentinel, described in §3.4).

Item Tax Binding The join row that attaches an Item Tax Profile to an Item (or to an Item Group, inherited by every item under it). Beyond naming the profile, it can carry a tax category, an effective-from date, and a minimum/maximum net-rate band — letting the same item switch profiles as of a date, or by the price it’s being sold/bought at.

Line Rate Override Map Not a stored record but a per-item-row, per-computation artifact: a {ledger account -> rate | "not applicable"} map assembled by taking the rates already present among the document’s own manually-entered Charge Lines and then overlaying the winning Item Tax Profile’s rows on top (only for accounts belonging to the transacting Legal Entity). The computation engine consults this map, keyed by each Charge Line’s own account, before falling back to the Charge Line’s own rate — this is the per-line override mechanism.

Line Tax Breakup Entry A per-item, per-Charge-Line record of the taxable amount and tax amount that item contributed to that row, captured during computation and optionally persisted after submission. Rows belonging to a Valuation-only category are excluded from the printed/reported breakdown, since they never entered the payable total.

Tax Category A simple named label (its own record carries only a title and a disabled flag) used as a coarse selector in both a Charge Template’s own scoping and a Tax Routing Rule’s filters, and separately resolved from a Legal-Entity setting that says whether the shipping or the billing address’s own tax category should drive a document’s default.

3.2 Algorithm — the charge-type cascade

Each item, each Charge Line, in document order:

• Actual — a fixed amount entered once, spread across items in proportion to each item’s net amount (item.net_amount / doc.net_total); the leftover from integer division is added entirely onto the last item, so the sum of item contributions always equals the entered amount exactly.
• On Net Total — rate% x item.net_amount (or the item’s un-rounded net amount when this row is tax-inclusive, to avoid double rounding — see §3.3).
• On Previous Row Amount — rate% x the referenced row’s own per-item tax contribution for this same item. This stacks a charge on top of another charge’s amount (compounding), not on the merchandise price.
• On Previous Row Total — rate% x the referenced row’s running cumulative total for this item so far (net amount plus every charge up to and including the referenced row).
• On Item Quantity — rate x item.qty, a flat per-unit charge that ignores price entirely.

Every row also consults the Line Rate Override Map first: if the row’s own account has an entry there for the current item, that rate is used instead of the row’s own configured rate — this is how one item can be taxed differently from its siblings on the same document without a second Charge Line. A worked example, one item, net amount 1,000:

Row 1  On Net Total,            rate 10%              -> tax  100.00   running total 1,100.00
Row 2  On Previous Row Amount,  row_id=1, rate 5%      -> tax    5.00   running total 1,105.00
                                  (5% of Row 1's own tax amount, 100.00)
Row 3  On Previous Row Total,   row_id=2, rate 2%      -> tax   22.10   running total 1,127.10
                                  (2% of Row 2's running total, 1,105.00)

Net Total            1,000.00
Total Taxes/Charges     127.10
Grand Total           1,127.10

If Row 3 instead read the same item’s Line Rate Override Map and found its own account marked “not applicable” for this item, both its taxable and tax contribution for that item collapse to zero — excluding just that item from that charge without touching the Charge Line itself. One reporting nuance worth flagging: an On-Net-Total row’s own displayed “taxable base” total only accumulates from items that carry an explicit override entry for that row’s account; items relying on the plain template rate still contribute fully to the row’s tax-amount total, just not to that particular reported base figure.

3.3 Algorithm — inclusive-tax back-calculation

Triggered only when at least one Charge Line on the document is marked tax-inclusive. For each item, the engine walks the Charge Lines computing a per-row “tax fraction”: On Net Total contributes rate/100; On Previous Row Amount contributes rate/100 x the referenced row’s own fraction; On Previous Row Total contributes rate/100 x the referenced row’s cumulative fraction-of-total; On Item Quantity contributes a flat inclusive amount per unit instead of a fraction. A Deduct-marked row (purchase side) flips the sign. Each row also tracks a running “grand total fraction” — row one starts at 1 + its own fraction, every later row adds its own fraction to the previous row’s running fraction — which is what lets a later inclusive row correctly compound on an earlier inclusive row’s effect.

Once every row’s fraction is known, the tax-exclusive net amount is solved directly:

net_amount = (item.amount - flat_inclusive_amount_per_qty * qty) / (1 + sum_of_all_tax_fractions)

The un-rounded result is kept alongside the rounded net_amount and reused specifically by inclusive On-Net-Total rows, so that rate x un-rounded-net does not compound a second rounding error on top of the one already taken when net_amount itself was rounded. After all rows are computed, the engine cross-checks its own total against the sum of item amounts plus any non-inclusive charges; a residual up to five units of the smallest tax-amount decimal place is folded into a rounding adjustment, and anything larger is discarded rather than silently hidden inside the grand total.

3.4 Algorithm — item-level tax resolution

1. Collect the item’s own Item Tax Bindings; if none, walk up its Item Group’s ancestor chain collecting each ancestor’s bindings until one is found.
2. Discard any binding whose Item Tax Profile is disabled or belongs to a different Legal Entity.
3. Split the remaining bindings into those that carry a validity constraint (an effective-from date and/or a net-rate band) and those that carry none.
4. If any validity-scoped bindings are actually in range for the document’s date and net rate, only those are eligible — unscoped bindings are ignored the moment at least one scoped candidate exists — and among them the one with the latest effective-from date (or highest net-rate ceiling, if no date is set) wins.
5. Within that winning tier, the first eligible binding whose tax category matches the document’s tax category is selected (an empty document tax category matches an equally empty binding tax category).
6. Stability rule: if the item row already carries a profile that is still among today’s eligible candidates, it is left unchanged rather than re-picked — an explicit earlier choice among several valid options is not silently overridden.

The winning Item Tax Profile is then flattened into the Line Rate Override Map: seed it from the rates already on the document’s own manually-entered Charge Lines, then overwrite with the profile’s own rows for accounts in the same Legal Entity. A profile row marked “not applicable” writes a distinct sentinel rather than a zero rate — a materially different signal (“this tax does not apply” versus “0%”). When enabled, any account named in an item’s map with no matching Charge Line yet is auto-appended as a new On-Net-Total row at rate zero, flagged as engine-added — the real effective rate still comes from each item’s own override map, and the zero rate only applies to items with no override. A second pass re-validates each item’s chosen profile against the document’s current tax category and date; if it’s no longer eligible, the engine silently swaps to the first eligible one and, if a grand-total discount was already applied, reruns the computation exactly once more.

3.5 Tax Routing Rule scoring

candidates = enabled rules where:
  - from_date/to_date bracket the transaction date, if one was supplied
    (rules with no from_date/to_date are the only ones eligible when no date is supplied)
  - every filter field is either blank on the rule (wildcard) or equal to the request's value
  - customer_group / supplier_group additionally match if the rule's group is an
    ancestor of the request's actual group
  - tax_category is compared by exact equality only — a blank rule value matches
    only a blank request value, no wildcard fallback

score each candidate by:
  1. count of the request's fields that are non-blank on that candidate (higher wins)
  2. priority (higher integer wins), as the tiebreaker

winner = highest-scoring candidate -> its Charge Template
         (or none, if that template is itself disabled)

This scoring is invoked from the same shared entry point regardless of whether the caller is a quotation, order, or invoice on either the sales or purchase side, and it composes with, rather than replaces, the plain “company default template” fallback: if nothing matches, or nothing was ever asked, the document still gets the Legal Entity’s own default Charge Template.

3.6 Rounding and precision

Two independent per-line rounding behaviors exist. A setting can force each item’s incremental tax and net contribution to be rounded the instant it’s computed, instead of staying unrounded until the row’s aggregate is rounded once. A separate, pluggable regional hook can force specific accounts’ tax amounts to the nearest whole currency unit regardless of the currency’s normal decimal precision. Per-item contributions to a row’s base-currency tax amount are derived by error diffusion: the engine keeps a running cumulative transaction-currency total, converts that running total to base currency and rounds it, and takes the difference from the previous cumulative rounded value as the current item’s contribution — guaranteeing the rounded per-item pieces always sum exactly to the rounded cumulative total, rather than drifting from independently rounding each piece. The same technique reconciles persisted per-item breakup rows against the row’s own total, tolerating a difference of up to half a unit (a full unit for a zero-decimal currency) before throwing. The document’s own rounded total is computed to the currency’s smallest denomination, with any difference from the unrounded grand total kept as its own visible rounding-adjustment figure rather than absorbed invisibly.

3.7 Error handling

• Stale item override, mid-computation: silently corrected and the whole computation is rerun exactly once (never unbounded).
• Unreconciled tax breakup beyond tolerance: hard validation stop, naming the offending row and the size of the discrepancy.
• Conflicting Tax Routing Rule: a save-time check rejects a new rule whose filters, dates, and priority exactly tie an existing one.
• Unmatched routing rule or unmatched item binding: both degrade gracefully — the document falls back to the Legal Entity’s default Charge Template, or the Charge Line’s own configured rate, rather than blocking the transaction.
• Discount exceeding the total it’s applied against: hard validation stop before the discount is distributed.
• Advance allocation exceeding the invoice total (in the party’s own account currency): hard validation stop.
• Cascading reference errors: a Charge Line referencing a row at or after its own position, or a non-cascading charge type carrying a row reference, is rejected at save time before it ever reaches the computation engine.

4. Scale and Reliability

• Load pattern: computation runs synchronously on every save of a transactional document, and can run several times per save as a user edits line items before finally submitting — the engine bounds this to at most one extra self-triggered rerun per save (the stale-item-override correction), preventing an unbounded recompute loop.
• Statelessness across documents: Charge Lines are child rows scoped to their own parent document or template; no shared mutable state exists between two documents computing taxes concurrently. Reference-data lookups (Ledger Account, Item Tax Profile, Legal Entity settings) are read through a cache layer, so repeated lookups across many line items or many concurrent documents do not each cost a fresh query.
• No cross-document locking for computation itself: unlike the ledger-posting step that follows it, tax computation touches only the document being edited. The remaining concurrency risk sits at the reference-data layer — two administrators editing overlapping Tax Routing Rule filter sets at the same time — guarded only by a save-time comparison query, not a database-level uniqueness constraint.
• Graceful degradation: an unmatched routing rule, an unmatched item binding, and a disabled template referenced by a rule all fail open (fall back to a default, or to the row’s own rate) rather than blocking document creation.
• Failure signal worth monitoring: the item-wise tax breakup reconciliation’s hard-fail path is effectively the leading indicator of a rounding or precision regression; it is worth alerting on directly rather than waiting for a user-reported discrepancy.

5. Trade-off Analysis

6. What to Revisit as the System Grows

• Promote the Tax Routing Rule conflict check to a real constraint. Today it is a save-time comparison query against existing rows; as the number of rules grows, a database-level uniqueness constraint on the normalized filter set would catch conflicts a query might miss under concurrent inserts.
• A tiered/bracket charge-type mode. Regulatory tax brackets currently have to be expressed by chaining several rows with row references; if that need grows common, a native bracket-aware charge type would be more auditable than a chain of rows depending on each other’s row_id.
• Extend “not applicable” to the Charge Template level. Today only an Item Tax Profile row can mark an account not applicable to a given item; a Charge Line itself has no equivalent way to say “this charge never applies to this document,” short of omitting the row entirely.
• Revisit the last-row rounding-residual convention if row/item ordering ever becomes something an end user actively curates — the row that happens to be last is not always the one a user would expect to silently absorb every reconciliation difference.
• Make the regional rounding hook a first-class, discoverable per-Legal-Entity setting rather than a silent no-op extension point — as more locales are supported, an administrator should be able to see whether it is active without reading the underlying computation code.