Class: Sxn::Database::SessionDatabase

Inherits:
Object
  • Object
show all
Defined in:
lib/sxn/database/session_database.rb

Overview

SessionDatabase provides high-performance SQLite-based session storage with O(1) indexed lookups, replacing filesystem scanning.

Features:

  • ACID transactions with rollback support

  • Prepared statements for security and performance

  • Full-text search with optimization

  • JSON metadata storage with indexing

  • Connection pooling and concurrent access handling

  • Automatic migrations and schema versioning

Performance characteristics:

  • Session creation: < 10ms

  • Session listing: < 5ms for 1000+ sessions

  • Search queries: < 20ms with proper indexing

  • Bulk operations: < 100ms for 100 sessions

Constant Summary collapse

SCHEMA_VERSION =

Current database schema version for migrations

2
DEFAULT_DB_PATH =

Default database path relative to sxn config directory

".sxn/sessions.db"
VALID_STATUSES =

Session status constants

%w[active inactive archived].freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(db_path = nil, config = {}) ⇒ SessionDatabase

Initialize database connection and ensure schema is current

Parameters:

  • db_path (String, Pathname) (defaults to: nil) —

    Path to SQLite database file

  • config (Hash) (defaults to: {}) —

    Database configuration options

Options Hash (config):

  • :readonly (Boolean) — default: false —

    Open database in readonly mode

  • :timeout (Integer) — default: 30000 —

    Busy timeout in milliseconds

  • :auto_vacuum (Boolean) — default: true —

    Enable auto vacuum



45
46
47
48
49
50
51
52
53
 
# File 'lib/sxn/database/session_database.rb', line 45

def initialize(db_path = nil, config = {})
  @db_path = resolve_db_path(db_path)
  @config = default_config.merge(config)
  @prepared_statements = {}

  ensure_directory_exists
  initialize_connection
  setup_database
end

Instance Attribute Details

#config ⇒ Object (readonly)

Returns the value of attribute config.



36
37
38
 
# File 'lib/sxn/database/session_database.rb', line 36

def config
  @config
end

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



36
37
38
 
# File 'lib/sxn/database/session_database.rb', line 36

def connection
  @connection
end

#db_path ⇒ Object (readonly)

Returns the value of attribute db_path.



36
37
38
 
# File 'lib/sxn/database/session_database.rb', line 36

def db_path
  @db_path
end

Instance Method Details

#close ⇒ Object

Close database connection and cleanup prepared statements



363
364
365
366
367
368
 
# File 'lib/sxn/database/session_database.rb', line 363

def close
  @prepared_statements.each_value(&:close)
  @prepared_statements.clear
  @connection&.close
  @connection = nil
end

#create_session(session_data) ⇒ String

Create a new session with validation and conflict detection

Parameters:

  • session_data (Hash) —

    Session attributes

Options Hash (session_data):

  • :name (String) —

    Required session name (must be unique)

  • :status (String) — default: 'active' —

    Session status

  • :linear_task (String) —

    Linear ticket ID

  • :description (String) —

    Session description

  • :tags (Array<String>) —

    Session tags

  • :metadata (Hash) —

    Additional metadata

Returns:

  • (String) —

    Generated session ID

Raises:



67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/sxn/database/session_database.rb', line 67

def create_session(session_data)
  validate_session_data!(session_data)

  # Use provided session ID if available, otherwise generate one
  session_id = session_data[:id] || generate_session_id
  timestamp = Time.now.utc.iso8601(6) # 6 decimal places for microseconds

  with_transaction do
    stmt = prepare_statement(:create_session, "      INSERT INTO sessions (\n        id, name, created_at, updated_at, status,\#{\" \"}\n        linear_task, description, tags, metadata, worktrees, projects\n      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n    SQL\n\n    stmt.execute(\n      session_id,\n      session_data[:name],\n      timestamp,\n      timestamp,\n      session_data[:status] || \"active\",\n      session_data[:linear_task],\n      session_data[:description],\n      serialize_tags(session_data[:tags]),\n      serialize_metadata(session_data[:metadata]),\n      serialize_metadata(session_data[:worktrees] || {}),\n      serialize_tags(session_data[:projects] || [])\n    )\n  end\n\n  session_id\nrescue SQLite3::ConstraintException => e\n  if e.message.include?(\"name\")\n    raise Sxn::Database::DuplicateSessionError,\n          \"Session with name '\#{session_data[:name]}' already exists\"\n  end\n  raise\nend\n")

#delete_session(session_id, cascade: true) ⇒ Boolean

Delete session with cascade options

Parameters:

  • session_id (String) —

    Session ID to delete

  • cascade (Boolean) (defaults to: true) —

    Whether to delete related records

