Как лучше хранить журнал в долгосрочной перспективе?

По сути три больших направления с кучей мелких ответвлений
1. хранить текстовый лог в отдельном файле/сервисе/логохранилище
2. хранить лог действий юзеров в отдельной таблице (встречал один проект, где лог действий был в десятки раз больше, чем сами данные, ага).
3. хранить в той-же таблице предыдущие записи. То есть при редактировании INSERT, а не UPDATE, при этом автоматически проставляется время и автор, а при выборке просто берете последнюю по времени версию.

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

Особняком стоит упомянуть системы с возможностью одновременного редактирования несколькики пользователями, которые автоматически разруливают коллизии. Самый знакомый всем пример — google docs. Но это довольно сложно в реализации.

С учетом «использоваться будет, я надеюсь редко» я бы остановился на текстовом логе. Отдельный лог на каждую запись, можно архивировать старые, можно logrotate натравить.

Как его лучше хранить? Организационная часть. Если вы хотите хранить его так чтобы никто не изменил — то надо строить отдельным сервисом. Иначе те-же люди что и натворили бед смогут зачистить свои следы. Или я не понял корень вашей проблемы.

Техническая часть. Очевидно что нужна еще одна таблица. С датой аудита. С реквизитами пользователя который делал бизнес-операцию. И две версии данных. «До» и «после» изменения. Данные можно хранить в денормализованном формате (XML или Json) для простоты схемы.

Лучше всего использовать для хранения логов отдельную БД,предназначенную для этих целей, иначе вы рискнуете очень быстро получить ситуцию, когда таблица с логами будет занимать больше места на диске, чем все остальные таблицы с бизнес данными, вместе взятые. Это неминуемо приведет к проблемам с администрированием такой базы, бэкапы станут больше по размеру, будут делаться дольше и т.д.

В качестве БД для логов лучше всего использовать Click House — базу от Яндекс. Она отлично для этих целей подходит и невероятно хорошо сжимает данные, т.е. помимо всего прочего, еще и на диске эти данные будут не много места занимать. Также вы можете с Click House настроить полтики хранения данных, например указать что данные в таблице лога должны храниться 5 лет, и CH будет сам их чистить.

Нужно также учесть, что если вы хотите сделать хранение лога транзакционным, т.е. гарантировать, что не будет ситуации, когда у вас бизнес данные поменялись, а при запили в лог упала ошибка, и данные не были залогированы, то нужно вести запись в CH в два этапа. Нужно продублировать таблицу для ведения лога в вашей транзакционной БД, и писать в нее информацию о действиях пользователей в одной транзакции с изменением бизнес данных. Далее нужно реализовать джобу, которая в фоне например по расписанию, или иным образом, будет скидывать данные из лог таблицы в транзакционной БД в таблицу ClickHouse, затем удалять данные в лог таблице транзакцонной БД, только после их успешного переноса в CH. Таким образом таблица с логами в транзакционной БД всегда будет маленького размера.

См. также паттерн Transactional Outbox

https://www.dolthub.com

Часто для таких целей используется откидывание в elasticsearch, который нативно умеет по нужным правилам сортировать данные на горячие, теплые и холодные (например последний месяц — hot, год — warm и остальное — cold)

Но всё зависит от потребностей бизнеса. Если история сущности востребована часто и отображается в рамках дизайна приложения — то можно и удобно хранить историю в таблице кто-то-когда-было-стало и если история нужна долго, но это дорого — то тем или иным образом сливать самое старое на дешевые и медленные хранилища.
Подразумевая sql — оптимально для такого писать историю триггерами, ну и если жмёт объём — разносить «по шпинделям».