Replace Enums with Check Constraints

[TangoYankee]

Description

Check constraints have arrived in Drizzle ORM as of drizzle-orm 0.35.0 and drizzle-kit 0.26.1 (drizzle-kit release notes). This is important because they could replace enums in our api database and simplify our data flow.

Enums are a bit tricky to maintain because neither pg_dump nor foreign data wrappers detect them. When we run data-flow , we use drizzle kit to get the enum definitions from the target database to the data flow database. Because of a quirk of drizzle-kit, we need to manually rerun this definition step whenever the ENUMS change. If we switch our enums to check constraints, pg_dump would detect these as part of their introspection (step 7). This means we could rely solely on that step to see the whole database schema. This would let us completely remove drizzle-kit from our data-flow (step 0 in the documentation)

Implementation ticker: drizzle-team/drizzle-orm#2917
Documentation: https://orm.drizzle.team/docs/indexes-constraints#check

Acceptance criteria (spread across several tickets)

• Upgrade to at latest version of drizzle orm and drizzle kit
• Refactor enums to check constraints