Returns:

  • (Boolean) —

    True if session was deleted

Raises:



221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
 
# File 'lib/sxn/database/session_database.rb', line 221

def delete_session(session_id, cascade: true)
  with_transaction do
    # Check if session exists
    get_session(session_id)

    # Delete related records if cascade requested
    if cascade
      delete_session_worktrees(session_id)
      delete_session_files(session_id)
    end

    # Delete the session
    stmt = prepare_statement(:delete_session, "DELETE FROM sessions WHERE id = ?")
    stmt.execute(session_id)

    true
  end
rescue Sxn::Database::SessionNotFoundError
  false
end

#get_session(session_id) ⇒ Hash Also known as: get_session_by_id

Get single session by ID

Parameters:

  • session_id (String) —

    Session ID

Returns:

  • (Hash) —

    Session data

Raises:



298
299
300
301
302
303
304
305
306
307
308
 
# File 'lib/sxn/database/session_database.rb', line 298

def get_session(session_id)
  stmt = prepare_statement(:get_session, "SELECT * FROM sessions WHERE id = ?")
  row = stmt.execute(session_id).first

  unless row
    raise Sxn::Database::SessionNotFoundError,
          "Session with ID '#{session_id}' not found"
  end

  deserialize_session_row(row)
end

#get_session_by_name(name) ⇒ Hash?

Get session by name

Parameters:

  • name (String) —

    Session name to find

Returns:

  • (Hash, nil) —

    Session data hash or nil if not found



314
315
316
317
318
319
320
321
 
# File 'lib/sxn/database/session_database.rb', line 314

def get_session_by_name(name)
  stmt = prepare_statement(:get_session_by_name, "SELECT * FROM sessions WHERE name = ?")
  row = stmt.execute(name).first

  return nil unless row

  deserialize_session_row(row)
end

#list_sessions(filters: {}, sort: {}, limit: 100, offset: 0) ⇒ Array<Hash>

List sessions with filtering, sorting, and pagination

Parameters:

  • filters (Hash) (defaults to: {}) —

    Query filters

  • sort (Hash) (defaults to: {}) —

    Sorting options

  • limit (Integer) (defaults to: 100) —

    Maximum number of results (default: 100)

  • offset (Integer) (defaults to: 0) —

    Results offset for pagination (default: 0)

Options Hash (filters:):

  • :status (String) —

    Filter by session status

  • :tags (Array<String>) —

    Filter by tags (AND logic)

  • :linear_task (String) —

    Filter by Linear task ID

  • :created_after (Date) —

    Filter by creation date

  • :created_before (Date) —

    Filter by creation date

Options Hash (sort:):

  • :by (Symbol) — default: :updated_at —

    Sort field

  • :order (Symbol) — default: :desc —

    Sort direction (:asc or :desc)

Returns:

  • (Array<Hash>) —

    Array of session hashes



120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
 
# File 'lib/sxn/database/session_database.rb', line 120

def list_sessions(filters: {}, sort: {}, limit: 100, offset: 0)
  # Ensure filters is a Hash and parameters are correct types
  filters ||= {}
  limit = limit.to_i if limit
  offset = offset.to_i if offset

  query_parts = ["SELECT * FROM sessions"]
  params = []

  # Build WHERE clause from filters
  where_conditions = build_where_conditions(filters, params)
  query_parts << "WHERE #{where_conditions.join(" AND ")}" unless where_conditions.empty?

  # Build ORDER BY clause
  sort_field = sort[:by] || :updated_at
  sort_order = sort[:order] || :desc
  query_parts << "ORDER BY #{sort_field} #{sort_order.to_s.upcase}"

  # Add pagination - ensure these are integers
  query_parts << "LIMIT ? OFFSET ?"
  params.push(limit || 100, offset || 0)

  sql = query_parts.join(" ")

  execute_query(sql, params).map do |row|
    deserialize_session_row(row)
  end
end

#maintenance(tasks = %i[vacuum analyze])) ⇒ Hash

Execute database maintenance tasks

Parameters:

  • tasks (Array<Symbol>) (defaults to: %i[vacuum analyze])) —

    Tasks to perform (:vacuum, :analyze, :integrity_check)

Returns:

  • (Hash) —

    Results of maintenance tasks



342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
 
# File 'lib/sxn/database/session_database.rb', line 342

def maintenance(tasks = i[vacuum analyze])
  results = {}

  tasks.each do |task|
    case task
    when :vacuum
      connection.execute("VACUUM")
      results[:vacuum] = "completed"
    when :analyze
      connection.execute("ANALYZE")
      results[:analyze] = "completed"
    when :integrity_check
      integrity_result = connection.execute("PRAGMA integrity_check").first
      results[:integrity_check] = integrity_result[0]
    end
  end

  results
