Struct Database 

pub struct Database { /* private fields */ }

SQLite database connection wrapper.

Provides methods for storing and querying sessions, messages, and session-to-commit links. Handles schema migrations automatically when opening the database.

Implementations

impl Database

pub fn open(path: &PathBuf) -> Result<Self>

Opens or creates a database at the specified path.

Runs schema migrations automatically to ensure tables exist.

pub fn open_default() -> Result<Self>

Opens the default database at ~/.lore/lore.db.

Creates the database file and directory if they do not exist.

pub fn insert_session(&self, session: &Session) -> Result<()>

Inserts a new session or updates an existing one.

If a session with the same ID already exists, updates the ended_at and message_count fields. Resets both synced_at and global_synced_at to NULL if either the message_count or ended_at has changed (a local content change invalidates both the per-repo and global sync tracks, so the session is re-exported to each store on the next sync). Also updates the sessions_fts index for full-text search on session metadata.

pub fn get_session(&self, id: &Uuid) -> Result<Option<Session>>

Retrieves a session by its unique ID.

Returns None if no session with the given ID exists.

pub fn list_sessions( &self, limit: usize, working_dir: Option<&str>, ) -> Result<Vec<Session>>

Lists sessions ordered by start time (most recent first).

Optionally filters by working directory prefix. Returns at most limit sessions.

pub fn list_ended_sessions( &self, limit: usize, working_dir: Option<&str>, ) -> Result<Vec<Session>>

Lists ended sessions ordered by start time (most recent first).

Optionally filters by working directory prefix.

pub fn session_exists_by_source(&self, source_path: &str) -> Result<bool>

Checks if a session with the given source path already exists.

Used to detect already-imported sessions during import operations.

pub fn get_session_by_source( &self, source_path: &str, ) -> Result<Option<Session>>

Retrieves a session by its source path.

Returns None if no session with the given source path exists. Used by the daemon to find existing sessions when updating them.

pub fn find_session_by_id_prefix(&self, prefix: &str) -> Result<Option<Session>>

Finds a session by ID prefix, searching all sessions in the database.

This method uses SQL LIKE to efficiently search by prefix without loading all sessions into memory. Returns an error if the prefix is ambiguous (matches multiple sessions).

Arguments

• prefix - The UUID prefix to search for (can be any length)

Returns

• Ok(Some(session)) - If exactly one session matches the prefix
• Ok(None) - If no sessions match the prefix
• Err - If multiple sessions match (ambiguous prefix) or database error

pub fn update_session_branch( &self, session_id: Uuid, new_branch: &str, ) -> Result<usize>

Updates the git branch for a session.

Used by the daemon when a message is processed with a different branch than the session’s current branch, indicating a branch switch mid-session. Also updates the sessions_fts index to keep search in sync.

Returns the number of rows affected (0 or 1).

pub fn insert_message(&self, message: &Message) -> Result<()>

Inserts a message into the database.

If a message with the same ID already exists, the insert is ignored. Message content is serialized to JSON for storage. Also inserts extracted text content into the FTS index for full-text search.

pub fn import_session_with_messages( &mut self, session: &Session, messages: &[Message], synced_at: Option<DateTime<Utc>>, ) -> Result<()>

Imports a session with all its messages in a single transaction.

This is much faster than calling insert_session and insert_message separately for each message, as it batches all operations into one database transaction. Optionally marks the session as synced.

Note: retained as public API and used as the storage tests’ fixture builder; production import paths write sessions incrementally.

pub fn list_tombstones(&self) -> Result<Vec<Tombstone>>

Returns every locally recorded tombstone.

Ordered by (kind, child_id) so the serialized store blob is stable across repeated syncs on the same machine (content-addressed dedup).

pub fn add_tombstones(&self, tombstones: &[Tombstone]) -> Result<()>

Unions a set of remote tombstones into the local table.

First-wins on (child_id, kind): an existing local tombstone is left untouched, so a machine’s own deletion timestamp is preserved. Used by the sync merge before importing remote records so the merge can suppress any child that was deleted elsewhere.

