Goals Hierarchy and Goal-to-Task Linking Data Model in Paperclip

Paperclip implements a flexible three-level goals system using a single self-referencing table with parent_id relationships and join tables for project associations.

The open-source Paperclip platform models work planning through a unified goals architecture that supports arbitrary nesting of projects, goals, and tasks. This article examines the complete data model, schema definitions, and API patterns used to implement hierarchical goal management and goal-to-task linking in the paperclipai/paperclip repository.

Core Goals Table Schema

The foundation of Paperclip's hierarchy lives in packages/db/src/schema/goals.ts. This Drizzle ORM schema defines a single table that stores all three logical levels—projects, goals, and tasks—differentiated by a level column rather than separate tables.

Key Columns and Their Roles

Column	Type	Purpose
id	UUID	Primary key for all goal entities
company_id	UUID	Multi-tenant isolation boundary
title	string	Display name
description	text	Detailed context
level	enum ("project", "goal", "task")	Logical type discriminator
status	enum ("planned", "active", "completed")	Lifecycle state
parent_id	UUID (nullable, self-referencing FK)	Hierarchical parent link
owner_agent_id	UUID (nullable)	Assignment to AI or human agent
created_at / updated_at	timestamps	Audit trail

The parent_id column creates the tree structure. Any goal row can reference another row in the same table, enabling unlimited nesting depth. The level column defaults to "task" and provides semantic categorization without constraining the actual hierarchy.

Goal Hierarchy Implementation

Paperclip's nesting mechanism relies entirely on the self-referencing foreign key pattern. This design choice keeps queries simple while supporting complex tree operations.

Creating Hierarchical Goals

// Create a top-level project
const project = await api.post('/goals', {
  title: 'Launch New Marketing Campaign',
  description: 'Define strategy, create assets, and run ads.',
  level: 'project',
  status: 'planned',
  companyId: currentCompany.id,
});

// Nest a goal under the project using parentId
const goal = await api.post('/goals', {
  title: 'Develop Campaign Copy',
  description: 'Write headlines and ad copy.',
  level: 'goal',
  status: 'planned',
  parentId: project.id,  // Hierarchical link established here
  companyId: currentCompany.id,
});

// Create a task under the goal
const task = await api.post('/goals', {
  title: 'Write Facebook Ad Text',
  level: 'task',
  status: 'planned',
  parentId: goal.id,
  companyId: currentCompany.id,
});

The level field is advisory—schema validation in server/src/services/goals.ts enforces business rules, but the database permits any level value at any nesting depth. This flexibility allows edge cases like project-within-project structures when business needs demand it.

Project-to-Goal Linking via Join Table

Goals exist independently of projects. The many-to-many relationship between projects and goals is managed through packages/db/src/schema/project_goals.ts:

// project_goals.ts schema structure
{
  project_id: UUID,  // references projects.id
  goal_id: UUID,     // references goals.id
}

This separation enables goal reusability: a single milestone goal can belong to multiple projects without duplication, and goals can exist in draft state before project assignment.

Linking a Goal to a Project

await api.post('/project-goals', {
  projectId: 'a1b2c3d4-5678-90ab-cdef-0987654321fe',
  goalId: goal.id,
});

The server/src/routes/goals.ts handler processes these associations, with validation logic in server/src/services/goals.ts ensuring company-scoped access control.

Fetching Hierarchical Data

Paperclip's API supports eager loading of child goals through a query parameter pattern:

const hierarchy = await api.get(
  `/projects/${projectId}/goals?includeChildren=true`
);

// Returns nested structure:
// {
//   id: "...",
//   title: "Launch New Marketing Campaign",
//   level: "project",
//   children: [
//     {
//       id: "...",
//       title: "Develop Campaign Copy", 
//       level: "goal",
//       children: [
//         { id: "...", title: "Write Facebook Ad Text", level: "task", children: [] }
//       ]
//     }
//   ]
// }

The service layer in server/src/services/goals.ts implements recursive CTEs or multiple query batching to construct this tree efficiently. The ui/src/api/goals.ts client unwraps the nested response for React component consumption.

Status and Ownership Tracking

Each goal carries status and assignment metadata that propagate through the hierarchy:

• status: Drives workflow automation and UI state. Valid transitions are enforced in the goals service layer.
• owner_agent_id: Links to the agents table, supporting both human users and autonomous AI agents as goal owners.

These fields enable aggregate reporting—project completion percentages roll up from leaf task statuses through parent goals.

Key Source Files

Understanding the complete implementation requires examining these files in paperclipai/paperclip:

File	Responsibility
packages/db/src/schema/goals.ts	Drizzle schema definition for the goals table with hierarchy fields
packages/db/src/schema/project_goals.ts	Join table schema for project-goal associations
server/src/services/goals.ts	Business logic: CRUD, validation, hierarchy queries, status transitions
server/src/routes/goals.ts	Express route handlers exposing /api/goals endpoints
ui/src/api/goals.ts	Frontend TypeScript client for goal API consumption
docs/api/goals-and-projects.md	Public API contract documentation

Summary

• Single-table inheritance: The goals table stores projects, goals, and tasks differentiated by level.
• Self-referencing hierarchy: parent_id creates unlimited nesting depth without separate tables per level.
• Decoupled project association: The project_goals join table enables many-to-many relationships.
• Service-layer validation: server/src/services/goals.ts enforces business rules on hierarchy depth, status transitions, and access control.
• Recursive fetching: includeChildren=true parameter loads complete goal trees in one round-trip.

Frequently Asked Questions

How does Paperclip distinguish between a project, goal, and task?

The level column in the goals table uses string values ("project", "goal", "task") to semantically categorize each row. This is not enforced at the database level—parent_id relationships control actual hierarchy—so the service layer validates that business rules match intended use.

Can a goal belong to multiple projects?

Yes. The project_goals join table implements a many-to-many relationship. Inserting multiple rows with the same goal_id and different project_id values links one goal to several projects simultaneously.

What happens when I delete a parent goal?

The schema uses a standard foreign key on parent_id. Depending on migration settings, deletion either cascades to child goals or is blocked until children are reassigned. Check packages/db/src/schema/goals.ts for the specific onDelete behavior in your deployment.

How do I query all tasks under a specific project?

Use the hierarchical fetch endpoint with includeChildren=true, then filter client-side by level: "task". For server-side filtering, extend the query in server/src/services/goals.ts to accept a level parameter that constrains the recursive CTE.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}