lorentz
9a448d111c
Migration 057:
- ADD user_defined_fields JSONB (GIN indexed)
- ADD middle_initial, note, external_id, country_id, company_location_id
- ADD create_date, impersonator_creator_resource_id
- ADD is_opted_out_from_bulk_email, bulk_email_opt_out_time
- ADD solicitation_opt_out_time, survey_opt_out
- ADD receives_email_notifications, billing_contact
entity-mapper.ts mapContact():
- Fix broken snake_case field refs → correct camelCase API names:
alternate_phone → alternatePhone, mobile_phone → mobilePhone
name_prefix/suffix → namePrefix/nameSuffix
facebook/twitter/linkedin_url → facebookUrl/twitterUrl/linkedInUrl
primary_contact → primaryContact, solicitation_opt_out → solicitationOptOut
last_activity/modified_date → lastActivityDate/lastModifiedDate
api_vendor_id → apiVendorID, is_active → isActive, is_deleted → isDeleted
- Add UDF conversion: userDefinedFields[] → JSONB {name: value}
- 17 UDFs stored: UserID, Birthday, O365License, VIP User, Department,
Password, Email Password, Archive Email, User System Profile, etc.
Results: 4234 contacts synced, 3318 with UDFs, 4140 with create_date
20 lines
1.2 KiB
SQL
20 lines
1.2 KiB
SQL
-- Migration 057: Add missing contacts fields + UDF storage
|
|
|
|
ALTER TABLE contacts
|
|
ADD COLUMN IF NOT EXISTS user_defined_fields JSONB,
|
|
ADD COLUMN IF NOT EXISTS middle_initial VARCHAR(10),
|
|
ADD COLUMN IF NOT EXISTS note TEXT,
|
|
ADD COLUMN IF NOT EXISTS external_id VARCHAR(50),
|
|
ADD COLUMN IF NOT EXISTS country_id INTEGER,
|
|
ADD COLUMN IF NOT EXISTS company_location_id BIGINT,
|
|
ADD COLUMN IF NOT EXISTS create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
ADD COLUMN IF NOT EXISTS is_opted_out_from_bulk_email BOOLEAN DEFAULT false,
|
|
ADD COLUMN IF NOT EXISTS bulk_email_opt_out_time TIMESTAMP WITHOUT TIME ZONE,
|
|
ADD COLUMN IF NOT EXISTS solicitation_opt_out_time TIMESTAMP WITHOUT TIME ZONE,
|
|
ADD COLUMN IF NOT EXISTS survey_opt_out BOOLEAN DEFAULT false,
|
|
ADD COLUMN IF NOT EXISTS receives_email_notifications BOOLEAN DEFAULT true,
|
|
ADD COLUMN IF NOT EXISTS billing_contact BOOLEAN DEFAULT false,
|
|
ADD COLUMN IF NOT EXISTS impersonator_creator_resource_id BIGINT;
|
|
|
|
-- GIN index for UDF queries (e.g. find by UserID, O365License)
|
|
CREATE INDEX IF NOT EXISTS idx_contacts_udfs ON contacts USING gin(user_defined_fields);
|