MemberJunction / MemberJunction/MJ

DBAutoDoc: value lists emitted as closed lists on unsuitable columns, with invented values and uncalibrated Confidence

Open
#4,537 0 comments 0 reactions 0 assignees View on GitHub
enhancement
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
2d 1h
Merged PRs (30d)
323

Description

## Summary

DBAutoDoc writes `Fields[].PossibleValues` as **closed** lists (`ValueListType: "List"`) for columns where a closed list is not appropriate, and some of those lists contain values the column cannot hold. CodeGen copies them verbatim into `EntityFieldValue`, where they are published as the field's permitted values.

## Examples from one production run

| Column | Generated list | Problem | Confidence |
|---|---|---|---:|
| `__mj_integration_SyncStatus` (6 tables) | `Active, Inactive, Pending` / `Active, Failed, Pending, Synced` / `Active, Error, Paused, Pending` / ... | The column's documented domain is `Active, Archived, Error, Conflict` (`Integration/schema-builder/src/DDLGenerator.ts:126`). Five of six generated lists contain invented values, and the six disagree with each other. | 85-99 |
| `__mj_integration_LastWriterDirection` | `Pull, Push` on one table, `Inbound, Outbound` on another | Mutually contradictory for the same system column. | 95 / 90 |
| `_add_user` | 5 staff user names | An audit stamp column turned into a closed enum; also writes user names into metadata. | 90 |
| `_pht_key` | 3 raw GUIDs | A uuid FK column enumerated as a value list. | 95 |
| `_dues_year`, `_renew_year` | `2022, 2024, 2025, 2026` | Time-varying values frozen as a closed list; next year violates it. | 95 / 90 |

Because these are `List` rather than `ListOrUserEntry`, they are the complete set as far as any consumer is concerned. For MJ's own `__mj_integration_*` columns, a closed list that omits real states risks failing validation on values the sync engine legitimately writes.

## Confidence is not usable as a filter

`Confidence` is emitted per field, filtered to >= 85 in `AdditionalSchemaInfoGenerator.ts:134,319`, and then **never read** by CodeGen (parsed at `manage-metadata.ts:947`, unused). Every surviving entry scores 85-100, so it cannot discriminate. In the sample above the invented lists score 85-99 while the one correct `SyncStatus` list scores 100.

## Suggested fix

- Never emit a value list for: `__mj_*` system columns (their domain is known to MJ), audit columns (`*_add_user`, `*_change_user`), uuid/key columns, or columns whose values are dates/years.
- Emit `ListOrUserEntry` unless a CHECK constraint or enum makes the set genuinely closed; reserve `List` for constraint-derived lists.
- For MJ's own system columns, take the domain from the DDL generator rather than from sampled data.
- Either calibrate `Confidence` against a labelled sample or drop it, since it currently implies a precision that is not there.

---
Found alongside #4531, #4532 and #4533 during a production schema-info audit.

Contributor guide

Open the contributing guide

Research direction

Start with Integration/schema-builder/src/DDLGenerator.ts:126 and AdditionalSchemaInfoGenerator.ts at lines 134 and 319 to trace how PossibleValues and Confidence are produced. Then inspect the CodeGen consumer and manage-metadata.ts:947 to confirm whether those fields affect published metadata. Done means unsuitable columns no longer receive misleading closed lists and the resulting metadata behavior is covered by the relevant tests.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql, typescript
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.