Looking for ideas on eliminating data synchronization feedback loop

[mandmdiet]

I'm a developer working on an Open Dental integration. Two of the things our application does with the Open Dental API are: 1. Imports notes for a claim for viewing. and 2. Allows a user to create a new note.

When a new note is created in OD for a claim our users are working to send, we import that note into our application for viewing. When a user creates a new note within our application, we first save that note into a local database and then use the Open Dental API to write the note into OD. The workflow for this is:

1. Create a new note locally with no OD key reference
2. Create the new note in OD and receive the new OD key for the note
3. Write the OD key for the note into our local record.

We are doing this because one requirement from our client is that a user must be able to add a note even if the eConnector is down for some reason. If the eConnector is down we will write the note back to OD as soon as it is back online, but in the meantime the user sees the note because it is stored locally as well.

One issue we're trying to solve with this process is sometimes our process that finds newly created notes in OD and imports them into our application executes BEFORE we get to complete step 3 in steps outlined above. When this happens we get a key violation on step 3 because we have a duplicate OD key. There are a lot of ways to handle this including deleting one of the duplicate records, but most of them are not great solutions. One thing we'd love to be able to do is to exclude records from our import process if they were created (or updated) by our application.

Is there a way for us to determine in OD if a record was most recently updated by the user associated with OUR API key? If that's not the case, is there either a place or field in which we can store data about records we've updated or created that our importer can use to exclude those updates? This question isn't just about note updates, we would need a solution that works for other records as well, such as claims and patients. Really to be certain we wouldn't run into an issue doing this across our application we need a solution that could work for most or all OD database updates. We could store a hash in our database that represents the latest known version of data and compare that to updates ore creates from our import process and that is our backup plan, but as fields change in OD or in our application that would have to be continually kept up to date, so the maintenance is a problem there and the possibility for bugs is very high in that case.

[justine]

We are doing this because one requirement from our client is that a user must be able to add a note even if the eConnector is down for some reason. If the eConnector is down we will write the note back to OD as soon as it is back online, but in the meantime the user sees the note because it is stored locally as well.

One option is to look into utilizing the API Service. This would run all OD API requests locally and avoid remote verification. There is, of course, validation that has to occur, but the validation is less frequent than remote API requests.

One issue we're trying to solve with this process is sometimes our process that finds newly created notes in OD and imports them into our application executes BEFORE we get to complete step 3 in steps outlined above. When this happens we get a key violation on step 3 because we have a duplicate OD key. There are a lot of ways to handle this including deleting one of the duplicate records, but most of them are not great solutions. One thing we'd love to be able to do is to exclude records from our import process if they were created (or updated) by our application.

Every claims GET request returns SecDateTEdit. This field holds a date and time the last time the claim was updated/modified. You could use this value to validate against the last time your application made a change.

Is there a way for us to determine in OD if a record was most recently updated by the user associated with OUR API key? If that's not the case, is there either a place or field in which we can store data about records we've updated or created that our importer can use to exclude those updates? This question isn't just about note updates, we would need a solution that works for other records as well, such as claims and patients. Really to be certain we wouldn't run into an issue doing this across our application we need a solution that could work for most or all OD database updates. We could store a hash in our database that represents the latest known version of data and compare that to updates ore creates from our import process and that is our backup plan, but as fields change in OD or in our application that would have to be continually kept up to date, so the maintenance is a problem there and the possibility for bugs is very high in that case.

There is no way to verify if a record was modified by OD or the API, within the table itself. This is by design. OD API honors OD UI functionality so both work seamlessly together. One thing that you can do is check the audit trail. This is done by via the API by using GET securitylogs. This would only return logs that were made using your API keys. A claims PUT request would be logged with the permission ClaimEdit.

Here's an example of what that request would look like:

Code: Select all

https://api.opendental.com/api/v1/securitylogs?PermType=ClaimEdit

[mandmdiet]

Thank you for the thoughtful response. I wasn't aware of API Service before so I will look at that and I will also look at the securitylogs, depending on what data is available there they could be just what we need!

[mandmdiet]

I'm wondering if my developer API key needs to be modified to have permissions to access the securitylogs resource because I've triple checked my request and it looks good, but the OD API is telling me that the resource doesn't exist. Do I need to make a request to have my API key updated for this?

GET /api/v1/securitylogs?PermType=ClaimEdit HTTP/1.1
Authorization: **masked**
User-Agent: PostmanRuntime/7.32.3
Accept: */*
Host: api.opendental.com
Accept-Encoding: gzip, deflate, br
Connection: keep-alive

HTTP/1.1 404 Not Found
Cache-Control: no-cache
Pragma: no-cache
Content-Length: 39
Content-Type: application/json; charset=utf-8
Expires: -1
Server: Microsoft-IIS/10.0
X-AspNet-Version: 4.0.30319
X-Powered-By: ASP.NET
Access-Control-Allow-Headers: *
Date: Wed, 30 Aug 2023 21:19:43 GMT

"securitylogs is not a valid resource."

[justine]

I'm wondering if my developer API key needs to be modified to have permissions to access the securitylogs resource because I've triple checked my request and it looks good, but the OD API is telling me that the resource doesn't exist. Do I need to make a request to have my API key updated for this?

GET /api/v1/securitylogs?PermType=ClaimEdit HTTP/1.1
Authorization: **masked**
User-Agent: PostmanRuntime/7.32.3
Accept: */*
Host: api.opendental.com
Accept-Encoding: gzip, deflate, br
Connection: keep-alive

