31 lines
1.4 KiB
SQL
31 lines
1.4 KiB
SQL
-- BizMatch Phase 2 — module 4: the fields the buyer side actually collects
|
|
-- (NDA form + intake sheet). 001_init.sql stays untouched.
|
|
|
|
-- -------------------------------------------------------------- contact
|
|
ALTER TABLE contact ADD COLUMN cell text;
|
|
|
|
-- ---------------------------------------------------------------- buyer
|
|
ALTER TABLE buyer
|
|
ADD COLUMN address text,
|
|
ADD COLUMN state text,
|
|
ADD COLUMN background_experience text,
|
|
ADD COLUMN how_heard text,
|
|
ADD COLUMN interested_in_updates boolean NOT NULL DEFAULT false;
|
|
|
|
-- ------------------------------------------------------------------ nda
|
|
-- Money stays text on purpose: the paper forms contain things like
|
|
-- "1.2M + inventory" or "TBD" that no numeric type survives.
|
|
ALTER TABLE nda
|
|
ADD COLUMN total_purchase_price text,
|
|
ADD COLUMN down_payment text,
|
|
ADD COLUMN intro_date date;
|
|
|
|
-- ------------------------------------------- dedup anchors (duplicates)
|
|
-- The duplicate check compares names case-insensitively and phone numbers
|
|
-- digits-only, so the indexes have to match those expressions exactly.
|
|
CREATE INDEX contact_name_lower_idx ON contact (lower(btrim(name)));
|
|
CREATE INDEX contact_phone_digits_idx
|
|
ON contact ((regexp_replace(phone, '[^0-9]', '', 'g'))) WHERE phone IS NOT NULL;
|
|
CREATE INDEX contact_cell_digits_idx
|
|
ON contact ((regexp_replace(cell, '[^0-9]', '', 'g'))) WHERE cell IS NOT NULL;
|