MemberJunction / MemberJunction/MJ
DBAutoDoc: organic-key detection emits all-pairs cliques with no value-overlap check
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Summary
DBAutoDoc's organic-key detection emits **all-pairs cliques** with no value-overlap check. The result is presented to consumers as join paths, but a large share of the pairs are many-to-many co-references, and some can never match at all.
## How the output is shaped
`OrganicKeyTranslator.ts` gives each cluster member every other member as a `RelatedEntity`, so spokes grow with the square of cluster size. One production run produced 1,530 keys and 24,798 spokes; a single table carried 128 related tables, 97 of them reachable through more than one key, and tables can carry the same key name twice (e.g. two "Customer Id Match" keys on different columns).
Per `plans/dbautodoc-organic-keys.md`, skipping value overlap is deliberate ("schema capacity for value matching, not proof of current overlap"). That is defensible for discovery, but the output is consumed as if it were verified.
## Measured quality on one deployment
Classifying 3,201 in-scope pairs against the FK metadata:
| Class | Share | Effect if joined |
|---|---:|---|
| Both columns are FKs to the **same third table** (e.g. two child tables sharing a customer key) | 61% | Many-to-many. Joining two fact tables this way multiplies rows and inflates any aggregate. |
| PK-anchored, no FK declared | 19% | Usually correct, one-to-many. |
| Duplicates an existing FK | 9% | Redundant. |
| Both FKs, to **different** tables | 5% | Semantically wrong (e.g. a registrant's customer key matched to an event's chapter key). |
| Other (email matches etc.) | 6% | Varies. |
Sampled value overlap confirmed the problem is not theoretical: a number of pairs had zero overlap despite both sides being populated, including type-mismatched pairs (`nvarchar` vs `uuid`) whose join would simply error on PostgreSQL.
A prior manual cleanup on the same file had to delete 720 cross-schema spokes after checking them against live data, which is the check the detector could have done itself.
## Suggested fix
- Emit **hub-and-spoke** from the PK-owning table rather than all-pairs cliques. This removes the co-reference class, which is the majority of the output and the one that silently multiplies rows.
- Add a cheap value-overlap sample (the driver already has `testValueOverlap`) and drop pairs with zero overlap, or record the measured overlap on the spoke so consumers can weigh it.
- Skip pairs whose column types cannot compare.
- Distinguish one-to-many from many-to-many in the emitted metadata, so downstream consumers do not present a co-reference as a join path.
Related: #3815 (organic-key config performance), #4409 (TransitiveView SQL Server syntax on PostgreSQL).
---
Found alongside #4531, #4532 and #4533 during a production schema-info audit.
Contributor guide
Research direction
Start with OrganicKeyTranslator.ts and the design notes in plans/dbautodoc-organic-keys.md; trace how cluster members become RelatedEntity join paths. Inspect the driver's testValueOverlap helper and define the emitted metadata needed to avoid invalid or misleading spokes; done means the detector no longer produces unchecked all-pairs join paths and its behavior is covered by relevant tests.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql, typescript
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100