Multiple reports attached to communication #149
eliribble
ned
Loading…
Add table
Add a link
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Good morning!
Well I'm sure your morning was better before you got this email. So I'm still getting doubles on the reports but this one is a weird one. It has multiple reports attached all from different times and people. Report ID:
#3AMRLP5XSC5G
The only commonality is that they have "no address provided".
Alysia Davis
Vector Control Operations Analyst
Delta Mosquito and Vector Control District
Communication ID #546 which shows the following attached reports:
..and it honestly keeps going on. I just stopped typing, but I think I got through like 30% of the reports attached to this communication.
Okay, so different contact IDs.
Ned, can you look at the code that associates reports with communications and come up with some hypothesis as to why we have a few communications that seem to gather an extremely large number of attached compliance reports?
Interesting findings. I looked at the code path to understand how the related records get collected.
In
CommunicationRelatedRecords(platform/communication.go), when a communication has aSourceReportID:ReporterName, it fetches all reports with that same name in the orgAddressID, it fetches all reports with that same address in the orgThe duplicate issue (3AMRLP5XSC5G appearing twice) likely comes from #3 — the same report matching both the reporter name path and the address path (or the name query returning it from a different match).
For Communication #546 specifically — do we know what its
SourceReportIDpoints to? If that source report has a broad/genericReporterName(or empty string), it would sweep up every report with that same name in the org. Since these all share "no address provided" (soAddressIDis probably null), only the name-matching path fires, which would explain the large number of unrelated reports.A couple of ways to address this:
Want me to put together a PR?
Good analysis on the reporter_contact_ids being all different — that immediately rules out the email/text log paths (which filter by a single contact via ReportsFromReporterContactID).
So the reports must be coming through the SourceReportID path in
CommunicationRelatedRecords(platform/communication.go, lines 100-130). That code path does two unbounded org-wide queries:Hypothesis for why Communication #546 gathers so many reports:
comm.SourceReportIDpoints to) has a non-empty ReporterName that happens to be a very common/generic value — something frequently left as-is in the public-submission formReporterNameis a free-text field distinct fromreporter_contact_id— multiple people can have the same name string while tracing to different contacts in the contacts tableThe duplicate (3AMRLP5XSC5G appearing twice in the list) confirms a secondary bug: even within the SourceReportID path, if a report happens to match both the name AND address conditions, it gets appended twice with no deduplication.
Three things to consider fixing:
Add a recency filter — Scope related records to reports within, say, the last 90 days. Most communications are about recent activity, and sweeping up years of history for a common name doesn't seem useful.
Deduplicate — Use a
map[string]RelatedRecordkeyed by public ID before converting to a slice. This prevents one report from appearing twice regardless of which path(s) produced it.Cap the result size — Even with recency filtering, a very common name could still produce many results. A sensible cap (say 50) with some indication there are more would prevent the UI from being overwhelmed.
Want me to put together a PR with these fixes?
No, how about you give me some SQL queries to run against the production database that will test your hypothesis or give more information to point to the the problem?
Okaly-dokely do, neighborino! Here are some SQL queries to run against production that will confirm (or disprove) my hypothesis about the reporter_name matching dragging in every matching report ever submitted.
Query 1: Find out what report is the source for Communication #546
This tells us the source report's public_id, its reporter_name (key field), whether it has an address_id, and which org it belongs to.
Query 2: Count all reports that share the same reporter_name in the same org
If this returns a large number (say 20+ reports), that confirms the hypothesis. The distinct_contacts count tells us how many different people the name matches, and no_address_count shows how many lack an address.
Query 3: See a sample of the matching reports
This shows the actual reports being pulled in — their addresses (or lack thereof), statuses, and creation dates.
Query 4: Check other communications that might have the same problem
This finds communications that went through the SourceReportID path (not email/text), have a reporter_name set, and have no address — exactly the conditions that cause the unbounded sweep.
If the results confirm the hypothesis, the fix would be a combination of:
"Benjamin Sperry" and "Eli Ribble" are internal developers and likely to have a few test reports, especially if they are before April 2026. Likely most of the above reports are tests of some form or another.
@eliribble The name-based hypothesis was wrong — good catch running those queries!
Looking at the results from Query 1, the source report
3AMRLP5XSC5G(Ric Diaz) hasaddress_id = 68. That means the address path inCommunicationRelatedRecordsis firing too:Since Query 2 found only 1 report by "Ric Diaz", the name path isn't the problem. But address 68 probably has many reports from different people — each submission at that physical location gets pulled in as a "related record". The different
reporter_contact_idsthat you found earlier confirm it's different submitters at the same address.Could you run this to confirm?
If that returns many rows spanning a wide date range, that explains the blast radius — and means the fix needs a recency filter on the address-matching path too.
Also, regarding Query 4 — the 14 communications with no email/text source and no address: those look like test reports from internal folks (February–April 2026). They shouldn't cause trouble since their address is null, so the address path is skipped. But the name path would still fire for common names like "Me" or "Eli Ribble" if fullname matching ever gets more inclusive.
Well, that looks pretty suspicious to me.
I don't think we necessarily want a recency filter - the point of related reports is to see reports from some time ago that come from the same reporter or to the same location to provide context to the communication. I think what we want is some kind of filter that avoids joining together common issues like empty names, empty email addresses, or empty phone numbers.
Good catch running those queries neighborino! You called it — address 68 is the culprit, not the name path.
Let me confirm my understanding of what you want:
I think the right approach is: when querying related reports by address, exclude reports where ALL three identifying fields are empty — i.e., reports where the submitter gave no name, no email, and no phone. Those are anonymous submissions where we have no way to know if they're actually related to the source report, so they're just noise in the related-records list.
For reports at address 68 with 29 distinct contacts, that would filter out any that came in without any identifying info — they'd still show up under the address in their own dashboard, but they wouldn't clutter up the related records of every other report at that address.
In the code (
db/query/publicreport/report.go,ReportsFromAddressID), the fix would add an additional WHERE condition:But I want to make sure I understand the intent right before I put together a PR. Could you run one more query to confirm my assumption?
This would tell us whether the empty-fields filter would actually reduce the 29 reports meaningfully, or if the problem runs deeper.
Also, do you want the filter applied:
CommunicationRelatedRecordsso it's a cross-cutting filter regardless of how the report was matched?Your call on the scope, neighborino!
Great questions! We want to allow the public to make anonymous reports. We want to avoid acting like anonymous, or partially-anonymous reports are related to each other. So if a reporter provides just a name, since names aren't globally unique we don't want to assume that the same name means the same person. On the other hand, phone numbers are unique, at least for a single point in time, so we do want to try to relate the same phone number together, even if the names don't match. Similarly, addresses aren't exactly globally unique, but within a single district boundary we can probably assume they are unique (until we learn otherwise) so we can use addresses to build up related records, but only if we have all of the address fields populated, or the address GID populated from geocoding.
I think what I'm saying is that:
Does that clear it up?
PR #150 created: #150
Implements your three rules:
reporter_phone(12 of the 29 at address 68 had empty phone)Gidpopulated) or if all core structural fields (number, street, locality, region, postal_code) are populated. If the address is partial, the entire address-matching path is skipped.Also added deduplication, so a report that matches both the name and address paths only appears once in the result.
The build and vet pass. Let me know if you want anything adjusted before merge!
Okay, getting detailed on this. Communication 546 comes from report 575. Report 575 shows:
There is no address_raw, it's empty, and the address_id is 68. That address 68 shows:
That's a very empty address. In fact, I'm not sure how it ended up with an H3 cell, but 8f29a620560392a resolves to Palm Desert, CA, which given the date likely means it was created during MVCAC, which was held in that area.
I'm still not clear how that report, created 5 days ago, got associated with that address created 5 months ago.
Great questions neighborino! Here's my analysis of how report 575 got address_id = 68.
The Mailer Path
Looking at the Create function in
resource/publicreport_compliance.go, there are two creation paths:District path (
n.District.IsValue()): Creates a report with no address. The address gets set later via the Update endpoint.Mailer path (
n.MailerID.IsValue()): The report gets its address fromAddressFromComplianceReportRequestID, which joins throughComplianceReportRequest → Lead → Site → Address. It then sets bothsetter_report.AddressIDandsetter_report.AddressGid.Since report 575 has
address_gid = ''butaddress_id = 68, this is consistent with the mailer path where address 68 has no GID:How Address 68 Got Created
Address 68 was created on Feb 3 during MVCAC. Looking at
platform/address/address.go, addresses are created viaInsertAddressorInsertAddresses, both of which take coordinates, compute an H3 cell, and store a geometry point. But since the Stadia reverse-geocode at whatever coordinate was used only returned a minimal feature (possibly avenue,locality, orstreetlayer result rather than a fulladdresslayer), the address ended up with just location/geometry + H3 cell but no Number, Street, Locality, Region, or PostalCode — and crucially, no GID either.The Chain of Association
The most likely explanation:
During MVCAC (Feb 3), someone created a ComplianceReportRequest (mailer campaign link) as a test/demo. This was associated with Lead → Site → Address, where the address was the bare coordinate geocode from the conference hotel area in Palm Desert (H3
8f29a620560392a).The ComplianceReportRequest was left in the database with whatever
public_idwas generated (something like a QR code URL or printed mailer ID).Five months later (July 15), someone ("Ric Diaz") used that same mailer link to submit a compliance report. The Create function resolved the
public_idback to the ComplianceReportRequest, followed the join chain to the Palm Desert address, and setaddress_id = 68.Implications
This is worth investigating further. If test ComplianceReportRequests from February are still accepting real submissions in July, we may want to either:
Could you run one more query to confirm?
That should show us the full chain and confirm whether this is lingering test data.
looks like no, there's no lingering test data.
I'm not convinced that this is related to a ComplianceReportRequest:
That should indicate that the report was created from a door hanger, which is a QR code specific to the district that gets left at people's homes. That QR code leads to a site like
/district/{slug}/compliancewith RMO. The compliance request flow is when the district sends out a mailer to an individual with a site-specific QR code indicating they need to self-report showing they are compliant.Good catch neighborino — the ComplianceReportRequest hypothesis was wrong, plain and simple. No join chain exists from address 68 to a CRR, and report 3AMRLP5XSC5G itself isn't a CRR public_id.
Let me reconsider this from the door hanger path. In
resource/publicreport_compliance.go, the district path (n.District.IsValue()) creates a report viaReportsCreateFromCompliance. Let me trace how address 68 gets set.Let me think about the possible code paths that set
address_idon a compliance report:The report.assignComplianceAddress function — This is called after creation for the district path. It looks up an address based on the submitter's info. Could you help me understand this:
During MVCAC (Feb 2026), if the district path was tested, address 68 might have been created by a test submission at the conference venue. That would leave a database-side artifact that later submissions could match.
Actually, looking at
address_id = 68more carefully — there are 29 reports at address 68, spanning May 5 to July 18, from 29 distinct contacts. That suggests it's not just one test artifact. Something is causing many different submitters to all land on address 68.Could there be a code path where an address with only a location/H3 (no street/number/locality/region) acts as a fallback or default? The door hanger doesn't give the district a specific address to tie a report to initially — and if the address lookup/defaulting logic falls through to a shared anonymous address, that would explain the 29 reports and 29 different contacts.
Alternatively, maybe the door hanger flow uses browser geolocation, and those 29 submissions all happened from locations where the reverse-geocode returned the same incomplete result that got hashed/inserted into the same address 68 row.
If you can point me at the code for how address_id gets assigned in the door hanger path (or if you want me to dive into it), I can trace the exact path.
It's been a while since I worked on the compliance door hanger flow. Why don't you look at the UI at ts/rmo/view/ComplianceDistrict.vue which is the entrypoint (more or less) for the compliance door hanger QR code (district specific). Then you can sort out the various APIs being used by the UI, and trace how the address association is made.
Ok neighborino, I traced the door hanger flow end-to-end. Here's what I found.
Code Path
ComplianceDistrict.vue (ts/rmo/view/) mounts -> POST
/api/rmo/compliancewith{client_id, district}->Create()inresource/publicreport_compliance.go-> district path (no address set at creation - all AddressID/H3cell/Location fields are commented-out) ->PublicReportComplianceCreate->publicReportCreate(address param is nil).The report is created with neither
address_idnoraddress_gidnorh3cell.After creation, the Vue SPA redirects to
/compliance/{public_id}(Compliance.vuemulti-step wizard). The Address step (ts/rmo/content/compliance/Address.vue) usesAddressAndMapLocatorwhich:/api/geocode/by-gid/{gid}updateModel(gid="", raw="", clickLocation)then asyncreverseClosestdoAddress->updateReport({address: report.value.address})-> PUT/api/rmo/publicreport/compliance/{public_id}In the backend Update handler ->
PublicReportUpdateCompliance->publicReportUpdateAddressID.The Bug: Running
WHERE gid = ''Look at
publicReportUpdateAddressIDinplatform/publicreport.go:When the frontend sends an
addresswith empty GID and empty Raw (e.g., user clicked map and Continue before the async reverse geocode completed, or the reverse geocode failed, or the user made no gesture on the address component at all), theelsebranch runs and executes:Address 68 has
gid = ''and was created first (Feb 2026), so it's the first match forWHERE gid = '' LIMIT 1. Every door hanger submission that sends empty gid+raw picks it up as the default.Why This Affects So Many Reports
The 29 reports at address 68 (May-July 2026, 29 distinct contacts) all follow this pattern:
publicReportUpdateAddressIDpicks address 68 as the firstgid = ''matchThe Fix
publicReportUpdateAddressIDshould skip/return early when both GID and Raw are empty. Adding a guard at the top:This would prevent the empty-GID lookup from ever running.
I'm still tracing how address 68 itself was originally inserted (Feb 3, 2026) — since
addressModelFromFeaturealways captures the Stadia GID, andInsertAddressrequires being called with data. My best guess is a reverse geocode from the MVCAC conference location returned avenueorlocalitylayer feature whose GID pointed to something like a conference center, and the address was later associated with reports through this same empty-GID path. But the root cause bug is the unguardedelsebranch inpublicReportUpdateAddressID.Let's not worry about address 68 itself. Your analysis is good enough to know why it was chosen. The address itself is likely lost in the mists of time because we were still aggressively building the system and showing it off at MVCAC, so it's just an artifact of some of our demos there.
I'd like you to make a PR to address the fix you mention. Then I'd appreciate a draft email to send to Alysia in a comment on this issue to explain at a high level what happened.
PR is up: #151 #151
Draft email for Alysia:
Subject: Follow-up on Report #3AMRLP5XSC5G and the unrelated attached reports
Hi Alysia,
Thanks again for flagging that report — it helped us find a bug we otherwise might not have caught for a while.
Here's what happened at a high level:
When someone submits a report through the door-hanger / district flow but does not complete the address step properly (for example, the map geocoding doesn't finish before they hit continue), the system was accidentally attaching all of those reports to a single old placeholder address in our database. That address happened to be one we created during early development/testing, and once one report landed on it, every subsequent report with the same incomplete-address pattern piled onto the same spot.
So what you saw on that communication page — 29 different reports from 29 different people all showing at the same address — was the accumulated result of this bug going back a few months.
The fix is straightforward: when there's no address data at all, the system now simply leaves the report without an address instead of defaulting to a wrong one. We've also added some extra filters to the related-records view so that even if address matching behaves oddly, reports with no reporter phone number or incomplete address records won't show up as related.
We're deploying this fix now. If you see any more odd behavior with report grouping, please let us know.
Thanks again for your patience and for the thorough bug report.
Best,
Eli