Taskboard Data Model & Architecture

Understanding Waymaker's taskboard data model is essential for advanced integrations, API usage, custom reporting, and building tools that interact with taskboard data.

Architecture Overview

Waymaker taskboards are built on a hierarchical, relational data model with real-time capabilities.

Key Architectural Principles

1. Hierarchical Structure

Organization
  └── Boards
       └── Layers
            └── Sections
                 └── Tasks
                      ├── Comments
                      ├── Attachments
                      └── Dependencies

2. Separation of Concerns

Authentication: Separate auth layer
Application Data: Application database
Real-time: Live subscriptions for instant updates
Storage: Separate bucket for file attachments

3. Security Model

Access rules on all tables
Policy-based access control
Organization-scoped data isolation
Role-based permissions

4. Performance Optimization

Strategic indexing on foreign keys and filters
Materialized views for complex queries
Efficient denormalization for common reads
Pagination for large datasets

Core Entities

1. Organizations

Purpose: Top-level container for all workspace data

Schema:

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  owner_id UUID NOT NULL REFERENCES auth.users(id),
  settings JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Key Fields:

id: Unique organization identifier
name: Display name (e.g., "Acme Corp")
slug: URL-friendly identifier (e.g., "acme-corp")
owner_id: Organization owner (billing responsible party)
settings: Organization-wide configuration

Settings JSONB Structure:

{
  "features": {
    "taskboards": true,
    "ai_assistance": false,
    "custom_fields": true
  },
  "limits": {
    "max_boards": 50,
    "max_members": 100,
    "storage_gb": 50
  },
  "branding": {
    "logo_url": "https://...",
    "primary_color": "#001126"
  }
}

Relationships:

Has Many: boards, organization_members, teams
Belongs To: users (owner)

Indexes:

CREATE INDEX idx_organizations_slug ON organizations(slug);
CREATE INDEX idx_organizations_owner ON organizations(owner_id);

2. Organization Members

Purpose: User membership and roles within organizations

Schema:

CREATE TABLE organization_members (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member', 'guest')),
  permissions JSONB DEFAULT '{}',
  invited_by UUID REFERENCES auth.users(id),
  joined_at TIMESTAMPTZ DEFAULT now(),
  created_at TIMESTAMPTZ DEFAULT now(),

  UNIQUE(organization_id, user_id)
);

Key Fields:

role: User's role (owner, admin, member, guest)
permissions: Custom permission overrides
invited_by: User who sent the invitation

Role Hierarchy:

Owner (1 per org)
  ├── Full organizational control
  ├── Billing and subscription management
  └── Can delete organization

Admin
  ├── Manage members and teams
  ├── Create and delete boards
  └── Configure organization settings

Member
  ├── Access assigned boards
  ├── Create and edit tasks
  └── Collaborate on projects

Guest
  ├── Limited board access (explicitly invited)
  ├── View and comment only (by default)
  └── No organizational visibility

Indexes:

CREATE INDEX idx_org_members_org ON organization_members(organization_id);
CREATE INDEX idx_org_members_user ON organization_members(user_id);
CREATE INDEX idx_org_members_role ON organization_members(organization_id, role);

3. Boards

Purpose: Container for tasks organized by workflow

Schema:

