Database Schema & Providers DB

Relevant source files

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

• src/db.rs
• src/providers_db.rs
• src/curate.rs
• src/search.rs
• src/worker.rs
• src/import.rs

Oboete uses two primary SQLite databases to manage agent memory and system state. The main database, oboete.db, stores sessions, raw events, observations, and search indexes. The auxiliary database, providers.db, manages the state of the curation phase, including pending tasks and provider usage statistics.

Both databases utilize Write-Ahead Logging (WAL) mode for concurrent access. They implement a 2-second busy timeout to handle lock contention between hooks and workers. The system enforces private file permissions (0600) on all database files to protect sensitive prompts and tool outputs.

Sources: src/db.rs:1-125, src/worker.rs:310-335

Primary Knowledge Store (oboete.db)

The main knowledge store contains the relational tables for agent sessions and derived memory items. It includes specialized tables for full-text search and vector embeddings.

Core Tables and Relationships

The sessions table serves as the primary anchor for all recorded activity. Memory items like observations, summaries, and prompts link to sessions via the session_id.

This diagram represents the core entities in the oboete.db schema and how they relate to the central session record.

Sources: src/db.rs:9-76

Data Model Details

Table	Description	Key Fields
sessions	Tracks agent activity and curation progress.	id, agent, repo, observed_event_id
observations	Memory items like decisions or bugfixes.	kind, title, body, uid
events	Raw hook payloads stored before processing.	session_id, event, payload
meta	Store-wide facts like device_id.	key, value
embeddings	Normalized fp32 vectors for semantic search.	doc, vec, indexed

Sources: src/db.rs:9-76, src/search.rs:10-25

Full-Text and Vector Search Indexes

Oboete implements high-performance search through two specialized virtual tables. These tables index data stored in the relational memory tables.

• FTS5 Index (fts): Uses the trigram tokenizer. This allows substring matching and character-based indexing for CJK (Chinese, Japanese, Korean) text. It indexes titles and bodies from observations, summaries, and prompts.
• Vector Index (vec_docs): A vec0 virtual table that stores bit-packed embeddings. It enables semantic search by calculating Hamming distance between query vectors and stored document vectors.

Sources: src/db.rs:360-435, src/search.rs:1-40

Curation State (providers.db)

The providers.db database tracks the status of the curation phase. It manages retry logic for windows that failed to curate due to provider errors or budget limits.

Pending Curation Tasks

The pending table identifies windows of records currently awaiting curation. It tracks why a window is held and when the system should attempt the next call.

This flowchart shows how the system uses the pending table to manage curation attempts and holds.

Sources: src/curate.rs:430-480, src/providers_db.rs

Hold States

A pending window enters one of three hold states depending on the failure reason:

1. Time: Used for HTTP errors (e.g., 429) or transient failures. Retried after 10 minutes.
2. Budget: Used when daily provider limits are reached. Retried when the budget resets at UTC midnight.
3. Owner: Used when a provider is manually stopped or credentials fail. Retried every 60 minutes.

Sources: src/curate.rs:690-720, src/db.rs:600-620

Schema Evolution and Maintenance

The system performs automatic migrations when the binary opens a database.

• Idempotent Updates: The ensure_column function checks for column existence before attempting an ALTER TABLE operation. This prevents errors when multiple processes open the same database.
• UID Generation: Every document receives a uid unique across devices. For local records, this is <device_id>:<doc_id>. For imported records, it is <source>:<source_id>. Triggers automatically fill these UIDs on insertion.
• Repository Rekeying: The rekey_paths function moves records from absolute local paths to origin URLs (e.g., github.com/owner/repo) once a repository acquires a remote origin.

Sources: src/db.rs:135-250, src/db.rs:438-460, src/import.rs:190-220

Device and File Identity

Oboete tracks the identity of the database file to prevent ID collisions if a store is copied to another machine.

• Store File ID: On Unix, this is derived from the device and inode numbers. On Windows, it uses the volume serial number and file index.
• Device ID: If the system detects the database is a new file (e.g., via a restore or copy), it generates a new 8-hex-digit device_id in the meta table.

Sources: src/db.rs:253-330

Oboete's dual-database architecture separates long-term knowledge from transient operational state. The use of specialized virtual tables ensures that search remains efficient even as the memory store grows. Schema maintenance functions allow the application to evolve without requiring manual intervention from the user.

Sources: src/db.rs:1-125, src/curate.rs:430-480