SQL structure of Safari's history tables

safari.history.md

sqlite3 ~/Library/Safari/History.db

sqlite> .tables
history_client_versions  history_items            history_tombstones
history_event_listeners  history_items_to_tags    history_visits
history_events           history_tags             metadata

sqlite> .schema history_client_versions

CREATE TABLE history_client_versions (
  client_version INTEGER PRIMARY KEY
  ,last_seen REAL NOT NULL
);

sqlite> .schema history_event_listeners

CREATE TABLE history_event_listeners (
  listener_name TEXT PRIMARY KEY NOT NULL UNIQUE
  ,last_seen REAL NOT NULL
);

sqlite> .schema history_events

CREATE TABLE history_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT
  , event_type TEXT NOT NULL
  , event_time REAL NOT NULL 
  , pending_listeners TEXT NOT NULL
  , value BLOB
);

sqlite> .schema history_items

CREATE TABLE history_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT
  , url TEXT NOT NULL UNIQUE
  , domain_expansion TEXT NULL
  , visit_count INTEGER NOT NULL
  , daily_visit_counts BLOB NOT NULL
  , weekly_visit_counts BLOB NULL
  , autocomplete_triggers BLOB NULL
  , should_recompute_derived_visit_counts INTEGER NOT NULL
  , visit_count_score INTEGER NOT NULL
);
CREATE INDEX history_items__domain_expansion ON history_items (domain_expansion);

sqlite> .schema history_items_to_tags

CREATE TABLE history_items_to_tags (
  history_item INTEGER NOT NULL
  , tag_id INTEGER NOT NULL
  , timestamp REAL NOT NULL
  , FOREIGN KEY(tag_id) REFERENCES history_tags(id) ON DELETE CASCADE
  , FOREIGN KEY(history_item) REFERENCES history_items(id) ON DELETE CASCADE
  , UNIQUE(history_item, tag_id) ON CONFLICT REPLACE
);
CREATE TRIGGER increment_count_on_insert AFTER INSERT ON history_items_to_tags 
  BEGIN 
    UPDATE history_tags SET item_count = item_count + 1 WHERE id = NEW.tag_id;
  END;
  
CREATE TRIGGER decrement_count_on_delete BEFORE DELETE ON history_items_to_tags 
  BEGIN 
    UPDATE history_tags SET item_count = item_count - 1 WHERE id = OLD.tag_id;
  END;

sqlite> .schema history_tags

CREATE TABLE history_tags (
  id INTEGER PRIMARY KEY
  , type INTEGER NOT NULL
  , level INTEGER NOT NULL
  , identifier TEXT NOT NULL
  , title TEXT NOT NULL
  , modification_timestamp REAL NOT NULL
  , item_count INTEGER NOT NULL DEFAULT 0
);

```bash
sqlite> .schema history_tombstones

CREATE TABLE history_tombstones (
  id INTEGER PRIMARY KEY AUTOINCREMENT
  , start_time REAL NOT NULL
  , end_time REAL NOT NULL
  , url TEXT
  , generation INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX history_tombstones__generation ON history_tombstones (generation);
CREATE INDEX history_tombstones__end_time ON history_tombstones (end_time);

sqlite> .schema history_visits

CREATE TABLE history_visits (
  id INTEGER PRIMARY KEY AUTOINCREMENT
  , history_item INTEGER NOT NULL REFERENCES history_items(id) ON DELETE CASCADE
  , visit_time REAL NOT NULL
  , title TEXT NULL
  , load_successful BOOLEAN NOT NULL DEFAULT 1
  , http_non_get BOOLEAN NOT NULL DEFAULT 0
  , synthesized BOOLEAN NOT NULL DEFAULT 0
  , redirect_source INTEGER NULL UNIQUE REFERENCES history_visits(id) ON DELETE CASCADE
  , redirect_destination INTEGER NULL UNIQUE REFERENCES history_visits(id) ON DELETE CASCADE
  , origin INTEGER NOT NULL DEFAULT 0
  , generation INTEGER NOT NULL DEFAULT 0
  , attributes INTEGER NOT NULL DEFAULT 0
  , score INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX history_visits__last_visit ON history_visits (history_item, visit_time DESC, synthesized ASC);
CREATE INDEX history_visits__origin ON history_visits (origin, generation);

sqlite> .schema metadata

CREATE TABLE metadata (key TEXT NOT NULL UNIQUE, value);

sqlite> PRAGMA auto_vacuum = FULL;
sqlite> VACUUM;

sqlite> select visit_count, url from history_items order by visit_count desc limit 10;
1734|https://news.ycombinator.com/
1361|https://mail.google.com/mail/u/0/#inbox
1203|https://news.ycombinator.com/news
946|https://mail.google.com/mail/u/1/#inbox
643|https://news.ycombinator.com/threads
618|https://eu-west-1.console.aws.amazon.com/athena/home?region=eu-west-1#query
545|https://news.ycombinator.com/comment
526|https://www.linkedin.com/feed/
468|https://www.linkedin.com/
361|https://www.google.hu/maps