Tracking change history (snapshot) of tables with one-to-many relationships?

[echo jolt]

Hello! We're using Prisma in a project and a feature request I've been given is to implement a way to track changes made to records in one of our models. This is in order to create a "Google-Docs-like" experience, in terms of giving users the ability to track changes to Projects, and even rename "historical" versions.

I've researched this a bit and have come across the "history table" pattern, among others, as well as temporal tables, which I understand Prisma doesn't currently support. So I'm obviously leaning towards using a history table, but I'm a fuzzy on what this would look like using a Prisma schema.

The "Project" model has several one-to-many relationships, so I'm not sure if we need a history table for each of those relationships, or one single history table. In our application a user should be able to view a list of historical versions of Projects.

I've seen a handful of methods to achieve this but I'm curious about what others might think about this using Prisma.

Thanks!

Additionally, there may be a need for users to be able to "rollback" a Project record to one of these historical versions.

[echo jolt]

Bump.

[marble cypress]

Hi You could do something like that:

model ProjectHistory {
 id         Int     @id @default(autoincrement())
 projectId  Int
 name       String
 description String?
 createdAt  DateTime
 updatedAt  DateTime
 project    Project @relation(fields: [projectId], references: [id])
}