Data Model

Flowstate Canvas stores all application data in MongoDB using Prisma ORM. This document explains the core entities, their relationships, and how they work together to power the platform.

Database Technology

MongoDB 7: Document database with flexible schema and rich query capabilities.

Prisma ORM: Type-safe database client that generates TypeScript types from the schema.

Why MongoDB: The data model is naturally document-oriented. Rooms contain Sparks. Offices contain members. Passports contain work history. MongoDB’s nested documents and arrays fit this structure perfectly.

Core Entities

User

The User model represents an account on the platform.

Fields:

id: Unique user ID
email: Email address (unique, required)
passwordHash: Bcrypt hash of the password
name: Display name
avatar: URL to avatar image
status: Account status (active, suspended, banned)
passport: Professional profile (JSON object, see Passport section)
createdAt: Account creation timestamp
updatedAt: Last update timestamp

Status Values:

active: Normal account, can sign in and use the platform
suspended: Temporarily disabled, cannot sign in
banned: Permanently disabled, cannot sign in

Relationships:

Has many Session (login sessions)
Has many Room (owned rooms)
Has many Office (owned offices)
Has many OfficeMember (office memberships)
Has many CanvasPosition (tile positions)
Has many ContentReport (reports filed by this user)
Has many ContentAppeal (appeals filed by this user)

Example:

{
  "id": "user_abc123",
  "email": "[email protected]",
  "name": "Alice Johnson",
  "avatar": "https://storage.example.com/avatars/alice.jpg",
  "status": "active",
  "passport": {
    "headline": "Product Designer",
    "bio": "I design delightful user experiences.",
    "workHistory": [...],
    "education": [...],
    "skills": ["UI Design", "Figma", "User Research"]
  },
  "createdAt": "2025-01-15T10:30:00Z",
  "updatedAt": "2025-02-20T14:45:00Z"
}

Session

The Session model represents a login session.

Fields:

id: Unique session ID
userId: User who owns this session
token: JWT token for authentication
deviceInfo: Device information (user agent, IP address)
expiresAt: Session expiration timestamp (7 days from creation)
createdAt: Session creation timestamp

Lifecycle:

User signs in with email and password
Backend creates a Session with a JWT token
Token is returned to the client and stored in a cookie (web) or local storage (desktop)
Client includes the token in all API requests
Backend validates the token and loads the session
Session expires after 7 days of inactivity

Security:

Tokens are signed with a secret key (JWT_SECRET)
Tokens include user ID and expiration timestamp
Expired sessions are rejected
Sessions can be revoked by deleting the Session record

Example:

{
  "id": "session_xyz789",
  "userId": "user_abc123",
  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
  "deviceInfo": {
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)...",
    "ipAddress": "192.168.1.100"
  },
  "expiresAt": "2025-02-27T10:30:00Z",
  "createdAt": "2025-02-20T10:30:00Z"
}

Room

A Room is a discussion space that contains Sparks.

Fields:

