I'm building a desktop application that I plan to use SQLite to store not only text (documents, notes, etc.), but also semi- and unstructured data (could be anything from biometrics to finances). Furthermore, all of this data will have metadata and user-defined relationships--all of which could be useful to filter by.

Although I've taught myself SQL for various projects, they all had very strict (or slowly evolving) schemas. I've built some test examples with and without an ORM in Python, but at the risk of bike shedding or just plain procrastinating, I'm looking for resources that may help me consider potential solutions and avoid obvious pitfalls. For instance, I envision I'll make extensive use of SQLite's JSON functions, but I haven't been able to find much on the ramifications of this.

Finally, I'm not looking for internet-scale anything. This would primarily be an offline single-user desktop application.