Workspace Store & SQLite Data Model

Relevant source files

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

• packages/workspace/src/store.ts
• packages/workspace/src/database.ts
• packages/workspace/src/backup.ts
• packages/workspace/test/store.test.ts
• AGENTS.md

The WorkspaceStore serves as the central persistence layer for the Noesis project, enforcing a strict "One Authority per Datum" policy. It coordinates between an authoritative SQLite database for operational state and the local file system for declarative definitions and large artifacts. The system is designed to ensure that every persisted item has exactly one declared authority, preventing dual-write conflicts and ensuring reliable recovery via SQLite transactions and file-system snapshots.

Architecturally, the store manages several specialized directories within the workspace root, including definitions for editable files, revisions for immutable byte-stable snapshots, and artifacts for large outputs. SQLite is used to track sessions, messages, tool calls, and the lifecycle of experiments, while also providing rebuildable indexes for full-text and semantic search.
Sources: AGENTS.md:28-35, packages/workspace/src/store.ts:114-140

Architectural Overview

The WorkspaceStore utilizes a hybrid storage model where short-lived or relational operational data resides in SQLite, while declarative logic and heavy binary data reside on the file system.

The diagram shows the relationship between the WorkspaceStore, the SQLite database, and the partitioned file system.
Sources: AGENTS.md:28-38, packages/workspace/src/store.ts:114-135

Workspace Directory Structure

The store initializes and manages a specific directory hierarchy to ensure isolation between active, candidate, and staged data:

Directory	Purpose
definitions/	Contains editable working files for prompts, skills, and tools.
definitions/active/	Authoritative area for currently active capability revisions.
definitions/candidates/	Staging area for proposed capability changes during experiments.
revisions/	Content-addressed, immutable snapshots of definition files.
artifacts/	Storage for large binary outputs with metadata tracked in SQLite.
evidence/	Append-only storage for evaluation inputs, outputs, and judgments.
staging/	Temporary storage for definitions during the multi-step publication process.

Sources: packages/workspace/src/store.ts:167-183, packages/workspace/src/paths.ts

SQLite Data Model

The SQLite database is the authoritative source for operational state. It uses a range of tables to track the execution history and research progress of the system.

Table Primary Key Mapping

The store utilizes a consistent mapping for primary keys across its relational tables:

Table	Primary Key	Description
sessions	session_id	Tracks individual chat sessions.
messages	message_id	Individual message entries within a session.
tool_calls	tool_call_id	Records of tool execution requests and responses.
outcomes	outcome_id	Summaries and status of completed turns.
experiments	experiment_id	Durable records of research hypotheses and status.
experiment_trials	trial_id	Individual test runs within an experiment.
file_revisions	revision_id	Metadata for file snapshots (digest, path, actor).
activity_log	activity_id	Provenance log for all significant workspace actions.

Sources: packages/workspace/src/store.ts:89-109

Entity Relationships

Entities in the operational database are linked to maintain a clear chain of provenance, especially during experiments and trials.

This ER diagram represents the core operational relationships tracked within the SQLite database.
Sources: packages/workspace/src/store.ts:143-165, packages/workspace/src/store.ts:771-850

Revision & Staging Lifecycle

A critical feature of the WorkspaceStore is the management of file revisions. Every edit to a working definition is recorded as an immutable snapshot.

Recording Revisions

When a file is saved or a staged definition is registered, the store:

1. Calculates a SHA-256 content digest of the bytes.
2. Persists the bytes atomically to a unique path in the revisions/ directory.
3. Records metadata in the file_revisions table, including the predecessor_revision_id to maintain a linear history.

Sources: packages/workspace/src/store.ts:251-285, packages/workspace/test/store.test.ts:39-55

The Staging Process

To prevent partial or invalid updates, changes to active definitions follow a staging lifecycle:

The sequence shows how the store ensures definitions are staged and verified before becoming active.
Sources: packages/workspace/src/store.ts:468-520, packages/workspace/test/store.test.ts:57-78

Database Management & Integrity

The WorkspaceDatabase module handles low-level SQLite interactions, ensuring that migrations and transactions are handled safely.

Transaction Management

The store uses BEGIN IMMEDIATE for transactions to prevent deadlocks during concurrent access. If any operation within a transaction fails, a ROLLBACK is issued to maintain atomicity.
Sources: packages/workspace/src/database.ts:21-33, packages/workspace/test/store.test.ts:258-274

Migrations

Migrations are applied incrementally based on a schema_migrations table. Each migration is an atomic operation that updates the database schema to the latest version.
Sources: packages/workspace/src/database.ts:42-70, packages/workspace/test/store.test.ts:80-99

Backup & Integrity Checks

The store provides built-in utilities for inspecting workspace health:

• Integrity Checks: Executes PRAGMA integrity_check and verifies that all files cited in the database exist on disk.
• Backups: Performs a FULL checkpoint on the WAL, begins an IMMEDIATE transaction, and copies the database and all managed files to a backup root with a manifest.

Sources: packages/workspace/src/backup.ts:24-60, packages/workspace/src/backup.ts:62-114

Search Indexing

The store maintains rebuildable search indexes within SQLite using FTS5 (lexical) and vector storage (semantic).

Feature	Implementation	Authority
Lexical Search	SQLite FTS5 (search_fts table)	Rebuilt from DB rows and file revisions.
Semantic Search	Cosine similarity on vector_json	Rebuilt from source documents via embeddings.
Source Citation	citation_json column	Always cites an authoritative DB Row or File Revision.

Sources: packages/workspace/src/store.ts:1145-1250, AGENTS.md:32-34

Conclusion

The Workspace Store and its underlying SQLite data model provide a robust, transactional foundation for Noesis. By strictly separating operational state (SQLite) from declarative content (Files) and enforcing immutability through a revisioning system, the project ensures that experiments and turn execution remain reproducible and auditable. The use of rebuildable indexes further allows for sophisticated search capabilities without compromising the integrity of the authoritative data sources.