Home / Developers / Database schema

Database schema

Lexicon stores everything in a single SQLite file, lexicon.db, initialized and upgraded automatically at startup. This page documents the schema as defined by the migrations in DatabaseManager.cpp.

Overview

Seven domain tables plus one bookkeeping table:

Relationships at a glance:

map 1 ──── n term 1 ──── n alias
                  │ ├───── n tag
                  │ └───── n flag
                  │
                  └── n link n ──  (term → term, typed, directed)

Migration system

On startup, DatabaseManager::initialize():

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–8) 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.

Table reference

map

CREATE TABLE map (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  name        TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT ''
);
CREATE UNIQUE INDEX map_name_unique ON map(name);

term

CREATE TABLE term (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  map_id         INTEGER NOT NULL,
  title          TEXT NOT NULL,
  disambiguation TEXT,
  status         INTEGER NOT NULL DEFAULT 0,  -- TermStatus
  understanding  INTEGER NOT NULL DEFAULT 0,  -- UnderstandingLevel
  pinned         INTEGER NOT NULL DEFAULT 0,
  content        TEXT,                        -- Markdown source
  FOREIGN KEY(map_id) REFERENCES map(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX term_unique
  ON term(map_id, title, COALESCE(disambiguation, ''));
CREATE INDEX idx_term_map_id ON term(map_id);
CREATE INDEX idx_term_title  ON term(title);

The unique index is why the same title can exist twice in one map only when the disambiguations differ.

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,
  term_id INTEGER NOT NULL,
  alias   TEXT NOT NULL,
  UNIQUE(term_id, alias),
  FOREIGN KEY(term_id) REFERENCES term(id) ON DELETE CASCADE
);
CREATE INDEX idx_alias_term_id ON alias(term_id);
CREATE INDEX idx_alias_alias   ON alias(alias);

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

link

CREATE TABLE link (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  from_term_id INTEGER NOT NULL,
  to_term_id   INTEGER NOT NULL,
  link_type    INTEGER NOT NULL DEFAULT 0,  -- LinkType
  position     INTEGER NOT NULL DEFAULT 0,  -- ordering within a term
  FOREIGN KEY(from_term_id) REFERENCES term(id) ON DELETE CASCADE,
  FOREIGN KEY(to_term_id)   REFERENCES term(id) ON DELETE CASCADE
);
CREATE INDEX idx_link_from_term_id ON link(from_term_id);
CREATE INDEX idx_link_to_term_id   ON link(to_term_id);

Links are directed: querying by from_term_id yields a term's outgoing links, querying by to_term_id yields its backlinks. link_type stores the LinkType enum value (see Architecture).

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
  happened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Integrity rules

• Foreign keys are enforced — PRAGMA foreign_keys = ON runs on every connection.
• Cascade deletes — deleting a map deletes its terms; deleting a term deletes its aliases, tags, flags, and any links in either direction.
• Uniqueness — map names are globally unique; (map_id, title, disambiguation) is unique per term; alias/tag/flag values are unique per term.
• Enum stability — status, understanding, and link_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 DatabaseManager::initialize():

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. Update the record structs and queries in DatabaseManager to read/write the new columns.
4. Test by launching with an old lexicon.db copy and with no database at all — both paths must succeed.