A Valid Pricing Row Can Still Be Wrong

One row charges EUR 12 from zero through five kilograms. Another charges EUR 18 from four through ten. Give them different names and sequence numbers, attach them to the right tariff book, and both rows can be perfectly valid database records. At 4.5 kilograms, they still disagree about the base price. Adding them gives EUR 30, which nobody specified as the base rate.

Moving my private rating engine from expression definitions to explicit rows made this problem easier to inspect. It didn’t solve it by itself. I needed to decide which combinations should be impossible, which should remain valid drafts, and which should stop a ruleset from becoming executable.

The interesting question wasn’t whether the design had “data integrity.” It was which write rejected a particular bad state. The answer involved several different mechanisms, and they didn’t all make equally strong promises. The excerpts below follow the PHP implementation, with imports, unrelated branches, and ownership names shortened where stated. The prices and example identifiers are fictional.

A correct parent ID can still belong to the wrong owner

The storage graph had independently versioned artifacts. A ruleset’s pricing profile referred to a particular shipment-break table; that table owned its rows. The profile didn’t carry an anonymous set of prices that could quietly be replaced by whatever table happened to have the same display name.

ruleset
  └─ pricing profile
       └─ shipment-break table, specific version
            ├─ row: light
            └─ row: heavier

Each level also had an owner. A row for tenant B must not point at tenant A’s table, even if the table ID exists. A foreign key on the parent ID alone would accept that combination. The actual schema pattern used the owner and parent together:

-- On the parent table:
UNIQUE (tenant_id, id)

-- On rows, with tenant_id and parent_id declared NOT NULL:
FOREIGN KEY (tenant_id, parent_id)
    REFERENCES shipment_break_tables (tenant_id, id)

UNIQUE (tenant_id, parent_id, row_code)
UNIQUE (tenant_id, parent_id, sequence)

This excerpt shortens the source’s owner and parent column names and omits the remaining table declarations. The important part is that the database checks the pair, not two unrelated facts. A row cannot combine one tenant with another tenant’s parent. Code and sequence uniqueness are likewise scoped to that parent. The same row code can legitimately exist in another version of a price book.

Those checks apply to a normal application insert, an importer, or an SQL maintenance script writing through the same constraints. That is a useful place to put ownership and identity rules. It avoids requiring every caller to remember the same check. PostgreSQL’s constraint documentation explains the composite-key and nullability behavior.

None of these declarations mentions which weights a row covers. They reject the wrong owner and repeated identities, not competing prices. Two distinct rows named light and heavier can still cover the same shipment. Fixing that requires looking at the actual pricing dimensions.

Range validation needs a definition of which rows compete

A shipment-break row stored its metric, unit, lower and upper bounds, component kind, charge group, matching descriptors, amount, and currency. The model cast the bounds and amount to six-decimal strings and JSON descriptors to arrays. The range policy then used BigDecimal for bound comparisons. That is a local implementation choice, not evidence that the entire quote evaluator avoided floating-point arithmetic.

The policy first checked each range. A lower bound above its upper bound was invalid. A missing endpoint was allowed, as was an exact-point range. The metric had to be known, and its unit had to belong to the metric’s family. Weight accepted weight units; a unitless position didn’t accept kg. A flat metric had no bounded range to compare. flat was a metric type here, not a promise that every component with kind flat_fee was unconditional.

Rejecting every overlapping interval would be wrong too. A transport component and a handling component may intentionally apply together. Two otherwise identical ranges can belong to different services or zones. Before checking overlap, the implementation grouped rows by a signature containing these values:

return json_encode([
    'kind' => $kind,
    'metric_type' => $metricType,
    'metric_unit' => $metricUnit,
    'group_code' => $row->getAttribute('group_code'),
    'service_match' => self::canonicalize(
        $row->getAttribute('service_match_json') ?? [],
    ),
    'zone_codes' => self::canonicalize($zoneCodes->toArray()),
    'route_match' => self::canonicalize(
        $row->getAttribute('route_match_json') ?? [],
    ),
], JSON_THROW_ON_ERROR);

The surrounding method checked the basic field types and converted stored zone data into a list-of-nonblank-strings value object. A malformed zone object wasn’t treated as an empty wildcard. The normalization helper sorted associative keys and recursively sorted lists, so simply reordering service IDs didn’t create a new family.

