Differences From
Artifact [daac991db5]:
13 13 -- ('policy-security',:'secmode'),
14 14 -- ('policy-self-register',:'regpol'),
15 15 -- ('master',:'admin'),
16 16
17 17 -- note that valid ids should always > 0, as 0 is reserved for null
18 18 -- on the client side, vastly simplifying code
19 19 create table parsav_servers (
20 - id bigint primary key default (1+random()*(2^63-1))::bigint,
20 + id <def:uniq>,
21 21 domain text not null unique,
22 22 key bytea,
23 23 knownsince bigint,
24 24 parsav boolean -- whether to use parsav protocol extensions
25 25 );
26 26 comment on table parsav_servers is
27 27 'all servers known to the parsav instance. the local server (including its private key) is stored in row (id = 0)';
28 28
29 29 create table parsav_actors (
30 - id bigint primary key default (1+random()*(2^63-1))::bigint,
30 + id <def:uniq>,
31 31 nym text,
32 32 handle text not null, -- nym [@handle@origin]
33 33 origin bigint references parsav_servers(id)
34 34 on delete cascade, -- null origin = local actor
35 35 knownsince bigint not null,
36 36 bio text,
37 37 avatarid bigint, -- artifact id, null if remote
................................................................................
58 58 primary key (key,actor)
59 59 );
60 60 create index on parsav_rights (actor);
61 61 comment on table parsav_rights is
62 62 'a backward-compatible list of every non-default privilege or deprivilege granted to a local user';
63 63
64 64 create table parsav_posts (
65 - id bigint primary key default (1+random()*(2^63-1))::bigint,
65 + id <def:uniq>,
66 66 author bigint references parsav_actors(id) on delete cascade,
67 67 subject text,
68 68 acl text not null default 'all', -- just store the script raw 🤷
69 69 body text,
70 70 posted bigint not null,
71 71 discovered bigint not null,
72 72 chgcount integer not null default 0,
................................................................................
94 94
95 95 primary key (relator, relatee, kind)
96 96 );
97 97 comment on table parsav_rels is
98 98 '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';
99 99
100 100 create table parsav_acts (
101 - id bigint primary key default (1+random()*(2^63-1))::bigint,
101 + id <def:uniq>,
102 102 kind text not null, -- like, rt, react, so on
103 103 time bigint not null,
104 104 actor bigint references parsav_actors(id) on delete cascade,
105 105 subject bigint, -- may be post or act, depending on kind
106 106 body text -- emoji, if react; complaint, if report
107 107 );
108 108 create index on parsav_acts (subject);
................................................................................
109 109 create index on parsav_acts (actor);
110 110 create index on parsav_acts (time);
111 111 comment on table parsav_acts is
112 112 'every simple action taken on a tweet by an actor, including likes, rts, reacts, and reports';
113 113
114 114 create table parsav_log (
115 115 -- accesses are tracked for security & sending delete acts
116 - id bigint primary key default (1+random()*(2^63-1))::bigint,
116 + id <def:uniq>,
117 117 time bigint not null,
118 118 actor bigint references parsav_actors(id)
119 119 on delete cascade,
120 120 post bigint not null
121 121 );
122 122 comment on table parsav_log is
123 123 'a log of accesses from foreign servers, tracking which will be sent update & delete events for each post';
124 124
125 125 create table parsav_artifacts (
126 - id bigint primary key default (1+random()*(2^63-1))::bigint,
126 + id <def:uniq>,
127 127 birth bigint not null,
128 128 content bytea, -- if null, this is a "ban record" preventing content matching the hash from being re-uploaded
129 129 hash bytea unique not null, -- sha256 hash of content
130 130 -- it would be cool to use a computed column for this, but i don't want
131 131 -- to lock people into PG12 or drag in the pgcrypto extension just for this
132 132 mime text -- null if unknown, will be reported as octet-stream
133 133 );
................................................................................
146 146 );
147 147 create index on parsav_artifact_claims (uid);
148 148 create index on parsav_artifact_claims (uid,folder);
149 149 comment on table parsav_artifact_claims is
150 150 'a list of users who have an ownership interest in each artifact (effectively an index of GC roots)';
151 151
152 152 create table parsav_circles (
153 - id bigint primary key default (1+random()*(2^63-1))::bigint,
153 + id <def:uniq>,
154 154 owner bigint not null references parsav_actors(id) on delete cascade,
155 155 name text not null,
156 156 members bigint[] not null default array[]::bigint[],
157 157
158 158 unique (owner,name)
159 159 );
160 160 create index on parsav_circles (owner);
161 161
162 162 create table parsav_rooms (
163 - id bigint primary key default (1+random()*(2^63-1))::bigint,
163 + id <def:uniq>,
164 164 origin bigint references parsav_servers(id) on delete cascade,
165 165 name text not null,
166 166 description text not null,
167 167 policy smallint not null
168 168 );
169 169 comment on table parsav_rooms is
170 170 'an index of user-created chatrooms';
................................................................................
177 177 title text, -- admin-granted title like reddit flair
178 178 vouchedby bigint references parsav_actors(id) on delete set null
179 179 );
180 180 create index on parsav_room_members (member);
181 181 create index on parsav_room_members (room);
182 182
183 183 create table parsav_invites (
184 - id bigint primary key default (1+random()*(2^63-1))::bigint,
184 + id <def:uniq>,
185 185 -- when a user is created from an invite, the invite is deleted and the invite
186 186 -- ID becomes the user ID. privileges granted on the invite ID during the invite
187 187 -- process are thus inherited by the user
188 188 issuer bigint references parsav_actors(id) on delete set null,
189 189 handle text, -- admin can lock invite to specific handle
190 190 rank smallint not null default 0,
191 191 quota integer not null default 1000
192 192 );
193 193 comment on table parsav_invites is
194 194 'all active invitations and the level of authority they grant if accepted';
195 195
196 196 create table parsav_sanctions (
197 - id bigint primary key default (1+random()*(2^63-1))::bigint,
197 + id <def:uniq>,
198 198 issuer bigint references parsav_actors(id) on delete set null,
199 199 scope bigint, -- can be null or room for local actions
200 200 nature smallint not null, -- silence, suspend, disemvowel, censor, noreply, etc
201 201 victim bigint not null, -- can be user, room, or post
202 202 expire bigint, -- auto-expires if set
203 203 review bigint, -- brings up for review at given time if set
204 204 reason text, -- visible to victim if set