Not Every Pricing Rule Belongs in a Formula

The first parcel costs EUR 20. Parcels two through ten cost EUR 5 each. After that, each additional parcel costs EUR 3. Twelve parcels should cost 20 + 9 × 5 + 2 × 3 = 71 euros. There is nothing particularly exotic about that tariff. Turning it into three small programs still creates more work than the arithmetic suggests.

My first pricing model could express those programs. It knew about package scope, conditions, calculated amounts, and ordered adjustments. The problem wasn’t an inability to calculate the right price. It was that ordinary tariff structure kept arriving as another expression for the system to interpret. That affected editing, validation, and eventually execution.

The opening chapter shows the table-backed architecture reached later. Here I want to go backward: what the earlier model actually stored, how it executed, and what changed when I replaced that pricing path. The code follows the historical PHP implementation, with imports, tracing, and unrelated branches omitted where stated. Identifiers and example rates are anonymized. This was private development, not a migration of a live customer price book.

The old model already had tables

Calling this a move “from formulas to a database” would be misleading. The formulas were already persisted, and their surrounding structure was relational. A ruleset owned component definitions; each definition could have several targets and a sequence of operations:

ruleset
  └─ pricing_component_definitions
       ├─ pricing_component_targets
       │    carrier / service / requested option
       └─ pricing_component_operations
            sequence / operation / operand / currency

A definition carried a component code and kind, scope, sequence, amount and currency, validity dates, and optional channel and weekday filters. Later extensions added an applicability expression and an amount expression, each with a language and version. Package handling and shipment transport were already different scopes. The model wasn’t a column containing an arbitrary script and a hopeful comment saying that the script should return a price.

The relevant model casts looked like this:

protected function casts(): array
{
    return [
        'amount_value' => 'decimal:8',
        'amount_expression_json' => 'array',
        'display_names_json' => 'array',
        'valid_from' => 'immutable_datetime',
        'valid_to' => 'immutable_datetime',
        'channels_json' => 'array',
        'weekdays_json' => 'array',
        'applicability_expression_json' => 'array',
    ];
}

Even this small excerpt needs care. decimal:8 is the Eloquent attribute cast in that model; the original migration declared the amount as decimal(20, 6). Neither declaration tells you how arithmetic happened. The evaluator converted numeric values to PHP floats and formatted amounts later. Reading a decimal cast and declaring the complete calculation decimal-safe would be inventing a guarantee the code didn’t provide.

The schema also already used ownership-scoped foreign keys. A component couldn’t simply point at another owner’s ruleset, and operation sequence numbers were unique within their definition. Those guards survived the redesign in other forms. The new design’s integrity benefit was more specific: it gave previously interpreted pricing facts explicit identities and relationships that other parts of the application could understand.

At runtime, the active-ruleset read model loaded the Eloquent graph and projected it into arrays. It renamed amount_expression_json to amount_expression, included targets, and emitted operations in their configured order. The calculator consumed that projection rather than performing one database query for every expression node. Keeping storage, projection, and interpretation separate matters when looking for the cost. The expression tree was interpreted in memory; its every branch was not a database round trip.

Three parcel bands become three applicability programs

The parcel tariff can be represented by three package-scoped definitions. Their fixed amounts are 20, 5, and 3, with applicability expressions using pricing_scope.position. The middle band’s stored expression is:

{
  "and": [
    {
      "greaterThan": [
        { "var": "pricing_scope.position" },
        { "const": 1 }
      ]
    },
    {
      "lessThanOrEqual": [
        { "var": "pricing_scope.position" },
        { "const": 10 }
      ]
    }
  ]
}

The first uses equals(position, 1); the last uses greaterThan(position, 10). Each definition also has the service target, scope, amount, and currency from the surrounding model. The JSON above is only the condition, not a complete authoring request.

This is a JSON syntax tree, not PHP passed to eval. A var node resolves a path; a const node supplies a value; the outer operator evaluates its operands. The formula evaluator’s public entry builds a root context and an initially empty alias scope:

