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.

Related Resources

• 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