26
27
28
29
30
31
32
33
34
35
36
37
38
39
..
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
..
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
|
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
................................................................................
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,
parent bigint not null default 0,
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
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
);
................................................................................
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%
|
>
|
>
|
|
>
>
>
|
>
>
>
>
>
>
>
<
>
>
>
>
|
|
|
|
|
|
|
|
|
|
>
>
>
|
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
..
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
..
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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
|
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,
avatarid bigint, -- artifact id, null if remote
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
................................................................................
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,
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
);
................................................................................
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
);
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
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) 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
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 a temporary managed auth table; we can delete this later
-- if it ends up being replaced with a view
%include pgsql-auth.sql%
|