SQLite Schema

Why SQLite?

The typical modern AI stack relies on standalone vector databases (like Pinecone, Qdrant, or Postgres with pgvector). ContextOS intentionally rejects this model for a local, embedded SQLite database.

1. Independent Project Isolation: A global vector DB suffers from cross-contamination. If you search for "UserAuth" on a machine with 5 projects, you will retrieve classes from all 5 projects. SQLite allows us to place a .contextos file directly in the repository, ensuring zero leakage.

2. Graph Traversal Speed: ContextOS relies heavily on resolving file dependencies. Relational joins in SQLite are orders of magnitude faster for local recursive CTE queries than making network hops to a graph database.

Schema v5 Tables

1. files

Tracks every file in the repository that has been successfully parsed, including dirty-checking mechanisms.

CREATE TABLE IF NOT EXISTS files (
  path TEXT PRIMARY KEY,
  layer TEXT NOT NULL,
  workspace_name TEXT,
  hash TEXT NOT NULL,
  last_indexed INTEGER NOT NULL,
  importance INTEGER DEFAULT 5,
  chunk_count INTEGER DEFAULT 0
);

2. chunks

A chunk is a specific AST node (e.g., a function, a class, an interface) extracted from a file. Notice how we store deep syntactic metadata (file_stem, parent_symbol, symbol_kind) to allow query-aware filtering.

CREATE TABLE IF NOT EXISTS chunks (
  id TEXT PRIMARY KEY,
  source_file TEXT NOT NULL,
  layer TEXT NOT NULL,
  workspace_name TEXT,
  section_title TEXT,
  section_depth INTEGER NOT NULL,
  content TEXT NOT NULL,
  summary TEXT,
  keywords TEXT,
  hash TEXT NOT NULL,
  importance INTEGER DEFAULT 5,
  token_count INTEGER NOT NULL,
  file_type TEXT,
  language TEXT,
  symbol_name TEXT,
  symbol_kind TEXT,
  parent_symbol TEXT,
  start_line INTEGER,
  end_line INTEGER,
  file_stem TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY(source_file) REFERENCES files(path) ON DELETE CASCADE
);

3. relationships

The graph edges representing imports and dependencies. Notice the weight column which powers the decay algorithm in the Graph Expander.

CREATE TABLE IF NOT EXISTS relationships (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  source TEXT NOT NULL,
  target TEXT NOT NULL,
  relationship_type TEXT NOT NULL,
  weight REAL DEFAULT 1.0,
  source_chunk_id TEXT NOT NULL,
  layer TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY(source_chunk_id) REFERENCES chunks(id) ON DELETE CASCADE,
  UNIQUE(source, target, relationship_type, source_chunk_id)
);

4. fts_chunks (BM25 Engine)

The virtual table powering the BM25 text search. We explicitly utilize the porter unicode61 tokenizer and a prefix index of '2 3' to guarantee sub-millisecond keyword matches across large codebases.

CREATE VIRTUAL TABLE chunks_fts USING fts5(
  content,
  summary,
  keywords,
  section_title,
  content=chunks,
  content_rowid=rowid,
  tokenize='porter unicode61',
  prefix='2 3'
);

5. Advanced Systems

Schema v5 also introduces several advanced tables for opt-in features:

• chunk_embeddings: Stores dense vectors (BLOBs) for hybrid RRF fusion retrieval.
• knowledge_facts: Stores user-provided architectural truths with its own FTS5 index.
• session_events: Stores cross-session memory for long-running workflows.
• feedback_signals: Tracks explicit upvotes/downvotes to adjust score_adjustment multipliers during retrieval ranking.