# 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:

```go
// 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