Enable Addie to create and manage website content via CMS

[bokelley]

Summary

Addie should be able to create and manage website content (perspectives, articles, posts) through the existing CMS system. This would allow members and admins to say things like "Hey Addie, write a post about X and publish it to the website."

Current State

• The perspectives table exists with full CMS capabilities (title, content, status, author, featured image, tags, etc.)
• Addie has a list_perspectives tool to read content
• Missing: Tools to create, edit, and publish content

Proposed Tools

For Members

• create_perspective - Draft a new article/post (starts as draft status)
• get_my_perspectives - List perspectives the user has authored

For Admins

• publish_perspective - Change status from draft to published
• update_perspective - Edit existing content
• archive_perspective - Remove from public view without deleting

Broader Vision

Ideally the perspectives system could be extended to manage all site content:

• Blog posts / articles
• Press releases
• Event announcements
• Working group updates
• Member spotlights

This would make Addie the primary interface for content management, reducing the need for a separate admin UI.

Implementation Notes

• Use existing perspectives table schema
• Follow pattern from create_working_group_post in member-tools.ts
• Consider adding created_by_user_id column to track authorship
• May want approval workflow for non-admin content

Related

• Migration: 018_perspectives.sql
• Existing tool: list_perspectives in server/src/addie/mcp/member-tools.ts

---

Phase 2: Index Shared Files for Search

When users share files in Slack (PDFs, Word docs, images), Addie can now read them in-session, but the content isn't indexed for later retrieval. Users should be able to ask "what did that sustainability report say?" days after the file was shared.

Current Behavior

• Files shared in Slack are processed in real-time during conversations
• Content is not persisted after the conversation ends
• search_slack only searches message text, not file contents

Proposed: indexed_files table

CREATE TABLE indexed_files (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Slack context
  slack_file_id VARCHAR(255) UNIQUE NOT NULL,
  slack_channel_id VARCHAR(255) NOT NULL,
  slack_user_id VARCHAR(255) NOT NULL,
  slack_message_ts VARCHAR(255),
  
  -- File metadata
  filename VARCHAR(500),
  file_type VARCHAR(50),  -- 'pdf', 'docx', 'image', etc.
  file_size INTEGER,
  slack_permalink TEXT,
  
  -- Extracted content
  extracted_text TEXT,  -- Full text for PDFs/Word docs
  ai_summary TEXT,      -- Claude-generated summary
  ai_description TEXT,  -- For images: what Claude sees
  
  -- Search
  search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(filename, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(ai_summary, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(extracted_text, '')), 'C')
  ) STORED,
  
  -- Timestamps
  shared_at TIMESTAMP WITH TIME ZONE,
  indexed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Extend search_slack

• Search indexed_files.search_vector alongside message history
• Return file matches with filename, summary, and link to original message
• Weight file content lower than direct message matches

Example

User: "What did that GMSF report say about carbon offsets?"
Addie: [searches indexed_files, finds the PDF shared 2 weeks ago]
"The GMSF report (shared by @brian on Dec 15) mentions carbon offsets..."