Enriching Tablet Inventory with Real Serial Numbers & Internal IPs from UNICO_RM_Devices.xlsx

TL;DR: I added serialNumber and internalIp columns to the CraveDevice model, exposed them through the React inventory table, and wired a one‑off Excel import that populates the new fields. The change required a Prisma migration, TypeScript type updates, and a small UI tweak, but it eliminated the “blank serial” issue in production.

---

The Problem

Our inventory UI (InventoryTable.tsx) displayed every tablet’s Client ID, OS version, and status, but the serial number column was always null. The backend CraveDevice model had a serialNumber field, yet we never filled it because the source data lives in an Excel file (UNICO_RM_Devices.xlsx) that is only updated manually by the operations team.

When the UI tried to render row.serialNumber, TypeScript threw:

Property 'serialNumber' does not exist on type 'CraveDeviceRow'.

Even after adding the property to the interface, the database still returned null because we never persisted the values. The result was a misleading inventory view that looked broken to the support staff.

---

What I Tried First

My first instinct was to hard‑code the serial numbers in the UI component:

// InventoryTable.tsx (initial attempt)
cell: (info) => <span>{info.row.original.serialNumber ?? "N/A"}</span>

I added the field to CraveDeviceRow in use-devices-store.ts but did not update the Prisma schema or run a migration. The app compiled, but at runtime the GraphQL resolver still returned null. The UI showed “N/A” for every row, which didn’t solve the problem.

I also tried reading the Excel file directly in the component with xlsx:

import * as XLSX from "xlsx";

const wb = XLSX.readFile("/data/UNICO_RM_Devices.xlsx");
const ws = wb.Sheets[wb.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(ws);

That approach failed with a CORS error because the file lives on the server’s file system, not in the client bundle. The component crashed during SSR, and the error log was noisy:

Error: ENOENT: no such file or directory, open '/data/UNICO_RM_Devices.xlsx'

So the UI‑only fixes were dead ends. I needed a proper data‑pipeline that writes the values to the DB and then surfaces them through the existing API.

---

The Implementation

1. Extend the Prisma schema

I added two nullable fields to the CraveDevice model. The comment reminds future developers that the data comes from a manual Excel import and should never be edited directly.

// prisma/schema.prisma
model CraveDevice {
  id          Int          @id @default(autoincrement())
  clientId    String
  osVersion   String?
  status      DeviceStatus @default(Unknown)
  lastRequest DateTime?

  // Enriquecimiento manual desde el listado UNICO_RM_Devices.xlsx —
  // NUNCA se toque sin pasar por la importación controlada
  serialNumber String?   // Real hardware serial
  internalIp   String?   // Internal network IP
}

Running npx prisma migrate dev --name add-serial-ip generated the migration and updated the SQLite/Postgres schema. Prisma’s type generator automatically added the new fields to the generated CraveDevice TypeScript type.

2. Update the TypeScript interface

The hook that normalizes DB rows for the UI (use-devices-store.ts) needed to reflect the new columns:

// src/hooks/use-devices-store.ts
export interface CraveDeviceRow {
  id: number;
  clientId: string;
  osVersion: string | null;
  status: "Online" | "Offline" | "Unknown";
  lastRequest: string | null;
  serialNumber: string | null;   // <-- added
  internalIp: string | null;     // <-- added
}

Because the hook maps the Prisma result to this interface, the change was painless: the mapDevice function now spreads the raw device and passes the new fields unchanged.

3. Add columns to the inventory table

I extended the column builder in InventoryTable.tsx to render the two new fields. The UI already uses @tanstack/react-table, so I just added two more ColumnDefs:

// src/features/inventory/InventoryTable.tsx
function buildColumns(duplicateRooms: Set<string>): ColumnDef<CraveDeviceRow>[] {
  return [
    // ... existing columns
    {
      accessorKey: "serialNumber",
      header: "Serial #",
      cell: (info) => (
        <span className="font-mono">{info.getValue() ?? "—"}</span>
      ),
    },
    {
      accessorKey: "internalIp",
      header: "Internal IP",
      cell: (info) => (
        <span className="font-mono">{info.getValue() ?? "—"}</span>
      ),
    },
  ];
}

No CSS changes were required; the existing table layout handled the extra width gracefully.

4. One‑off Excel import script

I created a small Node script (scripts/import-unico.ts) that runs on the server, parses the Excel file, and upserts the data into the CraveDevice table. The script uses prisma client and xlsx:

// scripts/import-unico.ts
import { PrismaClient } from "@prisma/client";
import * as XLSX from "xlsx";
import path from "path";

const prisma = new PrismaClient();
const filePath = path.resolve(__dirname, "../data/UNICO_RM_Devices.xlsx");

async function main() {
  const wb = XLSX.readFile(filePath);
  const ws = wb.Sheets[wb.SheetNames[0]];
  const rows = XLSX.utils.sheet_to_json(ws) as Array<{
    clientId: string;
    serialNumber: string;
    internalIp: string;
  }>;

  for (const row of rows) {
    await prisma.craveDevice.upsert({
      where: { clientId: row.clientId },
      update: {
        serialNumber: row.serialNumber,
        internalIp: row.internalIp,
      },
      create: {
        clientId: row.clientId,
        serialNumber: row.serialNumber,
        internalIp: row.internalIp,
        status: "Unknown",
      },
    });
    console.log(`✅ ${row.clientId} enriched`);
  }
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(() => prisma.$disconnect());

Running npm run import-unico (added to package.json scripts) populated the DB for all existing devices. The script is idempotent thanks to upsert, so re‑running it after a spreadsheet update won’t create duplicates.

5. Wire the import into the dev workflow

Because the data source is static, I added a post‑install hook that checks for the existence of the Excel file and runs the import automatically in development:

json
// package.json
"scripts": {
  "dev

---

*Part of my [Build in Public](https://dev.to/zaerohell) series — sharing the real process of building SaaS projects from Playa del Carmen, México.*

*Repo: `zaerohell/craveview` · 2026-08-28*

\#playadev #buildinpublic