end

#search_sessions(query, filters: {}, limit: 50) ⇒ Array<Hash>

Search sessions with full-text search and filters

Parameters:

  • query (String) —

    Search query (searches name, description, tags)

  • filters (Hash) (defaults to: {}) —

    Additional filters (same as list_sessions)

  • limit (Integer) (defaults to: 50) —

    Maximum results (default: 50)

Returns:

  • (Array<Hash>) —

    Matching sessions with relevance scoring



248
249
250
251
252
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
281
282
283
284
285
286
287
288
289
290
291
 
# File 'lib/sxn/database/session_database.rb', line 248

def search_sessions(query, filters: {}, limit: 50)
  return list_sessions(filters: filters, limit: limit) if query.nil? || query.strip.empty?

  search_terms = query.strip.split(/\s+/).map { |term| "%#{term}%" }

  query_parts = ["    SELECT *,\#{\" \"}\n           (CASE\#{\" \"}\n             WHEN name LIKE ? THEN 100\n             WHEN description LIKE ? THEN 50\n             WHEN tags LIKE ? THEN 25\n             ELSE 0\n           END) as relevance_score\n    FROM sessions\n  SQL\n\n  params = search_terms * 3 # Each term checked against name, description, tags\n\n  # Build search conditions\n  search_conditions = []\n  search_terms.each do |term|\n    search_conditions << \"(name LIKE ? OR description LIKE ? OR tags LIKE ?)\"\n    params.push(term, term, term)\n  end\n\n  where_conditions = [\"(\#{search_conditions.join(\" AND \")})\"]\n\n  # Add additional filters\n  filter_conditions = build_where_conditions(filters, params)\n  where_conditions.concat(filter_conditions)\n\n  query_parts << \"WHERE \#{where_conditions.join(\" AND \")}\"\n  query_parts << \"ORDER BY relevance_score DESC, updated_at DESC\"\n  query_parts << \"LIMIT ?\"\n  params << limit\n\n  sql = query_parts.join(\" \")\n\n  execute_query(sql, params).map do |row|\n    session = deserialize_session_row(row)\n    session[:relevance_score] = row[\"relevance_score\"]\n    session\n  end\nend\n"]

#statistics ⇒ Hash

Get session statistics

Returns:

  • (Hash) —

    Statistics including counts by status, recent activity



329
330
331
332
333
334
335
336
 
# File 'lib/sxn/database/session_database.rb', line 329

def statistics
  {
    total_sessions: count_sessions,
    by_status: count_sessions_by_status,
    recent_activity: recent_session_activity,
    database_size: database_size_mb
  }
end

#update_session(session_id, updates = {}, expected_version: nil) ⇒ Boolean

Update session data with optimistic locking

Parameters:

  • session_id (String) —

    Session ID to update

  • updates (Hash) (defaults to: {}) —

    Fields to update

  • expected_version (String) (defaults to: nil) —

    Expected updated_at for optimistic locking

Returns:

  • (Boolean) —

    True if update succeeded

Raises:



157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
 
# File 'lib/sxn/database/session_database.rb', line 157

def update_session(session_id, updates = {}, expected_version: nil)
  validate_session_updates!(updates)

  # Use higher precision timestamp to ensure updates are detectable
  # Only set updated_at if not explicitly provided
  unless updates.key?(:updated_at)
    timestamp = Time.now.utc.iso8601(6) # 6 decimal places for microseconds
    updates = updates.merge(updated_at: timestamp)
  end

  with_transaction do
    # Check current version if optimistic locking requested
    if expected_version
      current = get_session(session_id)
      if current[:updated_at] != expected_version
        raise Sxn::Database::ConflictError,
              "Session was modified by another process"
      end
    end

    # Build dynamic UPDATE statement
    set_clauses = []
    params = []

    updates.each do |field, value|
      case field
      when :tags
        set_clauses << "tags = ?"
        params << serialize_tags(value)
      when :metadata
        set_clauses << "metadata = ?"
        params << serialize_metadata(value)
      when :worktrees
        set_clauses << "worktrees = ?"
        params << serialize_metadata(value)
      when :projects
        set_clauses << "projects = ?"
        params << serialize_tags(value)
      else
        set_clauses << "#{field} = ?"
        params << value
      end
    end

    params << session_id

    sql = "UPDATE sessions SET #{set_clauses.join(", ")} WHERE id = ?"
    connection.execute(sql, params)

    if connection.changes.zero?
      raise Sxn::Database::SessionNotFoundError,
            "Session with ID '#{session_id}' not found"
    end

    true
  end
end