Skip to main content

​
Overview

Monolex uses multiple SQLite databases to manage different aspects of terminal operations, project tracking, and file monitoring. Each database has a single, focused purpose following the SMPC principle. 
~/Library/Application Support/Monolex/protocols/niia/database/
│
├── session.db          # Terminal session state and crash recovery
├── onit.db             # Project management and activity tracking
├── niia-watcher.db     # File system event monitoring
├── atomic-term.db      # PTY output parsing and activity logs
├── work-wiki-diff.db   # Git diff history and AI attribution
└── file-paths.db       # Path normalization and quick access

​
Architecture Layers

The databases are organized into three conceptual layers based on their role and update frequency. 
╔══════════════════════════════════════════════════════════════════════╗
║                         DATABASE ARCHITECTURE                        ║
╠══════════════════════════════════════════════════════════════════════╣
║                                                                      ║
║   ┌───────────────── CORE LAYER ────────────────────────────────┐    ║
║   │                                                             │    ║
║   │  onit.db           session.db          file-paths.db        │    ║
║   │  (Projects)        (Sessions)          (Path Index)         │    ║
║   │       │                │                    │               │    ║
║   │       └────────────────┴────────────────────┘               │    ║
║   │                        │                                    │    ║
║   │              project_id / session_id                        │    ║
║   │                        │                                    │    ║
║   └────────────────────────┼────────────────────────────────────┘    ║
║                            │                                         ║
║   ┌───────────────── ACTIVITY LAYER ────────────────────────────┐    ║
║   │                                                             │    ║
║   │  atomic-term.db        niia-watcher.db                      │    ║
║   │  (PTY Parsing)         (File Events)                        │    ║
║   │       │                      │                              │    ║
║   │       └──────────────────────┘                              │    ║
║   │                │                                            │    ║
║   │          file_path (correlation)                            │    ║
║   └─────────────────────────────────────────────────────────────┘    ║
║                                                                      ║
║   ┌───────────────── HISTORY LAYER ─────────────────────────────┐    ║
║   │                                                             │    ║
║   │  work-wiki-diff.db                                          │    ║
║   │  (Git Diff History)                                         │    ║
║   │                                                             │    ║
║   └─────────────────────────────────────────────────────────────┘    ║
║                                                                      ║
╚══════════════════════════════════════════════════════════════════════╝

​
Database Summaries

​
session.db - Session State

Terminal session persistence, heartbeat tracking, and crash recovery.
Tables:
  • terminal_heartbeats - Live terminal state monitoring
  • terminals - Terminal metadata and settings
  • terminal_sessions - Scrollback data for recovery
  • terminal_group_log - Multi-tab group tracking
Key Operations:
FunctionTriggerPurpose
session_db_save_terminalTab openRegister new terminal
session_db_heartbeatEvery 10sUpdate alive status
session_db_end_terminalTab closeMark as ended
cleanup_dead_terminalsApp startClear stale entries
┌────────────────────────────────────────────────────────────────────────┐
│                      terminal_heartbeats                               │
├────────────────────────────────────────────────────────────────────────┤
│ terminal_id     │ TEXT    │ PRIMARY KEY    │ Unique terminal ID        │
│ instance_id     │ TEXT    │ NOT NULL       │ App instance UUID         │
│ last_heartbeat  │ INTEGER │ NOT NULL       │ Last ping timestamp (ms)  │
│ pid             │ INTEGER │ nullable       │ PTY process ID            │
│ is_alive        │ INTEGER │ DEFAULT 1      │ Alive flag (0/1)          │
│ cols            │ INTEGER │ DEFAULT 80     │ Terminal columns          │
│ rows            │ INTEGER │ DEFAULT 24     │ Terminal rows             │
│ cwd             │ TEXT    │ nullable       │ Current working dir       │
│ pty_path        │ TEXT    │ nullable       │ PTY socket path           │
└────────────────────────────────────────────────────────────────────────┘
Session Lifecycle: 
Terminal Created
     │
     │ INSERT terminal_heartbeats (is_alive=1)
     ▼
┌────────────────────────────────────────────┐
│   ALIVE   │◀───── Heartbeat every 10 seconds
└────────────────┘       UPDATE last_heartbeat
     │
     ├────────────────────┐
     │                    │
     │ Tab closed         │ No heartbeat for 30s
     ▼                    ▼
┌───────────┐      ┌───────────┐
│   ENDED   │      │   DEAD    │
└───────────┘      └───────────┘
                         │
                         │ App restart + socket exists
                         ▼
                   ┌───────────┐
                   │ RECOVERED │
                   └───────────┘

​
onit.db - Project Management