This was an exact normalized-descriptor comparison. It wasn’t a general proof that two predicates described disjoint shipments. These two service matches have different signatures, but both can include the parcel service:

[
  { "service_ids": ["parcel"] },
  { "service_ids": ["parcel", "freight"] }
]

Likewise, a wildcard descriptor and a specific service descriptor are different families. So are kg and g, even if two ranges describe the same physical interval after conversion. I checked those cases against the actual model and policy; the family distinction allows them. Whether their components should stack or compete remains a pricing-selection and precedence question.

That’s a narrower guarantee than “there are no overlapping prices.” The validator can reject overlap among rows it has classified as comparable. If a future requirement needs semantic intersection across service sets, wildcards, or equivalent units, that is more work. Changing the signature alone could reject valid configurations or permit new conflicts.

The comparison deliberately allows touching endpoints

Within each family, ranges were sorted by their minimum. An absent minimum sorted first. The policy then walked the sorted sequence, keeping the previous maximum. This is the actual rejection loop after sorting:

$previousMaximum = null;
$hasPreviousRange = false;

foreach ($ranges as $range) {
    if (
        $hasPreviousRange
        && (
            $previousMaximum === null
            || $range['minimum'] === null
            || $range['minimum']->compareTo($previousMaximum) < 0
        )
    ) {
        return true;
    }

    $previousMaximum = $range['maximum'];
    $hasPreviousRange = true;
}

The separate hasPreviousRange flag matters. A null previous maximum means “the earlier interval is unbounded above,” not “there wasn’t an earlier interval.” Once such an interval exists, another comparable interval conflicts. An absent minimum after an earlier range also conflicts. Ordinary bounded interiors conflict when the next minimum is strictly below the previous maximum.

The strict < 0 is intentional behavior. Ranges [0, 5] and [4, 10] are rejected within one family. Ranges [0, 5] and [5, 10] are accepted. So are a gap between ranges, one open-ended range, and an exact-point range. The same policy exposes both draftIsInvalid and publicationIsInvalid; both call this check.

That does not make the touching intervals disjoint at runtime. The matcher uses inclusive endpoints, so both can match exactly five. Allowing their configuration leaves the pricing family’s composition or winner-selection policy responsible for that case. An overlap validator and a row matcher can agree on their implementations while still making different promises.

You can see the endpoint difference without PHP:

SELECT numrange(0, 5, '[]') && numrange(5, 10, '[]');
-- true: both contain 5

SELECT numrange(0, 5, '[)') && numrange(5, 10, '[)');
-- false: the first excludes 5

PostgreSQL’s range types make inclusion explicit. Moving to half-open intervals would change the answer at the boundary; it isn’t a harmless implementation cleanup. The previous chapter’s strict-threshold example shows the opposite danger: moving a bound by an epsilon can introduce a gap. Neither endpoint decision should be hidden inside a generic helper.

I would use an exclusion constraint if a particular family had a stable relational competition key and genuinely required disjoint intervals. PostgreSQL can then reject the conflicting write, including a concurrent one. That isn’t what this range policy enforced: it allowed touching boundaries and separated rows using structured descriptors. Translating it into a blanket exclusion rule would change the product’s behavior.

The draft writer validates the proposed collection

Validating only the changed row wouldn’t catch an overlap with its siblings. The update command loaded the persisted row and its parent with the current row collection, checked ownership, and refused edits unless the parent was in draft state. It also checked row-code and sequence conflicts before building a proposed replacement row.

The important part is how it constructed the candidate collection. This excerpt follows the creation of $attributes; idempotency failure recording and result construction are omitted, but the validation and save order is preserved:

$proposedRow = new PricingShipmentBreakRow($attributes);
$otherRows = $table->rows->filter(
    fn (PricingShipmentBreakRow $sibling): bool =>
        $this->pricingShipmentBreakRowId($sibling)
        !== $this->pricingShipmentBreakRowId($row),
);

if ($this->ranges->draftIsInvalid([...$otherRows, $proposedRow])) {
    // Record the failed command outcome.
    return PricingArtifactRowMutationResultData::failure(
        CanonicalFailureCodeRegistry::INPUT_INVALID,
    );
}

$row->fill($attributes);
$saved = $this->tables->saveRow($row);

