Recently one of the projects I've been working on at work is an asset management system. I had decided that a helpful feature to have was log events, this way when a user performs any actions there's a level of accountability.

Below is the function used to create logs and also the Prisma Model. The deviceId and groupId are nullable that way if the log is device related we can leave off the groupId.

await logService.create({
  deviceId: device.id,
  groupId: groupResult.id,
  content: `${user?.name} added asset ${device.brand} ${
    device?.serialNumber
      ? `with serial number ${device?.serialNumber}`
      : `with id ${device?.id}`
  } to group ${groupResult.name} with id ${groupResult.id}.`,
  metadata: {
    actionPerformedBy: user?.email,
    action: "update",
  },
});

model Log {
  id        Int      @id @default(autoincrement())
  device    Device?  @relation(fields: [deviceId], references: [id], onDelete: Cascade)
  group     Group?   @relation(fields: [groupId], references: [id])
  metadata  Json
  content   String   @db.LongText
  createdAt DateTime @default(now())
  deviceId  Int?
  groupId   Int?

  @@fulltext([content])
}

At first this seemed okay, I've got the content column for writing the text log. Great, until its not.

Here are some issues I started to encounter:

• [ ] I was not leaning into the power of the database.
• [ ] Having to put the device id in the string which isn't very helpful to the end user.
• [ ] The content string isn't dynamic, once its set that's it.
• [ ] I'm unable to generate helpful links on the client side.

I needed to advantage of the underlying database and the Prisma ORM. We already have the device and group IDs, we can use them to perform a Prisma include to get the relational data for groups and devices.

async function create(data) {
  return db.log.create({
    data,
    include: {
      device: {
        select: {
          id: true,
          brand: true,
          location: true,
          serialNumber: true,
          status: true,
          group: true,
        },
      },
      group: {
        select: {
          id: true,
          name: true,
          location: true,
          assignee: true,
          assigneeEmail: true,
        },
      },
    },
  });
}

Great we are taking advantage of the tooling, next we need get rid of the hardcoded log content. We can start by updating the log model to accept the id of the user performing the action, add an actionType enum column that describes the possible actions. We're keeping the metadata json field as a store for arbitrary data, such as fields that updated and their previous values. The updated schema can be seen below, remember to run the migration command npx prisma migrate.

Note: By dropping the content field we lose the @@fulltext directive for search, however we can make up for it through the use of filters. We added "OTHER" to the enum because actionType is a must-have field. However, since the logs aren't coming from user submissions, and I always supply the correct action type, "OTHER" is basically there as a stand-in.

model Log {
  id         Int       @id @default(autoincrement())
  device     Device?   @relation(fields: [deviceId], references: [id], onDelete: Cascade)
  group      Group?    @relation(fields: [groupId], references: [id])
  user       User      @relation(fields: [userId], references: [id])
  createdAt  DateTime  @default(now())
  actionType LogAction @default(OTHER)
  metadata   Json
  deviceId   Int?
  groupId    Int?
  userId     Int
}

enum LogAction {
  CREATE_DEVICE
  UPDATE_DEVICE
  CREATE_GROUP
  UPDATE_GROUP
  ADD_DEVICE_TO_GROUP
  REMOVE_DEVICE_FROM_GROUP
  OTHER
}

Now creating logs are much easier and the data is always up to date even if a device or group detail changes. We also update our crud function to have the new user include, selecting only the fields we need. I suppose when you think about it this should have been the first approach, development is an iterative approach and its best to plan out and also encounter mistakes in this stage.

await logService.create({
  deviceId: device.id,
  userId: user.id,
  actionType: "CREATE_DEVICE",
});

await logService.create({
  groupId: group.id,
  userId: user.id,
  actionType: "CREATE_GROUP",
});

await logService.create({
  deviceId: device.id,
  groupId: group.id,
  userId: user.id,
  actionType: "ADD_DEVICE_TO_GROUP",
});

async function create(data) {
  return db.log.create({
    data,
    include: {
      device: {
        select: {
          id: true,
          brand: true,
          location: true,
          serialNumber: true,
          status: true,
          group: true,
        },
      },
      group: {
        select: {
          id: true,
          name: true,
          location: true,
          assignee: true,
          assigneeEmail: true,
        },
      },
      user: {
        select: {
          id: true,
          name: true,
        },
      },
    },
  });
}

• [x] Use the tools provided to us by the database and ORM.
• [x] Relations remove the need for placing the id in a content string.
• [x] We can generate dynamic content from the log data on the fly.
• [x] We can easily use the actionType and other columns to dynamically create the right links with the use of react on the client side.

Note I did consider using Prisma computed fields for content, however there are some limitations such as accessing computed fields on includes relations.