Database Schema

GCMS uses PostgreSQL as its primary data store, hosted on Supabase. This section documents the data model that underpins every feature in the application.

• Entity Reference
• Entity Relationship Diagram

Overview

The schema is centred around three core entities:

• users — individual people who can sign in to GCMS

• projects — group coursework projects that users belong to

• tasks — units of work within a project, assigned to users

These are connected by a many-to-many relationship table (user_projects) and supported by feature-specific tables for messages, meetings, notifications, the interactive project board (widgets), shared project files (files), and AI assistant chat history (ai_chat_messages).

All tables use UUID primary keys generated by gen_random_uuid(), which makes IDs unguessable and avoids the need for sequence synchronisation across environments.