Optimizing Panic-Value Ranking in Documedic’s CDSS

#nestjs #react #postgres #api

Problem Statement

Documedic is a CDSS that delivers real‑time clinical decision support to physicians. In a typical workflow the clinician opens the Panic‑Value pane to see the most abnormal laboratory results for a patient. The original implementation fetched the entire patient chart, then performed the ranking in application memory. For charts that contain hundreds of lab observations, this operation added 200 ms of latency and consumed a large amount of RAM on the API server.

Solution Overview

The new feature moves the ranking logic into PostgreSQL. Using a window function, the query returns only the top N results, eliminating the need to stream the entire chart across the network and reducing the amount of data the NestJS service must process. The change is a pure performance improvement; no new business logic or external data sources are introduced.

Technical Implementation

The change is reflected in the PanicValuesService inside src/panic-values/panic-values.service.ts.

// src/panic-values/panic-values.service.ts
@Injectable()
export class PanicValuesService {
  constructor(private readonly prisma: PrismaService) {}

  /**
   * Returns the top `limit` panic‑value records for a patient.
   * Ranking is performed in SQL using a window function.
   */
  async getTopPanicValues(patientId: string, limit = 10) {
    const rows = await this.prisma.$queryRaw<unknown[]>(
      Prisma.sql`
        SELECT
          lab_id,
          value,
          unit,
          reference_range,
          ROW_NUMBER() OVER (ORDER BY ABS(value - reference_value) DESC) AS rank
        FROM
          lab_results
        WHERE
          patient_id = ${patientId}
          AND is_abnormal = true
        ORDER BY
          rank
        LIMIT ${limit}
      `,
    );
    return rows;
  }
}

The SQL uses ROW_NUMBER() to rank results by the absolute deviation from the reference value. The LIMIT clause guarantees that only the requested number of rows is materialized.

The controller simply forwards the request to the service:

// src/panic-values/panic-values.controller.ts
@UseGuards(AuthGuard)
@Get('panic-values')
async getPanicValues(@Req() req: Request, @Query('limit') limit: string) {
  const patientId = req.user.patientId;
  return this.panicValuesService.getTopPanicValues(patientId, Number(limit) || 10);
}

Front‑End Integration

On the React side, the component PanicValueTable calls the API and renders the ranked list.

// src/components/PanicValueTable.tsx
const PanicValueTable: React.FC = () => {
  const [values, setValues] = React.useState<LabResult[]>([]);

  React.useEffect(() => {
    fetch('/api/panic-values?limit=10')
      .then((res) => res.json())
      .then(setValues);
  }, []);

  return (
    <table>
      <thead>
        <tr>
          <th>Lab</th>
          <th>Value</th>
          <th>Unit</th>
          <th>Reference</th>
          <th>Rank</th>
        </tr>
      </thead>
      <tbody>
        {values.map((v) => (
          <tr key={v.lab_id}>
            <td>{v.labgrade}</td>
            <td>{v.value}</td>
            <td>{v.unit}</td>
            <td>{v.reference_range}</td>
            <td>{v.rank}</td>
          </tr>
        ))}
      </tbody>
    </table>
  );
};

The component remains unchanged in terms of UI; the only difference is that the data arriving from the server is now already sorted and trimmed.

Impact and Observations

The refactor reduces the API response size by up to 90 % for patients with large charts. Because the ranking is performed in the database, the NestJS process no longer allocates memory for the entire chart, lowering the CPU load on the server. In a recent load test the average latency for the panic‑value endpoint dropped from 240 ms to 60 ms when the patient chart contained 300 records.

This performance gain directly benefits the clinician’s workflow: the Panic‑Value pane populates almost instantly, allowing the physician to focus on decision‑making rather than waiting for data. The change aligns with Documedic’s goal of delivering a responsive, reliable clinical decision support experience.

Documedic is a NestJS/React/CDS‑based platform that integrates PostgreSQL for data storage and OpenRouter for LLM calls. The new SQL‑based ranking showcases how a targeted database optimization can improve a medical AI system’s usability for clinicians.

DEV Community