Sqldown

SQLDown Skill

Core Concept

Load with sqldown. Query with sqlite3. Dump when needed.

This skill handles bidirectional markdown ↔ SQLite conversion with intelligent column limit protection. For queries, use sqlite3 directly - it's already perfect.

When to Use

• Need to query across many markdown files efficiently
• Want SQL-powered filtering, aggregation, sorting
• Working with structured markdown (YAML frontmatter + H2 sections)
• Need context-efficient progressive disclosure

Commands

Import: Create or Update Tables

Use sqldown load - loads markdown files into SQLite database.

Command:

sqldown load PATH [OPTIONS]

What it does:

• Scans markdown files recursively
• Parses YAML frontmatter → database columns
• Extracts H2 sections → section_* columns
• Creates schema dynamically based on discovered fields
• Upserts into SQLite (idempotent - safe to run multiple times)
• Respects .gitignore patterns automatically

Options:

• -d, --db PATH - Database file (default: sqldown.db)
• -t, --table NAME - Table name (default: docs)
• -p, --pattern GLOB - File pattern (default: **/*.md)
• --max-columns N - Maximum allowed columns (default: 1800, SQLite limit: 2000)
• --top-sections N - Extract only top N most common sections (default: 20, 0=all)
• -w, --watch - Watch for file changes and auto-update
• -v, --verbose - Show detailed progress

Examples:

# Load markdown files into SQLite
sqldown load ~/tasks

# Specify database and table
sqldown load ~/tasks -d cache.db -t tasks

# Load notes
sqldown load ~/notes -d cache.db -t notes

# Load with specific pattern
sqldown load ~/.claude/skills -d cache.db -t skills -p "*/SKILL.md"

# Watch mode: auto-update on file changes
sqldown load ~/tasks -w

# Column limit protection - extract only top 10 sections
sqldown load ~/tasks --top-sections 10

# Extract all sections (may hit 2000 column limit with diverse docs)
sqldown load ~/tasks --top-sections 0

# Check column breakdown with verbose output
sqldown load ~/tasks -v
# Output shows: Base columns: 7, Frontmatter: 89, Sections: 20, Total: 116

Query: Use sqlite3 Directly

For ALL queries, use sqlite3 command directly:

# List available tables
sqlite3 cache.db ".tables"

# Show table schema
sqlite3 cache.db ".schema tasks"

# Query
sqlite3 cache.db "SELECT title, status FROM tasks WHERE status='active'"

# Aggregate
sqlite3 cache.db "SELECT status, COUNT(*) FROM tasks GROUP BY status"

# Complex queries
sqlite3 cache.db "
  SELECT project, COUNT(*) as count,
         SUM(CASE WHEN status='active' THEN 1 ELSE 0 END) as active
  FROM tasks
  GROUP BY project
  ORDER BY count DESC
"

Dynamic Schema

Core fields (always present):

_id TEXT PRIMARY KEY        -- SHA1 of file path
_path TEXT                   -- Relative path
_sections TEXT               -- JSON array of H2 names
title TEXT                   -- H1 heading
body TEXT                    -- Full content
lead TEXT                    -- First paragraph
file_modified FLOAT          -- Timestamp

Dynamic fields (auto-generated):

• YAML frontmatter: status, project, priority, tags, etc.
• H2 sections: section_objective, section_implementation_plan, etc.

Example: 87 tasks with varied structure → 181 columns generated automatically.

Column Limit Protection

SQLite has a hard limit of 2000 columns per table. The --top-sections flag prevents hitting this limit:

How it works:

1. Analyzes all documents to count section frequency
2. Extracts only the N most common sections as columns
3. All other sections remain in the body field

Real-world example:

• 5,225 tasks with diverse sections = 6,694 unique columns (exceeds limit!)
• With --top-sections 20 (default) = 116 columns ✅

Top extracted sections (from Mike's tasks): overview, usage, objective, notes, next_steps, troubleshooting, installation, configuration, requirements, testing, etc.

When to adjust:

• --top-sections 10 - Fewer columns for very diverse collections
• --top-sections 50 - More columns if you need more queryable sections
• --top-sections 0 - Extract all (only for homogeneous collections)

What about rare sections?

• Still in body field - nothing is lost
• Use FTS5 or LIKE '%text%' to search across all content
• Only the top N become directly queryable columns

Common Query Patterns

# Find active tasks
sqlite3 cache.db "SELECT title FROM tasks WHERE status='active'"

# Recent updates
sqlite3 cache.db "SELECT title, updated FROM tasks ORDER BY updated DESC LIMIT 10"

# Count by status
sqlite3 cache.db "SELECT status, COUNT(*) FROM tasks GROUP BY status"

# Search content
sqlite3 cache.db "SELECT title, _path FROM tasks WHERE body LIKE '%SQLite%'"

# High priority items
sqlite3 cache.db "SELECT title FROM tasks WHERE priority='high' AND status!='completed'"

# Project summary
sqlite3 cache.db "
  SELECT project,
         COUNT(*) as total,
         SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END) as done
  FROM tasks
  GROUP BY project
"

# Find related documents
sqlite3 cache.db "
  SELECT title FROM tasks
  WHERE section_related_tasks LIKE '%AG-22%'
"

Progressive Disclosure Pattern

1. Query metadata first (fast, context-efficient):

   sqlite3 cache.db "SELECT title, _path, status FROM tasks WHERE priority='high'"
   

2. Read full markdown only when needed (slower, more context):

   # After finding relevant tasks, read the actual files
   cat ~/tasks/AG-22_feat_add-configuration/README.md
   

This keeps context usage low while still finding what you need.

Multiple Tables Strategy

Keep different document types in separate tables:

# Load each type
sqldown load ~/tasks -d cache.db -t tasks
sqldown load ~/notes -d cache.db -t notes
sqldown load ~/.claude/skills -d cache.db -t skills

# Query across them
sqlite3 ~/cache.db "
  SELECT 'task' as type, title FROM tasks WHERE body LIKE '%cache%'
  UNION ALL
  SELECT 'note' as type, title FROM notes WHERE body LIKE '%cache%'
"

Refresh Strategy

One-time load (manual refresh):

Load is idempotent - run after file changes:

sqldown load ~/tasks -d cache.db -t tasks -v

Watch mode (automatic refresh):

Use --watch to automatically update when files change:

# Starts watching - runs until Ctrl-C
sqldown load ~/tasks -d cache.db -t tasks -w

# Output shows real-time updates:
# [2025-01-15 10:23:45] Updated: AG-22_feat_add-configuration/README.md
# [2025-01-15 10:24:12] Added: AG-31_feat_new-feature/README.md
# [2025-01-15 10:25:03] Deleted: AG-19_feat_old-feature/README.md

Watch mode is ideal for development workflows where you want the cache to stay in sync as you edit files.

Why sqlite3 Instead of Python Wrappers?

sqlite3 gives you:

• Full SQL power (no wrapper limitations)
• Standard tool (no custom command syntax to learn)
• Better output formats (.mode csv, .mode json, .mode column)
• Interactive shell with history and tab completion
• Better performance (no Python startup overhead)

sqldown load gives you:

• Markdown + YAML frontmatter parsing
• Dynamic schema generation
• H2 section extraction
• Automatic .gitignore filtering
• Watch mode for auto-updates

This division of responsibility keeps tools simple and powerful.

Workflow Guidelines

1. Start with schema inspection:

   sqlite3 cache.db ".schema tasks"
   

   Shows what columns are available (critical for dynamic schemas).

2. Use simple queries first:

   sqlite3 cache.db "SELECT title, status FROM tasks LIMIT 5"
   

3. Build up complexity:

   sqlite3 cache.db "SELECT status, COUNT(*) FROM tasks GROUP BY status"
   

4. Read full files last: Only after identifying relevant documents via SQL.

5. Trust .gitignore filtering: By default, sqldown load respects .gitignore automatically.

Requirements

Prerequisites:

• Python 3.10+ (includes sqlite3 module - standard library)
• sqlite3 CLI (built-in on macOS 10.4+ and most Linux distributions)

Installation:

# Install from PyPI
pip install sqldown

# Or use uv for faster installation
uv pip install sqldown

Limitations

• Best for <100K documents
• SQLite column limit: 2000 columns max (sqldown detects and reports)
• No built-in full-text search (though SQLite FTS5 could be added)

Additional Commands

Dump: Export Back to Markdown

sqldown dump -d DATABASE -o OUTPUT_DIR [OPTIONS]

What it does:

• Exports database rows back to markdown files
• Reconstructs original markdown structure with frontmatter
• Preserves file paths from original import
• Skips unchanged files (smart change detection)
• Supports SQL filtering to export subsets

Options:

• -d, --db PATH - Database file (required)
• -t, --table NAME - Table name (default: docs)
• -o, --output PATH - Output directory (required)
• -f, --filter WHERE - SQL WHERE clause to filter rows
• --force - Always write files, even if unchanged
• --dry-run - Preview what would be exported without writing
• -v, --verbose - Show detailed progress

Examples:

# Export all documents
sqldown dump -d cache.db -o ~/restored

# Export only active tasks
sqldown dump -d cache.db -t tasks -o ~/active --filter "status='active'"

# Preview export without writing files
sqldown dump -d cache.db -o ~/export --dry-run

Info: Database Statistics

sqldown info [OPTIONS]

What it does:

• Shows database statistics and table information
• Lists all tables with document counts
• Displays column breakdown (frontmatter vs sections)
• Provides schema details for specific tables

Options:

• -d, --db PATH - Database file (default: sqldown.db if exists)
• -t, --table NAME - Show detailed info for specific table

Examples:

# Show database overview
sqldown info

# Show info for specific database
sqldown info -d cache.db

# Show detailed table information
sqldown info -d cache.db -t tasks

Technical Details

Automatic .gitignore Support:

• Reads .gitignore from root directory by default
• Uses pathspec library for gitignore pattern matching
• Filters files before import to avoid unwanted content

Column Limit Protection:

• Uses --top-sections to extract only the N most common H2 sections
• Prevents hitting SQLite's 2000 column limit
• Rare sections remain in body field - nothing is lost

Related Files

• bin/sqldown - Main CLI tool
• README.md - Human-facing documentation
• SPECIFICATION.md - Technical specification