Build an OKR Tracking System

// prisma/schema.prisma
generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "postgresql"
}

model User {
  id    String @id @default(cuid())
  name  String
  email String @unique
}

model Cycle {
  id         String      @id @default(cuid())
  name       String      // "Q3 2026"
  startDate  DateTime
  endDate    DateTime
  status     String      @default("ACTIVE") // DRAFT | ACTIVE | COMPLETED
  objectives Objective[]
}

model Objective {
  id         String      @id @default(cuid())
  title      String
  level      String      // COMPANY | TEAM | INDIVIDUAL
  ownerId    String      // accountable user (the team lead for team objectives)
  ownerName  String      // denormalized for display
  cycleId    String
  cycle      Cycle       @relation(fields: [cycleId], references: [id])
  parentId   String?     // links a team objective to a company objective
  parent     Objective?  @relation("alignment", fields: [parentId], references: [id])
  children   Objective[] @relation("alignment")
  keyResults KeyResult[]
  status     Status      @default(ON_TRACK)
}

model KeyResult {
  id           String     @id @default(cuid())
  title        String
  startValue   Float      @default(0)
  targetValue  Float
  currentValue Float      @default(0)
  unit         String     @default("%")
  objectiveId  String
  objective    Objective  @relation(fields: [objectiveId], references: [id])
  updates      KRUpdate[]
  status       Status     @default(ON_TRACK)
}

model KRUpdate {
  id            String    @id @default(cuid())
  keyResultId   String
  keyResult     KeyResult @relation(fields: [keyResultId], references: [id])
  previousValue Float
  newValue      Float
  note          String?
  updatedById   String
  createdAt     DateTime  @default(now())
}

enum Status {
  ON_TRACK
  AT_RISK
  OFF_TRACK
  COMPLETED
  DROPPED // key results only
}

// src/lib/prisma.ts — Prisma 7 requires a driver adapter.
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "@/generated/prisma/client";

export const prisma = new PrismaClient({
  adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }),
});

// src/app/api/key-results/[id]/update/route.ts — Update KR value and compute status.
import { prisma } from "@/lib/prisma";
import { currentUserId } from "@/lib/auth"; // your own session helper

export async function POST(request: Request, { params }: { params: Promise<{ id: string }> }) {
  const { id } = await params;
  const userId = await currentUserId();
  if (!userId) return Response.json({ error: "Unauthorized" }, { status: 401 });

  const { newValue, note } = await request.json();
  if (!Number.isFinite(newValue)) return Response.json({ error: "newValue must be a number" }, { status: 400 });

  const kr = await prisma.keyResult.findUniqueOrThrow({
    where: { id },
    include: { objective: { include: { cycle: true } } },
  });

  const range = kr.targetValue - kr.startValue;
  const progress = range !== 0 ? ((newValue - kr.startValue) / range) * 100 : 0;

  const { startDate, endDate } = kr.objective.cycle;
  const elapsed = (Date.now() - startDate.getTime()) / (endDate.getTime() - startDate.getTime());
  const expectedProgress = Math.min(Math.max(elapsed, 0), 1) * 100;

  // Below 70% of the expected pace is at risk, below 40% is off track
  const ratio = expectedProgress > 0 ? progress / expectedProgress : 1;
  const status =
    progress >= 100 ? "COMPLETED" : ratio >= 0.7 ? "ON_TRACK" : ratio >= 0.4 ? "AT_RISK" : "OFF_TRACK";

  // One transaction, so the history never disagrees with the value
  const updated = await prisma.$transaction(async (tx) => {
    await tx.kRUpdate.create({
      data: { keyResultId: id, previousValue: kr.currentValue, newValue, note, updatedById: userId },
    });
    const result = await tx.keyResult.update({ where: { id }, data: { currentValue: newValue, status } });

    const sibs = (await tx.keyResult.findMany({
      where: { objectiveId: kr.objectiveId, status: { not: "DROPPED" } },
      select: { status: true },
    })).map((s) => s.status);
    const worst =
      sibs.includes("OFF_TRACK") ? "OFF_TRACK" :
      sibs.includes("AT_RISK") ? "AT_RISK" :
      sibs.every((s) => s === "COMPLETED") ? "COMPLETED" : "ON_TRACK";
    await tx.objective.update({ where: { id: kr.objectiveId }, data: { status: worst } });
    return result;
  });

  return Response.json({ ...updated, progress: +progress.toFixed(1), expectedProgress: +expectedProgress.toFixed(1) });
}

