Analyze application event logs ​

Use typed columns for fields that reports commonly filter or group, such as a day, service name and severity. Keep optional structured attributes in JSON when their shape varies. The application supplies the events through a documented insert or file-input workflow; no automatic log collector is implied.

CREATE TABLE application_events(day Date, service TEXT, severity TEXT, details JSON);
INSERT INTO application_events VALUES
    ('2025-01-01', 'api', 'error', '{"route":"/orders"}'),
    ('2025-01-01', 'api', 'info', '{"route":"/orders"}');

SELECT day, service, count(*) AS errors
FROM application_events
WHERE severity = 'error'
GROUP BY day, service
ORDER BY day, service;

Keep timestamps and severity values consistent at insertion time so comparisons do not depend on accidental spelling differences. Use JSON extraction for a specific attribute and json_each only when the task needs expanded child rows.

Event retention requires an explicit cutoff and DELETE workflow. Review sensitive values before importing them, and remove them from support examples and exported results. Application event tables are separate from system.query_log, which records database requests rather than arbitrary application messages.

See JSON functions, input formats, retention and query history.