They need to be surfaced to the product owner to decide. There may very well be reasons pieces of data should not be stored. And all of this adds complexity, more things to go wrong.
If the product owner wants to start tracking every change and by who, that can completely change your database requirements.
So have that conversation properly. Then decide it's either not worth it and don't add any of these "extra" fields you "might" need, or decide it is and fully spec it out and how much additional time and effort it will be to do it as a proper feature. But don't do it as some half-built just-in-case "favor" to a future programmer who may very well have to rip it out.
On a personal project, do whatever you want. But on something professional, this stuff needs to be specced out and accounted for. This isn't a programming decision, it's a product decision.
Sure there were people who's job was to offload as much compliance work from everyone else; by turning it into internal requirements, participating in design discussion and specializing in ensuring compliance. But trying to isolate the development team from it is just asking for micromanagers.
Think before you act. The machine has no brain. Use yours.
> Part of every development job is learning the product domain.
Yes.
> In that case devs become comfortable with reading standard/law/regulations and anticipating when software implementation might interact with the areas covered.
This is what I'm saying, too. A developer needs to think whether what they are doing is OK by the regulation they're flying against. They need to ask for permissions by asking themselves "wait, is this OK by the regulation I'm trying to comply?".
> But trying to isolate the development team from it is just asking for micromanagers.
Nope, I'm all for taking initiatives, and against micromanagement. However, I'm also against "I need no permission because I'm doing something amazing" attitude. So own your craft, "code responsibly".
This is the kind of tribal knowledge you want to spread among a development team, and if a collaborative document of "Why it's done this way" can be propped up with pointers to relevant sections of the regulation, it'd be a very good thing.
Not unlike NASA's global Lessons Learnt document.
Being unable to even call the shot of whether a database table should have an updated_at or soft-delete sounds like a Dilbertian hellscape to me.
The problem of course is that soft deletes are hard. As soon as you take sub-resources and relations into considerations, especially with shared ownership, things get complicated. SQL databases can usually handle cascading deletes and nulling but that doesn't work with soft deletes - also, if a soft delete exists to allow for a restore, how do you handle references you would null in an actual delete? Now you need to either track the deleted value or add logic to every query involving that reference to filter out soft deletes in addition to null references (which adds query complexity).
Also not sure what you mean by additional effort? Created_at, updated_at or soft-deletes are part of most proper frameworks. In Spring all you need is an annotation, I've been using those in major projects and implementation cost is around a few seconds with so far zero seconds of maintenance effort in years of development. At least those fields are solved problems.
I've been parts of teams where features had to be totally thrown out and rebuilt because developers made big assumptions that turned out to be wrong, because they didn't think it was worth it to check with the product owner. Because they assumed it was only a "technical decision", or they assumed they understood the customer needs despite never actually asking the customer.
This doesn't mean checking with product around each line of your code, obviously. But deciding what information gets stored in the database, what level of event tracking you do, whether deletes are hard or soft -- these have massive product implications, and potentially legal ones.
And it is additional effort. Now you have to write tests for all those things. Are the timestamps being stored correctly? Are the permission bits being stored correctly? Is "created_by" coming from the right user? Are we sure a malicious user can't spoof that? Do we care? Is "updated_at" actually being updated on every row change? But are we making sure "updated_at" is not getting changed when we import data from a separate table? How often do we remove soft-deleted data in order to comply with privacy policies and regulations, and with what cron job, and who maintains that? Where do alerts go if the cron job fails? What happens if that employee leaves? I could go on and on and on.
So that's what I mean by additional effort. It's not "around a few seconds". Because it's not just a technical question, it's a product one. It's a proper feature that needs to be properly defined and properly scoped out and properly tested.
That's not all the article was suggesting. You're ignoring the other three fields the article says to "store on almost any table".
I'm not "grasping", I'm describing actual reality. While you're misrepresenting the article by cherry-picking the simplest fields, which aren't even always simple for the reasons I gave.
the hypothetical future programmer is you in two weeks.
This argument is one of the reason why a backend engineer could just add the created_at and updated_at fields without asking a product owner.
It doesn't make much sense from the pure product perspective, so the standard answer will be "no, let's add them when we have a real case they're needed". The product I'm inheriting right now misses these fields on half of the tables. Except when you really want the data, it won't be there as you're not going back in time.
Trying to convince someone that it's worth it will also give the impression they're optional, when you already decided you need them. So at the end of the day, it's your responsibility as an engineer to do what's required, without punting it to non technical stakeholders to push your back.
I also wouldn't ask a product manager if they think table schema should be orthogonal.
Now keeping or not IPs or user IDs in a table is a whole different story and requires a lot more consulting, and not just with the PO.
in other words - YAGNI !
That's a little vague given this specific example, which appears to be about maintaining some form of informative logging; though I don't think it necessarily needs to be in the form of an DB table.
On the other hand, in some shops there is a dedicated DBA who is in charge of database schemas and possibly everything else. Before it became fashionable to create a "service layer" where people access the database (now database(s)) throw web endpoints, some organizations would put all the database access into a set of stored procedures managed by the DBA. Maybe that's extreme, but in the real world product owners come and go but the database is forever and deserves to have somebody speaking out for its interests.
CREATE TABLE ... WITH SOFT DELETES
If we like to talk about in-database processing so much, why don't we just put the actual common features in the DB, so that toggling them on or off doesn't take a bunch of code changes in app, or that you'd even be able to add soft deletes to any legacy app that knows nothing of the concept, on a per table basis or whatever.
"Soft deletes" is just a name for a regular write operation, with specific semantics.
Adding a layer of magic to the DB for this doesn't seem right to me.
And applications could have many different requirements for soft deletes, like the article points out. For example, the simplest version would be just a boolean "deleted" column, but it could also be "deleted_at", "deleted_by", etc.
All of these cases require an bunch of code changes anyway, and the more complex ones could interfere with an implementation of this feature at the database level: such a transparent implementation couldn't access app-specific concerns such as user data, for example.
Adding soft deletes to a legacy app that knows nothing about it would only work for a boolean flag and a maybe date-time value, unless the DBMS would also offer triggers for soft deletes etc?
Seems to me to that this capability would make a DBMS much more complicated.
... WITH SOFT UPDATES
Stored procedures are great but many shops don't use them for various reasons. It doesn't help that they look like a mix of FORTRAN and COBOL and also come in a few variations from the (better) set-based PL/SQL of Oracle to the (worse) Transact-SQL based stored proc of Microsoft SQL and PostgresSQL. The other day I talked with Krisztián Szabó of
who developed a compiler that writes stored procs that do database synchronization.
On the other hand, if you've got access to the internals of the frickin' database I think you can do something better than the ordinary application level soft updates. For instance a "customer record" might very well be not just a row in one table but maybe 15 rows in four tables that are inserted in a transaction and you want to be able to undelete them as a unit.
As an aside, I've never found this to be worth it since you have to change too much and re-test everything for minimal user benefit and time savings. The effort is way worse if the code is not great in the first place. It can be a great decision to make before everything is written.
Maybe it's worth it for files which are hard to reproduce, but you can also rely on DB backups to get those back. If people are regularly deleting things they're not supposed to, you're better off removing the user-facing delete actions, limiting the action to specific users, etc.
Most of the time if you want "soft deletes", you really want an immutable log so that you time travel to any point in the history. XTDB and Datomic are worth looking at if you want to solve the problem at the data model level.
Different products will handle soft deletes differently. Which queries need to include soft-deleted rows and which don't? What about different levels of soft deletes, e.g. done by the user (can be undone by user) vs. done by an admin (can't be undone by user)?
Implementing soft deletes yourself isn't hard. Yes you'll have to make a bunch of decisions about how they work in every circumstance, but that's the point.
0: https://learn.microsoft.com/en-us/sql/relational-databases/t...
It's one of these things that are available but most people ignore it and implement it manually with created_at updated_at deleted_at columns etc. I think one reason for this is lack of support in ORMs and lack of standardization between RDBMSes.
If they did this, nobody would use it. They do lots of more useful things that people don't use because it's not portable.
There's a sibling comment about temporal databases. Those solve a very bothersome problem, so a few people use them. That means that there's a chance soft deletes get adopted as a side effect of a much more complex standard.
It needs to be enabled of course and it's not free.
Do the long walk:
Make the schema fully auditable (one record per edit) and the tables normalized (it will feel weird). Then suffer with it, discover that normalization leads to performance decrease.
Then discover that pruned auditing records is a good middle ground. Just the last edit and by whom is often enough (ominous foreshadowing).
Fail miserably by discovering that a single missing auditing record can cost a lot.
Blame database engines for making you choose. Adopt an experimental database with full auditing history. Maybe do incremental backups. Maybe both, since you have grown paranoid by now.
Discover that it is not enough again. Find that no silver bullet exists for auditing.
Now you can make a conscious choice about it. Then you won't need acronyms to remember stuff!
If you never use it, that data can be dumped to s3 glacier periodically (e.g. after 90 days).
By losing the foreign key you gain flexibility in what you audit. Maybe audit the operation and not the 20 writes it causes.
So like OP said, no silver bullets exist for auditing.
I'm not saying databases are blameless. It's just that experiencing the issues they have by yourself is rewarding!
There is also a walk before the long walk of databases. Store things in text files and use basic tools (cat, sed, sh...).
The event driven stuff (like Kafka) reminds me of that. I am not very familiar with it though, just played a little bit with it once or twice.
It is the point where you give up modeling the audit as part of the systems tables.
The drawbacks of this choice are often related to retrieval. It depends on the engine.
I once maintained a system that kept a fully working log replicated instance delayed by 24h, ready for retrieval queries, in addition to regular disk backups (slow costy retrieval).
I am more developer than DBA, so I can probably speak more about modeling solutions than infra-centric solutions.
- 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.
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.
The absolute basics is to support snapshots and event replay. This is hardly rocket science.
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.
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.
It is tempting to supernormalize everything into the relations object(id, type) and edit(time, actor_id, object_id, key, value). This is getting dangerously and excitingly close to a graph database implemented in a relational database! Implement one at your peril — what you gain in schemaless freedom you also lose in terms of having the underlying database engine no longer enforcing consistency on your behalf.
This feels like a great unresolved tension in database / backend design - or maybe I'm just not sophisticated enough to notice the solutions?
Is the solution event sourcing and using the relational database as a "read model" only? Is that where the truly sophisticated application developers are at? Is it really overkill for everybody not working in finance? Or is there just not a framework that's made it super easy yet?
Users demand flexible schemas - should we tell them no?
I frankly hate this sort of thing whenever I see it. Software engineers have a tendency to optimize for the wrong things.
Generic relations reduce the number of tables in the database. But who cares about the number of tables in the database? Are we paying per table? Optimize for the data model actually being understandable and consistently enforced (+ bonus points for ease of querying).
Aren't you describing a non-functional approach to event sourcing? I mean, if the whole point of your system is to track events that caused changes, why isn't your system built around handling events that cause changes?
So is_deleted would contain a timestamp to represent the deleted_at time for example. This means you can store more information for a small marginal cost. It helps that rails will automatically let you use it as a Boolean and will interpret a timestamp as true.
A light switch doesn't have an atomic state, it has a range of motion. The answer to the question "is the switch on?" is a boolean answer to a question whose input state is a range (e.g. is distance between contacts <= epsilon).
Original design: store a row that needs to be reported to someone, with an is_reported column that is boolean.
Problem: one day for whatever reason the ReporterService turns out to need to run two of these in parallel. Maybe it's that the reporting is the last step after ingestion in a single service and we need to ingest in parallel. Maybe it's that there are too many reports to different people and the reports themselves are parallelizable (grab 5 clients, grab unreported rows that foreign key to them, report those rows... whoops sometimes two processes choose the same client!)... Maybe it's just that these are run in Kubernetes and if the report happens when you're rolling pods then the request gets retried by both the dying pod and the new pod.
Alternative to boolean: unreported and reported records both live in the `foo` table and then a trigger puts a row for any new Foos into the `foo_unreported` table. This table can now store a lock timestamp, a locker UUID, and denormalize any columns you need (client_id) to select them. The reporter UPDATEs a bunch of rows reserving them, SELECTs whatever it has successfully reserved, reports them, then DELETEs them. It reserves rows where the lock timestamp IS NULL or is less than now minus 5 minutes, and the Reporter itself runs with a 5 minute timeout. The DB will do the barest amount of locking to make sure that two UPDATES don't conflict, there is no risk of deadlock, and the Boolean has turned into whether something exists in a set or not.
A similar trick is used in the classic Python talk “Stop Writing Classes” by @jackdied where a version of The Game of Life is optimized by saying that instead of holding a big 2D array of true/false booleans on a finite gameboard, we'll hold an infinite gameboard with a set of (x,y) pairs of living cells which will internally be backed by a hashmap.
E.g. a field called userCannotLoginWithoutOTP.
Then in code "if not userCannotLoginWithoutOTP or otpPresent then..."
Thus may seem easy until you have a few flags to combine and check.
An enum called LoginRequirements with values Password, PasswordAndOTP is one less negation and easier to read.
Not that it really matters; deleted_at times for your database records will rarely predate the existence of said database.
MyModel.nondeleted.where(<criteria>)
which generates a query with "WHERE deleted_at IS NULL"
1-1-1970 is fine.
Many of these "we are going to need it"s come from experience. For example in the context of data structures (DS), I have made many "mistakes" that I do correctly a second time. These mistakes made writing algorithms for the DS harder, or made the DS have bad performance.
Sadly, it's hard to transfer this underlying breadth of knowledge and intuition for making good tradeoffs. As such, a one-off tip like this is limited in its usefulness.
If it's not data that's essential to serving the current functionality, just add a column later. `updated_at` doesn't have to be accurate for your entire dataset; just set it to `NOW()` when you run the migration.
But for the example of the "updated_at" column, or "soft delete" functionality, you only find out you need it because the operations team suddenly discovered they needed that functionality on existing production rows because something weird happened.
public interface ITrackable { DateTime CreatedOn {get; set;} DateTime ModifiedOn {get; set;} }
Saves so much time and hassle.
Use a popular framework. Run it against your test database. Always keep backups in case something unforseen happens.
Something especially trivial like adding additional columns is a solved problem.
Even in the best case (e.g. basic column addition), the migration itself can be "noisy neighbors" for other queries. It can cause pressure on downstream systems consuming CDC (and maybe some of those run queries too, and now your load is even higher).
Here’s one of mine: Postgres change applied fine in unit and integration and dev but not prod because the shape of the data (enum) did not conform to the new constraint.
Another would be a monorepo that had 5-6 services that talk across db’s to each other caused dev to apply the wrong migration to the wrong HEAD, mixing up the db’s. That was a fun one to sort out
"HOW DARE YOU MODIFY MY DOCUMENTS WITHOUT MY..."
So really you probably just want a reference to the tip of the audit log chain.
I like the heuristics described here. However if these things aren't making it into a product spec where appropriate, then I smell some dysfunction that goes beyond what's being stored by default.
Product need (expressed as spec, design, etc) should highlight the failure cases where we would expect fields like these to be surfaced.
I'd hope that any given buisness shouldn't need someone with production database access on hand to inform as to why/when/how 'thing' was deleted. Really we'd want the user (be it 'boss' or someone else) to be able to access that information in a controlled manner.
"What information do we need when something goes wrong?". Ask it. Drill it. Ask it again.
That said, if you can't get those things, this seems a fine way to be pragmatic.
That said, the monkey paw of this would be someone reading it and deciding they should capture and save all possible user data, "just in case", which becomes a liability.
I've been working on one in Typescript (with eventual re-writes in other langs. like Rust and Go), but it's difficult even coming up with conventions.
I'd counsel anyone considering event sourcing to use more "low power" solutions like audit logs or soft deletes (if really necessary) first if possible.
I find the complexity to still feel awkward enough that makes me wonder if deleted_at is worth it. Maybe there are better patterns out there to make this cleaner like triggers to prevent deletion, something else?
As for the article, I couldn't agree more on having timestamps / user ids on all actions. I'd even suggest updated_by to add to the list.
Something like a loan could live in a production environment for well over a year after closing, while an internal note may last just a month.
Deleting rows directly could mean you're breaking references. For example, say you have a product that the seller wants to delete. Well, what happens if customers have purchased that product? You still want it in the database, and you still want to fulfill the orders placed.
Your backend can selectively query for products, filter out deleted_at for any customer facing queries, but show all products when looking at purchase history.
There are times when deleting rows makes sense, but that's usually because you have a write-heavy table that needs clearing. Yes, soft-deletes requires being careful with WHERE statements filtering out deleted rows, but that's a feature not a bug.
You might still want to show to those customers their purchase history including what they bought 25 years ago. For example, my ISP doesn't have anymore that 10 Mb/s fiber optic product I bought im 2000, because it was superseded by 100 Mb/s products and then by 1 Gb/s ones. It's also not my ISP anymore but I use it for the SIM in my phone. That also accumulated a number of product changes along the years.
And think about the inventory of eshops with a zillion products and the archive of the pady orders. Maybe they keep the last few years, maybe everything until the db gets too large.
SQL:2011 temporal tables are worth a look.
If you have no audit log(or a bad one), like lots of apps, then you have to care a lot.
Personally, I just implement a good audit log and then I just delete with impunity. Worst case scenario, someone(maybe even me) made a mistake and I have to run undo_log_audit() with the id of the audit log entry I want to put back. Nearly zero hassle.
The upside, when something goes wrong, I can tell you who, what and when. I usually have to infer the why, or go ask a human, but it's not usually even difficult to do that.
Should this be at the application code level, or the ORM, or the database itself?
* Who
* What
* When
* Ideally Why
Also you need to take into account your regulatory environment. Sometimes it's very very important that your audit logs are write once, and read only afterwards and are stored off machine, etc. Other times it's just for internal use and you can be a little more lax about date integrity of your audit logs.
Our app is heavily database centric. We push into the DB the current unix user, the current PID of the process connecting to the DB, etc(also every user has their own login to the DB so it handles our authentication too). This means our database(Postgres) does all of the audit logging for us. There are plenty of Postgres audit logging extensions. We run 2 of them. One that is trigger based creating entries in a log_audit table(which the undo_log_audit() code uses along with most reporting use cases) and a second one that writes out to syslog(so we can move logs off machine and keep them read only). We are in a regulated industry that gets audited regularly however. Not everyone needs the same level of audit logging.
You need to figure out how you can answer the above questions given your architecture. Normally the "Why" question is hard to answer without talking with a human, but unless you have the who, what and when, it's nearly impossible to even get to the Why part of the question.
I tried to push for using svn, rather than just making copies of our source code folders and adding dates to them.
My manager allowed me to use svn, but to make sure I also did things the proper way by making copies of the source code folders.
That's the current level of discourse around audit logs. Write down what happened using your data tables ... but write down what really happened in the audit logs.
At some point you should just lean into putting audit logs first (just like developers reach for the git first).
Doing this with pure 'hard' deletes is not possible, unless you maintain 2 different tables, one of which would still have the soft delete explicit or implicit. You could argue the full db log would contain the data for the former requirement, but while academicly correct this does not fly in practice.
Instead, just for the tables where you want to support soft delete, copy the data somewhere else. Make a table like `deleteds (tablename text not null, data jsonb not null default '{}')` that you can stuff a serialized copy of the rows you delete from other tables (but just the ones you think you want to support soft delete on).
The theory here is: You don't actually want soft delete, you are just being paranoid and you will never go undelete anything. If you actually do want to undelete stuff, you'll end up building a whole feature around it to expose that to the user anyway so that is when you need to actually think through building the feature. In the meantime you can sleep at night, safe in the knowledge that the data you will never go look at anyway is safe in some table that doesn't cause increased runtime cost and development complexity.
(It Is Probable That While Not Immediately Required The Implementation of Storage of Data In Question May Be Simpler Now Rather Than Later)
I've gone ahead and included additional detail in the acronym in the event that the clarity is required later, as this would be difficult to retrofit into a shorter, more-established acronym.
I do. Each one is 8 bytes. At the billions of rows scale, that adds up. Disk is cheap, but not free; more importantly, memory is not cheap at all.
[0] https://docs.stately.cloud/schema/fields/#metadata-fields
https://docs.python-arango.com/en/main/
over tables in Postgres that has a PRIMARY _key and a JSONB document field. The issue is that I have a number of prototypes I've developed with arangodb but the license is awful and I don't feel like I can either open source or commercialize any of them until I'm running on an open source database.
It's a fun project because I don't need to support everything in python-arango, in fact I don't even need to support 100% of the features I use because I am free to change my applications. Also it's a chance to make the library that I really want to use so already it has real integer and UUID primary keys.
I just added a feature to have the library manage _created and _updated fields not just because I thought it was good in general but it was a feature I needed for a particular application, a crawler that fetches headlines from the HN API. I want to fetch headlines right away so I can avoid submitting duplicates but I also want accurate counts of how many votes and comments articles got and that involves recrawling again in two weeks. Of course _created and _updated are helpful for that.
The logical conclusion here is to log the updates (and creations and deletions and undeletions and such) themselves:
CREATE TABLE foo_log (id,
foo_id,
whodunnit,
action,
performed_at,
column_1,
column_2,
-- ...
column_n);
This is a pretty typical approach for things like bookkeeping and inventory management (though usually those are tracking the deltas between the old and new states, instead of recording the updated states directly as the above example would imply).
Not a huge fan of the example of soft delete, i think hard deletes with archive tables (no foreign key enforcement) is a much much better pattern. Takes away from the main point of the article a bit, but glad the author hinted at deleted_at only being used for soft deletes.
For example: as a company aspires to launch its product, one of the first features implemented in any system is to add a new user. But when the day comes when a customer leaves, suddenly you discover no one implemented off-boarding and cleanup of any sort.
Actually erasing data is quite hard. Soft deletes doesn't add any new lies, they just move the lies to the upper layer.
Anyone who has worked at a small company selling to large B2B SaaS can attest we get like 20 hits a day on a busy day. Most of that is done by one person in one company, who is probably also the only person from said company you've ever talked to.
From that lens, this is all overkill. It's not bad advice, it's just that it will get quoted for scenarios it doesn't apply. Which also apply to K8S, or microservices at large even, and most 'do as I say' tech blogs.
That's true for any other good advice you may have heard of.
Data has its own life cycles in every area it passes through. And it's part of requirements gathering to find those cycles: the dependent systems, the teams, and the questions you need to answer. Mindlessly adding fields won't save you in every situation.
Bonus point: when you start collecting questions while designing your service, you'll discover how mature your colleagues' thinking is.
I'll show myself out.
Answering queries like how many of these were never updated? Or how many of these were never cancelled?
It needs to be enabled tho, and it has some problems (for example you need to remove data for legal reasons sometimes).
Reading this in thread is very frustrating with people reinventing the wheel and asking why it wasn't invented already:)