public function evaluate(
    array $expression,
    array $context,
): FormulaEvaluationResult {
    return $this->evaluateNode($expression, [
        'root' => $context,
        'vars' => [],
    ]);
}

The alias scope lets collection operations bind one item at a time without rewriting the caller’s root data. For example, a sum can walk shipment packages with each package bound to an alias. A missing variable is a failed evaluation unless the variable node supplies a default. A present null is different from a missing path. That distinction matters if a default is allowed for one and an error is required for the other.

Value calculation and the more general predicate system had separate jobs. The formula language supported arithmetic, comparisons, conditionals, and collection operations, with an embedded predicate adapter for the existing boolean rule engine. I explicitly chose that split rather than building another complete proposition engine inside the formula package. It still left simple comparisons convenient to write directly in value expressions.

For the staggered tariff, the package context did some important work before the interpreter saw a node. The engine enumerated package identities and then selected the relevant package for each definition. This is the package branch of its scope-context builder:

if ($scope === 'package'
    && array_key_exists('package_id', $scopeReference)) {
    $packages = is_array($context['shipment']['packages'] ?? null)
        ? array_values(array_filter(
            $context['shipment']['packages'],
            static fn (mixed $package): bool => is_array($package),
        ))
        : [];

    foreach ($packages as $index => $package) {
        if ($this->stringValue($package, 'id')
            === $scopeReference['package_id']) {
            $context['package'] = $package;
            $context['package_metrics'] = $this->packageMetrics($package);
            $context['pricing_scope'] = [
                'position' => $index + 1,
                'count' => count($packages),
            ];

            return $context;
        }
    }
}

Position was therefore a one-based index into the filtered package list. It wasn’t a property of a package’s weight, an external parcel number, or the number of items allocated to that package. Swapping package order could change which physical parcel received a first-parcel component, even when the total for this particular tariff stayed the same.

The context builder also copied the selected package into package and derived package_metrics. Package weight, length, longest side, and volume could then participate in applicability expressions. This is why “evaluate the condition” understates the implementation: the condition relies on a specific interpretation of scope and on normalized facts produced beforehand.

There was repeated work here too. For each definition and scope reference, the builder searched the package list to find the selected identity. With C candidates, D applicable definitions, and P packages, that search could contribute work proportional to C × D × P² in this branch. The precise runtime depended on the tariff and early rejections; this is a description of the loop structure, not a measured speed factor. A compiled expression alone wouldn’t remove the package search around it.

The calculator distinguished a non-match from an invalid formula

For each candidate, the old path checked definition targets and effective time before enumerating scope references. Within each scope it evaluated applicability, resolved an amount, converted currency if necessary, applied ordered operations, and materialized a price component. The core branch looked like this; the surrounding target/time checks and later currency and component construction are omitted:

foreach ($scopeReferences as $scopeIndex => $scopeReference) {
    $scopeEvaluationContext = $this->pricingScopeEvaluationContext(
        $baseEvaluationContext,
        $definition,
        $scopeReference,
    );

    $applicability = $this->evaluatePricingBooleanExpression(
        $definition['applicability_expression'] ?? null,
        $scopeEvaluationContext,
    );

    if ($applicability === false) {
        continue;
    }

    if ($applicability === null) {
        return [
            'succeeded' => false,
            'failure_code' => CanonicalFailureCodeRegistry::FORMULA_SET_INVALID,
        ];
    }

    $amount = $this->resolvePricingAmount(
        $definition,
        $scopeEvaluationContext,
    );

    if ($amount === null) {
        return [
            'succeeded' => false,
            'failure_code' => CanonicalFailureCodeRegistry::FORMULA_SET_INVALID,
        ];
    }
    // Currency conversion, ordered operations and component construction.
}

