SQL

CREATE TABLE "frames"  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  video_chunk_id INTEGER DEFAULT NULL,
  offset_index INTEGER NOT NULL DEFAULT 0,
  timestamp TIMESTAMP NOT NULL,
  name TEXT,
  app_name TEXT DEFAULT NULL,
  window_name TEXT DEFAULT NULL,
  focused BOOLEAN DEFAULT NULL,
  browser_url TEXT DEFAULT NULL,
  device_name TEXT NOT NULL DEFAULT '',
  sync_id TEXT,
  machine_id TEXT,
  synced_at DATETIME,
  -- New event-driven capture columns
    snapshot_path TEXT DEFAULT NULL,
  accessibility_text TEXT DEFAULT NULL,
  accessibility_tree_json TEXT DEFAULT NULL,
  content_hash INTEGER DEFAULT NULL,
  simhash INTEGER DEFAULT NULL,
  capture_trigger TEXT DEFAULT NULL,
  text_source TEXT DEFAULT NULL,
  cloud_blob_id TEXT DEFAULT NULL,
  full_text TEXT DEFAULT NULL,
  elements_ref_frame_id INTEGER DEFAULT NULL,
  FOREIGN KEY (video_chunk_id) REFERENCES video_chunks(id)
)

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ read-only
video_chunk_id INTEGER ✓ read-only
offset_index INTEGER read-only
timestamp TIMESTAMP read-only
name TEXT ✓ read-only
app_name TEXT ✓ read-only
window_name TEXT ✓ read-only
focused BOOLEAN ✓ read-only
browser_url TEXT ✓ read-only
device_name TEXT read-only
sync_id TEXT ✓ read-only
machine_id TEXT ✓ read-only
synced_at DATETIME ✓ read-only
snapshot_path TEXT ✓ read-only
accessibility_text TEXT ✓ read-only
accessibility_tree_json TEXT ✓ read-only
content_hash INTEGER ✓ read-only
simhash INTEGER ✓ read-only
capture_trigger TEXT ✓ read-only
text_source TEXT ✓ read-only
cloud_blob_id TEXT ✓ read-only
full_text TEXT ✓ read-only
elements_ref_frame_id INTEGER ✓ read-only

Foreign Keys

Column Destination
video_chunk_id video_chunks.id

Indexes

Name Columns Unique SQL Drop?
idx_frames_cloud_blob_id cloud_blob_id SQL 
CREATE INDEX idx_frames_cloud_blob_id
ON frames(cloud_blob_id) WHERE cloud_blob_id IS NULL AND snapshot_path IS NOT NULL
read-only
idx_frames_elements_ref_frame_id elements_ref_frame_id SQL 
CREATE INDEX idx_frames_elements_ref_frame_id
ON frames(elements_ref_frame_id)
    WHERE elements_ref_frame_id IS NOT NULL
read-only
idx_frames_snapshot_path snapshot_path SQL 
CREATE INDEX idx_frames_snapshot_path
ON frames(snapshot_path) WHERE snapshot_path IS NOT NULL
read-only
idx_frames_sync_id sync_id SQL 
CREATE INDEX idx_frames_sync_id
ON frames(sync_id) WHERE sync_id IS NOT NULL
read-only
idx_frames_timestamp timestamp SQL 
CREATE INDEX idx_frames_timestamp
ON frames(timestamp)
read-only
idx_frames_timestamp_device
  • timestamp
  • device_name
 SQL 
CREATE INDEX idx_frames_timestamp_device
ON frames(timestamp, device_name)
read-only
idx_frames_video_chunk_id video_chunk_id SQL 
CREATE INDEX idx_frames_video_chunk_id
ON frames(video_chunk_id)
read-only

Triggers

Name SQL Drop?
frames_ai SQL 
CREATE TRIGGER frames_ai AFTER INSERT ON frames
WHEN NEW.full_text IS NOT NULL AND NEW.full_text != ''
BEGIN
    INSERT INTO frames_fts(rowid, full_text, app_name, window_name, browser_url)
    VALUES (
        NEW.id,
        NEW.full_text,
        COALESCE(NEW.app_name, ''),
        COALESCE(NEW.window_name, ''),
        COALESCE(NEW.browser_url, '')
    );
END
read-only
frames_ad SQL 
CREATE TRIGGER frames_ad AFTER DELETE ON frames
WHEN OLD.full_text IS NOT NULL AND OLD.full_text != ''
BEGIN
    INSERT INTO frames_fts(frames_fts, rowid, full_text, app_name, window_name, browser_url)
    VALUES (
        'delete',
        OLD.id,
        OLD.full_text,
        COALESCE(OLD.app_name, ''),
        COALESCE(OLD.window_name, ''),
        COALESCE(OLD.browser_url, '')
    );
END
read-only
frames_au SQL 
CREATE TRIGGER frames_au AFTER UPDATE OF full_text, app_name, window_name, browser_url ON frames
BEGIN
    -- remove old tokens if the old row was indexed
    INSERT INTO frames_fts(frames_fts, rowid, full_text, app_name, window_name, browser_url)
    SELECT
        'delete',
        OLD.id,
        OLD.full_text,
        COALESCE(OLD.app_name, ''),
        COALESCE(OLD.window_name, ''),
        COALESCE(OLD.browser_url, '')
    WHERE OLD.full_text IS NOT NULL AND OLD.full_text != '';

    -- insert new tokens if the new row should be indexed
    INSERT INTO frames_fts(rowid, full_text, app_name, window_name, browser_url)
    SELECT
        NEW.id,
        NEW.full_text,
        COALESCE(NEW.app_name, ''),
        COALESCE(NEW.window_name, ''),
        COALESCE(NEW.browser_url, '')
    WHERE NEW.full_text IS NOT NULL AND NEW.full_text != '';
END
read-only