CREATE TABLE boards (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  settings JSONB DEFAULT '{}',
  created_by UUID NOT NULL REFERENCES auth.users(id),
  archived BOOLEAN DEFAULT false,
  archived_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Key Fields:

name: Board title (e.g., "Q1 Product Launch")
description: Board purpose and context
settings: Board-specific configuration
archived: Soft delete flag

Settings JSONB Structure:

{
  "workflow": {
    "default_sections": ["To Do", "In Progress", "Done"],
    "auto_archive_completed": false,
    "task_id_prefix": "TASK"
  },
  "permissions": {
    "default_member_role": "editor",
    "allow_guest_comments": true,
    "require_assignee": false
  },
  "integrations": {
    "slack_channel": "#project-updates",
    "github_repo": "org/repo"
  },
  "notifications": {
    "task_assigned": true,
    "task_completed": false,
    "comment_mentioned": true
  }
}

Relationships:

Belongs To: organizations, users (creator)
Has Many: layers, board_members, tasks

Indexes:

CREATE INDEX idx_boards_org ON boards(organization_id);
CREATE INDEX idx_boards_archived ON boards(organization_id, archived);
CREATE INDEX idx_boards_created_by ON boards(created_by);

4. Board Members

Purpose: User access and permissions per board

Schema:

CREATE TABLE board_members (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  board_id UUID NOT NULL REFERENCES boards(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'editor', 'viewer', 'commenter')),
  added_by UUID REFERENCES auth.users(id),
  added_at TIMESTAMPTZ DEFAULT now(),

  UNIQUE(board_id, user_id)
);

Board Role Permissions:

Owner
  ├── Full board control
  ├── Manage board members
  ├── Archive/delete board
  └── Configure board settings

Editor
  ├── Create and edit all tasks
  ├── Manage layers and sections
  ├── Add comments and attachments
  └── Cannot manage board settings

Viewer
  ├── View all tasks and comments
  ├── Export and print
  └── Cannot edit or comment

Commenter
  ├── View all tasks
  ├── Add comments
  └── Cannot edit tasks

Indexes:

CREATE INDEX idx_board_members_board ON board_members(board_id);
CREATE INDEX idx_board_members_user ON board_members(user_id);

5. Layers

Purpose: Organize tasks into logical groupings (sprints, epics, phases)

Schema:

CREATE TABLE layers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  board_id UUID NOT NULL REFERENCES boards(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  layer_type TEXT NOT NULL CHECK (layer_type IN ('sprint', 'epic', 'milestone', 'phase', 'custom')),
  status TEXT NOT NULL CHECK (status IN ('active', 'completed', 'archived')),
  start_date DATE,
  end_date DATE,
  color TEXT,
  sort_order INTEGER NOT NULL,
  metadata JSONB DEFAULT '{}',
  created_by UUID NOT NULL REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Key Fields:

layer_type: Classification (sprint, epic, milestone, phase, custom)
status: Lifecycle state (active, completed, archived)
start_date, end_date: Time boundaries
sort_order: Display order on board
metadata: Layer-specific data

Metadata JSONB Structure:

{
  "sprint": {
    "sprint_number": 23,
    "velocity_target": 25,
    "actual_velocity": 23
  },
  "epic": {
    "epic_key": "EPIC-45",
    "theme": "User Onboarding",
    "business_value": "high"
  },
  "milestone": {
    "deliverables": ["Feature A", "Feature B"],
    "stakeholders": ["user1", "user2"]
  },
  "progress": {
    "total_tasks": 15,
    "completed_tasks": 8,
    "completion_percentage": 53
  }
}

Relationships:

Belongs To: boards, users (creator)
Has Many: tasks

Indexes:

CREATE INDEX idx_layers_board ON layers(board_id);
CREATE INDEX idx_layers_status ON layers(board_id, status);
CREATE INDEX idx_layers_sort ON layers(board_id, sort_order);
CREATE INDEX idx_layers_dates ON layers(board_id, start_date, end_date);

6. Tasks

Purpose: Individual work items with full lifecycle tracking

Schema:

CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  board_id UUID NOT NULL REFERENCES boards(id) ON DELETE CASCADE,
  layer_id UUID REFERENCES layers(id) ON DELETE SET NULL,
  section TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  assignee_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  reporter_id UUID NOT NULL REFERENCES auth.users(id),
  priority TEXT CHECK (priority IN ('lowest', 'low', 'medium', 'high', 'highest')),
  status TEXT NOT NULL,
  task_number INTEGER NOT NULL,
  sort_order REAL NOT NULL,
  due_date DATE,
  completed_at TIMESTAMPTZ,
  estimated_hours DECIMAL(5,2),
  actual_hours DECIMAL(5,2),
  tags TEXT[],
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),

  UNIQUE(board_id, task_number)
);

