12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
..
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
...
146
147
148
149
150
151
152
153
154
155
156
157
158
159
...
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
|
-- ('policy-self-register',:'regpol'),
-- ('master',:'admin'),
-- note that valid ids should always > 0, as 0 is reserved for null
-- on the client side, vastly simplifying code
create table parsav_servers (
id bigint primary key default (1+random()*(2^63-1))::bigint,
domain text not null,
key bytea,
knownsince timestamp,
parsav boolean -- whether to use parsav protocol extensions
);
create table parsav_actors (
id bigint primary key default (1+random()*(2^63-1))::bigint,
nym text,
handle text not null, -- nym [@handle@origin]
origin bigint references parsav_servers(id)
on delete cascade, -- null origin = local actor
knownsince timestamp not null default now(),
bio text,
avatarid bigint, -- artifact id, null if remote
avataruri text, -- null if local
rank smallint not null default 0,
quota integer not null default 1000,
invites integer not null default 0,
key bytea, -- private if localactor; public if remote
epithet text,
authtime timestamp not null default now(), -- cookies earlier than this timepoint will not be accepted
unique (handle,origin)
);
create table parsav_rights (
key text,
actor bigint references parsav_actors(id)
on delete cascade,
allow boolean not null,
scope bigint, -- for future expansion
primary key (key,actor)
);
create table parsav_posts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
author bigint references parsav_actors(id)
on delete cascade,
subject text,
acl text not null default 'all', -- just store the script raw 🤷
body text,
posted timestamp not null,
discovered timestamp not null,
chgcount integer not null default 0,
edited timestamp,
parent bigint not null default 0, -- if post: part of conversation; if chatroom: top-level post
circles bigint[], -- TODO at edit or creation, iterate through each circle
mentions bigint[], -- a user has, check if it can see her post, and if so add
artifacts bigint[],
convoheaduri text
-- only used for tracking foreign conversations and tying them to post heads;
-- local conversations are tracked directly and mapped to URIs based on the
-- head's ID. null if native tweet or not the first tweet in convo
);
create table parsav_rels (
relator bigint references parsav_actors(id)
on delete cascade, -- e.g. follower
relatee bigint references parsav_actors(id)
on delete cascade, -- e.g. followed
kind smallint, -- e.g. follow, block, mute
................................................................................
primary key (relator, relatee, kind)
);
create table parsav_acts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
kind text not null, -- like, rt, react, so on
time timestamp not null default now(),
actor bigint references parsav_actors(id)
on delete cascade,
subject bigint, -- may be post or act, depending on kind
body text -- emoji, if react
);
create table parsav_log (
-- accesses are tracked for security & sending delete acts
id bigint primary key default (1+random()*(2^63-1))::bigint,
time timestamp not null default now(),
actor bigint references parsav_actors(id)
on delete cascade,
post bigint not null
);
create table parsav_artifacts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
birth timestamp not null default now(),
content bytea, -- if null, this is a "ban record" preventing content matching the hash from being re-uploaded
hash bytea unique not null, -- sha256 hash of content
-- it would be cool to use a computed column for this, but i don't want
-- to lock people into PG12 or drag in the pgcrypto extension just for this
mime text -- null if unknown, will be reported as x-octet-stream
);
create index on parsav_artifacts (mime);
create table parsav_artifact_claims (
birth timestamp not null default now(),
uid bigint references parsav_actors(id) on delete cascade,
rid bigint references parsav_artifacts(id) on delete cascade,
description text,
folder text,
unique (uid,rid)
);
create index on parsav_artifact_claims (uid);
create table parsav_circles (
id bigint primary key default (1+random()*(2^63-1))::bigint,
owner bigint not null references parsav_actors(id) on delete cascade,
name text not null,
members bigint[] not null default array[]::bigint[],
unique (owner,name)
);
create table parsav_rooms (
id bigint primary key default (1+random()*(2^63-1))::bigint,
origin bigint references parsav_servers(id) on delete cascade,
name text not null,
description text not null,
policy smallint not null
................................................................................
room bigint not null references parsav_rooms(id) on delete cascade,
member bigint not null references parsav_actors(id) on delete cascade,
rank smallint not null default 0,
admin boolean not null default false, -- non-admins with rank can only moderate + invite
title text, -- admin-granted title like reddit flair
vouchedby bigint references parsav_actors(id) on delete set null
);
create table parsav_invites (
id bigint primary key default (1+random()*(2^63-1))::bigint,
-- when a user is created from an invite, the invite is deleted and the invite
-- ID becomes the user ID. privileges granted on the invite ID during the invite
-- process are thus inherited by the user
issuer bigint references parsav_actors(id) on delete set null,
................................................................................
create table parsav_sanctions (
id bigint primary key default (1+random()*(2^63-1))::bigint,
issuer bigint references parsav_actors(id) on delete set null,
scope bigint, -- can be null or room for local actions
nature smallint not null, -- silence, suspend, disemvowel, censor, noreply, etc
victim bigint not null, -- can be user, room, or post
expire timestamp, -- auto-expires if set
review timestamp, -- brings up for review at given time if set
reason text, -- visible to victim if set
context text -- admin-only note
);
create table parsav_actor_conf_strs (
uid bigint not null references parsav_actors(id) on delete cascade,
key text not null, value text not null, unique (uid,key)
);
create table parsav_actor_conf_ints (
uid bigint not null references parsav_actors(id) on delete cascade,
key text not null, value bigint not null, unique (uid,key)
);
-- create a temporary managed auth table; we can delete this later
-- if it ends up being replaced with a view
%include pgsql-auth.sql%
|
|
|
|
|
>
|
<
|
|
|
>
>
|
|
<
>
>
>
|
|
|
>
>
>
>
|
|
|
>
>
>
|
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
..
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
...
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
...
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
|
-- ('policy-self-register',:'regpol'),
-- ('master',:'admin'),
-- note that valid ids should always > 0, as 0 is reserved for null
-- on the client side, vastly simplifying code
create table parsav_servers (
id bigint primary key default (1+random()*(2^63-1))::bigint,
domain text not null unique,
key bytea,
knownsince bigint,
parsav boolean -- whether to use parsav protocol extensions
);
create table parsav_actors (
id bigint primary key default (1+random()*(2^63-1))::bigint,
nym text,
handle text not null, -- nym [@handle@origin]
origin bigint references parsav_servers(id)
on delete cascade, -- null origin = local actor
knownsince bigint not null,
bio text,
avatarid bigint, -- artifact id, null if remote
avataruri text, -- null if local
rank smallint not null default 0,
quota integer not null default 1000,
invites integer not null default 0,
key bytea, -- private if localactor; public if remote
epithet text,
authtime bigint not null, -- cookies earlier than this timepoint will not be accepted
unique (handle,origin)
);
create table parsav_rights (
key text,
actor bigint references parsav_actors(id)
on delete cascade,
allow boolean not null,
scope bigint, -- for future expansion
primary key (key,actor)
);
create index on parsav_rights (actor);
create table parsav_posts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
author bigint references parsav_actors(id) on delete cascade,
subject text,
acl text not null default 'all', -- just store the script raw 🤷
body text,
posted bigint not null,
discovered bigint not null,
chgcount integer not null default 0,
edited bigint,
parent bigint not null default 0, -- if post: part of conversation; if chatroom: top-level post
circles bigint[], -- TODO at edit or creation, iterate through each circle
mentions bigint[], -- a user has, check if it can see her post, and if so add
artifacts bigint[],
convoheaduri text
-- only used for tracking foreign conversations and tying them to post heads;
-- local conversations are tracked directly and mapped to URIs based on the
-- head's ID. null if native tweet or not the first tweet in convo
);
create index on parsav_posts (author);
create index on parsav_posts (parent);
create table parsav_rels (
relator bigint references parsav_actors(id)
on delete cascade, -- e.g. follower
relatee bigint references parsav_actors(id)
on delete cascade, -- e.g. followed
kind smallint, -- e.g. follow, block, mute
................................................................................
primary key (relator, relatee, kind)
);
create table parsav_acts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
kind text not null, -- like, rt, react, so on
time bigint not null,
actor bigint references parsav_actors(id) on delete cascade,
subject bigint, -- may be post or act, depending on kind
body text -- emoji, if react
);
create index on parsav_acts (subject);
create index on parsav_acts (actor);
create index on parsav_acts (time);
create table parsav_log (
-- accesses are tracked for security & sending delete acts
id bigint primary key default (1+random()*(2^63-1))::bigint,
time bigint not null,
actor bigint references parsav_actors(id)
on delete cascade,
post bigint not null
);
create table parsav_artifacts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
birth bigint not null,
content bytea, -- if null, this is a "ban record" preventing content matching the hash from being re-uploaded
hash bytea unique not null, -- sha256 hash of content
-- it would be cool to use a computed column for this, but i don't want
-- to lock people into PG12 or drag in the pgcrypto extension just for this
mime text -- null if unknown, will be reported as x-octet-stream
);
create index on parsav_artifacts (mime);
create table parsav_artifact_claims (
birth bigint not null,
uid bigint references parsav_actors(id) on delete cascade,
rid bigint references parsav_artifacts(id) on delete cascade,
description text,
folder text,
unique (uid,rid)
);
create index on parsav_artifact_claims (uid);
create index on parsav_artifact_claims (uid,folder);
create table parsav_circles (
id bigint primary key default (1+random()*(2^63-1))::bigint,
owner bigint not null references parsav_actors(id) on delete cascade,
name text not null,
members bigint[] not null default array[]::bigint[],
unique (owner,name)
);
create index on parsav_circles (owner);
create table parsav_rooms (
id bigint primary key default (1+random()*(2^63-1))::bigint,
origin bigint references parsav_servers(id) on delete cascade,
name text not null,
description text not null,
policy smallint not null
................................................................................
room bigint not null references parsav_rooms(id) on delete cascade,
member bigint not null references parsav_actors(id) on delete cascade,
rank smallint not null default 0,
admin boolean not null default false, -- non-admins with rank can only moderate + invite
title text, -- admin-granted title like reddit flair
vouchedby bigint references parsav_actors(id) on delete set null
);
create index on parsav_room_members (member);
create index on parsav_room_members (room);
create table parsav_invites (
id bigint primary key default (1+random()*(2^63-1))::bigint,
-- when a user is created from an invite, the invite is deleted and the invite
-- ID becomes the user ID. privileges granted on the invite ID during the invite
-- process are thus inherited by the user
issuer bigint references parsav_actors(id) on delete set null,
................................................................................
create table parsav_sanctions (
id bigint primary key default (1+random()*(2^63-1))::bigint,
issuer bigint references parsav_actors(id) on delete set null,
scope bigint, -- can be null or room for local actions
nature smallint not null, -- silence, suspend, disemvowel, censor, noreply, etc
victim bigint not null, -- can be user, room, or post
expire bigint, -- auto-expires if set
review bigint, -- brings up for review at given time if set
reason text, -- visible to victim if set
context text, -- admin-only note
appeal text -- null if no appeal lodged
);
create index on parsav_sanctions (victim,scope);
create index on parsav_sanctions (issuer);
create table parsav_actor_conf_strs (
uid bigint not null references parsav_actors(id) on delete cascade,
key text not null, value text not null, unique (uid,key)
);
create table parsav_actor_conf_ints (
uid bigint not null references parsav_actors(id) on delete cascade,
key text not null, value bigint not null, unique (uid,key)
);
-- create a temporary managed auth table; we can delete this later
-- if it ends up being replaced with a view
%include pgsql-auth.sql%
|