The current row is removed before the proposed one is added. Otherwise a perfectly ordinary edit could be compared with its own old interval and rejected as a conflict. More importantly, the command checks the collection the edit intends to leave behind. It doesn’t first save the new values and hope some later read notices that the table has become invalid.

Some validation still belongs in SQL. The command’s early duplicate check can give a useful error, while the database’s unique constraint remains the guard for another writer choosing the same code or sequence concurrently. For range policy, however, the source put the collection check in application code. A direct SQL import doesn’t run that PHP method. Scripts that write executable configuration need a controlled path too; calling an operation saveRow doesn’t make it a complete validator.

The repository acquired a parent lock when saving a row:

return DB::transaction(function () use ($row): PricingShipmentBreakRow {
    PricingShipmentBreakTable::query()
        ->whereKey($row->pricing_shipment_break_table_id)
        ->lockForUpdate()
        ->firstOrFail();

    $row->save();

    return $this->getRowById($this->rowKeyString($row))
        ?? throw RatingEntityNotReloadedException::afterOperation(
            'Pricing shipment break table row',
            'save',
        );
}, 3);

That establishes a serialization point for this save. It doesn’t move the earlier sibling read or range validation inside that point. By the time the lock is acquired, the command has already decided that its proposed collection is valid. A lock cannot make that earlier read newer.

If two editors can build different proposals from the same old collection, the concurrency contract needs to cover that complete read-check-write sequence. Re-reading and validating after acquiring the common lock, or checking an aggregate revision that every child writer advances, are ways to express that requirement. This source trace explains the boundary; it isn’t a claim that a particular simultaneous edit caused a historical failure. The application wasn’t deployed.

The same distinction appears at publication. Locking a root while changing its lifecycle state protects a different operation from deciding whether all of its children still match an earlier validation result.

Publication checks the connected artifact set

The ruleset publisher didn’t merely set a boolean on the record. After basic input and idempotency checks, it entered a transaction, loaded the requested ruleset/version, required the approved state and an admitted rule graph, checked the pricing profile, resolved approval and scenario evidence, superseded the previous published ruleset where appropriate, and applied the publication transition. It then materialized runtime state.

The profile check loaded the referenced artifact rows. It checked that the profile belonged to the ruleset and owner, that lane references resolved, and that attached artifacts were actually published rather than drafts. Inside its artifact loop, these were two of the gates:

$state = $attachedArtifact->state ?? null;
$publishedAt = $attachedArtifact->published_at ?? null;

if (!is_object($state)
    || !method_exists($state, 'isPublished')
    || $state->isPublished() !== true
    || $publishedAt === null) {
    return CanonicalFailureCodeRegistry::VALIDATION_FAILED;
}

if ($rowsRelation !== null
    && $this->relationCount($attachedArtifact, $rowsRelation) === 0) {
    return CanonicalFailureCodeRegistry::VALIDATION_FAILED;
}

A table with valid keys but no rows wasn’t automatically a usable pricing source. A populated draft wasn’t a published artifact. The check also required at least one attached source capable of pricing, and a tariff grid needed its axis schema. Reference existence, lifecycle readiness, and useful content were separate questions.

Configuration and conflict checks were dispatched by artifact family. For shipment breaks, the configuration validator reconstructed the relevant row attributes and called publicationIsInvalid on the same range policy used by draft edits. Other families had different checks: percentage charges needed a defined base, seasonal intervals needed valid ordering, and some tables rejected exact duplicate signatures. An exact-signature test wasn’t a proof of logical non-overlap for every possible condition.

This gave publication another chance to reject a bad connected configuration. It didn’t justify postponing every error until publication. Wrong-owner references and duplicate identities were still rejected at storage; draft commands could reject invalid ranges early. The later gate checked whether the collection was suitable to become runtime input.

A passed scenario has to belong to the right context

The publisher also required validation runs for the requested scenario packs. For each pack attached to the ruleset, it loaded the latest run. Missing, unfinished, failed, and cancelled runs were not interchangeable. After the status checks came a context comparison:

if ($runStatus->isFailed() || $runStatus->isCancelled()) {
    return CanonicalFailureCodeRegistry::VALIDATION_FAILED;
}

if (!$runStatus->isPassed()) {
    return CanonicalFailureCodeRegistry::VALIDATION_REQUIRED;
}

