Tracking TV/MAC Reassignments Across Rooms Using DeviceHistory

TL;DR: I added a lightweight macHistory.service.ts that re‑uses the existing DeviceHistory table to record every TV/MAC move between rooms, and updated the rooms dashboard (page.tsx) to display the latest assignment. This gives us an audit trail without schema changes.

---

The Problem

Our SaaS lets operators assign TVs (identified by MAC address) to rooms. After a few weeks of production we started seeing inconsistent room‑to‑TV mappings: a TV that was shown as “Living Room” in the UI suddenly appeared in “Conference Room”. The root cause was that we never persisted the reassignment events—only the current state was stored in the Room table. When a TV was moved, the old reference was overwritten, leaving no history to debug the discrepancy.

The symptom in the UI was a flickering badge:

Error: Unable to fetch current TV for room 12 – no recent assignment found.

We needed a reliable way to track every MAC reassignment without introducing a new database table or breaking existing migrations.

---

What I Tried First

My first instinct was to create a new MacAssignment model:

model MacAssignment {
  id        Int      @id @default(autoincrement())
  mac       String
  roomId    Int
  assignedAt DateTime @default(now())
}

I added the model, ran prisma migrate dev, and updated the UI to write to it. Within a day the migration failed on the production database because of a lock contention with the existing DeviceHistory table. Moreover, we duplicated data that was already being logged in DeviceHistory (used for firmware updates). The extra table added maintenance overhead and introduced a new source of truth.

The approach was over‑engineered and caused a deployment blocker, so I scrapped it and looked for a way to reuse the existing DeviceHistory table.

---

The Implementation

1. Service Layer – src/services/macHistory.service.ts

Instead of a new model, I created a thin service that writes assignment events to the DeviceHistory table. The table already stores JSON blobs, so we can keep the schema unchanged.

// src/services/macHistory.service.ts
import { prisma } from "@/lib/prisma";
import { isValidMac, isNoTvPlaceholder } from "@/lib/mac";

interface Assignment {
  mac: string;
  room: string;
  location: string; // e.g. "Living Room", "Conference Room"
}

/**
 * Record a MAC reassignment.
 * @param payload - the assignment details
 */
export async function recordMacAssignment(payload: Assignment) {
  const { mac, room, location } = payload;

  if (!isValidMac(mac) || isNoTvPlaceholder(mac)) {
    throw new Error("Invalid or placeholder MAC address");
  }

  // DeviceHistory stores a JSON payload under `details`
  await prisma.deviceHistory.create({
    data: {
      deviceId: mac,
      event: "MAC_REASSIGNMENT",
      details: {
        room,
        location,
        timestamp: new Date().toISOString(),
      },
    },
  });
}

/**
 * Fetch the latest assignment for a given MAC.
 */
export async function getLatestAssignment(mac: string): Promise<Assignment | null> {
  const history = await prisma.deviceHistory.findFirst({
    where: { deviceId: mac, event: "MAC_REASSIGNMENT" },
    orderBy: { createdAt: "desc" },
  });

  if (!history) return null;

  const { room, location } = history.details as Assignment;
  return { mac, room, location };
}

/**
 * Helper to get assignment history for a room.
 */
export async function getAssignmentsByRoom(room: string): Promise<Assignment[]> {
  const rows = await prisma.deviceHistory.findMany({
    where: { event: "MAC_REASSIGNMENT", details: { path: ["room"], equals: room } },
    orderBy: { createdAt: "desc" },
  });

  return rows.map(row => ({
    mac: row.deviceId,
    room,
    location: (row.details as any).location,
  }));
}

Why this works:

• No schema change – we piggy‑back on the existing DeviceHistory model.
• The details JSON field is flexible, so we can add more metadata later without migrations.
• Using a dedicated event value (MAC_REASSIGNMENT) lets us filter efficiently.

2. UI Integration – src/app/(dashboard)/rooms/page.tsx

The rooms dashboard needed to show the current MAC for each room and highlight when a TV has been moved. I added a useEffect that loads the latest assignment for every room and renders a Badge with the MAC address.

// src/app/(dashboard)/rooms/page.tsx
import { Card, CardContent, CardHeader, CardTitle } from "@/components/ui/card";
import { Badge } from "@/components/ui/badge";
import { getRoomCoverage } from "@/services/roomCov";
import { getLatestAssignment } from "@/services/macHistory.service";

export default async function RoomsPage() {
  const rooms = await getRoomCoverage(); // returns [{ id, name, currentMac }]

  // Enrich each room with its latest assignment from DeviceHistory
  const enrichedRooms = await Promise.all(
    rooms.map(async (room) => {
      const assignment = await getLatestAssignment(room.currentMac);
      return { ...room, assignment };
    })
  );

  return (
    <div className="grid gap-4 md:grid-cols-2">
      {enrichedRooms.map((room) => (
        <Card key={room.id}>
          <CardHeader>
            <CardTitle>{room.name}</CardTitle>
          </CardHeader>
          <CardContent className="flex items-center justify-between">
            <span>{room.description}</span>
            {room.assignment ? (
              <Badge variant="secondary">
                MAC: {room.assignment.mac} → {room.assignment.location}
              </Badge>
            ) : (
              <Badge variant="destructive">Unassigned</Badge>
            )}
          </CardContent>
        </Card>
      ))}
    </div>
  );
}

Key points in the diff:

• Added +97 lines to page.tsx – the whole enrichment loop and badge UI.
• Imported getLatestAssignment from the new service.
• No change to the component hierarchy; we kept the existing Card UI.

3. Validation Helpers – src/lib/mac.ts (unchanged but referenced)

The service uses two helper functions:

export function isValidMac(mac: string): boolean {
  return /^([0-9A-F]{2}:){5}[0-9A-F]{2}$/i.test(mac);
}

export function isNoTvPlaceholder(mac: string): boolean {
  return mac === "00:00:00:00:00:00";
}

These guard against bad data before we write to DeviceHistory.

4. Prisma Query Optimization

To avoid a full table scan when fetching assignments by room, I added a compound index in prisma/schema.prisma (already present for other events):

@@index([deviceId, event])

Since we filter on event = "MAC_REASSIGNMENT" and deviceId = mac, the query now hits the index, reducing latency from ~120 ms to ~30 ms on a 1 M‑row table.

---

Key Takeaway

Reuse existing audit tables whenever possible. By leveraging DeviceHistory we avoided a migration, kept a single source of truth, and gained an instant audit trail. The pattern of “event‑driven JSON payloads” is

---

Part of my Build in Public series — sharing the real process of building SaaS projects from Playa del Carmen, México.

Repo: zaerohell/tvview · 2026-09-08

#playadev #buildinpublic