`database/schema.sql`

132 lines
-- CyPresAwards Database Schema
-- PostgreSQL database for storing non-profit organizations and cy pres information

-- Organizations table
CREATE TABLE IF NOT EXISTS organizations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(500) NOT NULL,
    website_url VARCHAR(1000) NOT NULL UNIQUE,
    cypres_page_url VARCHAR(1000) NOT NULL,
    mission_statement TEXT,
    logo_url VARCHAR(1000),
    ein VARCHAR(20), -- Employer Identification Number
    founded_year INTEGER,
    location_city VARCHAR(200),
    location_state VARCHAR(2),
    location_country VARCHAR(100) DEFAULT 'USA',
    scraped_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_verified TIMESTAMP,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Categories/Topics table
CREATE TABLE IF NOT EXISTS categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    parent_category_id INTEGER REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Junction table for organizations and categories (many-to-many)
CREATE TABLE IF NOT EXISTS organization_categories (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    confidence_score DECIMAL(3,2), -- 0.00 to 1.00 for ML-based categorization
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(organization_id, category_id)
);

-- Legal topics table
CREATE TABLE IF NOT EXISTS legal_topics (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    keywords TEXT[], -- Array of keywords for matching
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Junction table for organizations and legal topics
CREATE TABLE IF NOT EXISTS organization_legal_topics (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    legal_topic_id INTEGER NOT NULL REFERENCES legal_topics(id) ON DELETE CASCADE,
    relevance_score DECIMAL(3,2), -- 0.00 to 1.00
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(organization_id, legal_topic_id)
);

-- CyPres statements table (extracted text and metadata)
CREATE TABLE IF NOT EXISTS cypres_statements (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    statement_text TEXT NOT NULL,
    page_title VARCHAR(500),
    extracted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    html_content TEXT, -- Store original HTML for reference
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Scraping log table
CREATE TABLE IF NOT EXISTS scraping_logs (
    id SERIAL PRIMARY KEY,
    url VARCHAR(1000) NOT NULL,
    status VARCHAR(50), -- success, failed, no_cypres_page
    error_message TEXT,
    scraped_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_org_name ON organizations(name);
CREATE INDEX IF NOT EXISTS idx_org_state ON organizations(location_state);
CREATE INDEX IF NOT EXISTS idx_org_active ON organizations(is_active);
CREATE INDEX IF NOT EXISTS idx_org_categories ON organization_categories(organization_id);
CREATE INDEX IF NOT EXISTS idx_category_orgs ON organization_categories(category_id);
CREATE INDEX IF NOT EXISTS idx_org_legal_topics ON organization_legal_topics(organization_id);
CREATE INDEX IF NOT EXISTS idx_legal_topic_orgs ON organization_legal_topics(legal_topic_id);
CREATE INDEX IF NOT EXISTS idx_cypres_org ON cypres_statements(organization_id);

-- Full-text search indexes
CREATE INDEX IF NOT EXISTS idx_org_name_fulltext ON organizations USING gin(to_tsvector('english', name));
CREATE INDEX IF NOT EXISTS idx_mission_fulltext ON organizations USING gin(to_tsvector('english', mission_statement));
CREATE INDEX IF NOT EXISTS idx_statement_fulltext ON cypres_statements USING gin(to_tsvector('english', statement_text));

-- Insert common legal topics
INSERT INTO legal_topics (name, description, keywords) VALUES
('Consumer Protection', 'Cases involving consumer rights, fraud, false advertising', ARRAY['consumer', 'fraud', 'advertising', 'protection', 'ftc']),
('Civil Rights', 'Discrimination, equal rights, voting rights', ARRAY['civil rights', 'discrimination', 'equality', 'voting', 'ada']),
('Environmental Law', 'Pollution, conservation, climate change', ARRAY['environment', 'pollution', 'climate', 'conservation', 'epa']),
('Labor & Employment', 'Wage disputes, workplace discrimination, labor rights', ARRAY['labor', 'employment', 'wage', 'workplace', 'union']),
('Privacy & Data Security', 'Data breaches, privacy violations, GDPR', ARRAY['privacy', 'data', 'security', 'breach', 'gdpr']),
('Healthcare', 'Medical malpractice, healthcare access, insurance', ARRAY['healthcare', 'medical', 'health', 'insurance', 'medicare']),
('Education', 'Student rights, education access, school discrimination', ARRAY['education', 'school', 'student', 'university', 'title ix']),
('Housing', 'Fair housing, tenant rights, homelessness', ARRAY['housing', 'tenant', 'homeless', 'rent', 'eviction']),
('Immigration', 'Immigration rights, asylum, deportation defense', ARRAY['immigration', 'asylum', 'deportation', 'refugee', 'visa']),
('Criminal Justice', 'Wrongful conviction, sentencing reform, prisoner rights', ARRAY['criminal', 'prison', 'conviction', 'sentencing', 'incarceration']),
('Technology & Internet', 'Net neutrality, digital rights, tech policy', ARRAY['technology', 'internet', 'digital', 'tech', 'software']),
('Financial Services', 'Banking, securities fraud, predatory lending', ARRAY['financial', 'banking', 'securities', 'lending', 'credit'])
ON CONFLICT (name) DO NOTHING;

-- Insert common organization categories
INSERT INTO categories (name, description) VALUES
('Legal Aid', 'Organizations providing direct legal services'),
('Advocacy & Policy', 'Policy reform and legislative advocacy organizations'),
('Education & Research', 'Educational institutions and research organizations'),
('Community Development', 'Community-based development and empowerment'),
('Health Services', 'Health and medical service organizations'),
('Environmental Conservation', 'Environmental protection and conservation'),
('Arts & Culture', 'Arts, culture, and humanities organizations'),
('Social Services', 'Social welfare and human services'),
('Technology & Innovation', 'Technology-focused non-profits'),
('Youth & Children', 'Organizations serving youth and children'),
('Veterans Services', 'Services for military veterans'),
('Animal Welfare', 'Animal rights and welfare organizations'),
('International Development', 'Global development and humanitarian aid'),
('Disability Rights', 'Organizations serving people with disabilities'),
('Senior Services', 'Organizations serving elderly populations')
ON CONFLICT (name) DO NOTHING;