Skip to main content

max / balanced_breakfast

6.9 KB · 195 lines History Blame Raw
1 # Balanced Breakfast Database Schema
2
3 SQLite schema reference. 10 migrations. Migrations live in `migrations/sqlite/` as numbered SQL files, applied on boot via sqlx-sqlite.
4
5 ## Domain Map
6
7 | Domain | Tables | Purpose |
8 |--------|--------|---------|
9 | Feeds | 2 | Feed sources and fetched items |
10 | Search | 1 | FTS5 full-text index on feed items |
11 | Organization | 2 | Feed tags, query feeds (saved filters) |
12 | Busser State | 1 | Key-value state storage for Rhai plugins |
13 | Preferences | 1 | User configuration key-value store |
14 | SyncKit | 2 | Cloud sync metadata and local changelog |
15
16 ---
17
18 ## Feeds
19
20 ### feeds
21 Registered feed sources (bussers). Each feed maps to a Rhai plugin that fetches content.
22
23 | Column | Type | Notes |
24 |--------|------|-------|
25 | `id` | TEXT PK | |
26 | `busser_id` | TEXT | Plugin identifier |
27 | `name` | TEXT | |
28 | `config` | TEXT | JSON config blob, default `{}` |
29 | `enabled` | INTEGER | Boolean, default 1 |
30 | `last_fetch` | TEXT | ISO datetime, nullable |
31 | `consecutive_failures` | INTEGER | Health tracking (migration 006), default 0 |
32 | `last_error` | TEXT | Last failure message (migration 006), nullable |
33 | `last_success_at` | TEXT | Last successful fetch (migration 006), nullable |
34 | `circuit_broken` | INTEGER | Auto-disabled after repeated failures (migration 008), default 0 |
35 | `created_at`, `updated_at` | TEXT | ISO datetimes |
36
37 Indexes: `busser_id`, `enabled`.
38
39 ### feed_items
40 All fetched items across all feeds. Two-tier display model: compact "bite" fields for list view, full content fields for detail view.
41
42 | Column | Type | Notes |
43 |--------|------|-------|
44 | `id` | TEXT PK | |
45 | `external_id` | TEXT | Unique, deduplication key |
46 | `feed_id` | TEXT FK | -> feeds (CASCADE) |
47 | `busser_id` | TEXT | Plugin identifier |
48 | `bite_author` | TEXT | Compact view: author name |
49 | `bite_text` | TEXT | Compact view: summary line |
50 | `bite_secondary` | TEXT | Compact view: secondary info, nullable |
51 | `bite_indicator` | TEXT | Compact view: status indicator, nullable |
52 | `title` | TEXT | Full content, nullable |
53 | `body` | TEXT | Full content, nullable |
54 | `url` | TEXT | Source URL, nullable |
55 | `media` | TEXT | JSON array, default `[]` |
56 | `published_at` | TEXT | ISO datetime |
57 | `fetched_at` | TEXT | ISO datetime |
58 | `source_name` | TEXT | Display name of source |
59 | `score` | INTEGER | Relevance/ranking score, nullable |
60 | `tags` | TEXT | JSON array, default `[]` |
61 | `is_read` | INTEGER | Boolean, default 0 |
62 | `is_starred` | INTEGER | Boolean, default 0 |
63 | `actions` | TEXT | JSON array of action definitions (migration 010), default `[]` |
64 | `created_at`, `updated_at` | TEXT | ISO datetimes |
65
66 Indexes: `feed_id`, `busser_id`, `published_at DESC`, `is_read`, `is_starred`.
67
68 ---
69
70 ## Search
71
72 ### feed_items_fts
73 FTS5 virtual table in external content mode. Indexes `title`, `body`, and `bite_text` from `feed_items`. Migration 005.
74
75 - External content source: `feed_items` (via `content=` and `content_rowid=`)
76 - Kept in sync by three triggers: `feed_items_fts_insert`, `feed_items_fts_update`, `feed_items_fts_delete`
77 - No data duplication -- FTS index references `feed_items` via rowid
78
79 ---
80
81 ## Organization
82
83 ### feed_tags
84 User-assigned flat tags on feeds for organization. Migration 004.
85
86 | Column | Type | Notes |
87 |--------|------|-------|
88 | `feed_id` | TEXT FK | -> feeds (CASCADE) |
89 | `tag` | TEXT | |
90
91 PK: `(feed_id, tag)`. Index: `tag`.
92
93 ### query_feeds
94 Saved filter rules that act as virtual feed sources. Migration 009.
95
96 | Column | Type | Notes |
97 |--------|------|-------|
98 | `id` | TEXT PK | |
99 | `name` | TEXT | |
100 | `rules` | TEXT | JSON array of filter rules, default `[]` |
101 | `created_at`, `updated_at` | TEXT | ISO datetimes |
102
103 ---
104
105 ## Busser State
106
107 ### busser_state
108 Key-value state storage for Rhai plugins (pagination cursors, auth tokens, etc.). Migration 003.
109
110 | Column | Type | Notes |
111 |--------|------|-------|
112 | `id` | TEXT PK | |
113 | `busser_id` | TEXT | Plugin identifier |
114 | `key` | TEXT | |
115 | `value` | TEXT | |
116 | `created_at`, `updated_at` | TEXT | ISO datetimes |
117
118 Unique: `(busser_id, key)`. Index: `busser_id`.
119
120 ---
121
122 ## Preferences
123
124 ### user_config
125 Key-value preferences store. Migration 007.
126
127 | Column | Type | Notes |
128 |--------|------|-------|
129 | `key` | TEXT PK | |
130 | `value` | TEXT | Nullable |
131
132 ---
133
134 ## SyncKit
135
136 ### sync_state
137 Sync metadata key-value store. Single-row-per-key design. Migration 007.
138
139 | Column | Type | Notes |
140 |--------|------|-------|
141 | `key` | TEXT PK | |
142 | `value` | TEXT | |
143
144 Seeded keys: `device_id`, `pull_cursor`, `auto_sync_enabled`, `sync_interval_minutes`, `applying_remote`, `last_sync_at`, `initial_snapshot_done`.
145
146 ### sync_changelog
147 Local change log for push/pull sync. Records changes to synced tables for the next push. Migration 007.
148
149 | Column | Type | Notes |
150 |--------|------|-------|
151 | `id` | INTEGER PK | AUTOINCREMENT |
152 | `table_name` | TEXT | Source table name |
153 | `op` | TEXT | `INSERT`, `UPDATE`, or `DELETE` |
154 | `row_id` | TEXT | PK of changed row |
155 | `timestamp` | TEXT | ISO datetime, default `datetime('now')` |
156 | `data` | TEXT | JSON snapshot of row, nullable (NULL on DELETE) |
157 | `pushed` | INTEGER | Boolean, default 0 |
158
159 Index: `pushed`.
160
161 ---
162
163 ## Design Patterns
164
165 - **Two-tier display model:** `feed_items` has compact "bite_*" fields for list rendering and full `title`/`body` fields for detail view
166 - **External content FTS:** `feed_items_fts` uses FTS5 external content mode -- zero data duplication, trigger-maintained
167 - **Circuit breaker:** `feeds.circuit_broken` auto-disables feeds after `consecutive_failures` exceeds threshold
168 - **Sync guard triggers:** All sync triggers check `applying_remote != '1'` to prevent re-recording server-pushed changes
169 - **Synced tables:** `feeds`, `feed_tags`, `feed_items` (user state only: `is_read`/`is_starred`), `user_config`, `query_feeds`
170 - **Composite row IDs for sync:** Compound PKs encoded as `a:b` strings in `sync_changelog.row_id` (e.g., `feed_id:tag`)
171 - **Plugin state isolation:** `busser_state` scoped by `busser_id` so plugins cannot collide
172
173 ## Key Indexes
174
175 - **Full-text** on feed_items (title, body, bite_text) via FTS5
176 - **Temporal** on feed_items (published_at DESC) for reverse-chronological feeds
177 - **State** on feed_items (is_read, is_starred) for filtered views
178 - **Lookup** on feeds (busser_id, enabled), busser_state (busser_id), feed_tags (tag)
179 - **Sync** on sync_changelog (pushed) for pending push detection
180
181 ## Schema Evolution
182
183 | Migration | Change |
184 |-----------|--------|
185 | 001 | feeds table (sources) |
186 | 002 | feed_items table (fetched content) |
187 | 003 | busser_state (plugin key-value storage) |
188 | 004 | feed_tags (user-assigned tags on feeds) |
189 | 005 | feed_items_fts (FTS5 full-text search) |
190 | 006 | Feed health tracking (consecutive_failures, last_error, last_success_at) |
191 | 007 | SyncKit integration (user_config, sync_state, sync_changelog, sync triggers) |
192 | 008 | Circuit breaker (circuit_broken column, updated sync triggers) |
193 | 009 | Query feeds (saved filter rules as virtual sources) |
194 | 010 | Item actions (actions JSON column on feed_items) |
195