Slack Integration for AAO Membership & Working Group Sync

## Overview

Build a Slack app to help AAO admins understand the relationship between:
- Slack workspace members ↔ AAO member organizations
- Slack channels ↔ AAO working groups

This enables admins to:
1. See which Slack users are/aren't AAO members
2. Compare working group memberships vs Slack channel memberships
3. Identify sync gaps (people in channels who shouldn't be, people missing from channels)

## Core Features

### 1. Member Verification Dashboard
**What it shows:**
- All Slack workspace members with their AAO membership status
- For each user: email, Slack display name, AAO org (if member), working groups

**Use cases:**
- "Who in our Slack workspace isn't an AAO member?"
- "Which AAO members haven't joined our Slack?"

### 2. Channel-to-Working-Group Sync View
**What it shows:**
- For each working group with a linked Slack channel:
  - Members in AAO working group but NOT in Slack channel
  - Members in Slack channel but NOT in AAO working group
  - Properly synced members

**Use cases:**
- "Protocol Development WG has 5 members, but the #protocol-dev channel has 12 people - who are the extras?"
- "3 people joined the TSC working group but aren't in #tsc channel yet"

### 3. Slash Commands (Optional/Future)
```
/aao-lookup @user        → Show AAO membership status for a user
/aao-channel-audit       → Audit current channel vs linked working group
/aao-sync-report         → Generate full sync report (posts to admin channel)
```

## Technical Architecture

```
┌─────────────────┐     ┌──────────────────┐     ┌─────────────────┐
│   Slack API     │────▶│  AAO Slack App   │────▶│  AAO Registry   │
│                 │     │   (New Service)   │     │  (Existing API) │
└─────────────────┘     └──────────────────┘     └─────────────────┘
                               │
                               ▼
                        ┌──────────────────┐
                        │  Admin Dashboard │
                        │  /admin/slack    │
                        └──────────────────┘
```

## Slack API Scopes Needed

| Scope | Purpose |
|-------|---------|
| `users:read` | List all workspace members |
| `users:read.email` | Get user emails for matching |
| `channels:read` | List public channels |
| `groups:read` | List private channels (if needed) |
| `channels:members` | Get channel membership |
| `commands` | Slash commands (optional) |
| `chat:write` | Post reports to admin channel |

## Configuration

**Environment Variables (for single-workspace app):**
```
SLACK_BOT_TOKEN=xoxb-...        # Bot token from Slack app
SLACK_SIGNING_SECRET=...         # For verifying Slack requests
SLACK_ADMIN_CHANNEL_ID=C...      # Channel to post reports to
```

No OAuth token database table needed since this is a single-workspace installation.

## Database Schema

**Migration: `025_slack_integration.sql`**

```sql
-- Cache Slack user info (refreshed periodically)
CREATE TABLE slack_users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slack_user_id VARCHAR(50) UNIQUE NOT NULL,
  slack_email VARCHAR(255),
  slack_display_name VARCHAR(255),
  slack_real_name VARCHAR(255),
  is_bot BOOLEAN DEFAULT false,
  is_deleted BOOLEAN DEFAULT false,
  -- Link to AAO user (matched by email)
  aao_user_id VARCHAR(255),
  aao_org_name VARCHAR(255),
  last_synced_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_slack_users_email ON slack_users(slack_email);
CREATE INDEX idx_slack_users_aao_user ON slack_users(aao_user_id);

-- Link Slack channels to working groups
CREATE TABLE slack_channel_mappings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slack_channel_id VARCHAR(50) UNIQUE NOT NULL,
  slack_channel_name VARCHAR(255) NOT NULL,
  working_group_id UUID REFERENCES working_groups(id) ON DELETE SET NULL,
  is_auto_matched BOOLEAN DEFAULT false,  -- true if matched by name
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Cache channel membership (refreshed periodically)
CREATE TABLE slack_channel_members (
  slack_channel_id VARCHAR(50) NOT NULL,
  slack_user_id VARCHAR(50) NOT NULL,
  last_synced_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (slack_channel_id, slack_user_id)
);

CREATE INDEX idx_slack_channel_members_user ON slack_channel_members(slack_user_id);
```

## API Endpoints

```
# Sync operations (admin only)
POST /api/admin/slack/sync              → Refresh all Slack data (users, channels, members)
POST /api/admin/slack/sync-users        → Refresh just Slack users
POST /api/admin/slack/sync-channels     → Refresh just channels

# Reports (admin only)  
GET  /api/admin/slack/members           → All Slack users with AAO status
GET  /api/admin/slack/non-members       → Slack users who aren't AAO members
GET  /api/admin/slack/channel-audit/:id → Channel vs working group comparison

# Channel-WG linking (admin only)
GET  /api/admin/slack/channels          → List channels with WG mappings
PUT  /api/admin/slack/channels/:id      → Link/unlink channel to working group

# Export
GET  /api/admin/slack/export?format=csv → Export full sync report as CSV
```

## UI Design

### Admin Page: `/admin/slack`

**Tab 1: Members**
```
┌─────────────────────────────────────────────────────────────┐
│ Slack ↔ AAO Member Sync                    [Sync Now] [Export]
├─────────────────────────────────────────────────────────────┤
│ Summary: 45 Slack users | 38 AAO members | 7 non-members    │
├─────────────────────────────────────────────────────────────┤
│ Filter: [All ▼] [☐ Show only non-members]                   │
├─────────────────────────────────────────────────────────────┤
│ Slack User          │ Email              │ AAO Status       │
├─────────────────────┼────────────────────┼──────────────────┤
│ John Smith          │ john@acme.com      │ ✓ Acme Corp      │
│ Jane Doe            │ jane@example.com   │ ✓ Example Inc    │
│ Bob Wilson          │ bob@random.com     │ ✗ Not a member   │
└─────────────────────┴────────────────────┴──────────────────┘
```

**Tab 2: Channel Sync**
```
┌─────────────────────────────────────────────────────────────┐
│ Channel ↔ Working Group Sync                                │
├─────────────────────────────────────────────────────────────┤
│ Channel             │ Working Group       │ Status          │
├─────────────────────┼─────────────────────┼─────────────────┤
│ #protocol-dev       │ Protocol Dev WG     │ ⚠️ 6 mismatches │
│ #tsc                │ TSC                 │ ✓ Synced        │
│ #general            │ (not linked)        │ —               │
└─────────────────────┴─────────────────────┴─────────────────┘

Click a row to see detailed audit:
┌─────────────────────────────────────────────────────────────┐
│ #protocol-dev ↔ Protocol Development WG                     │
├─────────────────────────────────────────────────────────────┤
│ ⚠️  In Slack but NOT in Working Group (4):                  │
│    • bob@random.com (not AAO member)                        │
│    • alice@acme.com (AAO member, hasn't joined WG)          │
│                                                             │
│ ⚠️  In Working Group but NOT in Slack (2):                  │
│    • charlie@example.com                                    │
│    • diana@bigco.com                                        │
│                                                             │
│ ✓  Properly synced (6): john@..., jane@..., ...             │
└─────────────────────────────────────────────────────────────┘
```

## Implementation Phases

### Phase 1: Read-Only Dashboard (MVP)
- [ ] Create migration `025_slack_integration.sql`
- [ ] Add Slack API client (`server/src/slack/client.ts`)
- [ ] Implement sync endpoints (users, channels, members)
- [ ] Create admin page `/admin/slack`
- [ ] Members tab with AAO status
- [ ] CSV export

### Phase 2: Channel-Working Group Linking
- [ ] Channel list with auto-match suggestions
- [ ] Manual channel-to-WG linking UI
- [ ] Channel audit view (in Slack vs in WG)
- [ ] Store `slack_channel_id` on working_groups table (alternative to mapping table)

### Phase 3: Automated Reports (Optional)
- [ ] Scheduled daily sync job
- [ ] Weekly report posted to #aao-admin
- [ ] Alert when new non-member joins workspace

### Phase 4: Slack Commands (Optional)
- [ ] `/aao-lookup @user`
- [ ] `/aao-channel-audit`
- [ ] Slash command handling endpoint

## Slack App Setup Instructions

1. Go to https://api.slack.com/apps
2. Create New App → From scratch
3. Name: "AAO Admin" (or similar)
4. Select your AAO workspace
5. Go to "OAuth & Permissions"
6. Add Bot Token Scopes:
   - `users:read`
   - `users:read.email`
   - `channels:read`
   - `groups:read`
   - `channels:members`
   - `chat:write`
7. Install to Workspace
8. Copy Bot User OAuth Token → `SLACK_BOT_TOKEN` env var
9. Go to "Basic Information" → Copy Signing Secret → `SLACK_SIGNING_SECRET` env var

## Acceptance Criteria

- [ ] Admin can see all Slack workspace members with their AAO membership status
- [ ] Admin can filter to show only non-members
- [ ] Admin can link Slack channels to working groups
- [ ] Admin can see channel audit showing who's in Slack vs who's in the WG
- [ ] Admin can export full sync report as CSV
- [ ] Data is cached and can be refreshed on-demand
- [ ] All endpoints require admin authentication

## Questions/Decisions

1. **Auto-invite to channels?** - Should we add ability to invite missing WG members to Slack channels? (Future phase)
2. **Notifications?** - Should we DM users who are in channels but not WG members? (Future phase)
3. **Private channels?** - Do we need to audit private channels or just public? (Probably both)

---

Related: Working groups feature was implemented in this branch.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Slack Integration for AAO Membership & Working Group Sync #350

Overview

Core Features

1. Member Verification Dashboard

2. Channel-to-Working-Group Sync View

3. Slash Commands (Optional/Future)

Technical Architecture

Slack API Scopes Needed

Configuration

Database Schema

API Endpoints

UI Design

Admin Page: `/admin/slack`

Implementation Phases

Phase 1: Read-Only Dashboard (MVP)

Phase 2: Channel-Working Group Linking

Phase 3: Automated Reports (Optional)

Phase 4: Slack Commands (Optional)

Slack App Setup Instructions

Acceptance Criteria

Questions/Decisions

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Scope	Purpose
`users:read`	List all workspace members
`users:read.email`	Get user emails for matching
`channels:read`	List public channels
`groups:read`	List private channels (if needed)
`channels:members`	Get channel membership
`commands`	Slash commands (optional)
`chat:write`	Post reports to admin channel

Slack Integration for AAO Membership & Working Group Sync #350

Description

Overview

Core Features

1. Member Verification Dashboard

2. Channel-to-Working-Group Sync View

3. Slash Commands (Optional/Future)

Technical Architecture

Slack API Scopes Needed

Configuration

Database Schema

API Endpoints

UI Design

Admin Page: /admin/slack

Implementation Phases

Phase 1: Read-Only Dashboard (MVP)

Phase 2: Channel-Working Group Linking

Phase 3: Automated Reports (Optional)

Phase 4: Slack Commands (Optional)

Slack App Setup Instructions

Acceptance Criteria

Questions/Decisions

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Admin Page: `/admin/slack`