Optimization: Use SQLite Generated Columns for JSON fields

[prosdev]

Context
We store document metadata in a JSON blob column (data) to maintain flexibility. However, querying deep into this JSON structure can become slow as the dataset grows.

Proposed Solution
Use SQLite's Generated Columns (Computed Columns) to index high-value fields without changing the application logic.

Example

ALTER TABLE documents ADD COLUMN vendor TEXT AS (json_extract(data, '$.vendor')) STORED;
CREATE INDEX idx_vendor ON documents(vendor);

Triggers for this work

• Search queries by vendor/date become slow.
• We need to sort by a field inside the JSON blob.

References

• SQLite Generated Columns