DB Toolkit

• Database: Postgresql v10+, with JSONB columns for storing ActivityStreams data as-is
• Data modeling tools:
  • JSON Editor Online & JSON-LD Playground or Hackolade for JSON objects
  • SQLEditor (Mac) or DBdesigner (online) for SQL DB

Defining our relational database

Starting off with Pleroma's Postgres DB, imported into SQLEditor:

The database is generated based on migrations defined in Elixir, so any changes should be done there. One can also generate an SQL definition files by running pg_dump -U postgres -h localhost pleroma -s -f db_definition.sql

Note the objects.data and activities.data JSONB fields, which contain ActivityStreams data as a JSON document (in binary form, with indexes on certain paths).

DB structure for MoodleNet

ActivityStreams represents entities capable of carrying out an Activity as Actors(possible types of Actors: Application | Group | Organization | Person | Service). Actors are elsewhere also known as Agents.

Pleroma only had a traditional concept of users though, so changes we need to make to the database include:

• actors (new table with fields relocated from the users table) which can represent both profiles (to enable one user having multiple Person identities) and groups (like Community and Curation)
  • primary_user_id indicates the user linked to that Actor, or who originally created that group
  • actor_type can include Person, Group, Community, Curation, Organisation, etc
  • actor_openness is optional and could include invite-only, moderated, open, etc
  • actor_uri (renamed from ap_id) indicates the actor URI
• actor_relationships (to store memberships and permissions of groups)
  • subject_actor_id could refer to a Person agent like Alice
  • target_actor_id could refer to a Group agent like 'Geographers of the world'
  • relationship_type could be follow, admin, moderate, contribute, participate, watch, vouch, etc
  • relationship_confirmed indicates whether the object has confirmed the relationship requested by the subject (for example a moderator has approved Alice's request to join the group)

Here's an updated DB structure:

Available as an SQL file (not meant to be used as-is).

Options to research

• http://leveldb.org/
• https://dgraph.io

About metadata

• Standards: https://docs.google.com/document/d/1HyhOe06nJOnrhQxkfowJKMZDEBak1yEBSuX-qv7AhKo/edit?ts=5b30e143#
• Metadata for MoodleNet: https://docs.google.com/document/d/1dXAaj9iME1Hvw8E5IhJD5UtAZBLwFI7TkiVK7HkgbZI/edit?ts=5b30e134#heading=h.rmppti8baa65
• OER platforms' metadata: https://gitlab.com/moodlenet/meta/wikis/OER-metadata
• Taxonomies and common metadata values: https://gitlab.com/moodlenet/meta/wikis/Taxonomies-and-common-metadata-values

---

Also see comments on deprecated Trello card: https://trello.com/c/cFtUI5Kb/69-databases-models-data-metadata