An applicability result of false meant that this component didn’t belong on this scope instance. A failed expression, or a successful expression returning a non-boolean value, meant the pricing configuration was invalid. Treating both cases as “no matching surcharge” could quietly undercharge. The wrapper enforced the distinction explicitly:

private function evaluatePricingBooleanExpression(
    ?array $expression,
    array $context,
): ?bool {
    if ($expression === null) {
        return true;
    }

    $result = $this->formulaEvaluator->evaluate($expression, $context);

    if (! $result->succeeded || ! is_bool($result->value)) {
        return null;
    }

    return $result->value;
}

An absent condition applied unconditionally. A condition containing {"const": 1} wasn’t an alternative spelling of true. That strictness was useful, but it made the expression language’s types part of the pricing contract. Its equality operator also used strict identity: an integer position of 1 matched an integer constant of 1; a float 1.0 didn’t. The original scope builder produced integer positions. A later translator that changed numeric types could change behavior without changing the expression’s apparent meaning.

Amount resolution had a different type requirement. With no amount expression, it used a numeric fixed value or fell back to 0.0. With an expression, the result had to succeed and satisfy is_numeric; otherwise resolution failed. A numeric string could therefore be a valid amount even though it wasn’t a valid applicability result. The fixed-value fallback was permissive too. The existence of the wrapper didn’t mean every malformed configuration was rejected in every path.

After resolving the amount, operations transformed it in order:

private function applyPricingOperation(
    float $amount,
    string $operationType,
    ?float $operandValue,
): float {
    return match ($operationType) {
        'add' => $amount + ($operandValue ?? 0.0),
        'remove' => $amount - ($operandValue ?? 0.0),
        'replace' => $operandValue ?? $amount,
        'percent' => $amount + ($amount * (($operandValue ?? 0.0) / 100)),
        'multiplier' => $amount * ($operandValue ?? 1.0),
        'cap_min' => $operandValue === null
            ? $amount : max($amount, $operandValue),
        'cap_max' => $operandValue === null
            ? $amount : min($amount, $operandValue),
        'round' => round($amount, (int) ($operandValue ?? 2)),
        default => $amount,
    };
}

Add EUR 3 to EUR 20 and then add ten percent: EUR 25.30. Apply the percentage before the fixed addition: EUR 25.00. Flattening the operations into an unordered bag of adjustments would change the price. Currency conversion happened before these operations, and an operand could require its own conversion. Missing exchange-rate data failed the calculation.

The schema contained fields such as operation rounding mode and cap currency, but this dispatcher didn’t read every piece of that metadata. The behavior of round here was PHP’s round with the operand converted to a precision. A richer-looking model declaration wasn’t proof of a richer implementation. That became especially important when deciding what a replacement really needed to preserve.

A conditional amount is a small program with its own control flow

The old tests also covered an amount that changed with aggregate shipment volume. Two parcels measured 100 × 80 × 40 cm and 90 × 70 × 35 cm. Their combined volume was 0.32 + 0.2205 = 0.5405 m³. A volume surcharge returned EUR 18 above 0.5 m³ and EUR 6 otherwise:

{
  "if": [
    {
      "greaterThan": [
        { "var": "shipment_metrics.total_volume_m3" },
        { "const": 0.5 }
      ]
    },
    { "const": 18 },
    { "const": 6 }
  ]
}

Together with a EUR 10 base component, that scenario expected EUR 28. It tested an amount expression, not two mutually exclusive applicability definitions. That difference matters when translating it: one expression always selects one branch after its condition succeeds; two rows need selection rules that preserve the same coverage.

The interpreter evaluated only the chosen branch. This is its actual conditional implementation:

