Differences From
Artifact [636689e0dd]:
1 1 \prompt 'domain name: ' domain
2 2 \prompt 'instance name: ' inst
3 3 \prompt 'bind to socket: ' bind
4 -\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.'
5 -\prompt 'admin actor: ' admin
4 +\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'
5 +\prompt 'security mode: ' secmode
6 +\qecho 'should user self-registration be allowed? yes or no'
7 +\prompt 'registration: ' regpol
8 +\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.'
9 +\prompt 'master actor: ' admin
6 10 \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.'
7 11
8 12 begin;
9 13
10 14 drop table if exists parsav_config;
11 15 create table if not exists parsav_config (
12 - key text primary key,
16 + key text primary key,
13 17 value text
14 18 );
15 19
16 20 insert into parsav_config (key,value) values
17 21 ('bind',:'bind'),
18 22 ('domain',:'domain'),
19 23 ('instance-name',:'inst'),
20 - ('administrator',:'admin'),
24 + ('policy-security',:'secmode'),
25 + ('policy-self-register',:'regpol'),
26 + ('master',:'admin'),
21 27 ('server-secret', encode(
22 28 digest(int8send((2^63 * (random()*2 - 1))::bigint),
23 29 'sha512'), 'base64'));
24 30
25 31 -- note that valid ids should always > 0, as 0 is reserved for null
26 32 -- on the client side, vastly simplifying code
27 33 drop table if exists parsav_servers cascade;
28 34 create table parsav_servers (
29 - id bigint primary key default (1+random()*(2^63-1))::bigint,
35 + id bigint primary key default (1+random()*(2^63-1))::bigint,
30 36 domain text not null,
31 - key bytea
37 + key bytea,
38 + parsav boolean -- whether to use parsav protocol extensions
32 39 );
40 +
33 41 drop table if exists parsav_actors cascade;
34 42 create table parsav_actors (
35 - id bigint primary key default (1+random()*(2^63-1))::bigint,
36 - nym text,
37 - handle text not null, -- nym [@handle@origin]
38 - origin bigint references parsav_servers(id)
43 + id bigint primary key default (1+random()*(2^63-1))::bigint,
44 + nym text,
45 + handle text not null, -- nym [@handle@origin]
46 + origin bigint references parsav_servers(id)
39 47 on delete cascade, -- null origin = local actor
40 - bio text,
41 - rank smallint not null default 0,
42 - quota integer not null default 1000,
43 - key bytea, -- private if localactor; public if remote
48 + bio text,
49 + avataruri text, -- null if local
50 + rank smallint not null default 0,
51 + quota integer not null default 1000,
52 + key bytea, -- private if localactor; public if remote
53 + title text
44 54
45 55 unique (handle,origin)
46 56 );
47 57
48 58 drop table if exists parsav_rights cascade;
49 59 create table parsav_rights (
50 60 key text,
................................................................................
63 73 ('censor',true),
64 74 ('suspend',true),
65 75 ('rebrand',true)
66 76 ) as a;
67 77
68 78 drop table if exists parsav_posts cascade;
69 79 create table parsav_posts (
70 - id bigint primary key default (1+random()*(2^63-1))::bigint,
71 - author bigint references parsav_actors(id)
80 + id bigint primary key default (1+random()*(2^63-1))::bigint,
81 + author bigint references parsav_actors(id)
72 82 on delete cascade,
73 - subject text,
74 - body text,
75 - posted timestamp not null,
83 + subject text,
84 + acl text not null default 'all', -- just store the script raw 🤷
85 + body text,
86 + posted timestamp not null,
76 87 discovered timestamp not null,
77 - scope smallint not null,
78 - convo bigint, parent bigint,
79 - circles bigint[], mentions bigint[]
88 + scope smallint not null,
89 + convo bigint,
90 + parent bigint,
91 + circles bigint[],
92 + mentions bigint[]
80 93 );
81 94
82 95 drop table if exists parsav_conversations cascade;
83 96 create table parsav_conversations (
84 - id bigint primary key default (1+random()*(2^63-1))::bigint,
85 - uri text not null,
97 + id bigint primary key default (1+random()*(2^63-1))::bigint,
98 + uri text not null,
86 99 discovered timestamp not null,
87 - head bigint references parsav_posts(id)
100 + head bigint references parsav_posts(id)
88 101 );
89 102
90 103 drop table if exists parsav_rels cascade;
91 104 create table parsav_rels (
92 105 relator bigint references parsav_actors(id)
93 106 on delete cascade, -- e.g. follower
94 107 relatee bigint references parsav_actors(id)
95 - on delete cascade, -- e.g. follower
96 - kind smallint, -- e.g. follow, block, mute
108 + on delete cascade, -- e.g. followed
109 + kind smallint, -- e.g. follow, block, mute
97 110
98 111 primary key (relator, relatee, kind)
99 112 );
100 113
101 114 drop table if exists parsav_acts cascade;
102 115 create table parsav_acts (
103 - id bigint primary key default (1+random()*(2^63-1))::bigint,
104 - kind text not null, -- like, react, so on
105 - time timestamp not null,
106 - actor bigint references parsav_actors(id)
116 + id bigint primary key default (1+random()*(2^63-1))::bigint,
117 + kind text not null, -- like, react, so on
118 + time timestamp not null default now(),
119 + actor bigint references parsav_actors(id)
107 120 on delete cascade,
108 121 subject bigint -- may be post or act, depending on kind
109 122 );
110 123
111 124 drop table if exists parsav_log cascade;
112 125 create table parsav_log (
113 126 -- accesses are tracked for security & sending delete acts
114 - id bigint primary key default (1+random()*(2^63-1))::bigint,
115 - time timestamp not null,
127 + id bigint primary key default (1+random()*(2^63-1))::bigint,
128 + time timestamp not null default now(),
116 129 actor bigint references parsav_actors(id)
117 130 on delete cascade,
118 - post bigint not null
131 + post bigint not null
132 +);
133 +
134 +drop table if exists parsav_attach cascade;
135 +create table parsav_attach (
136 + id bigint primary key default (1+random()*(2^63-1))::bigint,
137 + birth timestamp not null default now(),
138 + content bytea not null,
139 + mime text, -- null if unknown, will be reported as x-octet-stream
140 + description text,
141 + parent bigint -- post id, or userid for avatars
142 +);
143 +
144 +drop table if exists parsav_circles cascade;
145 +create table parsav_circles (
146 + id bigint primary key default (1+random()*(2^63-1))::bigint,
147 + owner bigint not null references parsav_actors(id),
148 + name text not null,
149 + members bigint[] not null default array[],
150 +
151 + unique (owner,name)
152 +);
153 +
154 +drop table if exists parsav_rooms cascade;
155 +create table parsav_rooms (
156 + id bigint primary key default (1+random()*(2^63-1))::bigint,
157 + origin bigint references parsav_servers(id),
158 + name text not null,
159 + description text not null,
160 + policy smallint not null
161 +);
162 +
163 +drop table if exists parsav_room_members cascade;
164 +create table parsav_room_members (
165 + room bigint references parsav_rooms(id),
166 + member bigint references parsav_actors(id),
167 + rank smallint not null default 0,
168 + admin boolean not null default false, -- non-admins with rank can only moderate + invite
169 + title text -- admin-granted title like reddit flair
170 +);
171 +
172 +drop table if exists parsav_invites cascade;
173 +create table parsav_invites (
174 + id bigint primary key default (1+random()*(2^63-1))::bigint,
175 + -- when a user is created from an invite, the invite is deleted and the invite
176 + -- ID becomes the user ID. privileges granted on the invite ID during the invite
177 + -- process are thus inherited by the user
178 + handle text, -- admin can lock invite to specific handle
179 + rank smallint not null default 0,
180 + quota integer not null default 1000
181 +};
182 +
183 +drop table if exists parsav_interventions cascade;
184 +create table parsav_interventions (
185 + id bigint primary key default (1+random()*(2^63-1))::bigint,
186 + issuer bigint references parsav_actors(id) not null,
187 + scope bigint, -- can be null or room for local actions
188 + nature smallint not null, -- silence, suspend, disemvowel, etc
189 + victim bigint not null, -- could potentially target group as well
190 + expire timestamp -- auto-expires if set
119 191 );
192 +
120 193 end;