Analyzes user computer usage patterns using raw SQL queries on the local Screenpipe database. Use when asking about screen time or app usage. Use when analyzing productivity or typing habits. Do not use for modifying the database. Do not use for tasks outside of local activity analytics.
Run raw SQL queries against the user's local Screenpipe database to answer questions about their computer usage patterns, productivity, and habits.
The API runs at http://localhost:3030.
bash, curlpowershell, curl.exe (not the alias)All examples below use bash. On Windows, adapt: use powershell, replace curl with curl.exe.
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
-d '{"query": "SELECT ... LIMIT 100"}'
Returns a JSON array of row objects.
LIMIT 100 or less.WHERE timestamp > datetime('now', '-24 hours') or similar. The database can have millions of rows.datetime('now', '-1 hours'), datetime('now', '-7 days'), date('now').date(timestamp) = date('now'). "yesterday" = date(timestamp) = date('now', '-1 day'). "this week" = timestamp > datetime('now', '-7 days').CREATE TABLE frames (
id INTEGER PRIMARY KEY,
video_chunk_id INTEGER,
offset_index INTEGER,
timestamp TIMESTAMP NOT NULL,
app_name TEXT, -- e.g. "Google Chrome", "VS Code", "Slack"
window_name TEXT, -- window title
focused BOOLEAN,
browser_url TEXT, -- URL if browser window
device_name TEXT -- monitor name
);
Key facts:
/activity-summary endpoint which uses gap-based duration (consecutive frames with gaps < 5min = active time).COUNT(*) * 2 / 60 (which assumes fixed 0.5 fps and is wildly inaccurate).app_name is the focused applicationbrowser_url is populated for browser windowswindow_name contains document/tab titlesCREATE TABLE audio_transcriptions (
id INTEGER PRIMARY KEY,
audio_chunk_id INTEGER,
timestamp TIMESTAMP NOT NULL,
transcription TEXT NOT NULL,
device TEXT, -- device name (e.g. "MacBook Pro Microphone")
is_input_device BOOLEAN, -- true = microphone, false = system audio
speaker_id INTEGER,
transcription_engine TEXT
);
CREATE TABLE audio_chunks (
id INTEGER PRIMARY KEY,
file_path TEXT NOT NULL,
timestamp TIMESTAMP
);
CREATE TABLE speakers (
id INTEGER PRIMARY KEY,
name TEXT,
metadata JSON
);
CREATE TABLE ui_events (
id INTEGER PRIMARY KEY,
timestamp DATETIME NOT NULL,
event_type TEXT NOT NULL, -- 'click', 'key', 'scroll', 'app_switch', 'window_focus', 'text'
app_name TEXT,
window_title TEXT,
browser_url TEXT,
element_role TEXT, -- accessibility role of clicked element
element_name TEXT, -- accessibility name of clicked element
text_content TEXT,
text_length INTEGER
);
CREATE TABLE accessibility (
id INTEGER PRIMARY KEY,
timestamp DATETIME NOT NULL,
app_name TEXT NOT NULL,
window_name TEXT NOT NULL,
text_content TEXT NOT NULL,
browser_url TEXT
);
CREATE TABLE ocr_text (
frame_id INTEGER NOT NULL,
text TEXT NOT NULL,
app_name TEXT,
window_name TEXT,
focused BOOLEAN
);
CREATE TABLE elements (
id INTEGER PRIMARY KEY,
frame_id INTEGER NOT NULL, -- FK to frames.id
source TEXT NOT NULL, -- 'accessibility' or 'ocr'
role TEXT NOT NULL, -- e.g. 'AXButton', 'AXStaticText', 'AXLink', 'line'
text TEXT, -- element text content
parent_id INTEGER, -- parent element id (for hierarchy)
depth INTEGER NOT NULL DEFAULT 0,
bounds_left REAL,
bounds_top REAL,
bounds_width REAL,
bounds_height REAL,
confidence REAL,
sort_order INTEGER NOT NULL DEFAULT 0
);
-- FTS index: elements_fts (text)
Key facts:
source='accessibility' has proper hierarchy (parent_id, depth) and rolessource='ocr' has flat text blocks with bounding boxes/elements API endpointframes via frame_id to get timestamps, app names, etc.curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
-d '{"query": "SELECT app_name, COUNT(*) as frames, ROUND(COUNT(*) * 2.0 / 60, 1) as minutes FROM frames WHERE timestamp > datetime('"'"'now'"'"', '"'"'-24 hours'"'"') AND app_name IS NOT NULL AND app_name != '"'"''"'"' GROUP BY app_name ORDER BY frames DESC LIMIT 20"}'
Simpler with heredoc:
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT app_name, COUNT(*) as frames, ROUND(COUNT(*) * 2.0 / 60, 1) as minutes FROM frames WHERE timestamp > datetime('now', '-24 hours') AND app_name IS NOT NULL AND app_name != '' GROUP BY app_name ORDER BY frames DESC LIMIT 20"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT CASE WHEN INSTR(SUBSTR(browser_url, INSTR(browser_url, '://') + 3), '/') > 0 THEN SUBSTR(SUBSTR(browser_url, INSTR(browser_url, '://') + 3), 1, INSTR(SUBSTR(browser_url, INSTR(browser_url, '://') + 3), '/') - 1) ELSE SUBSTR(browser_url, INSTR(browser_url, '://') + 3) END as domain, COUNT(*) as visits FROM frames WHERE timestamp > datetime('now', '-24 hours') AND browser_url IS NOT NULL AND browser_url != '' GROUP BY domain ORDER BY visits DESC LIMIT 20"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT date(timestamp) as day, COUNT(*) as frames, ROUND(COUNT(*) * 2.0 / 3600, 1) as hours, COUNT(DISTINCT app_name) as unique_apps FROM frames WHERE timestamp > datetime('now', '-7 days') AND app_name IS NOT NULL GROUP BY day ORDER BY day DESC LIMIT 10"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT strftime('%H:00', timestamp) as hour, app_name, COUNT(*) as frames FROM frames WHERE timestamp > datetime('now', '-24 hours') AND app_name IS NOT NULL AND app_name != '' GROUP BY hour, app_name HAVING frames > 5 ORDER BY hour DESC, frames DESC LIMIT 50"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT window_name, app_name, COUNT(*) as frames, ROUND(COUNT(*) * 2.0 / 60, 1) as minutes FROM frames WHERE timestamp > datetime('now', '-24 hours') AND window_name IS NOT NULL AND window_name != '' GROUP BY window_name, app_name ORDER BY frames DESC LIMIT 20"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT COALESCE(NULLIF(s.name, ''), 'Unknown') as speaker, COUNT(*) as segments, SUM(LENGTH(at.transcription)) as total_chars FROM audio_transcriptions at LEFT JOIN speakers s ON at.speaker_id = s.id WHERE at.timestamp > datetime('now', '-24 hours') GROUP BY at.speaker_id ORDER BY segments DESC LIMIT 20"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT strftime('%H:00', timestamp) as hour, COUNT(*) as switches FROM ui_events WHERE event_type = 'app_switch' AND timestamp > datetime('now', '-24 hours') GROUP BY hour ORDER BY hour LIMIT 24"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT app_name, COUNT(*) as clicks FROM ui_events WHERE event_type = 'click' AND timestamp > datetime('now', '-24 hours') AND app_name IS NOT NULL GROUP BY app_name ORDER BY clicks DESC LIMIT 20"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT f.app_name, e.role, COUNT(*) as count FROM elements e JOIN frames f ON f.id = e.frame_id WHERE f.timestamp > datetime('now', '-24 hours') AND e.source = 'accessibility' AND e.text IS NOT NULL GROUP BY f.app_name, e.role ORDER BY count DESC LIMIT 30"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT app_name, COUNT(*) as keystrokes FROM ui_events WHERE event_type = 'key' AND timestamp > datetime('now', '-24 hours') AND app_name IS NOT NULL GROUP BY app_name ORDER BY keystrokes DESC LIMIT 15"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT date(timestamp) as day, COUNT(DISTINCT id) as chunks, ROUND(COUNT(DISTINCT id) * 30.0 / 3600, 1) as approx_hours FROM audio_chunks WHERE timestamp > datetime('now', '-7 days') GROUP BY day ORDER BY day DESC LIMIT 10"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT CASE WHEN INSTR(SUBSTR(browser_url, INSTR(browser_url, '://') + 3), '/') > 0 THEN SUBSTR(SUBSTR(browser_url, INSTR(browser_url, '://') + 3), 1, INSTR(SUBSTR(browser_url, INSTR(browser_url, '://') + 3), '/') - 1) ELSE SUBSTR(browser_url, INSTR(browser_url, '://') + 3) END as domain, ROUND(COUNT(*) * 2.0 / 60, 1) as minutes FROM frames WHERE timestamp > datetime('now', '-24 hours') AND browser_url IS NOT NULL AND browser_url != '' GROUP BY domain ORDER BY minutes DESC LIMIT 20"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT CASE WHEN app_name IN ('VS Code', 'Code', 'Cursor', 'IntelliJ IDEA', 'PyCharm', 'WebStorm', 'Xcode', 'Android Studio', 'Neovim', 'Vim', 'Emacs', 'Sublime Text', 'Atom', 'WezTerm', 'iTerm2', 'Terminal', 'Alacritty', 'Warp', 'kitty', 'Ghostty', 'Hyper') THEN 'Coding & Terminal' WHEN app_name IN ('Google Chrome', 'Arc', 'Safari', 'Firefox', 'Brave Browser', 'Microsoft Edge', 'Chromium', 'Opera') THEN 'Browser' WHEN app_name IN ('Slack', 'Discord', 'Microsoft Teams', 'Telegram', 'Messages', 'WhatsApp', 'Signal', 'Zoom', 'zoom.us', 'Google Meet') THEN 'Communication' WHEN app_name IN ('Notion', 'Obsidian', 'Bear', 'Notes', 'Evernote', 'Roam Research', 'Logseq') THEN 'Notes & Docs' WHEN app_name IN ('Figma', 'Sketch', 'Adobe Photoshop', 'Adobe Illustrator', 'Canva') THEN 'Design' ELSE 'Other' END as category, COUNT(*) as frames, ROUND(COUNT(*) * 2.0 / 60, 1) as minutes FROM frames WHERE timestamp > datetime('now', '-24 hours') AND app_name IS NOT NULL AND app_name != '' GROUP BY category ORDER BY frames DESC LIMIT 10"}
PAYLOAD
curl -X POST http://localhost:3030/raw_sql \
-H "Content-Type: application/json" \
--data-binary @- <<'PAYLOAD'
{"query": "SELECT strftime('%w', timestamp) as weekday, strftime('%H', timestamp) as hour, COUNT(*) as frames FROM frames WHERE timestamp > datetime('now', '-7 days') AND app_name IS NOT NULL GROUP BY weekday, hour ORDER BY weekday, hour LIMIT 200"}
PAYLOAD
Weekday values: 0=Sunday, 1=Monday, ..., 6=Saturday.
| Table | Purpose | Time column | Key columns |
|---|---|---|---|
frames | Screen captures (~0.5 fps) | timestamp | app_name, window_name, browser_url, focused |
ocr_text | OCR text per frame | Join via frame_id | text, app_name, window_name |
elements | Structured UI elements | Join via frame_id → frames.timestamp | source, role, text, bounds_* |
audio_transcriptions | Speech segments | timestamp | transcription, device, speaker_id, is_input_device |
audio_chunks | Audio files (~30s) | timestamp | file_path |
speakers | Speaker identities | — | name, metadata |
ui_events | User interactions | timestamp | event_type, app_name, window_title, browser_url |
accessibility | Accessibility tree text | timestamp | app_name, window_name, text_content, browser_url |
-- Time filtering
WHERE timestamp > datetime('now', '-24 hours')
WHERE timestamp > datetime('now', '-7 days')
WHERE date(timestamp) = date('now')
WHERE timestamp BETWEEN '2024-01-15T00:00:00Z' AND '2024-01-15T23:59:59Z'
-- Group by time
GROUP BY date(timestamp) -- daily
GROUP BY strftime('%H:00', timestamp) -- hourly
GROUP BY strftime('%w', timestamp) -- by weekday
-- Frame count (NOT accurate screen time — use /activity-summary for that)
COUNT(*) as frame_count -- number of captured frames (event-driven, not fixed rate)
-- Domain extraction from browser_url
CASE WHEN INSTR(SUBSTR(browser_url, INSTR(browser_url, '://') + 3), '/') > 0
THEN SUBSTR(SUBSTR(browser_url, INSTR(browser_url, '://') + 3), 1, INSTR(SUBSTR(browser_url, INSTR(browser_url, '://') + 3), '/') - 1)
ELSE SUBSTR(browser_url, INSTR(browser_url, '://') + 3)
END as domain
-- Audio with speaker names
SELECT at.*, s.name as speaker_name
FROM audio_transcriptions at
LEFT JOIN speakers s ON at.speaker_id = s.id
-- OCR text with frame metadata
SELECT f.timestamp, f.app_name, o.text
FROM frames f
JOIN ocr_text o ON o.frame_id = f.id
-- UI events with frame context
SELECT u.event_type, u.app_name, u.element_name, f.window_name
FROM ui_events u
LEFT JOIN frames f ON u.frame_id = f.id
/activity-summary endpoint for accurate time estimates.HAVING frames > 5 removes apps you glanced at briefly.LEFT JOIN and COALESCE.click, key, scroll, app_switch, window_focus, text, clipboard.