renZBeEK

1create table if not exists customer_search (
2  customer_id uuid,
3  contact_id uuid,
4  primary key(customer_id, contact_id),
5  -- contact method type, for ex. 'Phone' or 'Email'
6  contact_method text,
7  -- term is a value computed from the contact method
8  term text,
9  -- term_display is used as display value instead of the term column which is more technical
10  term_display character varying,
11  business_id uuid,
12  -- the name of the customer
13  name text,
14  -- control field that marks stale rows
15  is_stale boolean not null,
16  -- document: computed from name and term
17  document tsvector,
18  deleted_at timestamptz,
19  photo character varying
20);
21
22create index idx_customer_id_contact_id_on_customer_search on customer_search using btree (customer_id, contact_id);
23
24create index idx_is_stale_on_customer_search on customer_search (is_stale) where is_stale = true;
25
26-- gin indexes on document, term and name
27create index idx_document_on_customer_search on customer_search using gin(document);
28-- gin_trgm_ops is needed by pg_trgm extension
29create index idx_term_customer_search on customer_search using gin(term gin_trgm_ops);
30create index idx_name_customer_search on customer_search using gin(name gin_trgm_ops);