marco
5bc68e3627
Implement local database storage to automatically save all sent insights with metadata including topic, content, AI provider, model, and timestamp. Add database module with initialization and query functions, integrate with main job flow, and update documentation with database schema details.
47 lines
1.2 KiB
Python
47 lines
1.2 KiB
Python
import sqlite3
|
|
import os
|
|
from datetime import datetime
|
|
|
|
|
|
DB_PATH = os.path.join(os.path.dirname(__file__), "insights.db")
|
|
|
|
|
|
def init_db() -> None:
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.execute("""
|
|
CREATE TABLE IF NOT EXISTS insights (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
topic TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
provider TEXT NOT NULL,
|
|
model TEXT NOT NULL,
|
|
sent_at TEXT NOT NULL
|
|
)
|
|
""")
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def save_insight(topic: str, content: str, provider: str, model: str) -> int:
|
|
conn = sqlite3.connect(DB_PATH)
|
|
cursor = conn.cursor()
|
|
sent_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
|
|
cursor.execute(
|
|
"INSERT INTO insights (topic, content, provider, model, sent_at) VALUES (?, ?, ?, ?, ?)",
|
|
(topic, content, provider, model, sent_at),
|
|
)
|
|
insight_id = cursor.lastrowid
|
|
conn.commit()
|
|
conn.close()
|
|
return insight_id
|
|
|
|
|
|
def get_insights(limit: int = 50):
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
rows = conn.execute(
|
|
"SELECT * FROM insights ORDER BY id DESC LIMIT ?", (limit,)
|
|
).fetchall()
|
|
conn.close()
|
|
return [dict(row) for row in rows]
|