`db/migrations/008_places_seed.sql`

30 lines
-- M-P1: Google Places DISCOVERY seed + free-tier hard-cap counter.
-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.
--
-- ToS posture (Google Maps Platform §3.2.3): place_id is the ONLY Places field
-- we persist. Everything durable (firm name/website/phone) is (re)derived from
-- the firm's OWN public site by the existing crawl pipeline, so Places is a
-- pointer ("where to look next"), never our stored dataset. place_id doubles as
-- the idempotent dedupe key so a seeded office is never re-seeded.

CREATE TABLE places_seed (
  place_id     TEXT PRIMARY KEY,            -- Google's stable id — the only cacheable field
  query        TEXT,                        -- the search text that surfaced it (audit)
  region_id    INT REFERENCES region(id),   -- metro/county we were seeding
  website_host TEXT,                        -- host only, for dedupe vs firm_site (not "Places Content")
  firm_id      INT REFERENCES firm(id),     -- the firm row this seed produced/linked (nullable until crawl)
  status       TEXT NOT NULL DEFAULT 'new', -- new | linked | no_website | crawled | dead
  first_seen   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_seen    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_places_seed_region ON places_seed (region_id);
CREATE INDEX idx_places_seed_status ON places_seed (status);

-- Monthly billing guard. One row per YYYY-MM; seed job HARD-STOPS when
-- calls_used >= cap so we never cross the free-tier line into real spend.
CREATE TABLE places_quota (
  year_month TEXT PRIMARY KEY,              -- 'YYYY-MM'
  calls_used INT NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);