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
..
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
..
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
...
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
...
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
|
-- ('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 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
knownsince bigint not null,
bio text,
avatarid bigint, -- artifact id, null if remote
................................................................................
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,
posted bigint not null,
discovered bigint not null,
chgcount integer not null default 0,
................................................................................
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_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[],
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
);
comment on table parsav_rooms is
'an index of user-created chatrooms';
................................................................................
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,
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
expire bigint, -- auto-expires if set
review bigint, -- brings up for review at given time if set
reason text, -- visible to victim if set
|
|
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
..
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
..
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
...
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
...
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
|
-- ('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 <def:uniq>,
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 <def:uniq>,
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
................................................................................
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 <def:uniq>,
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,
................................................................................
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 <def:uniq>,
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 <def:uniq>,
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 <def:uniq>,
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_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 <def:uniq>,
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 <def:uniq>,
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';
................................................................................
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 <def:uniq>,
-- 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
);
comment on table parsav_invites is
'all active invitations and the level of authority they grant if accepted';
create table parsav_sanctions (
id <def:uniq>,
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
|