private function evaluateIf(
    mixed $operand,
    array $scope,
): FormulaEvaluationResult {
    if (!is_array($operand)
        || !array_is_list($operand)
        || count($operand) !== 3) {
        return FormulaEvaluationResult::failure(
            FormulaErrorCode::InvalidOperand,
            'If operator expects [condition, then, else].',
        );
    }

    $condition = $this->evaluateNode($operand[0], $scope);

    if (!$condition->succeeded) {
        return $condition;
    }

    if (!is_bool($condition->value)) {
        return FormulaEvaluationResult::failure(
            FormulaErrorCode::InvalidOperand,
            'If condition must resolve to a boolean value.',
        );
    }

    return $this->evaluateNode(
        $condition->value ? $operand[1] : $operand[2],
        $scope,
    );
}

You could put an invalid division in the unselected branch without failing that evaluation. Eagerly evaluating both branches during a rewrite would change the failure behavior. Looking only at successful numeric examples wouldn’t expose it.

There was a reasonable design here. An expression can describe a derived value clearly, and a constrained interpreter avoids executing arbitrary application code from a stored tariff. The awkward part was using that same generality as the main representation of ordinary price-book structure. An editor asking for “the second parcel band” shouldn’t have to recognize a particular arrangement of and, greaterThan, and lessThanOrEqual nodes before it can find the band.

Even equivalent predicates can have different trees. position > 1 and not(position <= 1) express the same condition over these valid integer positions. A general expression inspector must either understand both, normalize them, or admit that it can’t answer the question. Add nested conditionals and collection expressions and this stops looking like a simple price-book query. I didn’t want every importer and validator to grow its own partial interpreter.

The replacement made the recurring shape explicit

I chose first-class table families: zone matrices, package breaks, shipment breaks, distance bands, surcharges, minimum-charge policies, and precedence policies. A pricing profile connected their versioned roots to a ruleset. Tariff grids and further families arrived later as the supported dimensions grew. The mature multi-axis grid in the opening chapter wasn’t the entire first replacement implemented in one step.

The early package-break row had a known metric and bounds, plus amount, currency, identity, ordering, service match, and zone constraints. These are selected declarations from its initial migration, with the ownership name shortened to tenant_id. Parent creation, timestamps, standalone owner reference, and secondary identity keys are omitted; this isn’t a complete executable migration:

$table->ulid('id')->primary();
$table->ulid('tenant_id');
$table->ulid('package_break_table_id');
$table->unsignedInteger('sequence');
$table->string('row_code');
$table->string('kind');
$table->string('scope')->default('package');
$table->string('metric_type');
$table->decimal('minimum_value', 20, 6)->nullable();
$table->decimal('maximum_value', 20, 6)->nullable();
$table->json('service_match_json')->nullable();
$table->json('zone_codes_json')->nullable();
$table->string('currency_code');
$table->decimal('amount', 20, 6);
$table->string('group_code')->nullable();
$table->unsignedInteger('precedence')->default(0);

$table->unique([
    'tenant_id', 'package_break_table_id', 'row_code',
]);
$table->unique([
    'tenant_id', 'package_break_table_id', 'sequence',
]);
$table->foreign(['tenant_id', 'package_break_table_id'])
    ->references(['tenant_id', 'id'])
    ->on('package_break_tables')
    ->cascadeOnDelete();

For the twelve-parcel example, the runtime rows can now expose the bands directly. Every row belongs to the selected package-break table, has metric_type: position, the same service match, and currency EUR:

Those bounds are inclusive. Since package positions are positive integers, the rows give the same partition as the three original expressions: one first parcel, nine parcels at EUR 5, and two at EUR 3. Each matching package produces a component associated with that package’s identity; the engine doesn’t multiply an anonymous total count and discard which parcels contributed.

The new runtime still had to build scope contexts, check service and zone eligibility, convert currency, and assemble components. What changed for the regular band was how its applicability was determined. It could select a known metric and compare it with declared bounds rather than evaluate an arbitrary program. The early matcher contained these decisions:

$metricType = $this->stringValue($row, 'metric_type') ?? 'flat';

if ($metricType === 'flat') {
    return true;
}