Key Fields:

section: Workflow column (e.g., "To Do", "In Progress", "Done")
title: Task name (required)
description: Detailed task information (Markdown supported)
assignee_id: Assigned user (optional)
reporter_id: Task creator
priority: Urgency level
status: Current state (matches section)
task_number: Auto-incrementing board-specific number
sort_order: Position within section (REAL for between-insert flexibility)
tags: Array of label strings

Metadata JSONB Structure:

{
  "checklist": [
    {
      "id": "check1",
      "text": "Review requirements",
      "completed": true
    },
    {
      "id": "check2",
      "text": "Write tests",
      "completed": false
    }
  ],
  "custom_fields": {
    "customer": "Acme Corp",
    "environment": "production",
    "severity": "critical"
  },
  "progress": {
    "total_subtasks": 5,
    "completed_subtasks": 2,
    "percentage": 40
  }
}

Task Lifecycle States:

Created → To Do → In Progress → In Review → Done
                       ↓
                   Blocked (temporary)
                       ↓
                   In Progress (resumed)

Relationships:

Belongs To: boards, layers, users (assignee, reporter)
Has Many: comments, attachments, task_dependencies
Has Many Through: blocking_tasks, blocked_by_tasks

Indexes:

CREATE INDEX idx_tasks_board ON tasks(board_id);
CREATE INDEX idx_tasks_layer ON tasks(layer_id);
CREATE INDEX idx_tasks_section ON tasks(board_id, section);
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id);
CREATE INDEX idx_tasks_reporter ON tasks(reporter_id);
CREATE INDEX idx_tasks_status ON tasks(board_id, status);
CREATE INDEX idx_tasks_sort ON tasks(board_id, section, sort_order);
CREATE INDEX idx_tasks_due_date ON tasks(board_id, due_date) WHERE due_date IS NOT NULL;
CREATE INDEX idx_tasks_tags ON tasks USING GIN(tags);
CREATE INDEX idx_tasks_number ON tasks(board_id, task_number);

7. Task Dependencies

Purpose: Model relationships between tasks

Schema:

CREATE TABLE task_dependencies (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  depends_on_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  dependency_type TEXT NOT NULL CHECK (dependency_type IN ('FS', 'SS', 'FF', 'SF')),
  created_by UUID NOT NULL REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now(),

  UNIQUE(task_id, depends_on_id),
  CHECK (task_id != depends_on_id)
);

Dependency Types:

FS (Finish-to-Start): Task A must finish before Task B starts
SS (Start-to-Start): Task A must start before Task B starts
FF (Finish-to-Finish): Task A must finish before Task B finishes
SF (Start-to-Finish): Task A must start before Task B finishes (rare)

Example Dependency Graph:

Task A (Design)
  ↓ FS
Task B (Development) ←── SS ──→ Task C (Testing setup)
  ↓ FS                              ↓ FF
Task D (QA)                     Task E (Test execution)

Validation Rules:

No circular dependencies
Dependencies must be within same board
Cannot depend on self

Indexes:

CREATE INDEX idx_task_deps_task ON task_dependencies(task_id);
CREATE INDEX idx_task_deps_depends ON task_dependencies(depends_on_id);
CREATE INDEX idx_task_deps_type ON task_dependencies(dependency_type);

8. Comments

Purpose: Discussion and collaboration on tasks

Schema:

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  mentions UUID[],
  edited BOOLEAN DEFAULT false,
  edited_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Key Fields:

content: Comment text (Markdown supported)
mentions: Array of mentioned user IDs
edited: Whether comment has been modified
edited_at: Last edit timestamp

Mention Pattern:

@[User Name](user_id) - this is a mention

Example:
"@[Sarah Johnson](uuid-123) could you review the API integration?"

Relationships:

Belongs To: tasks, users (author)

Indexes:

CREATE INDEX idx_comments_task ON comments(task_id);
CREATE INDEX idx_comments_user ON comments(user_id);
CREATE INDEX idx_comments_created ON comments(task_id, created_at DESC);
CREATE INDEX idx_comments_mentions ON comments USING GIN(mentions);

