← back to Nationalrealestate

db/migrations/002_firm_site.sql

29 lines

-- M-B2: firm website discovery + front-page crawl.
-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.

CREATE TABLE firm_site (
  id SERIAL PRIMARY KEY,
  firm_id INT NOT NULL UNIQUE REFERENCES firm(id),
  url TEXT,
  discovery_method TEXT NOT NULL,
  discovered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  http_status INT,
  title TEXT,
  has_idx_listings BOOLEAN,
  crawl_status TEXT,
  crawled_at TIMESTAMPTZ,
  screenshot_path TEXT
);

CREATE TABLE firm_contacts (
  id SERIAL PRIMARY KEY,
  firm_id INT NOT NULL REFERENCES firm(id),
  kind TEXT NOT NULL CHECK (kind IN ('email', 'phone', 'contact_url')),
  value TEXT NOT NULL,
  source_url TEXT,
  found_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (firm_id, kind, value)
);

CREATE INDEX idx_firm_contacts_firm ON firm_contacts (firm_id);