Using database triggers to reliably track model history with Wes Kendall

Speaker 1: Everybody. Yeah, so Max is over there. He's gonna be doing the second part of this and uh Yeah, so uh today going to be talking about using database triggers to track model history. It's tracking history is like this timeless classic problem that doesn't really pertain just to Django, pretty much pertains to any sort of web application or any application that's using a database. And there's quite a few Django apps out there already that aim to solve the problem of tracking an audit log or tracking changes to models. So people are probably familiar with some of these and You know, today we're going to be talking about a very similar concept, like still solving the same problem, but solving it using database triggers. So But really there's going to be two main takeaways from this talk today.

Speaker 1: First, uh just going to be talking about a new approach for history tracking. What does it even mean to track history with database triggers? Like what does that look like? I'm gonna go over what a trigger is for those that aren't familiar with database triggers. Just going to be talking about like some of the fundamental concepts. Why is it valuable? Why is it different than the other approaches? How can it potentially benefit you and your applications? And then Max is going to talk about other interesting design patterns that may not be immediately obvious with uh you know what you can do with a history tracking solution. So um we are gonna be talking about Postgres triggers specifically and like going over a library called Django PG history. So if you use MySQL um you won't be able to use this library

Speaker 1: but The uh the fundamentals of the approach are still the same. It's just triggers are implemented like slightly differently across different databases. The link to the library that we're going to be talking about is Django. Uh the Django BG History link is is under This link, so I'm the main maintainer and creator of the Opus 10 open source group. There's quite a few other libraries there if you want to check them out. And uh again, so I I'm Wes. Uh Max is over there, he's gonna be coming here. We we both work at standard metrics, uh, so heavy users of Django heavy users of Django PG history and um and using triggers uh for stuff. So just to jump right into things though, let's let's just declare a simple model. So we have my model up here. It has an integer field. It has a char

Speaker 1: field. Let's say that we create this model. uh integer field is two at the beginning, char field is high. Let's say we update the integer field to four and then we save it. And you know going back to kind of this timeless problem of history tracking, like the the question That uh all these apps and like what PG History tries to answer is like how do I track these changes? And um It was around 2018 or 2019 when I was looking into the history tracking landscape and all the apps and it like a lot of this stuff just wasn't really suitable for the needs that I had at the time at this organization I was at just like all sorts of um just traffic and and we needed to reliably track um changes and so so I started to go down the rabbit hole of okay well how how could I

Speaker 1: track history in a more reliable manner and and I you know quickly saw it's a giant iceberg. It's nowhere near as simple as it seems. And I I think the two questions you have to ask first, and these are kind of the two questions that all those libraries I showed answer first and foremost is how and where do I store all these historical changes. And you know you'll see some some people their first inclination is going to be I'm just going to track the audit log in JSON. Or I'm going to have one singular table that has the changes in JSON. And if you want to easily query this data in your application, it can be Kind of difficult. I mean if you want to use it just as an audit log to like look at the changes, that's great. But you need to easily query it. If your tracked model, if the structure of it changes, if the types change, um it it's

Speaker 1: it's kind of hard to migrate it if if you do need to do that. So uh an another approach is uh you have your table that has like an integer field and a char field. Another approach is like make a structured history model. So make a history model that also has an integer field and a char field. And kind of the rabbit hole you go down there a lot of times is you end up having a bunch of different event models or history models. associated with your models that you're tracking. So you got to keep those in sync. And uh it's it's just uh you know it's it becomes complicated to to even answer these simple questions because it depends on your app. It depends on what you're trying to do with the history. Kind of the second question though is how how do I reliably detect changes? And this was the big holdup that I had. Uh

