What is the best way to keep an interactive log in the admin panel?

Question

Journaling

Employees do something in the admin panel.
What is the best way to implement such a thing for a controlling person:
An employee created an order, something like "Employee No. 4 (Last Name First Name) created order # 115 (any details)" is written in the administrator's log. That being said, I want #4 and #115 to be active links.
I write logs in a table where I have id-datetime-log (just text).
Is it stupid for me to write this tablet with a href or are there simpler / more concise / more convenient solutions? Tell me, does my logging option have a place to be or not? Is it +- optimal?

Answer 1

It all depends on the purpose of logging, i.e. what do you want to achieve?
Regardless of this, the easier it will be to filter the log (ie, the more machine-readable attributes), the more convenient it will be to develop new tools for its analysis. Those. analyze plain text is wild crap.
Keep in the log as detailed as possible by fields (or maybe develop a whole complex database structure) what happens where when and why.
ps log example - duplication of the database structure, with the addition of new key fields, so that any change in the table generates a new entry in the log. Add the concept of a user session and write down in each table its id in a separate field (session is who, from which machine, when logged in, when it completed work), additionally the date of the change and the type of change itself (create/modify/delete). In some cases, data storage can be optimized if modify records store only modified fields and delete records only identifiers, but working with such a structure is more difficult.
These tables can be filled with triggers, the code for creating this database is generated almost automatically (it is a little more difficult to update the database after editing the structure, but also real). As a bonus, you can restore the database to any point in time (since the log-base is actually a transaction log), just be careful, after changing the structure, make a full backup of the original and store it forever, otherwise a full restoration from the beginning of history can cost a pretty penny.
Such a log database is write once, which allows you to choose the most effective storage technologies for this. No one bothers to copy the data of interest to a separate database for the duration of the analysis (in memory, for example, for the last day or week) and work with this subset more efficiently. Definitely suggests a separate placement of the base on another hardware (at least another disk).
ps advice, keep next to this database, in RAM (when you turn on the computer, it can be restored from the log), a snapshot (and preferably a lot, according to key events) of the identifiers of the last change tables (just a list of id, for example, in the form of a table {event_id, table_id , id_value}) then you can pull out the state at key points by a query with O(1) laboriousness and not n or n^2 (depending on the number of changes in the table).
Based on this log base, it will be possible to make any requests, literally solve any tasks of monitoring, analysis, solving problems with personnel, and even vandalism. Among the shortcomings of the solution is a high redundancy in data storage, as well as a high load on this database for analysis requests, but you can think in advance in the structure of partitioning tables by time, usually in the life of any system there are periods of debriefing, annual comprehensive reports, and so on, at least for these periods, you can divide the database and send outdated data to the archive.
The growth rate of such a database is an order of magnitude higher (a similar approach was used selectively for some tables in production, hundreds of users, a 10-gigabyte database, a log - 8 times larger). Don't spare disk space, it's cheaper than the problems it solves.