← back to Sdcc Awards

cypressaward/database/schema.sql

372 lines

-- Cy Pres Award Platform Database Schema
-- PostgreSQL 15+ with pgvector extension

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgvector";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- Custom types
CREATE TYPE entity_kind AS ENUM ('nonprofit', 'law_firm', 'court', 'media');
CREATE TYPE case_status AS ENUM ('pending', 'active', 'settled', 'awarded', 'closed');
CREATE TYPE user_role AS ENUM ('np_admin', 'firm_admin', 'staff', 'admin', 'viewer');
CREATE TYPE match_method AS ENUM ('rules', 'embedding', 'manual', 'hybrid');
CREATE TYPE delivery_status AS ENUM ('pending', 'sent', 'delivered', 'bounced', 'failed');
CREATE TYPE intake_status AS ENUM ('draft', 'open', 'reviewing', 'closed');

-- Cases table
CREATE TABLE cases (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    docket VARCHAR(100),
    court VARCHAR(200),
    jurisdiction VARCHAR(100),
    case_type VARCHAR(100),
    category TEXT[],
    summary TEXT,
    status case_status DEFAULT 'pending',
    filing_date DATE,
    settlement_date DATE,
    last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    url TEXT,
    source_hash VARCHAR(64),
    metadata JSONB DEFAULT '{}',
    embedding vector(1536),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_cases_status ON cases(status);
CREATE INDEX idx_cases_category ON cases USING GIN(category);
CREATE INDEX idx_cases_filing_date ON cases(filing_date DESC);
CREATE INDEX idx_cases_embedding ON cases USING ivfflat (embedding vector_cosine_ops);
CREATE INDEX idx_cases_source_hash ON cases(source_hash);

-- Entities table (nonprofits, law firms, courts, media)
CREATE TABLE entities (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    kind entity_kind NOT NULL,
    name VARCHAR(300) NOT NULL,
    ein_or_bar_no VARCHAR(50),
    website VARCHAR(500),
    phone VARCHAR(50),
    emails TEXT[],
    addresses JSONB DEFAULT '[]',
    mission TEXT,
    sectors TEXT[],
    tags TEXT[],
    verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP,
    logo_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_entities_kind ON entities(kind);
CREATE INDEX idx_entities_name_trgm ON entities USING GIN(name gin_trgm_ops);
CREATE INDEX idx_entities_sectors ON entities USING GIN(sectors);
CREATE INDEX idx_entities_tags ON entities USING GIN(tags);
CREATE INDEX idx_entities_verified ON entities(verified);

-- Awards table
CREATE TABLE awards (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    case_id UUID REFERENCES cases(id) ON DELETE CASCADE,
    amount_usd DECIMAL(15, 2),
    date_awarded DATE,
    recipient_entity_id UUID REFERENCES entities(id) ON DELETE SET NULL,
    notes TEXT,
    url TEXT,
    source_hash VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_awards_case_id ON awards(case_id);
CREATE INDEX idx_awards_recipient ON awards(recipient_entity_id);
CREATE INDEX idx_awards_date ON awards(date_awarded DESC);
CREATE INDEX idx_awards_amount ON awards(amount_usd DESC);

-- Nonprofit profiles
CREATE TABLE nonprofit_profiles (
    entity_id UUID PRIMARY KEY REFERENCES entities(id) ON DELETE CASCADE,
    programs JSONB DEFAULT '[]',
    eligibility_notes TEXT,
    geography TEXT[],
    docs_urls TEXT[],
    contacts JSONB DEFAULT '[]',
    annual_budget DECIMAL(15, 2),
    year_founded INTEGER,
    impact_metrics JSONB DEFAULT '{}',
    certifications TEXT[],
    embedding vector(1536),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_nonprofit_geography ON nonprofit_profiles USING GIN(geography);
CREATE INDEX idx_nonprofit_embedding ON nonprofit_profiles USING ivfflat (embedding vector_cosine_ops);

-- Law firm profiles
CREATE TABLE law_firm_profiles (
    entity_id UUID PRIMARY KEY REFERENCES entities(id) ON DELETE CASCADE,
    practice_areas TEXT[],
    intake_links TEXT[],
    investigators JSONB DEFAULT '[]',
    class_action_focus TEXT[],
    open_intake_bool BOOLEAN DEFAULT FALSE,
    bar_admissions TEXT[],
    notable_cases JSONB DEFAULT '[]',
    firm_size VARCHAR(50),
    founding_year INTEGER,
    embedding vector(1536),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_firm_practice_areas ON law_firm_profiles USING GIN(practice_areas);
CREATE INDEX idx_firm_class_action ON law_firm_profiles USING GIN(class_action_focus);
CREATE INDEX idx_firm_open_intake ON law_firm_profiles(open_intake_bool);
CREATE INDEX idx_firm_embedding ON law_firm_profiles USING ivfflat (embedding vector_cosine_ops);

-- Open intakes
CREATE TABLE open_intakes (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    law_firm_id UUID REFERENCES entities(id) ON DELETE CASCADE,
    case_id UUID REFERENCES cases(id) ON DELETE SET NULL,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    requirements JSONB DEFAULT '[]',
    intake_url TEXT,
    status intake_status DEFAULT 'draft',
    deadline DATE,
    estimated_award_range JSONB,
    categories TEXT[],
    embedding vector(1536),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_intakes_firm ON open_intakes(law_firm_id);
CREATE INDEX idx_intakes_case ON open_intakes(case_id);
CREATE INDEX idx_intakes_status ON open_intakes(status);
CREATE INDEX idx_intakes_deadline ON open_intakes(deadline);
CREATE INDEX idx_intakes_categories ON open_intakes USING GIN(categories);
CREATE INDEX idx_intakes_embedding ON open_intakes USING ivfflat (embedding vector_cosine_ops);

-- News articles
CREATE TABLE news (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    outlet VARCHAR(200),
    author VARCHAR(200),
    published_at TIMESTAMP,
    url TEXT UNIQUE,
    summary TEXT,
    full_text TEXT,
    case_ids UUID[],
    entity_ids UUID[],
    topics TEXT[],
    source_hash VARCHAR(64),
    embedding vector(1536),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_news_published ON news(published_at DESC);
CREATE INDEX idx_news_topics ON news USING GIN(topics);
CREATE INDEX idx_news_cases ON news USING GIN(case_ids);
CREATE INDEX idx_news_entities ON news USING GIN(entity_ids);
CREATE INDEX idx_news_source_hash ON news(source_hash);
CREATE INDEX idx_news_embedding ON news USING ivfflat (embedding vector_cosine_ops);

-- Matches between nonprofits and opportunities
CREATE TABLE matches (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    nonprofit_id UUID REFERENCES entities(id) ON DELETE CASCADE,
    law_firm_id UUID REFERENCES entities(id) ON DELETE CASCADE,
    case_id UUID REFERENCES cases(id) ON DELETE CASCADE,
    intake_id UUID REFERENCES open_intakes(id) ON DELETE CASCADE,
    method match_method NOT NULL,
    score DECIMAL(5, 4),
    explanation TEXT,
    tags TEXT[],
    reviewed BOOLEAN DEFAULT FALSE,
    accepted BOOLEAN,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT at_least_one_target CHECK (
        case_id IS NOT NULL OR intake_id IS NOT NULL
    )
);

CREATE INDEX idx_matches_nonprofit ON matches(nonprofit_id);
CREATE INDEX idx_matches_firm ON matches(law_firm_id);
CREATE INDEX idx_matches_case ON matches(case_id);
CREATE INDEX idx_matches_intake ON matches(intake_id);
CREATE INDEX idx_matches_score ON matches(score DESC);
CREATE INDEX idx_matches_created ON matches(created_at DESC);
CREATE INDEX idx_matches_reviewed ON matches(reviewed, accepted);

-- Users
CREATE TABLE users (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    entity_id UUID REFERENCES entities(id) ON DELETE CASCADE,
    role user_role NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    pw_hash VARCHAR(255),
    two_fa_secret VARCHAR(100),
    two_fa_enabled BOOLEAN DEFAULT FALSE,
    email_verified BOOLEAN DEFAULT FALSE,
    terms_accepted_at TIMESTAMP,
    last_login_at TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    preferences JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_entity ON users(entity_id);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_active ON users(is_active);

-- Messages
CREATE TABLE messages (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    thread_id UUID NOT NULL,
    from_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    to_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    subject VARCHAR(500),
    body TEXT,
    attachments JSONB DEFAULT '[]',
    read_at TIMESTAMP,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delivery_status delivery_status DEFAULT 'pending',
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX idx_messages_thread ON messages(thread_id);
CREATE INDEX idx_messages_from ON messages(from_user_id);
CREATE INDEX idx_messages_to ON messages(to_user_id);
CREATE INDEX idx_messages_sent ON messages(sent_at DESC);
CREATE INDEX idx_messages_unread ON messages(to_user_id, read_at) WHERE read_at IS NULL;

-- Email queue
CREATE TABLE email_queue (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    template_key VARCHAR(100) NOT NULL,
    to_email VARCHAR(255) NOT NULL,
    cc_emails TEXT[],
    payload_json JSONB NOT NULL,
    scheduled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sent_at TIMESTAMP,
    status delivery_status DEFAULT 'pending',
    attempts INTEGER DEFAULT 0,
    last_error TEXT,
    message_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_email_queue_status ON email_queue(status);
CREATE INDEX idx_email_queue_scheduled ON email_queue(scheduled_at) WHERE status = 'pending';
CREATE INDEX idx_email_queue_template ON email_queue(template_key);

-- Crawl log
CREATE TABLE crawl_log (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    url TEXT NOT NULL,
    domain VARCHAR(255),
    fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    http_status INTEGER,
    response_time_ms INTEGER,
    checksum VARCHAR(64),
    parser_name VARCHAR(100),
    qps_bucket VARCHAR(50),
    robots_txt_checked BOOLEAN DEFAULT TRUE,
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    next_crawl_at TIMESTAMP
);

CREATE INDEX idx_crawl_log_url ON crawl_log(url);
CREATE INDEX idx_crawl_log_domain ON crawl_log(domain);
CREATE INDEX idx_crawl_log_fetched ON crawl_log(fetched_at DESC);
CREATE INDEX idx_crawl_log_checksum ON crawl_log(checksum);
CREATE INDEX idx_crawl_log_next ON crawl_log(next_crawl_at) WHERE next_crawl_at IS NOT NULL;

-- Audit log
CREATE TABLE audit_log (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    actor_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    actor_ip INET,
    action VARCHAR(100) NOT NULL,
    target_table VARCHAR(100),
    target_id UUID,
    old_values JSONB,
    new_values JSONB,
    at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    meta_json JSONB DEFAULT '{}'
);

CREATE INDEX idx_audit_actor ON audit_log(actor_user_id);
CREATE INDEX idx_audit_action ON audit_log(action);
CREATE INDEX idx_audit_target ON audit_log(target_table, target_id);
CREATE INDEX idx_audit_at ON audit_log(at DESC);

-- Source configurations for crawlers
CREATE TABLE source_configs (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    url_pattern TEXT NOT NULL,
    source_type VARCHAR(50) NOT NULL,
    selectors JSONB NOT NULL,
    pagination_config JSONB,
    rate_limit_qps DECIMAL(5, 2) DEFAULT 1.0,
    enabled BOOLEAN DEFAULT TRUE,
    last_crawled_at TIMESTAMP,
    crawl_frequency_hours INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_source_configs_enabled ON source_configs(enabled);
CREATE INDEX idx_source_configs_type ON source_configs(source_type);

-- Saved searches
CREATE TABLE saved_searches (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,
    search_params JSONB NOT NULL,
    alert_enabled BOOLEAN DEFAULT FALSE,
    alert_frequency VARCHAR(50),
    last_alerted_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_saved_searches_user ON saved_searches(user_id);
CREATE INDEX idx_saved_searches_alert ON saved_searches(alert_enabled) WHERE alert_enabled = TRUE;

-- Functions
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Triggers for updated_at
CREATE TRIGGER update_cases_updated_at BEFORE UPDATE ON cases FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_entities_updated_at BEFORE UPDATE ON entities FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_awards_updated_at BEFORE UPDATE ON awards FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_nonprofit_profiles_updated_at BEFORE UPDATE ON nonprofit_profiles FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_law_firm_profiles_updated_at BEFORE UPDATE ON law_firm_profiles FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_open_intakes_updated_at BEFORE UPDATE ON open_intakes FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_news_updated_at BEFORE UPDATE ON news FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_matches_updated_at BEFORE UPDATE ON matches FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_source_configs_updated_at BEFORE UPDATE ON source_configs FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_saved_searches_updated_at BEFORE UPDATE ON saved_searches FOR EACH ROW EXECUTE FUNCTION update_updated_at();