Speaker 1: A while ago when I was like looking into history tracking stuff was just around reliability. Um kind of the first inclination that people do or that that apps do is they they use Django signals, so pre-init, post-init. pre-save, post-save, pre-delete, post-delete. Django has the ability to like link into the model lifecycle, whether it's you know actually instantiating the object or whether it's you know, saving with the database and you you you can use Django signals to get like a pretty high fidelity of like all the changes that are coming through to a model, but many bulk changes like bulk create dot objects dot update, many of those don't fire signals or or really file fire the same types of signals and there's inherent race conditions too with using uh signals so you know you have your

Speaker 1: model in the application, it changes, signals fire, you you collect those changes there and you persist them to the database, but other changes might have gone on during that time. So there's just like these subtle race conditions that can happen if if you're using signals. And similarly to, you know, you might override the save and create methods, or you might um require people to decorate their methods that have history tracked. And again, When we're talking about reliability and just making sure that nothing ever slips through the cracks, uh you you have to ensure that people go through the right interfaces. It it can be kind of difficult. to have it work performantly across bulk operations and all this other stuff. And similarly to like one thing that I I think is is always harder are third-party models. So imagine you install an app where you don't own the code, you don't have access to

Speaker 1: to change the models easily, uh like tracking changes and those can also be kind of difficult if you're reliving relying on overriding save and create methods. So PG history in a nutshell. The way that it works, at least from a library perspective, is you install Django PG history, you bit you add it to your installed apps like any other app And your model, so going back to our original model definition, you just import it, you just do pgehistory. track. And that's literally all you have to do. And once you have done this, what happens underneath the hood is uh the changes aren't tracked to some sort of model with JSON. There's actually a new model that's created entirely, and it's it's your event model for for the model that's being tracked. So a new model is created. When you run mic make migrations, you actually see this model appear.

Speaker 1: And then any change you do to this model is is tracked automatically. So let's say let's say again going back to our example, we create the model with an integer field two, change it to four, we save it. PG history, so that event tracking model is made. You have you have a reverse relation called dot events now on your object, and you can see every value of that integer field and it's it's a native integer field in the database. So we can see it was it was two originally when it was created and now it's four. And you know if you want to see all the fields you can you can see char field was never changed. Um so along with those fields that are being tracked, there's also metadata fields in these event models. They're all prefixed with PGH underscores, so like PG history. Uh so like the the time it was created is tracked.

Speaker 1: Th there's quite a few other metadata fields that that we'll go over that are useful, but kind of the premise here is that you know your events are stored in the structured model. You can query them in your application. So if you need to show history and Do things, uh index that data, access it performantly. You can do all that stuff uh pretty easily. So just just revisiting a little bit uh like okay, what just happened here. Um When you decorate a mod model with PG history track, it creates a completely separate model with like the exact same structure. So all the fields are there. They're named the same thing. There's additional metadata though, so like it actually creates another model called my model event. Same structure as the original model. It's almost like you're inheriting it, and then you're adding a couple metadata fields.

Speaker 1: So PGH object points to the main model. So you have you have your main model here, you have a totally different event log over here pointing to your main model with all the fields Um and again like the the thing to keep in mind is like you're not writing this model in your model step pie. It's just generated dynamically. So you can still import it like it's a model. Django still sees it as a model. You don't actually have to go create this though if If you go update my model, if you go add a new field, if you change the the char field to have a different max length, uh all that stuff is reflected in your event model. They are both migrated whenever you Change that original model. So all this stuff can be overridden and configured. I'm just kind of talking about the default behavior here of PG history. And then after that event model is made, after you've migrated it, triggers are installed on that model that's being tracked.

Speaker 1: And I'll go into what a trigger is, but Triggers are are installed on it. And then every time that model is inserted or updated, those values get snapshot to that event table. So you have this log of all the events. What values that model had at that at that point in time for those operations. So brief overview of triggers here. I won't be able to go into it too heavily, but think about database triggers conceptually like this. You have your application on the left, you have your code, you have your Django code. It runs there in Python. You know, you might call model. objects. create, you might call. save. Django is creating SQL and then it's issuing that SQL to the database. So the database is hosted somewhere else. And triggers are installed in the database. So triggers don't run in your application.

