Differences From
Artifact [347a4ab533]:
12 12 -- ('policy-self-register',:'regpol'),
13 13 -- ('master',:'admin'),
14 14
15 15 -- note that valid ids should always > 0, as 0 is reserved for null
16 16 -- on the client side, vastly simplifying code
17 17 create table parsav_servers (
18 18 id bigint primary key default (1+random()*(2^63-1))::bigint,
19 - domain text not null,
19 + domain text not null unique,
20 20 key bytea,
21 - knownsince timestamp,
21 + knownsince bigint,
22 22 parsav boolean -- whether to use parsav protocol extensions
23 23 );
24 24
25 25 create table parsav_actors (
26 26 id bigint primary key default (1+random()*(2^63-1))::bigint,
27 27 nym text,
28 28 handle text not null, -- nym [@handle@origin]
29 29 origin bigint references parsav_servers(id)
30 30 on delete cascade, -- null origin = local actor
31 - knownsince timestamp not null default now(),
31 + knownsince bigint not null,
32 32 bio text,
33 33 avatarid bigint, -- artifact id, null if remote
34 34 avataruri text, -- null if local
35 35 rank smallint not null default 0,
36 36 quota integer not null default 1000,
37 37 invites integer not null default 0,
38 38 key bytea, -- private if localactor; public if remote
39 39 epithet text,
40 - authtime timestamp not null default now(), -- cookies earlier than this timepoint will not be accepted
40 + authtime bigint not null, -- cookies earlier than this timepoint will not be accepted
41 41
42 42 unique (handle,origin)
43 43 );
44 44
45 45 create table parsav_rights (
46 46 key text,
47 47 actor bigint references parsav_actors(id)
48 48 on delete cascade,
49 49 allow boolean not null,
50 50 scope bigint, -- for future expansion
51 51
52 52 primary key (key,actor)
53 53 );
54 +create index on parsav_rights (actor);
54 55
55 56 create table parsav_posts (
56 57 id bigint primary key default (1+random()*(2^63-1))::bigint,
57 - author bigint references parsav_actors(id)
58 - on delete cascade,
58 + author bigint references parsav_actors(id) on delete cascade,
59 59 subject text,
60 60 acl text not null default 'all', -- just store the script raw 🤷
61 61 body text,
62 - posted timestamp not null,
63 - discovered timestamp not null,
62 + posted bigint not null,
63 + discovered bigint not null,
64 64 chgcount integer not null default 0,
65 - edited timestamp,
65 + edited bigint,
66 66 parent bigint not null default 0, -- if post: part of conversation; if chatroom: top-level post
67 67 circles bigint[], -- TODO at edit or creation, iterate through each circle
68 68 mentions bigint[], -- a user has, check if it can see her post, and if so add
69 69 artifacts bigint[],
70 70
71 71 convoheaduri text
72 72 -- only used for tracking foreign conversations and tying them to post heads;
73 73 -- local conversations are tracked directly and mapped to URIs based on the
74 74 -- head's ID. null if native tweet or not the first tweet in convo
75 75 );
76 +create index on parsav_posts (author);
77 +create index on parsav_posts (parent);
76 78
77 79 create table parsav_rels (
78 80 relator bigint references parsav_actors(id)
79 81 on delete cascade, -- e.g. follower
80 82 relatee bigint references parsav_actors(id)
81 83 on delete cascade, -- e.g. followed
82 84 kind smallint, -- e.g. follow, block, mute
................................................................................
83 85
84 86 primary key (relator, relatee, kind)
85 87 );
86 88
87 89 create table parsav_acts (
88 90 id bigint primary key default (1+random()*(2^63-1))::bigint,
89 91 kind text not null, -- like, rt, react, so on
90 - time timestamp not null default now(),
91 - actor bigint references parsav_actors(id)
92 - on delete cascade,
92 + time bigint not null,
93 + actor bigint references parsav_actors(id) on delete cascade,
93 94 subject bigint, -- may be post or act, depending on kind
94 95 body text -- emoji, if react
95 96 );
97 +create index on parsav_acts (subject);
98 +create index on parsav_acts (actor);
99 +create index on parsav_acts (time);
96 100
97 101 create table parsav_log (
98 102 -- accesses are tracked for security & sending delete acts
99 103 id bigint primary key default (1+random()*(2^63-1))::bigint,
100 - time timestamp not null default now(),
104 + time bigint not null,
101 105 actor bigint references parsav_actors(id)
102 106 on delete cascade,
103 107 post bigint not null
104 108 );
105 109
106 110 create table parsav_artifacts (
107 111 id bigint primary key default (1+random()*(2^63-1))::bigint,
108 - birth timestamp not null default now(),
112 + birth bigint not null,
109 113 content bytea, -- if null, this is a "ban record" preventing content matching the hash from being re-uploaded
110 114 hash bytea unique not null, -- sha256 hash of content
111 115 -- it would be cool to use a computed column for this, but i don't want
112 116 -- to lock people into PG12 or drag in the pgcrypto extension just for this
113 117 mime text -- null if unknown, will be reported as x-octet-stream
114 118 );
115 119 create index on parsav_artifacts (mime);
116 120
117 121 create table parsav_artifact_claims (
118 - birth timestamp not null default now(),
122 + birth bigint not null,
119 123 uid bigint references parsav_actors(id) on delete cascade,
120 124 rid bigint references parsav_artifacts(id) on delete cascade,
121 125 description text,
122 126 folder text,
123 127
124 128 unique (uid,rid)
125 129 );
126 130 create index on parsav_artifact_claims (uid);
131 +create index on parsav_artifact_claims (uid,folder);
127 132
128 133 create table parsav_circles (
129 134 id bigint primary key default (1+random()*(2^63-1))::bigint,
130 135 owner bigint not null references parsav_actors(id) on delete cascade,
131 136 name text not null,
132 137 members bigint[] not null default array[]::bigint[],
133 138
134 139 unique (owner,name)
135 140 );
141 +create index on parsav_circles (owner);
136 142
137 143 create table parsav_rooms (
138 144 id bigint primary key default (1+random()*(2^63-1))::bigint,
139 145 origin bigint references parsav_servers(id) on delete cascade,
140 146 name text not null,
141 147 description text not null,
142 148 policy smallint not null
................................................................................
146 152 room bigint not null references parsav_rooms(id) on delete cascade,
147 153 member bigint not null references parsav_actors(id) on delete cascade,
148 154 rank smallint not null default 0,
149 155 admin boolean not null default false, -- non-admins with rank can only moderate + invite
150 156 title text, -- admin-granted title like reddit flair
151 157 vouchedby bigint references parsav_actors(id) on delete set null
152 158 );
159 +create index on parsav_room_members (member);
160 +create index on parsav_room_members (room);
153 161
154 162 create table parsav_invites (
155 163 id bigint primary key default (1+random()*(2^63-1))::bigint,
156 164 -- when a user is created from an invite, the invite is deleted and the invite
157 165 -- ID becomes the user ID. privileges granted on the invite ID during the invite
158 166 -- process are thus inherited by the user
159 167 issuer bigint references parsav_actors(id) on delete set null,
................................................................................
164 172
165 173 create table parsav_sanctions (
166 174 id bigint primary key default (1+random()*(2^63-1))::bigint,
167 175 issuer bigint references parsav_actors(id) on delete set null,
168 176 scope bigint, -- can be null or room for local actions
169 177 nature smallint not null, -- silence, suspend, disemvowel, censor, noreply, etc
170 178 victim bigint not null, -- can be user, room, or post
171 - expire timestamp, -- auto-expires if set
172 - review timestamp, -- brings up for review at given time if set
179 + expire bigint, -- auto-expires if set
180 + review bigint, -- brings up for review at given time if set
173 181 reason text, -- visible to victim if set
174 - context text -- admin-only note
182 + context text, -- admin-only note
183 + appeal text -- null if no appeal lodged
175 184 );
185 +create index on parsav_sanctions (victim,scope);
186 +create index on parsav_sanctions (issuer);
176 187
177 188 create table parsav_actor_conf_strs (
178 189 uid bigint not null references parsav_actors(id) on delete cascade,
179 190 key text not null, value text not null, unique (uid,key)
180 191 );
181 192 create table parsav_actor_conf_ints (
182 193 uid bigint not null references parsav_actors(id) on delete cascade,
183 194 key text not null, value bigint not null, unique (uid,key)
184 195 );
185 196
186 197 -- create a temporary managed auth table; we can delete this later
187 198 -- if it ends up being replaced with a view
188 199 %include pgsql-auth.sql%