DB: Add source column to relationships table #44

New Issue

forbes · 2026-02-09T00:16:21Z

forbes commented

2026-02-09 00:16:21 +00:00

Summary

Add a dedicated source column to the relationships table to distinguish assembly-derived BOM entries from manually-added ones. This is required by the BOM merge feature — the merge endpoint needs to know which entries came from an assembly commit (and can be auto-updated) vs. which were added manually via the web UI or CSV import (and must never be auto-deleted).

Current State

The source field is currently stored inside the metadata JSONB column on the relationships table. The BOM CSV import writes it as metadata["source"], and the frontend BOMTab reads/displays m.source. This is a free-form text field with no enforced values.

Migration

-- 012_bom_source.sql
ALTER TABLE relationships
    ADD COLUMN source VARCHAR(20) NOT NULL DEFAULT 'manual'
    CHECK (source IN ('manual', 'assembly'));

-- Migrate existing metadata.source values
UPDATE relationships
SET source = 'manual'
WHERE source = 'manual';

All existing entries default to manual. The BOM merge endpoint will set source = 'assembly' for entries it creates.

Code Changes

`internal/db/relationships.go`

Add Source string field to Relationship struct
Add Source string field to BOMEntry struct
Update Create() to include source in INSERT
Update GetBOM(), GetWhereUsed(), GetExpandedBOM() queries to SELECT source

`internal/api/bom_handlers.go`

Add Source string to BOMEntryResponse (json:"source")
Add Source string to AddBOMEntryRequest (json:"source,omitempty") — defaults to manual if omitted
Update CSV import to write source column instead of metadata["source"]
Update CSV export to read from source column

Frontend (`web/src/components/items/BOMTab.tsx`)

Update to read/write source as a top-level field instead of from metadata
No visible change to the user

Merge Rule

The BOM merge endpoint (separate issue) relies on this column:

source = 'assembly' entries can be auto-added, quantity-updated, or flagged as unreferenced by assembly merges
source = 'manual' entries are never touched by assembly merges

## Summary Add a dedicated `source` column to the `relationships` table to distinguish assembly-derived BOM entries from manually-added ones. This is required by the BOM merge feature — the merge endpoint needs to know which entries came from an assembly commit (and can be auto-updated) vs. which were added manually via the web UI or CSV import (and must never be auto-deleted). ## Current State The `source` field is currently stored inside the `metadata` JSONB column on the `relationships` table. The BOM CSV import writes it as `metadata["source"]`, and the frontend BOMTab reads/displays `m.source`. This is a free-form text field with no enforced values. ## Migration ```sql -- 012_bom_source.sql ALTER TABLE relationships ADD COLUMN source VARCHAR(20) NOT NULL DEFAULT 'manual' CHECK (source IN ('manual', 'assembly')); -- Migrate existing metadata.source values UPDATE relationships SET source = 'manual' WHERE source = 'manual'; ``` All existing entries default to `manual`. The BOM merge endpoint will set `source = 'assembly'` for entries it creates. ## Code Changes ### `internal/db/relationships.go` - Add `Source string` field to `Relationship` struct - Add `Source string` field to `BOMEntry` struct - Update `Create()` to include `source` in INSERT - Update `GetBOM()`, `GetWhereUsed()`, `GetExpandedBOM()` queries to SELECT `source` ### `internal/api/bom_handlers.go` - Add `Source string` to `BOMEntryResponse` (`json:"source"`) - Add `Source string` to `AddBOMEntryRequest` (`json:"source,omitempty"`) — defaults to `manual` if omitted - Update CSV import to write `source` column instead of `metadata["source"]` - Update CSV export to read from `source` column ### Frontend (`web/src/components/items/BOMTab.tsx`) - Update to read/write `source` as a top-level field instead of from metadata - No visible change to the user ## Merge Rule The BOM merge endpoint (separate issue) relies on this column: - `source = 'assembly'` entries can be auto-added, quantity-updated, or flagged as unreferenced by assembly merges - `source = 'manual'` entries are never touched by assembly merges

forbes referenced this issue

2026-02-09 00:16:56 +00:00

API: Add POST /api/items/{partNumber}/bom/merge endpoint #45

forbes referenced this issue

2026-02-09 00:17:47 +00:00

Web UI: BOM merge resolution view #47

forbes referenced this issue from a commit

2026-02-09 00:45:45 +00:00

feat(db): add source column to relationships table (#44)

forbes referenced a pull request that will close this issue

2026-02-09 00:45:54 +00:00

feat(db): add source column to relationships table #50