Speaker 1: It's not like it's Python code that runs. Triggers actually live in your database and so you you attach them to your tables and PG history does that automatically. So it attaches a trigger that watches for inserts, a trigger that watches for updates. So every time a SQL operation comes through and it tries to uh edit your main table, a trigger there will add the the snapshot to that event table. So nothing falls through the cracks. um you you have like a level of reliability that again nothing falls through the cracks here. So um PG History uses another library called PG Trigger to manage the triggers. So I I did a talk on it a few years ago, um DjangoCon 2021. So For people that want to like learn a little bit more about triggers and how they work and how you can even you know manually put triggers on your models, uh go to the library there, like the the talk is linked and like a tutorial and

Speaker 1: all that fun stuff. So But that's triggers at a high level, like that's what they mean. They're installed in the database, they run by the database. And as a result, so again, simple example here, base configuration, as a result, every change is tracked in like a structured model You can query it in your application really quickly, easily. The model is changed. If the tracked model changes, you see it in your migrations. And your application requires like no real changes. So you can do dot objects. create. bulk update. You can modify data any way your heart desires. You don't have to remember to go through a particular interface to have your history tracked. It's just tracked. behind the scenes reliably. So uh imagine someone goes into your database in a SQL terminal and they edit stuff, your history would still be tracked

Speaker 1: is kind of like the the the key one of the key takeaways here and that that's why triggers It can be such a powerful way of uh tracking history. Just um it's it's a very reliable audit log um that that runs in the database itself. So I've talked about the library at like a high level. I've talked about how it's uh works by default. There's all sorts of ways you can configure it. Um I'll I'll put the link to the library at the end though. There's like a lot of docs on how you can like really customize uh how it works, but I am going to blast through just a couple frequently asked questions that typically typically come up when I tell people about PG history. First of which is like what if I don't want to track every field on my model? You have the ability to specify which fields you want to be tracked. If you specify one field like we have here, your event model will only have the integer field.

Speaker 1: It won't have the char field. And if any changes happen to that char field, it won't automatically trigger an event. You have to change those fields that are being tracked. So simple way to track only specific fields. Uh if you don't want to fire an event for every insert and update, you can override that behavior too. That's just the default behavior. You can provide trackers to PG history and you can be very fine grained about what events you actually store. You you don't have to use it just to store snapshots on models. If for example You want to store an event every time my model is created and the integer field is greater than four. You can specify event trackers to this uh level of granularity. And Max is going to go into some cool examples of how you can use this to make interesting event logs about

Speaker 1: things that happen in your application. But again, you can make uh and and tailor All sorts of event logs. You can customize the underlying event model if you want. You don't have to let PG history create it for you. You can still define it in your model. py. You can add indices. to the fields if you want to query them in a certain way, like um you you have the you have the flexibility to do that with PG history. And um We often talk about like a reliable audit log. You can go a step further with PG History and prevent people from tampering with your audit log. By just putting append only is equal to true. So what that means is that your event model itself, it'll have more triggers installed on it. And if anybody tries to edit or delete it, it'll just result in an error in the database.

Speaker 1: So it's like further protection. that your audit log is like a a true uh immutable type of audit log. Like you have to go really far out of your way to ever be able to uh tamper with this audit log or Make some sort of history that is uh actually not accurate. Uh there is a default admin integration. I know that's like one of the kind of the key uh parts of these these other libraries. Uh you have the ability to show the entire event log across multiple event models. So keep in mind here when you when you track multiple models, there are separate event tables. You can aggregate them all together in the admin. You can even query them all aggregate. in an aggregate manner with some proxy models that it has. You can also make admin pages specifically for a model and just show its events as well.

