1
2
3
4
5
6
7
8
9
10
11
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
..
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
157
158
159
160
161
162
163
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
189
190
191
192
193
194
195
196
197
198
|
\prompt 'domain name: ' domain
\prompt 'instance name: ' inst
\prompt 'bind to socket: ' bind
\qecho 'how locked down should this server be? public = anyone can see public timeline and tweets, private = anyone can see tweets with a link but login required for everything else, lockdown = login required for all activities, isolate = like lockdown but with federation protocols completely disabled'
\prompt 'security mode: ' secmode
\qecho 'should user self-registration be allowed? yes or no'
\prompt 'registration: ' regpol
\qecho 'by default, parsav tracks rights on its own. you can override this later by replacing the rights table with a view, but you''ll then need to set appropriate rules on the view to allow administrators to modify rights from the web UI, or set the rights-readonly flag in the config table to true. for now, enter the name of an actor who will be granted full rights when she logs in and identified as the server owner.'
\prompt 'master actor: ' admin
\qecho 'you will need to create an authentication view named parsav_auth mapping your user database to something parsav can understand; see auth.sql for an example.'
begin;
drop table if exists parsav_config;
create table if not exists parsav_config (
key text primary key,
value text
);
insert into parsav_config (key,value) values
('bind',:'bind'),
('domain',:'domain'),
('instance-name',:'inst'),
('policy-security',:'secmode'),
('policy-self-register',:'regpol'),
('master',:'admin'),
('server-secret', encode(
digest(int8send((2^63 * (random()*2 - 1))::bigint),
'sha512'), 'base64'));
-- note that valid ids should always > 0, as 0 is reserved for null
-- on the client side, vastly simplifying code
drop table if exists parsav_servers cascade;
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
);
drop table if exists parsav_actors cascade;
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,
bio text,
avataruri text, -- null if local
rank smallint not null default 0,
quota integer not null default 1000,
key bytea, -- private if localactor; public if remote
title text,
unique (handle,origin)
);
drop table if exists parsav_rights cascade;
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)
);
insert into parsav_actors (handle,rank,quota) values (:'admin',1,0);
insert into parsav_rights (actor,key,allow)
select (select id from parsav_actors where handle=:'admin'), a.column1, a.column2 from (values
('purge',true),
('config',true),
('censor',true),
('suspend',true),
('cred',true),
('elevate',true),
('demote',true),
('rebrand',true)
) as a;
drop table if exists parsav_posts cascade;
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,
................................................................................
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
);
drop table if exists parsav_conversations cascade;
drop table if exists parsav_rels cascade;
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)
);
drop table if exists parsav_acts cascade;
create table parsav_acts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
kind text not null, -- like, 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
);
drop table if exists parsav_log cascade;
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
);
drop table if exists parsav_attach cascade;
create table parsav_attach (
id bigint primary key default (1+random()*(2^63-1))::bigint,
birth timestamp not null default now(),
content bytea not null,
mime text, -- null if unknown, will be reported as x-octet-stream
description text,
parent bigint -- post id, or userid for avatars
);
drop table if exists parsav_circles cascade;
create table parsav_circles (
id bigint primary key default (1+random()*(2^63-1))::bigint,
owner bigint not null references parsav_actors(id),
name text not null,
members bigint[] not null default array[]::bigint[],
unique (owner,name)
);
drop table if exists parsav_rooms cascade;
create table parsav_rooms (
id bigint primary key default (1+random()*(2^63-1))::bigint,
origin bigint references parsav_servers(id),
name text not null,
description text not null,
policy smallint not null
);
drop table if exists parsav_room_members cascade;
create table parsav_room_members (
room bigint references parsav_rooms(id),
member bigint references parsav_actors(id),
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)
);
drop table if exists parsav_invites cascade;
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),
handle text, -- admin can lock invite to specific handle
rank smallint not null default 0,
quota integer not null default 1000
);
drop table if exists parsav_interventions cascade;
create table parsav_interventions (
id bigint primary key default (1+random()*(2^63-1))::bigint,
issuer bigint references parsav_actors(id) not null,
scope bigint, -- can be null or room for local actions
nature smallint not null, -- silence, suspend, disemvowel, etc
victim bigint not null, -- could potentially target group as well
expire timestamp -- auto-expires if set
);
end;
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
|
>
|
|
|
|
|
|
<
<
<
<
<
|
>
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
>
>
|
1
2
3
4
5
6
7
8
9
10
11
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
..
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
149
150
151
152
153
154
155
156
157
|
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'),
-- ('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,
bio text,
avataruri text, -- null if local
rank smallint not null default 0,
quota integer not null default 1000,
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,
................................................................................
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, 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
);
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_attach (
id bigint primary key default (1+random()*(2^63-1))::bigint,
birth timestamp not null default now(),
content bytea not null,
mime text, -- null if unknown, will be reported as x-octet-stream
description text,
parent bigint -- post id, or userid for avatars
);
create table parsav_circles (
id bigint primary key default (1+random()*(2^63-1))::bigint,
owner bigint not null references parsav_actors(id),
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),
name text not null,
description text not null,
policy smallint not null
);
create table parsav_room_members (
room bigint references parsav_rooms(id),
member bigint references parsav_actors(id),
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)
);
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),
handle text, -- admin can lock invite to specific handle
rank smallint not null default 0,
quota integer not null default 1000
);
create table parsav_interventions (
id bigint primary key default (1+random()*(2^63-1))::bigint,
issuer bigint references parsav_actors(id) not null,
scope bigint, -- can be null or room for local actions
nature smallint not null, -- silence, suspend, disemvowel, etc
victim bigint not null, -- could potentially target group as well
expire timestamp -- auto-expires if set
);
-- create a temporary managed auth table; we can delete this later
-- if it ends up being replaced with a view
%include pgsql-auth.sql%
|