Saas-voc-server/migrations/001_initial_domain.sql

CREATE SCHEMA IF NOT EXISTS voc;

CREATE TABLE IF NOT EXISTS voc.workspace (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  name text NOT NULL,
  case_name text NOT NULL,
  status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS voc.source_connection (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  platform text NOT NULL,
  connection_kind text NOT NULL DEFAULT 'fmode_gateway',
  status text NOT NULL DEFAULT 'configured' CHECK (status IN ('configured', 'active', 'disabled', 'error')),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  last_checked_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (workspace_id, platform, connection_kind)
);

CREATE INDEX IF NOT EXISTS source_connection_workspace_idx
  ON voc.source_connection (workspace_id);

CREATE TABLE IF NOT EXISTS voc.import_batch (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  platform text NOT NULL,
  source_kind text NOT NULL,
  source_file text,
  source_hash text,
  sheet_names jsonb NOT NULL DEFAULT '[]'::jsonb,
  status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'partial', 'failed')),
  total_rows integer NOT NULL DEFAULT 0 CHECK (total_rows >= 0),
  success_rows integer NOT NULL DEFAULT 0 CHECK (success_rows >= 0),
  failed_rows integer NOT NULL DEFAULT 0 CHECK (failed_rows >= 0),
  started_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS import_batch_workspace_created_idx
  ON voc.import_batch (workspace_id, created_at DESC);

CREATE TABLE IF NOT EXISTS voc.product (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  platform text NOT NULL,
  product_id text NOT NULL,
  product_key text NOT NULL,
  role text NOT NULL DEFAULT 'own' CHECK (role IN ('own', 'competitor')),
  brand text NOT NULL DEFAULT '',
  title text NOT NULL DEFAULT '',
  model text NOT NULL DEFAULT '',
  category_1 text NOT NULL DEFAULT '',
  category_2 text NOT NULL DEFAULT '',
  category_3 text NOT NULL DEFAULT '',
  source text NOT NULL DEFAULT '',
  raw_payload jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (workspace_id, platform, product_id),
  UNIQUE (workspace_id, product_key)
);

CREATE INDEX IF NOT EXISTS product_workspace_role_idx
  ON voc.product (workspace_id, role, id);
CREATE INDEX IF NOT EXISTS product_workspace_category_idx
  ON voc.product (workspace_id, category_2, category_3);

CREATE TABLE IF NOT EXISTS voc.daily_metric (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES voc.product(id) ON DELETE CASCADE,
  platform text NOT NULL,
  metric_date date NOT NULL,
  source text NOT NULL,
  gmv numeric(18, 2) NOT NULL DEFAULT 0,
  sold_units integer NOT NULL DEFAULT 0,
  transaction_orders integer NOT NULL DEFAULT 0,
  transaction_customers integer NOT NULL DEFAULT 0,
  impressions bigint NOT NULL DEFAULT 0,
  clicks bigint NOT NULL DEFAULT 0,
  views bigint NOT NULL DEFAULT 0,
  visitors bigint NOT NULL DEFAULT 0,
  cart_units integer NOT NULL DEFAULT 0,
  order_amount numeric(18, 2) NOT NULL DEFAULT 0,
  order_units integer NOT NULL DEFAULT 0,
  order_count integer NOT NULL DEFAULT 0,
  refund_amount numeric(18, 2) NOT NULL DEFAULT 0,
  refund_units integer NOT NULL DEFAULT 0,
  refund_orders integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (workspace_id, platform, product_id, metric_date, source)
);

CREATE INDEX IF NOT EXISTS daily_metric_product_date_idx
  ON voc.daily_metric (product_id, metric_date);
CREATE INDEX IF NOT EXISTS daily_metric_workspace_date_idx
  ON voc.daily_metric (workspace_id, metric_date);

CREATE TABLE IF NOT EXISTS voc.product_relation (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  platform text NOT NULL,
  own_product_id bigint NOT NULL REFERENCES voc.product(id) ON DELETE CASCADE,
  competitor_product_id bigint NOT NULL REFERENCES voc.product(id) ON DELETE CASCADE,
  relation_key text NOT NULL,
  category text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CHECK (own_product_id <> competitor_product_id),
  UNIQUE (workspace_id, platform, own_product_id, competitor_product_id),
  UNIQUE (workspace_id, relation_key)
);

CREATE INDEX IF NOT EXISTS product_relation_own_idx
  ON voc.product_relation (own_product_id, competitor_product_id);
CREATE INDEX IF NOT EXISTS product_relation_competitor_idx
  ON voc.product_relation (competitor_product_id);
CREATE INDEX IF NOT EXISTS product_relation_workspace_idx
  ON voc.product_relation (workspace_id);

CREATE TABLE IF NOT EXISTS voc.review (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES voc.product(id) ON DELETE CASCADE,
  platform text NOT NULL,
  source_review_id text,
  review_key text NOT NULL,
  rating numeric(3, 2),
  content text NOT NULL,
  review_date timestamptz,
  raw_payload jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CHECK (rating IS NULL OR (rating >= 0 AND rating <= 5)),
  UNIQUE (workspace_id, platform, review_key)
);

CREATE INDEX IF NOT EXISTS review_product_date_idx
  ON voc.review (product_id, review_date DESC, id DESC);
CREATE INDEX IF NOT EXISTS review_workspace_date_idx
  ON voc.review (workspace_id, review_date DESC, id DESC);

CREATE TABLE IF NOT EXISTS voc.sync_job (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  platform text NOT NULL,
  idempotency_key text NOT NULL,
  status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'partial', 'failed', 'cancelled')),
  scopes jsonb NOT NULL DEFAULT '[]'::jsonb,
  product_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
  progress integer NOT NULL DEFAULT 0 CHECK (progress >= 0 AND progress <= 100),
  attempts integer NOT NULL DEFAULT 0 CHECK (attempts >= 0),
  max_attempts integer NOT NULL DEFAULT 3 CHECK (max_attempts >= 1),
  worker_id text,
  error_summary text,
  requested_at timestamptz NOT NULL DEFAULT now(),
  started_at timestamptz,
  completed_at timestamptz,
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (workspace_id, idempotency_key)
);

CREATE INDEX IF NOT EXISTS sync_job_workspace_requested_idx
  ON voc.sync_job (workspace_id, requested_at DESC);
CREATE INDEX IF NOT EXISTS sync_job_pending_idx
  ON voc.sync_job (requested_at, id)
  WHERE status = 'pending';

CREATE TABLE IF NOT EXISTS voc.sync_job_event (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sync_job_id bigint NOT NULL REFERENCES voc.sync_job(id) ON DELETE CASCADE,
  level text NOT NULL DEFAULT 'info' CHECK (level IN ('debug', 'info', 'warning', 'error')),
  event_type text NOT NULL,
  message text NOT NULL,
  details jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS sync_job_event_job_created_idx
  ON voc.sync_job_event (sync_job_id, created_at, id);

INSERT INTO voc.workspace (public_id, name, case_name)
VALUES ('demashi', 'Demashi JD VOC', 'Demashi')
ON CONFLICT (public_id) DO NOTHING;

INSERT INTO voc.source_connection (workspace_id, platform, connection_kind, status, metadata)
SELECT id, 'jd', 'fmode_gateway', 'configured', '{"credentialStorage":"environment"}'::jsonb
FROM voc.workspace
WHERE public_id = 'demashi'
ON CONFLICT (workspace_id, platform, connection_kind) DO NOTHING;