Home / Developers / Database schema

Database schema

Lexicon stores structured data in lexicon.db and blob bytes in an adjacent blobs directory. The database is initialized at startup; the blob directory is created when a file is imported. This page documents the schema defined by lexicon-storage-sqlite/Migrations.cpp.

Overview

Fifteen domain tables, an optional full-text index, an operation log, one configuration table, and one bookkeeping table:

Relationships at a glance:

item_group 1 ──── n item ──── 0..1 item_type ──── n item_field
        │             ├───── n alias / tag / flag / property
        └── n item_type (optional scope)
                      item ──── n item_value ──── 1 item_field
                      item ──── n link n ── item (typed, directed)
                      item ──── n card

Migration system

On startup, SqliteRepository::open():

1. Opens (or creates) lexicon.db and executes PRAGMA foreign_keys = ON.
2. Ensures the bookkeeping table exists: CREATE TABLE IF NOT EXISTS db_version (version INTEGER PRIMARY KEY).
3. Walks an ordered list of migrations (currently versions 1–32) and applies every version greater than the current one, each inside a transaction. The version row is written in the same transaction, so a failed migration rolls back completely.
4. A migration that rebuilds a table — SQLite cannot change a CHECK constraint in place — runs with foreign keys switched off around its transaction, so dropping the old table cannot cascade into the rows that refer to it, and must pass PRAGMA foreign_key_check before it commits.

Table reference

item_group

