# Balanced Breakfast Database Schema

SQLite schema reference. 10 migrations. Migrations live in `migrations/sqlite/` as numbered SQL files, applied on boot via sqlx-sqlite.

## Domain Map

| Domain | Tables | Purpose |
|--------|--------|---------|
| Feeds | 2 | Feed sources and fetched items |
| Search | 1 | FTS5 full-text index on feed items |
| Organization | 2 | Feed tags, query feeds (saved filters) |
| Busser State | 1 | Key-value state storage for Rhai plugins |
| Preferences | 1 | User configuration key-value store |
| SyncKit | 2 | Cloud sync metadata and local changelog |

---

## Feeds

### feeds
Registered feed sources (bussers). Each feed maps to a Rhai plugin that fetches content.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `busser_id` | TEXT | Plugin identifier |
| `name` | TEXT | |
| `config` | TEXT | JSON config blob, default `{}` |
| `enabled` | INTEGER | Boolean, default 1 |
| `last_fetch` | TEXT | ISO datetime, nullable |
| `consecutive_failures` | INTEGER | Health tracking (migration 006), default 0 |
| `last_error` | TEXT | Last failure message (migration 006), nullable |
| `last_success_at` | TEXT | Last successful fetch (migration 006), nullable |
| `circuit_broken` | INTEGER | Auto-disabled after repeated failures (migration 008), default 0 |
| `created_at`, `updated_at` | TEXT | ISO datetimes |

Indexes: `busser_id`, `enabled`.

### feed_items
All fetched items across all feeds. Two-tier display model: compact "bite" fields for list view, full content fields for detail view.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `external_id` | TEXT | Unique, deduplication key |
| `feed_id` | TEXT FK | -> feeds (CASCADE) |
| `busser_id` | TEXT | Plugin identifier |
| `bite_author` | TEXT | Compact view: author name |
| `bite_text` | TEXT | Compact view: summary line |
| `bite_secondary` | TEXT | Compact view: secondary info, nullable |
| `bite_indicator` | TEXT | Compact view: status indicator, nullable |
| `title` | TEXT | Full content, nullable |
| `body` | TEXT | Full content, nullable |
| `url` | TEXT | Source URL, nullable |
| `media` | TEXT | JSON array, default `[]` |
| `published_at` | TEXT | ISO datetime |
| `fetched_at` | TEXT | ISO datetime |
| `source_name` | TEXT | Display name of source |
| `score` | INTEGER | Relevance/ranking score, nullable |
| `tags` | TEXT | JSON array, default `[]` |
| `is_read` | INTEGER | Boolean, default 0 |
| `is_starred` | INTEGER | Boolean, default 0 |
| `actions` | TEXT | JSON array of action definitions (migration 010), default `[]` |
| `created_at`, `updated_at` | TEXT | ISO datetimes |

Indexes: `feed_id`, `busser_id`, `published_at DESC`, `is_read`, `is_starred`.

---

## Search

### feed_items_fts
FTS5 virtual table in external content mode. Indexes `title`, `body`, and `bite_text` from `feed_items`. Migration 005.

- External content source: `feed_items` (via `content=` and `content_rowid=`)
- Kept in sync by three triggers: `feed_items_fts_insert`, `feed_items_fts_update`, `feed_items_fts_delete`
- No data duplication -- FTS index references `feed_items` via rowid

---

## Organization

### feed_tags
User-assigned flat tags on feeds for organization. Migration 004.

| Column | Type | Notes |
|--------|------|-------|
| `feed_id` | TEXT FK | -> feeds (CASCADE) |
| `tag` | TEXT | |

PK: `(feed_id, tag)`. Index: `tag`.

### query_feeds
Saved filter rules that act as virtual feed sources. Migration 009.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `name` | TEXT | |
| `rules` | TEXT | JSON array of filter rules, default `[]` |
| `created_at`, `updated_at` | TEXT | ISO datetimes |

---

## Busser State

### busser_state
Key-value state storage for Rhai plugins (pagination cursors, auth tokens, etc.). Migration 003.

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT PK | |
| `busser_id` | TEXT | Plugin identifier |
| `key` | TEXT | |
| `value` | TEXT | |
| `created_at`, `updated_at` | TEXT | ISO datetimes |

Unique: `(busser_id, key)`. Index: `busser_id`.

---

## Preferences

### user_config
Key-value preferences store. Migration 007.

| Column | Type | Notes |
|--------|------|-------|
| `key` | TEXT PK | |
| `value` | TEXT | Nullable |

---

## SyncKit

### sync_state
Sync metadata key-value store. Single-row-per-key design. Migration 007.

| Column | Type | Notes |
|--------|------|-------|
| `key` | TEXT PK | |
| `value` | TEXT | |

Seeded keys: `device_id`, `pull_cursor`, `auto_sync_enabled`, `sync_interval_minutes`, `applying_remote`, `last_sync_at`, `initial_snapshot_done`.

### sync_changelog
Local change log for push/pull sync. Records changes to synced tables for the next push. Migration 007.

| Column | Type | Notes |
|--------|------|-------|
| `id` | INTEGER PK | AUTOINCREMENT |
| `table_name` | TEXT | Source table name |
| `op` | TEXT | `INSERT`, `UPDATE`, or `DELETE` |
| `row_id` | TEXT | PK of changed row |
| `timestamp` | TEXT | ISO datetime, default `datetime('now')` |
| `data` | TEXT | JSON snapshot of row, nullable (NULL on DELETE) |
| `pushed` | INTEGER | Boolean, default 0 |

Index: `pushed`.

---

## Design Patterns

- **Two-tier display model:** `feed_items` has compact "bite_*" fields for list rendering and full `title`/`body` fields for detail view
- **External content FTS:** `feed_items_fts` uses FTS5 external content mode -- zero data duplication, trigger-maintained
- **Circuit breaker:** `feeds.circuit_broken` auto-disables feeds after `consecutive_failures` exceeds threshold
- **Sync guard triggers:** All sync triggers check `applying_remote != '1'` to prevent re-recording server-pushed changes
- **Synced tables:** `feeds`, `feed_tags`, `feed_items` (user state only: `is_read`/`is_starred`), `user_config`, `query_feeds`
- **Composite row IDs for sync:** Compound PKs encoded as `a:b` strings in `sync_changelog.row_id` (e.g., `feed_id:tag`)
- **Plugin state isolation:** `busser_state` scoped by `busser_id` so plugins cannot collide

## Key Indexes

- **Full-text** on feed_items (title, body, bite_text) via FTS5
- **Temporal** on feed_items (published_at DESC) for reverse-chronological feeds
- **State** on feed_items (is_read, is_starred) for filtered views
- **Lookup** on feeds (busser_id, enabled), busser_state (busser_id), feed_tags (tag)
- **Sync** on sync_changelog (pushed) for pending push detection

## Schema Evolution

| Migration | Change |
|-----------|--------|
| 001 | feeds table (sources) |
| 002 | feed_items table (fetched content) |
| 003 | busser_state (plugin key-value storage) |
| 004 | feed_tags (user-assigned tags on feeds) |
| 005 | feed_items_fts (FTS5 full-text search) |
| 006 | Feed health tracking (consecutive_failures, last_error, last_success_at) |
| 007 | SyncKit integration (user_config, sync_state, sync_changelog, sync triggers) |
| 008 | Circuit breaker (circuit_broken column, updated sync triggers) |
| 009 | Query feeds (saved filter rules as virtual sources) |
| 010 | Item actions (actions JSON column on feed_items) |