← back to Sdcc Awards

cypressaward/scraper/create-awards-table.sql

29 lines

-- Create table for actual cy pres award recipients
CREATE TABLE IF NOT EXISTS scraped_awards (
  id SERIAL PRIMARY KEY,
  recipient_organization TEXT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  amount_text TEXT, -- Original text format like "$5.3 million"
  case_name TEXT NOT NULL,
  court TEXT,
  year INTEGER,
  date_awarded DATE,
  purpose TEXT,
  law_firm TEXT,
  settlement_administrator TEXT,
  source_url TEXT NOT NULL,
  source_title TEXT,
  source_date DATE,
  verified BOOLEAN DEFAULT true,
  notes TEXT,
  hash TEXT UNIQUE,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create indexes for faster queries
CREATE INDEX IF NOT EXISTS idx_awards_recipient ON scraped_awards(recipient_organization);
CREATE INDEX IF NOT EXISTS idx_awards_case ON scraped_awards(case_name);
CREATE INDEX IF NOT EXISTS idx_awards_amount ON scraped_awards(amount);
CREATE INDEX IF NOT EXISTS idx_awards_year ON scraped_awards(year);
CREATE INDEX IF NOT EXISTS idx_awards_verified ON scraped_awards(verified);