$metricValue = match ($metricFamily) {
    'distance' => $this->distanceMetricValue($metricType, $context),
    default => $this->pricingMetricValue($metricType, $context),
};

if ($metricValue === null) {
    return false;
}

$minimumValue = is_numeric($row['minimum_value'] ?? null)
    ? (float) $row['minimum_value']
    : (is_numeric($row['minimum_distance_km'] ?? null)
        ? (float) $row['minimum_distance_km']
        : null);
$maximumValue = is_numeric($row['maximum_value'] ?? null)
    ? (float) $row['maximum_value']
    : (is_numeric($row['maximum_distance_km'] ?? null)
        ? (float) $row['maximum_distance_km']
        : null);

if ($minimumValue !== null && $metricValue < $minimumValue) {
    return false;
}

if ($maximumValue !== null && $metricValue > $maximumValue) {
    return false;
}

return true;

The method’s arguments were the row, scope context, and metric family. The metric resolver mapped position to pricing_scope.position, package weight to its canonical package metric, and aggregate volume to the shipment metric. It wasn’t accepting arbitrary field paths from the tariff and hoping the context happened to contain the right unit.

That explicit vocabulary made a useful optimization possible later: prepare an index for the fields known to affect candidate selection, then run the remaining predicates only on candidate rows. It also made family-specific validation possible. A package-break validator could ask about ranges without reverse-engineering value expressions. Neither benefit was automatic merely because a migration created some tables. The first table-backed evaluator still scanned rows and built contexts; specialized preparation and indexes were subsequent work.

Nor did every condition become a column. Service matches and zones used structured JSON; later tariff rows supported multiple metric axes. The design traded unrestricted programs for a vocabulary the engine could validate and optimize, while preserving structured dimensions where they were useful. “Everything is relational now” would be a poor description of what I actually built.

The cutover changed readers, writers, and tests separately

I explicitly wanted the table-backed path to replace the component/formula authoring path. This was a new private application. Keeping the old path indefinitely would create two ways to express the same price, two sets of validation rules, and an extra question about which representation won. I wasn’t trying to turn an implementation transition into a permanent product feature.

The source changes nevertheless came in stages:

1. The artifact foundation introduced roots, rows, profile relationships, and model topology. Existing runtime pricing remained unchanged.
2. Repository and active-read-model changes loaded the profile graph. Old component definitions were still available.
3. The evaluator preferred a profile when present. A missing profile could still enter the old component path.
4. Ruleset persistence and projection stopped carrying old components. The fallback code remained briefly without its former projected input.
5. The next removal deleted old models and the fallback. A missing profile now failed; obsolete tables still needed cleanup.
6. A later migration dropped the old component tables. Its rollback could recreate schema, not recover deleted tariff contents.

The temporary branch was real:

if ($ruleset->pricingProfile !== null) {
    return $this->priceComponentsForCandidateFromProfile(
        $candidate,
        $ruleset,
        $payload,
        $computedPropertyValues,
        $currencyMetadataVersion,
        $decisionInstantAt,
    );
}

// The old component-definition path followed here.

After removal, a missing profile returned a failure instead. There wasn’t a promise to interpret both pricing systems forever. That final refusal matters: a quiet fallback can make incomplete configuration look supported, especially if one reader has stopped projecting the fields the fallback expects.

For an already deployed application, the same removal would require a different plan: translating stored definitions, preserving historical decisions, retiring writers before readers, and establishing whether a rollback could still read the data written during the transition. Here there was no deployed customer corpus to migrate. I could make a cleaner replacement decision, but I still needed the pricing scenarios to survive.

The tests reveal how that was approached. Many scenario bodies kept their familiar helper calls, including formula-shaped inputs. The helper changed underneath them: instead of inserting component definitions, targets, and operations, it recognized supported expression shapes and inserted new package or shipment rows, surcharge rows, zone rows, or a minimum-charge policy. It could fold a fixed addition into a row’s amount and turn a conditional amount into two bands.