Speaker 1: So you B basic admin integration. I would say the admin integration isn't like one of the killer features of this thing, but but it's there if you want to use it. And um Finally, I'm gonna go into more detail about this because this is this is a fundamental part of the library, and it's kind of like the last fundamental part I'm gonna talk about. But Biggest burning question is uh, well, I'm tracking all these events, I'm tracking all these changes. How do I associate the change with the logged in user? that uh hit a button on the admin or the the user that um was the authenticated user of an API request and you can do uh all this stuff too. with uh PG history context tracking. So again, uh think about this way again. So database triggers are writing all the events. You have your application over here though with its authenticated users.

Speaker 1: You know, you might wonder how on earth do I share data from the application with those triggers so that it can associate those events with metadata and and you can you can do that. Um Backing up a little bit, looking at our model definition again, like our event model that's generated for us, one thing I didn't say at the beginning is that there is another field on it, another metadata field called PGH underscore context And it's a foreign key that's on every event model by default. And what it does is it automatically points to a centralized context table. So if you want to add free form metadata to your events, you can. And it also allows you to group a bunch of events together. So I'll I'll show I'll show what this means and how it can be valuable. Again, this is what the context model looks like in PG history.

Speaker 1: It's just Very simple one UUID, just the global ID of the context, and then a freeform JSON field of key-value pairs that you can add and that you can associate with events. So Let's imagine we have two different models, model A, model B. We're we're tracking them both. Again, they they have they have both separate event models. In order to use context tracking with PG History, you go into the context manager and you you add whatever key value pairs you want. To associate with any events that happen within that context manager. Here, some key is the key, value is the value, and I'm creating model A and I'm creating model B. And after these models are created, you know, there's going to be two insert events that are going to be created, and they're going to both point to the same context.

Speaker 1: So here just showing how you can grab the event. Grab the context, grab that key, and its value. Underneath the hood, again, the database tables look like this. You have two different uh event tables, they both point to the same context. So core pieces of context tracking again if if there are a thousand modifications that happen within like a web request, you can insert, you can go into the PG history context manager before the request. You can make all these modifications. They're all grouped together because they all point to like the same context and you can add whatever metadata you want, such as like the logged in user. And Max will go into some examples of that. I'm just uh talking about how it works at like a fundamental level, like what what this actually even means.

Speaker 1: And before we go into like some More uh interesting design patterns and stuff like that with PG history. One just final note about context. So every everything in PG history is just set up to be uh uh built with performance in mind. So when you go into PG history context, if you add it, if you nest it, you can nest it as many times as you want. It's not making queries or anything like that. You don't have to worry about it accidentally making a thousand queries. When you go into the context manager, it just aggregates data in like a global type of variable. And anytime the modifications do happen That metadata is sent alongside the SQL as a variable. So like that's kind of how it all happens underneath the hood. And when those triggers fire, they just associate the metadata with the events. So it's Uh pretty pretty simple how it actually works, but there's all

Speaker 1: there's a lot of magic going on behind the scenes to attach that SQL and to make sure that you don't accidentally Blow up uh your API request with a ton of queries because you were uh adding a bunch of context. So that's just that's kind of how it works. You you don't have to worry about additional round trip database queries. Um So with that being said, uh Max is gonna do the second half of this talk and um just gonna be going over some interesting design patterns. That you can do that may not be totally obvious with like PG history or just history tracking stuff in general. I think you'll be going a little bit over the context tracking stuff again. So yeah, this is Max.

Speaker 2: Thanks. Hey everyone. Uh Max, you know, work extender metrics with Wes. It's kind of funny. The original reason we actually met was because you know I was trying to solve this history tracking problem act standard metrics. And so that's originally why I reached out to Wes and and how we ended up working together. But I'm going to go over some cool design patterns that you can you sort of achieve with PG history. I think there's a lot of things in this library that are really achievable outside of your standard uh audit logs for tables, right? There's a lot of other cool things and want to make sure you guys know about them. um and can um you know maybe come up with some other cool ideas uh and ways you can use PG history. Um so the kind of first one is going to be default context tracking, right? So what do I mean by this?

