Differences From
Artifact [0ef43163b5]:
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 31 knownsince timestamp,
32 32 bio text,
33 + avatarid bigint, -- artifact id, null if remote
33 34 avataruri text, -- null if local
34 35 rank smallint not null default 0,
35 36 quota integer not null default 1000,
36 37 key bytea, -- private if localactor; public if remote
37 38 epithet text,
38 39 authtime timestamp not null default now(), -- cookies earlier than this timepoint will not be accepted
39 40
................................................................................
55 56 author bigint references parsav_actors(id)
56 57 on delete cascade,
57 58 subject text,
58 59 acl text not null default 'all', -- just store the script raw 🤷
59 60 body text,
60 61 posted timestamp not null,
61 62 discovered timestamp not null,
62 - parent bigint not null default 0,
63 + parent bigint not null default 0, -- if post: part of conversation; if chatroom: top-level post
63 64 circles bigint[], -- TODO at edit or creation, iterate through each circle
64 65 mentions bigint[], -- a user has, check if it can see her post, and if so add
66 + artifacts bigint[],
65 67
66 68 convoheaduri text
67 69 -- only used for tracking foreign conversations and tying them to post heads;
68 70 -- local conversations are tracked directly and mapped to URIs based on the
69 71 -- head's ID. null if native tweet or not the first tweet in convo
70 72 );
71 73
................................................................................
93 95 id bigint primary key default (1+random()*(2^63-1))::bigint,
94 96 time timestamp not null default now(),
95 97 actor bigint references parsav_actors(id)
96 98 on delete cascade,
97 99 post bigint not null
98 100 );
99 101
100 -create table parsav_attach (
102 +create table parsav_artifacts (
101 103 id bigint primary key default (1+random()*(2^63-1))::bigint,
102 104 birth timestamp not null default now(),
103 - content bytea not null,
104 - mime text, -- null if unknown, will be reported as x-octet-stream
105 + content bytea, -- if null, this is a "ban record" preventing content matching the hash from being re-uploaded
106 + hash bytea unique not null, -- sha256 hash of content
107 + -- it would be cool to use a computed column for this, but i don't want
108 + -- to lock people into PG12 or drag in the pgcrypto extension just for this
109 + mime text -- null if unknown, will be reported as x-octet-stream
110 +);
111 +create index on parsav_artifacts (mime);
112 +
113 +create table parsav_artifact_claims (
114 + birth timestamp not null default now(),
115 + uid bigint references parsav_actors(id) on delete cascade,
116 + rid bigint references parsav_artifacts(id) on delete cascade,
105 117 description text,
106 - parent bigint -- post id, or userid for avatars
118 + folder text,
119 +
120 + unique (uid,rid)
107 121 );
122 +create index on parsav_artifact_claims (uid);
108 123
109 124 create table parsav_circles (
110 125 id bigint primary key default (1+random()*(2^63-1))::bigint,
111 - owner bigint not null references parsav_actors(id),
126 + owner bigint not null references parsav_actors(id) on delete cascade,
112 127 name text not null,
113 128 members bigint[] not null default array[]::bigint[],
114 129
115 130 unique (owner,name)
116 131 );
117 132
118 133 create table parsav_rooms (
119 134 id bigint primary key default (1+random()*(2^63-1))::bigint,
120 - origin bigint references parsav_servers(id),
135 + origin bigint references parsav_servers(id) on delete cascade,
121 136 name text not null,
122 137 description text not null,
123 138 policy smallint not null
124 139 );
125 140
126 141 create table parsav_room_members (
127 - room bigint references parsav_rooms(id),
128 - member bigint references parsav_actors(id),
142 + room bigint not null references parsav_rooms(id) on delete cascade,
143 + member bigint not null references parsav_actors(id) on delete cascade,
129 144 rank smallint not null default 0,
130 145 admin boolean not null default false, -- non-admins with rank can only moderate + invite
131 146 title text, -- admin-granted title like reddit flair
132 147 vouchedby bigint references parsav_actors(id)
133 148 );
134 149
135 150 create table parsav_invites (
136 151 id bigint primary key default (1+random()*(2^63-1))::bigint,
137 152 -- when a user is created from an invite, the invite is deleted and the invite
138 153 -- ID becomes the user ID. privileges granted on the invite ID during the invite
139 154 -- process are thus inherited by the user
140 - issuer bigint references parsav_actors(id),
155 + issuer bigint references parsav_actors(id) on delete set null,
141 156 handle text, -- admin can lock invite to specific handle
142 157 rank smallint not null default 0,
143 158 quota integer not null default 1000
144 159 );
145 160
146 -create table parsav_interventions (
161 +create table parsav_sanctions (
147 162 id bigint primary key default (1+random()*(2^63-1))::bigint,
148 - issuer bigint references parsav_actors(id) not null,
163 + issuer bigint references parsav_actors(id) on delete set null,
149 164 scope bigint, -- can be null or room for local actions
150 - nature smallint not null, -- silence, suspend, disemvowel, etc
151 - victim bigint not null, -- could potentially target group as well
152 - expire timestamp -- auto-expires if set
165 + nature smallint not null, -- silence, suspend, disemvowel, censor, noreply, etc
166 + victim bigint not null, -- can be user, room, or post
167 + expire timestamp, -- auto-expires if set
168 + review timestamp, -- brings up for review at given time if set
169 + reason text, -- visible to victim if set
170 + context text -- admin-only note
153 171 );
154 172
155 173 -- create a temporary managed auth table; we can delete this later
156 174 -- if it ends up being replaced with a view
157 175 %include pgsql-auth.sql%