Temporal Tables (i.e., Record versioning/auditing/timetravel)

[semopz]

I have a requirement to keep track of all changes made to the data in my app.

For example, user details changes (email, phone number, name, etc...). All the old data should be kept but the app should only show the latest version. We currently keep audit logs of such things and have report pages that allow users to look through the old data. Going forward, I'd like to implement a time travel feature that would allow users to set a date filter, which would put the app in a read-only mode and show you what the app would have looked like on that date.

Here's an example (say at is a date field).

Old data

user = {
  name: "J Doe",
  email: "j.doe@notreal.com",
  id: 1,
  at: 2019
}

posts = [
  {
    title: "This is new",
    author: 1,
    category: 1,
    at: 2019
  }, 
  {
    title: "Everything is awesome",
    author: 1,
    category: 2,
    at: 2019
  }
]

categories = [
  {
    name: "General",
    id: 1,
    at: 2019
  },
  {
    name: "Other",
    id: 2,
    at: 2019
  }
]

New Data

user = {
  name: "J Doe",
  email: "me@doe.com", //changed
  id: 1,
  at: 2020
}

newPosts = [
   ...posts,
  { // new post
    title: "Learned so much",
    author: 1,
    category: 2,
    at: 2020
  }
]

categories = [
  {
    name: "General",
    id: 1,
    at: 2019
  },
  {
    name: "Useful stuff", // changed
    id: 2,
    at: 2020
  }
]

How would it work

• If we logged in today, we'd see that J Doe has three posts (one tagged as General and two as Useful stuff)
• If the date filter is changed to 2019 (i.e., what "today" looked like a year ago), J Doe should have an old email address, only two posts and they'd be tagged as General and Other respectively
• Notice that both past and present Post with id 2 are identical (i.e., it never changed since it was created), BUT the category changes depending on whether you're date filter is set to 2019 or 2020

Ideas?

I think that PostgreSQL triggers might be the way to go but not sure where to start. I'd appreciate if someone could could point me in the right direction

[ntgussoni]

I've been playing with proxies for a while now, you could probably proxy prisma create update delete to generate a snapshot of the record before changing it.

If you keep a versions table with the ID, modelname, date and a jsonb column with the serialized record you'll be able to go back in time.

This is obviously naive, because you'll have to deal with other things like relationships.. But it could give you something to start with.

I had it in my plans to make something like this

[BjoernRave]

It's not exactly what you are looking for, but what I am currently doing is to have a isDeleted field on the models, which I want to keep and then use prisma middleware like this:

const protectedEntities = [
  'Payment',
  'Client',
  'Supplier',
  'Product',
  'Movement',
  'TransportAgency',
  'Warehouse',
]


  prisma.$use(async (params, next) => {
    if (protectedEntities.includes(params.model)) {
      if (params.action == 'delete') {
        params.action = 'update'

        params.args['data'] = { deleted: true }
      }

      if (params.action == 'deleteMany') {
        params.action = 'updateMany'

        if (params.args.data != undefined) {
          params.args.data['deleted'] = true
        } else {
          params.args['data'] = { deleted: true }
        }
      }
    }

    const result = await next(params)

    if (
      result?.id &&
      ['create', 'update', 'delete'].includes(params.action) &&
      protectedEntities.includes(params.model)
    ) {
      const isDeleting = (params as any).clientMethod.indexOf('delete') !== -1

      const action = isDeleting ? 'delete' : params.action

      const model = params.model.charAt(0).toLowerCase() + params.model.slice(1)

      const existingInteraction = await prisma.interaction.findFirst({
        where: {
          AND: [
            { action: { equals: action } },
            { [model]: { id: { equals: result?.id } } },
          ],
        },
      })

      if (!existingInteraction) {
        let data: InteractionCreateInput = {
          userId: user.sub,
          data: JSON.stringify(params.args.data),
          action,
          type: model,
          [model]: {
            connect: {
              id: result.id,
            },
          },
        }

        await prisma.interaction.create({
          data,
        })
      }
    }

    return result
  })

the check for an existing interaction is probably not needed, but due to my setup I need to add the middleware inside the context creation, which (probably only in dev) causes it to be created multiple times

the code is inspired by: https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-client/middleware/soft-delete-middleware

[ntgussoni]

This is nice, I've tried this approach as well, but the only issue is that you have no context. So if you wanna, for example, store who made the change you can't as you dont have access to the session

[BjoernRave]

This is not from a blitz project, but just a prisma backend and because I add the middleware inside the context creation function, which get's passed to the apollo server I do have access to the session

[engelkes-finstreet]

You could also go with an update trigger and create a separate history table for each table you already have. Your update table could have a created_at and content jsonb column where the update trigger would create a new row for each update. This way you could go back in time as well.

[semopz]

Thanks, I've seen this suggested as well but I have a few concerns about this approach (I'm quite a novice in the SQL field so bear with me)

• No idea how that would work in the context of Blitz/Prisma (i.e., would I have to write my own migration scripts?)
• How would joins work in time travel queries? DB engines that have native support for temporal tables have an AS OF clause which I think helps with that

[semopz]

So I've learned that the domain of this discussion is called Temporal tables (I've amended the title to reflect this). Apparently this was an Oracle exclusive feature for a while but MS SQL has had it since SQL Server 2016. From my search, it seems PostgreSQL doesn't have this feature on its roadmap.

I won't list all the links I've visited but I'll just leave this one as it very succinctly describes what I'm after (just search for the topic, plenty of articles and videos).

A temporal table is a table that records the period of time when a row is valid. There are two types of periods: the application period (also known as valid-time or business-time) and the system period (also known as transaction-time).

EDIT: The above statement describes a bitemporal database (the holy grail). Most implementations are "just" System-Versioned Temporal Tables

[flybayer]

From what I understand Fauna DB supports this. Fauna is a bit cumbersome to use, but I think prisma plans to add fauna support sometime which would be really great.

[semopz]

I also found out that MariaDB has support for system versioning tables but Prisma's engine does not recognize this functionality.
prisma/prisma#3334

There's probably a better way of abstracting this requirement so as to avoid customizing the Blitz stack too much or hoping for a deus ex machina features to save the day. Wish I knew what that would look like 🙃