1
2
3
4
5
6
7
8
9
10
11
..
17
18
19
20
21
22
23
24
25
26
27
28
29
30
..
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
..
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
...
143
144
145
146
147
148
149
150
151
152
153
154
155
156
...
165
166
167
168
169
170
171
172
173
174
175
176
177
178
...
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
|
create table parsav_config (
key text primary key,
value text
);
insert into parsav_config (key,value) values ('schema-version','1'),
('credential-store','managed');
-- ('bind',:'bind'),
-- ('domain',:'domain'),
-- ('instance-name',:'inst'),
-- ('policy-security',:'secmode'),
................................................................................
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
................................................................................
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,
................................................................................
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[],
................................................................................
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
);
create table parsav_room_members (
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
................................................................................
-- 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,
handle text, -- admin can lock invite to specific handle
rank smallint not null default 0,
quota integer not null default 1000
);
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
................................................................................
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%
|
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
..
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
..
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
..
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
149
150
151
152
153
154
155
156
...
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
...
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
...
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
|
create table parsav_config (
key text primary key,
value text
);
comment on table parsav_config is
'server-wide configuration variables. highly sensitive!';
insert into parsav_config (key,value) values ('schema-version','1'),
('credential-store','managed');
-- ('bind',:'bind'),
-- ('domain',:'domain'),
-- ('instance-name',:'inst'),
-- ('policy-security',:'secmode'),
................................................................................
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
);
comment on table parsav_servers is
'all servers known to the parsav instance. the local server (including its private key) is stored in row (id = 0)';
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
................................................................................
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)
);
comment on table parsav_actors is
'all users known to the instance across the fediverse; local users satisfy constraint (origin = 0)';
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);
comment on table parsav_rights is
'a backward-compatible list of every non-default privilege or deprivilege granted to a local user';
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,
................................................................................
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)
);
comment on table parsav_rels is
'all relationships, positive and negative, between local users and other users; kind is a version-specific integer mapping to a type-of-relationship enum in store.t';
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; complaint, if report
);
create index on parsav_acts (subject);
create index on parsav_acts (actor);
create index on parsav_acts (time);
comment on table parsav_acts is
'every simple action taken on a tweet by an actor, including likes, rts, reacts, and reports';
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
);
comment on table parsav_log is
'a log of accesses from foreign servers, tracking which will be sent update & delete events for each post';
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 octet-stream
);
create index on parsav_artifacts (mime);
comment on table parsav_artifacts is
'deduplicated media files uploaded by users';
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);
comment on table parsav_artifact_claims is
'a list of users who have an ownership interest in each artifact (effectively an index of GC roots)';
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[],
................................................................................
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
);
comment on table parsav_rooms is
'an index of user-created chatrooms';
create table parsav_room_members (
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
................................................................................
-- 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,
handle text, -- admin can lock invite to specific handle
rank smallint not null default 0,
quota integer not null default 1000
);
comment on table parsav_invites is
'all active invitations and the level of authority they grant if accepted';
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
................................................................................
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);
comment on table parsav_sanctions is
'administrative actions taken against particular users, posts, rooms, or other entities';
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)
);
comment on table parsav_actor_conf_strs is 'per-user configuration settings (string properties)';
comment on table parsav_actor_conf_ints is 'per-user configuration settings (integer and enumeration properties)';
-- create a temporary managed auth table; we can delete this later
-- if it ends up being replaced with a view
%include pgsql-auth.sql%
|