`db/brands.sql`

49 lines
-- Unregistered-brand hunter schema (additive — does not touch existing items table).

DROP TABLE IF EXISTS brand_hunt_runs CASCADE;
DROP TABLE IF EXISTS brand_candidates CASCADE;

CREATE TABLE brand_candidates (
  id                       SERIAL PRIMARY KEY,
  domain                   TEXT UNIQUE NOT NULL,
  brand_name               TEXT,
  category                 TEXT DEFAULT 'fashion',
  established_year         INT,
  years_in_business        INT,
  whois_created_date       DATE,
  meta_description         TEXT,

  -- USPTO search outcome
  uspto_checked_at         TIMESTAMPTZ,
  uspto_live_marks         INT,                 -- count of apparent live federal registrations
  has_federal_registration BOOLEAN,
  uspto_search_url         TEXT,

  -- provenance
  suggested_by             TEXT DEFAULT 'seed', -- 'seed' | 'qwen' | 'manual'
  suggested_from_domain    TEXT,

  status                   TEXT DEFAULT 'pending',
  -- 'pending', 'verified_unregistered', 'registered', 'too_new', 'error'
  error_message            TEXT,
  notes                    TEXT,

  created_at               TIMESTAMPTZ DEFAULT NOW(),
  last_checked_at          TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_brands_status ON brand_candidates(status);
CREATE INDEX idx_brands_years  ON brand_candidates(years_in_business DESC NULLS LAST);
CREATE INDEX idx_brands_domain ON brand_candidates(domain);

CREATE TABLE brand_hunt_runs (
  id          SERIAL PRIMARY KEY,
  started_at  TIMESTAMPTZ DEFAULT NOW(),
  ended_at    TIMESTAMPTZ,
  seed_count  INT DEFAULT 0,
  discovered  INT DEFAULT 0,
  verified    INT DEFAULT 0,
  errors      INT DEFAULT 0,
  log         TEXT
);