datastructure.sql

create type hf_subscribe_status_t as enum ('initial', 'pending', 'done');
create table hf_subscribe
(
  id bigserial unique not null,
  user_id bigint not null,
  creation_time timestamp default current_timestamp,
  last_fetch_time timestamp,
  status hf_subscribe_status_t default 'initial',
  query jsonb not null,
  -- {
    -- type: 'product|company',
    -- keyword: '',
  -- }
  filter jsonb not null,
  -- {
    -- countrysets: [],
  -- }
);

create type hf_message_status_t as enum ('hidden', 'unread', 'read');
create table hf_message
(
  id bigserial unique not null,
  subscribe_id bigint not null,
  creation_time timestamp default current_timestamp,
  status hf_message_status_t default 'hidden',
  company_id bigserial not null, -- CompanyContact Id or ProductCompany Id in Dynamodb
  company_contact_version bigint not null -- version of company contact
);

create table company
(
  id bigserial unique not null,
  creation_time timestamp default current_timestamp,
  contact jsonb,
  -- {
    -- email:,
    -- country: ,
    -- address: ,
  -- }
  contact_journal jsonb,
  -- [
    -- {
      -- timestamp: 1234567,
      -- email: ,
      -- country: ,
      -- address: ,
    -- },
    -- {
      -- timestamp: 1234568,
      -- email: ,
      -- country: ,
      -- address: ,
    -- },
  -- ]
);

hf_subscribe
添加两个字段 followed（关注），progress（进度）

用户收藏表：
主要字段：
user_id:用户ID
src_company_id:原公司；
contact;联系方式；
last_read_time:用户最后读取公司contact历史的时间

hf_message:

1. unique(subscribe_id,company.id)
2. 新加字段：latest_contact

hf_subscribe 表中的status=‘done’是不是意味着finished，去掉‘first_finished’字段？