Storage & Search

Cortexa uses SQLite (as SQLCipher, so the whole database file is encrypted at rest) as its embedded database, with AES-256-GCM encrypted blob files on disk, FTS5 for full-text keyword search, and PRAGMA-based schema migrations. No external database server is required. See Encryption for the key design and the one-time migration of older plaintext vaults.

Data Directory Layout

All data lives under the platform-specific app data directory:

OS	Path
Windows	%APPDATA%\com.cortexa.app\
macOS	~/Library/Application Support/com.cortexa.app/
Linux	~/.local/share/com.cortexa.app/

com.cortexa.app/
  cortexa.db          # SQLCipher-encrypted SQLite database (items, jobs, theses, links, etc.)
  salt.bin            # 16-byte KDF salt
  dbkey.bin           # the DB key, AES-256-GCM wrapped under the vault key (back up with salt.bin)
  settings.json       # Non-secret configuration
  blobs/
    <uuid>.bin        # AES-256-GCM encrypted item content
  audios/
    <uuid>.bin        # AES-256-GCM encrypted briefing audio

Building with SQLCipher

src-tauri enables rusqlite's bundled-sqlcipher feature. The crypto provider differs by target:

Target	SQLCipher crypto	Build needs
macOS (arm64, and x86_64 cross-compiled on arm64)	CommonCrypto (system framework)	nothing extra
Windows MSVC (x64, ARM64)	vendored OpenSSL (bundled-sqlcipher-vendored-openssl), statically linked	a Windows-native perl (Strawberry Perl). Git for Windows' MSYS perl fails OpenSSL's Configure. Point OPENSSL_SRC_PERL at it if another perl comes first on PATH. NASM is optional (without it OpenSSL builds with no-asm)
Linux	vendored OpenSSL	perl and make (present on stock distros and GitHub's Ubuntu runners)

The first build compiles OpenSSL (several minutes); later builds reuse it. The Hive relay (hive/) depends on plain rusqlite with bundled; it is built on its own (-p cortexa-hive) for release, so it stays plain SQLite. In a cargo test --workspace run Cargo unifies features and the relay links the SQLCipher build too, which behaves exactly like SQLite for a database that is never keyed.

SQLite Schema

The database is opened with rusqlite and all tables are created in Store::open(). The schema is organized into core tables, auxiliary tables, and virtual tables.

Core Tables

items

The primary table for all captured thoughts.

Column	Type	Description
id	TEXT PRIMARY KEY	UUID v4
kind	TEXT NOT NULL	Item type (currently always "text")
blob_path	TEXT NOT NULL	Absolute path to the encrypted blob file
preview	TEXT NOT NULL	First ~200 characters of content (plaintext, for list display)
created_at	TEXT NOT NULL	RFC 3339 timestamp
updated_at	TEXT NOT NULL	RFC 3339 timestamp
summary	TEXT	LLM-generated summary (plaintext)
embedding	BLOB	Raw float vector (unencrypted, see Encryption)
deleted	INTEGER DEFAULT 0	Soft-delete flag
pinned	INTEGER DEFAULT 0	Pin flag for priority items
archived_at	TEXT	Timestamp when archived (NULL if active)
trashed_at	TEXT	Timestamp when trashed (NULL if active)
project_id	TEXT	Associated project ID
confidence_score	REAL	Numerical confidence (0.0–1.0), decays over time
last_validated_at	TEXT	When confidence was last affirmed (user interaction resets decay)

Indexes: items_created_at, items_updated_at, items_archived_at, items_trashed_at, items_pinned, items_project, items_confidence.

theses

Stores the Deep Think pipeline state and output for each item.

Column	Type	Description
item_id	TEXT PRIMARY KEY	References items.id
status	TEXT NOT NULL	One of: pending, expanding, critiquing, synthesizing, done, failed
expansion	BLOB	Encrypted expansion output
counterpoints	BLOB	Encrypted counterpoints output
thesis	BLOB	Encrypted structured JSON brief
error	TEXT	Error message if failed
created_at	TEXT NOT NULL	RFC 3339 timestamp
updated_at	TEXT NOT NULL	RFC 3339 timestamp

links

The knowledge graph edges connecting related items.

Column	Type	Description
source_id	TEXT NOT NULL	Origin item ID
target_id	TEXT NOT NULL	Target item ID
weight	REAL NOT NULL	Similarity score (0.0 - 1.0)
kind	TEXT NOT NULL	Link type (e.g., "auto" for HNSW-generated)
created_at	TEXT NOT NULL	RFC 3339 timestamp

Primary key: (source_id, target_id, kind). Indexes: links_target, links_kind.

briefings

Audio briefing metadata and scripts.

Column	Type	Description
id	TEXT PRIMARY KEY	UUID v4
scope	TEXT NOT NULL	recent or item
source_ids	TEXT NOT NULL	JSON array of item IDs included
status	TEXT NOT NULL	pending, scripting, voicing, done, failed
script	BLOB	Encrypted briefing script text
audio_path	TEXT	Path to encrypted audio file
audio_format	TEXT	e.g., "mp3"
voice	TEXT	TTS voice used
error	TEXT	Error message if failed
created_at	TEXT NOT NULL	RFC 3339 timestamp
updated_at	TEXT NOT NULL	RFC 3339 timestamp

reflections

Background reflection pass metadata and output (added in migration v5).

Column	Type	Description
id	TEXT PRIMARY KEY	UUID v4
status	TEXT NOT NULL	pending, running, done, failed
source_ids	TEXT NOT NULL	JSON array of item IDs analyzed
output	BLOB	Encrypted reflection output
themes	TEXT	JSON array of extracted themes
item_count	INTEGER	Number of items analyzed
error	TEXT	Error message if failed
created_at	TEXT NOT NULL	RFC 3339 timestamp
updated_at	TEXT NOT NULL	RFC 3339 timestamp

Index: reflections_created.

tool_invocations

Persistent log of every LLM, embedding, and tool execution (added in migration v6).

Column	Type	Description
id	TEXT PRIMARY KEY	UUID v4
tool_name	TEXT NOT NULL	Name of the tool/operation
category	TEXT NOT NULL	llm, embed, search, tts, job
status	TEXT NOT NULL	success, failure
duration_ms	INTEGER	Execution time
token_usage	INTEGER	Tokens consumed (LLM calls)
model_used	TEXT	Which model was used
item_id	TEXT	Associated item
job_id	TEXT	Associated job
error	TEXT	Error message on failure
created_at	TEXT NOT NULL	RFC 3339 timestamp

Indexes: tool_inv_name, tool_inv_created.

confidence_audit

Append-only ledger tracking every confidence score change with reason (added in migration v6).

Column	Type	Description
id	INTEGER PRIMARY KEY	Auto-increment
item_id	TEXT NOT NULL	Item whose score changed
old_score	REAL	Previous score (NULL for initial)
new_score	REAL NOT NULL	New score
reason	TEXT NOT NULL	initial, thesis_complete, decay, contradiction
source	TEXT	Additional context
created_at	TEXT NOT NULL	RFC 3339 timestamp

Index: conf_audit_item.

peer_profiles

Per-peer statistics from Hive interactions (added in migration v6).

Column	Type	Description
client_id	TEXT PRIMARY KEY	Peer's Hive client ID
display_name	TEXT NOT NULL	Peer's display name
item_count	INTEGER	Total shares received
imported_count	INTEGER	Shares imported to local vault
thesis_count	INTEGER	Shares with thesis data
avg_thesis_confidence	REAL	Average quality of peer's theses
topic_affinity	TEXT	JSON map of topic → count
trust_score	REAL	Synthesized credibility (0.0–1.0)

procedure_log / learned_procedures

Orchestration execution history and learned patterns (added in migration v6).

procedure_log records each orchestrator run with item_id, thought_type, tool_sequence (JSON array), success, duration_ms.

learned_procedures aggregates patterns: thought_type + tool_sequence → execution_count, success_count, avg_duration_ms, confidence.

Auxiliary Tables

tags

Column	Type	Description
item_id	TEXT NOT NULL	References items.id
tag	TEXT NOT NULL	Lowercased tag string
created_at	TEXT NOT NULL	RFC 3339 timestamp

Primary key: (item_id, tag). Indexes: tags_item, tags_tag.

notes

Conversation threads attached to items.

Column	Type	Description
id	TEXT PRIMARY KEY	UUID v4
item_id	TEXT NOT NULL	References items.id
role	TEXT NOT NULL	"user" or "assistant"
content	TEXT NOT NULL	Note content
created_at	TEXT NOT NULL	RFC 3339 timestamp

Index: notes_item on (item_id, created_at).

attachments

File attachments on items.

Column	Type	Description
id	TEXT PRIMARY KEY	UUID v4
item_id	TEXT NOT NULL	References items.id
filename	TEXT NOT NULL	Original filename
mime_type	TEXT NOT NULL	MIME type string
size	INTEGER NOT NULL	File size in bytes
blob_path	TEXT NOT NULL	Path to encrypted attachment blob
created_at	TEXT NOT NULL	RFC 3339 timestamp

Index: attachments_item.

item_meta

Structured metadata for agent-captured items (thought type, project, session).

Column	Type	Description
item_id	TEXT PRIMARY KEY	References items.id
agent	TEXT NOT NULL	Capturing agent identifier
thought_type	TEXT NOT NULL	e.g., "decision", "architecture", "debug"
project	TEXT	Project name
file_context	TEXT	File path context
language	TEXT	Programming language if applicable
session_id	TEXT	Grouping session identifier
confidence	TEXT	"speculation", "partially-vetted", "well-researched"
created_at	TEXT NOT NULL	RFC 3339 timestamp

Indexes: item_meta_agent, item_meta_thought_type, item_meta_session, item_meta_project.

api_tokens

Per-integration bearer tokens.

Column	Type	Description
token	TEXT PRIMARY KEY	Random 32-byte hex string
label	TEXT NOT NULL	Human-readable label
created_at	TEXT NOT NULL	RFC 3339 timestamp
last_used_at	TEXT	Updated on each successful auth
revoked_at	TEXT	Set when revoked (non-NULL = inactive)

hive_feed

Local cache of items received from the Hive relay.

Column	Type	Description
share_id	TEXT PRIMARY KEY	Hive share identifier
from_client_id	TEXT NOT NULL	Sending peer's client ID
from_display_name	TEXT NOT NULL	Sending peer's display name
preview	TEXT NOT NULL	Short preview text
content	TEXT NOT NULL	Full content
summary	TEXT	Summary if available
tags	TEXT DEFAULT '[]'	JSON array of tag strings
shared_at	TEXT NOT NULL	When originally shared
received_at	TEXT NOT NULL	When received locally
status	TEXT DEFAULT 'feed_only'	feed_only, imported, dismissed
local_item_id	TEXT	If imported, the local item ID
meta	TEXT	JSON structured metadata (thesis, notes, etc.)

Indexes: hive_feed_status, hive_feed_received.

jobs

The agent job queue (shared database file with items).

Column	Type	Description
id	TEXT PRIMARY KEY	UUID v4
kind	TEXT NOT NULL	Job type string
payload	TEXT NOT NULL	JSON-serialized Job enum
status	TEXT NOT NULL	pending, running, done, dead
attempts	INTEGER DEFAULT 0	Number of execution attempts
max_attempts	INTEGER DEFAULT 3	Maximum retry count
next_run_at	TEXT NOT NULL	Earliest time this job can run
last_error	TEXT	Error from most recent failure
created_at	TEXT NOT NULL	RFC 3339 timestamp
updated_at	TEXT NOT NULL	RFC 3339 timestamp

Indexes: jobs_pending on (status, next_run_at), jobs_created_at.

Blob Files

Item content is stored as encrypted files in the blobs/ directory, one file per item. The filename is <uuid>.bin. Each file contains:

[12-byte nonce][ciphertext + 16-byte GCM tag]

When reading an item's full content, the store reads the blob file from disk, decrypts it with the in-memory key, and returns the plaintext string. The preview column in SQLite holds the first ~200 characters for display without requiring decryption.

Briefing audio files follow the same pattern in the audios/ directory.

FTS5 Full-Text Search

Cortexa creates a virtual FTS5 table for keyword search:

CREATE VIRTUAL TABLE items_fts USING fts5(
    id UNINDEXED,
    preview,
    summary,
    content_body,
    tokenize='porter unicode61'
);

Indexed Columns

Column	Source	Purpose
id	items.id	UNINDEXED -- stored for joining, not searchable
preview	items.preview	First ~200 chars, searchable
summary	items.summary	LLM summary text, searchable
content_body	Decrypted blob	Full item content, searchable

The content_body column is populated when an item is ingested or updated. On FTS schema migration (user_version < 2), existing items are backfilled with preview and summary only (content_body is left empty and populated on next re-index).

Tokenizer

The porter unicode61 tokenizer provides:

• Unicode normalization via unicode61 -- handles accented characters, CJK, etc.
• Porter stemming -- "running" matches "run", "argued" matches "argue"
• Prefix search -- error* matches "error", "errors", "ErrorHandler"

BM25 Scoring

FTS5 queries use the built-in bm25() ranking function with custom column weights:

SELECT id, bm25(items_fts, 0.0, 1.0, 2.0, 5.0) as rank
FROM items_fts
WHERE items_fts MATCH ?
ORDER BY rank

The weight arguments correspond to (id, preview, summary, content_body):

Column	Weight	Rationale
id	0.0	Not searchable (UNINDEXED)
preview	1.0	Baseline weight
summary	2.0	Summaries are distilled -- a keyword match here is more relevant
content_body	5.0	Full content match is the strongest signal

Higher weights mean a match in that column contributes more to the final BM25 score. The search system then blends these keyword scores with semantic scores from the HNSW index using a configurable weight (default: 0.3 keyword, 0.7 semantic). When confidence scoring is enabled, high-confidence items receive a boost in FTS search ranking.

Schema Migrations

Cortexa uses PRAGMA user_version for schema versioning. Migrations run automatically on startup in Store::open():

Column Migrations

Before the versioned migrations, individual column additions are handled idempotently:

migrate_add_column(&conn, "summary", "TEXT")?;
migrate_add_column(&conn, "embedding", "BLOB")?;
migrate_add_column_with_default(&conn, "updated_at", "TEXT", "''")?;
migrate_add_column_with_default(&conn, "deleted", "INTEGER", "0")?;
migrate_add_column_with_default(&conn, "pinned", "INTEGER", "0")?;
migrate_add_column(&conn, "archived_at", "TEXT")?;
migrate_add_column(&conn, "trashed_at", "TEXT")?;

Each migrate_add_column call checks whether the column already exists before adding it, making upgrades from any version safe.

Versioned Migrations

Version	Migration
0 -> 2	Drop and recreate items_fts table with content_body column. Backfill FTS from existing items.
2 -> 3	Decrypt embeddings from AES-GCM encrypted BLOBs to raw float bytes. Removes the encryption layer from embedding vectors.

Crash Recovery

On startup, the store also runs recovery operations:

• Jobs: Any jobs left in running status (from a crash) are reset to pending.
• Theses: Any theses stuck in expanding, critiquing, or synthesizing are marked failed with "reset after crash".

Adding New Migrations

To add a new migration:

1. Read the current PRAGMA user_version.
2. If it is less than your target version, run the migration.
3. Set PRAGMA user_version = <new_version>.

The pattern ensures each migration runs exactly once, even across version jumps.

Store Implementation

The Store struct (src-tauri/src/storage.rs) owns:

• A rusqlite::Connection for all SQL operations
• A PathBuf for the blobs directory
• A PathBuf for the database file (used for size reporting)
• An EncryptionKey for encrypt/decrypt operations

Key methods:

Method	Purpose
open(data_dir, key)	Create tables, run migrations, return store
ingest_text(content)	Encrypt content, write blob, insert row, populate FTS
read_item_content(id)	Read blob from disk, decrypt, return plaintext
stats()	Count items, embedded items, DB size, blob directory size
rekey(new_key)	Re-encrypt all blobs with a new key
build_local_manifest()	Export entries for sync
apply_remote_item(entry, blob)	Import a synced item
search_fts(query, limit)	FTS5 keyword search with BM25 scoring
upsert_fts(id, preview, summary, content)	Insert or update FTS entry

Database Maintenance

• VACUUM: Available in Settings > Retention > "Compact database". Rebuilds the SQLite file to reclaim space after many deletes.
• Retention: Auto-archive after N days, trash grace period before hard delete, manual "Run retention now" button.
• Blob cleanup: When an item is hard-deleted, its blob file is removed from disk.