Social Annotations in the Atmosphere
1-- Create annotations table
2CREATE TABLE IF NOT EXISTS annotations (
3 uri TEXT PRIMARY KEY,
4 cid TEXT NOT NULL,
5 author_did TEXT NOT NULL,
6 author_handle TEXT,
7 target_url TEXT NOT NULL,
8 exact_text TEXT,
9 prefix TEXT,
10 suffix TEXT,
11 position_start INTEGER,
12 position_end INTEGER,
13 selectors_json TEXT NOT NULL,
14 body TEXT,
15 tags TEXT,
16 created_at TIMESTAMP NOT NULL,
17 indexed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
18);
19
20-- Create indexes for fast lookups
21CREATE INDEX IF NOT EXISTS idx_target_url ON annotations(target_url);
22CREATE INDEX IF NOT EXISTS idx_author_did ON annotations(author_did);
23CREATE INDEX IF NOT EXISTS idx_created_at ON annotations(created_at DESC);
24
25-- Create cursors table for firehose position tracking
26CREATE TABLE IF NOT EXISTS cursors (
27 name TEXT PRIMARY KEY,
28 seq INTEGER NOT NULL,
29 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
30);
31
32-- Create schema_migrations table to track applied migrations
33CREATE TABLE IF NOT EXISTS schema_migrations (
34 version INTEGER PRIMARY KEY,
35 applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
36);