Database Schema (DBOs)
Relevant source files
The following files were used as context for generating this wiki page:
The database schema for the Nucleus project is defined using SurrealDB's SurrealQL. These Database Objects (DBOs) are located in the shared/dbo/ directory and are structured modularly to support the different products within the ecosystem: Nucleus, Memotron, and Pointron. The schema definitions include table structures, fields, types, and indexes, providing the data persistence layer for the applications.
The schema is divided into three main parts: global, memotron, and pointron. The global schema contains tables shared across all products, while the memotron and pointron schemas define the specific data models for their respective applications. This modular approach allows for clear separation of concerns and independent evolution of each product's data requirements.
Sources: WARP.md, shared/dbo/global.dbo.ts, shared/dbo/memotron.dbo.ts, shared/dbo/pointron.dbo.ts
Schema Organization
The database schema is organized into distinct modules, each corresponding to a product or shared functionality. This structure is reflected in the file organization within the shared/dbo directory.
This diagram illustrates the modular breakdown of the database schema into Global, Memotron, and Pointron components and their primary tables.
Sources: shared/dbo/global.tables.ts, shared/dbo/memotron.tables.ts, shared/dbo/pointron.tables.ts
Schema Definition Strategy
The project employs a strategy of defining tables as SCHEMALESS initially, which allows for flexibility during development. This is followed by more rigid SCHEMAFULL definitions that specify field types and constraints. The OVERWRITE keyword is used to ensure the latest schema definition is applied.
For example, the node table in Memotron is first defined as schemaless:
DEFINE TABLE OVERWRITE node TYPE ANY SCHEMALESS;
This is useful for environments where schema migrations are handled flexibly. The codebase also contains functions to generate SCHEMAFULL definitions with detailed field types.
Sources: shared/dbo/memotron.tables.ts:7-10
Global Schema
The global schema defines data structures that are shared across all products. These tables are defined in shared/dbo/global.tables.ts.
Key Global Tables
| Table | Description | Source File |
|---|---|---|
user | Represents a user account. | shared/dbo/global.tables.ts |
file | Stores metadata about uploaded files. | shared/dbo/global.tables.ts |
view | Defines custom views for displaying collections of data (e.g., board, list). | shared/dbo/global.tables.ts |
property | Represents a custom property that can be attached to other resources. | shared/dbo/global.tables.ts |
import | Tracks data import sessions, such as from Pocket. | shared/dbo/global.tables.ts |
preference | Stores user-specific preferences. | shared/dbo/global.tables.ts |
Sources: shared/dbo/global.tables.ts:5-11
user Table Schema
| Field | Type | Description |
|---|---|---|
name | option<string> | The user's name. |
email | string | The user's email address (unique). |
password | string | The user's hashed password. |
avatar | option<string> | URL to the user's avatar image. |
config | option<object> | User-specific configuration settings. |
Sources: shared/dbo/global.tables.ts:14-23
Memotron Schema
The Memotron schema is designed for knowledge management and supports entities like notes, web clips, and collections. The main tables are node, collection, and vector.
Sources: shared/dbo/memotron.tables.ts
Memotron Entity Relationship Diagram
This diagram shows the relationships between the core tables in the Memotron schema.
node Table
The node table is the central entity in Memotron, representing any piece of information, such as a note, a webpage, or a text clip.
| Field | Type | Description |
|---|---|---|
label | option<string> | The title or name of the node. |
body | option<object | string> | The main content of the node. Flexible type. |
parent | option<record<node>> | A reference to a parent node, creating a hierarchy. |
contentType | string | The type of content (e.g., WEB_PAGE, TEXT_CLIP). |
url | option<string> | The source URL for the content, if applicable. |
metadata | option<object> | A flexible object for storing extra metadata (e.g., from web scraping). |
vector | option<record<vector> | null> | A reference to the vector record for semantic search. |
text | option<string> | A plain text representation of the node's content for full-text search. |
createdBy | option<record<user>> | The user who created the node. |
createdAt | datetime | Timestamp of when the node was created. |
Sources: shared/dbo/memotron.tables.ts:13-39, client/products/memotron/import/pocket.importer.ts:440-463
collection Table
Collections are used to group nodes, similar to folders or tags. They can be simple untyped collections or structured "typed" collections with custom properties.
| Field | Type | Description |
|---|---|---|
label | option<string> | The name of the collection. |
type | string | The type of collection (e.g., TYPED, UNTYPED). |
resource | option<string> | The type of resource this collection contains (e.g., node). |
views | option<array<record<view>>> | An array of view records defining how to display the collection's items. |
properties | option<array<record<property>>> | For typed collections, an array of property definitions. |
createdBy | option<record<user>> | The user who created the collection. |
Sources: shared/dbo/memotron.tables.ts:101-120, client/products/memotron/import/pocket.importer.ts:303-313
Indexing in Memotron
Memotron uses SurrealDB's search indexes to provide fast full-text search capabilities. Indexes are defined on the node table for several fields.
-- Example index definition for the 'label' field
DEFINE INDEX IF NOT EXISTS nodetextSearchIndexLabel ON TABLE node COLUMNS label SEARCH ANALYZER ascii HIGHLIGHTS;
Indexes are created for mdText, label, and text columns to support keyword-based searches.
Sources: shared/dbo/memotron.tables.ts:74-80
Pointron Schema
The Pointron schema is tailored for focus and time management. It includes tables for goals, tasks, events, and time-tracking sessions.
Sources: shared/dbo/pointron.tables.ts
Pointron Entity Relationship Diagram
This diagram outlines the core entities for the Pointron product and their relationships.
Sources: shared/dbo/pointron.tables.ts
Key Pointron Tables
| Table | Description |
|---|---|
goal | Represents a high-level objective or goal. |
task | A specific, actionable item. Tasks can be linked to a goal and can have parent/child relationships. |
event | A time-bound entry, similar to a calendar event. |
session | A record of a time-tracking session (e.g., a Pomodoro session) associated with a task. |
block | A flexible entity that can represent a goal, task, or event, used for scheduling. |
Sources: shared/dbo/pointron.tables.ts:7-10, 29-32, 51-54, 73-76, 112-115
task Table Schema
| Field | Type | Description |
|---|---|---|
label | option<string> | The name or description of the task. |
status | option<string> | The current status of the task (e.g., 'todo', 'done'). |
parent | option<record<task>> | A reference to a parent task for sub-tasking. |
goal | option<record<goal>> | The goal this task contributes to. |
createdBy | option<record<user>> | The user who created the task. |
createdAt | datetime | Timestamp of when the task was created. |
Sources: shared/dbo/pointron.tables.ts:33-47
Summary
The Nucleus database schema is a well-structured, modular system built on SurrealDB. It effectively separates concerns between global, Memotron, and Pointron functionalities. The use of both SCHEMALESS and SCHEMAFULL definitions provides a balance of flexibility and type safety. Key features like full-text search indexing, flexible metadata storage, and relational links (both hierarchical and graph-based) are thoughtfully implemented to support the diverse needs of the productivity applications it powers.