Skip to main content

Data types and schema

In order for the collaboration system to work, the following data must be able to be provided by your backend.

note

If you are using the default database, all of this data is provided by default, so you can skip this page.

info

The data in your database does not need to be stored exactly as specified below. You just need to be able to provide this data in your resolvers. If your data is stored in a different shape, you can use the resolvers to transform your spec into our spec.

Data types#

Users#

You must be able to provide the following information about users:

  • id the user's unique ID
  • email the user's email
  • type the type of user, either STANDARD or ANONYMOUS
  • createdAt the date the user was created (epoch timestamp in MS)
  • updatedAt the date the document was last updated (epoch timestamp in MS)

Documents#

You must be able to provide the following information about documents

  • id the document's unique ID
  • authorId the ID of the user who created the document
  • createdAt the date when the document was created (epoch timestamp in MS)
  • updatedAt the date when the document was last updated (epoch timestamp in MS)
  • isPublic whether or not the document is publicly viewable
  • name the name of the document

Annotations#

You must be able to provide the following information about annotations

  • id the annotation's unique ID
  • xfdf the XFDF command for the annotation
  • authorId the ID of the user who created the annotation
  • documentId the ID of the document that the annotation belongs to
  • pageNumber the page number for the annotation
  • createdAt the date when the annotation was created (epoch timestamp in MS)
  • updatedAt the date when the annotation was last updated (epoch timestamp in MS)
  • inReplyTo the ID of the parent annotation

Document Memberships#

You must be able to provide the following information about a user's membership to a document.

In most existing systems, this table will not exist. In that case, you can just create one and store the following data.

  • id the document membership's unique ID
  • userId the ID of the user who this membership belongs to
  • documentId the ID of the document that this membership belongs to
  • lastRead the date when the document was last read (epoch timestamp in MS)
  • createdAt the date when the membership was created (epoch timestamp in MS)
  • updatedAt the date when the member was last updated (epoch timestamp in MS)

Annotation Memberships#

You must be able to provide the following information about a user's membership to an annotation.

In most existing systems, this table will not exist. In that case, you can just create one and store the following data.

  • id the annotation membership's unique ID
  • userId the ID of the user who this membership belongs to
  • documentId the ID of the document that this membership belongs to
  • annotationId the ID of the annotation this membership belongs to
  • lastRead the date when the annotation was last read (epoch timestamp in MS)
  • createdAt the date when the membership was created (epoch timestamp in MS)
  • updatedAt the date when the member was last updated (epoch timestamp in MS)
  • annotationCreatedAt the date when the associated annotation was created (epoch timestamp in MS)

Mentions#

You must be able to provide the following information about a mention to an annotation.

In most existing systems, this table will not exist. In that case, you can just create one and store the following data.

  • id the mention unique ID
  • userId the ID of the user who this mention belongs to
  • documentId the ID of the document that this mention belongs to
  • annotationId the ID of the annotation that this mention belongs to
  • readBeforeMention whether or not the annotation that this mention belongs to had been read before this mention was created
  • createdAt the date the mention was created (epoch timestamp in MS)
  • updatedAt the date the mention was last updated (epoch timestamp in MS)

Recommended database schema#

If you are starting from scratch or are creating new tables for collaboration, we recommend the following database structure. The following query is SQL, but can translate to any database.

CREATE TYPE user_type AS ENUM('ANONYMOUS', 'STANDARD');
CREATE TYPE user_status AS ENUM ('ACTIVE', 'INACTIVE');
CREATE TABLE users (
id VARCHAR(36) NOT NULL PRIMARY KEY,
user_name VARCHAR(50),
email VARCHAR(150) UNIQUE,
password VARCHAR(64),
status user_status,
type user_type,
custom_data JSONB,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE documents (
id VARCHAR(36) NOT NULL PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
author_id VARCHAR(36) REFERENCES users (id),
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_public BOOLEAN NOT NULL,
name VARCHAR(1000)
);
CREATE TABLE annotations (
id VARCHAR(36) NOT NULL PRIMARY KEY,
xfdf TEXT,
author_id VARCHAR(36) REFERENCES users (id),
document_id VARCHAR(36) REFERENCES documents (id) ON DELETE CASCADE,
page_number BIGINT NOT NULL,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
in_reply_to VARCHAR(36)
);
CREATE TABLE annotation_members (
id VARCHAR(36) NOT NULL PRIMARY KEY,
user_id VARCHAR(36) REFERENCES users (id),
document_id VARCHAR(36) REFERENCES documents (id) ON DELETE CASCADE,
annotation_id VARCHAR(36) REFERENCES annotations (id) ON DELETE CASCADE,
last_read timestamptz DEFAULT CURRENT_TIMESTAMP,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
annotation_created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE (user_id, document_id, annotation_id)
);
CREATE TABLE document_members (
id VARCHAR(36) NOT NULL PRIMARY KEY,
user_id VARCHAR(36) REFERENCES users (id),
document_id VARCHAR(36) REFERENCES documents (id) ON DELETE CASCADE,
last_read timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE (user_id, document_id)
);
CREATE TABLE mentions (
id VARCHAR(36) NOT NULL PRIMARY KEY,
user_id VARCHAR(36) REFERENCES users (id),
document_id VARCHAR(36) REFERENCES documents (id) ON DELETE CASCADE,
annotation_id VARCHAr(36) REFERENCES annotations (id) ON DELETE CASCADE,
read_before_mention BOOLEAN,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE (user_id, document_id, annotation_id)
);