Database schema

[nmccarty]

After compiling a bit of research about how other zettelkasten implementations structure their databases, I think we can start off with a schema containing three tables, structured as follows:

Nodes

Holds the contents of a node in in the note graph

id	title	contents	creation time	modification time
unique, primary key	text	text	time	time

Columns:

• id Unique, auto-generate primary key for the node, used to reference it in the graph
• title Title of the node
• contents Contents of the node, in whatever markup language
• creation time Time the node was created at
• modification time Time the node was last modified at

Should be indexed on title, and full text indexed on contents.

Links

Holds information about the edges of the node graph, or links to external resources (destination should probably be a uri or an enum, I feel like a uri would be more flexible and appropriate here)

id	source	destination
unique, primary key	nodes.id	nodes.id

Should have an index generated on the source field, and probably also destination

Aliases

Alternate names that a node can be called by

id	node	title
unique, primary key	nodes.id	text

Should be indexed on the node and probably also the title field

[VictorKoenders]

I think nodes should also have a path or url field. This would be useful to look up specific entries in both the web and terminal UI.

I've had issues with a name like node being too generic. I would have personally gone with page, or tidbit (google: a small and particularly interesting item of gossip or information) or the obvious choice: zettel.

Additionally I think we should have the following tables:

Config

Contains the global settings of the application. I'd suggest storing this in a key-value pair because:

• if it is 1 column per setting, you will have 1 row with a dozen (or more) columns
  • diesel has a default limit of 32 columns per table. Increasing this massively increases compilation time
  • sqlx (what we're probably going with) does not have this limitation
• It is easier to add or remove config settings as each field can have a default implementation

key	value
text, unique	text/json

Some config settings:

• User mode
  • Single user auto login (when running locally)
  • Single user manual login (when running locally but you still want security)
  • Multi user (when running a shared cluster, allows new users to register)
• Terminal editor
  • When running the application in terminal mode, which editor to open when editing a page
  • I don't think we should be trying to make our own editor for obvious reasons

User

Contains information about the logged in user. The config can determine if the application is in single-user mode or multi-user-mode

id	username	password	last_visited_node
unique, primary key	text, unique	text	option

Node change history

It would be nice if each change to a node is stored as a patch change in a table. Users can see the history of a node, and (after enabling a checkbox) searching in all histories of all nodes.

node_id	timestamp	patch	user_id
unique, foreign key	datetime	text	unique, foreign key

[VictorKoenders]

This is mostly implemented and released in 0.1.0.

The thinks that are missing:

• zettel links #19
• zettel aliases (I don't see a need for this but feel free to open a separate issue if you do)
• zettel change history #18