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

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: Уровень БД.

Comments

[Akina]

На уровне БД есть два стандартных решения.
Первое - это использование "мягкой" технологии, когда любое изменение выполняется путём создания новой записи с актуальным состоянием, а предыдущая помечается как неактуальная либо используется системный штамп времени.
Второе - это создание для каждой таблицы пакета триггеров, который пишет все изменения в журнал.
Оба способа обеспечивают полное журналирование ценой расхода дискового пространства. Первый удобнее при откатах, второй при формировании экшен-логов.
Как это реализуется и отображается во фронте - не скажу, ибо не использую.

[shurshur]

При использовании ORM можно ещё сделать это средствами ORM. Встречал, например, django-reversion, с которым можно объекты в django версионировать.

[historydev]

Akina, Первое решение хорошо звучит, только эти копии очень много диска будут занимать.
- Есть ли способ сэкономить место с этим подходом?

Триггеры для записи в таблицу аудита, меньше места но чуть сложнее извлечение, с другой стороны обращаться к ней реже будут.
- При условии что аудит хранит лишь изменённые поля.

Кажется если учесть производительность в уравнении, копия таблицы с историей лучший вариант.

[historydev]

shurshur, Меняется orm - пропадает фундаментальная вещь, не годится.

[Akina]

historydev, как раз с точки производительности первый вариант ничем не проигрывает второму, если использовать партиционирование по полю актуальности. Правда, можно отловить проблем с других сторон, так что такие вещи нужно хорошо планировать.

А экономия места - при том, что дисковое пространство сейчас достаточно дешёвое,- почти бессмысленна. Можно вынести лог на отдельный диск, который, в отличие от дисков с данными, может быть и небыстрым. Ну и ничто не мешает периодически архивировать логи, благо жмутся они весьма неслабо.

[historydev]

Akina, Партицирование это partition by?

[Akina]

historydev, да.

[historydev]

Akina, спасибо

[historydev]

Akina, Как поступить с идентификаторами в случае дублирования при изменении в одну таблицу?

[Akina]

historydev, вопрос совершенно непонятен. Идентификатор записи в рабочей таблице явно не будет являться идентификатором записи в таблице логов. Хотя скорее всего будет его компонентом.

[shurshur]

historydev,

Меняется orm - пропадает фундаментальная вещь, не годится.

Обычно если уже используется ORM, то используется основательно и без тотального переписывания от неё не откажешься. Так что вполне себе нормальная затея связать этот функционал с ORM.

Как вариант, делать свои обёрточные классы по типу ORM, в которых и скрывать логгирование всех изменений при сохранении записи.

[historydev]

shurshur, В слоёной можно спокойно заменить orm на любую другую, главное чтобы контракт домена сохранялся. Придётся миграцию провести, а тотальное переписывание будет связано лишь c моделью.

Да, я добавлю мидлу prisma для вставки в базу - чтобы писать логи в таблицу, но ровно тоже самое можно сделать обычным sql или другой orm, так что я не жёстко привязан к ней.

[historydev]

Akina, Если мы вставляем обновления в туже таблицу где хранятся оригиналы, каждая запись будет иметь свой id и получается нужно 2 идентификатора, один записи, другой пользователя. И все связи нужно крепить на id пользователя.

[Akina]

historydev,

Если мы вставляем обновления в туже таблицу где хранятся оригиналы, каждая запись будет иметь свой id и получается нужно 2 идентификатора, один записи, другой пользователя. И все связи нужно крепить на id пользователя.

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

[historydev]

Akina, Мне кажется что это слишком, отдельная таблица намного проще чем 2 id и флаг lts/curr. Понятно что это критические изменения, но их сложность заставляет задуматься стоит ли оно того.

Я пришёл к такой реализации, опробовав всё описанное в рамках данного вопроса и решив проблемы, которые потенциально могут возникнуть:

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

  type TableAuditLogType
  tableId  Int @map("table_id")

  recordId Int @unique @map("record_id")

  oldFieldValues  Json? @map("old_field_values")
  newFieldsValues Json  @map("new_field_values")

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

  userAccount UserAccount @relation(fields: [createdBy], references: [id])
  table TableRegistry @relation(fields: [tableId], references: [id])
}

