Django as a Database Documentation Tool: The Hidden Power of Model Comments with Ryan Cheley

Speaker 1: Good afternoon everyone. Thank you so much for coming to my talk. I'm really excited about talking about this. It's a simple but powerful tool that can allow Django to help how your teams work with data. I'm Ryan Cheeley. I am a member of the Django Commons Commons Admin team with Daniel, Lacey, Storm, and Tim. Hey Tim. Hi Lacey. Uh I've been a navigator in Jenga Not space a couple of times. Lillian gave an amazing talk yesterday. If you missed it, I would watch it. It was so so good. Week three or week I'm sorry, session five starts in about three weeks, so you do have time to sign up. And there are a couple of people who are wearing Janganot Space t-shirts in here that would probably love to talk with you about that.

Speaker 1: I'm also one of the maintainers of Django packages with Jeff and Moxudle. I've been working with Django for about seven years. Python for about nine. And as I was putting this talk together, this next number kind of blew my mind. I bless you. I have been in healthcare for 17 years. I do not feel like I should be old enough to have been in doing anything for 17 years, and this will become a little more relevant later on. And so why this talk? Well, I really believe in the importance of documentation as a great benefit for communication. So I'm going to go through a dramatized scenario. The names have been removed to protect the innocent

Speaker 1: of what might look like a typical Slack conversation in my organization. So a web developer posts in Slack, hey, I just pushed the new field Chad score to the patient risk model. Now this looks innocent enough, right? All I did was add a new field. But then the ETL developer says, hey, that's awesome. But I need to know what this field is for so I can document it because I have to document it per our protocol. And then a report developer says, hey yeah, and I need that field in the data warehouse so I can actually write and update a dashboard, and I can't do that until this is there.

Speaker 1: So the ETL developer has a need, the report developer has a need, and this is where the pain starts. So the web developer goes back and they consult their notes, they work with a business analyst, they find a subject matter expert. And it takes three minutes, three hours, three days. Thank you, SpongeBob SquarePants. Is my slide not it is not going anywhere. I am so sorry about that, y'all. Okay, goodness. There we go. All right. Woo. Ah, okay. And okay. So some amount of time, three three minutes, three hours, three days later.

Speaker 1: The web developer comes back with their answer. Why, it's the CHA2DS2-VASC Stroke Risk Score. How many people in here might know what that means? Right? I've been in healthcare for 17 years. I have literally no idea what this means. Well, I do now. So this is a pain point, right? We had to wait a while to get to this answer, and we still have no clue what this field actually means, why it's important So there's a real cost here. There are delays in our ETL pipeline. There are reporting develop the report developer was blocked on updating the dashboard.

Speaker 1: There was web productivity loss here as well as they consulted their notes, tracked down the business analyst and talked to them, tracked down a subject matter expert and talked to them. So it's not just a minor inconvenience, right? There's real loss here. So let's take a look at the current state of what a database table might look like from a create table statement. So if you were to look at it in Postgres, you would see something like this: create table, it's got an ID which is an integer, has blood score, which is also an integer. Q risk 3 value, which is decimal, contraindication flags, which is JSON, and our new field chad score. This is clean, it's functional, it does what it's supposed to do.

Speaker 1: But it's completely opaque. There's nothing there that if you only have database access, it would tell you what any of these fields are. I have no idea what QRISC III value is. Still. And I'm giving the presentation. And if you only have direct database access, you're not going to have any idea at all. So we've got these mystery fields. Right? It's the root of our problem. The database itself contains no business context, just these mystery fields. Okay, now we're gonna get the interactive portion of the presentation. How many people knew that database fields could have comments? Raise your hands.

Speaker 1: Okay. Alright. Keep them up. Keep em up. Keep them up. How many of you have ever seen them filled in with anything? Yeah, that's what I thought. Right? Couple couple y'all. Couple y'all. So there's a documentation gap here, right? We can all acknowledge that code comments are not the same thing as database documentation. And that if we do happen to put these things inside of some sort of a knowledge management system like a wiki, confluence, u-track, whatever. Those can get stale, right? You updated a field or added a field, but you forgot to update the documentation in the wiki. You have undocumented expertise.