Project definitions, folder mappings, context documents, and daily statistics.
Tables:
  • monolex_projects - Project metadata and status
  • monolex_project_folders - Project-folder mappings
  • context_docs - Global and project-scoped context files
  • daily_stats - Aggregated daily activity metrics
  • terminal_name_history - Terminal rename audit trail
┌────────────────────────────────────────────────────────────────────────┐
│                        monolex_projects                                │
├────────────────────────────────────────────────────────────────────────┤
│ id          │ INTEGER  │ PRIMARY KEY         │ Unique project ID       │
│ name        │ TEXT     │ NOT NULL            │ Project name            │
│ description │ TEXT     │ nullable            │ Description             │
│ status      │ TEXT     │ DEFAULT 'active'    │ active/archived         │
│ is_active   │ BOOLEAN  │ DEFAULT 1           │ Quick filter flag       │
│ metadata    │ TEXT     │ nullable            │ JSON metadata           │
│ created_at  │ DATETIME │ DEFAULT NOW         │ Creation timestamp      │
│ archived_at │ DATETIME │ nullable            │ Archive timestamp       │
└────────────────────────────────────────────────────────────────────────┘
Project Status Flow: 
CREATE
   │
   │ INSERT (status='active', is_active=1)
   ▼
┌──────────────────────────────────────────┐
│  ACTIVE  │◀───── onit_db_restore_project()
└──────────────────────────────────────────┘
   │
   │ onit_db_archive_project()
   │ UPDATE status='archived', is_active=0
   ▼
┌──────────┐
│ ARCHIVED │
└──────────┘

Note: Soft delete pattern - no actual DELETE operations

​
niia-watcher.db - File Monitoring

File system event capture from the Rust watcher daemon.
Tables:
  • file_events - File change events with metadata
  • watch_paths - Directories being monitored
  • worker_status - Daemon health and statistics
  • daily_stats - Daily event counts by type
  • user_exclusions - Custom ignore patterns
┌────────────────────────────────────────────────────────────────────────┐
│                          file_events                                   │
├────────────────────────────────────────────────────────────────────────┤
│ id             │ INTEGER  │ PRIMARY KEY         │ Unique event ID      │
│ ts             │ INTEGER  │ NOT NULL            │ Timestamp (ms)       │
│ event_type     │ TEXT     │ NOT NULL            │ Create/Change/etc    │
│ abs_path       │ TEXT     │ NOT NULL            │ Full file path       │
│ rel_path       │ TEXT     │ nullable            │ Relative path        │
│ basename       │ TEXT     │ nullable            │ Filename only        │
│ file_size      │ INTEGER  │ nullable            │ Size in bytes        │
│ is_directory   │ BOOLEAN  │ DEFAULT 0           │ Is directory?        │
│ correlation_id │ TEXT     │ nullable            │ Event grouping       │
└────────────────────────────────────────────────────────────────────────┘
Write/Read Separation: 
╔══════════════════════════════════════════════════════════════════════╗
║                    WRITE (Daemon Only)                               ║
╠══════════════════════════════════════════════════════════════════════╣
║                                                                      ║
║  niia-watcher-daemon (Detached Process)                              ║
║       │                                                              ║
║       │ notify::RecommendedWatcher detects file change               ║
║       ▼                                                              ║
║  Direct rusqlite INSERT (NO IPC overhead)                            ║
║       │                                                              ║
║       ▼                                                              ║
║  file_events table                                                   ║
║                                                                      ║
╠══════════════════════════════════════════════════════════════════════╣
║                    READ (Tauri Backend)                              ║
╠══════════════════════════════════════════════════════════════════════╣
║                                                                      ║
║  Frontend invoke("query_niia_watcher")                               ║
║       │                                                              ║
║       ▼                                                              ║
║  lib.rs: SELECT FROM file_events WHERE ts > ?                        ║
║       │                                                              ║
║       ▼                                                              ║
║  JSON Response to frontend                                           ║
║                                                                      ║
╚══════════════════════════════════════════════════════════════════════╝
Event Types:
TypeDescription
CreateNew file or directory created
ChangeFile content modified
UnlinkFile or directory deleted
RenameFile renamed (includes old_path)

​
atomic-term.db - Terminal Activity

PTY output parsing results and activity logs.
Tables:
  • parsed_activities - Detected tool operations from PTY output
  • file_map - Fallback cache for path resolution
