Relationship credential is missing for one side of a relationship

[swcurran]

This issue occurs with OrgBook BC.

See entities:

• https://orgbook.gov.bc.ca/entity/FM1043109
• https://orgbook.gov.bc.ca/entity/LP0847867

The first is a "DBA" for the second. The issue is that while the relationship is included in the first, it is not included in the second, but should be.

Please investigate to determine if this is a one-off that we can fix, or if it is larger.

Once the issue is understood, and if there is an issue, please check about whether or not the audit should/could catch this.

Thanks!

[swcurran]

Hey @ianco -- can you take an initial look at this?

Thanks

[ianco]

Hey @ianco -- can you take an initial look at this?

Thanks

On it!

[ianco]

On first glance, the company FM1043109 was added April 10 (and posted to OrgBook).

The last update for the owning company LP0847867 was March 26.

Since the two companies are updated separately, and the relationship credential is included in that update, there was nothing triggering the update to LP0847867 when the new DBA was added. I'll need to dig into the code to see what should have triggered this update since I thought this scenario was handled ...

[swcurran]

In the meantime, can you please trigger the processing of LP0847867?

And of course, the big question — are there other instances of this?

[ianco]

In the meantime, can you please trigger the processing of LP0847867?

Yep will do

And of course, the big question — are there other instances of this?

I'll check OrgBook to see if there are any more mis-matched relationships (there should always be 2 credentials for each relationship). Checking COLIN/LEAR vs OrgBook to see if there are any completely missing relationships will be a bit more complicated ...

[swcurran]

I’m less worried about both missing than 1, since that seems to be the issue here.

[ianco]

Company LP0847867 is re-processed and is showing up correctly.

Couple of issues came up:

• When we re-process a company, it affects related companies (not sure exactly, but I had to re-process all DBA's when I reprocessed the parent company)
• DBA's are now in LEAR, so when they are originally posted from COLIN and then need to be reprocessed from LEAR the process is a bit more complicated

There are other companies in OrgBook with the same situation (only one relationship credential rather than 2) but the queries are running for a long time due to the number of records, so it's going to take a bit of time to figure out the number of companies impacted. I suspect it will affect any DBA's that have been created since we switched over to the LEAR database for SP's.

[esune]

There are other companies in OrgBook with the same situation (only one relationship credential rather than 2) but the queries are running for a long time due to the number of records, so it's going to take a bit of time to figure out the number of companies impacted. I suspect it will affect any DBA's that have been created since we switched over to the LEAR database for SP's.

Is this something we will need to account for when we work through the changes to accommodate the BC Registries database updates?

[ianco]

Is this something we will need to account for when we work through the changes to accommodate the BC Registries database updates?

Yes for sure, handling the relationships was a big issue when we switched the SP's over to LEAR (and is still an issue apparently) so we'll need to pay attention to how these are working in the new data model.

[ianco]

Based on this query in the OrgBook search database, there are quite a few companies in the same (or similar) situation:

select tr1_topic_id, tr1_related_topic_id,
  t1.id, t1.source_id,
  t2.id, t2.source_id
from (
select * from (
select 
  tr1.topic_id tr1_topic_id,
  tr1.related_topic_id tr1_related_topic_id,
  tr2.topic_id tr2_topic_id,
  tr2.related_topic_id tr2_related_topic_id
from topic_relationship tr1
full outer join topic_relationship tr2
   on tr1.topic_id = tr2.related_topic_id
  and tr1.related_topic_id = tr2.topic_id
order by tr1.topic_id, tr1.related_topic_id
) as related
where (related.tr2_topic_id is null
   or related.tr2_related_topic_id is null)
) as unrelated,
  topic as t1,
  topic as t2
where t1.id = tr1_topic_id
  and t2.id = tr1_related_topic_id
order by tr1_topic_id desc;

The relationship exists for the DBA but not for the owning company, same as the reported issue with LP0847867.

However some of the records from the above query are the opposite situation (relationship is on the "owning company" side but not on the DBA.

And there were also a few records that looked like "false positives" (when I looked at the company in OrgBook it looked ok).

I suggest we re-queue all these companies for reprocessing, which should pull in any missing relationships. We'll need to pause the regularly scheduled jobs and manually process these companies @WadeBarnes @swcurran @esune

I can write a query to post the records to the event processor database to queue the companies.

Once we've re-processed the companies I'll re-run the query to see if there are any still outstanding, or what other issues we may have.

In the meantime I'll look into the BC Reg Agent code (and possibly the underlying COLIN and LEAR data) to figure out how we are missing these.

Regarding the audit, we can audit either on the orgook side (baed on the above query) or by comparing to the source data. I'll have to do the investigation on the agent side first before we can decide ...

[swcurran]

Can we assume that the problem stems from a relationship being established by one side of the relationship, and that not triggering the processing of the other side? For example, in the case in question, the DBA was added with a relationship to the BC Company, but the BC Company was not updated, so the new DBA didn’t show up. Is that the hole in the process, or do you think it is something more?

[ianco]

Can we assume that the problem stems from a relationship being established by one side of the relationship, and that not triggering the processing of the other side? For example, in the case in question, the DBA was added with a relationship to the BC Company, but the BC Company was not updated, so the new DBA didn’t show up. Is that the hole in the process, or do you think it is something more?

I think there is more than one problem.

For the reported company, I think the "DBA" was added in LEAR, and the relationship was added, but then nothing triggered an update on the "owning company" side of the relationship. (In COLIN we trigger updates off the event table, and in LEAR off the transaction table. Just off the top of my head.) So even though the relationship was added, nothing triggered the BC Reg agent to re-process the "owning company". (When we manually re-queued the company the relationship was posted so that indicates the underlying data is ok.)

There are a bunch more cases like this (that I'm currently looking into) but there are other scenarios as well. I haven't had a chance to look into all the scenarios yet. Possibly something similar the other way (a sole prop is converted to a DBA but we don't get a triggering event to fire off the credentials).

Once I've had a chance to do some investigations it prob makes sense to circle back with the BC Reg team to confirm exactly what are the triggering events that we need to look for to kick off credential processing.

[ianco]

Here's another example - https://orgbook.gov.bc.ca/entity/A0131501

It's an ex-pro company that looks like it was replaced in March 2024 - there are (at least) 2 "historical" records for this company with different business numbers. There are a bunch of DBA's and some relationships point to the "historical" version of the parent company (and not the current one).

[ianco]

Fixed one issue in BC Reg issuer, once this is promoted into prod I'll do another pass at the OrgBook relationship cleanup and verify if there are any other issues.

In the meantime I'll look into beefing up the audit script.

[WadeBarnes]

The changes in bcgov/von-bc-registries-agent#351 have been deployed to test.

[ianco]

Fixed a few problems with the BC Reg issuer (mainly dealing with how data is split between the 2 databases) and one problem with how companies are re-queued for processing. Added support to the audit job for testing mis-matched relationships (I am still testing the audit updates).

I've gone through OrgBook and done a cleanup. There are still a few stragglers, which may turn out to be issues with the source data ...

[ianco]

One more issue fixed in BC Reg - bcgov/von-bc-registries-agent#353

This affects a couple of the "missing relationships" in OrgBook, but there are a few others that look like data issues from the source systems (LEAR vs COLIN), I think inconsistent info between the 2 systems, but I need to follow up (I will do this next week!)

[ianco]

All of the known processing issues have been fixed, there are a few stragglers that I think are due to data issues (inconsistencies between COLIN and LEAR) and I'm following up on those

[ianco]

Now fixed.