HTTP/1.1 404 Not Found
Cache-Control: no-cache
Pragma: no-cache
Content-Length: 39
Content-Type: application/json; charset=utf-8
Expires: -1
Server: Microsoft-IIS/10.0
X-AspNet-Version: 4.0.30319
X-Powered-By: ASP.NET
Access-Control-Allow-Headers: *
Date: Wed, 30 Aug 2023 21:19:43 GMT

"securitylogs is not a valid resource."

Good afternoon mandmdiet,

securitylogs GET was implemented in version 23.1.33. Are you updated to that version or newer?

[mandmdiet]

Oh, I didn't think to look at the version. You're probably right if that's a fairly recent release. Thanks for your help again.

[mandmdiet]

Hi again Justine,

Can you tell me what this call is returning in terms of time scope? Is it showing everything the current user has ever done through the API for the permission type specified?

Also, since this only returns data about usage for the current user there wouldn't be any way for me to know if the logged information represents the most recent modification to an object, correct? For example, in a test database, I have this showing in my report.

{
"SecurityLogNum": 872106,
"PermType": "ClaimEdit",
"UserNum": 0,
"LogDateTime": "08/30/2023 6:46:35 AM",
"LogText": "Updated by Developer through API.",
"PatNum": 4614,
"CompName": "SOME-NAME",
"LogSource": "API"
}

This tells me that on 8/30/23 at 6:46 AM some action using the ClaimEdit permission occurred and it was in some way related to patient 4614, correct? I don't think this even tells me what object was being changed does it? I'm guessing a claim was updated belonging to patient 4614, but I can't tell. It also is entirely possible this record has had further updates since 8/30/23 at 6:46 am, so I can't use this to know if the most recent update was one from us either I think, right?

[justine]

Hi again Justine,

Can you tell me what this call is returning in terms of time scope? Is it showing everything the current user has ever done through the API for the permission type specified?

Also, since this only returns data about usage for the current user there wouldn't be any way for me to know if the logged information represents the most recent modification to an object, correct? For example, in a test database, I have this showing in my report.

{
"SecurityLogNum": 872106,
"PermType": "ClaimEdit",
"UserNum": 0,
"LogDateTime": "08/30/2023 6:46:35 AM",
"LogText": "Updated by Developer through API.",
"PatNum": 4614,
"CompName": "SOME-NAME",
"LogSource": "API"
}

This tells me that on 8/30/23 at 6:46 AM some action using the ClaimEdit permission occurred and it was in some way related to patient 4614, correct? I don't think this even tells me what object was being changed does it? I'm guessing a claim was updated belonging to patient 4614, but I can't tell. It also is entirely possible this record has had further updates since 8/30/23 at 6:46 am, so I can't use this to know if the most recent update was one from us either I think, right?

Good morning mandmdiet,

It tells you every action that was done via the API on that customer's database, ordered by most recent action.

What you are seeing in your return is a Claims PUT log for PatNum 4614. It is possible that other changes were made within Open Dental itself, but this is the most recent update that was made via the API, using your credentials.

[mandmdiet]

OK thank you

Do you think it would be reasonable or possible to implement a feature whereby when a record in OD is updated, it's recorded by whom and when so that in the future we'd be able to do a simple query and know whether the record we're querying was updated by our user or by another user? I know the last time a record was updated is already stored, there's just no way to know who updated the record at this point if I'm understanding everything correctly.

[justine]

OK thank you

Do you think it would be reasonable or possible to implement a feature whereby when a record in OD is updated, it's recorded by whom and when so that in the future we'd be able to do a simple query and know whether the record we're querying was updated by our user or by another user? I know the last time a record was updated is already stored, there's just no way to know who updated the record at this point if I'm understanding everything correctly.

Hi mandmdiet.

I think the way what you're describing would have to work, would be to add an additional column to every single table in Open Dental, which is currently not feasible. What you can do, is take note of the SecDateTEdit on the GET, then run an additional GET and compare. Additionally, you can write a simple query and use ShortQuery to determine changes in a way that's best for your particular use case.

[mandmdiet]

You're suggesting I compare SecDateTEdit to a date we have in our own system, right? With the ShortQuery suggestion, you're still only talking about looking at SecDateTEdit and the security log, right? Since those are the only two relevant pieces of data in this scenario?

[justine]

You're suggesting I compare SecDateTEdit to a date we have in our own system, right? With the ShortQuery suggestion, you're still only talking about looking at SecDateTEdit and the security log, right? Since those are the only two relevant pieces of data in this scenario?

Correct. Run an additional claims GET and see if the SecDateTEdit time has changed since the initial GET. If so, someone updated that claim. (OD, other API dev)

You could use ShortQuery to see all logs, but securitylogs were not intended to be used in this fashion and do not contain exacting information. (PK to what's changed, etc.)