Differences From
Artifact [29d1b18fbc]:
1 1 create table parsav_config (
2 2 key text primary key,
3 3 value text
4 4 );
5 +comment on table parsav_config is
6 +'server-wide configuration variables. highly sensitive!';
5 7
6 8 insert into parsav_config (key,value) values ('schema-version','1'),
7 9 ('credential-store','managed');
8 10 -- ('bind',:'bind'),
9 11 -- ('domain',:'domain'),
10 12 -- ('instance-name',:'inst'),
11 13 -- ('policy-security',:'secmode'),
................................................................................
17 19 create table parsav_servers (
18 20 id bigint primary key default (1+random()*(2^63-1))::bigint,
19 21 domain text not null unique,
20 22 key bytea,
21 23 knownsince bigint,
22 24 parsav boolean -- whether to use parsav protocol extensions
23 25 );
26 +comment on table parsav_servers is
27 +'all servers known to the parsav instance. the local server (including its private key) is stored in row (id = 0)';
24 28
25 29 create table parsav_actors (
26 30 id bigint primary key default (1+random()*(2^63-1))::bigint,
27 31 nym text,
28 32 handle text not null, -- nym [@handle@origin]
29 33 origin bigint references parsav_servers(id)
30 34 on delete cascade, -- null origin = local actor
................................................................................
37 41 invites integer not null default 0,
38 42 key bytea, -- private if localactor; public if remote
39 43 epithet text,
40 44 authtime bigint not null, -- cookies earlier than this timepoint will not be accepted
41 45
42 46 unique (handle,origin)
43 47 );
48 +comment on table parsav_actors is
49 +'all users known to the instance across the fediverse; local users satisfy constraint (origin = 0)';
44 50
45 51 create table parsav_rights (
46 52 key text,
47 53 actor bigint references parsav_actors(id)
48 54 on delete cascade,
49 55 allow boolean not null,
50 56 scope bigint, -- for future expansion
51 57
52 58 primary key (key,actor)
53 59 );
54 60 create index on parsav_rights (actor);
61 +comment on table parsav_rights is
62 +'a backward-compatible list of every non-default privilege or deprivilege granted to a local user';
55 63
56 64 create table parsav_posts (
57 65 id bigint primary key default (1+random()*(2^63-1))::bigint,
58 66 author bigint references parsav_actors(id) on delete cascade,
59 67 subject text,
60 68 acl text not null default 'all', -- just store the script raw 🤷
61 69 body text,
................................................................................
81 89 on delete cascade, -- e.g. follower
82 90 relatee bigint references parsav_actors(id)
83 91 on delete cascade, -- e.g. followed
84 92 kind smallint, -- e.g. follow, block, mute
85 93
86 94 primary key (relator, relatee, kind)
87 95 );
96 +comment on table parsav_rels is
97 +'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';
88 98
89 99 create table parsav_acts (
90 100 id bigint primary key default (1+random()*(2^63-1))::bigint,
91 101 kind text not null, -- like, rt, react, so on
92 102 time bigint not null,
93 103 actor bigint references parsav_actors(id) on delete cascade,
94 104 subject bigint, -- may be post or act, depending on kind
95 - body text -- emoji, if react
105 + body text -- emoji, if react; complaint, if report
96 106 );
97 107 create index on parsav_acts (subject);
98 108 create index on parsav_acts (actor);
99 109 create index on parsav_acts (time);
110 +comment on table parsav_acts is
111 +'every simple action taken on a tweet by an actor, including likes, rts, reacts, and reports';
100 112
101 113 create table parsav_log (
102 114 -- accesses are tracked for security & sending delete acts
103 115 id bigint primary key default (1+random()*(2^63-1))::bigint,
104 116 time bigint not null,
105 117 actor bigint references parsav_actors(id)
106 118 on delete cascade,
107 119 post bigint not null
108 120 );
121 +comment on table parsav_log is
122 +'a log of accesses from foreign servers, tracking which will be sent update & delete events for each post';
109 123
110 124 create table parsav_artifacts (
111 125 id bigint primary key default (1+random()*(2^63-1))::bigint,
112 126 birth bigint not null,
113 127 content bytea, -- if null, this is a "ban record" preventing content matching the hash from being re-uploaded
114 128 hash bytea unique not null, -- sha256 hash of content
115 129 -- it would be cool to use a computed column for this, but i don't want
116 130 -- to lock people into PG12 or drag in the pgcrypto extension just for this
117 - mime text -- null if unknown, will be reported as x-octet-stream
131 + mime text -- null if unknown, will be reported as octet-stream
118 132 );
119 133 create index on parsav_artifacts (mime);
134 +comment on table parsav_artifacts is
135 +'deduplicated media files uploaded by users';
120 136
121 137 create table parsav_artifact_claims (
122 138 birth bigint not null,
123 139 uid bigint references parsav_actors(id) on delete cascade,
124 140 rid bigint references parsav_artifacts(id) on delete cascade,
125 141 description text,
126 142 folder text,
127 143
128 144 unique (uid,rid)
129 145 );
130 146 create index on parsav_artifact_claims (uid);
131 147 create index on parsav_artifact_claims (uid,folder);
148 +comment on table parsav_artifact_claims is
149 +'a list of users who have an ownership interest in each artifact (effectively an index of GC roots)';
132 150
133 151 create table parsav_circles (
134 152 id bigint primary key default (1+random()*(2^63-1))::bigint,
135 153 owner bigint not null references parsav_actors(id) on delete cascade,
136 154 name text not null,
137 155 members bigint[] not null default array[]::bigint[],
138 156
................................................................................
143 161 create table parsav_rooms (
144 162 id bigint primary key default (1+random()*(2^63-1))::bigint,
145 163 origin bigint references parsav_servers(id) on delete cascade,
146 164 name text not null,
147 165 description text not null,
148 166 policy smallint not null
149 167 );
168 +comment on table parsav_rooms is
169 +'an index of user-created chatrooms';
150 170
151 171 create table parsav_room_members (
152 172 room bigint not null references parsav_rooms(id) on delete cascade,
153 173 member bigint not null references parsav_actors(id) on delete cascade,
154 174 rank smallint not null default 0,
155 175 admin boolean not null default false, -- non-admins with rank can only moderate + invite
156 176 title text, -- admin-granted title like reddit flair
................................................................................
165 185 -- ID becomes the user ID. privileges granted on the invite ID during the invite
166 186 -- process are thus inherited by the user
167 187 issuer bigint references parsav_actors(id) on delete set null,
168 188 handle text, -- admin can lock invite to specific handle
169 189 rank smallint not null default 0,
170 190 quota integer not null default 1000
171 191 );
192 +comment on table parsav_invites is
193 +'all active invitations and the level of authority they grant if accepted';
172 194
173 195 create table parsav_sanctions (
174 196 id bigint primary key default (1+random()*(2^63-1))::bigint,
175 197 issuer bigint references parsav_actors(id) on delete set null,
176 198 scope bigint, -- can be null or room for local actions
177 199 nature smallint not null, -- silence, suspend, disemvowel, censor, noreply, etc
178 200 victim bigint not null, -- can be user, room, or post
................................................................................
180 202 review bigint, -- brings up for review at given time if set
181 203 reason text, -- visible to victim if set
182 204 context text, -- admin-only note
183 205 appeal text -- null if no appeal lodged
184 206 );
185 207 create index on parsav_sanctions (victim,scope);
186 208 create index on parsav_sanctions (issuer);
209 +comment on table parsav_sanctions is
210 +'administrative actions taken against particular users, posts, rooms, or other entities';
187 211
188 212 create table parsav_actor_conf_strs (
189 213 uid bigint not null references parsav_actors(id) on delete cascade,
190 214 key text not null, value text not null, unique (uid,key)
191 215 );
192 216 create table parsav_actor_conf_ints (
193 217 uid bigint not null references parsav_actors(id) on delete cascade,
194 218 key text not null, value bigint not null, unique (uid,key)
195 219 );
220 +comment on table parsav_actor_conf_strs is 'per-user configuration settings (string properties)';
221 +comment on table parsav_actor_conf_ints is 'per-user configuration settings (integer and enumeration properties)';
196 222
197 223 -- create a temporary managed auth table; we can delete this later
198 224 -- if it ends up being replaced with a view
199 225 %include pgsql-auth.sql%