Module: Sentiero::Stores::SQLite::Schema

Defined in:
lib/sentiero/stores/sqlite/schema.rb

Overview

Table/index definitions for the store's SQLite connections.

Constant Summary collapse

EVENTS_COLUMNS =

The events.type column (denormalized rrweb event type) sits BEFORE the data blob on purpose: SQLite reads record columns left to right, so a typed scan can check type without ever touching the (often multi-page overflow) data payload of non-matching rows.

<<~SQL
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id TEXT NOT NULL,
  window_id TEXT NOT NULL,
  timestamp REAL,
  type INTEGER,
  data TEXT NOT NULL
SQL
SCHEMA_VERSION =

user_version 1 = every large payload is zlib-compressed (PayloadCodec).

1

Class Method Summary collapse

Class Method Details

.create(db) ⇒ Object 



21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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
82
83
84
85
86
87
 
# File 'lib/sentiero/stores/sqlite/schema.rb', line 21

def self.create(db)
  db.execute_batch(<<~SQL)
    CREATE TABLE IF NOT EXISTS sessions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      session_id TEXT NOT NULL UNIQUE,
      created_at REAL NOT NULL,
      updated_at REAL NOT NULL,
      first_event_at REAL,
      last_event_at REAL,
      metadata TEXT
    );

    CREATE INDEX IF NOT EXISTS idx_sessions_session_id ON sessions(session_id);
    CREATE INDEX IF NOT EXISTS idx_sessions_updated_at ON sessions(updated_at);

    CREATE TABLE IF NOT EXISTS events (
      #{EVENTS_COLUMNS}
    );

    CREATE INDEX IF NOT EXISTS idx_events_session_window_ts ON events(session_id, window_id, timestamp);

    CREATE TABLE IF NOT EXISTS problems (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      fingerprint TEXT NOT NULL UNIQUE,
      project TEXT NOT NULL,
      exception_class TEXT NOT NULL,
      title TEXT NOT NULL,
      message TEXT,
      count INTEGER NOT NULL,
      status TEXT NOT NULL,
      first_seen REAL NOT NULL,
      last_seen REAL NOT NULL,
      resolved_at REAL
    );
    CREATE INDEX IF NOT EXISTS idx_problems_project ON problems(project);
    CREATE INDEX IF NOT EXISTS idx_problems_status ON problems(status);
    CREATE INDEX IF NOT EXISTS idx_problems_last_seen ON problems(last_seen);

    CREATE TABLE IF NOT EXISTS occurrences (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      occurrence_id TEXT NOT NULL,
      fingerprint TEXT NOT NULL,
      session_id TEXT,
      timestamp REAL NOT NULL,
      data TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_occurrences_fp_ts ON occurrences(fingerprint, timestamp);
    CREATE INDEX IF NOT EXISTS idx_occurrences_session ON occurrences(session_id);

    CREATE TABLE IF NOT EXISTS server_events (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      event_id TEXT NOT NULL,
      project TEXT NOT NULL,
      name TEXT NOT NULL,
      level TEXT,
      session_id TEXT,
      timestamp REAL NOT NULL,
      data TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_server_events_event_id ON server_events(event_id);
    CREATE INDEX IF NOT EXISTS idx_server_events_project ON server_events(project);
    CREATE INDEX IF NOT EXISTS idx_server_events_session ON server_events(session_id);
  SQL

  migrate_events_type!(db)
  migrate_compress_payloads!(db)
end

.migrate_compress_payloads!(db) ⇒ Object

One-time pass compressing pre-existing plain-TEXT payloads. Batched in short transactions so a live recorder's ingest writes interleave instead of timing out behind one giant lock. Guarded by PRAGMA user_version, so subsequent opens skip the table scan. Frees pages for reuse but does not shrink the file — VACUUM manually to reclaim disk.



124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
 
# File 'lib/sentiero/stores/sqlite/schema.rb', line 124

def self.migrate_compress_payloads!(db)
  return if db.get_first_value("PRAGMA user_version").to_i >= SCHEMA_VERSION

  loop do
    rows = db.execute(
      "SELECT id, data FROM events WHERE typeof(data) = 'text' AND LENGTH(data) >= ? LIMIT 500",
      [PayloadCodec::COMPRESSION_THRESHOLD]
    )
    break if rows.empty?

    db.transaction(:immediate) do
      rows.each do |row|
        db.execute("UPDATE events SET data = ? WHERE id = ?", [PayloadCodec.encode(row["data"]), row["id"]])
      end
    end
  end
  db.execute("PRAGMA user_version = #{SCHEMA_VERSION}")
end

.migrate_events_type!(db) ⇒ Object

Databases created before the events.type column existed get a one-time table rebuild (not ALTER ADD COLUMN — that would append type AFTER the data blob, defeating the column-order optimization above). Backfills type from each row's JSON payload.



93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/sentiero/stores/sqlite/schema.rb', line 93

def self.migrate_events_type!(db)
  columns = db.execute("PRAGMA table_info(events)").map { |row| row["name"] }
  return if columns.include?("type")

  db.transaction(:immediate) do
    db.execute_batch(<<~SQL)
      CREATE TABLE events_migrated (
        #{EVENTS_COLUMNS}
      );
      INSERT INTO events_migrated (id, session_id, window_id, timestamp, type, data)
        SELECT id, session_id, window_id, timestamp,
               CASE WHEN json_type(data, '$.type') = 'integer'
                    THEN json_extract(data, '$.type') END,
               data
        FROM events;
      DROP TABLE events;
      ALTER TABLE events_migrated RENAME TO events;
      CREATE INDEX idx_events_session_window_ts ON events(session_id, window_id, timestamp);
    SQL
  end
end