Live video on the AT Protocol
stream.place
1-- Streamers dimension table
2CREATE TABLE IF NOT EXISTS streamers (
3 streamer_did String,
4 username String,
5 display_name String,
6 created_at DateTime64(3),
7 updated_at DateTime64(3),
8
9 -- aggregates
10 total_streams UInt32 DEFAULT 0,
11 total_events UInt64 DEFAULT 0,
12 follower_count UInt32 DEFAULT 0
13)
14ENGINE = ReplacingMergeTree(updated_at)
15ORDER BY streamer_did;
16
17-- Follows table
18CREATE TABLE IF NOT EXISTS follows (
19 follower_did String,
20 streamer_did String,
21 followed_at DateTime64(3),
22 unfollowed_at Nullable(DateTime64(3)),
23 status LowCardinality(String), -- active, unfollowed
24 updated_at DateTime64(3)
25)
26ENGINE = ReplacingMergeTree(updated_at)
27ORDER BY (follower_did, streamer_did);
28
29-- Streams dimension table
30CREATE TABLE IF NOT EXISTS streams (
31 stream_id String,
32 streamer_did String,
33 title String,
34 started_at DateTime64(3),
35 ended_at Nullable(DateTime64(3)),
36 status LowCardinality(String), -- live, ended, error
37
38 -- metadata
39 created_at DateTime64(3),
40 updated_at DateTime64(3),
41
42 -- aggregates
43 total_viewers UInt32 DEFAULT 0,
44 peak_viewers UInt32 DEFAULT 0,
45 total_events UInt64 DEFAULT 0
46)
47ENGINE = ReplacingMergeTree(updated_at)
48ORDER BY stream_id;
49
50-- Events table
51CREATE TABLE IF NOT EXISTS events (
52 event_id UUID,
53 event_type LowCardinality(String),
54 device_id String,
55 did Nullable(String),
56 session_id String,
57
58 timestamp DateTime64(3),
59
60 streamer_did String,
61 stream_id Nullable(String),
62
63 properties String,
64 schema_version UInt16,
65
66 client_version String,
67 platform LowCardinality(String)
68)
69ENGINE = MergeTree()
70PARTITION BY toYYYYMM(timestamp)
71ORDER BY (streamer_did, timestamp, device_id);
72
73-- Deletion requests audit table
74CREATE TABLE IF NOT EXISTS deletion_requests (
75 request_id UUID,
76 did String,
77 requested_at DateTime64(3),
78 completed_at Nullable(DateTime64(3)),
79 status LowCardinality(String)
80)
81ENGINE = MergeTree()
82ORDER BY requested_at;