quicknotes/specs/database.md

Database Specification

Overview

QuickNotes uses SQLite as its database engine, with GORM as the object-relational mapping (ORM) layer. The database stores notes, read later items, feeds, and feed entries.

Database Engine

• SQLite: A self-contained, serverless, zero-configuration, transactional SQL database engine
• File Location: The database is stored in a file named notes.db in the application root directory
• Access: The database is accessed directly by the Go backend using the glebarez/sqlite driver for GORM

Schema

Notes Table

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Unique identifier for the note
title	TEXT	NOT NULL	Title of the note
content	TEXT	NOT NULL	Content of the note in Markdown format
created_at	DATETIME		When the note was created
updated_at	DATETIME		When the note was last updated

Note Links Table

Column	Type	Constraints	Description
source_note_id	TEXT	PRIMARY KEY, FOREIGN KEY	ID of the source note
target_note_id	TEXT	PRIMARY KEY, FOREIGN KEY	ID of the target note
created_at	DATETIME		When the link was created

Read Later Items Table

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Unique identifier for the item
url	TEXT	NOT NULL	Original URL of the article
title	TEXT	NOT NULL	Title of the article
content	TEXT		Extracted HTML content of the article
description	TEXT		Brief description or excerpt of the article
created_at	DATETIME		When the item was saved
updated_at	DATETIME		When the item was last updated
read_at	DATETIME		When the item was marked as read (null if unread)
archived_at	DATETIME		When the item was archived (null if not archived)

Feeds Table

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Unique identifier for the feed
title	TEXT		Title of the feed
url	TEXT	UNIQUE	URL of the feed (RSS/Atom)
description	TEXT		Description of the feed
site_url	TEXT		URL of the website associated with the feed
image_url	TEXT		URL of the feed's image or logo
last_fetched	DATETIME		When the feed was last fetched
created_at	DATETIME		When the feed was added
updated_at	DATETIME		When the feed was last updated

Entries Table

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Unique identifier for the entry
feed_id	TEXT	FOREIGN KEY, INDEX	ID of the parent feed
title	TEXT		Title of the entry
url	TEXT	UNIQUE	URL of the entry
content	TEXT		HTML content of the entry
summary	TEXT		Summary or excerpt of the entry
author	TEXT		Author of the entry
published	DATETIME		When the entry was published
updated	DATETIME		When the entry was last updated
read_at	DATETIME		When the entry was marked as read (null if unread)
full_content	TEXT		Full content of the entry (if fetched separately)
created_at	DATETIME		When the entry was added to the system
updated_at	DATETIME		When the entry was last updated in the system

Relationships

1. Notes to Notes (Many-to-Many):

   • A note can link to many other notes
   • A note can be linked from many other notes
   • The relationship is managed through the note_links table

2. Feeds to Entries (One-to-Many):

   • A feed can have many entries
   • An entry belongs to one feed
   • The relationship is managed through the feed_id foreign key in the entries table

Initialization

The database schema is automatically created and migrated when the application starts:

// Initialize database
db, err := gorm.Open(sqlite.Open(config.DBPath), &gorm.Config{})
if err != nil {
    log.Fatal(err)
}

// Auto migrate the schema
if err := db.AutoMigrate(&notes.Note{}, &notes.NoteLink{}, &readlist.ReadLaterItem{}, &feeds.Feed{}, &feeds.Entry{}); err != nil {
    log.Fatal(err)
}

Indexes

The following indexes are created to optimize query performance:

1. URL Index on Feeds: Ensures fast lookup of feeds by URL
2. Feed ID Index on Entries: Ensures fast lookup of entries by feed
3. URL Index on Entries: Ensures fast lookup of entries by URL

Data Access

Data access is managed through GORM, which provides:

1. Object-Relational Mapping: Maps Go structs to database tables
2. Query Building: Simplifies building SQL queries
3. Transactions: Ensures data consistency
4. Hooks: Allows for custom logic before and after database operations

Data Integrity

The following measures ensure data integrity:

1. Foreign Key Constraints: Ensure referential integrity between related tables
2. Unique Constraints: Prevent duplicate entries for URLs
3. Not Null Constraints: Ensure required fields are provided
4. Transactions: Used for operations that affect multiple tables

Backup and Recovery

The SQLite database file (notes.db) can be backed up by:

1. Copying the file when the application is not running
2. Using SQLite's backup API
3. Exporting data to a SQL dump file

Recovery can be performed by:

1. Replacing the database file with a backup
2. Importing data from a SQL dump file