pub fn apply_tombstones(&self, tombstones: &[Tombstone]) -> Result<()>

Deletes any locally-present child records that are tombstoned.

Enforces, on this machine, a deletion that happened on another machine. Deletes with plain SQL rather than the user-facing delete methods so it does NOT record a fresh tombstone (the child is already tombstoned) and cannot recurse. Each session whose child was actually removed is marked unsynced so the cleaned record is re-exported on the next sync, replacing the stale blob in the store. Suppression during merge already prevents resurrection, so this re-export is a cleanup rather than a correctness requirement.

pub fn prune_tombstones(&self, before: DateTime<Utc>) -> Result<usize>

Prunes tombstones deleted before the given cutoff.

Returns the number of tombstones removed. This is NOT called during sync: an age-based prune there could drop a tombstone still needed to suppress a stale session blob, resurrecting the deleted child. Tombstones are tiny, so the sync path keeps the full set indefinitely. This method is retained for a possible future safe garbage collection that only prunes tombstones proven to have propagated to every machine.

pub fn merge_remote_record( &mut self, session: &Session, messages: &[Message], links: &[SessionLink], tags: &[Tag], annotations: &[Annotation], summary: Option<&Summary>, synced_at: DateTime<Utc>, ) -> Result<bool>

Merges a full remote reasoning record into the database atomically.

Used by git-ref sync when pulling a remote store. The session row, messages, and every child record are persisted inside a single transaction, so a crash can never leave child data missing while the session already looks synced and current. Merge rules:

• Session row and messages use newer-wins: they are written only when there is no local session yet or the remote session is newer (more messages, or an equal message count with a later ended_at). When written, the session is marked synced with synced_at.
• Links, tags, and annotations are additive and idempotent by id, so they are merged regardless of which session row is newer, EXCEPT that a child whose (child_id, kind) is tombstoned is suppressed. This lets a remote that added a child to an already-synced session still deliver it, while a child deleted on another machine is not resurrected.
• The summary is applied through the newer-wins writer (unless tombstoned), so an older remote summary never clobbers a newer local one.

Deletion propagation: the caller unions remote tombstones into the local table before merging (so suppression here sees them) and applies them afterward via Self::apply_tombstones to remove any child that was already present locally. See cli/commands/sync.rs.

Returns true when the session row and messages were written (the newer-wins branch ran), which callers use for the pulled count.

This marks imported sessions on the per-repo synced_at track. The global store uses Self::merge_remote_record_global, which shares the identical merge logic but marks the global_synced_at track.

pub fn merge_remote_record_global( &mut self, session: &Session, messages: &[Message], links: &[SessionLink], tags: &[Tag], annotations: &[Annotation], summary: Option<&Summary>, synced_at: DateTime<Utc>, ) -> Result<bool>

Global-store counterpart of Self::merge_remote_record.

Applies the identical newer-wins and additive-child merge, but marks an imported session on the global global_synced_at track so a global pull does not affect the per-repo synced_at track (and vice versa).

pub fn get_messages(&self, session_id: &Uuid) -> Result<Vec<Message>>

Retrieves all messages for a session, ordered by index.

Messages are returned in conversation order (by their index field).

pub fn get_session_branch_history( &self, session_id: Uuid, ) -> Result<Vec<String>>

Returns the ordered list of distinct branches for a session.

Branches are returned in the order they first appeared in messages, with consecutive duplicates removed. This shows the branch transitions during a session (e.g., “main -> feat/auth -> main”).

Returns an empty vector if the session has no messages or all messages have None branches.

pub fn insert_link(&self, link: &SessionLink) -> Result<()>

Inserts a link between a session and a git commit.

Links can be created manually by users or automatically by the auto-linking system based on time and file overlap heuristics.

pub fn get_links_by_commit(&self, commit_sha: &str) -> Result<Vec<SessionLink>>

Retrieves all session links for a commit.

Supports prefix matching on the commit SHA, allowing short SHAs (e.g., first 8 characters) to be used for lookup.

pub fn get_links_by_session( &self, session_id: &Uuid, ) -> Result<Vec<SessionLink>>

