Meridiona Meridian ETL Skill
How the ETL Works
screenpipe.db (read-only)
│
▼
runner.rs ← src/etl/runner.rs
polls every POLL_INTERVAL_SECS
│
├─ get_frames_since(cursor) ← src/db/screenpipe.rs
│ returns new frames since last processed frame_id
│
├─ detect_boundaries()
│ splits frames into blocks by focused_app change
│
├─ extract_block_context() ← src/etl/extractor.rs
│ OCR samples, window titles, audio snippets, signals
│
├─ upsert active_session ← src/db/meridian.rs
│ (current open block, updated each poll)
│
└─ insert completed app_sessions
(previous blocks, now closed)
Key Concepts
| Concept | What it is |
|---|---|
| cursor | last_frame_id in etl_cursor — marks where ETL left off |
| app-switch boundary | frame where focused_app differs from the previous frame |
| active_session | the currently-open, in-progress session (one row, upserted) |
| app_sessions | completed, closed sessions with final timestamps |
| category / confidence | AI-assigned activity category and confidence score on each session |
| gaps | user_idle or system_sleep periods between sessions |
Running with Verbose Logging
RUST_LOG=debug ./target/release/meridian
RUST_LOG=meridian=trace ./target/release/meridian # trace-level for ETL internals
Useful Debug Queries
# Open the meridian DB
sqlite3 ~/.meridian/meridian.db
# Top apps by time today
SELECT app_name, ROUND(SUM(duration_s)/60.0,1) AS minutes, COUNT(*) AS sessions
FROM app_sessions
WHERE started_at >= date('now')
GROUP BY app_name ORDER BY minutes DESC LIMIT 10;
# Check cursor (last processed frame)
SELECT * FROM etl_cursor;
# Inspect active session
SELECT * FROM active_session;
# Find gaps between sessions (potential missed frames)
SELECT
a.ended_at,
b.started_at,
ROUND((julianday(b.started_at) - julianday(a.ended_at)) * 86400) AS gap_secs
FROM app_sessions a
JOIN app_sessions b ON b.rowid = a.rowid + 1
WHERE gap_secs > 120
ORDER BY gap_secs DESC LIMIT 20;
# Sessions with zero duration (regression check)
SELECT * FROM app_sessions WHERE duration_s = 0;
# Count sessions per day
SELECT date(started_at) AS day, COUNT(*) AS n, ROUND(SUM(duration_s)/3600.0,2) AS hours
FROM app_sessions
GROUP BY day ORDER BY day DESC;
# Category breakdown (today)
SELECT category, COUNT(*) AS sessions, ROUND(SUM(duration_s)/60.0,1) AS minutes
FROM app_sessions
WHERE started_at >= date('now')
GROUP BY category ORDER BY minutes DESC;
# Inspect gaps (sleep / idle periods)
SELECT kind, COUNT(*) AS n, ROUND(SUM(duration_s)/60.0,1) AS total_min
FROM gaps GROUP BY kind;
# Long sessions that may be miscategorised
SELECT app_name, category, confidence, ROUND(duration_s/60.0) AS min, window_titles
FROM app_sessions WHERE duration_s > 600
ORDER BY started_at DESC LIMIT 10;
Common Issues
Zero-duration sessions
Single-frame sessions returned duration_s = 0. Fixed in commit 317ceb2 (Option D).
Verify with: SELECT * FROM app_sessions WHERE duration_s = 0;
Phantom sessions spanning sleep gaps
Machine sleep between two ETL runs created a session covering the sleep period.
Fixed in commit a8f2280 (sleep gap detection at ETL run boundary).
Check: SELECT * FROM app_sessions WHERE duration_s > 3600 ORDER BY duration_s DESC;
Duplicate sessions
Cursor not advancing correctly caused re-processing. Check cursor value:
sqlite3 ~/.meridian/meridian.db "SELECT * FROM etl_cursor;"
Screenpipe DB locked
Meridian must open screenpipe DB with read-only flag. If you see SQLITE_BUSY:
lsof ~/.screenpipe/db.sqlite
Reset and Re-run ETL from Scratch
# Stop the daemon
pkill meridian
# Delete meridian DB (this resets all sessions and cursor)
rm ~/.meridian/meridian.db
# Restart — ETL will re-process all screenpipe frames from scratch
./target/release/meridian
Running Integration Tests
cargo test # all tests
cargo test integration # integration tests only
RUST_LOG=debug cargo test -- --nocapture # with log output