Database Schema¶

Graph Core uses SQLite as its data store, accessed through sql.js in the Electron main process.

Tables¶

nodes¶

Primary data table storing all node information.

Columns after deleted_at in the base schema (workspace_id, tags, collapsed, the graph_* set) are added by migrations on existing databases, so their physical order varies between installs. Always select by name.

workspaces¶

Workspace definitions for organizing nodes.

node_links¶

Many-to-many relationships between nodes.

UNIQUE(source_id, target_id) prevents duplicate links.

node_tables¶

Spreadsheet data attached to nodes.

node_table_cells¶

Individual cell data for node tables.

UNIQUE(table_id, row_index, col_index) keeps one row per cell.

categories, statuses, settings¶

Supporting tables: categories and statuses hold the optional labels referenced by nodes.category_id / nodes.status_id; settings is a key/value store for settings that must survive a reinstall (for example the per-workspace graph max depth).

Foreign Keys¶

sql.js opens databases with foreign-key enforcement off, so the schema's ON DELETE CASCADE / ON DELETE SET NULL clauses would be inert. Database._init() runs PRAGMA foreign_keys = ON immediately after opening the file, which makes them live:

• Hard-deleting a node cascades away its node_links rows and its node_tables / node_table_cells
• Hard-deleting a node sets parent_id = NULL on rows still pointing at it

Path and Depth Invariant¶

Every node's path is the slash-joined ids of its ancestors ('' for a root) and depth is the number of ancestors. Descendant queries (getDescendants, getDescendantsBatch) rely on path prefix matching, so a stale path makes a subtree invisible to them.

The invariant is maintained by two helpers in electron/database/nodes.js:

updateSubtreePath is used everywhere a node can change parent:

Both helpers read rows regardless of deleted_at: soft-deleted nodes are still rows in the table, and a stale path would resurface when they are restored.

Acyclicity¶

No node may be its own ancestor. moveNode rejects a move whose target is the node itself or one of its descendants, checking the target's path before writing anything. Without that check the tree gains a cycle and updateDescendantPaths walks it until the call stack overflows.

Reachability After Restore¶

Every live node must be reachable from a root. deleteNode reparents a deleted node's children to their grandparent, but only the children that are still live, so a node trashed before its parent keeps pointing at a parent that is now trashed too.

restoreNode therefore reattaches such a node to the nearest ancestor that is still live, falling back to the root level, and recomputes path/depth through moveNode. Restoring it in place would leave it invisible: not a root, and not a child of anything the tree renders.

emptyTrash keeps its own recovery for the same invariant, since a live node can point at a trashed parent through other routes.

Table Columns and Cells¶

node_table_cells addresses cells by position (col_index), so the cell store and the table's column_definitions have to change together. deleteTableColumn(nodeId, colIndex) deletes that column's cells, decrements col_index for every cell to its right, and rewrites the definitions in one batch. Removing a definition on its own would leave every later column showing its neighbour's values and strand the last column's cells.

setCells merges each write with the stored row rather than replacing it. Values and styles arrive from separate UI events, so a replacing write would blank whichever of the two the caller did not send. A field is cleared only when the caller names it explicitly.

Batched Writes¶

_save() writes the whole database file, so a naive subtree update would write the file once per row. Database._batch(fn) wraps fn in a single BEGIN/COMMIT and defers the write until the outermost batch ends — one transaction, one file write, rolled back on error. It is nestable, so helpers can batch without knowing their caller.

Every tree mutation listed above runs inside _batch, as do importJSON and importCSV. This makes them crash-atomic: either the whole move (or import) is on disk, or none of it.

Indexes¶

Indexes optimize common query patterns:

Single-column indexes¶

Composite indexes¶

Query Patterns¶

Get children with ordering¶

SELECT * FROM nodes
WHERE parent_id = ? AND deleted_at IS NULL
ORDER BY sort_order, created_at

Uses idx_nodes_parent_sort for efficient ordering.

Workspace-filtered roots¶

SELECT * FROM nodes
WHERE parent_id IS NULL
  AND workspace_id = ?
  AND deleted_at IS NULL
ORDER BY sort_order, created_at

Uses idx_nodes_workspace_parent.

Search with pagination¶

SELECT * FROM nodes
WHERE deleted_at IS NULL
  AND (title LIKE ? OR notes LIKE ?)
  AND workspace_id = ?
ORDER BY updated_at DESC
LIMIT ? OFFSET ?

Materialized path invariant¶

depth and path form a materialized-path encoding of the tree, enabling descendant lookups (path LIKE '1/5/%') and depth-capped queries without recursion. The invariant for every live node is:

• depth equals the number of its ancestors (0 for roots).
• path is the slash-joined ids of its ancestors, root first, and references only live ancestors (e.g. "1/5" for a node whose ancestors are 1 then 5).

Any operation that changes a node's parent must reset that node's own depth/path and then recompute its descendants. moveNode is the single source of truth for this; deleteNode (which reassigns children to the grandparent) and reparentToRoot apply the same reset. Updating only a node's descendants while leaving its own depth/path stale corrupts the whole subtree, because descendant paths are derived from the parent's path.

Migrations¶

Schema migrations run automatically on startup (electron/database/migrations.js):

1. Column additions are idempotent — only a "duplicate column name" error is swallowed, anything else is raised
2. Default workspaces (work, private) are seeded with INSERT OR IGNORE
3. Data migrations run conditionally (assigning unassigned nodes to work, repairing root paths, converting string tags to tag nodes)
4. A snapshot is taken before a destructive migration, but only when an existing database file is being upgraded — a brand-new database has nothing to back up

See Also¶

• Architecture Overview
• Development Guide