meridian-etl

name	meridian-etl
description	Debug and work with Meridian's ETL pipeline. Covers session boundary detection, cursor management, DB queries, and common failure modes.
allowed-tools	Bash, Read, Edit, Grep

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