Database Validation That Does Not Punish Value Evolution

Enum columns nobody guards

Migration 0004 in DemandScope started from a plain finding: three string columns behaving like enums spread across three tables, and not one of them guarded at the database level. Validation lived entirely in the application layer through StrEnum and Pydantic. As long as every writer goes through the same app, that is enough. The problem is that databases outlive any application.

My early guess was that discipline in code was adequate. That guess ignored rogue writers: an ad-hoc debug script, a direct psql session, a future microservice sharing the same database. A single garbage INSERT from a manual session is enough to corrupt a status column every reader assumes is always valid. Validation that lives only in the app is basically a promise nobody enforces.

A generic constraint for value lists

The fix is three CHECK constraints (ck_signals_signal_type, ck_ingest_runs_status, ck_users_role) enforcing list membership directly in the database. PostgreSQL defines CHECK as the most generic constraint type: the column value must satisfy a Boolean expression [1].

Verification did not stop at the schema. An upgrade-downgrade-upgrade cycle ran clean; deliberate garbage inserts were rejected on all three columns; 49 tests green; and a live 20-case shipment stayed steady-state, unchanged 20 times across two consecutive runs:

def upgrade():
    # Tiny pilot tables: full validation is instant.
    # Past ~1M rows: switch to NOT VALID + separate VALIDATE.
    op.create_check_constraint(
        "ck_signals_signal_type", "signals",
        "signal_type IN ('vendor_blacklist', 'suit_bankruptcy_pkpu')",
    )

Why not a native ENUM

The fair question: why not use the built-in ENUM type. The PostgreSQL docs answer it directly: enum types are "primarily intended for static sets of values", and adding new values goes through ALTER TYPE [13]. The value sets in status columns are anything but static; new values are born from product needs, not from schema migration schedules.

The MySQL world shows the same instinct breaking differently: MySQL 5.7 permits in-place ENUM change only by appending members at the end of the list without changing storage size; inserting in the middle renumbers existing members and forces a full table copy (a finding from related prior migration research). The motivation is identical across both ecosystems: value sets evolve, and the schema must not punish evolution with expensive operations. Adding a value to a CHECK constraint is just a plain ADD CONSTRAINT, with no type rewrite and no long lock.

The layering order is deliberate too. A Pydantic error message can explain to the user that a value is unknown; a database-constraint message is coarser by nature, and that is exactly its function: it lands in logs as a writer anomaly, not as user-facing confusion. The two layers catch two different kinds of problems, and only one deserves to be seen by users.

One anticipated evolution as an example: new signal types will be born as new sources join the pilot. With CHECK, adding one is a single new constraint statement without touching the global type definition; no lookup table to update, no type-migration downtime. The cost of evolution drops from a schema operation to a configuration operation.

A migration that does not lock the workbench

For small pilot tables, full validation happens in one go at constraint creation. This commit documents a clear rule for later: once a table passes roughly one million rows, switch to ADD CONSTRAINT ... NOT VALID. That command skips the potentially long table scan and commits immediately; its purpose is reducing the impact of adding a constraint on concurrent updates [12]. The new constraint applies to incoming INSERTs and UPDATEs right away, while old rows catch up later.

Follow-up validation through VALIDATE CONSTRAINT takes only a SHARE UPDATE EXCLUSIVE lock on the altered table, so ordinary reads and writes keep flowing while old rows are checked. The one-million-row rule above is this commit's own operational stipulation, not an external benchmark; what matters is not the number but that the strategy is written down before any table gets big.

The decision I took: columns with a limited vocabulary always get a two-layer guard. The app layer (StrEnum, Pydantic) stays the first validator because its messages are the most human; the database constraint is the last line, holding firm when every first validator is bypassed. A shared database must never trust a promise it cannot enforce itself.

Sources

1. PostgreSQL Documentation: Constraints
2. PostgreSQL Documentation: ALTER TABLE
3. PostgreSQL Documentation: Enumerated Types