General > Create Mock Database

[JacobHeldt]

Description

We need to set up a mock PostgreSQL database in Supabase to facilitate the development of our platform. This database will simulate real-world data interactions and allow our team to develop and test features without affecting the production environment. If you have any questions or suggestions regarding the schema, just comment on this issue or message me on Discord (@.___jacob___).

Tasks

Define and Implement Schema:

• Define the database schema and create tables

Populate Mock Data:

• Generate realistic mock data according to the schema.
• Ensure the mock data covers all use cases and edge cases for thorough testing.

Database Schema:

You can find the full schema with examples at docs/schema.txt.

Preview:

// Schema
type User = {
    id: ObjectId;
    name: string;
    userType: UserType; 
    email: string;
    about: string;
    avatar: string;
    isTutor: boolean;
    settings: UserSettings;
    notifications: Notification[];
    groupsJoined: ObjectId[]; // Array of group ObjectIds
    groupsCreated: ObjectId[]; // Array of group ObjectIds
};

enum UserType {
    Student,
    Teacher
}

type UserSettings = {
    emailNotifications: boolean;
    showGroups: boolean;
};

enum NotificationCategory {
    GroupQuestion,
    GroupMaterial,
    PlatformUpdate
}

type Notification = {
    id: ObjectId;
    category: NotificationCategory;
    createdAt: Date;
    title: string;
    content: string;
    read: boolean;
};

type Message = {
    id: ObjectId;
    content: string;
    groupId: ObjectId;
    user: ObjectId; // user ObjectId
    createdAt: Date;
};

type MessageReport {
    id: ObjectId;
    messageId: ObjectId;
    note: string;
}

enum GroupSubject {
    Math,
    English,
    Science,
    SocialScience,
    ComputerScience,
    Language,
    TestPrep,
    Other
}

type Group = {
    id: ObjectId;
    name: string;
    adminId: ObjectId; // User ObjectId of person who created the group
    description: string;
    subject: GroupSubject;
    members: ObjectId[]; // Array of user ObjectIds
    bannerImage: string;
    qna: Question[];
    messages: Message[];
    materialFolders: Folder[];
    materials: Material[];
};

type Folder = {
    id: ObjectId;
    name: string;
    createdAt: Date;
};

enum MaterialType {
    GoogleDoc,
    GoogleSlides,
    GoogleSheet,
    Word,
    Powerpoint,
    Excel,
    YouTube,
    Website
}

type Material = {
    id: ObjectId;
    name: string;
    type: MaterialType;
    folder: ObjectId; // Reference to Folder ObjectId
    link: string;
    createdAt: Date;
};

type Question = {
    id: ObjectId;
    title: string;
    content: string;
    user: ObjectId; // user ObjectId
    comments: QuestionComment[];
    upvotes: number;
    createdAt: Date;
};

type QuestionComment = {
    id: ObjectId;
    content: string;
    user: ObjectId; // user ObjectId
    createdAt: Date;
};

[zaibutcooler]

Will work on the issue