9. Attachments

Purpose: Files associated with tasks

Schema:

CREATE TABLE attachments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  file_name TEXT NOT NULL,
  file_path TEXT NOT NULL,
  file_size BIGINT NOT NULL,
  mime_type TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

Key Fields:

file_name: Original filename
file_path: File storage path
file_size: Size in bytes
mime_type: File type (e.g., "image/png", "application/pdf")

Storage Path Structure:

organizations/{org_id}/boards/{board_id}/tasks/{task_id}/{file_id}-{filename}

Example:
organizations/abc-123/boards/def-456/tasks/ghi-789/attach-001-screenshot.png

Relationships:

Belongs To: tasks, users (uploader)

Indexes:

CREATE INDEX idx_attachments_task ON attachments(task_id);
CREATE INDEX idx_attachments_user ON attachments(user_id);

Data Relationships Diagram

┌─────────────────┐
│  Organizations  │
└────────┬────────┘
         │ 1:N
    ┌────┴────┐
    ↓         ↓
┌────────┐  ┌──────────────────┐
│ Boards │  │ Org Members      │
└───┬────┘  └──────────────────┘
    │ 1:N
    ├─────────────┐
    ↓             ↓
┌────────┐   ┌──────────────┐
│ Layers │   │ Board Members│
└───┬────┘   └──────────────┘
    │ 1:N
    ↓
┌────────────────┐
│     Tasks      │
└───┬────────┬───┘
    │ 1:N    │ 1:N
    ↓        ↓
┌──────────┐ ┌──────────────────┐
│Comments  │ │   Attachments    │
└──────────┘ └──────────────────┘

┌──────────────────────┐
│  Task Dependencies   │
│  (Self-referencing)  │
└──────────────────────┘

Data Access Rules

All tables use access rules for data security.

Organizations Access Rules

