[arialdomartini]

While I like the YAGRI principle very much, I find that adding

- updated_at

- deleted_at (soft deletes)

- created_by etc

- permission used during CRUD

to every table is a solution weaker than having a separate audit log table.

I feel that mixing audit fields with transactional data in the same table is a violation of the separation of concerns principle.

In the proposed solution, updated_at only captures the last change only. A problem that a separate audit log table is not affected to.

[grey-area]

An audit log table often takes a huge amount of space compared to simple fields on the records so there are tradeoffs. Which solution is best depends on how important change logs are.

[wodenokoto]

I kinda agree, but don’t underestimate the power of having things where people are looking.

Put your documentation in doc strings where the function is defined - don’t have a separate file in a separate folder for that. It might separate concerns, but no one is looking there.

Similarly if those fields aren’t nullable, someone trying to add new rows will have to fill in something for those metadata fields - and that something will now very likely be what’s needed, rather than not pushing anything to the audit table.

Obviously your app can outgrow these simple columns, but you’re getting value now.

[motorest]

Event sourcing also works great. You don't need an audit log per se if you already track a history of all commands that introduced changes to your system.

[nine_k]

Event sourcing and "the right to be forgotten" are not always easy to marry.

[motorest]

> Event sourcing and "the right to be forgotten" are not always easy to marry.

The absolute basics is to support snapshots and event replay. This is hardly rocket science.

[nine_k]

If you try to redact a part of the past, it can also affect the present, as any time traveler knows.

Let's assume we want to remove every message related to user A.

A photo by user B got to be the best of the day because it collected most upvotes. Without the A's vote, it's no longer so. The photo also got to become the best of the month because it was later voted as the top from the best-of-the-day entries, and received a prize. Should we now play the message stream without the A's upvote, things are going to end up radically different, or end up in a processing error.

User B was able to send a message to user C, and thus start a long thread, because user A had introduced them. With user A removed, the message replay chokes at the attempt of B to communicate with C.

One way is to ignore the inconsistencies; it deprives you of most of the benefits of event sourcing.

Another way is anonymizing: replace messages about user A with messages about some null user, representing the removed users. This can lead to more paradoxes and message replay inconsistencies.

[motorest]

> If you try to redact a part of the past, it can also affect the present, as any time traveler knows.

That's not how snapshots work. You record the state of your system at a point in time, and then you keep all events that occurred after that point. This means you retain the ability to rebuild the current state from that snapshot by replaying all events. I.e., event sourcing's happy flow.

> User B was able to send a message to user C, and thus start a long thread, because user A had introduced them. With user A removed, the message replay chokes at the attempt of B to communicate with C.

Not really. That's just your best attempt at reasoning how the system could work. In the meantime, depending on whether you have a hard requirement on retaining messages from removed users you can either keep them assigned to a deleted user or replace them by deleted messages. This is not a problem caused by event sourcing; it's a problem caused by failing to design a system that meets it's requirements.

[arialdomartini]

Yep. But Event Sourcing comes with its own set of other problems. Also, I don't think this would apply to OP's post: with Event Sourcing you would not even have those DB tables.

[motorest]

The DB tables suggested by OP are a kin to snapshots, whereas each event would require a separate data store. OP is trying to shoehorn event history into the snapshots, which hardly makes any sense.