Speaker 1: This is the just ask Sarah she knows approach. When you only have three people and only one of them is named Sarah, that might work. When you have thirty people or three hundred people or three thousand people and you have more than one Sarah, As we do in Django , the question becomes, which Sarah? Also, this creates a single point of failure. And in healthcare, anyway, there is a regulatory compliance. Our auditors want to know what this data means? And they want to look at it in the database. At least they have is the last six or twelve months, which has been a challenge. And I know some of you out might out there might be saying, but Ryan, we've got help text.

Speaker 1: That'll solve all of our problems. Right? If we just add help text to the chat score. This isn't a problem anymore. Oh, but it is. It's still a problem. This doesn't solve anything for us on the database side. Because help text, well, that is for our end users. It's UI-focused guidance. It's only avail uh visible on the front end or in um in the Django admin. If you're a database administrator who's accessing all this through PG Admin or some other tool, you may not have access to the front end. You may not have access to PG Admin. Data analysts and ETL developers are also

Speaker 1: similarly not going to know to go look there potentially. And so it's a good tool, but for the wrong audience. And just for those of you that have maybe never seen what help text might look like on the front end, we have a Little uh screenshot here to show us what the the Chad score is. And so I think we need to acknowledge that at this point that we have different stakeholders who have different needs Our end users want form guidance. They want to know what these fields, like what value should I put in there? The help text, yeah, that's gonna help them. Database comments, they're not gonna look at those. They don't care. Our web developers, they want field purpose.

Speaker 1: So the help text, well yeah, it could be helpful, but any comments in the database are probably going to be more helpful. It's going to provide more context Our DBAs want to know about the schema. They're not going to look at the help text. Something in the database will be more helpful. The auditors, from a compliance perspective, help text, maybe it's gonna help them, maybe not. But database comments, that's what they're looking for. And finally, our data analysts. They just want to have context around the database itself. The help text, again, not going to help them out, but database comments will. And so this is where the help text falls short. A team that's using a tool like SSRS, Power BI, Tableau, any of the uh many Python instances, the reason I'm referencing these specifically is because I work in a Microsoft shop and well this is what we use.

Speaker 1: Regulatory audits, our auditors are gonna examine the database directly and not the Django admin or the front end. Cross-team collaboration, as we saw in my example earlier, the ETL developers aren't sure it's going to delay data pipeline builds, all because there's not any field context. What can we do? Well in Django 4. 2 we introduced the most amazing feature in my opinion. DB Comment. And 4. 2 was released in April of 2023. Two years ago. Two years ago. And this is what it looks like. Now, um a quick note, this is not supported in SQLite

Speaker 1: because SQLite doesn't have comments on fields, but it is supported in the other uh uh SQL engines. How many of you all have used this feature before? One, two, three, four, five. Oh my god. Okay, y'all. Okay. I'm so excited. I'm so excited. Okay, this is awesome. This is awesome. All right, great. So So this allows us to put documentation directly into the database schema. This chat score, well yeah, it's the CHA2DS2-VASC stroke risk, which goes from 0 to 9, with a greater than 2 indicating anticoagulation considerations per the 2010 ESC guidelines. And this is going to be put directly into our database. And I mean, this is kind of clean in my opinion, right?

Speaker 1: It's right there in the model, it's gonna show up in the database, and all we had to do was add a dbcomment. Now, the magic happens with the migration. Migrations are magical to me. I wish that I fully understood exactly how they worked. I don't. I say uh python manage. py migrate and magic occurs. But what this does is it will put the comment directly into the database where anyone with database access can see it. And so we have a solution. Our documentation lives in the database. And this is what it generates from a SQL perspective. We have that alter table, which will add the column Chad score, but then it adds to that a comment on