This kept the scenario readable while exercising the new runtime. It also meant that a test still containing an if expression was no longer testing the old interpreter. The expression had become a convenient input to a test-data translator. That’s a usable technique, provided you don’t mistake it for running both implementations and comparing them.

A green example can leave a gap between its rows

The conditional-volume helper shows the limit concretely. It recognized an if with three parts, exactly one supported comparison, and constant then/else amounts. For greaterThan, it returned two bands. The final construction was:

return [
    [
        'metric_type' => $metricRule['metric_type'],
        'minimum_value' => runtimeEngineMinimumValueForComparison($comparison),
        'maximum_value' => null,
        'amount' => $thenAmount,
    ],
    [
        'metric_type' => $metricRule['metric_type'],
        'minimum_value' => null,
        'maximum_value' => (float) $comparison['value'],
        'amount' => $elseAmount,
    ],
];

How did it turn an exclusive lower bound into the new matcher’s inclusive minimum? This was the helper:

function runtimeEngineMinimumValueForComparison(array $comparison): float
{
    $value = (float) $comparison['value'];

    return $comparison['operator'] === 'greaterThan'
        ? $value + 0.000001
        : $value;
}

For a threshold of 0.5, the upper band’s minimum became 0.500001. The lower band’s maximum remained 0.5. The original scenario’s volume of 0.5405 selected EUR 18 in both representations, so that example could pass. A volume of 0.5000005 exposed a difference:

Running the old amount resolver, the historical row matcher, and the actual helper against those values reproduces the gap. This is a limit of that historical test translation, not a report of production mispricing; the application wasn’t deployed. The successful scenario didn’t establish equivalence over the values between its examples.

Adding a tiny number can be a valid translation only if the input domain really has that resolution. Positive integer parcel positions have a well-defined next value: after position 1 comes position 2. A continuously derived volume doesn’t inherit a six-decimal resolution just because the database stores a row’s bound with six decimal places. You must either define and apply quantization before comparison, represent exclusive endpoints directly, or accept a narrower supported domain. Picking an epsilon and leaving the domain implicit makes the assumption easy to miss.

The helper wasn’t a universal expression compiler either. It recognized particular comparison paths and shapes; its conditional-band extraction accepted a specific greaterThan form. A nested conditional or equivalent predicate written differently wasn’t automatically translated. That was test scaffolding for the existing cases, not a general migration API.

The lesson for this change was more demanding than “keep the tests green.” Keep the scenarios, but inspect what now produces their inputs. Test positions 1, 2, 10, and 11; test the exact threshold and values immediately around it; compare component identities and scope as well as totals. A preserved test name can hide a completely changed path underneath it. That becomes even more important later when a language port uses a large test corpus as its starting point.

The gain came with a smaller language and more application code

I observed a substantial performance improvement from the representation redesign. It would be convenient to attach one impressive factor to it, but the later development also changed preparation, indexing, cache payloads, persistence, and eventually runtime. Those were separate interventions. The source explains why the table model made specialized execution possible; it doesn’t isolate every millisecond saved by the schema itself.

The cost is visible in the replacement. Each family needed a model, relationships, authoring behavior, projection, validation, and evaluator support. A new metric or policy wasn’t always something an author could introduce by arranging existing expression nodes. It could require an application change. That restriction was part of the point: recurring pricing concepts acquired a shared meaning instead of being rediscovered by every consumer.

For a small fixed price book, ordinary code or a simple lookup may still be easier. For irregular numerical derivations, a constrained expression can be clearer than inventing another table with fifteen optional fields. The engine retained computed formulas feeding the pricing context. The replacement didn’t abolish mathematics, or even the interpreter; it stopped making ordinary tariff structure depend on interpreting another small program.

What I wanted to ask next was much more concrete: are these particular rows valid together, and which revision has actually been validated? Now the rows had identities and declared dimensions, that question had a sensible place to live. The database could enforce some of the answer. The rest still needed code.