the JSON functions are genuinely useful even for simple apps. i use sqlite as a dev database and being able to query JSON columns without a preprocessing step saves a lot of time. STRICT tables are also great, caught a bug where I was accidentally inserting the wrong type and it just silently worked in regular mode

> caught a bug where I was accidentally inserting the wrong type and it just silently worked in regular mode

Typically one would design their "DTO"s to catch such errors right in the application layer, way before it even made it into the DB.

Different people call this serialization-deserialization layer different names (DTO being the most ubiquitous I think) but in general, one programs the serialization-deserialization layer to catch structural issues (age is "O" instead of 0).

The DB is then delegated to catch unchanging ground truths and domain consistency issues (people who have a DOB into the future are signing up or humans who don't have any email or address at all when the business needs it).

In your case, it's great the DB caught gaps in the application layer but one would handle it way before it even made it there.

The way I think about DB types are:

1. unexpected gaps/bugs: "What did I miss in my application layer?"

2. expected and unchanging constraints: "What are some unchanging ground truth constraints in my business?", "What are the absolute physical limits of my data?" - while I check for a negative age in my DTOs to provide fast errors to the user, I put these constraints in the DB because it's an unchanging rule of reality.

Crucially, by keeping volatile business rules out of the database and restricting it only to these ground truths, I avoid being dragged down by constant DB migrations in a fast-evolving business