Writing to the db is discouraged, but the API is very barebones, what do I do?

[rasheemo]

Hey guys, for example the Patient API doesnt include things like insurance info, their current status (in room, unconfirmed, etc) etc so I feel like I don't have a choice. What are the best practices when the API is not adequate? I really hate the idea of writing to the db and queries are more complicated than RESTful API, but i feel like i dont get the data i need from the services so i have to go to the db anyway.

[cmcgehee]

We understand that the API is limited in its utility. Because we have had instances where 3rd party developers have violated the integrity of the Open Dental database, we have begun to discourage directly writing to the database. Currently this is not more than a recommendation, but in the future we plan to add mechanisms to make it more difficult to write to the database. With that said, it is up to each developer to weigh their business needs and decide if it is worth the risk in putting programmer time into developing an integration that writes to the database which might not be viable long term. If you do end up writing to the database, it is encouraged that you use your own tables to keep track of things instead of writing to the Open Dental tables.

[jordansparks]

We will definitely be locking down the ability to write to the dangerous tables. So I would say it's more than a recommendation to not write to the db because it will break soon.

[rasheemo]

Hi Jordan, what would be a "dangerous" table example?

[jordansparks]

Anything having to do with finances or clinical records. I know that covers a lot. When we start clamping down, we will start with things like payment and procedure, and then go from there. The more heavily used a table is, the earlier will will address it.

[sammyp42]

When you start locking down tables, will you provide "safe" routines we can use to make updates? For example, I have a routine that updates writeoff using a percentage of feebilled. Running that as a backend job is extremely efficient. What is the recommended way of implementing such logic if the tables were locked for write access? Is creating a plugin for that kind of logic the only option? If so, that seems extremely cumbersome.

[ajhalls]

This has me worried as well.

[jordansparks]

You are "updating writeoff using a percentage of feebilled"? You're using an UPDATE query to do that? How are we supposed to maintain the integrity of the database if queries are being run that we have no control over? We waste an enormous amount of very expensive engineer time fixing databases that were corrupted by third parties, even those with good intentions. Let's say we lock that table down. What happens? Well, we can't stop you. So technically, what we're doing is tracking the changes, not blocking the changes. We haven't decided exactly what to do when we see changes. We would probably start with annoying warnings. The warnings could get more aggressive. We would never go so far as to actually block normal functionality. Since our problem is engineer time, we would probably also charge $150/hr to troubleshoot and fix database that was flagged as altered. We have no way of initially filtering out our bugs from your bugs, so we would probably need some guarantee of payment of something like $500 for just about any phone call. If it turned out to be our bug instead of your bug, the extra fee wouldn't be charged. I should point out that the fee would very likely be on top of the normal monthly fee and anyone not on support would not have access to this service. I suppose there are a few daring power users who would be willing to take this risk. I suppose we could turn off the warnings for them if they signed a form agreeing to be responsible for the consequences. But that's a liability for us because now we have a customer who will get into an argument with us when we ask for the $500 to fix something that he feels is not his fault. An angry customer is no fun for anyone.

[nathansparks]

I would add that editing appointment confirmation status for patient arrival, confirmed (which is what started the thread) is allowed with the API but I think it needs to be improved, because it was designed to match the FHIR specification, and instead we need to expand it to allow for more custom statuses (I will put more details back here once I understand why the allowed FHIR API appointment status changes do not seem to match appointment statuses or appointment confirmation statuses). In the meantime, changing appointment confirmation status is something we are unlikely to disallow, in fact it is a field that we have quietly allowed third parties to change for ten years without any negative comment. It is not really clinical or financial. Updating a write-off on the other hand (with or without a plugin) is probably something we would mark as externally modified data and possibly flag as bad data in the UI.
Reading data out is a fine idea, and writing data to new tables to add new functionality for a third party is great. I think 'putting data into'/'modifying data in' existing tables outside of the program is and always has been very dangerous, and creates liability because the patient record is now an amalgam of data entered by different software.

[nathansparks]

I have researched with our engineers at Open Dental and you can already set an appointment status (like scheduled) and appointment confirmation status. However, right now you are limited on appointment confirmation status to unconfirmed (like all new appointments) and confirmed (default in Open Dental is 'Appointment Confirmed', set in eServices setup). We are writing a feature (no timeline) to allow three other appointment confirmation statuses (arrived, seated, dismissed) that are triggers for the waiting room. All other confirmation statuses are custom and do not do anything in Open Dental and so cannot be set by API as they are not defined from one instance of Open Dental to the next by preferences or definitions.

[sammyp42]

I agree, doing updates can be extremely dangerous and if I was a software provider, I would absolutely hate it if customers modified data through unsupportable means. I love the idea of better audit logs and integrity checks, especially for billing related tables. I've only don't direct updates on a very, very limited basis... mostly as an experiment an also to save a few minutes of time, but it's not anything I do with any level of frequency. I like the idea of maintaining separate tables and will explore using hooks to reconcile activity... makes sense.

Any plans to expose account / payment related information through the FHIR API?

[Justin Shafer]

Hmmmm..

[tlavelle]

Hello - I have a related question.

I would like to be able to make two very simple updates to the patient table (patient.TxtMsgOK and patient.Email) programmatically. The update queries that I would use (and have tested out on a demo database without any noticeable problems) are:

UPDATE patient p SET p.Email = '' WHERE p.PatNum = {patnum}; #clears out the email address
UPDATE patient p SET p.TxtMsgOK = 2 WHERE p.PatNum = {patnum}; #sets TxtMsgOK to 2
UPDATE patient p SET p.TxtMsgOK = 1 WHERE p.PatNum = {patnum}; #sets TxtMsgOK to 1

Obviously in the above, '{patnum}' is what would be replaced in order to update only certain patients' accounts. I have seen the warnings regarding writing to the database directly, so before doing that, wanted to get feedback from anyone on this board as to whether you foresee any problems with the above. Ideally I would use the API, but it doesn't appear that those two fields are editable by the API (as far as I could tell).

Any feedback would be appreciated. I did check first with the OpenDental Query Request team and they suggested I post here for feedback.

Thank you.

[jordansparks]

I can't quite figure out why you would want to delete someone's email address, but those do seem pretty safe.

[tlavelle]

Thanks.

(to answer your question, we delete emails outright when someone tells us they no longer consent to receiving any emails from the practice)

[pid_user]

Since our problem is engineer time, ...... An angry customer is no fun for anyone.

Agreed and May be 3rd Parties can work in collaboration with OD with open communication and keep the innovation going from both sides.

Few ideas to make things better for OD and 3rd Party software guys who are invested with OD as it being Open Source.
May be have a open discussion with 3rd Parties
OD to list out risk area
OD to share known issues so mistake doesn't repeat
Best practice - backup OD before any 3rd party installs and do some quick tests