Data Models and Schemas

Understanding data structures and relationships in the system

Learn about the data models that power our platform.

Entity Relationship Overview

Our primary entities and their relationships:

User Entity

{
  "id": "uuid",
  "username": "string",
  "email": "string",
  "created_at": "timestamp",
  "updated_at": "timestamp",
  "profile": {
    "first_name": "string",
    "last_name": "string",
    "avatar_url": "string"
  }
}

Organization Entity

{
  "id": "uuid",
  "name": "string",
  "slug": "string",
  "owner_id": "uuid",
  "members": ["user_id"],
  "created_at": "timestamp"
}

Project Entity

{
  "id": "uuid",
  "name": "string",
  "organization_id": "uuid",
  "description": "string",
  "settings": {
    "visibility": "public|private",
    "features": ["string"]
  }
}

Relationships

• One User can own multiple Organizations
• One Organization can have multiple Projects
• Many Users can be members of one Organization (many-to-many)
• One Project belongs to one Organization

Database Schema

Users Table

Organizations Table

Data Validation Rules

1. Email: Must be valid email format
2. Username: 3-50 characters, alphanumeric and underscores only
3. Slug: Lowercase, alphanumeric and hyphens only
4. Password: Minimum 8 characters, must include uppercase, lowercase, and number

Query Patterns

Fetching User with Organizations

SELECT u.*, o.*
FROM users u
LEFT JOIN organization_members om ON u.id = om.user_id
LEFT JOIN organizations o ON om.organization_id = o.id
WHERE u.id = $1;

Listing Projects by Organization

SELECT p.*
FROM projects p
WHERE p.organization_id = $1
ORDER BY p.created_at DESC;

Data Migration

When updating schemas, always:

1. Create a migration file
2. Include both up and down migrations
3. Test in staging environment
4. Backup production data before applying