schema.rst

Database schema for Rivulet

This document attempts to describe, in a general way, my current thoughts around a sketch for what the (ultimately SQL) database schema for Rivulet could look like. I currently imagine that Rivulet would eventually support several database engines; I think I'll probably want to prototype with SQLite and then I will probably have energy for additional development to support PostgreSQL. But I hope that the documentation here will be largely independent of the particular engine that we might use.

Common columns

name	description	type
name	the name of the thing	text
description	the description of the thing	text
updated	when the record was last updated	date and time

Users table

A Rivulet user can be either anonymous or authenticated. What is the best way to model this dichotomy? This is one approach.

User table columns

name	description	type
id	the user's id	auto-incremented primary key
email	the user's email address	text
anonymous token	a token "authenticating" a user with an anonymous session	UUID

Watersheds table

name	description	type
id	the watershed's id	auto-incremented primary key
branch size	the size of branches in this watershed (i.e. the number of users at which a branch is considered "full")	integer greater than 1

Branches table

name	description	type
id	the branch's id	auto-incremented primary key
watershed id	the parent watershed for this branch	reference to the watershed, in n:1 correspondence
conclusions	the text of any conclusions generated by the members of this branch	text
head	Is the branch currently unrepresentated on further branches? This had been called "active". That was based on the following understanding (but I think "head" is a more useful generalization): Is the current branch still having active conversation? This can be inferred from information in the participation table, so having this here is a sort of redundant check of the consistency of information there.	boolean
progression	How many branch merges (and thus representation selections) does this branch represent? This may (also) be inferred from the participation table.	non-negative integer

Participation table

This table tracks how users are participating in branches throughout a space. A user can be listed as participating in multiple branches in the same watershed, but only one of those is active; the others are historic, following a sequence of convergences back to an original branch.

name	description	type
user id	the user whose participation we are tracking	reference to the user, in n:1 correspondence
branch id	the branch under consideration that the user has participated in (and which is possibly their current branch). If this is empty, it means that the user has been chosen as a representative, but has not yet joined a new branch.	reference to the branch, in n:1? correspondence
branch source id	the branch that the user is representing in the current branch. If this is empty, it means the user's participation in the current branch was as an entry into the watershed.	reference to the branch, in n:1? correspondence
end status	indicates the reason why the user is no longer on the branch under consideration; if this is null, then the user should still be active on that branch.	string from curated list (e.g. abandoned, ejected, representative)
last seen	when the user was last noted to have connected to the branch	timestamp
override	allow the user to access the branch ignoring normal participation restrictions (possibly for facilitation, dispute mediation, or because of a loss of history)	boolean

Messages table

name	description	type
id	an identifier for this message	auto-incremented primary key
author id	the user who wrote the message	reference to the user, in n:1 correspondence
content	the text of the message itself	text
submitted	when the message was sent	timestamp
branch id	the branch containing this message (i.e. by proxy the set of users who can see the message in a given watershed)	reference to the branch, in n:1 correspondence
quoted message id	the message that this message optionally quotes	reference to the message, in n:1 correspondence
proposal type	if this message is also a proposal, this field will be set to the type of the proposal. Two options that I currently imagine are for summary proposals and representative proposals.	string, possibly curated list
proposal input	the content of the proposal for consideration within the branch	text

Reactions table

Users can give reactions to messages; these reactions are stored in this table.

name	description	type
id	the identifier for the reaction	auto-incremented primary key, possibly use (user id, type, message id) instead?
user id	the user making the reaction	reference to the user, in n:1 correspondence
intent	the type of the reaction (e.g. "+1")	string, possibly curated list
message id	the message targeted by the reaction	reference to the message, in n:1 correspondence
submitted	when the reaction was sent	timestamp