← back to Grant

db/schema.sql

196 lines

-- Grant App Database Schema
-- Database: grant_app
-- Connection: postgresql://dw_admin@127.0.0.1:5432/  # password in .env.local (DATABASE_URL)
--   grant_app

-- Requires: CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    google_id TEXT UNIQUE,
    email TEXT NOT NULL UNIQUE,
    display_name TEXT,
    avatar_url TEXT,
    phone TEXT,
    role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
    org_id UUID,
    gmail_tokens JSONB,
    drive_tokens JSONB,
    last_login_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    address TEXT,
    city TEXT,
    state TEXT,
    zip TEXT,
    phone TEXT,
    website_url TEXT,
    nonprofit_status TEXT NOT NULL DEFAULT 'planning' CHECK (nonprofit_status IN ('have_501c3', 'have_501c4', 'planning', 'other')),
    ein TEXT,
    onboarding_step INTEGER NOT NULL DEFAULT 0,
    onboarding_complete BOOLEAN NOT NULL DEFAULT false,
    funding_losing_money INTEGER DEFAULT 0,
    funding_free_overhead INTEGER DEFAULT 0,
    funding_working_alone INTEGER DEFAULT 0,
    funding_donations INTEGER DEFAULT 0,
    funding_grants INTEGER DEFAULT 0,
    funding_intern_hours INTEGER DEFAULT 0,
    staff_count INTEGER DEFAULT 1,
    staff_names TEXT[],
    ai_mission TEXT,
    ai_focus_areas TEXT[],
    ai_keywords TEXT[],
    ai_org_summary TEXT,
    ai_profile_json JSONB,
    site_scraped_at TIMESTAMPTZ,
    sample_donation_email TEXT,
    sample_petition_email TEXT,
    sample_general_email TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE users ADD CONSTRAINT fk_users_org FOREIGN KEY (org_id) REFERENCES organizations(id);
CREATE INDEX idx_users_org ON users(org_id);

CREATE TABLE grants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    funder TEXT NOT NULL,
    funder_url TEXT,
    amount_min NUMERIC,
    amount_max NUMERIC,
    description TEXT,
    eligibility TEXT,
    focus_areas TEXT[],
    application_url TEXT,
    deadline TIMESTAMPTZ,
    cycle TEXT CHECK (cycle IN ('annual', 'biannual', 'rolling', 'one-time')),
    ai_fit_score REAL,
    ai_suggestion TEXT,
    status TEXT NOT NULL DEFAULT 'discovered' CHECK (status IN ('discovered', 'researching', 'preparing', 'submitted', 'awarded', 'declined', 'expired')),
    priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('high', 'medium', 'low')),
    notes TEXT,
    applied_at TIMESTAMPTZ,
    amount_requested NUMERIC,
    amount_awarded NUMERIC,
    tags TEXT[],
    is_bookmarked BOOLEAN DEFAULT false,
    grants_gov_id TEXT,
    federal_register_id TEXT,
    source_api TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_grants_org ON grants(org_id);
CREATE INDEX idx_grants_deadline ON grants(deadline);

CREATE TABLE grant_proposals (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    grant_id UUID NOT NULL REFERENCES grants(id) ON DELETE CASCADE,
    org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    subject TEXT NOT NULL,
    recipient_email TEXT,
    recipient_name TEXT,
    body_html TEXT NOT NULL,
    body_text TEXT,
    proposal_type TEXT DEFAULT 'letter_of_inquiry' CHECK (proposal_type IN ('letter_of_inquiry', 'full_proposal', 'one_pager', 'meeting_request')),
    status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'review', 'sent', 'archived')),
    version INTEGER NOT NULL DEFAULT 1,
    sent_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE news_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    headline TEXT NOT NULL,
    outlet TEXT,
    url TEXT,
    published_at TIMESTAMPTZ,
    summary TEXT,
    tags TEXT[],
    relevance_score REAL,
    author_name TEXT,
    author_linkedin TEXT,
    is_used BOOLEAN DEFAULT false,
    source_type TEXT DEFAULT 'google_news',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_news_org ON news_items(org_id);

CREATE TABLE collaborations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    collab_type TEXT NOT NULL CHECK (collab_type IN ('nonprofit', 'politician', 'corporation', 'municipality')),
    name TEXT NOT NULL,
    title TEXT,
    organization TEXT,
    website_url TEXT,
    email TEXT,
    phone TEXT,
    district TEXT,
    state TEXT,
    ai_reason TEXT,
    ai_relevance REAL,
    ai_talking_points TEXT[],
    status TEXT NOT NULL DEFAULT 'suggested' CHECK (status IN ('suggested', 'contacted', 'in_discussion', 'partnered', 'declined', 'archived')),
    notes TEXT,
    last_contacted TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_collabs_org ON collaborations(org_id);
CREATE INDEX idx_collabs_type ON collaborations(collab_type);

CREATE TABLE outreach_templates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    template_type TEXT NOT NULL CHECK (template_type IN ('meeting_request', 'one_pager', 'thank_you', 'follow_up', 'introduction', 'donation_ask')),
    target_type TEXT CHECK (target_type IN ('official', 'corporation', 'nonprofit', 'donor', 'general')),
    title TEXT NOT NULL,
    subject TEXT,
    body_html TEXT NOT NULL,
    body_text TEXT,
    is_ai_generated BOOLEAN DEFAULT true,
    version INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE audit_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    org_id UUID,
    user_id UUID,
    event_type TEXT NOT NULL,
    entity_type TEXT NOT NULL,
    entity_id UUID,
    metadata JSONB,
    ip_address TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_audit_org ON audit_events(org_id);

-- Updated_at triggers
CREATE OR REPLACE FUNCTION update_updated_at_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_users_updated BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER tr_orgs_updated BEFORE UPDATE ON organizations FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER tr_grants_updated BEFORE UPDATE ON grants FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER tr_proposals_updated BEFORE UPDATE ON grant_proposals FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER tr_news_updated BEFORE UPDATE ON news_items FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER tr_collabs_updated BEFORE UPDATE ON collaborations FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER tr_templates_updated BEFORE UPDATE ON outreach_templates FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();