`db/schema.sql`

61 lines
-- Trademarks & Copyright opportunity finder schema

DROP TABLE IF EXISTS swot_cache CASCADE;
DROP TABLE IF EXISTS portfolio_layout CASCADE;
DROP TABLE IF EXISTS items CASCADE;

CREATE TABLE items (
  id                    SERIAL PRIMARY KEY,
  kind                  TEXT NOT NULL CHECK (kind IN ('trademark', 'copyright')),
  name                  TEXT NOT NULL,
  registration_number   TEXT,
  original_owner        TEXT,
  nice_class            TEXT,              -- trademark only: international class(es)
  goods_services        TEXT,              -- what the mark covered
  status                TEXT NOT NULL,     -- 'abandoned', 'cancelled', 'expired', 'public_domain'
  filed_date            DATE,
  registered_date       DATE,
  expired_date          DATE NOT NULL,     -- abandonment / expiration / PD entry
  source                TEXT NOT NULL,     -- 'uspto', 'copyright_office', 'gutenberg', 'wikipedia'
  source_url            TEXT,
  notes                 TEXT,

  -- scoring inputs (0-100 each unless noted)
  distinctiveness_score  INT,              -- fanciful=90, arbitrary=75, suggestive=55, descriptive=30, generic=10
  recognition_score      INT,              -- rough proxy for brand recall
  domain_available       BOOLEAN,          -- .com check
  competing_active_marks INT,              -- count of live USPTO marks with similar name
  monetization_breadth   INT,              -- 0-100 (merch+content+licensing)
  composite_score        NUMERIC(5,2),     -- calculated

  -- monetization ideas (JSON array of {title, description})
  monetization_ideas     JSONB DEFAULT '[]'::jsonb,

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

CREATE INDEX idx_items_kind     ON items(kind);
CREATE INDEX idx_items_status   ON items(status);
CREATE INDEX idx_items_score    ON items(composite_score DESC);
CREATE INDEX idx_items_expired  ON items(expired_date);
CREATE INDEX idx_items_name     ON items USING gin (to_tsvector('english', name || ' ' || coalesce(goods_services,'') || ' ' || coalesce(notes,'')));

CREATE TABLE swot_cache (
  item_id       INT NOT NULL REFERENCES items(id) ON DELETE CASCADE,
  quadrant      TEXT NOT NULL CHECK (quadrant IN ('strength','weakness','opportunity','threat')),
  content       TEXT NOT NULL,
  generated_at  TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (item_id, quadrant)
);

CREATE TABLE portfolio_layout (
  id          SERIAL PRIMARY KEY,
  board_name  TEXT NOT NULL DEFAULT 'default',
  item_id     INT NOT NULL REFERENCES items(id) ON DELETE CASCADE,
  x           NUMERIC NOT NULL DEFAULT 0,
  y           NUMERIC NOT NULL DEFAULT 0,
  updated_at  TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(board_name, item_id)
);