-- Users can only see organizations they're members of
CREATE POLICY "Users can view their organizations"
  ON organizations FOR SELECT
  USING (
    id IN (
      SELECT organization_id
      FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

-- Only organization owners can update
CREATE POLICY "Owners can update their organizations"
  ON organizations FOR UPDATE
  USING (owner_id = auth.uid());

Boards Access Rules

-- Users can see boards in their organizations or boards they're explicitly members of
CREATE POLICY "Users can view accessible boards"
  ON boards FOR SELECT
  USING (
    organization_id IN (
      SELECT organization_id
      FROM organization_members
      WHERE user_id = auth.uid()
    )
    OR
    id IN (
      SELECT board_id
      FROM board_members
      WHERE user_id = auth.uid()
    )
  );

-- Board owners and org admins can update boards
CREATE POLICY "Authorized users can update boards"
  ON boards FOR UPDATE
  USING (
    created_by = auth.uid()
    OR
    organization_id IN (
      SELECT organization_id
      FROM organization_members
      WHERE user_id = auth.uid()
        AND role IN ('owner', 'admin')
    )
  );

Tasks Access Rules

-- Users can see tasks on boards they have access to
CREATE POLICY "Users can view tasks on accessible boards"
  ON tasks FOR SELECT
  USING (
    board_id IN (
      SELECT id FROM boards
      WHERE organization_id IN (
        SELECT organization_id
        FROM organization_members
        WHERE user_id = auth.uid()
      )
      OR id IN (
        SELECT board_id
        FROM board_members
        WHERE user_id = auth.uid()
      )
    )
  );

-- Users with editor role or higher can create tasks
CREATE POLICY "Editors can create tasks"
  ON tasks FOR INSERT
  WITH CHECK (
    board_id IN (
      SELECT board_id
      FROM board_members
      WHERE user_id = auth.uid()
        AND role IN ('owner', 'editor')
    )
  );

-- Users with editor role can update tasks
CREATE POLICY "Editors can update tasks"
  ON tasks FOR UPDATE
  USING (
    board_id IN (
      SELECT board_id
      FROM board_members
      WHERE user_id = auth.uid()
        AND role IN ('owner', 'editor')
    )
  );

Materialized Views

For performance optimization on common queries:

Board Summary View

CREATE MATERIALIZED VIEW board_summaries AS
SELECT
  b.id AS board_id,
  b.name AS board_name,
  COUNT(DISTINCT t.id) AS total_tasks,
  COUNT(DISTINCT t.id) FILTER (WHERE t.section = 'Done') AS completed_tasks,
  COUNT(DISTINCT t.id) FILTER (WHERE t.section != 'Done') AS active_tasks,
  COUNT(DISTINCT l.id) AS total_layers,
  COUNT(DISTINCT bm.user_id) AS member_count,
  MAX(t.updated_at) AS last_activity
FROM boards b
LEFT JOIN tasks t ON b.id = t.board_id
LEFT JOIN layers l ON b.id = l.board_id
LEFT JOIN board_members bm ON b.id = bm.board_id
WHERE b.archived = false
GROUP BY b.id, b.name;

CREATE INDEX idx_board_summaries_id ON board_summaries(board_id);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY board_summaries;

Task Activity View

CREATE MATERIALIZED VIEW task_activity_summary AS
SELECT
  t.id AS task_id,
  t.board_id,
  t.title,
  COUNT(DISTINCT c.id) AS comment_count,
  COUNT(DISTINCT a.id) AS attachment_count,
  COUNT(DISTINCT td.id) AS dependency_count,
  MAX(c.created_at) AS last_comment_at,
  COALESCE(
    EXTRACT(EPOCH FROM (t.completed_at - t.created_at)) / 86400,
    EXTRACT(EPOCH FROM (NOW() - t.created_at)) / 86400
  ) AS age_days
FROM tasks t
LEFT JOIN comments c ON t.id = c.task_id
LEFT JOIN attachments a ON t.id = a.task_id
LEFT JOIN task_dependencies td ON t.id = td.task_id
GROUP BY t.id, t.board_id, t.title, t.completed_at, t.created_at;

CREATE INDEX idx_task_activity_board ON task_activity_summary(board_id);

Real-Time Subscriptions

Waymaker uses real-time subscriptions for live updates.

Subscription Patterns

Board-Level Updates:

// Subscribe to all task changes on a board
supabase
  .channel(`board:${boardId}`)
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'tasks',
      filter: `board_id=eq.${boardId}`
    },
    (payload) => {
      handleTaskUpdate(payload);
    }
  )
  .subscribe();

Task-Level Updates:

// Subscribe to comments on a specific task
supabase
  .channel(`task:${taskId}:comments`)
  .on(
    'postgres_changes',
    {
      event: 'INSERT',
      schema: 'public',
      table: 'comments',
      filter: `task_id=eq.${taskId}`
    },
    (payload) => {
      handleNewComment(payload.new);
    }
  )
  .subscribe();

