Database APIs

Monitoring data will be stored in a SQLlite DB. This module exposes all necessary APIs for the same.

The process monitor is detecting events with timestamps.

The next logical step is to persist these events rather than just printing them. That means building the storage module (db.py) with SQLite. 2 options in my mind 1. store as events - pid, app, start/ stop, timestamp (only inserts) 2. create new record at open - pid, app, start time, update stop time on stop based on pid and not sttopped (based on NULL stop time)

Option 1 (event log) — Simpler to write (inserts only), but querying “how long was Chrome open?” requires matching start/stop pairs afterwards. Option 2 (session record) — Slightly more complex (insert + update), but gives you duration directly. The risk: if the app or machine crashes, you’ll have orphan rows with NULL stop times.

Option 1 has less locking risk — it’s insert-only, so each write is a quick, independent transaction. Option 2 requires a SELECT to find the matching row, then an UPDATE, which holds the lock longer.

With Option 1 you can pair start/stop events in a SQL query when generating reports (e.g., using a self-join on PID). Want to start building the events table?

Process monitor output

Opened processes: 2026-02-21 16:20:08-{(874, 'sleep')}
Closed processes: 2026-02-21 16:20:23-{(870, 'top')}
Opened processes: 2026-02-21 16:21:08-{(875, 'top')}
Closed processes: 2026-02-21 16:21:23-{(875, 'top')}

HOw can I import a module from ../<folder name?>

🤖Reply🤖

🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠

The quickest way is to add that folder to sys.path:

import sys
sys.path.insert(0, '../folder_name')
import config as cf

Replace folder_name with the actual folder. If config.py is directly in the parent directory, just use '..' instead.

I would like to use the fastlite library for SQLlite related code. Documentation available at https://fastlite.answer.ai/

🤖Reply🤖

🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠

Let me check out the fastlite docs so I can help you use it effectively.

read_url(url=‘https://fastlite.answer.ai/’)

{
  "id": "toolu_01VjsUoGPqmkidDPRwzKecNN",
  "call": {
    "function": "read_url",
    "arguments": {
      "url": "https://fastlite.answer.ai/"
    }
  },
  "result": "<TRUNCATED>\u2026e ](./index.html)\\n\\n  * [ __ ](https://github.com/AnswerDotAI/fastlite)\\n\\n\\n\\n__\\n\\n  1. [fastlite](./index.html)\\n\\n\\n\\n  * [ fastlite](./index.html)\\n\\n  * [ Source](./core.html)\\n\\n  * [ Test Insert Operations](./test_insert.html)\\n\\n  * [ Test Update Operations](./test_update.html)\\n\\n  * [ Test Upsert Operations](./test_upsert.html)\\n\\n\\n\\n\\n## On this page\\n\\n  * Install\\n  * Overview\\n  * Dataclass support\\n  * Core design\\n  * Manipulating data\\n    * .insert()\\n    * .update()\\n    * .delete()\\n    * Multi-field primary keys\\n  * Diagrams\\n    * Importing CSV/TSV/etc\\n    * Database.import_file\\n\\n\\n\\n  * [__Edit this page](https://github.dev/AnswerDotAI/fastlite/blob/main/nbs/index.ipynb)\\n  * [ __Report an issue](https://github.com/AnswerDotAI/fastlite/issues/new)\\n  * [ __View source](https://github.com/AnswerDotAI/fastlite/blob/main/nbs/index.ipynb)\\n\\n\\n\\n## Other Formats\\n\\n  * [ __CommonMark](index.html.md)\\n\\n\\n\\n# fastlite\\n\\nA bit of extra usabi\u2026</TRUNCATED>"
}

🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠

Nice choice! fastlite makes SQLite much more pleasant to work with. To get started with your events table, you’d use something like:

db = database("snooper.db")

Then you can define your events table schema using a class. Have a go at defining an Event class with the fields we discussed (pid, app, event_type, timestamp) and create the table using db.create(Event).

let me start from DB creation again. I have deleted the file in back end

🤖Reply🤖

🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠

Fresh start! You’ve got the db object ready. Now try defining your Event dataclass and creating the table:

from dataclasses import dataclass

@dataclass
class Event:
    pid: int
    app: str
    event_type: str
    timestamp: str
    id: int = None

Then create the table with db.create(Event). Give it a go!

---

source

Event

def Event(
    args:VAR_POSITIONAL, kwargs:VAR_KEYWORD
):

The class container for process monitoring

now?

🤖Reply🤖

🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠

Table’s created! Now try inserting a test event to make sure it works:

events = db.t.event
events.insert(Event(pid=874, app='sleep', event_type='start', timestamp='2026-02-21 16:20:08'))

lets wrap this in a function

🤖Reply🤖

🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠

Sure! You’ll want a function that takes the event details and inserts into the table. Something like:

def log_event(pid, app, event_type, timestamp):
    ...

Have a go at filling in the body — you’ve already got the insert working above so it’s just wrapping it up.

---

source

log_event

def log_event(
    pid:int, # process identifier
    app:str, # application name
    event_type:str, # start of stop
    timestamp:str, # time of event
):

Creates the database if it does not exist and inserts a record

log_event(pid=874, app='sleep', event_type='stop', timestamp='2026-02-21 16:20:38')

how do I check for records in the table?

🤖Reply🤖

🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠

Try:

events()

That returns all rows from the table. You can also use events(limit=5) to limit results.

how do I write a query to so that start and stop event for a combination of pid and app can be extracted as a single row with duration calculated?

🤖Reply🤖

🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠

You can do this with a self-join — join the events table to itself, matching start events with stop events on the same pid and app. SQLite has a julianday() function that’s useful for calculating time differences.

The general shape is:

SELECT s.pid, s.app, s.timestamp AS start_time, e.timestamp AS stop_time
FROM events s JOIN events e ON s.pid = e.pid AND s.app = e.app
WHERE s.event_type = 'start' AND e.event_type = 'stop'

You can run raw SQL in fastlite with db.execute(sql).fetchall(). Try this query first, then we can add the duration calculation using julianday().

how to calculate the duration here?

🤖Reply🤖

🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠

The duration came out as 0 because SQLite is subtracting two text strings. You need to convert them to numeric values first using julianday():

(julianday(e.timestamp) - julianday(s.timestamp)) * 86400 AS duration_seconds

julianday() returns days, so multiplying by 86400 (seconds in a day) gives you duration in seconds. Try replacing your duration line with that.

---

source

get_processes

def get_processes(
    
):

Returns application usage with start time, stop time and duration in seconds

get_processes()