Speaker 2: So, you know, as Wes just went over, you know, you can instrument this context tracking, you know, throughout your application, right? So just use this context manager. um you know pat you know as a decorator or as a context manager um and you're really able to associate any data you want in this context table with events um but there might be a lot of other places in which you want default you know context tracking right So maybe within the lifecycle of an API request or within a salary task or a base management command. And you want to understand how those events were created, right? I I've seen this before, right? Where you maybe you're creating an object in a variety of different ways. Maybe there's some bulk process action in a task. but it can also be created within an API request and you want to understand how it originated. Um this can not only be useful for maybe you know finding ways to kind of represent that state on the front end of your application, but also maybe just for debugging purposes, right?

Speaker 2: You want to understand, okay, the the object originated from this task and or from this management command and you can do that. So Um, you know, one of the cool things that PG history actually offers out of the box is uh just default contacts tracking, you know, via middleware. So there's this installable middleware you can you can just add to your settings um and by default this will just then for any events created within your api request will add in the user id in the url um So quite easy to get this set up by default, right? You know, obviously there's additional things that you don't want to track, you know, uh in your middleware you can sort of create your own custom middleware that just uses PG history, but if you just sort of want to base, you know, user and and and you know web request URL, you can get that quite easily. And you know, as kind of Wes alluded to earlier, this will aggregate all the events together for the for this kind of context table that's being created, right?

Speaker 2: So we're we're gonna be able, it's not just we're not gonna sort of have a context per event, but rather context for all the events created um you know within these API requests, right? So kind of going over that example I mentioned with salary, right? Like what if we want to instrument base uh context tracking within a salary task, right? Um so you know, let's say we're overriding the base salary task, right? We can just sort of add in, you know, the context manager here and grab the task name. Um and then that's gonna easily let us, you know, you know, regardless of whether we're using uh PG history not within Cellar Task right, any events created in Cellar Task will then be um associated with uh context for the task right. And kind of a quick example with this, like let's say we just have some task that processes some type of payment order, right? So let's assume all these objects have event tracking enabled, right?

Speaker 2: So order, payment, shipment. They all have kind of the base PG history tracker. Sort of what we have now is this ability to understand that all of these objects were created from the same task, right? So these are all these all these sort of event event instincts are going to foreign key back to the same context instincts. And we're going to be able to understand that these all came from process order. And um, you know, kind of going into sort of this further, um it's also possible for us to stack context tracking, right? So maybe there's some additional data you want to grab in the seller task. Maybe for example, the user ID, right? So let's say the seller task is being kicked off from inside uh you know an a an API request and we're passing in the user ID who kind of initiated this. Um we can also just add that in. And what's going to happen is instead of two context instincts existing, these are going to get aggregated into the same context, you know, table instincts.

Speaker 2: So that way we don't, you know, you you can sort of You don't have to worry about extra queries here or sort of any performance implications. It's really easy. You can you can stack this context as as much as you want and really not worry about it. And it's all sort of going to be aggregated cleanly for you. And so yeah. Kind of going into another design pattern that can be quite useful is this ability to go back to previous events, right? So I'm going to walk through an example here of sort of a use case in which you might want this, but I'm sure there's there's a lot that you guys can think of Um so let's say you're building a CMS, right? So something like WordPress or or some type of you know admin you know application where you want users to basically have this ability to go back and see all edits they've made to blog posts, right? So Users are creating, editing, managing blog posts. And you want to be able to just uh go back and and and visualize those addicts and and and and and see sort of how uh the post has progressed, right?

Speaker 2: So we sort of have this simple table here for a blog post. And we just add a history tracker onto it. And as simple as that, right? Now what we effectively have are fully captured updates and creates. And we can really understand all the blog events that sort of occur. So every single edit, you know, to the content, but also any of the other fields. are going to be tracked here and uh we're gonna then be able to surface this information to users however we like and and and easily have that with really just a single line of code. But you know, what if we want to give users the ability to then revert back to one of these previous blog posts, right? Um how can we do that Well, uh what's super cool is actually the base event model. So basically every single, you know, events are going to kind of be derived from this model. uh