Presence (Who's Online):

// Track users viewing a board
const channel = supabase.channel(`board:${boardId}:presence`);

channel
  .on('presence', { event: 'sync' }, () => {
    const users = channel.presenceState();
    updateOnlineUsers(users);
  })
  .subscribe(async (status) => {
    if (status === 'SUBSCRIBED') {
      await channel.track({
        user_id: currentUserId,
        online_at: new Date().toISOString(),
      });
    }
  });

Data Integrity Constraints

Referential Integrity

All foreign keys use appropriate ON DELETE actions:

CASCADE: Delete dependent records (e.g., delete board → delete all tasks)
SET NULL: Nullify reference (e.g., delete user → set task.assignee_id to null)
RESTRICT: Prevent deletion if dependents exist (default)

Check Constraints

Enforce data validity at the database level:

-- Priority must be valid value
ALTER TABLE tasks ADD CONSTRAINT valid_priority
  CHECK (priority IN ('lowest', 'low', 'medium', 'high', 'highest'));

-- Dates must be logical
ALTER TABLE layers ADD CONSTRAINT logical_dates
  CHECK (end_date IS NULL OR end_date >= start_date);

-- Sort order must be positive
ALTER TABLE tasks ADD CONSTRAINT positive_sort_order
  CHECK (sort_order > 0);

Unique Constraints

Prevent duplicate data:

-- One membership per user per organization
ALTER TABLE organization_members
  ADD CONSTRAINT unique_org_user
  UNIQUE (organization_id, user_id);

-- Unique task numbers per board
ALTER TABLE tasks
  ADD CONSTRAINT unique_board_task_number
  UNIQUE (board_id, task_number);

Performance Considerations

Query Optimization

Use Indexes Wisely:

Index foreign keys for joins
Index commonly filtered fields (status, section, assignee)
Use partial indexes for filtered queries (e.g., WHERE archived = false)

Avoid N+1 Queries:

// Bad: N+1 query
const tasks = await getTasks(boardId);
for (const task of tasks) {
  const comments = await getComments(task.id); // N queries
}

// Good: Single query with join
const tasksWithComments = await getTasksWithComments(boardId);

Use Pagination:

-- Cursor-based pagination (efficient for large datasets)
SELECT * FROM tasks
WHERE board_id = $1
  AND sort_order > $2
ORDER BY sort_order
LIMIT 50;

Denormalization

Strategic denormalization improves read performance:

Task Count on Layers:

-- Store computed count for fast access
ALTER TABLE layers ADD COLUMN task_count INTEGER DEFAULT 0;

-- Update via trigger
CREATE OR REPLACE FUNCTION update_layer_task_count()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE layers
  SET task_count = (
    SELECT COUNT(*) FROM tasks WHERE layer_id = NEW.layer_id
  )
  WHERE id = NEW.layer_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER task_count_trigger
AFTER INSERT OR UPDATE OR DELETE ON tasks
FOR EACH ROW EXECUTE FUNCTION update_layer_task_count();

Best Practices

For Application Developers

✅ Use transactions for multi-table operations ✅ Validate data before database operations ✅ Handle null values appropriately ✅ Use indexes for common query patterns ✅ Paginate large result sets ✅ Subscribe to real-time only for active views ✅ Clean up subscriptions when components unmount

For Database Queries

✅ Filter early in WHERE clauses ✅ Select only needed columns (avoid SELECT *) ✅ Use appropriate join types (INNER vs LEFT) ✅ Leverage indexes in ORDER BY and WHERE ✅ Batch operations when possible ✅ Use prepared statements to prevent SQL injection

For Data Modeling

✅ Normalize appropriately (balance normal forms with performance) ✅ Use meaningful names for tables and columns ✅ Document JSONB structures in code comments ✅ Version your schema with migration files ✅ Test access rules thoroughly ✅ Monitor query performance and optimize as needed

Conclusion

Waymaker's taskboard data model provides:

Hierarchical organization from orgs to tasks
Flexible schema with JSONB for extensibility
Strong security via database-level access rules
Real-time capabilities for collaboration
Performance optimization through indexing and materialized views

Understanding this data model enables you to build powerful integrations, custom reports, and advanced features on top of Waymaker's taskboard platform.

API & Webhooks - Programmatic access to taskboard data
Security & Compliance - Security features and policies
Interface Overview - Visual tour of taskboard UI

Last updated: January 10, 2025

Taskboard Data Model & Architecture

Taskboard Data Model & Architecture

Architecture Overview

Key Architectural Principles

Core Entities

1. Organizations

2. Organization Members

3. Boards

4. Board Members

5. Layers

6. Tasks

7. Task Dependencies

8. Comments

9. Attachments

Data Relationships Diagram

Data Access Rules

Organizations Access Rules

Boards Access Rules

Tasks Access Rules

Materialized Views

Board Summary View

Task Activity View

Real-Time Subscriptions

Subscription Patterns

Data Integrity Constraints

Referential Integrity

Check Constraints

Unique Constraints

Performance Considerations

Query Optimization

Denormalization

Best Practices

For Application Developers

For Database Queries

For Data Modeling

Conclusion

Related Resources