SQL

CREATE TABLE elements  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  frame_id INTEGER NOT NULL,
  source TEXT NOT NULL,
  -- 'ocr' | 'accessibility'
    role TEXT NOT NULL,
  -- OCR: 'page',
  'block',
  'paragraph',
  'line',
  'word'
                                      -- AX: 'AXButton',
  'AXTextField',
  'AXStaticText',
  etc.
    text TEXT,
  -- element text content (NULL for container nodes)
    parent_id INTEGER,
  -- self-referential FK for tree hierarchy (NULL = root)
    depth INTEGER NOT NULL DEFAULT 0,
  -- tree depth (0 = root)
    left_bound REAL,
  -- normalized 0-1 bounding box
    top_bound REAL,
  width_bound REAL,
  height_bound REAL,
  confidence REAL,
  -- OCR confidence (0-100),
  NULL for AX
    sort_order INTEGER NOT NULL DEFAULT 0,
  properties TEXT,
  -- sibling order within parent
    FOREIGN KEY (frame_id) REFERENCES frames(id),
  FOREIGN KEY (parent_id) REFERENCES elements(id)
)

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ read-only
frame_id INTEGER read-only
source TEXT read-only
role TEXT read-only
text TEXT ✓ read-only
parent_id INTEGER ✓ read-only
depth INTEGER read-only
left_bound REAL ✓ read-only
top_bound REAL ✓ read-only
width_bound REAL ✓ read-only
height_bound REAL ✓ read-only
confidence REAL ✓ read-only
sort_order INTEGER read-only
properties TEXT ✓ read-only

Foreign Keys

Column Destination
parent_id elements.id
frame_id frames.id

Indexes

Name Columns Unique SQL Drop?
idx_elements_frame_id frame_id SQL 
CREATE INDEX idx_elements_frame_id
ON elements(frame_id)
read-only
idx_elements_frame_source
  • frame_id
  • source
 SQL 
CREATE INDEX idx_elements_frame_source
ON elements(frame_id, source)
read-only
idx_elements_frame_source_role
  • frame_id
  • source
  • role
 SQL 
CREATE INDEX idx_elements_frame_source_role
ON elements(frame_id, source, role) WHERE text IS NOT NULL
read-only
idx_elements_parent_id parent_id SQL 
CREATE INDEX idx_elements_parent_id
ON elements(parent_id)
read-only
idx_elements_source source SQL 
CREATE INDEX idx_elements_source
ON elements(source)
read-only
idx_elements_source_role_text
  • source
  • role
  • frame_id
 SQL 
CREATE INDEX idx_elements_source_role_text
ON elements(source, role, frame_id)
    WHERE text IS NOT NULL
read-only

Triggers

Name SQL Drop?
elements_ai SQL 
CREATE TRIGGER elements_ai AFTER INSERT ON elements
WHEN NEW.text IS NOT NULL AND NEW.text != ''
BEGIN
    INSERT INTO elements_fts(rowid, text, role, frame_id)
    VALUES (NEW.id, NEW.text, NEW.role, NEW.frame_id);
END
read-only
elements_ad SQL 
CREATE TRIGGER elements_ad AFTER DELETE ON elements
WHEN OLD.text IS NOT NULL AND OLD.text != ''
BEGIN
    INSERT INTO elements_fts(elements_fts, rowid, text, role, frame_id)
    VALUES ('delete', OLD.id, OLD.text, OLD.role, OLD.frame_id);
END
read-only
elements_au SQL 
CREATE TRIGGER elements_au AFTER UPDATE ON elements
WHEN OLD.text IS NOT NULL AND OLD.text != ''
BEGIN
    INSERT INTO elements_fts(elements_fts, rowid, text, role, frame_id)
    VALUES ('delete', OLD.id, OLD.text, OLD.role, OLD.frame_id);
    INSERT INTO elements_fts(rowid, text, role, frame_id)
    VALUES (NEW.id, NEW.text, NEW.role, NEW.frame_id);
END
read-only