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

> Understand the Paperclip data model for goals hierarchy and goal-to-task linking. Explore how Paperclip uses a self-referencing table and join tables for flexible goal management.

- Repository: [Paperclip/paperclip](https://github.com/paperclipai/paperclip)
- Tags: internals
- Published: 2026-08-16

---

**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`](https://github.com/paperclipai/paperclip/blob/main/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

```typescript
// 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`](https://github.com/paperclipai/paperclip/blob/main/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`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/schema/project_goals.ts):

```typescript
// 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

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

```

The [`server/src/routes/goals.ts`](https://github.com/paperclipai/paperclip/blob/main/server/src/routes/goals.ts) handler processes these associations, with validation logic in [`server/src/services/goals.ts`](https://github.com/paperclipai/paperclip/blob/main/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:

```typescript
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`](https://github.com/paperclipai/paperclip/blob/main/server/src/services/goals.ts) implements recursive CTEs or multiple query batching to construct this tree efficiently. The [`ui/src/api/goals.ts`](https://github.com/paperclipai/paperclip/blob/main/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`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/schema/goals.ts) | Drizzle schema definition for the goals table with hierarchy fields |
| [`packages/db/src/schema/project_goals.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/schema/project_goals.ts) | Join table schema for project-goal associations |
| [`server/src/services/goals.ts`](https://github.com/paperclipai/paperclip/blob/main/server/src/services/goals.ts) | Business logic: CRUD, validation, hierarchy queries, status transitions |
| [`server/src/routes/goals.ts`](https://github.com/paperclipai/paperclip/blob/main/server/src/routes/goals.ts) | Express route handlers exposing `/api/goals` endpoints |
| [`ui/src/api/goals.ts`](https://github.com/paperclipai/paperclip/blob/main/ui/src/api/goals.ts) | Frontend TypeScript client for goal API consumption |
| [`docs/api/goals-and-projects.md`](https://github.com/paperclipai/paperclip/blob/main/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`](https://github.com/paperclipai/paperclip/blob/main/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`](https://github.com/paperclipai/paperclip/blob/main/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`](https://github.com/paperclipai/paperclip/blob/main/server/src/services/goals.ts) to accept a `level` parameter that constrains the recursive CTE.