My agentic slop goes here. Not intended for anyone else!
anil.recoil.org
1-- Main log entries table with comprehensive structured fields
2CREATE TABLE IF NOT EXISTS logs (
3 id INTEGER PRIMARY KEY AUTOINCREMENT,
4
5 -- Core fields
6 timestamp REAL NOT NULL, -- Unix timestamp with microseconds
7 level INTEGER NOT NULL, -- 0=App, 1=Error, 2=Warning, 3=Info, 4=Debug
8 source TEXT NOT NULL, -- Module/source name (e.g., 'myapp.db.connection')
9 message TEXT NOT NULL, -- Full log message, no truncation
10
11 -- Error classification
12 error_type TEXT, -- Exception name or error classification
13 error_code TEXT, -- Specific error code if applicable
14 error_hash TEXT, -- Hash for grouping similar errors
15 stack_trace TEXT, -- Full stack trace
16
17 -- Execution context
18 pid INTEGER, -- Process ID
19 ppid INTEGER, -- Parent process ID
20 thread_id TEXT, -- Thread/fiber ID for Eio
21 fiber_id TEXT, -- Specific Eio fiber ID
22 domain_id INTEGER, -- OCaml domain ID for multicore
23
24 -- Request/Session tracking
25 session_id TEXT, -- Session identifier
26 request_id TEXT, -- Request identifier
27 trace_id TEXT, -- Distributed tracing ID
28 span_id TEXT, -- Span ID for tracing
29 parent_span_id TEXT, -- Parent span for trace hierarchy
30
31 -- Source location
32 file_path TEXT, -- Full file path
33 file_name TEXT, -- Just the filename
34 line_number INTEGER, -- Line number in source
35 column_number INTEGER, -- Column number if available
36 function_name TEXT, -- Function/method name
37 module_path TEXT, -- Full module path (e.g., 'Myapp.Db.Connection')
38
39 -- Performance metrics
40 duration_ms REAL, -- Operation duration in milliseconds
41 memory_before INTEGER, -- Memory usage before operation (bytes)
42 memory_after INTEGER, -- Memory usage after operation (bytes)
43 memory_delta INTEGER, -- Memory change
44 cpu_time_ms REAL, -- CPU time consumed
45
46 -- Network context
47 client_ip TEXT, -- Client IP address
48 server_ip TEXT, -- Server IP address
49 host_name TEXT, -- Hostname
50 port INTEGER, -- Port number
51 protocol TEXT, -- Protocol (HTTP, gRPC, etc.)
52 method TEXT, -- HTTP method or RPC method
53 path TEXT, -- Request path or endpoint
54 status_code INTEGER, -- Response status code
55 bytes_sent INTEGER, -- Bytes sent
56 bytes_received INTEGER, -- Bytes received
57
58 -- User context
59 user_id TEXT, -- User identifier
60 tenant_id TEXT, -- Tenant ID for multi-tenant apps
61 organization_id TEXT, -- Organization ID
62
63 -- System context
64 os_name TEXT, -- Operating system name
65 os_version TEXT, -- OS version
66 ocaml_version TEXT, -- OCaml compiler version
67 app_version TEXT, -- Application version
68 environment TEXT, -- Environment (dev, staging, prod)
69 region TEXT, -- Deployment region
70 container_id TEXT, -- Docker/container ID
71 k8s_pod TEXT, -- Kubernetes pod name
72 k8s_namespace TEXT, -- Kubernetes namespace
73
74 -- Metadata fields (JSON for flexibility)
75 tags TEXT, -- JSON array of tags
76 labels TEXT, -- JSON object of key-value labels
77 context TEXT, -- JSON object with arbitrary context
78 custom_fields TEXT, -- JSON object for app-specific fields
79
80 -- Audit fields
81 created_at REAL DEFAULT (julianday('now')),
82 indexed_at REAL -- When added to FTS index
83);
84
85-- Full-text search index on all text fields
86CREATE VIRTUAL TABLE IF NOT EXISTS logs_fts USING fts5(
87 message,
88 error_type,
89 error_code,
90 source,
91 file_path,
92 function_name,
93 module_path,
94 path,
95 method,
96 stack_trace,
97 tags,
98 labels,
99 context,
100 custom_fields,
101 content=logs,
102 content_rowid=id,
103 tokenize='porter unicode61'
104);
105
106-- Pattern tracking for error analysis
107CREATE TABLE IF NOT EXISTS patterns (
108 id INTEGER PRIMARY KEY AUTOINCREMENT,
109 pattern_hash TEXT UNIQUE NOT NULL, -- Hash of normalized pattern
110 pattern TEXT NOT NULL, -- Normalized pattern template
111 pattern_type TEXT, -- Type of pattern (error, warning, anomaly)
112
113 -- Occurrence tracking
114 first_seen REAL NOT NULL,
115 last_seen REAL NOT NULL,
116 occurrence_count INTEGER DEFAULT 1,
117 unique_sources INTEGER DEFAULT 1, -- Number of unique sources
118 unique_sessions INTEGER DEFAULT 1, -- Number of unique sessions affected
119
120 -- Statistical analysis
121 avg_duration_ms REAL, -- Average duration when this occurs
122 max_duration_ms REAL, -- Maximum duration observed
123 min_duration_ms REAL, -- Minimum duration observed
124
125 -- Trend analysis
126 hourly_rate REAL, -- Occurrences per hour
127 daily_rate REAL, -- Occurrences per day
128 acceleration_rate REAL, -- Rate of increase/decrease
129
130 -- Claude consultation tracking
131 severity_score REAL, -- Calculated severity (0-10)
132 impact_score REAL, -- Business impact score
133 last_consultation REAL, -- When Claude last reviewed
134 consultation_count INTEGER DEFAULT 0, -- Number of consultations
135 fix_attempted BOOLEAN DEFAULT 0, -- Whether a fix was attempted
136 fix_successful BOOLEAN DEFAULT 0, -- Whether the fix worked
137
138 -- Metadata
139 sample_log_ids TEXT, -- JSON array of example log IDs
140 related_patterns TEXT, -- JSON array of related pattern IDs
141 notes TEXT, -- Human or Claude notes
142
143 created_at REAL DEFAULT (julianday('now')),
144 updated_at REAL DEFAULT (julianday('now'))
145);
146
147-- Time-series data for pattern analysis
148CREATE TABLE IF NOT EXISTS pattern_metrics (
149 pattern_id INTEGER REFERENCES patterns(id) ON DELETE CASCADE,
150 timestamp REAL NOT NULL, -- Bucket timestamp (e.g., hour boundary)
151 bucket_size INTEGER NOT NULL, -- Bucket size in seconds (3600 for hourly)
152
153 -- Metrics for this time bucket
154 occurrence_count INTEGER DEFAULT 0,
155 unique_sources INTEGER DEFAULT 0,
156 unique_sessions INTEGER DEFAULT 0,
157 avg_duration_ms REAL,
158 max_duration_ms REAL,
159 error_rate REAL, -- Errors per second in this bucket
160
161 PRIMARY KEY (pattern_id, timestamp, bucket_size)
162);
163
164-- Session tracking for correlation
165CREATE TABLE IF NOT EXISTS sessions (
166 session_id TEXT PRIMARY KEY,
167 start_time REAL NOT NULL,
168 end_time REAL,
169 duration_ms REAL,
170
171 -- Session metadata
172 user_id TEXT,
173 tenant_id TEXT,
174 client_ip TEXT,
175 user_agent TEXT,
176
177 -- Session metrics
178 log_count INTEGER DEFAULT 0,
179 error_count INTEGER DEFAULT 0,
180 warning_count INTEGER DEFAULT 0,
181
182 -- Performance metrics
183 total_duration_ms REAL,
184 total_memory_bytes INTEGER,
185
186 created_at REAL DEFAULT (julianday('now'))
187);
188
189-- System metrics snapshot table
190CREATE TABLE IF NOT EXISTS system_metrics (
191 timestamp REAL PRIMARY KEY,
192
193 -- CPU metrics
194 cpu_usage_percent REAL,
195 cpu_user_percent REAL,
196 cpu_system_percent REAL,
197 cpu_idle_percent REAL,
198 load_avg_1m REAL,
199 load_avg_5m REAL,
200 load_avg_15m REAL,
201
202 -- Memory metrics
203 memory_total_bytes INTEGER,
204 memory_used_bytes INTEGER,
205 memory_free_bytes INTEGER,
206 memory_available_bytes INTEGER,
207 swap_used_bytes INTEGER,
208 swap_free_bytes INTEGER,
209
210 -- Disk metrics
211 disk_read_bytes_per_sec REAL,
212 disk_write_bytes_per_sec REAL,
213 disk_usage_percent REAL,
214
215 -- Network metrics
216 network_rx_bytes_per_sec REAL,
217 network_tx_bytes_per_sec REAL,
218 network_connections INTEGER,
219
220 -- Process metrics
221 process_count INTEGER,
222 thread_count INTEGER,
223 fiber_count INTEGER,
224
225 -- GC metrics (OCaml specific)
226 gc_minor_collections INTEGER,
227 gc_major_collections INTEGER,
228 gc_heap_words INTEGER,
229 gc_live_words INTEGER,
230
231 created_at REAL DEFAULT (julianday('now'))
232);
233
234-- Comprehensive indexes for efficient querying
235CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON logs(timestamp DESC);
236CREATE INDEX IF NOT EXISTS idx_logs_level_timestamp ON logs(level, timestamp DESC);
237CREATE INDEX IF NOT EXISTS idx_logs_source_timestamp ON logs(source, timestamp DESC);
238CREATE INDEX IF NOT EXISTS idx_logs_error_hash ON logs(error_hash) WHERE error_hash IS NOT NULL;
239CREATE INDEX IF NOT EXISTS idx_logs_session_id ON logs(session_id) WHERE session_id IS NOT NULL;
240CREATE INDEX IF NOT EXISTS idx_logs_request_id ON logs(request_id) WHERE request_id IS NOT NULL;
241CREATE INDEX IF NOT EXISTS idx_logs_trace_id ON logs(trace_id) WHERE trace_id IS NOT NULL;
242CREATE INDEX IF NOT EXISTS idx_logs_user_id ON logs(user_id) WHERE user_id IS NOT NULL;
243CREATE INDEX IF NOT EXISTS idx_logs_error_type ON logs(error_type) WHERE error_type IS NOT NULL;
244CREATE INDEX IF NOT EXISTS idx_logs_duration ON logs(duration_ms) WHERE duration_ms IS NOT NULL;
245CREATE INDEX IF NOT EXISTS idx_logs_status_code ON logs(status_code) WHERE status_code IS NOT NULL;
246
247CREATE INDEX IF NOT EXISTS idx_patterns_last_seen ON patterns(last_seen DESC);
248CREATE INDEX IF NOT EXISTS idx_patterns_severity ON patterns(severity_score DESC) WHERE severity_score IS NOT NULL;
249CREATE INDEX IF NOT EXISTS idx_patterns_count ON patterns(occurrence_count DESC);
250CREATE INDEX IF NOT EXISTS idx_pattern_metrics_timestamp ON pattern_metrics(timestamp DESC);
251
252-- Triggers to maintain FTS and update timestamps
253CREATE TRIGGER IF NOT EXISTS logs_fts_insert AFTER INSERT ON logs
254BEGIN
255 INSERT INTO logs_fts(rowid, message, error_type, error_code, source,
256 file_path, function_name, module_path, path, method,
257 stack_trace, tags, labels, context, custom_fields)
258 VALUES (new.id, new.message, new.error_type, new.error_code, new.source,
259 new.file_path, new.function_name, new.module_path, new.path, new.method,
260 new.stack_trace, new.tags, new.labels, new.context, new.custom_fields);
261
262 UPDATE logs SET indexed_at = julianday('now') WHERE id = new.id;
263END;
264
265CREATE TRIGGER IF NOT EXISTS patterns_update_timestamp AFTER UPDATE ON patterns
266BEGIN
267 UPDATE patterns SET updated_at = julianday('now') WHERE id = new.id;
268END;
269
270-- Views for common queries
271CREATE VIEW IF NOT EXISTS recent_errors AS
272SELECT
273 error_hash,
274 error_type,
275 error_code,
276 COUNT(*) as count,
277 COUNT(DISTINCT session_id) as affected_sessions,
278 COUNT(DISTINCT user_id) as affected_users,
279 MAX(timestamp) as last_seen,
280 MIN(timestamp) as first_seen,
281 GROUP_CONCAT(DISTINCT source) as sources,
282 AVG(duration_ms) as avg_duration_ms,
283 message as sample_message
284FROM logs
285WHERE level <= 2 -- Error and Warning
286 AND timestamp > julianday('now') - 1 -- Last 24 hours
287GROUP BY error_hash
288ORDER BY count DESC;
289
290CREATE VIEW IF NOT EXISTS error_trends AS
291SELECT
292 date(timestamp) as day,
293 level,
294 COUNT(*) as count,
295 COUNT(DISTINCT error_hash) as unique_errors,
296 COUNT(DISTINCT session_id) as affected_sessions,
297 AVG(duration_ms) as avg_duration_ms
298FROM logs
299WHERE level <= 2
300GROUP BY date(timestamp), level
301ORDER BY day DESC, level;
302
303CREATE VIEW IF NOT EXISTS slow_operations AS
304SELECT
305 source,
306 function_name,
307 COUNT(*) as count,
308 AVG(duration_ms) as avg_duration_ms,
309 MAX(duration_ms) as max_duration_ms,
310 MIN(duration_ms) as min_duration_ms,
311 SUM(duration_ms) as total_duration_ms
312FROM logs
313WHERE duration_ms IS NOT NULL
314GROUP BY source, function_name
315HAVING avg_duration_ms > 100 -- Operations slower than 100ms
316ORDER BY avg_duration_ms DESC;