Class: KairosMcp::Storage::SqliteBackend

Inherits:
Backend
  • Object
show all
Defined in:
lib/kairos_mcp/storage/sqlite_backend.rb

Overview

SQLite-based storage backend (optional, for team use)

This backend provides:

  • ACID transactions for data integrity

  • Built-in locking for concurrent access

  • WAL mode for better read/write concurrency

Requires: gem install sqlite3

Storage:

  • Blockchain: blocks table

  • Action logs: action_logs table

  • Knowledge metadata: knowledge_meta table (content still in files)

Constant Summary collapse

SCHEMA = 
<<~SQL
  -- Blockchain blocks
  CREATE TABLE IF NOT EXISTS blocks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      block_index INTEGER NOT NULL UNIQUE,
      timestamp TEXT NOT NULL,
      data TEXT NOT NULL,
      previous_hash TEXT NOT NULL,
      merkle_root TEXT NOT NULL,
      hash TEXT NOT NULL,
      created_at TEXT DEFAULT (datetime('now'))
  );

  -- Action logs
  CREATE TABLE IF NOT EXISTS action_logs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      timestamp TEXT NOT NULL,
      action TEXT NOT NULL,
      skill_id TEXT,
      layer TEXT,
      details TEXT,
      created_at TEXT DEFAULT (datetime('now'))
  );

  -- Knowledge metadata (content is in files)
  CREATE TABLE IF NOT EXISTS knowledge_meta (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      content_hash TEXT NOT NULL,
      version TEXT,
      description TEXT,
      tags TEXT,
      is_archived INTEGER DEFAULT 0,
      archived_at TEXT,
      archived_reason TEXT,
      superseded_by TEXT,
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now'))
  );

  -- Indexes
  CREATE INDEX IF NOT EXISTS idx_blocks_hash ON blocks(hash);
  CREATE INDEX IF NOT EXISTS idx_blocks_index ON blocks(block_index);
  CREATE INDEX IF NOT EXISTS idx_action_logs_timestamp ON action_logs(timestamp);
  CREATE INDEX IF NOT EXISTS idx_action_logs_skill ON action_logs(skill_id);
  CREATE INDEX IF NOT EXISTS idx_knowledge_meta_archived ON knowledge_meta(is_archived);
  CREATE INDEX IF NOT EXISTS idx_knowledge_meta_hash ON knowledge_meta(content_hash);
SQL

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods inherited from Backend

create, default, load_config, register, unregister

Constructor Details

#initialize(config = {}) ⇒ SqliteBackend

Returns a new instance of SqliteBackend.



76
77
78
79
80
81
82
83
84
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 76

def initialize(config = {})
  @db_path = config[:path] || config['path'] || KairosMcp.sqlite_path
  @wal_mode = config[:wal_mode] != false && config['wal_mode'] != false

  require 'sqlite3'
  setup_database
rescue LoadError => e
  raise LoadError, "SQLite backend requires sqlite3 gem: #{e.message}"
end

Instance Attribute Details

#db ⇒ Object (readonly)

Get the raw database connection (for advanced operations)



356
357
358
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 356

def db
  @db
end

#db_path ⇒ Object (readonly)

Get the database path



353
354
355
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 353

def db_path
  @db_path
end

Instance Method Details

#action_history(limit: 50) ⇒ Object 



172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 172

def action_history(limit: 50)
  rows = @db.execute(<<~SQL, [limit])
    SELECT timestamp, action, skill_id, details
    FROM action_logs
    ORDER BY id DESC
    LIMIT ?
  SQL

  rows.reverse.map do |row|
    {
      timestamp: row[0],
      action: row[1],
      skill_id: row[2],
      details: row[3] ? (JSON.parse(row[3]) rescue row[3]) : nil
    }
  end
rescue SQLite3::Exception => e
  warn "[SqliteBackend] Failed to get action history: #{e.message}"
  []
end

#all_blocks ⇒ Object 



145
146
147
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 145

def all_blocks
  load_blocks || []
end

#backend_type ⇒ Object 



348
349
350
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 348

def backend_type
  :sqlite
end

#clear_action_log! ⇒ Object 



193
194
195
196
197
198
199
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 193

def clear_action_log!
  @db.execute("DELETE FROM action_logs")
  true
rescue SQLite3::Exception => e
  warn "[SqliteBackend] Failed to clear action log: #{e.message}"
  false
end

#delete_knowledge_meta(name) ⇒ Object 



311
312
313
314
315
316
317
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 311

def delete_knowledge_meta(name)
  @db.execute("DELETE FROM knowledge_meta WHERE name = ?", [name])
  true
rescue SQLite3::Exception => e
  warn "[SqliteBackend] Failed to delete knowledge meta: #{e.message}"
  false
end

#execute(sql, params = []) ⇒ Object

Execute raw SQL (for migrations, etc.)



359
360
361
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 359

def execute(sql, params = [])
  @db.execute(sql, params)
end

#get_knowledge_meta(name) ⇒ Object 



253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 253

def get_knowledge_meta(name)
  row = @db.get_first_row(<<~SQL, [name])
    SELECT name, content_hash, version, description, tags,
           is_archived, archived_at, archived_reason, superseded_by,
           created_at, updated_at
    FROM knowledge_meta
    WHERE name = ?
  SQL

  return nil unless row

  {
    name: row[0],
    content_hash: row[1],
    version: row[2],
    description: row[3],
    tags: row[4] ? (JSON.parse(row[4]) rescue []) : [],
    is_archived: row[5] == 1,
    archived_at: row[6],
    archived_reason: row[7],
    superseded_by: row[8],
    created_at: row[9],
    updated_at: row[10]
  }
rescue SQLite3::Exception => e
  warn "[SqliteBackend] Failed to get knowledge meta: #{e.message}"
  nil