Speaker 2: has a revert method. So um you know this revert method will basically return the original uh object type that this event is for and we'll let you know sort of revert based on the chosen event right So this is pretty cool. So what we can do here is go ahead and add, you know, implement our own revert method on blog post, just take in the version ID. We sort of the events is the default reverse relationship name for all the associated events And then we can just call this method and there you go. We're sort of gonna do uh we're just gonna update the existing instincts and then we'll we'll sort of have that block post reverted to its previous state um sort of while maintaining this this full audit log of of all the edicts still. So this is pretty powerful to set up. You know, we can even create a simple, you know, DRF API for this, right? Just just call the method on on post ,

Speaker 2: just grab it, and and there you go, right? Sort of with with sort of less than 15 lines of code, you've effectively set up full history tracking for these blog posts and also the ability to go back to historical blog posts. And um sort of one caveat here is obviously you want to make sure that you're tracking all of the fields for the table. Um as if if there's anything missing, you're you're not going to be able to go back and revert. So Make sure you're tracking all the fields and you're not excluding any if you want to use this functionality. Another cool thing I'm going to be talking about here is soft deleting models, right? So this is something that I've I've actually used PG History for that initially I didn't think of as sort of a main application or something that I'd want to use it for, but I ended up finding it quite useful for this use case for a variety of reasons that I'll go into. Uh but to kind of like talk about soft deleting Django

Speaker 2: models in general, uh maybe it's a problem you all have seen, but it it's kind of a tricky problem to solve, right? There's there's sort of this um naive approach which is you kind of use a simple Boolean flag to indicate whether a model is being soft deleted. And this works, but it but it has its issues as you can assume. So let's say we have blog posts, right? And let's say we want to self-delete this model, right? So maybe one we want to give uh users the ability to restore deleted blog post, right? And we can just have it in his deleted field. It defaults to false. We override the delete method. And instead of doing an actual delete, it you know does a save and just sects this. this fill to true, right? So um, you know, at this point, right, we can then just go into the base man sort of the man override the base manager, create our own blog post manager, um, and override git query set, and then you know just filter on

Speaker 2: sort of non-deleted blog posts, right? And this works in practice, but it kind of has several issues. Um one is like when we're filtering, we're not going to sort of uh exclude default objects when filtering on reverse relationships, or just generally when we're we're creating a a query object, right? We're not It's one of those things where you kind of it's up to developers to remember to filter on deleted objects every single time. Um and and this may not be a big deal if you're working in a small code base, but for for larger code bases, this can be uh a little bit it it can be sort of it you can have gaps, right? Um is I guess what I'm trying to say. Um and then also sort of you're you're just not not gonna get proper cascading deletes, right? That's that's just another issue as well. So um Kind of nothing and also nothing just stops us from hard deleting the model and getting around this delete method, right?

Speaker 2: Um so we could do a filter and then a delete um or we could um you know just sort of write raw SQL and delete the object. So there 's nothing really stopping us from I mean we're making it harder, right? And we can just kind of try and patch all the wiz in Django that you could delete it. But at the end of the day, it's still deletable. So yeah. So PG history, as Wes chatted about, is actually built on a library code Jingo PG trigger. And there's this kind of interesting middle ground approach that's still based on triggers, but sort of lects us. Take this field-based approach to filtering out objects that works in my opinion a lot better. And and solves one sort of one of the issues around cascading gleaks. Um so the sort of the way I'm I'm not going to spend much time on this, but the way Django PG trigger works is you can basically set up triggers in the class meta. So here PG trigger

