`src/migrations/005_create_link_checks_table.sql`

34 lines
-- Create link_checks table for tracking link validation results
CREATE TABLE IF NOT EXISTS link_checks (
  id SERIAL PRIMARY KEY,
  url VARCHAR(2048) NOT NULL,
  source_type VARCHAR(50) NOT NULL, -- 'property', 'activity', etc.
  source_id INTEGER NOT NULL,
  http_status INTEGER,
  is_valid BOOLEAN DEFAULT false,
  error_message TEXT,
  response_time_ms INTEGER,
  last_checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(url, source_type, source_id)
);

-- Create index on URL for faster lookups
CREATE INDEX IF NOT EXISTS idx_link_checks_url ON link_checks(url);

-- Create index on source_type and source_id for faster lookups
CREATE INDEX IF NOT EXISTS idx_link_checks_source ON link_checks(source_type, source_id);

-- Create index on is_valid for filtering broken links
CREATE INDEX IF NOT EXISTS idx_link_checks_valid ON link_checks(is_valid);

-- Create index on last_checked_at for sorting by check date
CREATE INDEX IF NOT EXISTS idx_link_checks_last_checked ON link_checks(last_checked_at DESC);

-- Create trigger to update updated_at timestamp
CREATE TRIGGER update_link_checks_updated_at
  BEFORE UPDATE ON link_checks
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();