logo
ExamplesEditorPricingDocs
37.1kSign in

Note-taking app database schema with backlinks

A local-first SQLite notes database: notebooks and notes, wiki-style backlinks parsed from note bodies into a link graph, tags, attachments, a full-text search index, revision history and last-writer-wins sync with a conflict record.

Note Taking ER diagram

Note-taking app database schema with backlinks n1 n1 n1 n1 n1 n1 n1 n1 11 n1 n1 11 n1 n1 n1 n1 n1 devices idINTEGER device_uuidTEXT nameTEXT platformTEXT is_currentBOOLEAN last_sync_atTIMESTAMP created_atTIMESTAMP notebooks idINTEGER notebook_uuidTEXT parent_idINTEGER nameTEXT iconTEXT positionINTEGER is_archivedBOOLEAN created_atTIMESTAMP updated_atTIMESTAMP notes idINTEGER note_uuidTEXT notebook_idINTEGER titleTEXT title_normalizedTEXT bodyTEXT body_hashTEXT word_countINTEGER is_pinnedBOOLEAN is_encryptedBOOLEAN deleted_atTIMESTAMP created_atTIMESTAMP updated_atTIMESTAMP note_links idINTEGER source_note_idINTEGER target_note_idINTEGER target_title_norm…TEXT link_textTEXT char_offsetINTEGER is_embedBOOLEAN tags idINTEGER nameTEXT colorTEXT parent_idINTEGER usage_countINTEGER note_tags note_idINTEGER tag_idINTEGER sourceTEXT attachments idINTEGER note_idINTEGER filenameTEXT mime_typeTEXT size_bytesINTEGER content_hashTEXT storage_pathTEXT inline_dataBLOB created_atTIMESTAMP note_search note_idINTEGER titleTEXT bodyTEXT indexed_atTIMESTAMP note_revisions idINTEGER note_idINTEGER device_idINTEGER revision_clockINTEGER titleTEXT bodyTEXT body_hashTEXT is_syncedBOOLEAN created_atTIMESTAMP sync_state note_idINTEGER local_clockINTEGER remote_clockINTEGER last_synced_revis…INTEGER is_dirtyBOOLEAN last_errorTEXT last_attempt_atTIMESTAMP sync_conflicts idINTEGER note_idINTEGER winning_revision_…INTEGER losing_revision_idINTEGER winning_device_idINTEGER resolutionTEXT resolved_atTIMESTAMP detected_atTIMESTAMP

Related database schemas

SQLite
Productivity

Habit tracker app database schema

A small local-first SQLite schema for habit tracking: habits with date-versioned schedules, one row per completion, pauses that skip a streak instead of breaking it, reminders, and streaks derived from check-ins rather than stored as a counter.

SQLite
Productivity

Password manager & encrypted vault database schema

A local-first SQLite vault where the secrets are ciphertext the database cannot read: vaults as key boundaries, encrypted item fields, blind indexes for the few searches that must still work, TOTP seeds, password history, sharing and an audit log.

PostgreSQL
Productivity

Calendar and scheduling database schema

How a calendar app models its data: calendars and sharing, events with their own time zones, RRULE recurrence with exception dates and per-occurrence overrides, attendees and RSVP, reminders, working hours and a free-busy index.

PostgreSQL
Productivity

Dropbox-style file storage & sync database schema

How a cloud drive models its data: a folder tree, versioned files, content-addressed blocks that deduplicate identical uploads to a single copy, shares and expiring public links, per-device sync cursors, trash retention and quota accounting.