Skip to main content

max / balanced_breakfast

1.2 KB · 41 lines History Blame Raw
1 -- Feature: Feed items
2 -- Create feed_items table for all fetched items
3 CREATE TABLE IF NOT EXISTS feed_items (
4 id TEXT PRIMARY KEY,
5 external_id TEXT NOT NULL UNIQUE,
6 feed_id TEXT NOT NULL REFERENCES feeds(id) ON DELETE CASCADE,
7 busser_id TEXT NOT NULL,
8
9 -- Bite display fields (compact view)
10 bite_author TEXT NOT NULL,
11 bite_text TEXT NOT NULL,
12 bite_secondary TEXT,
13 bite_indicator TEXT,
14
15 -- Full content fields
16 title TEXT,
17 body TEXT,
18 url TEXT,
19 media TEXT NOT NULL DEFAULT '[]',
20
21 -- Metadata
22 published_at TEXT NOT NULL,
23 fetched_at TEXT NOT NULL DEFAULT (datetime('now')),
24 source_name TEXT NOT NULL,
25 score INTEGER,
26 tags TEXT NOT NULL DEFAULT '[]',
27
28 -- User state
29 is_read INTEGER NOT NULL DEFAULT 0,
30 is_starred INTEGER NOT NULL DEFAULT 0,
31
32 created_at TEXT NOT NULL DEFAULT (datetime('now')),
33 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
34 );
35
36 CREATE INDEX idx_feed_items_feed_id ON feed_items(feed_id);
37 CREATE INDEX idx_feed_items_busser_id ON feed_items(busser_id);
38 CREATE INDEX idx_feed_items_published_at ON feed_items(published_at DESC);
39 CREATE INDEX idx_feed_items_is_read ON feed_items(is_read);
40 CREATE INDEX idx_feed_items_is_starred ON feed_items(is_starred);
41