customer_search.sql

customer_search.sql

create table if not exists customer_search (
  customer_id uuid,
  contact_id uuid,
  primary key(customer_id, contact_id),
  -- contact method type, for ex. 'Phone' or 'Email'
  contact_method text,
  -- term is a value computed from the contact method
  term text,
  -- term_display is used as display value instead of the term column which is more technical
  term_display character varying,
  business_id uuid,
  -- the name of the customer
  name text,
  -- control field that marks stale rows
  is_stale boolean not null,
  -- document: computed from name and term
  document tsvector,
  deleted_at timestamptz,
  photo character varying
);

create index idx_customer_id_contact_id_on_customer_search on customer_search using btree (customer_id, contact_id);

create index idx_is_stale_on_customer_search on customer_search (is_stale) where is_stale = true;

-- gin indexes on document, term and name
create index idx_document_on_customer_search on customer_search using gin(document);
-- gin_trgm_ops is needed by pg_trgm extension
create index idx_term_customer_search on customer_search using gin(term gin_trgm_ops);
create index idx_name_customer_search on customer_search using gin(name gin_trgm_ops);