SQLite Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• src/db/schema.ts
• skill-packages/cxs/references/json-schema.md
• AGENTS.md
• README.md
• src/indexer.ts
• skill-packages/cxs/references/cli-surface.md

The SQLite database in cxs serves as the central storage and retrieval engine for local Codex session logs. It indexes raw JSONL session files to enable efficient full-text search (FTS), metadata filtering, and progressive retrieval. The database is typically stored at ~/.local/state/cxs/index.sqlite and is updated exclusively via the sync command.

Sources: README.md:92-94, AGENTS.md:12-14

Core Data Architecture

The schema is designed to support a multi-stage retrieval flow: initial recall via Full-Text Search, followed by heuristic re-ranking and progressive reading of message ranges or pages. It consists of standard relational tables for metadata and virtual tables for advanced search capabilities.

The diagram above illustrates the relationships between core tables and their corresponding Full-Text Search (FTS) virtual tables.
Sources: src/db/schema.ts:9-85, skill-packages/cxs/references/json-schema.md:104-128

Database Tables

Sessions Table

The sessions table stores top-level metadata and derived summaries for each indexed Codex session.

Field	Type	Description
id	INTEGER	Primary key, autoincrement.
session_uuid	TEXT	Unique identifier for the session.
file_path	TEXT	Unique absolute path to the source JSONL file.
source_root	TEXT	Root directory where the file was discovered.
title	TEXT	The title of the session.
summary_text	TEXT	Long-form summary of the conversation.
compact_text	TEXT	Condensed summary for high-density recall.
reasoning_summary_text	TEXT	Summary of reasoning/thought process steps.
cwd	TEXT	The current working directory associated with the session.
started_at	TEXT	ISO8601 timestamp of session start.
ended_at	TEXT	ISO8601 timestamp of session end.
message_count	INTEGER	Total number of messages in the session.
raw_file_mtime	INTEGER	Modification time of the source file (for cache invalidation).

Sources: src/db/schema.ts:13-35, skill-packages/cxs/references/json-schema.md:104-116

Messages Table

The messages table contains the individual conversational turns within a session.

Field	Type	Description
id	INTEGER	Primary key, autoincrement.
session_id	INTEGER	Foreign key referencing sessions.id.
session_uuid	TEXT	Redundant UUID for quick lookups.
seq	INTEGER	The sequence number of the message within the session.
role	TEXT	Role of the speaker (e.g., "user", "assistant").
content_text	TEXT	The raw text content of the message.
timestamp	TEXT	ISO8601 timestamp of the message.

Sources: src/db/schema.ts:46-59, skill-packages/cxs/references/json-schema.md:118-126

Coverage Table

The coverage table tracks which portions of the local file system have been indexed and whether that data is fresh or stale compared to the source files.

Field	Type	Description
selector_key	TEXT	Unique key derived from the selector (e.g., all, cwd, date_range).
selector_json	TEXT	The full JSON representation of the selector.
source_fingerprint	TEXT	A hash of the source files' metadata to detect changes.
source_file_count	INTEGER	Number of files expected in this coverage range.
indexed_session_count	INTEGER	Number of sessions actually written to the DB.
index_version	TEXT	The version of the index schema used.

Sources: src/db/schema.ts:74-88, skill-packages/cxs/references/json-schema.md:154-162

Full-Text Search (FTS) Implementation

cxs utilizes SQLite FTS5 virtual tables to provide high-performance keyword searching across session metadata and message contents.

Message-Level Search (messages_fts)

Indexes the content_text of all messages. It uses the unicode61 tokenizer with diacritics removal.
Sources: src/db/schema.ts:63-71

Session-Level Search (sessions_fts)

Indexes session-level textual fields. During retrieval, these fields are weighted differently to prioritize relevant hits:

• Title: 8.0
• Compact Text: 4.0
• Summary Text: 3.0
• Reasoning Summary: 1.2

Sources: src/db/schema.ts:107-115, AGENTS.md:28-29

The flow above describes how search queries interact with different FTS tables before being re-ranked for the user.
Sources: AGENTS.md:21-29, skill-packages/cxs/references/cli-surface.md:65-80

Index Maintenance and Synchronization

Synchronization is a strict operation that reconciles the SQLite state with the physical JSONL files on disk based on a Selector.

1. Inventory Collection: The system scans the rootDir to build a SourceInventory.
2. Fingerprinting: A source_fingerprint is generated based on file paths, sizes, and modification times.
3. Reconciliation:
   • Files that are unreadable or invalid JSONL result in sync errors.
   • If a source file is removed, its corresponding rows in sessions and messages are deleted.
   • If bestEffort is disabled, any single file error causes the entire transaction to fail, and no coverage record is written.
4. Coverage Update: Upon successful sync, a record is added to the coverage table, marking that specific Selector as "fresh."

Sources: src/indexer.ts:47-104, README.md:104-114, skill-packages/cxs/references/cli-surface.md:46-63

Summary

The cxs SQLite database schema is optimized for local-first, progressive retrieval of developer conversation history. By combining relational metadata with weighted full-text search and a robust coverage tracking system, it ensures that search results are both fast and accurately synchronized with the underlying Codex session logs.