Class: Llmemory::LongTerm::Procedural::Storages::DatabaseStorage

Inherits:
Base
  • Object
show all
Includes:
Crypto::FieldHelpers
Defined in:
lib/llmemory/long_term/procedural/storages/database_storage.rb

Overview

PostgreSQL backend. Each skill is stored as a JSONB data document (plus id/user_id/created_at and a denormalized search_text), mirroring the file-based DatabaseStorage pattern.

Instance Method Summary collapse

Constructor Details

#initialize(database_url: nil, cipher: nil) ⇒ DatabaseStorage

Returns a new instance of DatabaseStorage.



19
20
21
22
23
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 19

def initialize(database_url: nil, cipher: nil)
  @database_url = database_url || Llmemory.configuration.database_url
  @connection = nil
  @cipher = cipher || Llmemory.build_cipher
end

Instance Method Details

#archive_skills(user_id, ids) ⇒ Object 



137
138
139
140
141
142
143
144
145
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 137

def archive_skills(user_id, ids)
  ensure_tables!
  Array(ids).sum do |id|
    conn.exec_params(
      "UPDATE llmemory_skills SET archived_at = NOW() WHERE user_id = $1 AND id = $2 AND archived_at IS NULL",
      [user_id, id]
    ).cmd_tuples
  end
end

#count_skills(user_id) ⇒ Object 



125
126
127
128
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 125

def count_skills(user_id)
  ensure_tables!
  conn.exec_params("SELECT COUNT(*) AS c FROM llmemory_skills WHERE user_id = $1 AND archived_at IS NULL", [user_id]).first["c"].to_i
end

#delete_skills(user_id, ids) ⇒ Object 



130
131
132
133
134
135
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 130

def delete_skills(user_id, ids)
  ensure_tables!
  Array(ids).sum do |id|
    conn.exec_params("DELETE FROM llmemory_skills WHERE user_id = $1 AND id = $2", [user_id, id]).cmd_tuples
  end
end

#expired_skill_ids(user_id, cutoff:) ⇒ Object 



147
148
149
150
151
152
153
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 147

def expired_skill_ids(user_id, cutoff:)
  ensure_tables!
  conn.exec_params(
    "SELECT id FROM llmemory_skills WHERE user_id = $1 AND archived_at IS NULL AND created_at < $2",
    [user_id, cutoff.iso8601]
  ).map { |r| r["id"] }
end

#find_skills_by_name(user_id, name) ⇒ Object 



83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 83

def find_skills_by_name(user_id, name)
  ensure_tables!
  if cipher.enabled?
    rows = conn.exec_params(
      "SELECT data FROM llmemory_skills WHERE user_id = $1 AND archived_at IS NULL AND name_det = $2",
      [user_id, enc_det(name.to_s)]
    )
    indexed = rows.map { |r| parse_data(r["data"]) }
    legacy_rows = conn.exec_params(
      "SELECT data FROM llmemory_skills WHERE user_id = $1 AND archived_at IS NULL AND name_det IS NULL",
      [user_id]
    )
    legacy = legacy_rows.map { |r| parse_data(r["data"]) }.select do |skill|
      (skill[:name] || skill["name"]).to_s == name.to_s
    end
    by_id = {}
    (indexed + legacy).each { |skill| by_id[skill[:id] || skill["id"]] = skill }
    by_id.values
  else
    conn.exec_params(
      "SELECT data FROM llmemory_skills WHERE user_id = $1 AND archived_at IS NULL AND data->>'name' = $2",
      [user_id, name.to_s]
    ).map { |r| parse_data(r["data"]) }
  end
end

#get_skill(user_id, id) ⇒ Object 



41
42
43
44
45
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 41

def get_skill(user_id, id)
  ensure_tables!
  rows = conn.exec_params("SELECT data FROM llmemory_skills WHERE user_id = $1 AND id = $2", [user_id, id])
  rows.any? ? parse_data(rows.first["data"]) : nil
end

