SQLite is traditionally viewed as a simple relational database, ideal for lightweight client storage. But in building a local-first desktop application that indexes files, tools, and screen context, we required the flexibility of a schema-less document store coupled with strict ACID compliance. The solution lay in SQLite's robust JSON1 extension suite.
"By using generated columns and expression indexes over JSON payloads, SQLite out-retrieves dedicated document engines at zero memory overhead."
Rather than shipping heavy embedded databases that consume 300MB of resident memory on startup, Maple compiles SQLite with -DSQLITE_ENABLE_JSON1 and creates generated virtual columns on hot JSON paths. This lets us query deeply nested workspace properties with standard B-Tree indexing speed.