experiments in a post-browser web
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', 'entity', 'event')),
9 content TEXT,
10 mimeType TEXT DEFAULT '',
11 metadata TEXT DEFAULT '{}',
12 syncId TEXT DEFAULT '',
13 syncedAt INTEGER DEFAULT 0,
14 createdAt INTEGER NOT NULL,
15 updatedAt INTEGER NOT NULL,
16 deletedAt INTEGER DEFAULT 0,
17 starred INTEGER DEFAULT 0,
18 archived INTEGER DEFAULT 0,
19 visitCount INTEGER DEFAULT 0,
20 lastVisitAt INTEGER DEFAULT 0,
21 frecencyScore INTEGER DEFAULT 0,
22 title TEXT DEFAULT '',
23 domain TEXT DEFAULT '',
24 favicon TEXT DEFAULT ''
25);
26
27CREATE INDEX IF NOT EXISTS idx_items_type ON items(type);
28CREATE INDEX IF NOT EXISTS idx_items_syncId ON items(syncId);
29CREATE INDEX IF NOT EXISTS idx_items_deletedAt ON items(deletedAt);
30CREATE INDEX IF NOT EXISTS idx_items_createdAt ON items(createdAt DESC);
31CREATE INDEX IF NOT EXISTS idx_items_starred ON items(starred);
32CREATE INDEX IF NOT EXISTS idx_items_lastVisitAt ON items(lastVisitAt);
33CREATE INDEX IF NOT EXISTS idx_items_visitCount ON items(visitCount);
34CREATE INDEX IF NOT EXISTS idx_items_frecencyScore ON items(frecencyScore DESC);
35CREATE INDEX IF NOT EXISTS idx_items_domain ON items(domain);
36
37-- Tag definitions with frecency tracking
38CREATE TABLE IF NOT EXISTS tags (
39 id TEXT PRIMARY KEY NOT NULL,
40 name TEXT NOT NULL UNIQUE,
41 frequency INTEGER DEFAULT 1,
42 lastUsed INTEGER NOT NULL,
43 frecencyScore REAL DEFAULT 0.0,
44 createdAt INTEGER NOT NULL,
45 updatedAt INTEGER NOT NULL,
46 slug TEXT,
47 color TEXT DEFAULT '#999999',
48 parentId TEXT DEFAULT '',
49 description TEXT DEFAULT '',
50 metadata TEXT DEFAULT '{}'
51);
52
53CREATE INDEX IF NOT EXISTS idx_tags_name ON tags(name);
54CREATE INDEX IF NOT EXISTS idx_tags_frecency ON tags(frecencyScore DESC);
55CREATE INDEX IF NOT EXISTS idx_tags_slug ON tags(slug);
56CREATE INDEX IF NOT EXISTS idx_tags_parentId ON tags(parentId);
57
58-- Junction table linking items to tags
59CREATE TABLE IF NOT EXISTS item_tags (
60 id TEXT PRIMARY KEY NOT NULL,
61 itemId TEXT NOT NULL,
62 tagId TEXT NOT NULL,
63 createdAt INTEGER NOT NULL
64);
65
66CREATE INDEX IF NOT EXISTS idx_item_tags_itemId ON item_tags(itemId);
67CREATE INDEX IF NOT EXISTS idx_item_tags_tagId ON item_tags(tagId);
68CREATE UNIQUE INDEX IF NOT EXISTS idx_item_tags_unique ON item_tags(itemId, tagId);
69
70-- Event-dispatch rules — declarative subscribers that tie an event topic + match predicate to a script. Drives the rule engine that lets features participate in event pipelines without being permanently resident. See peek MCP plan 3d7d9239.
71CREATE TABLE IF NOT EXISTS rules (
72 id TEXT PRIMARY KEY NOT NULL,
73 featureId TEXT NOT NULL,
74 topic TEXT NOT NULL,
75 match TEXT DEFAULT '{}',
76 scriptId TEXT NOT NULL,
77 config TEXT DEFAULT '{}',
78 display TEXT DEFAULT '',
79 enabled INTEGER DEFAULT 1,
80 source TEXT NOT NULL CHECK(source IN ('manifest', 'user', 'runtime')),
81 ordering INTEGER DEFAULT 0,
82 createdAt INTEGER NOT NULL,
83 updatedAt INTEGER NOT NULL
84);
85
86CREATE INDEX IF NOT EXISTS idx_rules_topic ON rules(topic);
87CREATE INDEX IF NOT EXISTS idx_rules_featureId ON rules(featureId);
88CREATE INDEX IF NOT EXISTS idx_rules_source ON rules(source);
89CREATE INDEX IF NOT EXISTS idx_rules_enabled ON rules(enabled);
90
91-- Events/entries for series and feeds - append-only time-series data
92CREATE TABLE IF NOT EXISTS item_events (
93 id TEXT PRIMARY KEY NOT NULL,
94 itemId TEXT NOT NULL,
95 content TEXT,
96 value REAL,
97 occurredAt INTEGER NOT NULL,
98 metadata TEXT DEFAULT '{}',
99 createdAt INTEGER NOT NULL
100);
101
102CREATE INDEX IF NOT EXISTS idx_item_events_item_time ON item_events(itemId, occurredAt DESC);
103CREATE INDEX IF NOT EXISTS idx_item_events_occurred ON item_events(occurredAt DESC);