┌────────────────────────────────────────────────────────────────────────┐
│                        parsed_activities                               │
├────────────────────────────────────────────────────────────────────────┤
│ id              │ INTEGER  │ PRIMARY KEY         │ Unique activity ID  │
│ terminal_id     │ TEXT     │ NOT NULL            │ Terminal reference  │
│ operation       │ TEXT     │ nullable            │ Read/Write/Edit/Bash│
│ target          │ TEXT     │ nullable            │ Raw path from output│
│ path            │ TEXT     │ nullable            │ Auto-resolved path  │
│ rel_path        │ TEXT     │ nullable            │ Project-relative    │
│ ai_provider     │ TEXT     │ nullable            │ claude/gemini/etc   │
│ content         │ TEXT     │ nullable            │ Parsed content      │
│ raw_log         │ TEXT     │ NOT NULL            │ Full PTY output     │
│ timestamp       │ DATETIME │ DEFAULT NOW         │ Activity time       │
└────────────────────────────────────────────────────────────────────────┘
Data Flow: 
PTY Daemon
     │
     │ Unix Socket
     ▼
SessionActor (lib.rs)
     │
     │ PTY Data
     ▼
┌────────────────────────────┐
│ pty_log.rs                 │
│                            │
│ 1. VTE parsing             │
│ 2. Activity detection      │
│ 3. INSERT parsed_activities│
└─────────────┬──────────────┘
              │
              ▼
┌───────────────────────────┐
│ niia_auto_fix.rs          │
│                           │
│ 1. Search niia-watcher.db │
│ 2. UPDATE path, rel_path  │
└─────────────┬─────────────┘
              │
              ▼
emit("onit-activity-added")
              │
              ▼
Frontend UI (real-time update)
Cross-Database Path Resolution: The target field from PTY output often contains truncated paths. The system correlates with niia-watcher.db to resolve full paths: 
Time Expansion Strategy:
  1st attempt: ts +/- 1 minute
  2nd attempt: ts +/- 5 minutes
  3rd attempt: ts +/- 30 minutes
  Fallback: file_map cache

​
work-wiki-diff.db - Diff History

Git diff tracking for time-travel debugging and AI attribution.
Tables:
  • file_diffs - Individual file diff records
  • settings - Retention and size limits
  • ai_sessions - AI agent work sessions (v2)
  • commit_work_mapping - Links diffs to commits (v2)
┌────────────────────────────────────────────────────────────────────────┐
│                           file_diffs                                   │
├────────────────────────────────────────────────────────────────────────┤
│ id             │ INTEGER  │ PRIMARY KEY         │ Unique diff ID       │
│ ts             │ INTEGER  │ NOT NULL            │ Timestamp (seconds)  │
│ abs_path       │ TEXT     │ NOT NULL            │ Full file path       │
│ git_repo_path  │ TEXT     │ NOT NULL            │ Repository root      │
│ head_hash      │ TEXT     │ NOT NULL            │ HEAD at save time    │
│ diff_content   │ TEXT     │ nullable            │ Unified diff text    │
│ additions      │ INTEGER  │ DEFAULT 0           │ Lines added          │
│ deletions      │ INTEGER  │ DEFAULT 0           │ Lines deleted        │
│ committed_hash │ TEXT     │ nullable            │ Final commit hash    │
│ ai_session_id  │ TEXT     │ nullable            │ AI session ref       │
│ agent_name     │ TEXT     │ nullable            │ claude/gemini/etc    │
└────────────────────────────────────────────────────────────────────────┘
Capture Flow: 
File Save Event
     │
     │ diff_history_record(file_path)
     ▼
┌───────────────────────────┐
│ git2::Repository::discover│
│ Find nearest .git         │
└─────────────┬─────────────┘
              │
              ▼
┌───────────────────────────┐
│ repo.diff_tree_to_workdir │
│                           │
│ Calculate: HEAD vs working│
│ Extract: additions, etc   │
└─────────────┬─────────────┘
              │
              ▼
┌───────────────────────────┐
│ Deduplication Check       │
│                           │
│ Same path + hash + content│
│ -> Skip if duplicate      │
└─────────────┬─────────────┘
              │
              ▼
INSERT INTO file_diffs
Use Cases:
QueryPurpose
Time-travel”What did file.rs look like 2 hours ago?”
AI attribution”How much code did Claude write today?”
Pre-commit review”What changes will this commit include?”
Commit archaeology”What work led to commit xyz?”
Retention Policy:
  • Default: 30 days
  • Max file size: 1MB (larger files store metadata only)
  • Uncommitted work preserved regardless of age

​
file-paths.db - Path Index

