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)
     v
+-----------+
|   ALIVE   |<----- Heartbeat every 10 seconds
+-----------+       UPDATE last_heartbeat
     |
     +--------------------+
     |                    |
     | Tab closed         | No heartbeat for 30s
     v                    v
+-----------+      +-----------+
|   ENDED   |      |   DEAD    |
+-----------+      +-----------+
                         |
                         | App restart + socket exists
                         v
                   +-----------+
                   | 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)
   v
+--------+
| ACTIVE |<----- onit_db_restore_project()
+--------+
   |
   | onit_db_archive_project()
   | UPDATE status='archived', is_active=0
   v
+----------+
| 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               |
|       v                                                              |
|  Direct rusqlite INSERT (NO IPC overhead)                           |
|       |                                                              |
|       v                                                              |
|  file_events table                                                  |
|                                                                      |
+=====================================================================+
|                    READ (Tauri Backend)                              |
+---------------------------------------------------------------------+
|                                                                      |
|  Frontend invoke("query_niia_watcher")                              |
|       |                                                              |
|       v                                                              |
|  lib.rs: SELECT FROM file_events WHERE ts > ?                       |
|       |                                                              |
|       v                                                              |
|  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
     v
SessionActor (lib.rs)
     |
     | PTY Data
     v
+---------------------------+
| pty_log.rs                |
|                           |
| 1. VTE parsing            |
| 2. Activity detection     |
| 3. INSERT parsed_activities|
+-------------+-------------+
              |
              v
+---------------------------+
| niia_auto_fix.rs          |
|                           |
| 1. Search niia-watcher.db |
| 2. UPDATE path, rel_path  |
+-------------+-------------+
              |
              v
emit("onit-activity-added")
              |
              v
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)
     v
+---------------------------+
| git2::Repository::discover|
| Find nearest .git         |
+-------------+-------------+
              |
              v
+---------------------------+
| repo.diff_tree_to_workdir |
|                           |
| Calculate: HEAD vs working|
| Extract: additions, etc   |
+-------------+-------------+
              |
              v
+---------------------------+
| Deduplication Check       |
|                           |
| Same path + hash + content|
| -> Skip if duplicate      |
+-------------+-------------+
              |
              v
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            |
|       |                                      |                       |
|       v                                      v                       |
|   pty_log.rs                          niia-watcher-daemon           |
|   (VTE parsing)                       (notify crate)                |
|       |                                      |                       |
|       v                                      v                       |
|   atomic-term.db                      niia-watcher.db               |
|   (parsed_activities)                 (file_events)                 |
|       |                                      |                       |
|       +------------------+-------------------+                       |
|                          v                                           |
|                     file-paths.db                                   |
|                     (path normalization)                            |
|                          |                                           |
|                          v                                           |
|                      onit.db                                        |
|                  (activity aggregation)                             |
|                          |                                           |
|                          v                                           |
|                     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)
     v
std::sync::mpsc::send() --- NON-BLOCKING
     |
     v
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;