CREATE TABLE item_group (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  name        TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  position    INTEGER NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX item_group_name_unique ON item_group(name);
CREATE INDEX idx_item_group_position ON item_group(position, name COLLATE NOCASE);

item

CREATE TABLE item (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  group_id       INTEGER NOT NULL,
  title          TEXT NOT NULL,
  disambiguation TEXT,
  status         INTEGER NOT NULL DEFAULT 0,  -- ItemStatus
  understanding  INTEGER NOT NULL DEFAULT 0,  -- UnderstandingLevel
  pinned         INTEGER NOT NULL DEFAULT 0,
  content        TEXT,                        -- Markdown source
  item_type_id   INTEGER,                     -- added in migration 14
  revision       INTEGER NOT NULL DEFAULT 1,  -- migration 21
  reviewed_at    TEXT,                        -- UTC; migration 22
  FOREIGN KEY(group_id) REFERENCES item_group(id) ON DELETE CASCADE,
  FOREIGN KEY(item_type_id) REFERENCES item_type(id) ON DELETE SET NULL
);
CREATE UNIQUE INDEX item_unique
  ON item(group_id, title, COALESCE(disambiguation, ''));
CREATE INDEX idx_item_group_id ON item(group_id);
CREATE INDEX idx_item_title  ON item(title);
CREATE INDEX idx_item_item_type_id ON item(item_type_id);
CREATE INDEX idx_item_reviewed_at ON item(reviewed_at);

The unique index is why the same title can exist twice in one group only when the disambiguations differ. A client saving an item names the revision it loaded; a save based on an older one is refused. The next review is due a number of days after reviewed_at that grows with understanding.

item_type and item_field

CREATE TABLE item_type (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  group_id INTEGER,                         -- NULL: available in every group
  name     TEXT NOT NULL CHECK(TRIM(name) <> ''),
  description TEXT NOT NULL DEFAULT '',    -- added in migration 18
  FOREIGN KEY(group_id) REFERENCES item_group(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX item_type_scope_name_unique
  ON item_type(COALESCE(group_id, 0), name COLLATE NOCASE);

CREATE TABLE item_field (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  item_type_id INTEGER NOT NULL,
  name         TEXT NOT NULL CHECK(TRIM(name) <> ''),
  data_type    INTEGER NOT NULL CHECK(data_type BETWEEN 0 AND 11),
  position     INTEGER NOT NULL DEFAULT 0,
  enum_options TEXT NOT NULL DEFAULT '[]', -- JSON array
  description  TEXT NOT NULL DEFAULT '',   -- migration 30
  target_item_type_id INTEGER,             -- migration 33, no value-level FK constraint
  FOREIGN KEY(item_type_id) REFERENCES item_type(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX item_field_type_name_unique
  ON item_field(item_type_id, name COLLATE NOCASE);
CREATE INDEX idx_item_field_type_position
  ON item_field(item_type_id, position);

Fields are displayed by position, then name and ID. Their optional descriptions are shown by all three clients. data_type uses the stable FieldDataType integer enum. A ForeignKey value stores an item ID and uses target_item_type_id for title resolution; missing or wrong-type targets display as !missing! {id}. The stored value has no referential constraint. A type may be assigned only to items in its group, unless group_id is null; triggers enforce this.

item_value and property

CREATE TABLE item_value (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  item_id       INTEGER NOT NULL,
  item_field_id INTEGER NOT NULL,
  value         TEXT NOT NULL,
  UNIQUE(item_id, item_field_id),
  FOREIGN KEY(item_id) REFERENCES item(id) ON DELETE CASCADE,
  FOREIGN KEY(item_field_id) REFERENCES item_field(id) ON DELETE CASCADE
);
CREATE INDEX idx_item_value_field_id ON item_value(item_field_id);

CREATE TABLE property (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  item_id INTEGER NOT NULL,
  "key"   TEXT NOT NULL CHECK(TRIM("key") <> ''),
  value   TEXT NOT NULL DEFAULT '',
  FOREIGN KEY(item_id) REFERENCES item(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX property_item_key_unique
  ON property(item_id, "key" COLLATE NOCASE);

Field values are stored as canonical text validated against their declared type. Blob values contain a lowercase SHA-256 hash; Image values contain <media type>:<SHA-256>, such as image/png:6c7d…. The bytes of both live at blobs/<first 2 hex characters>/<remaining 62> next to the database. Properties are independent of item types and remain when an item changes type.

alias, tag, flag

Three structurally identical child tables (the value column is alias in alias, name in tag and flag):

CREATE TABLE alias (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  item_id INTEGER NOT NULL,
  alias   TEXT NOT NULL,
  UNIQUE(item_id, alias),
  FOREIGN KEY(item_id) REFERENCES item(id) ON DELETE CASCADE
);
CREATE INDEX idx_alias_item_id ON alias(item_id);
CREATE INDEX idx_alias_alias   ON alias(alias);

The per-item UNIQUE constraint deduplicates values; updates are implemented as replace-all per item (replaceStringValues()).

link

CREATE TABLE link (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  from_item_id INTEGER NOT NULL,
  to_item_id   INTEGER NOT NULL,
  link_type    INTEGER NOT NULL DEFAULT 0,  -- LinkType
  position     INTEGER NOT NULL DEFAULT 0,  -- ordering within an item
  custom_value TEXT NOT NULL DEFAULT '',
  FOREIGN KEY(from_item_id) REFERENCES item(id) ON DELETE CASCADE,
  FOREIGN KEY(to_item_id)   REFERENCES item(id) ON DELETE CASCADE
);
CREATE INDEX idx_link_from_item_id ON link(from_item_id);
CREATE INDEX idx_link_to_item_id   ON link(to_item_id);

Links are directed: querying by from_item_id yields an item's outgoing links, querying by to_item_id yields its backlinks. link_type stores the LinkType enum value (see Architecture).

item_history

Before an item or its links change, Lexicon saves the previous item, links, cards and referenced file hashes as a JSON snapshot. It also snapshots every affected item before deleting a type, deleting a field, or changing a field's data type discards assignments or values; the snapshot and schema operation share one transaction. A deletion adds a Trash entry independent of the live item. The latest 100 update snapshots per item are retained; deleted entries remain available for restoration. Referenced files stay protected from Blob cleanup. Schema-change snapshots retain old IDs and raw values, though automatic restoration requires compatible type and field definitions. A raw database backup preserves the snapshots, while a portable export contains current data only.

study_plan

CREATE TABLE study_plan (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  item TEXT NOT NULL,
  type INTEGER NOT NULL CHECK(type BETWEEN 0 AND 7),
  unit_type INTEGER NOT NULL CHECK(unit_type BETWEEN 0 AND 8),
  current_progress INTEGER NOT NULL DEFAULT 0,
  start_date TEXT NOT NULL,
  end_date TEXT NOT NULL,
  note TEXT NOT NULL DEFAULT '',
  first_unit INTEGER NOT NULL DEFAULT 1 CHECK(first_unit >= 1),
  last_unit INTEGER NOT NULL CHECK(last_unit >= first_unit),
  study_days_mask INTEGER NOT NULL DEFAULT 127 CHECK(study_days_mask BETWEEN 1 AND 127),
  custom_unit TEXT NOT NULL DEFAULT '',
  "group" TEXT NOT NULL DEFAULT '',
  CHECK(current_progress = 0 OR current_progress BETWEEN first_unit AND last_unit)
);
CREATE INDEX idx_study_plan_dates ON study_plan(start_date, end_date);

Migration 35 appends "group" to the original table; the definition above shows the current columns. Additional checks require nonblank item text, ISO date shapes, end date on or after start, and a nonblank custom unit for Other. Application validation checks actual calendar dates and that a selected weekday occurs in the inclusive range. The item and Group are plain UTF-8 text with no relationship to the item or item-group tables. Progress is the last completed absolute unit, or 0. Monday is bit 1 through Sunday bit 64. There is no redundant unit count; it is last_unit - first_unit + 1.

alarm

CREATE TABLE alarm (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  title        TEXT NOT NULL CHECK(TRIM(title) <> ''),
  description  TEXT NOT NULL DEFAULT '',
  fires_at     TEXT NOT NULL,  -- UTC "YYYY-MM-DDTHH:MM:SSZ"
  dismissed_at TEXT,           -- UTC; migration 25
  repeat_days  INTEGER NOT NULL DEFAULT 0 CHECK(repeat_days BETWEEN 0 AND 365),
  item_id      INTEGER REFERENCES item(id) ON DELETE SET NULL,
  anchor_at    TEXT,           -- original recurrence time
  asap         INTEGER NOT NULL DEFAULT 0 CHECK(asap IN (0, 1)),
  "group"      TEXT NOT NULL DEFAULT ''
);
CREATE INDEX idx_alarm_fires_at ON alarm(fires_at);

Times are UTC text of one fixed shape, so text order is time order. A one-time alarm whose fires_at has passed rings until dismissed_at is set. Dismissing a recurring alarm moves it to the next occurrence derived from anchor_at; snoozing does not shift that schedule. asap marks work to handle as soon as possible, while group is an optional free-text grouping label; neither changes the alarm schedule.

card

CREATE TABLE card (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  item_id       INTEGER NOT NULL,
  question      TEXT NOT NULL CHECK(TRIM(question, ' ' || char(9, 10, 11, 12, 13)) <> ''),
  answer        TEXT NOT NULL CHECK(TRIM(answer, ' ' || char(9, 10, 11, 12, 13)) <> ''),
  success_count INTEGER NOT NULL DEFAULT 0 CHECK(success_count >= 0),
  failure_count INTEGER NOT NULL DEFAULT 0 CHECK(failure_count >= 0),
  last_attempt  TEXT,  -- UTC "YYYY-MM-DDTHH:MM:SSZ"; NULL when never attempted
  FOREIGN KEY(item_id) REFERENCES item(id) ON DELETE CASCADE
);
CREATE INDEX idx_card_item_id ON card(item_id, id);  -- migration 26

A card belongs to one item and goes when the item goes. The question and the answer are plain UTF-8 text; the counts and last_attempt are moved by a quiz answer only, each in one UPDATE ... SET success_count = success_count + 1, last_attempt = strftime(...), so answers given at once on several connections are all counted and the time is the database's. Cards are not Review: they have no due date, and nothing here touches item.understanding, item.reviewed_at or item.revision. Card text is not in item_search.

board

CREATE TABLE board (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  name     TEXT NOT NULL COLLATE NOCASE UNIQUE CHECK(TRIM(name) <> ''),
  content  TEXT NOT NULL DEFAULT '',
  revision INTEGER NOT NULL DEFAULT 1
);

A new database starts with one empty Board named Main. Names are required and unique ignoring ASCII case, more Boards can be added, and the final Board cannot be deleted. Every save increments that Board's revision; clients send the revision they loaded so a concurrent change is refused instead of overwritten.

item_search

CREATE VIRTUAL TABLE item_search USING fts5(
  title, disambiguation, aliases, tags, flags, content, revision UNINDEXED,
  tokenize = 'unicode61 remove_diacritics 2', prefix = '2 3'
);

Created when the database is opened by a SQLite that has FTS5, and never by a migration: a database stays readable without it. It holds nothing that is not in the other tables, and each row remembers the item revision it was built from, so a search re-indexes whatever changed since — also changes made by a program without FTS5.

log

CREATE TABLE log (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  table_name  TEXT NOT NULL,
  record_id   INTEGER NOT NULL,
  log_type    INTEGER NOT NULL,  -- 1=created, 2=updated, 3=deleted, 4=read, 5=reviewed
  happened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

configuration

CREATE TABLE configuration (
  "key" TEXT PRIMARY KEY NOT NULL CHECK(TRIM("key") <> ''),
  value TEXT NOT NULL
);

The main table stores optional column visibility as main.columns.attributes (Disambiguation, Tags, Flags, Aliases, Status, Understanding, Pinned) and main.columns.values (the value columns of the selected type), with 1 or 0 values. Missing keys mean visible.

Integrity rules

• Foreign keys are enforced — PRAGMA foreign_keys = ON runs on every connection.
• Cascade deletes — the schema has cascading foreign keys, but the repository refuses to delete a group that still has items or group-specific types. Deleting an item deletes its aliases, tags, flags, properties, field values, links, and cards. Before deleting a type or field, or changing a field's data type, the repository snapshots affected items in the same transaction; deleting a type then sets affected item_type_id values to null and removes their field values.
• Uniqueness — group names are globally unique; (group_id, title, disambiguation) is unique per item; alias/tag/flag values and property keys are unique per item; type names are unique within their scope.
• Enum stability — status, understanding, link_type, and data_type store raw enum integers; enum values must only ever be appended, never renumbered.

Adding a migration

To evolve the schema, follow the pattern in SqliteRepository::open():

1. Append a new {version, statements} entry to the migrations list with the next sequential version number. Never edit an already-shipped migration — existing databases have already recorded it as applied.
2. Use idempotent DDL where possible (CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS) and ALTER TABLE ... ADD COLUMN for extensions.
3. Changing a CHECK constraint means rebuilding the table: copy it aside, drop and re-create it, copy back, re-create its indexes and the triggers that name it, and mark the migration as rebuilding tables (see migration 24).
4. Update the core record structs and queries in SqliteRepository to read/write the new columns.
5. Test by launching with an old lexicon.db copy and with no database at all — both paths must succeed.