askrobots / dbbasic-tsv
Public
A database in TSV text files: data you can read, fast inserts, and an optional Rust speed-up.
audit_log.py · 9.9 KB · 313 lines
| 1 | #!/usr/bin/env python3 |
| 2 | """ |
| 3 | Audit logging for TSV database operations |
| 4 | Tracks all operations for debugging and compliance |
| 5 | """ |
| 6 | |
| 7 | import os |
| 8 | import json |
| 9 | import time |
| 10 | import threading |
| 11 | from pathlib import Path |
| 12 | from datetime import datetime |
| 13 | from typing import Dict, Any, Optional, List |
| 14 | from collections import deque |
| 15 | |
| 16 | |
| 17 | class AuditLog: |
| 18 | """ |
| 19 | Append-only audit log for database operations |
| 20 | Each operation gets logged with timestamp, user, and details |
| 21 | """ |
| 22 | |
| 23 | def __init__(self, log_dir: Path = None, max_memory_logs: int = 1000): |
| 24 | self.log_dir = log_dir or Path.cwd() / "logs" |
| 25 | self.log_dir.mkdir(parents=True, exist_ok=True) |
| 26 | |
| 27 | # Current log file (rotates daily) |
| 28 | self.current_date = datetime.now().date() |
| 29 | self.log_file = self._get_log_file() |
| 30 | |
| 31 | # In-memory buffer for recent operations |
| 32 | self.memory_buffer = deque(maxlen=max_memory_logs) |
| 33 | |
| 34 | # Thread safety |
| 35 | self.lock = threading.Lock() |
| 36 | |
| 37 | # Statistics |
| 38 | self.stats = { |
| 39 | 'total_operations': 0, |
| 40 | 'operations_by_type': {}, |
| 41 | 'errors': 0, |
| 42 | 'start_time': datetime.now() |
| 43 | } |
| 44 | |
| 45 | def _get_log_file(self) -> Path: |
| 46 | """Get current log file (rotates daily)""" |
| 47 | date_str = self.current_date.strftime("%Y%m%d") |
| 48 | return self.log_dir / f"audit_{date_str}.log" |
| 49 | |
| 50 | def _rotate_if_needed(self): |
| 51 | """Check if we need to rotate to new log file""" |
| 52 | current = datetime.now().date() |
| 53 | if current != self.current_date: |
| 54 | self.current_date = current |
| 55 | self.log_file = self._get_log_file() |
| 56 | |
| 57 | def log(self, operation: str, table: str, details: Dict[str, Any], |
| 58 | success: bool = True, error: Optional[str] = None): |
| 59 | """ |
| 60 | Log an operation |
| 61 | |
| 62 | Args: |
| 63 | operation: Type of operation (insert, query, update, delete) |
| 64 | table: Table name |
| 65 | details: Operation details (conditions, data, etc.) |
| 66 | success: Whether operation succeeded |
| 67 | error: Error message if failed |
| 68 | """ |
| 69 | with self.lock: |
| 70 | self._rotate_if_needed() |
| 71 | |
| 72 | # Create log entry |
| 73 | entry = { |
| 74 | 'timestamp': datetime.utcnow().isoformat(), |
| 75 | 'operation': operation, |
| 76 | 'table': table, |
| 77 | 'details': details, |
| 78 | 'success': success, |
| 79 | 'error': error, |
| 80 | 'user': os.environ.get('USER', 'unknown'), |
| 81 | 'pid': os.getpid() |
| 82 | } |
| 83 | |
| 84 | # Add to memory buffer |
| 85 | self.memory_buffer.append(entry) |
| 86 | |
| 87 | # Write to file |
| 88 | with open(self.log_file, 'a') as f: |
| 89 | f.write(json.dumps(entry) + '\n') |
| 90 | |
| 91 | # Update statistics |
| 92 | self.stats['total_operations'] += 1 |
| 93 | self.stats['operations_by_type'][operation] = \ |
| 94 | self.stats['operations_by_type'].get(operation, 0) + 1 |
| 95 | |
| 96 | if not success: |
| 97 | self.stats['errors'] += 1 |
| 98 | |
| 99 | def query_logs(self, operation: Optional[str] = None, |
| 100 | table: Optional[str] = None, |
| 101 | start_time: Optional[datetime] = None, |
| 102 | limit: int = 100) -> List[Dict]: |
| 103 | """Query recent logs from memory""" |
| 104 | results = [] |
| 105 | |
| 106 | for entry in reversed(self.memory_buffer): |
| 107 | # Apply filters |
| 108 | if operation and entry['operation'] != operation: |
| 109 | continue |
| 110 | if table and entry['table'] != table: |
| 111 | continue |
| 112 | if start_time: |
| 113 | entry_time = datetime.fromisoformat(entry['timestamp']) |
| 114 | if entry_time < start_time: |
| 115 | continue |
| 116 | |
| 117 | results.append(entry) |
| 118 | |
| 119 | if len(results) >= limit: |
| 120 | break |
| 121 | |
| 122 | return results |
| 123 | |
| 124 | def get_stats(self) -> Dict[str, Any]: |
| 125 | """Get audit statistics""" |
| 126 | with self.lock: |
| 127 | uptime = datetime.now() - self.stats['start_time'] |
| 128 | |
| 129 | return { |
| 130 | 'total_operations': self.stats['total_operations'], |
| 131 | 'operations_by_type': dict(self.stats['operations_by_type']), |
| 132 | 'errors': self.stats['errors'], |
| 133 | 'error_rate': self.stats['errors'] / max(1, self.stats['total_operations']), |
| 134 | 'uptime_seconds': uptime.total_seconds(), |
| 135 | 'current_log_file': str(self.log_file), |
| 136 | 'memory_buffer_size': len(self.memory_buffer) |
| 137 | } |
| 138 | |
| 139 | def tail(self, n: int = 10) -> List[Dict]: |
| 140 | """Get last n log entries""" |
| 141 | return list(self.memory_buffer)[-n:] |
| 142 | |
| 143 | def search(self, pattern: str, limit: int = 100) -> List[Dict]: |
| 144 | """Search logs for pattern""" |
| 145 | results = [] |
| 146 | |
| 147 | # Search in memory first |
| 148 | for entry in reversed(self.memory_buffer): |
| 149 | if pattern.lower() in json.dumps(entry).lower(): |
| 150 | results.append(entry) |
| 151 | if len(results) >= limit: |
| 152 | return results |
| 153 | |
| 154 | # Search in current log file if needed |
| 155 | if len(results) < limit and self.log_file.exists(): |
| 156 | with open(self.log_file, 'r') as f: |
| 157 | for line in reversed(f.readlines()): |
| 158 | if pattern.lower() in line.lower(): |
| 159 | try: |
| 160 | entry = json.loads(line) |
| 161 | results.append(entry) |
| 162 | if len(results) >= limit: |
| 163 | break |
| 164 | except json.JSONDecodeError: |
| 165 | continue |
| 166 | |
| 167 | return results |
| 168 | |
| 169 | |
| 170 | class QueryLog: |
| 171 | """ |
| 172 | Specialized log for query performance tracking |
| 173 | """ |
| 174 | |
| 175 | def __init__(self, log_dir: Path = None): |
| 176 | self.log_dir = log_dir or Path.cwd() / "logs" |
| 177 | self.log_dir.mkdir(parents=True, exist_ok=True) |
| 178 | self.query_log = self.log_dir / "queries.log" |
| 179 | |
| 180 | # Performance statistics |
| 181 | self.slow_queries = deque(maxlen=100) # Keep last 100 slow queries |
| 182 | self.query_stats = { |
| 183 | 'total_queries': 0, |
| 184 | 'total_time': 0, |
| 185 | 'cache_hits': 0, |
| 186 | 'cache_misses': 0, |
| 187 | 'index_hits': 0, |
| 188 | 'full_scans': 0 |
| 189 | } |
| 190 | |
| 191 | def log_query(self, table: str, conditions: Dict, |
| 192 | execution_time: float, rows_returned: int, |
| 193 | used_index: bool = False, cache_hit: bool = False): |
| 194 | """Log query execution""" |
| 195 | entry = { |
| 196 | 'timestamp': datetime.utcnow().isoformat(), |
| 197 | 'table': table, |
| 198 | 'conditions': conditions, |
| 199 | 'execution_time_ms': execution_time * 1000, |
| 200 | 'rows_returned': rows_returned, |
| 201 | 'used_index': used_index, |
| 202 | 'cache_hit': cache_hit |
| 203 | } |
| 204 | |
| 205 | # Write to log |
| 206 | with open(self.query_log, 'a') as f: |
| 207 | f.write(json.dumps(entry) + '\n') |
| 208 | |
| 209 | # Update statistics |
| 210 | self.query_stats['total_queries'] += 1 |
| 211 | self.query_stats['total_time'] += execution_time |
| 212 | |
| 213 | if cache_hit: |
| 214 | self.query_stats['cache_hits'] += 1 |
| 215 | else: |
| 216 | self.query_stats['cache_misses'] += 1 |
| 217 | |
| 218 | if used_index: |
| 219 | self.query_stats['index_hits'] += 1 |
| 220 | elif not cache_hit: |
| 221 | self.query_stats['full_scans'] += 1 |
| 222 | |
| 223 | # Track slow queries (> 100ms) |
| 224 | if execution_time > 0.1: |
| 225 | self.slow_queries.append(entry) |
| 226 | |
| 227 | def get_slow_queries(self) -> List[Dict]: |
| 228 | """Get recent slow queries""" |
| 229 | return list(self.slow_queries) |
| 230 | |
| 231 | def get_stats(self) -> Dict: |
| 232 | """Get query performance statistics""" |
| 233 | total = max(1, self.query_stats['total_queries']) |
| 234 | |
| 235 | return { |
| 236 | 'total_queries': total, |
| 237 | 'avg_time_ms': (self.query_stats['total_time'] / total) * 1000, |
| 238 | 'cache_hit_rate': self.query_stats['cache_hits'] / total, |
| 239 | 'index_hit_rate': self.query_stats['index_hits'] / total, |
| 240 | 'full_scan_rate': self.query_stats['full_scans'] / total, |
| 241 | 'slow_queries': len(self.slow_queries) |
| 242 | } |
| 243 | |
| 244 | |
| 245 | # Global audit log instance |
| 246 | _audit_log = None |
| 247 | _query_log = None |
| 248 | |
| 249 | |
| 250 | def get_audit_log() -> AuditLog: |
| 251 | """Get global audit log instance""" |
| 252 | global _audit_log |
| 253 | if _audit_log is None: |
| 254 | _audit_log = AuditLog() |
| 255 | return _audit_log |
| 256 | |
| 257 | |
| 258 | def get_query_log() -> QueryLog: |
| 259 | """Get global query log instance""" |
| 260 | global _query_log |
| 261 | if _query_log is None: |
| 262 | _query_log = QueryLog() |
| 263 | return _query_log |
| 264 | |
| 265 | |
| 266 | def test_audit_log(): |
| 267 | """Test audit logging""" |
| 268 | print("Testing Audit Log") |
| 269 | print("=" * 50) |
| 270 | |
| 271 | log = AuditLog() |
| 272 | |
| 273 | # Log some operations |
| 274 | print("\n1. Logging Operations:") |
| 275 | log.log('insert', 'users', {'id': '1', 'name': 'Alice'}) |
| 276 | log.log('query', 'users', {'conditions': {'id': '1'}}) |
| 277 | log.log('update', 'users', {'id': '1', 'changes': {'name': 'Alicia'}}) |
| 278 | log.log('delete', 'users', {'id': '1'}) |
| 279 | log.log('insert', 'users', {'id': '2'}, success=False, error='Duplicate key') |
| 280 | |
| 281 | print(f" ✓ Logged 5 operations") |
| 282 | |
| 283 | # Get recent logs |
| 284 | print("\n2. Recent Operations:") |
| 285 | recent = log.tail(3) |
| 286 | for entry in recent: |
| 287 | print(f" {entry['operation']}: {entry['table']} - Success: {entry['success']}") |
| 288 | |
| 289 | # Get statistics |
| 290 | print("\n3. Audit Statistics:") |
| 291 | stats = log.get_stats() |
| 292 | print(f" Total operations: {stats['total_operations']}") |
| 293 | print(f" Error rate: {stats['error_rate']:.1%}") |
| 294 | print(f" Operations by type: {stats['operations_by_type']}") |
| 295 | |
| 296 | # Test query log |
| 297 | print("\n4. Query Performance Log:") |
| 298 | qlog = QueryLog() |
| 299 | |
| 300 | qlog.log_query('users', {'id': '1'}, 0.001, 1, used_index=True) |
| 301 | qlog.log_query('users', {'name': 'Alice'}, 0.150, 1, used_index=False) |
| 302 | qlog.log_query('users', {'id': '2'}, 0.0005, 1, cache_hit=True) |
| 303 | |
| 304 | qstats = qlog.get_stats() |
| 305 | print(f" Average query time: {qstats['avg_time_ms']:.2f}ms") |
| 306 | print(f" Cache hit rate: {qstats['cache_hit_rate']:.1%}") |
| 307 | print(f" Index hit rate: {qstats['index_hit_rate']:.1%}") |
| 308 | |
| 309 | print("\n✓ All audit tests passed!") |
| 310 | |
| 311 | |
| 312 | if __name__ == "__main__": |
| 313 | test_audit_log() |