27 lines
991 B
MySQL
27 lines
991 B
MySQL
|
-- Multiple exact sender domains per vendor.
|
||
|
|
CREATE TABLE IF NOT EXISTS vendor_email_domains (
|
||
|
|
domain TEXT PRIMARY KEY,
|
||
|
|
vendor_id INTEGER NOT NULL REFERENCES vendors(id) ON DELETE CASCADE,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_vendor_email_domains_vendor_id
|
||
|
|
ON vendor_email_domains(vendor_id);
|
||
|
|
|
||
|
|
INSERT INTO vendor_email_domains (domain, vendor_id)
|
||
|
|
SELECT LOWER(TRIM(domain)), id
|
||
|
|
FROM vendors
|
||
|
|
WHERE NULLIF(TRIM(domain), '') IS NOT NULL
|
||
|
|
ON CONFLICT (domain) DO NOTHING;
|
||
|
|
|
||
|
|
-- Also promote the legacy single customer domain into the existing
|
||
|
|
-- multi-domain mapping table.
|
||
|
|
INSERT INTO email_domain_customer_mappings (domain, customer_id, source)
|
||
|
|
SELECT LOWER(TRIM(email_domain)), id, 'legacy_customer_domain'
|
||
|
|
FROM customers
|
||
|
|
WHERE NULLIF(TRIM(email_domain), '') IS NOT NULL
|
||
|
|
ON CONFLICT (domain) DO NOTHING;
|
||
|
|
|
||
|
|
COMMENT ON TABLE vendor_email_domains IS
|
||
|
|
'Exact trusted sender domains belonging to vendors; no fuzzy matching.';
|