MemberJunction / MemberJunction/MJ
DBAutoDoc: PostgreSQL FK extraction cross-multiplies composite foreign key columns (N x N soft FKs)
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 308
Description
## Summary
`DBAutoDoc`'s PostgreSQL driver emits every **combination** of a composite foreign key's columns instead of the actual column pairs. An N-column composite FK becomes N x N soft-FK declarations in `additionalSchemaInfo.json`.
## Cause
`packages/DBAutoDoc/src/drivers/PostgreSQLDriver.ts:537-558` joins `information_schema.key_column_usage` to `constraint_column_usage` on the constraint name alone:
```sql
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
```
Neither view says which source column pairs with which referenced column, so the join is a cross product. `AdditionalSchemaInfoGenerator.ts:356-394` only de-duplicates exact `FieldName -> RelatedTable.RelatedField` triples, so all N x N rows reach the file.
## Reproduction
For `FOREIGN KEY (a_key, a_prd_key, a_ptp_key) REFERENCES price (prc_key, prc_prd_key, prc_prd_ptp_key)` the generated file contains 9 declarations:
```
a_key -> price.prc_key | price.prc_prd_key | price.prc_prd_ptp_key
a_prd_key -> price.prc_key | price.prc_prd_key | price.prc_prd_ptp_key
a_ptp_key -> price.prc_key | price.prc_prd_key | price.prc_prd_ptp_key
```
CodeGen then applies them in file order and the last one wins (see companion issue on soft-FK validation), so all three columns resolve to `prc_prd_ptp_key`.
## Impact
Observed on a production PostgreSQL deployment with an AMS schema that uses composite FKs heavily: 43 multi-target declarations across 16 tables. Because the columns were NOT NULL, CodeGen emitted INNER JOINs for the display label and **two base views returned 0 rows out of 67,389 and 6,079**. Nothing errored, and every monetary aggregate downstream silently returned zero.
## Not affected
- `SQLServerDriver.ts:507-527` uses `sys.foreign_key_columns`, which is one row per column pair.
- `MySQLDriver.ts:524-540` uses `KEY_COLUMN_USAGE`, which carries `REFERENCED_COLUMN_NAME` per row.
- `BaseAutoDocDriver` has no FK code.
This is PostgreSQL-only.
## Suggested fix
Pair the columns by ordinal, the way MJ's own `__mj.vwForeignKeys` already does:
```sql
CROSS JOIN LATERAL UNNEST(con.conkey, con.confkey) cols(parent_col, ref_col)
```
i.e. read from `pg_constraint` and unnest `conkey`/`confkey` together, or join `key_column_usage.position_in_unique_constraint` to the referenced constraint's `ordinal_position`.
Still reproduces on `next` as of 2026-09-16.
Contributor guide
Research direction
Start with packages/DBAutoDoc/src/drivers/PostgreSQLDriver.ts:537-558 and compare its foreign-key query with __mj.vwForeignKeys. Verify how AdditionalSchemaInfoGenerator.ts:356-394 consumes the rows, then reproduce the composite-FK example against PostgreSQL. Done means each source column is paired with only its corresponding referenced column, without N x N declarations in additionalSchemaInfo.json.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, sql, typescript
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 76/100