> There's always the lazy approach of storing JSON blobs in TEXT fields, but I personally shy away from that because you lose out on a huge part of the benefits of using a SQL DB in the first place, most importantly migrations and querying/indexing.

SQLite at least provides functions to make the “querying” part of that straightforward: https://sqlite.org/json1.html