8e8616c835
- Added endpoints to link and unlink customers to vendors, including validation for relationship types. - Implemented a UI for managing linked customers in the vendor detail view. - Introduced a search feature for customers when linking to vendors. - Updated database schema to support customer-vendor relationships with necessary constraints and indices. - Added migration scripts for new tables and fields related to supplier invoices and customer-vendor links. - Modified bottom bar visibility in the frontend for improved user experience.
69 lines
2.5 KiB
SQL
69 lines
2.5 KiB
SQL
-- Migration 173: Supplier invoice flow metadata
|
|
-- Created: 2026-04-13
|
|
|
|
ALTER TABLE supplier_invoices
|
|
ADD COLUMN IF NOT EXISTS supplier_flow_type VARCHAR(20),
|
|
ADD COLUMN IF NOT EXISTS tags_json JSONB DEFAULT '[]'::jsonb,
|
|
ADD COLUMN IF NOT EXISTS source_email_id INTEGER REFERENCES email_messages(id) ON DELETE SET NULL,
|
|
ADD COLUMN IF NOT EXISTS linked_customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
|
|
ADD COLUMN IF NOT EXISTS linked_order_id BIGINT,
|
|
ADD COLUMN IF NOT EXISTS linked_order_source VARCHAR(50);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'chk_supplier_invoices_supplier_flow_type'
|
|
) THEN
|
|
ALTER TABLE supplier_invoices
|
|
ADD CONSTRAINT chk_supplier_invoices_supplier_flow_type
|
|
CHECK (
|
|
supplier_flow_type IS NULL
|
|
OR supplier_flow_type IN ('varekob', 'ydelse')
|
|
);
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'chk_supplier_invoices_linked_order_source'
|
|
) THEN
|
|
ALTER TABLE supplier_invoices
|
|
ADD CONSTRAINT chk_supplier_invoices_linked_order_source
|
|
CHECK (
|
|
linked_order_source IS NULL
|
|
OR linked_order_source IN ('tmodule_orders', 'webshop_orders')
|
|
);
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_supplier_invoices_supplier_flow_type
|
|
ON supplier_invoices(supplier_flow_type)
|
|
WHERE supplier_flow_type IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_supplier_invoices_source_email_id
|
|
ON supplier_invoices(source_email_id)
|
|
WHERE source_email_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_supplier_invoices_linked_customer_id
|
|
ON supplier_invoices(linked_customer_id)
|
|
WHERE linked_customer_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_supplier_invoices_linked_order_id
|
|
ON supplier_invoices(linked_order_id)
|
|
WHERE linked_order_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_supplier_invoices_tags_json
|
|
ON supplier_invoices USING GIN (tags_json);
|
|
|
|
COMMENT ON COLUMN supplier_invoices.supplier_flow_type IS 'Flow type: varekob or ydelse';
|
|
COMMENT ON COLUMN supplier_invoices.tags_json IS 'Tag list as JSON array';
|
|
COMMENT ON COLUMN supplier_invoices.source_email_id IS 'Source email message id';
|
|
COMMENT ON COLUMN supplier_invoices.linked_customer_id IS 'Linked customer context';
|
|
COMMENT ON COLUMN supplier_invoices.linked_order_id IS 'Linked order id';
|
|
COMMENT ON COLUMN supplier_invoices.linked_order_source IS 'Order table source: tmodule_orders or webshop_orders';
|