Retrieves all links associated with a session.

A session can be linked to multiple commits if it spans several git operations.

pub fn delete_link(&self, link_id: &Uuid) -> Result<bool>

Deletes a specific session link by its ID.

Returns true if a link was deleted, false if no link with that ID existed.

Note: This method is part of the public API for programmatic use, though the CLI currently uses session/commit-based deletion.

pub fn delete_links_by_session(&self, session_id: &Uuid) -> Result<usize>

Deletes all links for a session.

Returns the number of links deleted.

pub fn delete_link_by_session_and_commit( &self, session_id: &Uuid, commit_sha: &str, ) -> Result<bool>

Deletes a link between a specific session and commit.

The commit_sha is matched as a prefix, so short SHAs work. Returns true if a link was deleted, false if no matching link existed.

pub fn search_messages( &self, query: &str, limit: usize, working_dir: Option<&str>, since: Option<DateTime<Utc>>, role: Option<&str>, ) -> Result<Vec<SearchResult>>

Searches message content using full-text search.

Uses SQLite FTS5 to search for messages matching the query. Returns results ordered by FTS5 relevance ranking.

Optional filters:

• working_dir: Filter by working directory prefix
• since: Filter by minimum timestamp
• role: Filter by message role

Note: This is the legacy search API. For new code, use search_with_options.

pub fn search_with_options( &self, options: &SearchOptions, ) -> Result<Vec<SearchResult>>

Searches messages and session metadata using full-text search with filters.

Uses SQLite FTS5 to search for messages matching the query. Also searches session metadata (tool, project, branch) via sessions_fts. Returns results ordered by FTS5 relevance ranking.

Supports extensive filtering via SearchOptions:

• tool: Filter by AI tool name
• since/until: Filter by date range
• project: Filter by project name (partial match)
• branch: Filter by git branch (partial match)
• role: Filter by message role
• repo: Filter by working directory prefix

pub fn get_context_messages( &self, session_id: &Uuid, message_index: i32, context_count: usize, ) -> Result<(Vec<Message>, Vec<Message>)>

Gets messages around a specific message for context.

Returns N messages before and N messages after the specified message, useful for displaying search results with surrounding context.

pub fn get_message_by_index( &self, session_id: &Uuid, index: i32, ) -> Result<Option<Message>>

Gets a single message by its index within a session.

pub fn rebuild_search_index(&self) -> Result<usize>

Rebuilds the full-text search index from existing messages and sessions.

This should be called when:

• Upgrading from a database without FTS support
• The FTS index becomes corrupted or out of sync

Returns the number of messages indexed.

pub fn search_index_needs_rebuild(&self) -> Result<bool>

Checks if the search index needs rebuilding.

Returns true if there are messages or sessions in the database but the FTS indexes are empty, indicating data was imported before FTS was added.

pub fn upsert_memory(&self, memory: &Memory) -> Result<Uuid>

Inserts a mirrored memory or updates the existing one for the same source file.

Memories are uniquely identified by their (project_path, source_tool, file_path) triple. If a memory already exists for that source file its existing id is preserved and its fields are updated in place. The memories_fts index is kept in sync so search reflects the latest content. Returns the id of the stored memory.

pub fn delete_memory(&self, id: &Uuid) -> Result<bool>

Deletes a mirrored memory by id, also removing it from the FTS index.

Returns true if a memory was deleted.

pub fn get_memories( &self, project_path: &str, source_tool: &str, ) -> Result<Vec<Memory>>

Returns all mirrored memories for a project and source tool.

Results are ordered by name for stable display.

pub fn search_memories( &self, project_path: &str, source_tool: &str, query: &str, limit: usize, ) -> Result<Vec<Memory>>

Full-text searches mirrored memories for a project and source tool.

Matches against memory name, description, and content using FTS5 and returns results ordered by relevance, limited to limit rows.

pub fn get_unsynced_sessions(&self) -> Result<Vec<Session>>

Returns sessions that have not been synced.

Unsynced sessions are those where synced_at is NULL. Returns sessions ordered by start time (oldest first) to sync in chronological order.