Speaker 2: actually is like a built-in soft delete trigger. And we just specify the field that kind of represents whether the model is soft deleted. And we tell it to, you know, say, hey, flip it to true, right, upon a delete. Um and so this sort of what will happen now is whenever the model is deleted, um, regardless of how it's deleted, no matter what delete method you're using Django, whether you're writing raw SQL, this field will get flipped to true, right? Because it's being set up by trigger. So this is Not selling in your Python code. This is sort of happening at the database level. And so this first of all solves that one problem I mentioned here about making sure you're always flipping this field. And then it will also sort of um you know you know another cool thing this will do is they'll actually cascade delete other models, right? So if there's any model foreign keying to blog post , this trigger will still for you sort of have that model be cascade deleted, which solves that sort of problem that we saw there.

Speaker 2: Um, you know, but we're still lived in a state where you do have to filter out the reverse relationships, which depending on on your use case, may sort of be inconvenient. Um and you know This is a difficult problem to solve and and one I I tried to solve. You know, there are ways to do it kind of you you can on sort of traditional reverse relationships, it's there there's sort of a way to to maybe work around it, but like it's not it then becomes harder to get the deleted objects if you ever want them. And then it's it's nearly impossible to do it on on kind of uh you know Q objects and and whatnot. So Um it's really sort of a difficult problem to sort of work through. Um and kind of like I was just saying, right? So every single time, whether through like a for a direct foreign key or a reverse relationship. you we're actually just going to be including deleted objects here, right? Which is not what we would want and maybe not what other developers would

Speaker 2: expect unless there's kind of this established pattern of soft leaks in the in sort of the code base. And yeah. And so sort of store using PG history to solve this problem actually can work quite well because what we're gonna end up doing here is storing the soft deleted model in a different table. So we're not worrying about filtering out these objects or anything like that. And it also solves all the sort of problems around cascading deletes. So I talked about how you know using Django PG Trigger we can cascade other m cascade delete other models. But the model itself can still not be cascade deleted because it just the deletion status is being represented by that field. So you just made basically no foreign keys that live on blog posts. can lead to a cascade lead of blog posts. You just need to make sure they're like set null or or or sort of protect.

Speaker 2: But with this approach with PG history I'm about to go into, we don't have to worry about any of those concerns. We can just The table sort of operate almost as as if it wasn't being soft deleted, but you're you're still gonna get soft delicts. Um so kind of now here what we're gonna do is actually add a second tracker. Um and this is because we want to store these, you know, these uh This is in a separate table, right? So we can just add a second tracker here for a deletion event. And we'll specify the model name as just deleted blog post. And so now whenever this blog post is deleted, you know, we're just going to store the deleted instincts and delete a blog post. Um and this will sort of fully you sort of solve all the problems around cascading deletes both ways. So this model itself, like if the author gets deleted, this table will just get cascade deleted and we'll create a deleted blog post. So no restrictions on on sort of how you set up your foreign keys, no restrictions on models

Speaker 2: foreign key to foreign keying to this, and you never have to worry about filtering out those deleted objects, right? You can only still get them if you want by just querying on the separate table. But yeah. So and you're still going to have access to the sort of primary key on that table if you want to filter on it by that. But Um, you know, as I just basically just what I mentioned here, right? You know, cascade leaks that work both ways and that ability to make sure we're always excluding soft-related objects. And now we can even create a simple endpoint to restore a deleted blog post, kind of like we had with reverting to previous blog post. We can just sort of you know grab a deleted blog post. Revert does a update or create, right? So in this situation, we're just gonna you know be creating uh a blog post here, right? A new one. Um and then we're just gonna we can serialize this and return the data. And there you go, right? Like once again with sort of uh

Speaker 2: Less than 15 lines of code, we've sort of been able to now have soft leaks on this table with you know a lot of these issues mitigated and also be able to go back to deleted instincts, right? So you know this hypothetical uh you know content management sort of system now has sort of a lot of this functionality that traditionally would sort of be uh difficult to get to. Um so another the next thing I'm gonna go into here is kind of like use cases around foreign king to event models. Um and this is useful because you can really be helpful to understand. A model at a given point in time without having to just you know add additional fields to that table to understand it for you know at at a higher granularity Um so kind of like an example I'm going to talk about here is like let's say that we have some maybe some f fantasy sports application, right? And so we're tracking like football games.

