`cypressaward/scraper/create-tables.sql`

101 lines
-- Create tables for scraped data with deduplication support

-- Cases table
CREATE TABLE IF NOT EXISTS scraped_cases (
  id SERIAL PRIMARY KEY,
  hash VARCHAR(32) UNIQUE NOT NULL,
  title TEXT,
  court TEXT,
  date_filed TIMESTAMP,
  docket_number TEXT,
  summary TEXT,
  url TEXT,
  source TEXT,
  data_type VARCHAR(50),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Awards table
CREATE TABLE IF NOT EXISTS scraped_awards (
  id SERIAL PRIMARY KEY,
  hash VARCHAR(32) UNIQUE NOT NULL,
  title TEXT,
  recipient TEXT,
  organization TEXT,
  date_awarded TIMESTAMP,
  category TEXT,
  description TEXT,
  url TEXT,
  source TEXT,
  data_type VARCHAR(50),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Law firms table
CREATE TABLE IF NOT EXISTS scraped_law_firms (
  id SERIAL PRIMARY KEY,
  hash VARCHAR(32) UNIQUE NOT NULL,
  name TEXT,
  ranking TEXT,
  location TEXT,
  size TEXT,
  practice_areas TEXT,
  revenue TEXT,
  website TEXT,
  source TEXT,
  data_type VARCHAR(50),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Non-profits table
CREATE TABLE IF NOT EXISTS scraped_nonprofits (
  id SERIAL PRIMARY KEY,
  hash VARCHAR(32) UNIQUE NOT NULL,
  name TEXT,
  category TEXT,
  location TEXT,
  mission TEXT,
  rating TEXT,
  website TEXT,
  source TEXT,
  data_type VARCHAR(50),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- News table
CREATE TABLE IF NOT EXISTS scraped_news (
  id SERIAL PRIMARY KEY,
  hash VARCHAR(32) UNIQUE NOT NULL,
  title TEXT,
  description TEXT,
  url TEXT,
  published_date TIMESTAMP,
  author TEXT,
  category TEXT,
  source TEXT,
  data_type VARCHAR(50),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Scraping logs table
CREATE TABLE IF NOT EXISTS scraping_logs (
  id SERIAL PRIMARY KEY,
  timestamp TIMESTAMP DEFAULT NOW(),
  results JSONB,
  error TEXT,
  status VARCHAR(50)
);

-- Create indexes for better performance
CREATE INDEX idx_cases_hash ON scraped_cases(hash);
CREATE INDEX idx_awards_hash ON scraped_awards(hash);
CREATE INDEX idx_firms_hash ON scraped_law_firms(hash);
CREATE INDEX idx_nonprofits_hash ON scraped_nonprofits(hash);
CREATE INDEX idx_news_hash ON scraped_news(hash);
CREATE INDEX idx_news_published ON scraped_news(published_date DESC);
CREATE INDEX idx_logs_timestamp ON scraping_logs(timestamp DESC);