In this show, Stephen and Chris get to talk about an internal technical detail we were improving in our database, which led to a public-facing feature for y’all. The idea is that all (most, anyway) database tables should have a deleted_at column. When you query against them, under regular circumstances, any rows that have a non-null value will be filtered out. This is the concept of “soft” delete. It’s not really gone from the database, it just behaves like it is. This has two clear benefits:
- Deleting is super fast, it’s just manipulating a single value.
- It opens the door for un-deleting things that perhaps were mistakenly deleted.
This approach can be slightly more work. You’ll need to offer a way to truly wipe data completely for users. You’ll probably want a reaper to clean up data that has been deleted long enough. You’ll need to comply with regulations about data deletion. You might have to build un-deletion features. You might need to be doing that filtering yourself throughout the app. It’s work, but it’s generally worth it.
In our case, because we soft delete everything on CodePen now, the Deleted Items area of Your Work now works for all Item Types (Pens, Collections, and Projects). Before, it only worked for Pens, and even there it was a little messy as it didn’t retain the URL slug or comments and such — now it does.
Time Jumps
Sponsor: Memberful
Memberful is a best-in-class membership software used by the web’s biggest creators. If you’re building a membership website for a client, Memberful handles the hard stuff so you can complete your builds quickly and your clients can focus on creating content while earning revenue with ease. It seamlessly integrates with popular tools like WordPress, Mailchimp, and Discord, so they can reach and monetize their existing audience. Memberful maintains GraphQL API, webhooks, and OAuth Single Sign on to make integration even easier.
Help your client monetize their passion. Get started for free at Memberful.com
Transcript
[Radio channel adjustment]
Announcer: Today, on CodePen Radio.
Chris Coyier: Hey, everybody. CodePen Radio 383. I'm going to call this, I guess, maybe just Soft Delete, as a thing. It should be pretty interesting.
It's actually kind of a new feature, so I guess the second time in relatively recent history where we've actually shipped something user-facing while we're so busy on non-user-facing things. But anyway, we'll talk about it. Mr. Stephen Shaw is with me.
Stephen Shaw: Hello.
Chris: Hey. This was kind of a result of a variety of people's work, and it stems from the same kind of place.
Remember you and I talked about -- what was the last thing -- bulk delete for assets.
Stephen: Right. Apparently, all I'm working on lately is deleting people's stuff.
Chris: [Laughter] But remember when we were like--
Stephen: Hope that's useful.
Chris: Yeah. [Laughter] Get out of our database.
It came together because we were doing API work anyway, and we were like, "What's this tiny change in an API? That's no problem."
The more interesting parts of that one was all the UI nuances to get right and feel good and work good and stuff. It was so funny. I've actually used it a couple of times, just myself. So, well done. Very useful.
But then, of course, I had three assets, and I'm like, "Man, I really need to duplicate all three of these."
Stephen: [Laughter] Now.
Chris: The one action we didn't put in is the one I needed. Oh, well. We'll get to that at some point.
But we were doing API work and thus made this, like, "Ah, we might as well slip it out," even though we've been trying to be so dedicated to not do too much distracting stuff.
This one actually required almost no distraction at all because it was almost hilarious how it just fell out as a feature without us really planning on it being a feature. Right? Okay.
Soft deleting is kind of a concept, isn't it? If you're writing a database application, a CRUD thing, the D in CRUD is delete. You could certainly write an API that just goes to the database and runs a query that deletes it. Any database supports that.
And so, you'd think, on your first crack at building an application, that's what you would do. You would offer it to users. "Oh, do you not want that recipe anymore? Do you not want that blog post anymore?"
Stephen: What else to get rid of?
Chris: In our case, do you not want that Pen? Yeah. You just run the delete and it's gone. The user asked you to do it, so you might be like, "Well, how can we improve that UX?" Let's throw a modal up for one thing to make sure they don't accidentally click that button.
Stephen: Yeah.
Chris: That would suck.
Stephen: Are you sure?
Chris: Are you sure? Nobody minds that on a deleting action, for the most part.
For a really important one, have you tried to delete a repo? You've got to type the name of the repo in GitHub and stuff like that.
Stephen: [Laughter] Enter your social security number.
Chris: [Laughter] Yeah. On CodePen we make you -- for a project, just because there tends to be a little bit more time and effort put behind those, I think we make you type out "delete project."
Stephen: Yes.
Chris: Not the name of the project necessarily, but just that exact sentence, and then you do it, just to prevent, essentially, Marie having to get emails of, like, "I deleted my..." or, like, "You did it!" [Laughter]
[Laughter]
Chris: Or something like that.
Stephen: It's very easy to point that blame at us when people manually delete something.
Chris: Yeah. I remember us doing this at Wufoo a million years ago. You had to type out the name of the form or something to delete it, and it really, really cuts -- people remember it. You have to use your brain for a minute. It's not just this clickity-click, absentminded. When you are forced to think about it, that helps the UX of it. It might get in your way a minute, but at least you didn't accidentally delete anything.
Then I'll put up the "And yet..."
[Laughter]
Chris: It still happens. Unbelievably, it still happens, even if you're using your brain, you might have buyer's regret and be like, "Ooh... You know what? I did think about it. But I really want that back."
[Laughter]
Chris: We knew that, through support requests and such over the years.
Stephen: Yeah. So, so many times people have written in about something that's gone missing that they want us to get back for them.
Chris: No promises there.
Stephen: If you don't have any safety nets then, yeah, that's not really something we can do.
Chris: There have been extreme cases in the past where we've been able to restore an old database and pluck something out of it. I am not promising you do that. If anything, I'd like to promise you that I won't do that for you.
[Laughter]
Chris: Because that's too much. You'd have to be -- I'd have to be doing that for - I don't know - the lead singer of Led Zeppelin or something. I'd have to really want to rub your shoulders to do that for you.
Anyway, here's another one, though, that you have to do is we put it in your activity log who deleted it and when. Just one more little protection that if somebody writes in and says, "What happened to this?!" we can be like, "Look in your log. It's right there."
Stephen: Yep.
Chris: You did it on this date at this time.
Stephen: You did it.
Chris: Yeah. [Laughter]
Stephen: This was you.
Chris: Absolutely.
[Guitar music starts]
Chris: This episode of CodePen Radio is brought to you in part by Memberful, which is an awesome piece of software to help you build membership-driven businesses.
Say you're a developer and your goal -- just to make this extra developer-y, let's say what you're going to do is build a website in order to sell an online course to other developers. So, we're a developer ourselves and we're building things for other developers.
How am I going to do that? How would I, Chris Coyier, build that? I'd probably spin up a WordPress site just because I happen to know WordPress, but that's a smaller consideration than knowing that Memberful has a really powerful integration through a WordPress plugin.
What that means is I can build the site any way I want. Have a wonderful homepage. Have sales pages. Have a blog. Have a podcast that I know I can protect through Memberful for members only. Have the video course page laid out with an ability to mark courses as done.
All that stuff, I know that I could build that in WordPress. But then I know, through Memberful, that I know that people will be able to sign up. I know I'll be able to make annual plans, monthly plans, one-time plans, and team plans and all this stuff that I need to do.
I know that I'd be able to lock down access to different parts of the site and put upsells there depending on whether people are members or not. I know I'll be able to have different levels, so I can say you get access to this if you're at this level, and these additional things if you're at this level. I know that I'll have all the tools I need as a developer to build the site that I want to build and know that Memberful will take care of so much of this stuff, emailing people, charging cards, and repeating charging cards, and that server-side protection of my content and my pages for these paid plans.
That's how I would do it. I would just use Memberful and use WordPress and know that it really would be a nice experience as a developer. Thanks for the support.
[Guitar music ends]
Chris: Okay, so that's that. Here's another one, though. This is where this is all headed with the soft delete.
Instead of just running a delete SQL statement to get rid of that thing, you can just... wait for it... not do that.
[Laughter]
Chris: Instead of just deleting it, you can add a field to a table called something like deleted_at and put a timestamp there, like you would for created_at and updated_at.
Created_at and updated_at are just standard SQL-y things that are just on so many tables that are just there for sorting reasons and historical reasons and charting and data reasons and all that stuff.
Put a deleted_at thing there too. Then all you're doing when you delete an item is just plunking that time in which it was deleted at in there. So, you know, a little white lie that it's actually deleted, but this is pretty standard practice and that's called a soft delete rather than a hard delete.
It means that you have a little more work to do that a lot of frameworks kind of support. But nonetheless, it's work to do that when you query for items, that if they have any value at all in deleted_at, they're filtered out from that query because you can't all of a sudden have people be deleting things and then having them show right up in their dashboard again.
Stephen: Yeah. It's essentially a tag that means don't show this item anymore.
Chris: Yeah.
Stephen: It kind of removes it from view. Then it's really to help keep that data around for a short period of time. It will be cleaned up and removed.
Chris: Mm-hmm.
Stephen: But we keep it around for just a little while so that if you do have that buyer's regret, you can go to a special area of the site called "Deleted Items" and pull that item back out.
Chris: Yes! Yes, which it's just a UX thing. It just saves you from really sad, bummer-y moments. You know?
Now, there are limits to what soft delete can do on purpose because let's say you just made it. Let's say you stored all soft deleted items forever. On CodePen, that's a loophole for a pro feature, unfortunately, because it could mean that you just keep all you private stuff in deleted. [Laughter]
It'd be a workaround for privacy, which is a little funny. But that could be the way that it was operated on. You know?
I should also point out that this is relatively new to CodePen that, way in the early days, we just didn't do this. Hence, the pulling old databases and stuff like that. But we knew, through support requests, that people really wanted essentially a trashcan.
We ended up not calling it a trashcan because that's a little like Click Wheel. People in different countries and stuff don't necessarily call it a trashcan.
Stephen: Recycle bin.
Chris: Yeah. In Windows, I think it's the recycle bin, isn't it? Whatever. We called it deleted items.
Items isn't my favorite word because it's kind of like an internal API kind of word for us, but we called it items because it could be a number of different things. It doesn't automatically mean Pens because we have collections. We used to have posts. We have projects. There are assets and comments and all sorts of things that theoretically could be an item.
Anyway, we called it deleted items. The ship has sailed. That's what it's called, and you get at it right from your dashboard, right where it says, "Your Work," in there and you're looking at your Pens and you're collections. It just says "Deleted Items" right there. You can click onto it even if you don't have any deleted items, and it'll just tell you that you don't have any deleted items.
Our very first time we made that, we did not use the soft delete concept. We literally had a separate table. We would pluck off your Pen, put it onto this separate table, which is a way of kind of trimming down the table size, although it's not the most effective thing in the world for that. Then if you wanted to put it back, we would re-pluck it up and kind of use an API to put it back in the table.
The sad part about that was that you lost stuff like your slug. You lost your comments. You lost your hearts and stuff.
At first, we were like, "Well, that's kind of the... You deleted it."
Stephen: That's the price you pay.
Chris: Yeah. We're getting your code back for you. But really, it was more of a microcosm of the technology we were using to restore your stuff.
How much easier is it to just remove the deleted_at thing from a field and then, "Wow! All your stuff is back. It's 100% okay"?
It's, of course, not quite that simple because we use concepts like denormalization in our database that has to propagate data around to different tables, and we've got to make sure that super-related things like comments that are probably on a different table are handled and stuff. There are all kinds of little callbacks and side actions and stuff that happened during this, so it's not absolutely trivial, but it's certainly easier than what we were doing before and a little bit more reconstructively beneficial.
Stephen: Yeah. It's way simpler than the previous approach. We were offloading data, like getting it as files and all this kind of stuff just to get it out of the database.
We loaded it on S3. Remember that?
Chris: At one point, we did put Pen data as flat files on S3. I don't know if that was -- that was maybe the very first iteration.
Stephen: Yeah.
Chris: Then it became a separate table - or something like that. I don't know. It went through iterations, of course. That's a little embarrassing even to think about now, knowing how soft delete is just like, uh, not exactly a new concept. Whatever.
But there are other concerns. Not to raise the beast or anything, but there are things like GDPR that it matters when you say you're deleting data that you're deleting data and stuff. Reapers have to be in place to be upholding your deletion promises and things like that.
And you have to be able to delete from deleted. If you want to make sure this thing is gone from the face of the earth, you have to delete it, go into deleted items, and delete it from there. A little bit like the trashcan on Mac OS or whatever where you delete a file. It goes into the trashcan. You delete it from there. Not a totally weird concept.
Stephen: Or you just wait three days and we'll clean that up for you.
Chris: Yep. It gets zooshed away.
Stephen: We'll come around, empty all the trashcans. Take them out to the dumpster.
Chris: Yeah. We're so nice to you. All right, so that was Pens, though. We did it for Pens first because, in an old sprint -- and this is probably, what, a couple of years ago--
Stephen: Yeah.
Chris: That we just had to scope down the work to get it out the door because we're big scopers around here.
Stephen: Well, I think it was related to how cumbersome it was. We were doing all that S3 export and all that.
Chris: Yeah.
Stephen: How are you going to do that for a collection? How does that even make sense? We isolated it to Pens, as most people just delete and manage Pens and wanted that data back. But now with this new setup of just soft delete flag in the database, that opened up the capability to do that with all the items.
Chris: Yeah, which you'd think we would have just jumped on right away, but again with the scoping and all that. That's why I mentioned all this API work right away is because, in some cases when we're porting and cleaning up and thinking about APIs, the time comes to do cleanup of your own messes. You know? Be like, "Oh, gosh. It's so weird how we do pagination one way here and pagination a different way here. Let's smash those together and make sure that it's cleaned up." But trying to stop yourself from doing so much that that's all you ever do is clean up after yourself. That's tricky, too.
But in this case, I think the gist of it is because we treat items as a kind of generic concept -- I'm sure in programming you can understand. You might have an item is a way to describe something generically. Then we might have a Pen, and Pen extends item but has unique stuff of its own. Projects extends item but has unique things of its own.
You'd probably want the deleted_at concept to be part of the overall interface for an item, not a unique thing for one item. That required a little work. You're like, "Let's just do it. Let's get that. Let's make sure deleted_at is a concept for all items and just do whatever migrations are necessary and API changes necessary to make that happen."
And so, I think Alex sat down and did that one day and left the PR up for a minute. You saw it and were like, "Oh, how interesting. I wonder if this means that soft delete," like our deleted thing, "how close are we to that working?" It turns out, it wasn't close. It just did work.
Stephen: Yeah, and that was totally unintentional on Alex's part to have the front-end client just already there, basically. It was originally more for our admin tooling to make sure that we could delete bad items and things and restore those if need be. And so, all that API work going into it, it wasn't really expected to be just ready to go for the client.
Chris: Yeah.
Stephen: But I think he forgot to add just the tiniest little flag of only return Pens for deleted items. So, when I went in to test it--
Chris: Right.
Stephen: --everything was working. I deleted a collection. It was in the deleted items. I restored it out, and it was great. I deleted a project, the same thing.
Chris: Yeah, if you write a query that just says, "Send me back all things with a deleted_at on it," and forget to -- not forget, but it didn't even matter at first to just scope it to Pens. All of a sudden, it was returning everything. You're like, "Whoa! Sick!"
[Laughter]
Chris: Your addition, you write in that same PR. You're like, "Well, why don't I go just change some in-app text where it refers specifically to Pens?" make it more generic, and then change the text of when you're deleting an item to remind you that, "Hey, guess what. Now you've got three days to restore this thing," when you didn't before.
Stephen: Yeah.
Chris: It just was a little--
Stephen: Alex was offline or distracted, and so I was just checking out the PR and added those things in there. Then he was like, "Wait a second. What?!"
Chris: Yeah. [Laughter]
Stephen: "This works? This is ready to go?"
Chris: Yeah because we actually made the decision to not do it. We weren't trying to ship a feature, necessarily. We were just trying to clean up the API and move on.
We knew we were close with the feature, but we were like, "Nope. Discipline. That's what we have around here." You know?
Stephen: Not me.
Chris: Yeah.
[Laughter]
Chris: But it was so close that, yeah, no, it was very much worth doing. So, the feature, to put a point on it, is just that on anything on CodePen -- well, not anything because assets don't really apply as an item (at the moment). Those are handled a bit differently. But Pens, projects, collections, those three; if you delete them, they end up in your little deleted items bucket and you've got three days to restore them. If you restore them, you just 100% of the glory of that item back. It's the same slug. It's the same hearts. It's the same if it was picked or not. It's the same everything. Complete restoration of that item.
You can delete more things and get them back. When you get them back, they're in the exact shape you left them in. That's live now in CodePen.
A tiny thing. It maybe will never come up for you but, hey, it makes our software a little better.
Stephen: Yeah.
Chris: Pretty solid. You know? Turns out having a good API is pretty nice.
[Laughter]
Chris: All right. Take care, everybody. Enjoy it. Go Pro. It's mandatory. Even though this isn't a pro feature, there are many that are. We'll talk to you next time.
Stephen: Bye.
[Radio channel adjustment]