How ActivityWatch Stores Data

Understanding How ActivityWatch Stores Data

ActivityWatch is an open-source platform designed to monitor and record your digital activities, giving insight into how you spend your time on your devices. It tracks application usage and records event data in SQLite databases, providing a detailed look at user behavior.

Database Schema

Buckets Table

This table defines “buckets” that categorize different types of recorded events.

Column Name	Data Type	Description
id	INTEGER PRIMARY KEY	The unique autoincrement id of the “bucket”.
name	TEXT UNIQUE NOT NULL	The unique name identifying the “bucket”.
type	TEXT NOT NULL	The type of events the “bucket” records.
client	TEXT NOT NULL	The client or watcher that created the “bucket”.
hostname	TEXT NOT NULL	The hostname of the device where events are tracked.
created	TEXT NOT NULL	The creation timestamp of the “bucket”.
data_deprecated	TEXT DEFAULT ’{}‘	Legacy field for additional data (unused, deprecated).
data	TEXT NOT NULL DEFAULT ’{}‘	A field for additional JSON-formatted data about the “bucket”.

id	name	type	client	hostname	created	data_deprecated	data
1	aw-watcher-afk_shasharma-c13130	afkstatus	aw-watcher-afk	shasharma-c13130	2024-07-29 15:53:15.705137+00:00	{}	{“$aw.sync.origin”:“shasharma-c13130”}
2	aw-watcher-window_shasharma-c13130	currentwindow	aw-watcher-window	shasharma-c13130	2024-07-29 15:53:13.811889+00:00	{}	{“$aw.sync.origin”:“shasharma-c13130”}

Events Table

This table stores the actual time-tracked events.

Column Name	Data Type	Description
id	INTEGER PRIMARY KEY	The unique autoincrement id of the event.
bucketrow	INTEGER NOT NULL	Foreign key to the associated “bucket”.
starttime	INTEGER NOT NULL	The starting timestamp of the event.
endtime	INTEGER NOT NULL	The ending timestamp of the event.
data	TEXT NOT NULL	The JSON-formatted data about the event.

id	bucketrow	starttime	endtime	data
1966	2	1722344179173000000	1722344199921000000	{“app”:“Arc”,“title”:“ActivityWatch Web Watcher…
1967	2	1722344199922000000	1722344200879000000	{“app”:“Arc”,“title”:“github.com/activitywatch…
1968	2	1722344200880000000	1722344208653000000	{“app”:“Arc”,“title”:“github.com/ActivityWatch…

Key-Value Table

A table for various settings and configuration values.

Column Name	Data Type	Description
key	TEXT PRIMARY KEY	The unique identifier for the setting.
value	TEXT	The attached value for the setting.
last_modified	NUMBER NOT NULL	The timestamp of the last modification.

Indexes

ActivityWatch creates indexes to improve the performance of the database:

CREATE INDEX bucket_id_index ON buckets(id);
CREATE INDEX events_bucketrow_index ON events(bucketrow);
CREATE INDEX events_starttime_index ON events(starttime);
CREATE INDEX events_endtime_index ON events(endtime);

Query

.headers on
.mode column

SELECT *
FROM events
JOIN buckets ON events.bucketrow = buckets.id LIMIT 5;

Event ID	BucketRow	StartTime	EndTime	Event Data	Bucket ID	Bucket Name	Bucket Type	Bucket Client	Bucket Hostname	Bucket Created	Bucket Data Deprecated	Bucket Data
1	1	1722248595700000000	1722248846293000000	{“status”:“not-afk”}	1	aw-watcher-afk_shasharma-c13130	afkstatus	aw-watcher-afk	shasharma-c13130	2024-07-29 15:53:15.705137+00:00	{}	{“$aw.sync.origin”:“shasharma-c13130”}
2	1	1722248846293000000	1722249644100000000	{“status”:“afk”}	1	aw-watcher-afk_shasharma-c13130	afkstatus	aw-watcher-afk	shasharma-c13130	2024-07-29 15:53:15.705137+00:00	{}	{“$aw.sync.origin”:“shasharma-c13130”}
3	1	1722249644099000000	1722251767816000000	{“status”:“not-afk”}	1	aw-watcher-afk_shasharma-c13130	afkstatus	aw-watcher-afk	shasharma-c13130	2024-07-29 15:53:15.705137+00:00	{}	{“$aw.sync.origin”:“shasharma-c13130”}
4	1	1722249644099000000	1722249748850000000	{“status”:“not-afk”}	1	aw-watcher-afk_shasharma-c13130	afkstatus	aw-watcher-afk	shasharma-c13130	2024-07-29 15:53:15.705137+00:00	{}	{“$aw.sync.origin”:“shasharma-c13130”}
5	1	1722249644099000000	1722249718606000000	{“status”:“not-afk”}	1	aw-watcher-afk_shasharma-c13130	afkstatus	aw-watcher-afk	shasharma-c13130	2024-07-29 15:53:15.705137+00:00	{}	{“$aw.sync.origin”:“shasharma-c13130”}

Better query will look like:

SELECT
    e.id AS EventID,
    e.starttime AS StartTime,
    e.endtime AS EndTime,
    e.data AS EventData,
    b.id AS BucketID,
    b.name AS BucketName,
    b.type AS BucketType,
    b.client AS BucketClient,
    b.hostname AS BucketHostname
FROM events e
JOIN buckets b ON e.bucketrow = b.id LIMIT 5;