Project Tracking Dashboard: Integrating Primavera P6 with Fastify and React

Why build your own dashboard instead of using P6 Web

Primavera P6 Professional stores everything in Oracle—or SQLite on smaller installs. P6 Web Access exists, but on mining projects like the ones I've worked on—SMCV with more than 1,200 activities across a 21-day shutdown—the standard report takes 4 to 7 minutes to render and gives you no way to cross-reference field data against the schedule. The setup I'm describing here solves that: a Fastify backend that queries the P6 database directly, and a React frontend that shows status in near real time.

This is not a P6 replacement. It's a read layer on top of it.

---

Overall architecture

┌─────────────────────────────────────────────────────┐
│  Primavera P6 Professional (Oracle 19c / SQLite)    │
└────────────────────┬────────────────────────────────┘
                     │  JDBC / node-oracledb / better-sqlite3
┌────────────────────▼────────────────────────────────┐
│  Backend  Fastify 4 + TypeScript                    │
│  • /api/projects          project list              │
│  • /api/wbs/:projId       WBS tree                  │
│  • /api/activities/:wbsId activities + SPI/CPI      │
│  • /api/resources/:actId  resource assignments      │
│  • WebSocket /ws/live     push every 60 s           │
└────────────────────┬────────────────────────────────┘
                     │  REST + WS
┌────────────────────▼────────────────────────────────┐
│  Frontend  React 18 + TypeScript + Vite 5           │
│  • Simplified Gantt (react-gantt-chart)             │
│  • S-Curve (Recharts)                               │
│  • Critical activities table                        │
│  • KPI cards: SPI, CPI, Total Float                 │
└─────────────────────────────────────────────────────┘

The backend never writes to P6. Only SELECT. That eliminates any risk of corrupting the schedule.

---

Backend: Fastify + TypeScript

Connecting to the P6 database

P6 Professional on Oracle uses the PMSYS schema by default. The key tables are TASK, PROJECT, TASKRSRC, RSRC, and PROJWBS.

// src/db/oracle.ts
import oracledb from 'oracledb';

const pool = await oracledb.createPool({
  user: process.env.P6_DB_USER,       // 'pmsys'
  password: process.env.P6_DB_PASS,
  connectString: process.env.P6_DSN,  // 'host:1521/ORCL'
  poolMin: 2,
  poolMax: 10,
  poolIncrement: 1,
});

export async function query<T>(sql: string, binds: unknown[] = []): Promise<T[]> {
  const conn = await pool.getConnection();
  try {
    const result = await conn.execute<T>(sql, binds, { outFormat: oracledb.OUT_FORMAT_OBJECT });
    return result.rows ?? [];
  } finally {
    await conn.close();
  }
}

Activities endpoint with SPI

P6 stores PHYS_COMPLETE_PCT and baseline dates in TASK. I calculate SPI on the backend so the frontend stays free of business logic.

// src/routes/activities.ts
import { FastifyInstance } from 'fastify';
import { query } from '../db/oracle';

interface Activity {
  TASK_ID: number;
  TASK_NAME: string;
  TARGET_START_DATE: Date;
  TARGET_END_DATE: Date;
  ACT_START_DATE: Date | null;
  PHYS_COMPLETE_PCT: number;
  TOTAL_FLOAT_HR_CNT: number;
  SPI: number;
}

export async function activitiesRoute(app: FastifyInstance) {
  app.get<{ Params: { wbsId: string } }>('/api/activities/:wbsId', async (req, reply) => {
    const rows = await query<Activity>(
      `SELECT
         t.TASK_ID,
         t.TASK_NAME,
         t.TARGET_START_DATE,
         t.TARGET_END_DATE,
         t.ACT_START_DATE,
         t.PHYS_COMPLETE_PCT,
         t.TOTAL_FLOAT_HR_CNT,
         ROUND(
           t.PHYS_COMPLETE_PCT /
           NULLIF(
             (SYSDATE - t.TARGET_START_DATE) /
             NULLIF(t.TARGET_END_DATE - t.TARGET_START_DATE, 0) * 100,
           0), 3
         ) AS SPI
       FROM PMSYS.TASK t
       WHERE t.WBS_ID = :wbsId
         AND t.STATUS_CODE != 'TK_NotStart'
       ORDER BY t.TARGET_START_DATE`,
      [req.params.wbsId]
    );
    return reply.send(rows);
  });
}

