MemberJunction / MemberJunction/MJ

DBAutoDoc: PostgreSQL FK extraction cross-multiplies composite foreign key columns (N x N soft FKs)

Open
#4,531 0 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.