Add Relation Between Entities

TL;DR: Add FK column, set up relations on both sides, filter child queries by parent, add a picker in the UI.

When You Need This

You have two entities and one "belongs to" the other:

Contacts belong to Clients
Invoices belong to Projects
Tasks belong to Milestones

The parent entity has many children. The child entity has one parent.

The Checklist

#	File	What to do
1	`src/lib/db/schema/<child>.ts`	Add FK column pointing to parent
2	`src/lib/db/schema/<child>.ts`	Add `one()` relation to parent
3	`src/lib/db/schema/<parent>.ts`	Add `many()` relation to children
4	`src/features/<child>/validation.ts`	Add `parentId` to create/update schemas
5	`src/features/<child>/queries.ts`	Filter by `parentId`, join parent in detail query
6	`src/features/<child>/actions.ts`	Accept `parentId` in create, verify parent exists
7	`src/server/routers/<child>.ts`	Pass `parentId` from input to queries/actions
8	UI	Add parent picker (SearchSelect) in create/edit forms

Step 1: Add Foreign Key to Child Schema

// src/lib/db/schema/tasks.ts
import { pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core"
import { relations } from "drizzle-orm"
import { organizations } from "./auth"
import { projects } from "./projects"

export const tasks = pgTable("tasks", {
  id: uuid("id").primaryKey().defaultRandom(),
  organizationId: uuid("organization_id")
    .notNull()
    .references(() => organizations.id, { onDelete: "cascade" }),

  // ← The foreign key
  projectId: uuid("project_id")
    .notNull()
    .references(() => projects.id, { onDelete: "cascade" }),

  name: text("name").notNull(),
  // ... other fields
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
})

Choosing onDelete:

"cascade" — deleting the parent deletes all children (contacts die with client)
"set null" — deleting the parent sets FK to null (task survives without project). Use with .notNull() removed from the FK column.

Step 2: Add Relations on Both Sides

Child side — one task belongs to one project:

// src/lib/db/schema/tasks.ts (continued)
export const tasksRelations = relations(tasks, ({ one }) => ({
  project: one(projects, {
    fields: [tasks.projectId],
    references: [projects.id],
  }),
}))

Parent side — one project has many tasks:

// src/lib/db/schema/projects.ts (add to existing relations)
import { tasks } from "./tasks"

export const projectsRelations = relations(projects, ({ many }) => ({
  tasks: many(tasks),
}))

Then push to database:

npm run db:push

Step 3: Update Validation

// src/features/tasks/validation.ts
import { z } from "zod"

export const createTaskSchema = z.object({
  name: z.string().min(1, "Name is required"),
  // ... other fields
  // Note: projectId is NOT here — it's passed separately in the router
})

export const updateTaskSchema = z.object({
  projectId: z.string().uuid().optional(), // Can re-assign to different project
  name: z.string().min(1).optional(),
  // ... other fields
})

Why projectId is not in the create schema: The router passes it separately from the form data. This keeps the form schema clean and matches the URL structure (/projects/:id/tasks/new).

Step 4: Update Queries

List children filtered by parent:

// src/features/tasks/queries.ts
export async function listTasks(params: {
  organizationId: string
  projectId: string // ← Filter by parent
  page: number
  pageSize: number
}) {
  const conditions = [
    eq(tasks.organizationId, params.organizationId),
    eq(tasks.projectId, params.projectId), // ← Parent filter
  ]

  // ... standard list query with pagination
}

Get child detail with parent data:

export async function getTaskById(params: {
  organizationId: string
  taskId: string
}) {
  const rows = await db
    .select({
      task: tasks,
      projectName: projects.name, // ← Join parent for display
    })
    .from(tasks)
    .innerJoin(projects, eq(tasks.projectId, projects.id))
    .where(
      and(
        eq(tasks.organizationId, params.organizationId),
        eq(tasks.id, params.taskId)
      )
    )
    .limit(1)

  if (!rows[0]) return null

  return {
    ...mapToTask(rows[0].task),
    project: { id: rows[0].task.projectId, name: rows[0].projectName },
  }
}

Count children in parent list (for indicator badges):

// In parent's queries.ts (listProjects)
const rows = await db
  .select({
    project: projects,
    tasksCount: sql<number>`(
      SELECT count(*)::int FROM tasks
      WHERE tasks.project_id = ${projects.id}
    )`,
  })
  .from(projects)
  // ...

Step 5: Update Actions

Create — verify parent exists:

// src/features/tasks/actions.ts
export async function createTask(params: {
  organizationId: string
  projectId: string
  data: CreateTaskInput
}) {
  // Verify parent exists and belongs to this organization
  const project = await db
    .select({ id: projects.id })
    .from(projects)
    .where(
      and(
        eq(projects.organizationId, params.organizationId),
        eq(projects.id, params.projectId)
      )
    )
    .limit(1)

  if (!project[0]) {
    throw new Error("Project not found")
  }

  const [inserted] = await db
    .insert(tasks)
    .values({
      organizationId: params.organizationId,
      projectId: params.projectId,
      ...params.data,
    })
    .returning()

  return inserted
}

Update — can change parent:

export async function updateTask(params: {
  organizationId: string
  taskId: string
  data: UpdateTaskInput
}) {
  // If changing parent, verify new parent exists
  if (params.data.projectId) {
    const project = await db
      .select({ id: projects.id })
      .from(projects)
      .where(
        and(
          eq(projects.organizationId, params.organizationId),
          eq(projects.id, params.data.projectId)
        )
      )
      .limit(1)

    if (!project[0]) {
      throw new Error("Project not found")
    }
  }

  await db
    .update(tasks)
    .set({ ...params.data, updatedAt: new Date() })
    .where(
      and(
        eq(tasks.organizationId, params.organizationId),
        eq(tasks.id, params.taskId)
      )
    )
}

Step 6: Update Router

// src/server/routers/tasks.ts
export const tasksRouter = {
  listByProject: authedProcedure
    .input(z.object({
      projectId: z.string().uuid(),
      page: z.number().int().min(1).default(1),
      pageSize: z.number().int().min(1).max(100).default(20),
    }))
    .handler(async ({ input, context }) => {
      return listTasks({
        organizationId: context.organizationId,
        projectId: input.projectId,
        page: input.page,
        pageSize: input.pageSize,
      })
    }),

  create: authedProcedure
    .input(z.object({
      projectId: z.string().uuid(), // ← Separate from data
      data: createTaskSchema,
    }))
    .handler(async ({ input, context }) => {
      return createTask({
        organizationId: context.organizationId,
        projectId: input.projectId,
        data: input.data,
      })
    }),
}

Step 7: Add Parent Picker in UI

Use EditableSearchSelect to let users pick a parent entity:

// In your form or detail sheet
import { EditableSearchSelect } from "@/components/inline-edit"
import { orpcUtils } from "@/lib/orpc/client"

<EditableSearchSelect
  value={projectId}
  displayName={projectName}
  queryOptions={(q) =>
    orpcUtils.projects.search.queryOptions({
      input: { q, limit: 10 },
    })
  }
  getItemId={(item) => item.id}
  getItemLabel={(item) => item.name}
  onSave={(newId, newName) => {
    setProjectId(newId)
    setProjectName(newName)
  }}
  placeholder="Select project..."
  searchPlaceholder="Search projects..."
  emptyMessage="No projects found"
/>

For this to work, the parent entity needs a search endpoint in its router that accepts { q: string, limit: number } and returns { id, name }[].

Step 8: Show Children in Parent Detail Page

Use an embedded DataTable inside a tab on the parent's detail page:

// In project detail page — Contacts tab
const { data, isLoading } = useQuery(
  orpcUtils.tasks.listByProject.queryOptions({
    input: { projectId: project.id, page, pageSize },
  })
)

// Render with embedded DataTable
<SectionCard title={`Tasks (${data?.meta.rowCount ?? 0})`}>
  <DataTable table={table} isLoading={isLoading} variant="embedded" />
  <DataTablePagination table={table} />
</SectionCard>

Common Mistakes

Don't put organizationId in input:

// ❌ Wrong
.input(z.object({ organizationId: z.string(), projectId: z.string() }))

// ✅ Correct — organizationId comes from context
.input(z.object({ projectId: z.string().uuid() }))
// Then in handler: context.organizationId

Don't skip parent verification:

// ❌ Wrong — trusts client input
await db.insert(tasks).values({ projectId: input.projectId, ... })

// ✅ Correct — verify parent exists in same organization
const project = await db.select(...).where(
  and(eq(projects.organizationId, context.organizationId), eq(projects.id, input.projectId))
)
if (!project[0]) throw new ORPCError("NOT_FOUND")

Don't forget onDelete:

// ❌ Wrong — orphaned records when parent deleted
projectId: uuid("project_id").references(() => projects.id)

// ✅ Correct — choose cascade or set null
projectId: uuid("project_id").references(() => projects.id, { onDelete: "cascade" })

Verify

npm run typecheck

Add New Feature — Create the entities first
Add Field to Entity — Add more fields
Add Dictionary — For lookup fields (status, type) use dictionaries instead of relations

Add Relation Between Entities

On this page