Relational SQLite Storage & Codebase Query Pipelines

Codebase intelligence tools need to serve queries in milliseconds. Storing indexes as raw JSON files or keeping heavy memory-pointer graphs in JVM heaps is slow and resource-heavy. CostAffective uses a local SQLite database built directly inside your repository directory.

The Schema Structure

By mapping declarations and usages into structured tables, CostAffective converts repository exploration into SQL query execution. The database implements three core schemas:

CREATE TABLE symbols (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    kind TEXT NOT NULL,
    filepath TEXT NOT NULL,
    start_line INTEGER NOT NULL,
    end_line INTEGER NOT NULL,
    signature TEXT
);

CREATE TABLE references (
    symbol_id TEXT,
    filepath TEXT NOT NULL,
    line_number INTEGER NOT NULL,
    line_content TEXT NOT NULL,
    FOREIGN KEY(symbol_id) REFERENCES symbols(id)
);

CREATE TABLE calls (
    caller_id TEXT,
    callee_name TEXT NOT NULL,
    filepath TEXT NOT NULL,
    line_number INTEGER NOT NULL
);

Fast Query Pipelines

When you ask a coding agent to trace a function's usage, it invokes the find_references tool. The tool runs a single indexed query: SELECT * FROM references WHERE symbol_id = ?. This query returns accurate result sets in less than 2 milliseconds, bypassing slow workspace searches.

Performance Optimization

• Local Cache Isolation: Indexes are kept inside .mycli-fts directories in your project root, ensuring privacy.
• Fast Indexes: B-Tree indexing on columns like name and symbol_id guarantees fast lookups even in large codebases.
• Sub-Millisecond Execution: SQLite processes relational lookups without starting external network servers or opening sockets.