We validate fixed category keys in app code only, with no Postgres enum or CHECK; make the strongest case against that

Goal and context

Pumasi is adding two small controlled vocabularies to its data: a Category with eight keys (`engineering`, `product`, `design`, `writing`, `research`, `marketing`, `career`, `business`) and a Work type with five (`review`, `create`, `analyze`, `transform`, `plan`). Both are stored as the key string on two tables: `offer_version.category` / `offer_version.work_type` (NOT NULL) and `service_request.category` / `service_request.work_type` (nullable, but a CHECK constraint requires them NOT NULL when `kind = 'open'` and NULL when `kind = 'direct'`). The stored value is the key, not the display label. The decision under review: the set of valid keys is defined once in a TypeScript module and enforced with a zod enum at every write path (the seed, the Open Request creation command, and the WebMCP filter inputs). The database columns stay plain `text`. There is no Postgres `ENUM` type and no `CHECK (category IN (...))`. The stated reason is that adding a ninth category should be a code change and a deploy, not a migration; and that Postgres enum types are awkward to extend and impossible to shrink without a rewrite. Context that may matter to your judgement: the application is a single Next.js service with one Postgres database, migrations are explicit files applied by hand (`pnpm db:migrate`), never by the build. All writes go through the application; there is no second writer. The seeded data is verified after migration by a script that compares stored Offer rows to the seed definitions field by field. Category and Work type are used for discovery filtering, never for authorization or money. The team is one person plus coding agents; a migration is cheap in effort but is an extra step that has been forgotten before. The `service_request` table already has one large CHECK constraint encoding which columns must be null per `kind`, so there is precedent for putting shape rules in the database. We are not asking whether to use `text` versus `varchar`, nor about indexing. We are asking whether "valid values enforced only in application code" is the right layer for this vocabulary, and under what conditions we would regret it. We would also like you to weigh a middle path we considered and rejected: keep `text`, add a `CHECK (category IN (...))` constraint whose list is regenerated by the migration tool whenever the TypeScript list changes, so the database refuses unknown keys but adding one is still a normal migration. We rejected it because it reintroduces the migration step we wanted to avoid, but we may be wrong about the cost. Argue against us. If after doing so you agree with the decision, say so and say why the arguments against it do not bite here.

Result you want

A Markdown opinion with these sections. 1. **Conditions under which this decision is wrong.** At least three. For each: the concrete scenario, how likely it is in the described setup (not in general), what it would cost when it happens, and how we would notice. 2. **The middle path.** Your assessment of the CHECK-constraint-regenerated-by-migration option against the chosen one: what it buys, what it costs, and whether it is worth it here. 3. **Signals that should make us change the decision.** Observable events (a second writer appears, a bug of a certain kind ships, the vocabulary changes N times) that should trigger a revisit. 4. **Your recommendation.** Keep, change to the middle path, or change to a Postgres enum. One paragraph. If you recommend keeping it, list the arguments against that you found and why each does not apply to this setup. Keep it under two pages. Do not write SQL or TypeScript.

Details

Response

One answer

Credits

500

The Request is public. Only the Member who asked and the Member who answers see the Result.