Structured System Logging with SQLite

[pbalduino]

Description

Implement a structured logging system using SQLite to store, query, and analyze system logs, replacing traditional text-based log files with a queryable database.

Overview

Traditional logging (syslog, text files) has limitations:

• Hard to query (need grep, awk, sed)
• No structure (parsing is fragile)
• Difficult to correlate events
• No easy filtering by severity, component, time range

SQLite-based logging provides:

• Structured data (columns, types)
• Fast queries with indexes
• Easy filtering and aggregation
• Reliable storage
• Standard SQL interface

Database Schema

Tables

log_entries - Main log table

• log_id (primary key, autoincrement)
• timestamp (integer, Unix timestamp)
• level (text: DEBUG, INFO, WARN, ERROR, FATAL)
• component (text: kernel, mosh, gcc, etc)
• message (text)
• pid (integer)
• thread_id (integer, optional)

log_metadata - Additional context

• log_id (foreign key)
• key (text)
• value (text)

Indexes

• idx_timestamp ON log_entries(timestamp)
• idx_level ON log_entries(level)
• idx_component ON log_entries(component)
• idx_pid ON log_entries(pid)

Logging API

Kernel API

C functions for kernel logging.

Userspace API

Library functions for userspace programs.

Log Daemon

Architecture

• Kernel writes to ring buffer
• Log daemon reads from ring buffer
• Daemon writes to SQLite database
• Runs in userspace

Features

• Asynchronous logging (non-blocking)
• Automatic log rotation (size/time based)
• Log compression (old logs)
• Log retention policies

Command-Line Tools

# View recent logs
logctl

# View logs for specific component
logctl --component=kernel

# View errors only
logctl --level=ERROR

# View logs in time range
logctl --since="2025-01-01" --until="2025-01-02"

# Follow logs (like tail -f)
logctl --follow

# Search logs
logctl --grep="page fault"

# Complex query
logctl --query="SELECT * FROM log_entries WHERE component='mosh' AND level='ERROR' ORDER BY timestamp DESC LIMIT 10"

SQL Query Examples

Common log analysis queries for recent errors, component stats, error patterns, and top log producers.

Log Rotation

• Archive old logs to separate database files
• Compress archived logs
• Delete logs older than retention period
• Configurable policies per component

Implementation

Components

1. Kernel ring buffer (existing)
2. Log daemon (new userspace process)
3. SQLite database (/var/log/messages.db)
4. Client library (libc logging functions)
5. CLI tool (logctl)

Log Flow

Kernel/Process → Ring Buffer → Log Daemon → SQLite DB → Query Tools

Tasks

• Design log database schema
• Create kernel logging API
• Create userspace logging library
• Implement log daemon
• Add ring buffer → SQLite writer
• Create logctl command-line tool
• Implement log rotation
• Add log compression
• Create log retention policies
• Add log query optimization
• Implement log forwarding (optional, for remote logging)
• Add structured metadata support

Integration

• Kernel ring buffer
• SQLite library
• File system for database files
• Process management (log daemon)

Acceptance Criteria

• Logs stored in SQLite database
• Fast queries on large log databases (100K+ entries)
• Kernel and userspace can log messages
• logctl tool provides intuitive interface
• Log rotation works automatically
• Old logs compressed and archived
• No log loss under heavy load
• Performance acceptable (logging doesn't slow system)

Priority

Medium - Nice to have, improves debuggability

Estimated Effort

2-3 weeks

Notes

• Similar to systemd's journald
• Start with basic logging, add features incrementally
• Consider memory-mapped DB for performance
• Log daemon should be reliable (don't lose logs)