Note: production sync scopes pushes with Database::get_unsynced_sessions_for_repo (per-repo) or Database::get_unsynced_global_sessions (global). This unscoped accessor is retained as public API and is exercised by the storage tests.

pub fn get_unsynced_sessions_for_repo( &self, repo_path: &Path, ) -> Result<Vec<Session>>

Returns unsynced sessions whose working directory is inside repo_path.

A per-repo lore store must hold only the reasoning history produced in that repository, so an outbound sync scopes its push to sessions whose working_directory is the repo root or a descendant of it. Cross-project and cross-tool sessions captured elsewhere are excluded, keeping one repo’s store from leaking a user’s entire history to teammates.

Matching uses the shared repo-scoping predicate (see [repo_scope_predicate]), which covers both the repo path as given and its canonicalized form so a session captured under a symlinked path still syncs, while a prefix sibling such as /x/foobar never matches the repo /x/foo. Results are ordered oldest first to sync in chronological order.

pub fn get_session_ids_for_repo( &self, repo_path: &Path, ) -> Result<HashSet<Uuid>>

Returns the ids of ALL sessions (regardless of synced_at) whose working directory is inside repo_path.

Unlike Database::get_unsynced_sessions_for_repo, this ignores the synced state: it answers “which session ids belong to this repo”, which the outbound sync uses to decide whether an already-stored, local-only session artifact is in scope to carry forward. It shares the exact same directory scoping (see [repo_scope_predicate]) so the two agree on what “in this repo” means.

pub fn get_all_session_ids(&self) -> Result<HashSet<Uuid>>

Returns the ids of ALL sessions in the database, regardless of sync state.

The global personal store aggregates every session across all repos and tools, so its carry-forward scope is the entire session set (the global analogue of Database::get_session_ids_for_repo, which scopes to one repo). Used by the global sync to decide which already-stored, local-only session artifacts to carry forward.

pub fn get_unsynced_global_sessions(&self) -> Result<Vec<Session>>

Returns sessions that have not been synced to the global personal store.

Unsynced-global sessions are those where global_synced_at is NULL. Unlike Database::get_unsynced_sessions_for_repo, this is not scoped to any repository: the global store holds every session regardless of working directory. Returns sessions ordered by start time (oldest first).

pub fn unsynced_global_count(&self) -> Result<i32>

Returns the count of sessions not yet synced to the global personal store.

pub fn mark_global_synced( &self, session_ids: &[Uuid], synced_at: DateTime<Utc>, ) -> Result<usize>

Marks sessions as synced to the global personal store.

Updates the global_synced_at column for all specified session IDs, leaving the per-repo synced_at track untouched.

pub fn last_global_sync_time(&self) -> Result<Option<DateTime<Utc>>>

Returns the most recent global-store sync timestamp across all sessions.

Returns None if no session has been synced to the global store yet.

pub fn unsynced_session_count_for_repo(&self, repo_path: &Path) -> Result<i32>

Returns the count of unsynced sessions whose working directory is inside repo_path.

Uses the same directory scoping as Database::get_unsynced_sessions_for_repo so lore sync status reports what this repo will actually push.

pub fn mark_sessions_synced( &self, session_ids: &[Uuid], synced_at: DateTime<Utc>, ) -> Result<usize>

Marks sessions as synced with the given timestamp.

Updates the synced_at column for all specified session IDs.

pub fn last_sync_time(&self) -> Result<Option<DateTime<Utc>>>

Returns the most recent sync timestamp across all sessions.

Returns None if no sessions have been synced yet.

pub fn session_count(&self) -> Result<i32>

Returns the total number of sessions in the database.

pub fn message_count(&self) -> Result<i32>

Returns the total number of messages across all sessions.

pub fn link_count(&self) -> Result<i32>

Returns the total number of session links in the database.

pub fn db_path(&self) -> Option<PathBuf>

Returns the path to the database file, if available.

Returns None for in-memory databases.

pub fn find_sessions_near_commit_time( &self, commit_time: DateTime<Utc>, window_minutes: i64, working_dir: Option<&str>, ) -> Result<Vec<Session>>