id: Unique room ID
userId: User who owns this room
title: Room title
description: Room description (optional)
color: Room color (hex code, e.g., #3b82f6)
icon: Room icon (emoji or icon name)
channels: Array of chat channels (see Channels section)
createdAt: Room creation timestamp
updatedAt: Last update timestamp

Relationships:

Belongs to User (owner)
Has many Spark (ideas/requests inside the room)
Has many CanvasPosition (positions on users’ canvases)

Channels:

Each Room has one or more chat channels. Channels are stored as an array of objects:

{
  "id": "channel_abc123",
  "name": "general",
  "description": "General discussion",
  "createdAt": "2025-02-20T10:30:00Z"
}

The first channel is always named “general” and is created automatically when the room is created.

Example:

{
  "id": "room_xyz789",
  "userId": "user_abc123",
  "title": "Q1 Features",
  "description": "Feature ideas for Q1 2025",
  "color": "#3b82f6",
  "icon": "💡",
  "channels": [
    {
      "id": "channel_abc123",
      "name": "general",
      "description": "General discussion",
      "createdAt": "2025-02-20T10:30:00Z"
    }
  ],
  "createdAt": "2025-02-20T10:30:00Z",
  "updatedAt": "2025-02-20T14:45:00Z"
}

Spark

A Spark is an idea or request placed inside a Room.

Fields:

id: Unique spark ID
roomId: Room that contains this spark
userId: User who created this spark
text: Spark content (plain text)
position: Position within the room (x, y coordinates)
createdAt: Spark creation timestamp
updatedAt: Last update timestamp

Relationships:

Belongs to Room
Belongs to User (creator)

Position:

Sparks have a position within the room. This is separate from the canvas position. The room position determines where the Spark appears inside the Room’s detail view.

Example:

{
  "id": "spark_def456",
  "roomId": "room_xyz789",
  "userId": "user_abc123",
  "text": "Add dark mode toggle to settings",
  "position": { "x": 100, "y": 200 },
  "createdAt": "2025-02-20T11:00:00Z",
  "updatedAt": "2025-02-20T11:00:00Z"
}

Office

An Office is a team workspace owned by one user and shared with members.

Fields:

id: Unique office ID
userId: User who owns this office
name: Office name
description: Office description (optional)
slug: URL-friendly slug (unique, e.g., acme-design-team)
avatar: URL to office avatar image
createdAt: Office creation timestamp
updatedAt: Last update timestamp

Relationships:

Belongs to User (owner)
Has many OfficeMember (members with roles)
Has many OfficeInvite (pending invites)
Has many CanvasPosition (positions on users’ canvases)

Example:

{
  "id": "office_ghi789",
  "userId": "user_abc123",
  "name": "Acme Design Team",
  "description": "Design team workspace for Acme Corp",
  "slug": "acme-design-team",
  "avatar": "https://storage.example.com/offices/acme.jpg",
  "createdAt": "2025-02-20T10:30:00Z",
  "updatedAt": "2025-02-20T14:45:00Z"
}

OfficeMember

An OfficeMember represents a user’s membership in an Office.

Fields:

id: Unique membership ID
officeId: Office this membership belongs to
userId: User who is a member
role: Member role (owner, admin, member)
joinedAt: Timestamp when the user joined

Roles:

owner: Created the office, can delete it, manage all members
admin: Can manage members, edit office settings
member: Can view and edit content, cannot manage members

Relationships:

Belongs to Office
Belongs to User

Example:

{
  "id": "member_jkl012",
  "officeId": "office_ghi789",
  "userId": "user_def456",
  "role": "admin",
  "joinedAt": "2025-02-21T09:00:00Z"
}

OfficeInvite

An OfficeInvite represents a pending invitation to join an Office.

Fields:

id: Unique invite ID
officeId: Office this invite is for
email: Email address of the invitee (optional, for email invites)
role: Role the invitee will have when they accept (admin or member)
token: Unique token for the invite link
expiresAt: Invite expiration timestamp (7 days from creation)
createdAt: Invite creation timestamp

Invite Types:

Email invite: Sent to a specific email address. Only that user can accept.
Link invite: Shareable link. Anyone with the link can accept.

Lifecycle:

Office owner or admin creates an invite
Backend generates a unique token
Invite is sent via email or shared as a link
Recipient clicks the link and signs in (or signs up)
Backend validates the token and adds the user to the office
Invite is deleted after acceptance

Example:

{
  "id": "invite_mno345",
  "officeId": "office_ghi789",
  "email": "[email protected]",
  "role": "member",
  "token": "inv_abc123xyz789",
  "expiresAt": "2025-02-27T10:30:00Z",
  "createdAt": "2025-02-20T10:30:00Z"
}

Shelf

A Shelf is a collection of links organized by category.

Fields:

id: Unique shelf ID
userId: User who owns this shelf
links: Array of link objects (see Links section)
createdAt: Shelf creation timestamp
updatedAt: Last update timestamp

Relationships:

Belongs to User
Has many CanvasPosition (positions on users’ canvases)

Links:

Each link is an object with these fields:

{
  "id": "link_abc123",
  "url": "https://example.com",
  "title": "Example Site",
  "category": "portfolio",
  "order": 0
}

Categories:

social: Social media profiles (Twitter, LinkedIn, etc.)
portfolio: Portfolio sites, personal websites
resource: Useful resources, documentation, tools
custom: Custom category (user-defined)

Example:

{
  "id": "shelf_pqr678",
  "userId": "user_abc123",
  "links": [
    {
      "id": "link_abc123",
      "url": "https://twitter.com/alice",
      "title": "Twitter",
      "category": "social",
      "order": 0
    },
    {
      "id": "link_def456",
      "url": "https://alice.design",
      "title": "Portfolio",
      "category": "portfolio",
      "order": 1
    }
  ],
  "createdAt": "2025-02-20T10:30:00Z",
  "updatedAt": "2025-02-20T14:45:00Z"
}

CanvasPosition

A CanvasPosition represents where a tile sits on a user’s canvas.

Fields:

id: Unique position ID
userId: User who owns this position
itemType: Type of tile (room, office, widget, etc.)
itemId: ID of the entity (e.g., room_xyz789)
canvasScope: Which canvas (main by default)
x: X coordinate
y: Y coordinate
isPinned: Whether the tile is pinned
createdAt: Position creation timestamp
updatedAt: Last update timestamp

Unique Constraint:

The database enforces a unique constraint on (userId, itemType, itemId, canvasScope). Each tile has exactly one position per user per canvas.

Relationships:

Belongs to User

Example:

{
  "id": "pos_stu901",
  "userId": "user_abc123",
  "itemType": "room",
  "itemId": "room_xyz789",
  "canvasScope": "main",
  "x": 240,
  "y": 180,
  "isPinned": false,
  "createdAt": "2025-02-20T10:30:00Z",
  "updatedAt": "2025-02-20T14:45:00Z"
}

Passport

A Passport is a professional profile stored as a JSON object on the User model.

Structure:

{
  "headline": "Product Designer",
  "bio": "I design delightful user experiences.",
  "workHistory": [
    {
      "id": "work_abc123",
      "company": "Acme Corp",
      "title": "Senior Product Designer",
      "startDate": "2023-01-01",
      "endDate": null,
      "description": "Lead designer for the core product.",
      "current": true
    }
  ],
  "education": [
    {
      "id": "edu_def456",
      "institution": "Design University",
      "degree": "Bachelor of Fine Arts",
      "field": "Interaction Design",
      "startDate": "2015-09-01",
      "endDate": "2019-05-31"
    }
  ],
  "skills": ["UI Design", "Figma", "User Research"],
  "certifications": [
    {
      "id": "cert_ghi789",
      "name": "Certified UX Professional",
      "issuer": "UX Certification Institute",
      "issueDate": "2022-06-15",
      "expiryDate": null,
      "credentialUrl": "https://example.com/cert/abc123"
    }
  ]
}

Fields:

headline: Short professional headline (e.g., “Product Designer”)
bio: Longer bio or summary
workHistory: Array of work experience entries
education: Array of education entries
skills: Array of skill names
certifications: Array of certification entries

Why JSON:

Passports are stored as JSON because the structure is flexible and varies by user. Some users have extensive work history, others have minimal. JSON allows this flexibility without requiring complex schema changes.

A Widget is an extensible tool installed on the canvas.

Fields:

id: Unique widget installation ID
userId: User who installed this widget
widgetType: Type of widget (e.g., todo, calendar, analytics)
settings: Widget-specific settings (JSON object)
trustLevel: Trust level (trusted, verified, unverified)
createdAt: Installation timestamp
updatedAt: Last update timestamp

Relationships:

Belongs to User
Has many CanvasPosition (positions on users’ canvases)

Trust Levels:

trusted: Built by the Flowstate team, fully trusted
verified: Built by verified third-party developers, reviewed by Flowstate
unverified: Built by unverified developers, use at your own risk

Settings:

Each widget type has its own settings schema. For example, a todo widget might have:

{
  "showCompleted": true,
  "sortBy": "dueDate",
  "theme": "light"
}

Example:

{
  "id": "widget_vwx234",
  "userId": "user_abc123",
  "widgetType": "todo",
  "settings": {
    "showCompleted": true,
    "sortBy": "dueDate"
  },
  "trustLevel": "trusted",
  "createdAt": "2025-02-20T10:30:00Z",
  "updatedAt": "2025-02-20T14:45:00Z"
}

MOTD (Message of the Day)

A MOTD is a platform-wide announcement from administrators.

Fields:

id: Unique MOTD ID
title: Announcement title
message: Announcement message (supports Markdown)
type: Message type (info, warning, critical)
isActive: Whether the message is currently displayed
createdAt: Creation timestamp
updatedAt: Last update timestamp

Types:

info: General information (blue)
warning: Important notice (yellow)
critical: Urgent alert (red)

Lifecycle:

Admin creates a MOTD
MOTD appears on all users’ canvases
Users can dismiss the MOTD (it’s hidden for them)
Admin can deactivate the MOTD (it’s hidden for everyone)

Example:

{
  "id": "motd_yza567",
  "title": "Scheduled Maintenance",
  "message": "The platform will be down for maintenance on Feb 25 from 2-4 AM UTC.",
  "type": "warning",
  "isActive": true,
  "createdAt": "2025-02-20T10:30:00Z",
  "updatedAt": "2025-02-20T10:30:00Z"
}

Moderation Models

ContentReport

A ContentReport represents a user-submitted report of inappropriate content.

Fields:

id: Unique report ID
reporterId: User who filed the report
contentType: Type of content (room, spark, office, user)
contentId: ID of the reported content
reason: Report reason (see Reasons section)
details: Additional details from the reporter (optional)
status: Report status (pending, resolved, dismissed)
resolvedBy: Admin who resolved the report (optional)
resolvedAt: Resolution timestamp (optional)
createdAt: Report creation timestamp

Reasons:

spam: Spam or advertising
harassment: Harassment or bullying
inappropriate: Inappropriate content (NSFW, offensive, etc.)
copyright: Copyright violation
other: Other reason (details required)

Status Values:

pending: Report is under review
resolved: Report was reviewed and action was taken
dismissed: Report was reviewed and no action was taken

Relationships:

Belongs to User (reporter)
Optionally belongs to AdminUser (resolver)

Example:

{
  "id": "report_bcd890",
  "reporterId": "user_abc123",
  "contentType": "spark",
  "contentId": "spark_def456",
  "reason": "spam",
  "details": "This spark is advertising a product.",
  "status": "pending",
  "createdAt": "2025-02-20T10:30:00Z"
}

ContentAppeal

A ContentAppeal represents a user’s appeal of a moderation decision.

Fields:

id: Unique appeal ID
reportId: Report being appealed
userId: User filing the appeal
reason: Appeal reason (text)
status: Appeal status (pending, approved, denied)
reviewedBy: Admin who reviewed the appeal (optional)
reviewedAt: Review timestamp (optional)
createdAt: Appeal creation timestamp

Lifecycle:

User’s content is moderated (removed or hidden)
User files an appeal explaining why the decision was wrong
Admin reviews the appeal
Admin approves (content is restored) or denies (decision stands)

Relationships:

Belongs to ContentReport
Belongs to User (appellant)
Optionally belongs to AdminUser (reviewer)

Example:

{
  "id": "appeal_efg123",
  "reportId": "report_bcd890",
  "userId": "user_def456",
  "reason": "This was not spam, it was a legitimate feature request.",
  "status": "pending",
  "createdAt": "2025-02-21T09:00:00Z"
}

Admin Models

AdminUser

An AdminUser represents a platform administrator.

Fields:

id: Unique admin ID
email: Email address (unique, required)
name: Display name
role: Admin role (see Roles section)
oidcSub: OIDC subject identifier (from Google Workspace)
createdAt: Account creation timestamp
updatedAt: Last update timestamp

Roles:

super_admin: Full platform access, can manage other admins
admin: Can manage users, content, and settings
moderator: Can review reports and moderate content
support: Can view user data and assist with support requests
analyst: Read-only access to analytics and metrics

Authentication:

Admins sign in via OIDC (Google Workspace). The backend verifies the OIDC token and checks the admin’s role before granting access.

Relationships:

Has many AuditLog (actions performed by this admin)

Example:

{
  "id": "admin_hij456",
  "email": "[email protected]",
  "name": "Admin User",
  "role": "admin",
  "oidcSub": "google-oauth2|123456789",
  "createdAt": "2025-01-01T00:00:00Z",
  "updatedAt": "2025-02-20T10:30:00Z"
}

AuditLog

An AuditLog entry records an admin action for compliance and debugging.

Fields:

id: Unique log ID
adminId: Admin who performed the action
action: Action type (e.g., user.suspend, content.delete)
targetType: Type of target entity (user, room, office, etc.)
targetId: ID of the target entity
details: Additional details (JSON object)
createdAt: Action timestamp

Common Actions:

user.suspend: Suspended a user account
user.ban: Banned a user account
user.unsuspend: Unsuspended a user account
content.delete: Deleted content (room, spark, etc.)
report.resolve: Resolved a content report
appeal.approve: Approved a content appeal

Relationships:

Belongs to AdminUser

Example:

{
  "id": "log_klm789",
  "adminId": "admin_hij456",
  "action": "user.suspend",
  "targetType": "user",
  "targetId": "user_abc123",
  "details": {
    "reason": "Repeated spam violations",
    "duration": "7 days"
  },
  "createdAt": "2025-02-20T10:30:00Z"
}

Relationships Diagram

Here’s how the core entities relate to each other:

User
  ├─ has many Session
  ├─ has many Room
  │    └─ has many Spark
  ├─ has many Office (owned)
  │    ├─ has many OfficeMember
  │    └─ has many OfficeInvite
  ├─ has many OfficeMember (memberships)
  ├─ has many CanvasPosition
  ├─ has many Widget
  ├─ has one Shelf
  ├─ has one Passport (JSON field on User)
  ├─ has many ContentReport (filed)
  └─ has many ContentAppeal (filed)

AdminUser
  ├─ has many AuditLog
  ├─ resolves many ContentReport
  └─ reviews many ContentAppeal

MOTD
  └─ appears on all users' canvases

Indexes

The Prisma schema defines indexes for common queries:

User.email: Unique index for fast email lookups
Session.userId: Index for loading user sessions
Room.userId: Index for listing user’s rooms
Spark.roomId: Index for loading sparks in a room
Office.slug: Unique index for office URL lookups
OfficeMember.officeId: Index for loading office members
CanvasPosition.(userId, itemType, itemId, canvasScope): Unique index for position lookups
Widget.userId: Index for listing user’s widgets
ContentReport.status: Index for filtering pending reports
AuditLog.adminId: Index for loading admin’s actions

These indexes ensure queries are fast, even with millions of records.

Data Validation

All API inputs are validated with Zod schemas before being written to the database. This ensures data integrity and prevents invalid data from entering the system.

Example validation schema:

const createRoomSchema = z.object({
  title: z.string().min(1).max(100),
  description: z.string().max(500).optional(),
  color: z.string().regex(/^#[0-9a-f]{6}$/i),
  icon: z.string().max(10),
});

If validation fails, the API returns a 400 Bad Request with detailed error messages.

Data Migration

When the schema changes, Prisma generates migration files that are applied during deployment.

Migration workflow:

Update the Prisma schema
Regenerate the Prisma client
Apply the schema changes to the database
Test the changes locally
Commit the schema and migration files
Deploy to production (migrations run automatically)

Data Seeding

A seed script populates the database with demo data for development.

Seed data includes:

3 demo users ([email protected], [email protected], [email protected])
5 Rooms with Sparks
2 Offices with members
Sample canvas positions
A Message of the Day

Data Backup

In production, MongoDB is backed up daily. Backups are stored in S3 with 30-day retention.

Backup process:

Automated script runs daily at 2 AM UTC
Script uses mongodump to export the database
Dump is compressed and uploaded to S3
Old backups (30+ days) are deleted

Restore process:

Download the backup from S3
Decompress the dump
Use mongorestore to restore the database
Verify data integrity

Summary

Flowstate Canvas uses MongoDB to store all application data. The core entities are User, Session, Room, Spark, Office, CanvasPosition, Passport, Widget, and MOTD. Moderation is handled by ContentReport and ContentAppeal. Admins are managed by AdminUser and AuditLog. Relationships between entities are defined in the Prisma schema. Indexes ensure fast queries. Validation ensures data integrity. Migrations handle schema changes. Seeding provides demo data for development.

For more details on how these entities are used in the application, see the Architecture Overview and The Canvas documentation.