---
name: "AC Tool"
client: Oliver Agency (Internal)
status: active
tech: [React, TypeScript, Quart, Python, PostgreSQL, Docker, Azure AD]
local_path: /Users/ai_leed/Documents/Projects/Oliver/ac-tool
deploy: sudo bash /opt/ac-tool/deploy.sh
url: https://ai-sandbox.oliver.solutions/ac-helper/
tags: [oliver, activation-calendar]
created: 2026-04-14
server: optical-web-1
port: 8100
db: PostgreSQL 16
---

## Overview

AC Tool (Activation Calendar Tool / AC Helper) is an internal web application for Oliver Agency that combines AI-powered brief extraction with a collaborative spreadsheet editor. Teams upload client campaign briefs in multiple formats (PDF, PPTX, DOCX, XLSX), AI models extract and structure deliverables, users review and edit the results in an interactive calendar grid, and export formatted CSV files. The system orchestrates multiple LLM providers in parallel, consolidates results, and provides real-time job progress updates via WebSocket.

## Tech Stack

- **Frontend:** React 18 + TypeScript, Vite (build), Handsontable (spreadsheet editor), MSAL (Azure AD auth), Axios (HTTP client)
- **Backend:** Quart (async Python web framework), Hypercorn (ASGI server), asyncio task workers
- **Database:** PostgreSQL 16 (asyncpg async driver)
- **Infrastructure:** Docker + Docker Compose, Apache reverse proxy (optical-web-1)
- **AI/ML:** Anthropic Claude, Google Gemini (parallel LLM analysis + consolidation)
- **Key libraries:** LibreOffice (document parsing), PyMuPDF, python-pptx, openpyxl, Handsontable, MSAL.js

## Architecture

AC Tool is a three-layer system deployed on a single server:

**Frontend (React SPA):** Built with Vite, served as static files by Apache at `/ac-helper/`. Handles user auth via MSAL (Azure AD PKCE flow), brief upload UI, deliverable review, interactive spreadsheet editing with Handsontable, and natural-language AI commands.

**Backend (Quart API):** Python async web framework running on Hypercorn (Docker container, port 8000 → proxied as `localhost:8100` by Apache). Seven API blueprints handle auth, job management, sheet persistence, exports, AI commands, dropdowns/enums, admin operations, and client management. A WebSocket handler (`/ws`) broadcasts real-time job progress to connected clients.

**Database (PostgreSQL 16):** Stores sheets (activation calendars with structured rows), client metadata, category hierarchies, and audit logs. Connected via asyncpg async pool. Old JSON-based storage migrated to PostgreSQL at deployment time.

**Job Processing Pipeline:**
1. User uploads brief file → `JobManager` creates in-memory job, writes file to `/app/data/uploads`
2. Background worker extracts text (LibreOffice, PyMuPDF, python-pptx, openpyxl) based on file type
3. Multiple LLM providers (Anthropic + Gemini) analyze extracted text in parallel
4. Consolidation model merges provider outputs into a single structured list
5. CSV generation writes deliverables to `/app/data/outputs`
6. WebSocket broadcasts progress (`job.created` → `job.accepted` → `job.completed` or `job.failed`) to user in real-time
7. User reviews, edits, and imports into a persistent sheet

```
Browser (React SPA on :5173 dev / Apache prod)
  ↓ HTTPS + WebSocket
Apache (ai-sandbox.oliver.solutions)
  ├─ /ac-helper/         → /var/www/html/ac-helper/ (static)
  ├─ /ac-helper/api/*    → http://localhost:8100/api/ (Docker)
  └─ /ac-helper/ws       → ws://localhost:8100/ws (Docker)
          ↓
Docker: ac-tool (Hypercorn :8000 → host :8100)
  ├─ Quart app (routes, WebSocket, background workers)
  ├─ JobManager (in-memory queue + file I/O)
  └─ asyncpg connection pool
          ↓
Docker: ac-tool-db (PostgreSQL :5432)
```

## Dev Commands

```bash
# Clone repo
git clone git@bitbucket.org:zlalani/ac-helper.git ac-tool
cd ac-tool

# Setup local environment
cp .env.example .env
# Edit .env: set DEV_MODE=true, add GEMINI_API_KEY

# Terminal 1: Frontend dev server (Vite, :5173)
cd frontend
npm install
npm run dev

# Terminal 2: Backend dev server (Quart, :8000)
cd backend
pip install -r requirements.txt
docker compose up -d postgres  # Start PostgreSQL only in Docker
python run_server.py

# In DEV_MODE=true, auth bypasses Azure AD (auto-grants admin role)
# Frontend proxies to http://localhost:8000 (vite.config.ts)
# Access app at http://localhost:5173
```

## Deployment