Finds sessions that were active around a commit time.

A session is considered active if the commit time falls within the window before and after the session’s time range (started_at to ended_at).

Arguments

• commit_time - The timestamp of the commit
• window_minutes - The window in minutes before/after the session
• working_dir - Optional working directory filter (prefix match)

Returns

Sessions that were active near the commit time, ordered by proximity.

pub fn link_exists(&self, session_id: &Uuid, commit_sha: &str) -> Result<bool>

Checks if a link already exists between a session and commit.

Used to avoid creating duplicate links during auto-linking.

pub fn find_active_sessions_for_directory( &self, directory: &str, recent_minutes: Option<i64>, ) -> Result<Vec<Session>>

Finds sessions that are currently active or recently ended for a directory.

This is used by forward auto-linking to find sessions to link when a commit is made. A session is considered “active” if:

• It has no ended_at timestamp (still ongoing), OR
• It ended within the last recent_minutes (default 5 minutes)

The directory filter uses a prefix match, so sessions in subdirectories of the given path will also be included.

Arguments

• directory - The repository root path to filter sessions by
• recent_minutes - How many minutes back to consider “recent” (default 5)

Returns

Sessions that are active or recently ended in the given directory.

pub fn delete_session(&self, session_id: &Uuid) -> Result<(usize, usize)>

Deletes a session and all its associated data.

Removes the session, all its messages, all FTS index entries, and all session links. Returns the counts of deleted items.

Returns

A tuple of (messages_deleted, links_deleted) counts.

pub fn insert_annotation(&self, annotation: &Annotation) -> Result<()>

Inserts a new annotation for a session.

Annotations are user-created bookmarks or notes attached to sessions.

pub fn get_annotations(&self, session_id: &Uuid) -> Result<Vec<Annotation>>

Retrieves all annotations for a session.

Annotations are returned in order of creation (oldest first).

pub fn delete_annotation(&self, annotation_id: &Uuid) -> Result<bool>

Deletes an annotation by its ID.

Returns true if an annotation was deleted, false if not found.

pub fn delete_annotations_by_session(&self, session_id: &Uuid) -> Result<usize>

Deletes all annotations for a session.

Returns the number of annotations deleted.

pub fn insert_tag(&self, tag: &Tag) -> Result<()>

Inserts a new tag for a session.

Tags are unique per session, so attempting to add a duplicate tag label to the same session will fail with a constraint error.

pub fn get_tags(&self, session_id: &Uuid) -> Result<Vec<Tag>>

Retrieves all tags for a session.

Tags are returned in alphabetical order by label.

pub fn tag_exists(&self, session_id: &Uuid, label: &str) -> Result<bool>

Checks if a tag with the given label exists for a session.

pub fn delete_tag(&self, session_id: &Uuid, label: &str) -> Result<bool>

Deletes a tag by session ID and label.

Returns true if a tag was deleted, false if not found.

pub fn delete_tags_by_session(&self, session_id: &Uuid) -> Result<usize>

Deletes all tags for a session.

Returns the number of tags deleted.

pub fn list_sessions_with_tag( &self, label: &str, limit: usize, ) -> Result<Vec<Session>>

Lists sessions with a specific tag label.

Returns sessions ordered by start time (most recent first).

pub fn insert_summary(&self, summary: &Summary) -> Result<()>

Inserts a new summary for a session.

Each session can have at most one summary. If a summary already exists for the session, this will fail due to the unique constraint.

pub fn get_summary(&self, session_id: &Uuid) -> Result<Option<Summary>>

Retrieves the summary for a session, if one exists.

pub fn get_sessions_with_summaries( &self, session_ids: &[Uuid], ) -> Result<HashSet<Uuid>>

Returns the set of session IDs (from the given list) that have summaries.

This is a batch alternative to calling get_summary per session, avoiding N+1 queries in list views where only existence matters.

pub fn update_summary(&self, session_id: &Uuid, content: &str) -> Result<bool>

Updates the summary for a session.

