---
name: "Social Reporting Tool"
client: Oliver Internal
status: active
server: Ubuntu + Apache
tech: [TypeScript, Node.js, React/Vanilla JS, PostgreSQL, Docker, Apache, Apify API, Claude AI]
local_path: /Users/ai_leed/Documents/Projects/Oliver/social-reporting-tool
deploy: git pull && cp frontend/* /var/www/html/social-reporting/ && docker compose -f docker-compose.yml -f docker-compose.prod.yml up -d --build
url: https://your-domain.com/social-reports/
tags:
  - project
created: 2026-04-15
port: 3456
db: PostgreSQL
---

## Overview

Social Listening Pipeline is an automated social media research tool that orchestrates an 8-stage pipeline to scrape TikTok, Instagram, and YouTube via Apify, analyse content with Claude AI, and generate client-ready HTML reports. Built for Oliver Internal, it provides a real-time SSE dashboard for monitoring pipeline execution, cost tracking, saved client briefs, and complete run history backed by PostgreSQL. The platform enforces budget controls, integrates optional Azure AD SSO, and deploys as containerized services behind Apache on Ubuntu production servers.

## Tech Stack

- **Frontend:** Vanilla HTML/CSS/JavaScript (with optional MSAL.js for Azure AD SSO)
- **Backend:** Node.js 20+ with TypeScript, tsx for execution
- **Database:** PostgreSQL 16 (Alpine), pgdata volume persistence
- **Infrastructure:** Docker Compose (local + production), Apache reverse proxy on Ubuntu, static files served from `/var/www/html/social-reporting`
- **AI/ML:** Claude AI (Anthropic API) for analysis; Apify scrapers (TikTok, Instagram, YouTube actors)
- **Key libraries:** `postgres` (native PostgreSQL client), `tsx` (TypeScript executor), Node.js built-in `http` and `EventTarget` for SSE

## Architecture

The system comprises four main layers:

1. **Frontend** (`frontend/`): Vanilla JavaScript dashboard with tabs for running pipelines, viewing reports, managing saved briefs, and run history. Communicates with backend via HTTP POST and SSE for real-time updates.

2. **Backend Server** (`agents/social-listening/dashboard/server.ts`): Node.js HTTP server on port 3456 that:
   - Authenticates requests via HMAC-signed HttpOnly session cookies (`sl_session`)
   - Handles POST `/run` to trigger pipelines (singleton; returns HTTP 409 if one is already running)
   - Streams pipeline progress via SSE (Server-Sent Events)
   - Exposes REST endpoints for briefs, runs, cost history, and report downloads
   - Rate-limits login attempts (5 per 15 min per IP)

3. **Pipeline Orchestrator** (`agents/social-listening/pipeline-v2.ts`): 8-stage sequential processor:
   - **Stage 1 (Brief Validation):** Normalises and validates the client brief schema
   - **Stage 2 (Strategy Review):** Claude AI reviews strategy, suggests up to 3 extra hashtags
   - **Stage 3 (Discovery Scrape):** Apify scrapes TikTok/Instagram/YouTube for matching content
   - **Stage 4 (Data Review):** Claude AI identifies trends and insights from scraped data
   - **Stage 5 (Enrichment Scrape):** Fetches transcripts and additional metadata
   - **Stage 6 (Pre-Report Review):** Claude AI refines findings before report generation
   - **Stage 7 (Desk Research):** Web search for additional context
   - **Stage 8 (Report Generation):** Outputs final HTML report with video embeds

4. **Data Layer** (`agents/social-listening/db.ts`): PostgreSQL client managing:
   - `runs` table: pipeline execution records (brief, start/end timestamps, status, final HTML)
   - `cost_events` table: granular Apify call costs for budget tracking and accountability
   - Supports querying, filtering (by date range), and soft deletes

```
┌─────────────────────────────────────────────────────────┐
│ Frontend (Vanilla JS)                                    │
│ ├─ Dashboard tab (start pipeline, SSE progress)          │
│ ├─ Reports tab (view, download past reports)             │
│ ├─ Briefs tab (save/load client briefs JSON)             │
│ └─ History tab (cost breakdown, delete runs)             │
└────────────────┬────────────────────────────────────────┘
                 │ HTTP + SSE (port 3456)
                 ↓
┌─────────────────────────────────────────────────────────┐
│ Backend Server (Node.js)                                 │
│ ├─ Auth: Cookie-based HMAC sessions                      │
│ ├─ POST /run → Singleton pipeline orchestration          │
│ ├─ SSE /progress → Real-time stage updates + costs       │
│ └─ REST: /briefs, /runs, /costs, /download/:id           │
└────────┬─────────────────────────┬──────────────────────┘
         │                         │
         ↓                         ↓
┌──────────────────┐     ┌─────────────────────┐
│ Pipeline (8 stages)   │ PostgreSQL (pgdata)  │
│ ├─ Brief validation   │ ├─ runs table        │
│ ├─ AI strategy review │ └─ cost_events table │
│ ├─ Apify scraping     │                     │
│ ├─ AI analysis        │                     │
│ ├─ Enrichment scrape  │                     │
│ ├─ Pre-report AI      │                     │
│ ├─ Desk research      │                     │
│ └─ Report gen         │                     │
└──────────────────┘     └─────────────────────┘
         │
         ↓
┌─────────────────────────────────────────────┐
│ External APIs                               │
│ ├─ Apify (TikTok, Instagram, YouTube actors)
│ ├─ Claude AI (Anthropic)                    │
│ └─ Azure AD (optional SSO)                  │
└─────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────┐
│ Apache (Reverse Proxy on Ubuntu)                        │
│ ├─ Frontend static files ← /var/www/html/social-reports │
│ └─ Backend API ← :3456                                   │
└─────────────────────────────────────────────────────────┘
```

## Dev Commands

```bash
# Start full stack with Docker (PostgreSQL + app)
docker compose up -d

# Rebuild images (e.g., after package.json changes)
docker compose up -d --build

# Start dashboard server only (no Docker; requires manual PostgreSQL)
npm install && npm run dashboard

# Run pipeline CLI (dry run; no API calls)
npm run pipeline

# Run pipeline CLI (test mode; mocked data, no live Apify)
npm run pipeline:test

# Run pipeline CLI (live Apify scraping; requires APIFY_LIVE_APPROVED=true)
npm run pipeline:live

# Deploy to production server
git pull && cp frontend/* /var/www/html/social-reporting/ && docker compose -f docker-compose.yml -f docker-compose.prod.yml up -d --build
```

## Deployment

- **Server:** Ubuntu + Apache (production); Docker containers at `/opt/social-reporting`, static files at `/var/www/html/social-reporting`
- **Deploy:** `git pull && cp frontend/* /var/www/html/social-reporting/ && docker compose -f docker-compose.yml -f docker-compose.prod.yml up -d --build`
- **URL:** `https://your-domain.com/social-reports/` (set via `ALLOWED_ORIGIN` env var)
- **Port:** 3456 (dashboard backend; bound to 127.0.0.1 in Docker to prevent direct exposure)
- **Service:** None (containerized; managed via docker compose, not systemd)
- **Local path:** `/Users/ai_leed/Documents/Projects/Oliver/social-reporting-tool`

**Setup:** Run `deploy/setup.sh` on a fresh Ubuntu server for initial provisioning (Apache config, PostgreSQL init, Docker setup).

## Environment Variables

Essential env vars (copy `.env.example` to `.env`):

- `APIFY_TOKEN` — API token for Apify account (required for scraping)
- `ANTHROPIC_API_KEY` — API key for Claude AI (required for analysis stages)
- `APIFY_LIVE_APPROVED` — Set to `true` to enable real Apify scraping; default `false` (dry runs only)
- `APIFY_COST_LIMIT` — Maximum USD spend per pipeline run (default `5.00`); pipeline aborts scraping when exceeded
- `TEST_MODE` — Set to `true` to skip live Apify calls and use mock data (overrides `APIFY_LIVE_APPROVED`)
- `DASHBOARD_PORT` — HTTP port for backend server (default `3456`)
- `DATABASE_URL` — PostgreSQL connection string (e.g., `postgresql://sl_user:password@db:5432/social_listening`)
- `DASH_USER` — Dashboard login username (default `admin`)
- `DASH_PASS` — Dashboard login password (required; must not be `changeme` in production or server refuses to start)
- `SESSION_SECRET` — Random string for HMAC signing session cookies (required in production; use `openssl rand -base64 32`)
- `ALLOWED_ORIGIN` — CORS origin for frontend requests (e.g., `https://your-domain.com`)
- `AZURE_TENANT_ID` — (Optional) Azure AD tenant ID for SSO; requires `AZURE_CLIENT_ID` to be set too
- `AZURE_CLIENT_ID` — (Optional) Azure AD app registration client ID; requires `AZURE_TENANT_ID` to be set too

## Sessions
### 2026-04-15 – Fix frontend deployment to /var/www/html and
**Asked:** Fix frontend deployment to /var/www/html and ensure Apache reloads after copy.
**Done:** Updated deploy.sh and setup.sh to use recursive copy, added Apache reload, and made file copying self-documenting.

### 2026-04-15 – Fix frontend deployment not copying files
**Asked:** Fix frontend deployment not copying files to /var/www/html folder.
**Done:** Updated deploy.sh to recursively copy frontend files and added Apache reload command.

### 2026-04-15 – Set up SSO (SPA) with token
**Asked:** Set up SSO (SPA) with token exchange in browser using Azure AD / MSAL configuration.
**Done:** Implemented Azure AD MSAL configuration with self-hosted MSAL.js library and SSO setup across frontend files.

### 2026-04-15 – Set up SSO (SPA) with Azure
**Asked:** Set up SSO (SPA) with Azure AD / MSAL token exchange in the browser
**Done:** Verified Azure AD configuration and server.ts implementation for SSO token exchange


## Change Log
| Date | Requested | Changed | Files |
|------|-----------|---------|-------|
| 2026-04-15 | Frontend deployment | cp -r recursive copy, Apache systemctl reload, mkdir -p safety check | deploy.sh, setup.sh |
| 2026-04-15 | Frontend deployment | Fixed cp glob pattern to use recursive copy, added Apache reload | deploy.sh, setup.sh |
| 2026-04-15 | SSO setup | Added MSAL.js library and Azure AD configuration, enabled SSO token exchange | frontend/msal-browser.min.js, frontend/config.js, frontend/login.html |
| 2026-04-15 | SSO setup with Azure AD | Azure tenant ID, client ID, redirect URI configured; server.ts verified | server.ts |

## Related