create table parsav_config (
key text primary key,
value text
);
insert into parsav_config (key,value) values ('schema-version','1'),
('credential-store','managed');
-- ('bind',:'bind'),
-- ('domain',:'domain'),
-- ('instance-name',:'inst'),
-- ('policy-security',:'secmode'),
-- ('policy-self-register',:'regpol'),
-- ('master',:'admin'),
-- note that valid ids should always > 0, as 0 is reserved for null
-- on the client side, vastly simplifying code
create table parsav_servers (
id bigint primary key default (1+random()*(2^63-1))::bigint,
domain text not null unique,
key bytea,
knownsince bigint,
parsav boolean -- whether to use parsav protocol extensions
);
create table parsav_actors (
id bigint primary key default (1+random()*(2^63-1))::bigint,
nym text,
handle text not null, -- nym [@handle@origin]
origin bigint references parsav_servers(id)
on delete cascade, -- null origin = local actor
knownsince bigint not null,
bio text,
avatarid bigint, -- artifact id, null if remote
avataruri text, -- null if local
rank smallint not null default 0,
quota integer not null default 1000,
invites integer not null default 0,
key bytea, -- private if localactor; public if remote
epithet text,
authtime bigint not null, -- cookies earlier than this timepoint will not be accepted
unique (handle,origin)
);
create table parsav_rights (
key text,
actor bigint references parsav_actors(id)
on delete cascade,
allow boolean not null,
scope bigint, -- for future expansion
primary key (key,actor)
);
create index on parsav_rights (actor);
create table parsav_posts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
author bigint references parsav_actors(id) on delete cascade,
subject text,
acl text not null default 'all', -- just store the script raw 🤷
body text,
posted bigint not null,
discovered bigint not null,
chgcount integer not null default 0,
edited bigint,
parent bigint not null default 0, -- if post: part of conversation; if chatroom: top-level post
circles bigint[], -- TODO at edit or creation, iterate through each circle
mentions bigint[], -- a user has, check if it can see her post, and if so add
artifacts bigint[],
convoheaduri text
-- only used for tracking foreign conversations and tying them to post heads;
-- local conversations are tracked directly and mapped to URIs based on the
-- head's ID. null if native tweet or not the first tweet in convo
);
create index on parsav_posts (author);
create index on parsav_posts (parent);
create table parsav_rels (
relator bigint references parsav_actors(id)
on delete cascade, -- e.g. follower
relatee bigint references parsav_actors(id)
on delete cascade, -- e.g. followed
kind smallint, -- e.g. follow, block, mute
primary key (relator, relatee, kind)
);
create table parsav_acts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
kind text not null, -- like, rt, react, so on
time bigint not null,
actor bigint references parsav_actors(id) on delete cascade,
subject bigint, -- may be post or act, depending on kind
body text -- emoji, if react
);
create index on parsav_acts (subject);
create index on parsav_acts (actor);
create index on parsav_acts (time);
create table parsav_log (
-- accesses are tracked for security & sending delete acts
id bigint primary key default (1+random()*(2^63-1))::bigint,
time bigint not null,
actor bigint references parsav_actors(id)
on delete cascade,
post bigint not null
);
create table parsav_artifacts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
birth bigint not null,
content bytea, -- if null, this is a "ban record" preventing content matching the hash from being re-uploaded
hash bytea unique not null, -- sha256 hash of content
-- it would be cool to use a computed column for this, but i don't want
-- to lock people into PG12 or drag in the pgcrypto extension just for this
mime text -- null if unknown, will be reported as x-octet-stream
);
create index on parsav_artifacts (mime);
create table parsav_artifact_claims (
birth bigint not null,
uid bigint references parsav_actors(id) on delete cascade,
rid bigint references parsav_artifacts(id) on delete cascade,
description text,
folder text,
unique (uid,rid)
);
create index on parsav_artifact_claims (uid);
create index on parsav_artifact_claims (uid,folder);
create table parsav_circles (
id bigint primary key default (1+random()*(2^63-1))::bigint,
owner bigint not null references parsav_actors(id) on delete cascade,
name text not null,
members bigint[] not null default array[]::bigint[],
unique (owner,name)
);
create index on parsav_circles (owner);
create table parsav_rooms (
id bigint primary key default (1+random()*(2^63-1))::bigint,
origin bigint references parsav_servers(id) on delete cascade,
name text not null,
description text not null,
policy smallint not null
);
create table parsav_room_members (
room bigint not null references parsav_rooms(id) on delete cascade,
member bigint not null references parsav_actors(id) on delete cascade,
rank smallint not null default 0,
admin boolean not null default false, -- non-admins with rank can only moderate + invite
title text, -- admin-granted title like reddit flair
vouchedby bigint references parsav_actors(id) on delete set null
);
create index on parsav_room_members (member);
create index on parsav_room_members (room);
create table parsav_invites (
id bigint primary key default (1+random()*(2^63-1))::bigint,
-- when a user is created from an invite, the invite is deleted and the invite
-- ID becomes the user ID. privileges granted on the invite ID during the invite
-- process are thus inherited by the user
issuer bigint references parsav_actors(id) on delete set null,
handle text, -- admin can lock invite to specific handle
rank smallint not null default 0,
quota integer not null default 1000
);
create table parsav_sanctions (
id bigint primary key default (1+random()*(2^63-1))::bigint,
issuer bigint references parsav_actors(id) on delete set null,
scope bigint, -- can be null or room for local actions
nature smallint not null, -- silence, suspend, disemvowel, censor, noreply, etc
victim bigint not null, -- can be user, room, or post
expire bigint, -- auto-expires if set
review bigint, -- brings up for review at given time if set
reason text, -- visible to victim if set
context text, -- admin-only note
appeal text -- null if no appeal lodged
);
create index on parsav_sanctions (victim,scope);
create index on parsav_sanctions (issuer);
create table parsav_actor_conf_strs (
uid bigint not null references parsav_actors(id) on delete cascade,
key text not null, value text not null, unique (uid,key)
);
create table parsav_actor_conf_ints (
uid bigint not null references parsav_actors(id) on delete cascade,
key text not null, value bigint not null, unique (uid,key)
);
-- create a temporary managed auth table; we can delete this later
-- if it ends up being replaced with a view
%include pgsql-auth.sql%