Path normalization and usage frequency tracking. 
┌────────────────────────────────────────────────────────────────────────┐
│                          file_paths                                    │
├────────────────────────────────────────────────────────────────────────┤
│ abs_path      │ TEXT     │ UNIQUE NOT NULL     │ Full path (key)       │
│ rel_path      │ TEXT     │ nullable            │ Project-relative      │
│ project_root  │ TEXT     │ nullable            │ Project directory     │
│ file_type     │ TEXT     │ nullable            │ Extension category    │
│ access_count  │ INTEGER  │ DEFAULT 1           │ Usage frequency       │
└────────────────────────────────────────────────────────────────────────┘
Purpose: Provides fast path lookups and normalizes paths across databases for consistent correlation.

​
Data Flow Overview

╔══════════════════════════════════════════════════════════════════════╗
║                         DATA FLOW                                    ║
╠══════════════════════════════════════════════════════════════════════╣
║                                                                      ║
║   PTY Output                           File System Event             ║
║       │                                      │                       ║
║       ▼                                      ▼                       ║
║   pty_log.rs                          niia-watcher-daemon            ║
║   (VTE parsing)                       (notify crate)                 ║
║       │                                      │                       ║
║       ▼                                      ▼                       ║
║   atomic-term.db                      niia-watcher.db                ║
║   (parsed_activities)                 (file_events)                  ║
║       │                                      │                       ║
║       └──────────────────┬───────────────────┘                       ║
║                          ▼                                           ║
║                     file-paths.db                                    ║
║                     (path normalization)                             ║
║                          │                                           ║
║                          ▼                                           ║
║                      onit.db                                         ║
║                  (activity aggregation)                              ║
║                          │                                           ║
║                          ▼                                           ║
║                     session.db                                       ║
║                 (state persistence)                                  ║
║                                                                      ║
╚══════════════════════════════════════════════════════════════════════╝

​
Write Patterns

DatabaseWrite FrequencyPatternOptimization
session.dbLowSingle row updatesStandard
onit.dbMediumBatch insertsWAL mode
niia-watcher.dbHigh (bursts)Direct writeWAL + no IPC
atomic-term.dbHighDBWriter threadWAL + dedicated thread
work-wiki-diff.dbPer saveDeduplicationContent hash check
file-paths.dbLowInsert/updateStandard

​
DBWriter Thread Pattern (atomic-term.db)

LogWorker (Tokio task)
     │
     │ queue_db_write(req)
     ▼
std::sync::mpsc::send() ─── NON-BLOCKING
     │
     ▼
DBWriter (Dedicated std::thread)
┌───────────────────────────────────┐
│ - WAL mode enabled                │
│ - Hash-based deduplication        │
│ - Auto-fix path after INSERT      │
│ - Emit "onit-activity-added"      │
└───────────────────────────────────┘

Why std::thread (not Tokio)?
- SQLite blocking doesn't starve async workers
- Keyboard input remains responsive
- No terminal lag during heavy PTY output

​
Cross-Database Relationships

╔══════════════════════════════════════════════════════════════════════╗
║                    CROSS-DATABASE CORRELATION                        ║
╠══════════════════════════════════════════════════════════════════════╣
║                                                                      ║
║  atomic-term.db ◀─────── file_path ───────▶ niia-watcher.db          ║
║  (truncated path)                           (full path + ts)         ║
║                                                                      ║
║  work-wiki-diff.db ◀──── git_repo_path ────▶ onit.db                 ║
║  (diff history)                              (project folders)       ║
║                                                                      ║
║  session.db ◀─────────── terminal_id ──────▶ atomic-term.db          ║
║  (session state)                             (activity logs)         ║
║                                                                      ║
║  file-paths.db ◀──────── abs_path ─────────▶ all databases           ║
║  (normalized paths)                          (path references)       ║
║                                                                      ║
╚══════════════════════════════════════════════════════════════════════╝

​
SMPC/OFAC Principles

SMPC Applied

  • Each database has ONE clear purpose
  • No complex JOIN queries across DBs
  • WAL mode for concurrent access
  • Simple key-value patterns where possible

OFAC Applied

  • Time-based event correlation
  • Graceful degradation on DB unavailability
  • file-paths.db normalizes chaos
  • session.db enables crash recovery

​
Common Queries

-- Active terminals
SELECT * FROM terminal_heartbeats
WHERE is_alive = 1
ORDER BY last_heartbeat DESC;

-- Project activity stats
SELECT p.name, COUNT(*) as activities
FROM parsed_activities a
JOIN monolex_projects p ON a.project_id = p.id
GROUP BY p.id;

-- Recent file events
SELECT event_type, abs_path, datetime(ts/1000, 'unixepoch')
FROM file_events
ORDER BY ts DESC LIMIT 50;

-- AI attribution summary
SELECT agent_name, SUM(additions), SUM(deletions)
FROM file_diffs
WHERE ai_session_id IS NOT NULL
GROUP BY agent_name;