solpbc.org
open source is social v-it.org
1CREATE TABLE IF NOT EXISTS caps (
2 id INTEGER PRIMARY KEY AUTOINCREMENT,
3 did TEXT NOT NULL,
4 rkey TEXT NOT NULL,
5 uri TEXT NOT NULL UNIQUE,
6 cid TEXT,
7 title TEXT NOT NULL,
8 description TEXT,
9 ref TEXT NOT NULL,
10 beacon TEXT,
11 kind TEXT,
12 record_json TEXT NOT NULL,
13 created_at TEXT NOT NULL,
14 indexed_at TEXT NOT NULL DEFAULT (datetime('now')),
15 UNIQUE(did, rkey)
16);
17
18CREATE INDEX IF NOT EXISTS idx_caps_beacon ON caps(beacon);
19CREATE INDEX IF NOT EXISTS idx_caps_created_at ON caps(created_at DESC);
20CREATE INDEX IF NOT EXISTS idx_caps_ref ON caps(ref);
21CREATE INDEX IF NOT EXISTS idx_caps_kind ON caps(kind);
22
23CREATE TABLE IF NOT EXISTS vouches (
24 id INTEGER PRIMARY KEY AUTOINCREMENT,
25 did TEXT NOT NULL,
26 rkey TEXT NOT NULL,
27 uri TEXT NOT NULL UNIQUE,
28 cid TEXT,
29 cap_uri TEXT NOT NULL,
30 ref TEXT NOT NULL,
31 beacon TEXT,
32 kind TEXT,
33 record_json TEXT NOT NULL,
34 created_at TEXT NOT NULL,
35 indexed_at TEXT NOT NULL DEFAULT (datetime('now')),
36 UNIQUE(did, rkey)
37);
38
39CREATE INDEX IF NOT EXISTS idx_vouches_cap_uri ON vouches(cap_uri);
40CREATE INDEX IF NOT EXISTS idx_vouches_beacon ON vouches(beacon);
41CREATE INDEX IF NOT EXISTS idx_vouches_kind ON vouches(kind);
42
43CREATE TABLE IF NOT EXISTS beacons (
44 id INTEGER PRIMARY KEY AUTOINCREMENT,
45 name TEXT NOT NULL UNIQUE,
46 cap_count INTEGER NOT NULL DEFAULT 0,
47 vouch_count INTEGER NOT NULL DEFAULT 0,
48 last_activity TEXT NOT NULL DEFAULT (datetime('now'))
49);
50
51CREATE TABLE IF NOT EXISTS handles (
52 did TEXT PRIMARY KEY,
53 handle TEXT NOT NULL,
54 fetched_at TEXT NOT NULL DEFAULT (datetime('now'))
55);
56
57CREATE TABLE IF NOT EXISTS skills (
58 id INTEGER PRIMARY KEY AUTOINCREMENT,
59 did TEXT NOT NULL,
60 rkey TEXT NOT NULL,
61 uri TEXT NOT NULL UNIQUE,
62 cid TEXT,
63 name TEXT NOT NULL,
64 description TEXT,
65 ref TEXT NOT NULL,
66 version TEXT,
67 tags TEXT,
68 record_json TEXT NOT NULL,
69 created_at TEXT NOT NULL,
70 indexed_at TEXT NOT NULL DEFAULT (datetime('now')),
71 UNIQUE(did, rkey)
72);
73
74CREATE INDEX IF NOT EXISTS idx_skills_created_at ON skills(created_at DESC);
75CREATE INDEX IF NOT EXISTS idx_skills_name ON skills(name);