experiments in a post-browser web
burrito.space
1-- Generated by schema/codegen.js
2-- Schema version: 1
3-- DO NOT EDIT - regenerate with: yarn schema:codegen
4
5-- Unified content storage - URLs, text notes, tagsets, and images
6CREATE TABLE IF NOT EXISTS items (
7 id TEXT PRIMARY KEY NOT NULL,
8 type TEXT NOT NULL CHECK(type IN ('url', 'text', 'tagset', 'image', 'series', 'feed')),
9 content TEXT,
10 mimeType TEXT DEFAULT '',
11 metadata TEXT DEFAULT '{}',
12 syncId TEXT DEFAULT '',
13 syncSource TEXT DEFAULT '',
14 syncedAt INTEGER DEFAULT 0,
15 createdAt INTEGER NOT NULL,
16 updatedAt INTEGER NOT NULL,
17 deletedAt INTEGER DEFAULT 0,
18 starred INTEGER DEFAULT 0,
19 archived INTEGER DEFAULT 0
20);
21
22CREATE INDEX IF NOT EXISTS idx_items_type ON items(type);
23CREATE INDEX IF NOT EXISTS idx_items_syncId ON items(syncId);
24CREATE INDEX IF NOT EXISTS idx_items_deletedAt ON items(deletedAt);
25CREATE INDEX IF NOT EXISTS idx_items_createdAt ON items(createdAt DESC);
26CREATE INDEX IF NOT EXISTS idx_items_starred ON items(starred);
27
28-- Tag definitions with frecency tracking
29CREATE TABLE IF NOT EXISTS tags (
30 id TEXT PRIMARY KEY NOT NULL,
31 name TEXT NOT NULL UNIQUE,
32 frequency INTEGER DEFAULT 1,
33 lastUsed INTEGER NOT NULL,
34 frecencyScore REAL DEFAULT 0.0,
35 createdAt INTEGER NOT NULL,
36 updatedAt INTEGER NOT NULL
37);
38
39CREATE INDEX IF NOT EXISTS idx_tags_name ON tags(name);
40CREATE INDEX IF NOT EXISTS idx_tags_frecency ON tags(frecencyScore DESC);
41
42-- Junction table linking items to tags
43CREATE TABLE IF NOT EXISTS item_tags (
44 itemId TEXT NOT NULL,
45 tagId TEXT NOT NULL,
46 createdAt INTEGER NOT NULL
47);
48
49CREATE INDEX IF NOT EXISTS idx_item_tags_itemId ON item_tags(itemId);
50CREATE INDEX IF NOT EXISTS idx_item_tags_tagId ON item_tags(tagId);
51CREATE UNIQUE INDEX IF NOT EXISTS idx_item_tags_unique ON item_tags(itemId, tagId);
52
53-- Events/entries for series and feeds - append-only time-series data
54CREATE TABLE IF NOT EXISTS item_events (
55 id TEXT PRIMARY KEY NOT NULL,
56 itemId TEXT NOT NULL,
57 content TEXT,
58 value REAL,
59 occurredAt INTEGER NOT NULL,
60 metadata TEXT DEFAULT '{}',
61 createdAt INTEGER NOT NULL
62);
63
64CREATE INDEX IF NOT EXISTS idx_item_events_item_time ON item_events(itemId, occurredAt DESC);
65CREATE INDEX IF NOT EXISTS idx_item_events_occurred ON item_events(occurredAt DESC);