I made PostgreSQL refuse to store a lie

#postgres #python #database #softwareengineering

I'm building a tool that does one thing: a small business describes a manual process in plain words, and gets back what it costs per year, how much of it is automatable, and with what risk.

The whole product is a promise: this number is an honest estimate. And the fastest way to break that promise is to show a number that nobody actually estimated — a placeholder, a silent zero, a guess dressed up as a measurement. The day a customer catches one of those, the product is dead.

So the interesting engineering question wasn't "how do we estimate." It was: how do we make it impossible to store a number we didn't estimate?

A UI rule is not a guarantee

The obvious answer is "show a dash when there's no estimate." But that's a rule in one code path. There are dozens of code paths: the report, the CSV export, the dashboard total, next month's new endpoint written by a tired version of me. Any one of them can forget.

An invariant that lives in the presentation layer isn't an invariant. It's a suggestion.

Two ways to say "I don't know"

First, the model has to admit that "no number" isn't one state, it's several. Collapsing them into null throws away the reason, and the reason is the whole point:

class EstadoEstimacion(StrEnum):
    ESTIMADO = "estimado"                   # we have a number
    NO_ESTIMABLE = "no_estimable"           # missing input — fixable by asking the customer
    FUERA_DE_ALCANCE = "fuera_de_alcance"   # this axis doesn't apply to this process
    ERROR = "error"                         # something broke on our side

NO_ESTIMABLE and ERROR look the same to a naive UI ("no value"), but they mean opposite things: one is a question you can ask the customer, the other is a bug you have to fix. Aggregates have to treat them differently, and they can only do that if the difference survives all the way to the database.

The invariant, in the schema

Then the honesty rule goes where no code path can skip it — as CHECK constraints on the table itself:

__table_args__ = (
    # If there's no number, you must say why. A silent "unestimable"
    # can't go in a report or become a question — it's useless.
    CheckConstraint(
        "estado = 'estimado' OR motivo IS NOT NULL",
        name="ck_estimacion_motivo",
    ),
    # If there IS a number, you must say how much you trust it.
    # "Don't invent precision" — enforced by making the row impossible without it.
    CheckConstraint(
        "estado <> 'estimado' OR (valor IS NOT NULL AND confianza IS NOT NULL)",
        name="ck_estimacion_cifra",
    ),
    # A backwards range is a calculation bug that otherwise prints
    # cheerfully: "between €10,000 and €7,000 a year."
    CheckConstraint(
        "valor_min IS NULL OR valor_max IS NULL OR valor_min <= valor_max",
        name="ck_estimacion_horquilla",
    ),
)

Three constraints. I shipped that and felt good about it.

The bug that added the fourth

Then someone wiring up the estimation engines hit a case I hadn't: a row with estado = 'no_estimable' and a leftover valor. It sailed through all three checks — motivo was present, estado <> 'estimado' so the number wasn't required, the range was fine.

And then an aggregate that summed process costs forgot to filter by estado. So a number nobody had estimated got added into a company's yearly total. The exact lie the whole design existed to prevent — waved right through, because I'd guarded "estimated rows must have a number" but not "non-estimated rows must NOT have one."

The fix is the constraint I should have written first:

# Without an estimate, there is no number to store. This is the
# direction that gives the promise its name.
CheckConstraint(
    "estado = 'estimado' OR valor IS NULL",
    name="ck_estimacion_sin_cifra",
),

Now that row can't exist. The aggregate's missing filter is still a bug — but it's a bug that can't produce a dishonest total, because there's no dishonest data to sum.

The takeaway

Put your load-bearing invariant in the lowest layer that can enforce it. For a product whose entire value is "you can trust this number," that layer is the database, not the view and not a code review comment. The DB is the one place every path goes through and none can skip.

The bonus: the constraint names show up verbatim in the error when something violates them (ck_estimacion_sin_cifra). The failure tells you which promise you almost broke.

I'm building this at handmetric.com — process cost estimation for small businesses. Happy to talk shop about the estimation-confidence modeling in the comments.

DEV Community