enum TableAuditLogType {
  CREATE
  UPDATE
  SOFT_DELETE
  DELETE
}

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

  name      String @unique @map("name")
  modelName String @map("model_name")

  createdAt DateTime  @map("created_at") @db.Timestamptz()
  updatedAt DateTime? @map("updated_at") @db.Timestamptz()

  auditLogs TableAuditLog[]
}

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

[historydev]

Vamp, Безусловно и разработка всегда идёт рядом с продом, если такая ситуация может возникнуть - нужно уметь с ней работать.

Answer 2

[rPman]

Есть один способ (видел давно и позже в другом проекте использовал), он почти универсальный, обладает кучей достоинств по возможностям аналитики, но повышает требования к месту на диске (и незначительно нагрузку на базу, но это в зависимости от типа аналитики, т.е. необходимости создания индексов). Например позволяет восстанавливать состояние базы на указанный момент времени (вырожденный случай но да). И главное, почти полностью автоматическое создание этой архивной базы (и с оговорками обновление при изменениях), без необходимости ковыряться в ее DDL.

Создание полной копии записи базы с каждым изменением, проводимым в них. Создается новая база данных (или в той же самой базе, но имена таблиц снабжаются унифицированным преффиксом), настоятельно рекомендуется отдельное хранилище для таблиц (отдельный диск еще лучше), но не другой сервер. Каждая архивная таблица структурно (список полей и их типы) без индексов и информации об ограничениях (все поля могут быть null, кстати тут есть оговорка, если оригинальные таблицы не будут содержать null то возможна значительная оптимизация по размеру архивных таблиц) должна дублировать оригинальные, с добавлением полей - тип действия (insert/update/delete), время, пользователь или ссылка на таблицу архива сессий авторизации (в этой таблице время, пользователь и информация о его сессии например ip,..). Каждая оригинальная таблица должна быть снабжена тригером на insert/update/delete который будет дублировать предыдущее значение (или сохранять новое - оба варианта подходят но я рекомендую второе) в архивной таблице, в простом варианте дублируя всю запись, но в расширенном варианте возможно сохранение только изменившихся колонок (сравнением new и old) а для остальных полей и для delete оставлять нули и primary key.

Все можно сделать скриптами, которые анализируют структуру базы и создают (пересоздают) тригеры и таблицы.

Comments

[historydev]

Как понять "скрипты базы"?

[historydev]

Как в таком случае поступить со связями?

Например у пользователя может быть множество товаров, нужно определить состояние определённого товара для определённой записи пользователя.
- Реальный сценарий?

[rPman]

скрипт - примитивная программа, подключается к базе данных, делает запрос на структуру и на основе результата генерирует sql ddl на создание архивной таблицы и тригеров

связи - они у вас в голове (в смысле то как вы понимаете хранение данных в таблицах), в базе данных они 'обычно' обозначаются индексами и ограничениями (foreign key) и никто не мешает необходимые индексы создавать и на этой базе (но тут не рекомендуется делать бездумно копирование, так как это может двухкратно уронить скорость работы базы на запись и поднять требования к оперативной памяти)

Например у пользователя может быть множество товаров, нужно определить состояние определённого товара для определённой записи пользователя.

вообще эта задача никаким боком к архивной базе не имеет отношение. Простой запрос на товары, заказанные пользователем.

Архивная база может дать ответ на вопрос, как часто данный пользователь делает заказы, или сказать что заказывал пользователь на конкретную дату, или проанализировать нагрузку на финансовый отдел, построив график зависимости времени между сменой статуса у заказов на время и день недели... или вот реальный случай, нужно было понять кто из ответственных изменил статус куда не следует (но это было не запрещено из-за не слишком сложной базы)

[historydev]

rPman, Хорошо, спасибо. Со связями я вообще не туда пошёл, какой-то абсурд придумал - голова вскипела.