File size: 9,986 Bytes
04913cd
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
"""Small SQLite repository shared by runs, history, and AI cache."""
from __future__ import annotations

import json
import sqlite3
import threading
import time
from contextlib import contextmanager
from pathlib import Path
from typing import Any, Iterator

from ..settings import settings


class Database:
    def __init__(self, path: Path | None = None):
        self.path = path or settings.database_path
        self._lock = threading.RLock()
        self._initialized = False

    @contextmanager
    def connect(self) -> Iterator[sqlite3.Connection]:
        self.path.parent.mkdir(parents=True, exist_ok=True)
        connection = sqlite3.connect(self.path, timeout=30, isolation_level=None)
        connection.row_factory = sqlite3.Row
        connection.execute("PRAGMA busy_timeout=30000")
        connection.execute("PRAGMA foreign_keys=ON")
        try:
            yield connection
        finally:
            connection.close()

    def initialize(self) -> None:
        with self._lock:
            if self._initialized:
                return
            with self.connect() as db:
                db.execute("PRAGMA journal_mode=WAL")
                db.execute("PRAGMA synchronous=NORMAL")
                db.executescript(
                    """
                    CREATE TABLE IF NOT EXISTS runs (
                      run_id TEXT PRIMARY KEY,
                      tool TEXT NOT NULL,
                      status TEXT NOT NULL,
                      created_at REAL NOT NULL,
                      updated_at REAL NOT NULL,
                      completed_at REAL,
                      input_fingerprint TEXT,
                      payload_json TEXT NOT NULL
                    );
                    CREATE INDEX IF NOT EXISTS idx_runs_updated ON runs(updated_at DESC);
                    CREATE INDEX IF NOT EXISTS idx_runs_fingerprint ON runs(tool, input_fingerprint, status);
                    CREATE TABLE IF NOT EXISTS history (
                      id INTEGER PRIMARY KEY AUTOINCREMENT,
                      created_at REAL NOT NULL,
                      payload_json TEXT NOT NULL
                    );
                    CREATE INDEX IF NOT EXISTS idx_history_created ON history(created_at DESC);
                    CREATE TABLE IF NOT EXISTS ai_cache (
                      cache_key TEXT PRIMARY KEY,
                      created_at REAL NOT NULL,
                      last_accessed REAL NOT NULL,
                      text_length INTEGER NOT NULL DEFAULT 0,
                      response TEXT NOT NULL
                    );
                    CREATE INDEX IF NOT EXISTS idx_cache_accessed ON ai_cache(last_accessed);
                    CREATE TABLE IF NOT EXISTS migrations (name TEXT PRIMARY KEY, applied_at REAL NOT NULL);
                    CREATE TABLE IF NOT EXISTS app_state (
                      state_key TEXT PRIMARY KEY,
                      updated_at REAL NOT NULL,
                      payload_json TEXT NOT NULL
                    );
                    """
                )
            self._initialized = True

    def upsert_run(self, run: dict[str, Any]) -> None:
        self.initialize()
        with self.connect() as db:
            db.execute(
                """INSERT INTO runs(run_id, tool, status, created_at, updated_at, completed_at, input_fingerprint, payload_json)
                   VALUES(?, ?, ?, ?, ?, ?, ?, ?)
                   ON CONFLICT(run_id) DO UPDATE SET tool=excluded.tool, status=excluded.status,
                   updated_at=excluded.updated_at, completed_at=excluded.completed_at,
                   input_fingerprint=excluded.input_fingerprint, payload_json=excluded.payload_json""",
                (run["run_id"], run.get("tool", "unknown"), run.get("status", "unknown"),
                 float(run.get("created_at") or time.time()), float(run.get("updated_at") or time.time()),
                 run.get("completed_at"), run.get("input_fingerprint"), json.dumps(run, ensure_ascii=False)),
            )

    def migration_applied(self, name: str) -> bool:
        self.initialize()
        with self.connect() as db:
            return db.execute("SELECT 1 FROM migrations WHERE name=?", (name,)).fetchone() is not None

    def mark_migration(self, name: str) -> None:
        self.initialize()
        with self.connect() as db:
            db.execute("INSERT OR IGNORE INTO migrations(name, applied_at) VALUES(?, ?)", (name, time.time()))

    def get_state(self, key: str, default: Any = None) -> Any:
        self.initialize()
        with self.connect() as db:
            row = db.execute("SELECT payload_json FROM app_state WHERE state_key=?", (key,)).fetchone()
        return json.loads(row[0]) if row else default

    def set_state(self, key: str, payload: Any) -> None:
        self.initialize()
        with self.connect() as db:
            db.execute("""INSERT INTO app_state(state_key, updated_at, payload_json) VALUES(?, ?, ?)
                          ON CONFLICT(state_key) DO UPDATE SET updated_at=excluded.updated_at,
                          payload_json=excluded.payload_json""",
                       (key, time.time(), json.dumps(payload, ensure_ascii=False)))

    def get_run(self, run_id: str) -> dict[str, Any] | None:
        self.initialize()
        with self.connect() as db:
            row = db.execute("SELECT payload_json FROM runs WHERE run_id=?", (run_id,)).fetchone()
        return json.loads(row[0]) if row else None

    def list_runs(self, limit: int = 100) -> list[dict[str, Any]]:
        self.initialize()
        with self.connect() as db:
            rows = db.execute("SELECT payload_json FROM runs ORDER BY created_at DESC LIMIT ?", (max(1, limit),)).fetchall()
        return [json.loads(row[0]) for row in rows]

    def add_history(self, payload: dict[str, Any]) -> None:
        self.initialize()
        created = float(payload.get("timestamp") or time.time())
        with self.connect() as db:
            db.execute("INSERT INTO history(created_at, payload_json) VALUES(?, ?)", (created, json.dumps(payload, ensure_ascii=False)))

    def history(self, limit: int = 500) -> list[dict[str, Any]]:
        self.initialize()
        with self.connect() as db:
            rows = db.execute("SELECT payload_json FROM (SELECT payload_json, created_at FROM history ORDER BY created_at DESC LIMIT ?) ORDER BY created_at ASC", (max(1, limit),)).fetchall()
        return [json.loads(row[0]) for row in rows]

    def clear_history(self) -> None:
        self.initialize()
        with self.connect() as db:
            db.execute("DELETE FROM history")

    def cache_get(self, key: str, ttl_seconds: float) -> dict[str, Any] | None:
        self.initialize()
        now = time.time()
        with self.connect() as db:
            row = db.execute("SELECT * FROM ai_cache WHERE cache_key=?", (key,)).fetchone()
            if not row:
                return None
            if now - float(row["created_at"]) > ttl_seconds:
                db.execute("DELETE FROM ai_cache WHERE cache_key=?", (key,))
                return None
            db.execute("UPDATE ai_cache SET last_accessed=? WHERE cache_key=?", (now, key))
        return dict(row)

    def cache_set(self, key: str, response: str, text_length: int) -> None:
        self.initialize()
        now = time.time()
        with self.connect() as db:
            db.execute("""INSERT INTO ai_cache(cache_key, created_at, last_accessed, text_length, response)
                          VALUES(?, ?, ?, ?, ?) ON CONFLICT(cache_key) DO UPDATE SET
                          created_at=excluded.created_at, last_accessed=excluded.last_accessed,
                          text_length=excluded.text_length, response=excluded.response""",
                       (key, now, now, text_length, response))

    def cache_clear(self) -> None:
        self.initialize()
        with self.connect() as db:
            db.execute("DELETE FROM ai_cache")

    def cache_stats(self, ttl_seconds: float) -> dict[str, int]:
        self.initialize()
        cutoff = time.time() - ttl_seconds
        with self.connect() as db:
            total = int(db.execute("SELECT COUNT(*) FROM ai_cache").fetchone()[0])
            expired = int(db.execute("SELECT COUNT(*) FROM ai_cache WHERE created_at < ?", (cutoff,)).fetchone()[0])
        return {"total": total, "expired": expired}

    def apply_retention(self) -> dict[str, int]:
        """Bound metadata growth for a small VM; active runs are never removed."""
        self.initialize()
        now = time.time()
        policy = settings.retention
        deleted = {"runs": 0, "history": 0, "cache": 0}
        with self.connect() as db:
            before = db.total_changes
            db.execute("DELETE FROM runs WHERE status IN ('completed','failed','cancelled') AND updated_at < ?", (now - policy.run_days * 86400,))
            db.execute("""DELETE FROM runs WHERE run_id IN (SELECT run_id FROM runs WHERE status IN ('completed','failed','cancelled')
                        ORDER BY updated_at DESC LIMIT -1 OFFSET ?)""", (policy.max_runs,))
            deleted["runs"] = db.total_changes - before
            before = db.total_changes
            db.execute("DELETE FROM history WHERE created_at < ?", (now - policy.history_days * 86400,))
            db.execute("DELETE FROM history WHERE id IN (SELECT id FROM history ORDER BY created_at DESC LIMIT -1 OFFSET ?)", (policy.max_history,))
            deleted["history"] = db.total_changes - before
            before = db.total_changes
            db.execute("DELETE FROM ai_cache WHERE created_at < ?", (now - policy.cache_days * 86400,))
            db.execute("DELETE FROM ai_cache WHERE cache_key IN (SELECT cache_key FROM ai_cache ORDER BY last_accessed DESC LIMIT -1 OFFSET ?)", (policy.max_cache_entries,))
            deleted["cache"] = db.total_changes - before
            db.execute("PRAGMA optimize")
        return deleted


database = Database()