`migrations/002_firm_office_columns.sql`

26 lines
-- Office-clustering columns for firm-focused data model.
-- Tracks neighborhood (Beverly Hills, Century City, DTLA, …), geo, building, derived attorney count.

BEGIN;

ALTER TABLE organizations
  ADD COLUMN IF NOT EXISTS neighborhood    TEXT,
  ADD COLUMN IF NOT EXISTS lat             DOUBLE PRECISION,
  ADD COLUMN IF NOT EXISTS lng             DOUBLE PRECISION,
  ADD COLUMN IF NOT EXISTS geocoded_at     TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS building_name   TEXT,
  ADD COLUMN IF NOT EXISTS attorney_count  INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS practice_areas  TEXT[],
  ADD COLUMN IF NOT EXISTS firm_size_band  TEXT;       -- 'solo' | 'small' | 'medium' | 'large' | 'biglaw'

CREATE INDEX IF NOT EXISTS idx_organizations_neighborhood ON organizations (neighborhood);
CREATE INDEX IF NOT EXISTS idx_organizations_attorney_count ON organizations (attorney_count DESC);

-- Address-canonicalization helpers — used to group "1888 Century Park East, Suite 200"
-- and "1888 Century Park E #200" as the same building.
ALTER TABLE organizations
  ADD COLUMN IF NOT EXISTS address_norm TEXT;
CREATE INDEX IF NOT EXISTS idx_organizations_address_norm ON organizations (address_norm);

COMMIT;