- **Server:** `optical-web-1` (ai-sandbox.oliver.solutions)
- **Deploy:** `sudo bash /opt/ac-tool/deploy.sh`
- **URL:** `https://ai-sandbox.oliver.solutions/ac-helper/`
- **Port:** 8100 (Docker mapped; 8000 internal Hypercorn)
- **Service:** None (Docker Compose managed)
- **Local path:** `/Users/ai_leed/Documents/Projects/Oliver/ac-tool`

**First-time setup:**
1. SSH to `optical-web-1`
2. Clone to `/opt/ac-tool`: `git clone git@bitbucket.org:zlalani/ac-helper.git /opt/ac-tool`
3. Create `.env` with `GEMINI_API_KEY`, `SESSION_SECRET`, `POSTGRES_PASSWORD`, `ADMIN_EMAILS`
4. Run `sudo bash /opt/ac-tool/deploy.sh`
5. Add Apache VirtualHost config (see infrastructure.md) and reload Apache

**Subsequent deploys:** Run `sudo bash /opt/ac-tool/deploy.sh` (pulls latest, rebuilds Docker, migrates data if needed, health-checks)

**Container logs:**
- App: `docker logs -f ac-tool`
- DB: `docker logs -f ac-tool-db`

## Environment Variables

- `DEV_MODE` — If `true`, bypasses Azure AD auth and auto-grants admin role (dev only)
- `GEMINI_API_KEY` — Google Gemini API key (required for LLM analysis)
- `SESSION_SECRET` — Secret for session signing (required in production)
- `POSTGRES_PASSWORD` — PostgreSQL password (required in production)
- `ADMIN_EMAILS` — Comma-separated list of admin user email addresses (required in production)
- `DATA_DIR` — Root data directory; defaults to `/app/data` (contains uploads, outputs, sheets)
- `UPLOADS_DIR` — Temp directory for uploaded briefs; defaults to `{DATA_DIR}/uploads`
- `OUTPUTS_DIR` — Output CSV directory; defaults to `{DATA_DIR}/outputs`
- `SHEETS_DIR` — Legacy JSON sheets directory; defaults to `{DATA_DIR}/sheets` (used for migration)
- `EMERGENCY_TOKEN` — Bypass token for login when Azure AD unavailable (optional)
- `MAX_CONCURRENT_JOBS` — Max parallel background workers; defaults to 3

## API / Endpoints

**Auth:**
- `POST /api/auth/login` — Azure AD token validation
- `POST /api/auth/logout` — Clear session
- `GET /api/auth/me` — Current user info

**Jobs (brief extraction):**
- `POST /api/jobs` — Upload brief file, create job
- `GET /api/jobs/{job_id}` — Fetch job status/metadata
- `GET /api/jobs/{job_id}/output` — Download generated CSV

**Sheets (activation calendars):**
- `GET /api/sheets` — List user's sheets
- `POST /api/sheets` — Create new sheet
- `GET /api/sheets/{sheet_id}` — Fetch sheet data + rows
- `PUT /api/sheets/{sheet_id}` — Update sheet metadata
- `PUT /api/sheets/{sheet_id}/rows` — Bulk update rows
- `POST /api/sheets/{sheet_id}/import` — Import job output into sheet
- `POST /api/sheets/{sheet_id}/export` — Export sheet as CSV

**AI Commands:**
- `POST /api/ai/command` — Apply natural-language mutation to sheet rows (e.g., "duplicate all rows where Media is Social")

**Admin:**
- `GET /api/admin/clients` — List clients
- `POST /api/admin/clients` — Create client
- `PUT /api/admin/clients/{client_id}` — Update client + category hierarchy

**WebSocket:**
- `GET /ws` — Upgrade to WebSocket; receive real-time job progress (`job.created`, `job.accepted`, `job.completed`, `job.failed`)

## Known Issues

- Legacy JSON-based sheet storage migrated to PostgreSQL; migration runs at deploy time if old files exist. No rollback implemented yet.
- Emergency token login requires manual `.env` configuration; no UI toggle.
- Background job workers are in-memory (not persistent across container restart); jobs in progress will be lost if container crashes.
- CSV export template customization is per-client but lacks UI for managing templates (must be edited in database or code).

## Git

- **Remote:** `git@bitbucket.org:zlalani/ac-helper.git`
- **Recent work:** PostgreSQL migration (8da149b), emergency token auth, AI commands, custom CSV export templates, client management, Azure AD token refresh fixes
- **Branch:** Assume `main` is production-ready

## Sessions
### 2026-04-14 – Project catalogued
**Done:** Added to Obsidian second brain.

---
## Change Log
| Date | Requested | Changed | Files |
|------|-----------|---------|-------|
| 2026-04-14 | Initial setup | Note created | — |

## Related
- [[ac-helper/AC Helper]]