Speaker 2: Um so we have a home team and an away team. And we want to like we also have another table that effectively tracks the win-loss record uh for a given team in a season. Okay. And um, you know, we want to understand the win-loss record for each team for a given game. Um so I mean what what can we do here, right? Um well obviously you could just denormalize the data onto the table. I mean that will work. But what if we just want to keep everything, all the information within this table, right? We can just add a history we can just add history tracking here, right? So for inserts and updates, we're starting to have history tracking now. Um and now we can just foreign key to this table to understand the win-loss you know ratio at a given point in time, right? So we're not we don't have to we can keep all the data on this table, we don't have to denormalize everything. Um and whenever we query from this table, we can get the most up-to-date

Speaker 2: you know, win-loss uh ratio you know for a season, but um we can also sort of understand it at any historical point in time, right? So th this is sort of a pretty useful use case of of that. Um and kind of uh, you know, and and as I just said, right, like the alternative would maybe you could just track the w the sort of win-loss, you know, record for a season at like a You could just store it for, you know, you could just have it, you know, directly point uh you know to the game or just have it have a date field on it. But with this, you don't even have to worry about that, right? You can just store it every single time it changes and and link it with uh the given game So this is this is pretty powerful. And sort of the last thing I'm going to go over here is kind of application-specific event logs and kind of going into some of that custom, you know, history tracking that Wes mentioned and some useful applications of this.

Speaker 2: So sort of an example I'm gonna give here, right, is sort of let's say we have a bank account model, right, that has a balance. Um what if we want to send out an email to users if their bank account balance goes below zero, right? Some something that uh a lot of banks are gonna are gonna want to do, right? Um so what we can do is just add in a history tracker um and we can sort of have an update event on the condition that the balance is less than zero, right? Um And we'll store this in a table called overdraft event. Okay. So every single time any count goes below zero, this this is what will happen, right? And uh let's say if we want to understand the sort of state of whether an email was sent out, we'll just have this kind of uh simple table that will one-to-one with an overdraft event. Basically, if this exists, it means the email was sent out. And now what we can do is just have a seller

Speaker 2: task that filters on these events that don't have uh this object, this sort of one-to-one object existing. Um and then we can send out those emails and then create the object. Right. And so what we have here now is sort of a almost like an eventually consistent type of task that will sort of, you know, if there's if there's no emails to send out, it's just gonna be a no-op. But if there are Uh you're sort of always going to ensure that you have those all sent out. And the kind of alternative here would probably be to just, you know, you could maybe instrument some like custom logic, right? Like maybe in the save method or something. where okay if if the balance goes below zero you like send out an email using salary or or maybe i mean you you probably want to do it asynchronously right um but the issue there right is like what if the salary pod dies what if there's some transient error Um then you have to go into Sangrid or whatever you're using for managing your emails and then okay then see that that email wasn't sent and then see about getting it resent with this, even if the salary

Speaker 2: pod dies or uh there's some transient error, then you know this task can just rerun. You know, we never would have sort of created that email sent object. Um and we sort of understand, right, that you know we sort of have a uh we have a we have a good understanding of of what emails are sent and which were not. So Um this is sort of a, you know, but even just beyond this, right? Just being able to understand these events is useful for a lot of other things outside of you know just sending out emails, right? Um so some some really cool stuff we can do with kind of this conditional tracking. Uh but yeah, that's basically it. You know, uh huge thanks to the DjangoCon organizers, um, you know, as um what's coming up here, but you know As we mentioned, you know, we both work at a company called Extended Metrics. But if you're uh want to contact us, we have our personal emails here. Um and if you want sort of to see Django PG history or also Django PG trigger, uh go over to Opus

Speaker 2: 10 on GitHub and you can see both of those repositories there.

Speaker 1: I was gonna mention too that we'll answer questions out in the hallway as well.

Speaker 2: Yeah. Awesome. Thank you, everyone. Thank you.