← back to Patty

db/schema.sql

160 lines

-- Patty - The Petition Specialist
-- Database Schema
-- Database: postgresql://dw_admin@127.0.0.1:5432/  # password in .env.local (DATABASE_URL)
--   patty

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,
    org_name TEXT,
    org_type TEXT CHECK (org_type IN ('individual', '501c3', '501c4', 'for_profit', 'other')),
    address TEXT,
    phone TEXT,
    website TEXT,
    gmail_tokens JSONB,
    gdrive_tokens JSONB,
    onboarding_step INTEGER DEFAULT 0,
    is_admin BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE petitions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    summary TEXT,
    body_html TEXT NOT NULL,
    body_text TEXT,
    target TEXT,
    target_emails TEXT[],
    category TEXT,
    tags TEXT[],
    image_url TEXT,
    status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'paused', 'closed', 'delivered')),
    signature_goal INTEGER DEFAULT 100,
    signature_count INTEGER DEFAULT 0,
    share_count INTEGER DEFAULT 0,
    org_type TEXT,
    compliance_flags JSONB DEFAULT '{}',
    ai_trend_score REAL,
    ai_source TEXT,
    ai_topic_data JSONB,
    distribution_platform TEXT DEFAULT 'internal',
    external_url TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_petitions_user ON petitions(user_id);
CREATE INDEX idx_petitions_status ON petitions(status);
CREATE INDEX idx_petitions_slug ON petitions(slug);

CREATE TABLE signatures (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    petition_id UUID NOT NULL REFERENCES petitions(id) ON DELETE CASCADE,
    signer_email TEXT NOT NULL,
    signer_name TEXT,
    signer_zip TEXT,
    signer_comment TEXT,
    is_public BOOLEAN DEFAULT true,
    ip_address TEXT,
    source TEXT DEFAULT 'web',
    opted_in_email BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(petition_id, signer_email)
);

CREATE INDEX idx_signatures_petition ON signatures(petition_id);

CREATE TABLE email_subscribers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    email TEXT NOT NULL,
    name TEXT,
    zip_code TEXT,
    source TEXT DEFAULT 'signup',
    petition_ids UUID[],
    tags TEXT[],
    is_active BOOLEAN DEFAULT true,
    unsubscribed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE email_campaigns (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    petition_id UUID REFERENCES petitions(id) ON DELETE SET NULL,
    subject TEXT NOT NULL,
    body_html TEXT NOT NULL,
    body_text TEXT,
    status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'scheduled', 'sending', 'sent', 'failed')),
    send_to TEXT DEFAULT 'all',
    recipient_count INTEGER DEFAULT 0,
    open_count INTEGER DEFAULT 0,
    click_count INTEGER DEFAULT 0,
    scheduled_at TIMESTAMPTZ,
    sent_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE trending_topics (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    source TEXT NOT NULL,
    source_url TEXT,
    title TEXT NOT NULL,
    content TEXT,
    engagement_score REAL,
    sentiment TEXT,
    category TEXT,
    tags TEXT[],
    raw_data JSONB,
    is_used BOOLEAN DEFAULT false,
    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_trends_source ON trending_topics(source);
CREATE INDEX idx_trends_score ON trending_topics(engagement_score DESC);

CREATE TABLE petition_templates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title TEXT NOT NULL,
    category TEXT,
    body_html TEXT NOT NULL,
    body_text TEXT,
    tags TEXT[],
    usage_count INTEGER DEFAULT 0,
    is_featured BOOLEAN DEFAULT false,
    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(),
    event_type TEXT NOT NULL,
    entity_type TEXT NOT NULL,
    entity_id UUID,
    actor TEXT DEFAULT 'system',
    metadata JSONB,
    ip_address TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 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_petitions_updated BEFORE UPDATE ON petitions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER tr_subscribers_updated BEFORE UPDATE ON email_subscribers FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER tr_campaigns_updated BEFORE UPDATE ON email_campaigns FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER tr_templates_updated BEFORE UPDATE ON petition_templates FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();