Postgres for Rule Tables & Liquibase Migrations

The rules engine that decides whether an offer gets a guardrail cap, a boost, or a flat no-buy is driven entirely by rows in a handful of Postgres tables — `pricing_rule` chief among them. Before looking at the schema, it is worth being explicit about why this data earns a relational database and not, say, a MongoDB collection sitting right next to the audit log we will meet two lessons from now. Three properties push it there: referential integrity, multi-row transactions, and a query pattern that is fundamentally about structure — filtering, joining and sorting over well-known columns.

Referential integrity first. A `pricing_rule` row does not stand alone — it applies to a `vehicle_category` (sedan, SUV, truck), it may reference a `provider_weighting`, and it has an `effective_from` / `effective_to` window. If a rule pointed at a vehicle category that had been silently deleted, the rules engine would either crash at evaluation time or, worse, silently skip a guardrail nobody meant to remove. A foreign key constraint makes that class of bug structurally impossible: Postgres refuses the delete, or the insert, before bad data ever lands. A document store can approximate this with application-level checks, but it is checking, not guaranteeing — the guarantee lives at the database layer only in a relational engine.

Multi-row transactions second. When an analyst publishes a new rule set — say, tightening the guardrail on branded-title vehicles while simultaneously loosening the boost on low-mileage trucks — those two changes need to land together. If the process died halfway, after the guardrail tightened but before the boost loosened, the rules engine would spend the next few minutes (or the next few thousand valuations) evaluating a rule set that never existed as a deliberate business decision. Wrapping the update in a single `@Transactional` boundary means Postgres guarantees both rows change or neither does — this is precisely what ACID's 'atomicity' and 'consistency' buy you, and it is not a property MongoDB's multi-document transactions give you for free with the same performance characteristics, nor one Redis offers at all.

Query pattern third. The rules engine's actual query is not 'give me a document by its ID' — it is 'give me every active rule for this vehicle category, ordered by priority, where the effective window covers today.' That is a `WHERE`, a `JOIN` against `vehicle_category`, and an `ORDER BY`, over a table that is, realistically, a few thousand rows even at scale. Postgres was built for exactly this: a query planner that can pick an index, a small enough table that the whole thing likely lives in memory, and SQL that expresses the filter declaratively instead of forcing the application to reassemble it from multiple lookups.

Here is where Liquibase enters, and it solves a problem that has nothing to do with which database you picked and everything to do with how a team changes that database's shape over time without stepping on each other. A hand-run SQL script — someone SSHing into a box and pasting `ALTER TABLE pricing_rule ADD COLUMN ...` — has three failure modes: it is not repeatable (did staging get the same script as production, in the same order?), it is not reviewable (nobody put it in a pull request), and it is not reversible in any disciplined way (rolling back means someone remembers the inverse SQL, under pressure, during an incident).

Liquibase replaces all three of those with a changelog: an ordered, version-controlled list of `changeSet` blocks, each with a unique `id` and `author`, each applied exactly once and tracked in a `DATABASECHANGELOG` table that Liquibase itself maintains. Adding a column becomes a changeSet that ships in the same pull request as the Kotlin code that reads it, gets reviewed the same way, runs identically whether it is your laptop, a CI pipeline, or production, and — because Liquibase computes a checksum per changeSet — fails loudly if someone edits history after the fact instead of adding a new changeSet on top.

The discipline this buys is easy to underrate until a rollback goes wrong: because every changeSet is small, additive, and independently identified, `pricing_rule` can gain a `max_boost_percentage` column today without anyone touching the twelve changeSets that came before it, and if today's changeSet turns out to be wrong, Liquibase can be told to roll back exactly that one unit of change — not 'restore from last night's backup and hope.'

CREATE TABLE vehicle_category (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(64) NOT NULL UNIQUE  -- 'SEDAN', 'SUV', 'TRUCK'
);

CREATE TABLE pricing_rule (
    id BIGSERIAL PRIMARY KEY,
    vehicle_category_id BIGINT NOT NULL REFERENCES vehicle_category(id),
    rule_type VARCHAR(32) NOT NULL,          -- 'GUARDRAIL', 'BOOST', 'NO_BUY'
    adjustment_percentage NUMERIC(5,2),
    priority INT NOT NULL DEFAULT 0,
    effective_from DATE NOT NULL,
    effective_to DATE,
    CONSTRAINT chk_effective_window CHECK (effective_to IS NULL OR effective_to > effective_from)
);

CREATE INDEX idx_pricing_rule_lookup
    ON pricing_rule (vehicle_category_id, effective_from, effective_to);

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <changeSet id="2026-08-add-max-boost-percentage" author="c.torres">
        <addColumn tableName="pricing_rule">
            <column name="max_boost_percentage" type="NUMERIC(5,2)" defaultValueNumeric="0.00">
                <constraints nullable="false"/>
            </column>
        </addColumn>
        <rollback>
            <dropColumn tableName="pricing_rule" columnName="max_boost_percentage"/>
        </rollback>
    </changeSet>

</databaseChangeLog>

@Repository
interface PricingRuleRepository : JpaRepository<PricingRule, Long> {

    @Query(
        """
        SELECT r FROM PricingRule r
        WHERE r.vehicleCategory.id = :categoryId
          AND r.effectiveFrom <= CURRENT_DATE
          AND (r.effectiveTo IS NULL OR r.effectiveTo > CURRENT_DATE)
        ORDER BY r.priority DESC
        """
    )
    fun findActiveRulesForCategory(categoryId: Long): List<PricingRule>
}