`db/migrations/001_claim_columns.sql`

52 lines
-- 001 · Claim columns + provenance + opt-out
-- Adds the columns the option-C "scrape lite + claim flow" needs.
-- Idempotent: re-runnable, all ADD COLUMN guarded by IF NOT EXISTS.

BEGIN;

ALTER TABLE installers
  ADD COLUMN IF NOT EXISTS claim_status      TEXT DEFAULT 'self',  -- self | unclaimed | pending_claim | claimed
  ADD COLUMN IF NOT EXISTS claim_token       TEXT,                 -- one-shot verification token
  ADD COLUMN IF NOT EXISTS claim_token_at    TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS claimed_at        TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS instagram_handle  TEXT,                 -- e.g. "atelierbond" — no @, no URL
  ADD COLUMN IF NOT EXISTS source_name       TEXT,                 -- e.g. "wia" | "self_signup" | "manual"
  ADD COLUMN IF NOT EXISTS source_url        TEXT,                 -- exact URL we scraped from
  ADD COLUMN IF NOT EXISTS source_scraped_at TIMESTAMPTZ;

CREATE INDEX IF NOT EXISTS idx_installers_claim_status ON installers(claim_status);
CREATE INDEX IF NOT EXISTS idx_installers_source_url ON installers(source_url);

-- Self-signed-up installers default to 'self'
-- (existing rows that came in via signup will get 'self' by default)
UPDATE installers SET claim_status = 'self' WHERE claim_status IS NULL;

-- ----------------------------------------------------------------------
-- Opt-out registry: domains that have asked to be delisted and never
-- re-scraped, no matter what source they show up on.
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS directory_optout (
  id          SERIAL PRIMARY KEY,
  domain      TEXT UNIQUE NOT NULL,
  reason      TEXT,
  requested_by_email TEXT,
  created_at  TIMESTAMPTZ DEFAULT now()
);

-- ----------------------------------------------------------------------
-- Scrape audit: per-request log so we can prove crawl etiquette later.
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS scrape_log (
  id          SERIAL PRIMARY KEY,
  source      TEXT NOT NULL,        -- e.g. 'wia', 'studio_site'
  url         TEXT NOT NULL,
  http_status INTEGER,
  bytes       INTEGER,
  duration_ms INTEGER,
  error       TEXT,
  created_at  TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_scrape_log_source_created ON scrape_log(source, created_at DESC);

COMMIT;