Class: SwarmSDK::V3::Memory::Adapters::SqliteAdapter

Inherits:
Base
  • Object
show all
Includes:
VectorUtils
Defined in:
lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb

Overview

SQLite-based storage adapter with sqlite-vec vector index

Stores cards, edges, clusters, and vector embeddings in a single SQLite database for durable, transactional, multi-process-safe storage. Vector search uses sqlite-vec’s vec0 virtual table with cosine distance, eliminating external FAISS files and ID mapping tables.

Database structure:

.swarm/memory/
└── memory.db        (SQLite database with vec0 virtual table)

## Why UPSERT instead of INSERT OR REPLACE

‘INSERT OR REPLACE` deletes the old row then inserts a new one. With `ON DELETE CASCADE` on edges, this silently destroys all edges for the card on every update (including `record_access!`). `ON CONFLICT DO UPDATE` updates in-place without triggering CASCADE.

Examples:

adapter = SqliteAdapter.new("/path/to/.swarm/memory")
adapter.load
adapter.write_card(card)
adapter.save

Constant Summary collapse

EMBEDDING_DIMENSIONS =

Embedding dimensions for MiniLM-L6-v2 model

384
SCHEMA_SQL =

SQL schema for the memory database

<<~SQL
  CREATE TABLE IF NOT EXISTS cards (
    id TEXT PRIMARY KEY,
    text TEXT NOT NULL,
    type TEXT NOT NULL,
    entities TEXT NOT NULL DEFAULT '[]',
    source_turn_ids TEXT NOT NULL DEFAULT '[]',
    embedding TEXT,
    importance REAL NOT NULL DEFAULT 0.5,
    confidence REAL NOT NULL DEFAULT 1.0,
    access_count INTEGER NOT NULL DEFAULT 0,
    last_accessed TEXT,
    dwell REAL NOT NULL DEFAULT 0.0,
    compression_level INTEGER NOT NULL DEFAULT 0,
    canonical_id TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
  );

  CREATE TABLE IF NOT EXISTS edges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    from_id TEXT NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
    to_id TEXT NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
    type TEXT NOT NULL,
    weight REAL NOT NULL DEFAULT 1.0,
    created_at TEXT NOT NULL
  );

  CREATE INDEX IF NOT EXISTS idx_edges_from_id ON edges(from_id);
  CREATE INDEX IF NOT EXISTS idx_edges_to_id ON edges(to_id);

  CREATE TABLE IF NOT EXISTS clusters (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    embedding TEXT,
    rolling_summary TEXT NOT NULL DEFAULT '',
    decision_log TEXT NOT NULL DEFAULT '[]',
    key_entities TEXT NOT NULL DEFAULT '[]',
    card_ids TEXT NOT NULL DEFAULT '[]',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
  );
SQL

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from VectorUtils

#similarity

Methods inherited from Base

#similarity

Constructor Details

#initialize(directory) ⇒ SqliteAdapter

Returns a new instance of SqliteAdapter.

Parameters:

  • directory (String) —

    Root directory for memory storage



85
86
87
88
89
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 85

def initialize(directory)
  super()
  @directory = File.expand_path(directory)
  @db = nil
end

Instance Attribute Details

#directory ⇒ String (readonly)

Returns Root directory for storage.

Returns:

  • (String) —

    Root directory for storage



37
38
39
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 37

def directory
  @directory
end

Instance Method Details

#delete_card(id) ⇒ void

This method returns an undefined value.

Parameters:

  • id (String) —

    Card ID



130
131
132
133
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 130

def delete_card(id)
  @db.execute("DELETE FROM cards WHERE id = ?", [id])
  @db.execute("DELETE FROM vec_cards WHERE card_id = ?", [id])
end

#delete_edges_for(card_id) ⇒ void

This method returns an undefined value.

Parameters:

  • card_id (String) —

    Card ID



184
185
186
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 184

def delete_edges_for(card_id)
  @db.execute("DELETE FROM edges WHERE from_id = ? OR to_id = ?", [card_id, card_id])
end

#edges_for(card_id, type: nil) ⇒ Array<Edge>

Parameters:

  • card_id (String) —

    Card ID

  • type (Symbol, nil) (defaults to: nil) —

    Filter by edge type

Returns:



167
168
169
170
171
172
173
174
175
176
177
178
179
180
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 167

def edges_for(card_id, type: nil)
  rows = if type
    @db.execute(
      "SELECT * FROM edges WHERE (from_id = ? OR to_id = ?) AND type = ?",
      [card_id, card_id, type.to_s],
    )
  else
    @db.execute(
      "SELECT * FROM edges WHERE from_id = ? OR to_id = ?",
      [card_id, card_id],
    )
  end
  rows.map { |row| edge_from_row(row) }
end

#list_cards(prefix: nil) ⇒ Array<Card>

Parameters:

  • prefix (String, nil) (defaults to: nil) —

    ID prefix filter

Returns:



137
138
139
140
141
142
143
144
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 137

def list_cards(prefix: nil)
  rows = if prefix
    @db.execute("SELECT * FROM cards WHERE id LIKE ?", ["#{prefix}%"])
  else
    @db.execute("SELECT * FROM cards")
  end
  rows.map { |row| card_from_row(row) }
end

#list_cards_for_compression(max_level: 3) ⇒ Array<Card>

Returns Cards eligible for compression.

Parameters:

  • max_level (Integer) (defaults to: 3) —

    Maximum compression level to include

Returns:

  • (Array<Card>) —

    Cards eligible for compression



148
149
150
151
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 148

def list_cards_for_compression(max_level: 3)
  rows = @db.execute("SELECT * FROM cards WHERE compression_level <= ?", [max_level])
  rows.map { |row| card_from_row(row) }
end

#list_clusters ⇒ Array<Cluster>

Returns:



218
219
220
221
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 218

def list_clusters
  rows = @db.execute("SELECT * FROM clusters")
  rows.map { |row| cluster_from_row(row) }
end

#load ⇒ void

This method returns an undefined value.

Load state from SQLite and initialize vec0 index

Opens the database, creates schema if needed, loads the sqlite-vec extension, and creates the vec0 virtual table. If vec_cards is empty but cards have embeddings, auto-rebuilds the index (handles FAISS migration and first-run).



305
306
307
308
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 305

def load
  load_sqlite!
  auto_rebuild_vec_if_needed!
end

#read_card(id) ⇒ Card?

Parameters:

  • id (String) —

    Card ID

Returns:



121
122
123
124
125
126
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 121

def read_card(id)
  row = @db.execute("SELECT * FROM cards WHERE id = ?", [id]).first
  return unless row

  card_from_row(row)
end

#read_cluster(id) ⇒ Cluster?

Parameters:

  • id (String) —

    Cluster ID

Returns:



210
211
212
213
214
215
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 210

def read_cluster(id)
  row = @db.execute("SELECT * FROM clusters WHERE id = ?", [id]).first
  return unless row

  cluster_from_row(row)
end

#rebuild_index ⇒ void

This method returns an undefined value.

Rebuild the vec0 index from all stored card embeddings

Drops and recreates the vec_cards virtual table, then re-inserts all card embeddings from the cards table.



260
261
262
263
264
265
266
267
268
269
270
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 260

def rebuild_index
  @db.execute("DROP TABLE IF EXISTS vec_cards")
  create_vec_table!
  @db.execute("SELECT id, embedding FROM cards WHERE embedding IS NOT NULL").each do |row|
    embedding = JSON.parse(row["embedding"])
    @db.execute(
      "INSERT INTO vec_cards(card_id, embedding) VALUES (?, ?)",
      [row["id"], embedding.pack("f*")],
    )
  end
end

#save ⇒ void

This method returns an undefined value.

Ensure the storage directory exists

SQLite with WAL mode is already durable — no external index files need saving.



293
294
295
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 293

def save
  FileUtils.mkdir_p(@directory)
end

#transaction { ... } ⇒ Object

Execute a block within a SQLite transaction

Uses IMMEDIATE mode to acquire a write lock at the start, preventing deadlocks in WAL mode.

Yields:

  • Block to execute within the transaction

Returns:

  • (Object) —

    Return value of the block



281
282
283
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 281

def transaction(&block)
  @db.transaction(:immediate, &block)
end

#vector_search(embedding, top_k:, threshold: 0.0) ⇒ Array<Hash>

Search the vec0 virtual table for similar vectors

sqlite-vec’s cosine distance returns values where 0 = identical and 2 = opposite. We convert to similarity: ‘1.0 - distance`.

