turbovault-sql

SQL query engine for Obsidian vault frontmatter, powered by GlueSQL.

Part of the TurboVault workspace.

Overview

Builds in-memory tables from vault data and exposes them for arbitrary SQL queries via MCP tools. Three tables are auto-populated on each session:

Example Queries

-- Find all active tasks
SELECT path, status, priority FROM files
WHERE type = 'task' AND status = 'active'
ORDER BY priority DESC;

-- Tag frequency report
SELECT tag, COUNT(*) AS cnt FROM tags
GROUP BY tag ORDER BY cnt DESC LIMIT 10;

-- Notes tagged 'work' that have broken outgoing links
SELECT DISTINCT t.path FROM tags t
JOIN links l ON t.path = l.source
WHERE t.tag = 'work' AND l.is_valid = FALSE;

-- Cross-reference: files with most outgoing links
SELECT source, COUNT(*) AS link_count FROM links
GROUP BY source ORDER BY link_count DESC LIMIT 10;

Usage

This crate is feature-gated in TurboVault. Enable with:

cargo build --features sql

One-shot query

use turbovault_sql::FrontmatterSqlEngine;

let engine = FrontmatterSqlEngine::new(vault_manager);
let result = engine.query("SELECT path, status FROM files WHERE status = 'active'").await?;

Session (build tables once, query many times)

let engine = FrontmatterSqlEngine::new(vault_manager);
let mut session = engine.session().await?;

let tasks = session.query("SELECT path FROM files WHERE type = 'task'").await?;
let tags = session.query("SELECT tag, COUNT(*) as cnt FROM tags GROUP BY tag").await?;

Supported SQL

GlueSQL supports a practical subset of SQL:

• SELECT, WHERE, ORDER BY, LIMIT
• JOIN (INNER, LEFT, RIGHT, FULL, CROSS)
• GROUP BY, HAVING
• Aggregates: COUNT, SUM, AVG, MIN, MAX, STDEV, VARIANCE
• Subqueries
• CAST for type coercion (e.g., CAST('2024-01-01' AS DATE))
• CREATE FUNCTION for user-defined helpers
• CREATE INDEX for repeated queries
• String functions: CONCAT, UPPER, LOWER, SUBSTRING
• Date functions: FORMAT, date arithmetic

Not supported: Window functions, CTEs (WITH clause).

Architecture

turbovault-sql/
  src/
    lib.rs       -- Public API, module docs
    engine.rs    -- FrontmatterSqlEngine, SqlSession, table builders
    convert.rs   -- GlueSQL Value <-> serde_json conversion

License

MIT