Updates the content and generated_at timestamp for an existing summary. Returns true if a summary was updated, false if no summary exists.

pub fn delete_summary(&self, session_id: &Uuid) -> Result<bool>

Deletes the summary for a session.

Returns true if a summary was deleted, false if no summary existed.

pub fn upsert_machine(&self, machine: &Machine) -> Result<()>

Registers a machine or updates its name if it already exists.

Used to store machine identity information for sync deduplication. If a machine with the given ID already exists, updates the name.

pub fn get_machine(&self, id: &str) -> Result<Option<Machine>>

Gets a machine by ID.

Returns None if no machine with the given ID exists.

pub fn get_machine_name(&self, id: &str) -> Result<String>

Gets the display name for a machine ID.

Returns the machine name if found, otherwise returns a truncated UUID (first 8 characters) for readability.

pub fn list_machines(&self) -> Result<Vec<Machine>>

Lists all registered machines.

Returns machines ordered by creation date (oldest first).

pub fn get_most_recent_session_for_directory( &self, working_dir: &str, ) -> Result<Option<Session>>

Gets the most recent session for a given working directory.

Returns the session with the most recent started_at timestamp where the working directory matches or is a subdirectory of the given path.

pub fn vacuum(&self) -> Result<()>

Runs SQLite VACUUM to reclaim unused space and defragment the database.

This operation can take some time on large databases and temporarily doubles the disk space used while rebuilding.

pub fn file_size(&self) -> Result<Option<u64>>

Returns the file size of the database in bytes.

Returns None for in-memory databases.

pub fn delete_sessions_older_than(&self, before: DateTime<Utc>) -> Result<usize>

Deletes sessions older than the specified date.

Also deletes all associated messages, links, and FTS entries.

Arguments

• before - Delete sessions that started before this date

Returns

The number of sessions deleted.

pub fn count_sessions_older_than(&self, before: DateTime<Utc>) -> Result<i32>

Counts sessions older than the specified date (for dry-run preview).

Arguments

• before - Count sessions that started before this date

Returns

The number of sessions that would be deleted.

pub fn get_sessions_older_than( &self, before: DateTime<Utc>, ) -> Result<Vec<Session>>

Returns sessions older than the specified date (for dry-run preview).

Arguments

• before - Return sessions that started before this date

Returns

A vector of sessions that would be deleted, ordered by start date.

pub fn stats(&self) -> Result<DatabaseStats>

Returns database statistics including counts and date ranges.

Returns

A DatabaseStats struct with session, message, and link counts, plus the date range of sessions and a breakdown by tool.

pub fn sessions_in_date_range( &self, since: Option<DateTime<Utc>>, until: Option<DateTime<Utc>>, working_dir: Option<&str>, ) -> Result<Vec<Session>>

Returns sessions filtered by an optional date range and working directory.

Both since and until are optional bounds on started_at. When working_dir is provided, only sessions whose working directory starts with the given prefix are returned.

pub fn average_session_duration_minutes( &self, since: Option<DateTime<Utc>>, working_dir: Option<&str>, ) -> Result<Option<f64>>

Returns the average session duration in minutes.

Only sessions where ended_at is set are included in the calculation. Returns None if no matching sessions have an end time.

pub fn sessions_by_tool_in_range( &self, since: Option<DateTime<Utc>>, working_dir: Option<&str>, ) -> Result<Vec<(String, i32)>>

Returns session counts grouped by tool name.

Results are sorted by count in descending order. Optionally filters by a minimum start date and working directory prefix.

pub fn sessions_by_weekday( &self, since: Option<DateTime<Utc>>, working_dir: Option<&str>, ) -> Result<Vec<(i32, i32)>>

Returns session counts grouped by weekday number.

Uses SQLite strftime('%w', started_at) which yields 0 for Sunday through 6 for Saturday. Only weekdays that have at least one session are returned. Optionally filters by a minimum start date and working directory prefix.

pub fn average_message_count( &self, since: Option<DateTime<Utc>>, working_dir: Option<&str>, ) -> Result<Option<f64>>

Returns the average message count across sessions.

Returns None if no sessions match the given filters.