Как вести историю работы с записями во всех таблицах для всех пользователей?

Question

[historydev]

Я хочу отслеживать кто и когда: создаёт, изменяет, удаляет сущности.
- Если оставить текущее решение, гор копи-паста не избежать.

Текущая схема prisma (только затронутые поля):

model UserAccount {
  id Int @id @unique @default(autoincrement()) @map("id")

  creatorAccount UserAccount? @relation("CreatorAccount", fields: [createdBy], references: [id])
  editorAccount  UserAccount? @relation("EditorAccount", fields: [editedBy], references: [id])
  deleterAccount UserAccount? @relation("DeleterAccount", fields: [deletedBy], references: [id])

  isDeleted      Boolean   @default(false) @map("is_deleted")
  deletedBy      Int       @map("deleted_by")
  deletedAt      DateTime? @map("deleted_at") @db.Timestamptz()
  deletionReason String?   @map("deletion_reason") @db.VarChar(128)

  createdUsers UserAccount[] @relation("CreatorAccount")
  editedUsers  UserAccount[] @relation("EditorAccount")
  deletedUsers UserAccount[] @relation("DeleterAccount")

  createdBy Int?      @map("created_by")
  createdAt DateTime @map("created_at") @db.Timestamptz()

  editedBy Int?      @map("edited_by")
  editedAt DateTime? @map("edited_at") @db.Timestamptz()

  @@map("user_accounts")
}

Решил идти в сторону отдельной таблицы (или множества) для истории.
Вижу 2 варианта:
1. Самый удобный как по мне, но наименее компактный и трудно-поддерживаемый - по таблице с историей на каждую таблицу.
2. Одна общая таблица с полями oldData (JSON), newData (JSON).

И есть такой:
- Массив как поле у записи.

Как поступают в таком случае?

P.S: Уровень БД.

Answer 1

[Vamp]

У себя на проекте я сделал по вашему второму варианту. Колонки date, user_id, ip, table, data. Где data - json структура вида {"field_name": {"old": "было", "new": "стало"}}

Затем в отдельном разделе в админке этот лог можно смотреть в удобном виде, фильтровать, выгружать для аудита в сторонних программах.

Comments

[N]

Схема хорошая...только автору видимо нужно на уровне "БД", а не "интерфейса" это сделать...

[historydev]

Что происходит с таблицей истории при переименовании одной или нескольких основных таблиц?

[historydev]

N, Совершенно верно.

[Vamp]

historydev, такого ещё не было, но если пофантазировать, то я бы историю не трогал, новые записи вставлял с новым именем таблицы (забыл, кстати, указать в ответе, что есть ещё колонка с именем таблицы, в которой производилось изменение) и переименование обыграл в коде, отвечающем за web ui. Например, массивом с инфой о переименованиях.

[historydev]

Vamp, Ужасное решение.

[Vamp]

historydev, переименовывать таблицы - вот ужасное решение. Это допустимо на этапе разработки, но в продакшене это лишь ненужный гемор с неясным выхлопом. И чем больше проект и команда, которая над ним работает - тем хуже последствия.