SQLite continues to surprise developers with features that rival traditional databases. The JSON extension lets you store and query JSON documents directly in tables while maintaining schema flexibility. Example: `SELECT json_extract(payload, '$.user.id') AS user_id FROM events` extracts nested data without schema rigidity. Similarly, FTS5 transforms SQLite into a full-text search engine, enabling phrases like `'local NEAR/5 storage'` to find relevant documents without external services.

Analytical capabilities shine through CTEs and window functions. Compute running totals with `SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at)` to build dashboards directly in SQLite. STRICT tables enforce type constraints at insert time, preventing subtle bugs in large codebases. Example: `CREATE TABLE users (...) STRICT` rejects invalid types during writes, aligning SQLite with PostgreSQL-like reliability.

Generated columns automate derived data storage. A `full_name` column combining `first_name` and `last_name` stays updated automatically when either changes. Enable WAL mode (`PRAGMA journal_mode = WAL`) for desktop apps and local-first tools, where readers and writers coexist without blocking. These features collectively make SQLite a viable option for complex applications previously requiring heavier databases.

While SQLite's flexibility has long been both a strength and limitation, these additions address critical gaps. WAL mode's concurrency benefits and STRICT tables' type safety demonstrate SQLite's evolution. Developers can now handle JSON, analytics, and strict schemas within a single file, reducing operational overhead. For small services or embedded systems, this means fewer dependencies and simpler deployments.