32 lines
1.1 KiB
SQL
32 lines
1.1 KiB
SQL
CREATE TABLE logs (
|
|
log_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
|
|
timestamp TEXT NOT NULL,
|
|
level TEXT NOT NULL CHECK (level IN ('DEBUG', 'INFO', 'WARN', 'ERROR', 'FATAL')),
|
|
message TEXT NOT NULL,
|
|
args TEXT, -- JSON string of log arguments
|
|
source TEXT, -- tag or source component
|
|
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
|
|
-- Security-specific fields
|
|
event_type TEXT, -- For security events
|
|
severity TEXT, -- For security events
|
|
remote_addr TEXT,
|
|
ccn_pubkey TEXT,
|
|
event_id TEXT,
|
|
risk_score REAL
|
|
);
|
|
|
|
CREATE INDEX idx_logs_timestamp ON logs(timestamp);
|
|
CREATE INDEX idx_logs_level ON logs(level);
|
|
CREATE INDEX idx_logs_created_at ON logs(created_at);
|
|
CREATE INDEX idx_logs_source ON logs(source);
|
|
CREATE INDEX idx_logs_event_type ON logs(event_type);
|
|
CREATE INDEX idx_logs_severity ON logs(severity);
|
|
CREATE INDEX idx_logs_ccn_pubkey ON logs(ccn_pubkey);
|
|
|
|
CREATE TRIGGER cleanup_old_logs
|
|
AFTER INSERT ON logs
|
|
WHEN (SELECT COUNT(*) FROM logs) > 100000
|
|
BEGIN
|
|
DELETE FROM logs
|
|
WHERE created_at < (unixepoch() - 2592000); -- 30 days
|
|
END;
|