Speaker 1: column that matches exactly what was in The DB comment. And as a result, anyone with cure querying access to the database can see this documentation And if you're using PG Admin, well now you all can say that you've seen a comment inside of the PG Admin area. Congratulations! Achievement unlocked. Now, before we had these mystery fields, right? This has blood score, Q risk three value, contraindication flags. And what we really want to do, we can acknowledge this is clean, it's functional, but it doesn't really tell us anything. Our ETL developers will have no idea what the Chad score represents.

Speaker 1: Is it a count? Is it a percentage? Is it a risk level? And let's be honest with ourselves here as web developers. Next week, next month, next year, are we gonna remember what it is? I don't remember what I had for breakfast. So let's start adding some DB comments. Come in. Got our self-documenting models here with the db comment. Again, this is what the uh the output looks like. With our Hasblood score, well now we can see that Hasblood is a bleeding risk indicator, with greater than three being uh a risk for uh uh indicating uh a high bleeding risk based on FDA guidance from 2019.

Speaker 1: And as a result, there's content for our GTL developers and our report developers who are looking directly in the database. And again, this is what the comment looks like. I think one of the more interesting things that you can do is with JSON fields. The thing about JSON fields is that they can have lots of different keys, and those keys may or may not be known. Now, if you start documenting your JSON fields with the keys, that can be very helpful later on. In this particular case, the contraindication flags are clinical contraindications per CMS-134B8. Again, I have no idea what this means. And the keys are warforn allergy, bleeding disorder, and pregnancy status, and they are all Boolean

Speaker 1: values. And why does this matter? As an ETL developer, I can make a decision now. I can say, hey, you know what? I can extract all of those keys into actual columns on my dimensions. And I know that they're always going to be Boolean values. And then that allows the report developer to not have to go mucking around in SQL into that JSON field to extract that data out. It's already there for them. It allows reporting to be done in a much easier way. And so here I go, again, inside of the comment Now, both of these features can work together, and I would recommend that they should work together. Because the help text you'll remember is for our data entry users, whereas the dbcomment is for our db users.

Speaker 1: And so I would say as a best practice, use both, because it serves two different sets of audiences. Great, so we've got something for our columns, but what about tables? Well, you'll be happy to know that in Django 4. 2 we got dbtable comment, which allows you to add a comment to a table. How many of you all knew that you could add comments to tables in pg admin? Many fewer hands. How many of you have ever seen it filled in before? Yeah. Yeah, we don't like our database friends very much, it seems, but that's okay. We can change that. We can change that. So now we can have complete context on the table level by adding a meta class to our model class that indicates

Speaker 1: What the table is for. In this particular case, we have a risk model that's actually about cardiovascular risk based on joint commissions PCO3, and the owner is the cardio team at example. com. So we have table level documentation. We know what this table is for, we know who it's important to, we know who to contact even. So suddenly there's all of this context there available in the database. And now you all have seen a comment inside of PG Admin and its filled in. Okay, great. So we now have full documentation for everyone by using dbcomment and db table comment. Now, if I ended it here, that would be all well and good.

Speaker 1: You know about this. But the next logical question is: okay, great Ryan, now what? What what should I do? And you can start today It's kind of late, so maybe start tomorrow. When you go back to work, audit your top ten confusing fields. Find those fields that have cryptic names or complex business logic, the ones that cause the most confusion. Write them down. All you gotta do is write them down. Step one. Take some time. Step two. Document them. If you do this well enough, you only have to do it once. So write it all down. Add business context, any calculation methodology that might be involved, any regulatory requirements that might help an auditor

Speaker 1: make it a lot easier. And then standardize. Make it a part of your code reviews. You could go so far as to start adding tests that fail if dbcomment isn't there. It might be a bit too extreme until you've gone all the way through, but you could. DB table comments can be a very similar process. Audit your tables. Find the cryptic names and complex business logic. Identify the owners. I have a funny example of a s of a of a table that I saw. The name of the table was SWPTABTPRNIB. Spoke with patient about