#list_skills(user_id, limit: nil, offset: nil) ⇒ Object 



47
48
49
50
51
52
53
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 47

def list_skills(user_id, limit: nil, offset: nil)
  ensure_tables!
  sql = "SELECT data FROM llmemory_skills WHERE user_id = $1 AND archived_at IS NULL ORDER BY created_at DESC"
  sql += " LIMIT #{limit.to_i}" if limit && limit.to_i.positive?
  sql += " OFFSET #{offset.to_i}" if offset && offset.to_i.positive?
  conn.exec_params(sql, [user_id]).map { |r| parse_data(r["data"]) }
end

#list_users ⇒ Object 



155
156
157
158
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 155

def list_users
  ensure_tables!
  conn.exec("SELECT DISTINCT user_id FROM llmemory_skills").map { |r| r["user_id"] }
end

#record_outcome(user_id, skill_id, success:) ⇒ Object 



109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 109

def record_outcome(user_id, skill_id, success:)
  ensure_tables!
  data = get_skill(user_id, skill_id)
  return nil unless data
  key = success ? :success_count : :failure_count
  data[key] = (data[key] || 0).to_i + 1
  data[:updated_at] = Time.now.utc.iso8601
  search = searchable_text(data)
  name = (data[:name] || data["name"]).to_s
  conn.exec_params(
    "UPDATE llmemory_skills SET data = $3::jsonb, search_text = $4, search_tokens = $5, name_det = $6 WHERE user_id = $1 AND id = $2",
    [user_id, skill_id, store_data(data), enc(search), search_tokens_for(search), enc_det(name)]
  )
  data
end

#save_skill(user_id, skill) ⇒ Object 



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 25

def save_skill(user_id, skill)
  ensure_tables!
  id = skill[:id] || skill["id"] || "skill_#{SecureRandom.hex(8)}"
  data = symbolize(skill).merge(id: id, user_id: user_id)
  data[:created_at] ||= Time.now.utc.iso8601
  search = searchable_text(data)
  name = (data[:name] || data["name"]).to_s
  conn.exec_params(
    "INSERT INTO llmemory_skills (id, user_id, data, search_text, search_tokens, name_det, created_at) " \
    "VALUES ($1, $2, $3::jsonb, $4, $5, $6, $7) " \
    "ON CONFLICT (id) DO UPDATE SET data = $3::jsonb, search_text = $4, search_tokens = $5, name_det = $6",
    [id, user_id, store_data(data), enc(search), search_tokens_for(search), enc_det(name), created_at_value(data)]
  )
  id
end

#search_skills(user_id, query) ⇒ Object 



55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/llmemory/long_term/procedural/storages/database_storage.rb', line 55

def search_skills(user_id, query)
  ensure_tables!
  tokens = Llmemory::Tokenizer.tokenize(query)
  return conn.exec_params(
    "SELECT data FROM llmemory_skills WHERE user_id = $1 AND archived_at IS NULL ORDER BY created_at DESC",
    [user_id]
  ).map { |r| parse_data(r["data"]) } if tokens.empty?

  suffix, params = blind_token_filter("search_text", query, 2, search_tokens_column: cipher.enabled? ? "search_tokens" : nil)
  rows = conn.exec_params(
    "SELECT data FROM llmemory_skills WHERE user_id = $1 AND archived_at IS NULL#{suffix} ORDER BY created_at DESC",
    [user_id, *params]
  )
  skills = rows.map { |r| parse_data(r["data"]) }
  return skills unless cipher.enabled?

  legacy_rows = conn.exec_params(
    "SELECT data FROM llmemory_skills WHERE user_id = $1 AND archived_at IS NULL AND search_tokens IS NULL",
    [user_id]
  )
  legacy = legacy_rows.map { |r| parse_data(r["data"]) }.select do |skill|
    Llmemory::Tokenizer.matches?(searchable_text(skill), query)
  end
  by_id = {}
  (skills + legacy).each { |skill| by_id[skill[:id] || skill["id"]] = skill }
  by_id.values
end