Examples:

results = adapter.vector_search(query_embedding, top_k: 5, threshold: 0.7)
results.each { |r| puts "#{r[:id]}: #{r[:similarity]}" }

Parameters:

  • embedding (Array<Float>) —

    Query embedding

  • top_k (Integer) —

    Maximum number of results

  • threshold (Float) (defaults to: 0.0) —

    Minimum cosine similarity to include

Returns:

  • (Array<Hash>) —

    Array of ‘{ id: String, similarity: Float }`



238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 238

def vector_search(embedding, top_k:, threshold: 0.0)
  rows = @db.execute(
    "SELECT card_id, distance FROM vec_cards WHERE embedding MATCH ? AND k = ?",
    [embedding.pack("f*"), top_k],
  )

  results = []
  rows.each do |row|
    sim = 1.0 - row["distance"]
    next if sim < threshold

    results << { id: row["card_id"], similarity: sim }
  end
  results
end

#write_card(card) ⇒ void

This method returns an undefined value.

Parameters:

  • card (Card) —

    Card to write (insert or update)



95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 95

def write_card(card)
  @db.execute(<<~SQL, card_params(card))
    INSERT INTO cards (id, text, type, entities, source_turn_ids, embedding,
                       importance, confidence, access_count, last_accessed,
                       dwell, compression_level, canonical_id, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET
      text = excluded.text,
      type = excluded.type,
      entities = excluded.entities,
      source_turn_ids = excluded.source_turn_ids,
      embedding = excluded.embedding,
      importance = excluded.importance,
      confidence = excluded.confidence,
      access_count = excluded.access_count,
      last_accessed = excluded.last_accessed,
      dwell = excluded.dwell,
      compression_level = excluded.compression_level,
      canonical_id = excluded.canonical_id,
      updated_at = excluded.updated_at
  SQL
  upsert_vec_embedding(card.id, card.embedding) if card.embedding
end

#write_cluster(cluster) ⇒ void

This method returns an undefined value.

Parameters:

  • cluster (Cluster) —

    Cluster to write (insert or update)



192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 192

def write_cluster(cluster)
  @db.execute(<<~SQL, cluster_params(cluster))
    INSERT INTO clusters (id, title, embedding, rolling_summary, decision_log,
                          key_entities, card_ids, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET
      title = excluded.title,
      embedding = excluded.embedding,
      rolling_summary = excluded.rolling_summary,
      decision_log = excluded.decision_log,
      key_entities = excluded.key_entities,
      card_ids = excluded.card_ids,
      updated_at = excluded.updated_at
  SQL
end

#write_edge(edge) ⇒ void

This method returns an undefined value.

Parameters:

  • edge (Edge) —

    Edge to write



157
158
159
160
161
162
 
# File 'lib/swarm_sdk/v3/memory/adapters/sqlite_adapter.rb', line 157

def write_edge(edge)
  @db.execute(
    "INSERT INTO edges (from_id, to_id, type, weight, created_at) VALUES (?, ?, ?, ?, ?)",
    [edge.from_id, edge.to_id, edge.type.to_s, edge.weight, edge.created_at.iso8601],
  )
end