create table parsav_config (
key text primary key,
value text
);
comment on table parsav_config is
'server-wide configuration variables. highly sensitive!';
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 <def:uniq>,
domain text not null unique,
key bytea,
knownsince bigint,
parsav boolean -- whether to use parsav protocol extensions
);
comment on table parsav_servers is
'all servers known to the parsav instance. the local server (including its private key) is stored in row (id = 0)';
create table parsav_actors (
id <def:uniq>,
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)
);
comment on table parsav_actors is
'all users known to the instance across the fediverse; local users satisfy constraint (origin = 0)';
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);
comment on table parsav_rights is
'a backward-compatible list of every non-default privilege or deprivilege granted to a local user';
create table parsav_posts (
id <def:uniq>,
uri text, -- null if local
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 not null, -- e.g. follow, block, mute
since bigint not null,
primary key (relator, relatee, kind)
);
comment on table parsav_rels is
'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';
create table parsav_acts (
id <def:uniq>,
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; complaint, if report
);
create index on parsav_acts (subject);
create index on parsav_acts (actor);
create index on parsav_acts (time);
comment on table parsav_acts is
'every simple action taken on a tweet by an actor, including likes, rts, reacts, and reports';
create table parsav_log (
-- accesses are tracked for security & sending delete acts
id <def:uniq>,
time bigint not null,
actor bigint references parsav_actors(id)
on delete cascade,
post bigint not null
);
comment on table parsav_log is
'a log of accesses from foreign servers, tracking which will be sent update & delete events for each post';
create table parsav_artifacts (
id <def:uniq>,
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 octet-stream
);
create index on parsav_artifacts (mime);
comment on table parsav_artifacts is
'deduplicated media files uploaded by users';
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);
comment on table parsav_artifact_claims is
'a list of users who have an ownership interest in each artifact (effectively an index of GC roots)';
create table parsav_circles (
id <def:uniq>,
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 <def:uniq>,
origin bigint references parsav_servers(id) on delete cascade,
name text not null,
description text not null,
policy smallint not null
);
comment on table parsav_rooms is
'an index of user-created chatrooms';
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 <def:uniq>,
-- 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
);
comment on table parsav_invites is
'all active invitations and the level of authority they grant if accepted';
create table parsav_sanctions (
id <def:uniq>,
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);
comment on table parsav_sanctions is
'administrative actions taken against particular users, posts, rooms, or other entities';
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)
);
comment on table parsav_actor_conf_strs is 'per-user configuration settings (string properties)';
comment on table parsav_actor_conf_ints is 'per-user configuration settings (integer and enumeration properties)';
-- create a temporary managed auth table; we can delete this later
-- if it ends up being replaced with a view
%include pgsql-auth.sql%