// src/lib/checkins.ts — Send weekly OKR check-in emails to KR owners.
import { Resend } from "resend";
import { prisma } from "@/lib/prisma";

const resend = new Resend(process.env.RESEND_API_KEY);

export async function sendWeeklyCheckins() {
  const cycle = await prisma.cycle.findFirst({
    where: { status: "ACTIVE" },
    include: { objectives: { where: { status: { not: "COMPLETED" } }, include: { keyResults: true } } },
  });
  if (!cycle) return;

  const byOwner = new Map<string, (typeof cycle.objectives)[number]["keyResults"]>();
  for (const obj of cycle.objectives) {
    byOwner.set(obj.ownerId, [...(byOwner.get(obj.ownerId) ?? []), ...obj.keyResults]);
  }

  const owners = await prisma.user.findMany({ where: { id: { in: [...byOwner.keys()] } } });

  const emails = owners.map((owner) => {
    const krs = (byOwner.get(owner.id) ?? []).filter((kr) => kr.status !== "DROPPED");
    return {
      from: "OKRs <okrs@yourcompany.com>", // a sender on a domain verified in Resend
      to: [owner.email],
      subject: `Weekly OKR check-in: ${cycle.name}`,
      text: [
        `Weekly check-in: ${cycle.name}`,
        ...krs.map((kr) => `- ${kr.title}: ${kr.currentValue}/${kr.targetValue} ${kr.unit}\n  ${process.env.NEXT_PUBLIC_APP_URL}/kr/${kr.id}/update`),
        "What progress did you make? What is blocking you?",
      ].join("\n\n"),
    };
  });

  for (let i = 0; i < emails.length; i += 100) {
    const { error } = await resend.batch.send(emails.slice(i, i + 100));
    if (error) throw new Error(`Resend batch failed: ${error.message}`);
  }
}

// src/app/api/dashboard/health/route.ts — Company OKR health summary.
import { prisma } from "@/lib/prisma";

export async function GET() {
  const cycle = await prisma.cycle.findFirst({ where: { status: "ACTIVE" } });
  if (!cycle) return Response.json({ error: "No active cycle" }, { status: 404 });

  const [byStatus, atRisk] = await Promise.all([
    prisma.objective.groupBy({ by: ["status"], where: { cycleId: cycle.id }, _count: true }),
    prisma.objective.findMany({ where: { cycleId: cycle.id, status: { in: ["AT_RISK", "OFF_TRACK"] } } }),
  ]);

  return Response.json({
    cycleName: cycle.name,
    objectivesByStatus: Object.fromEntries(byStatus.map((g) => [g.status, g._count])),
    atRiskObjectives: atRisk,
  });
}

// src/lib/retrospective.ts — Generate and email the end-of-quarter retrospective.
import { Resend } from "resend";
import { prisma } from "@/lib/prisma";

const resend = new Resend(process.env.RESEND_API_KEY);

export async function sendQuarterlyRetro(cycleId: string, leadershipEmails: string[]) {
  const cycle = await prisma.cycle.findUniqueOrThrow({
    where: { id: cycleId },
    include: { objectives: { include: { keyResults: true } } },
  });
  const krs = cycle.objectives.flatMap((o) => o.keyResults).filter((kr) => kr.status !== "DROPPED");
  const done = krs.filter((kr) => kr.status === "COMPLETED");
  const missed = krs.filter((kr) => kr.status !== "COMPLETED");
  const rate = krs.length ? Math.round((done.length / krs.length) * 100) : 0;
  const list = (items: string[]) => items.map((i) => `- ${i}`).join("\n");

  const { error } = await resend.emails.send({
    from: "OKRs <okrs@yourcompany.com>",
    to: leadershipEmails,
    subject: `${cycle.name} retrospective: ${rate}% KR completion`,
    text: [
      `${cycle.name}: ${done.length} of ${krs.length} key results completed (${rate}%).`,
      `Achieved:\n${list(done.map((kr) => kr.title))}`,
      `Missed:\n${list(missed.map((kr) => `${kr.title}: ${kr.currentValue}/${kr.targetValue} ${kr.unit}`))}`,
      "Which misses were in our control? What do we carry forward, and what do we drop?",
    ].join("\n\n"),
  });
  if (error) throw new Error(`Resend failed: ${error.message}`);

  await prisma.cycle.update({ where: { id: cycleId }, data: { status: "COMPLETED" } });
}