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:

Table	Schema	Source
`files`	Schemaless (path + all frontmatter keys)	Vault markdown files
`tags`	`(path TEXT, tag TEXT)`	Unnested from frontmatter tag arrays
`links`	`(source TEXT, target TEXT, link_type TEXT, is_valid BOOLEAN)`	Vault link graph

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

turbovault-sql 1.4.1