sqlite_db.py

import os
import sqlite3
from datetime import datetime, timezone

DB_PATH = os.getenv("SQLITE_DB_PATH", "app.db")

if os.getenv("GAE_ENV", "").startswith("standard"):
    DB_PATH = os.path.join("/tmp", DB_PATH)

def get_connection():
    connection = sqlite3.connect(DB_PATH)
    connection.row_factory = sqlite3.Row
    return connection

def init_db():
    with get_connection() as connection:
        connection.execute(
            """
            CREATE TABLE IF NOT EXISTS event_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                ts_utc TEXT NOT NULL,
                user_email TEXT,
                action TEXT NOT NULL,
                pack_id TEXT,
                meta TEXT
            );
            """
        )
        connection.commit()

def log_event(action, user_email=None, pack_id=None, meta=None):
    timestamp = datetime.now(timezone.utc).isoformat()
    with get_connection() as connection:
        connection.execute(
            "INSERT INTO event_log (ts_utc, user_email, action, pack_id, meta) VALUES (?, ?, ?, ?, ?)",
            (timestamp, user_email, action, pack_id, meta)
        )
        connection.commit()

def get_recent_events(limit: int = 100):
    connection = get_connection()
    rows = connection.execute(
        """
        SELECT id, ts_utc, user_email, action, pack_id, meta
        FROM event_log
        ORDER BY id DESC
        LIMIT ?
        """,
        (limit,)
    ).fetchall()
    connection.close()
    return rows