← back to Sdcc Awards

cypressaward/scraper/create-cases-table.sql

34 lines

-- Create table for current class action cases
DROP TABLE IF EXISTS scraped_cases;

CREATE TABLE scraped_cases (
  id SERIAL PRIMARY KEY,
  case_name TEXT NOT NULL,
  case_number TEXT,
  court TEXT,
  status TEXT,
  date_filed DATE,
  last_update DATE,
  settlement_amount_estimated TEXT,
  settlement_amount DECIMAL(15,2),
  description TEXT,
  law_firms TEXT[],
  potential_cy_pres_recipients TEXT[],
  source_url TEXT,
  source_title TEXT,
  category TEXT,
  plaintiff_count TEXT,
  verified BOOLEAN DEFAULT true,
  hash TEXT UNIQUE,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create indexes for faster queries
CREATE INDEX idx_cases_name ON scraped_cases(case_name);
CREATE INDEX idx_cases_court ON scraped_cases(court);
CREATE INDEX idx_cases_status ON scraped_cases(status);
CREATE INDEX idx_cases_date_filed ON scraped_cases(date_filed);
CREATE INDEX idx_cases_last_update ON scraped_cases(last_update);
CREATE INDEX idx_cases_category ON scraped_cases(category);
CREATE INDEX idx_cases_settlement_amount ON scraped_cases(settlement_amount);