if ($latestRun->validated_context_hash !== $snapshot->contextHash) {
    return CanonicalFailureCodeRegistry::VALIDATION_EVIDENCE_MISSING;
}

A run marked passed wasn’t sufficient if it described different dependencies. The snapshot builder resolved the active runtime policy, carrier/service configuration, option schemas and relationships, computed formulas, currency metadata, and request/response contracts at the chosen instant. It built canonical context material and hashed it:

$contextMaterial = $this->artifactHashes->buildValidatedContextMaterial(
    $validatedContext,
);
$contextHash = $this->hashes->hash(
    $contextMaterial,
    HashProfile::ValidationContextHashV1,
);

The contents matter more than the existence of a hash. This context material described those supporting dependencies; it wasn’t a digest of every pricing row and every field in the complete ruleset graph. Calling it a context hash didn’t make it a universal revision token. A currency or formula change could invalidate the comparison because the builder included it. A field outside the material required some other guard.

Even names in the implementation needed that close reading. graphIsCurrent, called earlier by the publisher, loaded rule expressions and condition groups, required a nonempty rule collection, and checked expression admission. It wasn’t a comparison of the whole graph with a previously approved content hash. I can’t use the method’s reassuring name as evidence of a guarantee its body doesn’t implement.

The useful contract is specific: these scenario packs passed, under this resolved supporting context, for this ruleset scope. Structural profile checks, dependency fingerprints, scenario outcomes, and mutation control each answer part of the publication question. They shouldn’t be collapsed into a single “validated” label whose meaning nobody can explain.

The root’s version protects the root transition

The ruleset repository did lock and check a concurrency version when saving the lifecycle transition. The relevant part was:

$current = Ruleset::query()
    ->whereKey($ruleset->getKey())
    ->lockForUpdate()
    ->firstOrFail();

if ($current->concurrency_version !== $expectedConcurrencyVersion) {
    throw ConcurrencyVersionMismatchException::forEntity('Ruleset');
}

// Check the proposed publication window when publishing.
$current->fill([
    'state' => $ruleset->state,
    'concurrency_version' => $expectedConcurrencyVersion + 1,
    'effective_from' => $ruleset->effective_from,
    'effective_to' => $ruleset->effective_to,
    'published_at' => $ruleset->published_at,
]);
$current->save();

Transition and optional approval records were saved in that transaction too. The publisher’s root transition could therefore reject a stale expected version instead of silently overwriting a newer lifecycle state. That is useful. It doesn’t prove that every independently referenced child artifact changed that root’s counter when edited, or that every earlier validation read was protected by this eventual lock.

PostgreSQL’s default Read Committed isolation also permits successive statements in one transaction to see different committed data. Putting validation and transition inside an outer transaction isn’t, by itself, a description of a stable graph snapshot. The isolation documentation explains that distinction. The design needs a coordinated writer protocol, an immutable executable revision, or an isolation/locking strategy that covers the actual invariants—not just a transaction wrapped around one convenient method.

For a price book that must be replayed exactly, I’d prefer an explicit answer to which revision was validated and made executable. New edits should produce another revision or participate in a versioned aggregate write. Where parent locking is the chosen mechanism, all relevant writers need to acquire it before reading and checking the state they will change. Adding another hash won’t rescue fields it never hashes, and acquiring a lock afterward won’t protect a check already made.

Give each rejected state its own test

The actual PHP range checks can be exercised without booting the application or opening a database connection: instantiate the row models, the metric catalog, and the range policy. That checks the model casts and policy together rather than reimplementing the algorithm in a separate teaching model. It exposes accepted cases as well as refusals: touching endpoints, gaps, distinct service signatures, wildcard/specific pairs, and different unit signatures aren’t silently recast as errors.

The standalone SQL example serves a different purpose. It demonstrates ownership and unique-key rejections, then adds an endpoint-order check and still shows two valid rows matching 4.5 kilograms. That extra SQL check is an alternative demonstration, not a claim that the historical migration already contained it. Neither example substitutes for a publication test using the actual writer and referenced artifacts.

Moving the prices into explicit rows was worth doing because these questions became concrete. I could name a wrong-owner reference, a duplicate code, a reversed range, a conflict inside one family, or missing publication evidence, then point at the code expected to refuse it. Where the promised guarantee was stronger than the refusing operation, the design still had work to do. A table-and-row architecture made that work visible; it didn’t give me permission to stop looking.