1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
"""
MeshBay Node — SQLite audit log for legal compliance.
Logs user actions with IP address, timestamp, and details.
Required by LCEN (France), EU e-Commerce Directive, and DSA
for hosting service operators.
Retention: 1 year minimum. Cleanup is caller's responsibility.
"""
import logging
import time
from dataclasses import dataclass
from pathlib import Path
import aiosqlite
log = logging.getLogger(__name__)
_SCHEMA = """
CREATE TABLE IF NOT EXISTS audit_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
timestamp REAL NOT NULL,
user_id TEXT NOT NULL,
username TEXT NOT NULL DEFAULT '',
ip TEXT NOT NULL DEFAULT '',
event TEXT NOT NULL,
group_id TEXT NOT NULL DEFAULT '',
detail TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_audit_ts ON audit_log(timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_event ON audit_log(event);
"""
EVENTS = {
"connect",
"disconnect",
"handshake",
"file_download",
"file_upload",
"file_delete",
"stream_video",
"chat_message",
"chat_history",
"index_sync",
"auth_failed",
}
RETENTION_DAYS = 365
@dataclass
class AuditEntry:
id: int
timestamp: float
user_id: str
username: str
ip: str
event: str
group_id: str
detail: str
class AuditStore:
"""Async SQLite audit log."""
def __init__(self, db_path: Path):
self._db_path = db_path
self._db: aiosqlite.Connection | None = None
async def open(self) -> None:
self._db_path.parent.mkdir(parents=True, exist_ok=True)
self._db = await aiosqlite.connect(str(self._db_path))
await self._db.executescript(_SCHEMA)
await self._db.commit()
async def close(self) -> None:
if self._db:
await self._db.close()
self._db = None
async def log_event(
self,
user_id: str,
event: str,
ip: str = "",
username: str = "",
group_id: str = "",
detail: str = "",
) -> None:
if not self._db:
return
await self._db.execute(
"INSERT INTO audit_log (timestamp, user_id, username, ip, event, group_id, detail) "
"VALUES (?, ?, ?, ?, ?, ?, ?)",
(time.time(), user_id, username, ip, event, group_id, detail),
)
await self._db.commit()
async def get_entries(
self,
since: float = 0,
limit: int = 200,
user_id: str | None = None,
event: str | None = None,
offset: int = 0,
) -> list[AuditEntry]:
conditions = ["timestamp > ?"]
params: list = [since]
if user_id:
conditions.append("user_id = ?")
params.append(user_id)
if event:
conditions.append("event = ?")
params.append(event)
params.append(limit)
params.append(max(0, offset))
where = " AND ".join(conditions)
cursor = await self._db.execute(
f"SELECT id, timestamp, user_id, username, ip, event, group_id, detail "
f"FROM audit_log WHERE {where} ORDER BY timestamp DESC LIMIT ? OFFSET ?",
params,
)
rows = await cursor.fetchall()
return [
AuditEntry(
id=r[0], timestamp=r[1], user_id=r[2], username=r[3],
ip=r[4], event=r[5], group_id=r[6], detail=r[7],
)
for r in rows
]
async def entry_count(self) -> int:
if not self._db:
return 0
cursor = await self._db.execute("SELECT COUNT(*) FROM audit_log")
row = await cursor.fetchone()
return row[0]
async def cleanup(self, retention_days: int = RETENTION_DAYS) -> int:
"""Delete entries older than retention_days. Returns count deleted."""
if not self._db:
return 0
cutoff = time.time() - (retention_days * 86400)
cursor = await self._db.execute(
"DELETE FROM audit_log WHERE timestamp < ?", (cutoff,))
await self._db.commit()
return cursor.rowcount
|