Speaker 1: um taking ibuprofen as needed. I wish I was making that up. Document it. Don't just keep it up in your head. Write this stuff down. Put it in your table comment. And then standardize. Again, make it a part of your code review process. Make it a part When you're done of some potential failing tests. So that if a new class is added that will generate into um into a table later on, that it will be caught. So hopefully I've been able to convince you of the power and usefulness of dbcomment and db table comment. And that you can make documentation a habit

Speaker 1: and not just an afterthought. My hope is that we can reduce confusion for our friends that only have access to the database. And remembering that documentation is not just about helping others, but it's about helping our future selves as well. Thank you so much. And you can find me online in these various places.

Speaker 2: Great job, Ryan. Thank you. Do we have any questions?

Speaker 3: Thank you for this great talk. I'm a big fan of documentation. So I'm wondering, because you mentioned like enforce this in that unit test or something. Is there is it worth building out like a linter kind of thing to fail your code if you don't have that field in

Speaker 1: I guess yeah you could. You could. I mean it really is going to depend on like the culture of your team and what's what are the types of things that you look for and want to enforce. This would be I don't think you'd want to start doing anything that extreme until you got towards the end of the process of documenting everything, because otherwise you're going to just have a whole bunch of failing tests and you can't ever push anything to production. But yeah, I think you have kind of some options there in terms of like a linting a linting solution or a a failing test solution. So yeah. Yeah. Great question. Thank you.

Speaker 4: Hi Ryan, thanks. That was a great talk. Um I was wondering it have you looked into The cases where like the db comment and the help text might be the same, like do you duplicate that? Have you found a way to like hack that in?

Speaker 1: Yeah, so In general, my experience is that they're not the same, but that could be just a very healthcare-specific sort of Solution? Um you know that uh in my my examples there, the the things that help text were especially with the Chad score was like, well, it's a risk score from zero to nine. But in the DB comment, it was more details about like where it came from and why it was important and where the the demarcation was between something that might be okay and something that's not okay. And that may or may not be something that you want on the front end of a clinical system, you know, but it just it kind of depends, I think. So there is some potential for some overlap there, but I'd say that even then you'd still want to have both of them there because Like the database folks don't have access to the the front end necessarily.

Speaker 1: And even if they do, which they might, have you ever tried to find the field that you're looking for on a front end? It's hard. It's real hard. Yeah, great question. Thank you, Tim.

Speaker 2: If there's no other questions, um one more.

Speaker 5: Thank you for for the talk. Um what your thought about um using db commons or table comments for other things than only just share with other people that interact with them. I don't know, maybe as a starting point for some type of documentation that Django can generate or

Speaker 1: yeah that's a great question. Um so with with my team we use um we use a a a tool by a company called Redgate that actually extracts out all of the the comments that are on the fields and on the table and generates into an HTML set of pages that anyone can go and look at. In terms of like what would a a a Django specific solution be I'd be super interested in that because then quite honestly we wouldn't have to pay for Redgate but you know it's a great tool it's a great tool but you know anytime you can save money yeah

Speaker 6: Do you have any thoughts about documenting the choices for choice fields other than just kind of write the text, do it the hard way?

Speaker 1: Yeah. I wish I had a better answer than it depends, but it depends. Um Yeah. It really becomes contextual. If it's just a binary yes-no, like maybe not so much. If there are check mock check boxes that allow for multiple fields, then knowing that a zero is, you know um blue and and a one is red and like that can be super important. And again, like I I kind of think about what would be the most helpful on any sort of output. Uh-oh. What would be helpful on any sort of output and then back my way in there? And so if there's a specific reporting need that's going to need to be able to translate those numbers into actual values, then yeah, I would document

Speaker 1: But sometimes they are more or less self-evident, and in those cases, probably not so much.

Speaker 2: All right, thank you so much. Uh that's all the time we've got. Uh please find Ryan if you have other questions in the hallway. Please leave Ryan.