Field note: on projects running Oracle 12c—common on older mine-site installs—SYSDATE returns the database server time, not the application server time. Sync NTP before you deploy.

Schema validation with Zod

I use Zod at the API boundary so I'm not blindly trusting whatever Oracle hands back:

import { z } from 'zod';

export const ActivitySchema = z.object({
  TASK_ID: z.number(),
  TASK_NAME: z.string(),
  PHYS_COMPLETE_PCT: z.number().min(0).max(100),
  TOTAL_FLOAT_HR_CNT: z.number(),
  SPI: z.number().nullable(),
});

export type ActivityDTO = z.infer<typeof ActivitySchema>;

---

Frontend: React + TypeScript + Vite

Folder structure

src/
  api/          # fetching hooks (TanStack Query)
  components/
    KpiCard/
    GanttPanel/
    SCurve/
    ActivityTable/
  pages/
    ProjectDashboard.tsx
  types/        # ActivityDTO, WbsNode, etc.

Data hook with TanStack Query

// src/api/useActivities.ts
import { useQuery } from '@tanstack/react-query';
import type { ActivityDTO } from '../types';

export function useActivities(wbsId: string) {
  return useQuery<ActivityDTO[]>({
    queryKey: ['activities', wbsId],
    queryFn: async () => {
      const res = await fetch(`/api/activities/${wbsId}`);
      if (!res.ok) throw new Error('Failed to fetch activities');
      return res.json();
    },
    refetchInterval: 60_000, // refresh every 60 s
    staleTime: 30_000,
  });
}

KPI cards

KPI	P6 source	Alert threshold
SPI	PHYS_COMPLETE_PCT vs planned progress	< 0.95
CPI	ACT_COST vs TARGET_COST	< 0.90
Minimum total float	TOTAL_FLOAT_HR_CNT	< 8 h
Open critical activities	TOTAL_FLOAT_HR_CNT = 0	> 5

Color logic—green, amber, red—lives in a pure function getKpiStatus(value, thresholds) imported by every component. No business logic scattered through JSX.

S-Curve with Recharts

The S-Curve needs cumulative data by period. The backend exposes /api/scurve/:projId?period=week, which groups TARGET_COST and ACT_COST by ISO week. The frontend just renders:

<ComposedChart data={scurveData}>
  <Area dataKey="planned" stroke="#6366f1" fill="#e0e7ff" />
  <Line dataKey="actual" stroke="#dc2626" dot={false} />
  <XAxis dataKey="week" />
  <YAxis tickFormatter={(v) => `$${(v/1e6).toFixed(1)}M`} />
  <Tooltip />
</ComposedChart>

---

On-site deployment

Internet access on mining projects is restricted. The entire stack runs on a local VM (Windows Server 2019) inside the project network:

• Fastify exposed on port 3001 behind Nginx.
• React built with vite build and served as static files by the same Nginx instance.
• The P6 Oracle database sits on the same VLAN; no traffic leaves the site.

At ENGIE I ran this architecture through a 72-hour outage at a thermal power plant. The dashboard let the shift team track progress without opening P6 Professional, which requires a floating license that wasn't always available.

---

What I learned across three deployments

1. Don't calculate SPI on the frontend. P6 data has nulls and inconsistent dates. Centralize that logic in the backend and cover it with unit tests.
2. Add a health endpoint (/health) that checks the Oracle pool connection. Nginx uses it as an upstream health check.
3. A 60-second refresh interval is enough for a shutdown. Don't reach for WebSocket unless you have a genuine real-time requirement—it adds operational complexity with no real payoff.
4. Document every P6 table you touch. Oracle Primavera does not guarantee schema stability across minor versions. Going from P6 20.12 to 21.12, the PHYS_COMPLETE_PCT column changed scale on some installs.

This dashboard doesn't replace the PM. It replaces the hours the PM wastes waiting for P6 Web to load.