Differences From
Artifact [a3359b8b76]:
31 31 -- note that valid ids should always > 0, as 0 is reserved for null
32 32 -- on the client side, vastly simplifying code
33 33 drop table if exists parsav_servers cascade;
34 34 create table parsav_servers (
35 35 id bigint primary key default (1+random()*(2^63-1))::bigint,
36 36 domain text not null,
37 37 key bytea,
38 + knownsince timestamp,
38 39 parsav boolean -- whether to use parsav protocol extensions
39 40 );
40 41
41 42 drop table if exists parsav_actors cascade;
42 43 create table parsav_actors (
43 44 id bigint primary key default (1+random()*(2^63-1))::bigint,
44 45 nym text,
45 46 handle text not null, -- nym [@handle@origin]
46 47 origin bigint references parsav_servers(id)
47 48 on delete cascade, -- null origin = local actor
49 + knownsince timestamp,
48 50 bio text,
49 51 avataruri text, -- null if local
50 52 rank smallint not null default 0,
51 53 quota integer not null default 1000,
52 54 key bytea, -- private if localactor; public if remote
53 - title text
55 + title text,
54 56
55 57 unique (handle,origin)
56 58 );
57 59
58 60 drop table if exists parsav_rights cascade;
59 61 create table parsav_rights (
60 62 key text,
................................................................................
81 83 author bigint references parsav_actors(id)
82 84 on delete cascade,
83 85 subject text,
84 86 acl text not null default 'all', -- just store the script raw 🤷
85 87 body text,
86 88 posted timestamp not null,
87 89 discovered timestamp not null,
88 - scope smallint not null,
89 - convo bigint,
90 - parent bigint,
91 - circles bigint[],
92 - mentions bigint[]
90 + parent bigint not null default 0,
91 + circles bigint[], -- TODO at edit or creation, iterate through each circle
92 + mentions bigint[], -- a user has, check if it can see her post, and if so add
93 +
94 + convoheaduri text
95 + -- only used for tracking foreign conversations and tying them to post heads;
96 + -- local conversations are tracked directly and mapped to URIs based on the
97 + -- head's ID. null if native tweet or not the first tweet in convo
93 98 );
94 99
95 100 drop table if exists parsav_conversations cascade;
96 -create table parsav_conversations (
97 - id bigint primary key default (1+random()*(2^63-1))::bigint,
98 - uri text not null,
99 - discovered timestamp not null,
100 - head bigint references parsav_posts(id)
101 -);
102 101
103 102 drop table if exists parsav_rels cascade;
104 103 create table parsav_rels (
105 104 relator bigint references parsav_actors(id)
106 105 on delete cascade, -- e.g. follower
107 106 relatee bigint references parsav_actors(id)
108 107 on delete cascade, -- e.g. followed
................................................................................
142 141 );
143 142
144 143 drop table if exists parsav_circles cascade;
145 144 create table parsav_circles (
146 145 id bigint primary key default (1+random()*(2^63-1))::bigint,
147 146 owner bigint not null references parsav_actors(id),
148 147 name text not null,
149 - members bigint[] not null default array[],
148 + members bigint[] not null default array[]::bigint[],
150 149
151 150 unique (owner,name)
152 151 );
153 152
154 153 drop table if exists parsav_rooms cascade;
155 154 create table parsav_rooms (
156 155 id bigint primary key default (1+random()*(2^63-1))::bigint,
................................................................................
162 161
163 162 drop table if exists parsav_room_members cascade;
164 163 create table parsav_room_members (
165 164 room bigint references parsav_rooms(id),
166 165 member bigint references parsav_actors(id),
167 166 rank smallint not null default 0,
168 167 admin boolean not null default false, -- non-admins with rank can only moderate + invite
169 - title text -- admin-granted title like reddit flair
168 + title text, -- admin-granted title like reddit flair
169 + vouchedby bigint references parsav_actors(id)
170 170 );
171 171
172 172 drop table if exists parsav_invites cascade;
173 173 create table parsav_invites (
174 174 id bigint primary key default (1+random()*(2^63-1))::bigint,
175 175 -- when a user is created from an invite, the invite is deleted and the invite
176 176 -- ID becomes the user ID. privileges granted on the invite ID during the invite
177 177 -- process are thus inherited by the user
178 + issuer bigint references parsav_actors(id),
178 179 handle text, -- admin can lock invite to specific handle
179 180 rank smallint not null default 0,
180 181 quota integer not null default 1000
181 -};
182 +);
182 183
183 184 drop table if exists parsav_interventions cascade;
184 185 create table parsav_interventions (
185 186 id bigint primary key default (1+random()*(2^63-1))::bigint,
186 187 issuer bigint references parsav_actors(id) not null,
187 188 scope bigint, -- can be null or room for local actions
188 189 nature smallint not null, -- silence, suspend, disemvowel, etc
189 190 victim bigint not null, -- could potentially target group as well
190 191 expire timestamp -- auto-expires if set
191 192 );
192 193
193 194 end;