database

Database Rules for Code-Index-MCP

Overview

This file defines database design patterns, query optimization strategies, and data management rules for the Code-Index-MCP project.

Storage Architecture

Local-First Design

• Primary Storage: SQLite with FTS5 for full-text search
• Index Format: Structured JSON with normalized tables
• File-Based: Each project has its own database file
• Portability: Database files can be copied/moved

Schema Design

-- Core tables structure
CREATE TABLE files (
    id INTEGER PRIMARY KEY,
    path TEXT UNIQUE NOT NULL,
    content TEXT,
    language TEXT,
    last_modified INTEGER,
    hash TEXT
);

CREATE TABLE symbols (
    id INTEGER PRIMARY KEY,
    file_id INTEGER REFERENCES files(id),
    name TEXT NOT NULL,
    type TEXT, -- function, class, variable, etc.
    line_start INTEGER,
    line_end INTEGER,
    column_start INTEGER,
    column_end INTEGER,
    parent_id INTEGER REFERENCES symbols(id)
);

CREATE TABLE imports (
    id INTEGER PRIMARY KEY,
    file_id INTEGER REFERENCES files(id),
    module_name TEXT,
    alias TEXT,
    line_number INTEGER
);

-- Full-text search tables
CREATE VIRTUAL TABLE files_fts USING fts5(
    path, content, tokenize='porter unicode61'
);

CREATE VIRTUAL TABLE symbols_fts USING fts5(
    name, type, tokenize='porter unicode61'
);

Query Optimization

Indexing Strategy

-- Performance indexes
CREATE INDEX idx_symbols_file ON symbols(file_id);
CREATE INDEX idx_symbols_name ON symbols(name);
CREATE INDEX idx_symbols_type ON symbols(type);
CREATE INDEX idx_imports_file ON imports(file_id);
CREATE INDEX idx_files_language ON files(language);

Query Patterns

# Efficient symbol lookup
def find_symbol(name: str, file_path: str = None):
    query = """
    SELECT s.*, f.path 
    FROM symbols s
    JOIN files f ON s.file_id = f.id
    WHERE s.name = ?
    """
    params = [name]
    
    if file_path:
        query += " AND f.path = ?"
        params.append(file_path)
    
    return db.execute(query, params)

# Full-text search with ranking
def search_code(query: str):
    return db.execute("""
    SELECT path, snippet(files_fts, 1, '<b>', '</b>', '...', 32) as snippet,
           rank
    FROM files_fts
    WHERE files_fts MATCH ?
    ORDER BY rank
    LIMIT 50
    """, [query])