end

#list_knowledge_meta ⇒ Object 



282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 282

def list_knowledge_meta
  rows = @db.execute(<<~SQL)
    SELECT name, content_hash, version, description, tags,
           is_archived, archived_at, archived_reason, superseded_by,
           created_at, updated_at
    FROM knowledge_meta
    ORDER BY name ASC
  SQL

  rows.map do |row|
    {
      name: row[0],
      content_hash: row[1],
      version: row[2],
      description: row[3],
      tags: row[4] ? (JSON.parse(row[4]) rescue []) : [],
      is_archived: row[5] == 1,
      archived_at: row[6],
      archived_reason: row[7],
      superseded_by: row[8],
      created_at: row[9],
      updated_at: row[10]
    }
  end
rescue SQLite3::Exception => e
  warn "[SqliteBackend] Failed to list knowledge meta: #{e.message}"
  []
end

#load_blocks ⇒ Object

Block Operations



90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 90

def load_blocks
  rows = @db.execute(<<~SQL)
    SELECT block_index, timestamp, data, previous_hash, merkle_root, hash
    FROM blocks
    ORDER BY block_index ASC
  SQL

  return nil if rows.empty?

  rows.map do |row|
    {
      index: row[0],
      timestamp: row[1],
      data: JSON.parse(row[2]),
      previous_hash: row[3],
      merkle_root: row[4],
      hash: row[5]
    }
  end
rescue SQLite3::Exception => e
  warn "[SqliteBackend] Failed to load blocks: #{e.message}"
  nil
end

#ready? ⇒ Boolean

Utility Methods

Returns:

  • (Boolean) 


342
343
344
345
346
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 342

def ready?
  @db && @db.execute("SELECT 1").first == [1]
rescue StandardError
  false
end

#record_action(entry) ⇒ Object

Action Log Operations



153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 153

def record_action(entry)
  params = [
    entry[:timestamp] || Time.now.iso8601,
    entry[:action],
    entry[:skill_id],
    entry[:layer],
    entry[:details]&.to_json
  ]
  sql = <<~SQL
    INSERT INTO action_logs (timestamp, action, skill_id, layer, details)
    VALUES (?, ?, ?, ?, ?)
  SQL
  @db.execute(sql, params)
  true
rescue SQLite3::Exception => e
  warn "[SqliteBackend] Failed to record action: #{e.message}"
  false
end

#save_all_blocks(blocks) ⇒ Object 



135
136
137
138
139
140
141
142
143
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 135

def save_all_blocks(blocks)
  @db.transaction do
    blocks.each { |block| save_block(block) }
  end
  true
rescue SQLite3::Exception => e
  warn "[SqliteBackend] Failed to save all blocks: #{e.message}"
  false
end

#save_block(block) ⇒ Object 



114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 114

def save_block(block)
  data = block.is_a?(Hash) ? block : block.to_h
  params = [
    data[:index],
    data[:timestamp].to_s,
    data[:data].to_json,
    data[:previous_hash],
    data[:merkle_root],
    data[:hash]
  ]
  sql = <<~SQL
    INSERT OR REPLACE INTO blocks (block_index, timestamp, data, previous_hash, merkle_root, hash)
    VALUES (?, ?, ?, ?, ?, ?)
  SQL
  @db.execute(sql, params)
  true
rescue SQLite3::Exception => e
  warn "[SqliteBackend] Failed to save block: #{e.message}"
  false
end

#save_knowledge_meta(name, meta) ⇒ Object

Knowledge Meta Operations



205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 205

def save_knowledge_meta(name, meta)
  existing = get_knowledge_meta(name)

  if existing
    params = [
      meta[:content_hash],
      meta[:version],
      meta[:description],
      meta[:tags]&.to_json,
      meta[:is_archived] ? 1 : 0,
      meta[:archived_at],
      meta[:archived_reason],
      meta[:superseded_by],
      name
    ]
    sql = <<~SQL
      UPDATE knowledge_meta
      SET content_hash = ?, version = ?, description = ?, tags = ?,
          is_archived = ?, archived_at = ?, archived_reason = ?, superseded_by = ?,
          updated_at = datetime('now')
      WHERE name = ?
    SQL
    @db.execute(sql, params)
  else
    params = [
      name,
      meta[:content_hash],
      meta[:version],
      meta[:description],
      meta[:tags]&.to_json,
      meta[:is_archived] ? 1 : 0,
      meta[:archived_at],
      meta[:archived_reason],
      meta[:superseded_by]
    ]
    sql = <<~SQL
      INSERT INTO knowledge_meta (name, content_hash, version, description, tags,
                                  is_archived, archived_at, archived_reason, superseded_by)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    SQL
    @db.execute(sql, params)
  end
  true
rescue SQLite3::Exception => e
  warn "[SqliteBackend] Failed to save knowledge meta: #{e.message}"
  false
end

#transaction(&block) ⇒ Object

Run a transaction



364
365
366
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 364

def transaction(&block)
  @db.transaction(&block)
end

#update_knowledge_archived(name, archived, reason: nil) ⇒ Object 



319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
 
# File 'lib/kairos_mcp/storage/sqlite_backend.rb', line 319

def update_knowledge_archived(name, archived, reason: nil)
  params = [
    archived ? 1 : 0,
    archived ? Time.now.iso8601 : nil,
    reason,
    name
  ]
  sql = <<~SQL
    UPDATE knowledge_meta
    SET is_archived = ?, archived_at = ?, archived_reason = ?, updated_at = datetime('now')
    WHERE name = ?
  SQL
  @db.execute(sql, params)
  true
rescue SQLite3::Exception => e
  warn "[SqliteBackend] Failed to update knowledge archived status: #{e.message}"
  false
end