Structured System Logging with SQLite

[pbalduino]

Replace text-based system logs with a queryable SQLite database for structured logging.

Overview

Implement a structured logging system using SQLite instead of traditional text log files. This enables powerful querying, filtering, and analysis of system logs while maintaining reliability and performance.

Database Schema

log_entries table:

• log_id (primary key)
• timestamp (indexed)
• level (ERROR, WARN, INFO, DEBUG)
• component (kernel, mosh, libc, app name)
• message (text)
• pid, thread_id
• additional metadata

log_metadata table:

• session_id (boot session)
• boot_time
• hostname
• kernel_version

Indexes on timestamp, component, and level for efficient queries.

Architecture

Log Daemon:

• Reads from kernel ring buffer
• Receives userspace log messages via IPC
• Batches writes to SQLite for performance
• Handles log rotation and retention policies

Command-Line Tool (logctl):

• Query logs with filters (time range, component, level)
• Tail logs in real-time
• Export to text formats

Benefits

• Powerful querying without grep/awk
• Structured data support
• Efficient time-range queries
• Reliable storage with SQLite ACID guarantees
• Similar to systemd journald

Dependencies

• Issue 257: SQLite integration must be complete
• Issue 193: libc required for daemon implementation

Estimated Effort

2-3 weeks