---
name: "Activation Calendar Helper"
client: Oliver Agency
status: active
server: optical-web-1
tech: [React 19, TypeScript, Python 3.11, Quart, PostgreSQL 16, Azure AD, Google Gemini, Docker Compose]
local_path: /Users/ai_leed/Documents/Projects/Oliver/ac-helper
deploy: sudo ./deploy.sh
url: https://ai-sandbox.oliver.solutions/ac-helper/
tags: [oliver, activation-calendar, ai, gemini, deliverables]
created: 2026-04-14
port: 8100
db: PostgreSQL 16
---

## Overview
AC Helper is a web-based internal tool for Oliver Agency staff to manage marketing deliverables (activation calendars). It combines an AI-powered natural language command interface for creating and updating deliverables with a brief extractor pipeline that automatically parses uploaded marketing documents (PDF, PPTX, DOCX, XLSX) and converts them into structured rows. The app is live in production on optical-web-1 and serves as the primary interface for activation calendar management.

## Tech Stack
- **Frontend:** React 19 + TypeScript + Vite, Handsontable grid component, Tailwind CSS 4, Zustand state management
- **Backend:** Python 3.11, Quart (async Flask), Hypercorn ASGI server
- **Database:** PostgreSQL 16 with asyncpg connection pool; JSONB columns for structured data
- **Infrastructure:** Docker Compose (2 containers: app + postgres); Apache reverse proxy with TLS termination
- **AI/ML:** Google Gemini (flash model) for natural language commands; parallel LLM analysis (OpenAI GPT-4.1, Google Gemini, Anthropic Claude) for brief extraction
- **Auth:** Azure AD MSAL (PKCE flow); JWT validation with PyJWT; dev mode bypass support
- **Real-time:** WebSocket (Quart native) for job progress streaming
- **Key libraries:** axios, react-query, TypeScript, pydantic, asyncpg, aiohttp, PyJWT

## Architecture

AC Helper follows a client-server architecture with a single Apache reverse proxy fronting all traffic:

```
┌─────────────────────────────────────────────────────────────────┐
│ Browser (React SPA)                                             │
│ - CommandBar (AI natural language input)                        │
│ - Handsontable grid (deliverable editing)                       │
│ - Brief upload interface                                        │
│ - Admin panels                                                  │
└────────────────────┬────────────────────────────────────────────┘
                     │ HTTPS
                     ▼
        ┌─────────────────────────┐
        │ Apache (optical-web-1)  │
        │ - TLS terminator        │
        │ - Reverse proxy         │
        └──┬──────────────────┬───┘
           │                  │
    ┌──────▼─────────┐   ┌───▼──────────┐
    │ Static files   │   │ API/WS proxy │
    │ /var/www/html/ │   │ :8100→:8000  │
    │ ac-helper/     │   └───┬──────────┘
    └────────────────┘       │
                             ▼
            ┌────────────────────────────┐
            │ Docker: ac-tool (Quart)    │
            │ - REST API blueprints      │
            │ - WebSocket handler        │
            │ - Background job workers   │
            │ - asyncpg pool             │
            └────────────┬───────────────┘
                         │
                         ▼
            ┌────────────────────────────┐
            │ Docker: ac-tool-db         │
            │ PostgreSQL 16 (named vol)  │
            └────────────────────────────┘
```

**Key components:**
- **Frontend:** Vite SPA with Zustand state store; Handsontable for grid UI; Quart WebSocket listener for real-time job progress
- **Backend:** Blueprints split by domain (auth, sheets, jobs, ai, export, dropdowns, admin, clients); sheets manager handles CRUD with JSONB persistence; job manager queues and executes background tasks; AI command processor validates against dropdown constraints then calls Gemini
- **Database:** PostgreSQL with sheets stored as JSONB; clients, users, exports as relational tables
- **Job system:** In-memory queue with asyncio locks for concurrency control; background workers process brief extraction, exports, etc.; results broadcast via WebSocket

## Dev Commands

**Option A — Backend only (fastest for API work):**
```bash
cd /Users/ai_leed/Documents/Projects/Oliver/ac-helper
git clone git@bitbucket.org:zlalani/ac-helper.git
cp .env.example .env
# Edit .env: set DEV_MODE=true, GEMINI_API_KEY, POSTGRES_PASSWORD
# Start a separate PostgreSQL container:
docker run -d --name ac-pg \
  -e POSTGRES_DB=achelper \
  -e POSTGRES_USER=achelper \
  -e POSTGRES_PASSWORD=achelper \
  -p 5432:5432 postgres:16-alpine

# Terminal 1: Backend
cd backend
pip install -r requirements.txt
python run_server.py
# Listens on http://localhost:8000

# Terminal 2: Frontend dev server
cd frontend
npm install
npm run dev
# Listens on http://localhost:5173; proxies /api to localhost:8000
```

**Option B — Full Docker Compose (preferred for realistic testing):**
```bash
cd /Users/ai_leed/Documents/Projects/Oliver/ac-helper
cp .env.example .env
# Edit .env: GEMINI_API_KEY, SESSION_SECRET, POSTGRES_PASSWORD (at minimum)
docker compose up --build
# Access http://localhost:8100
```

**Build frontend only:**
```bash
cd frontend && npm run build
# Output: dist/ (served by Apache in prod)
```

**Run tests:**
```bash
cd backend && pytest
```

## Deployment

- **Server:** optical-web-1 (ai-sandbox.oliver.solutions)
- **Deploy:** `sudo ./deploy.sh` (run from project root as root)
- **URL:** https://ai-sandbox.oliver.solutions/ac-helper/
- **Port:** 8100 (internal Docker port; Apache proxies from 443)
- **Service:** Docker Compose (no systemd service; containers use `restart: unless-stopped`)
- **Local path:** /Users/ai_leed/Documents/Projects/Oliver/ac-helper

**Deploy process** (automated by deploy.sh):
1. Validates `.env` exists and required keys are set
2. Checks Docker, docker compose, and git are installed
3. `git pull --ff-only` (no merges)
4. `docker compose build --pull` (rebuilds both containers)
5. Extracts frontend dist to `/var/www/html/ac-helper/` and fixes ownership
6. `docker compose down && docker compose up -d` (blue-green restart)
7. Waits for PostgreSQL healthcheck and app `/health` endpoint
8. **Rollback:** `git revert <commit>` + `./deploy.sh` (or manual `docker compose down && git checkout <previous> && docker compose up`)

## Environment Variables

| Variable | Purpose | Dev default | Production | Required |
|----------|---------|------------|-----------|----------|
| `DEV_MODE` | Bypass Azure AD auth; allow all endpoints | `true` | `false` | Yes |
| `DATABASE_URL` | PostgreSQL connection string | `postgresql://achelper:achelper@localhost:5432/achelper` | Built from `POSTGRES_PASSWORD` | Yes |
| `POSTGRES_PASSWORD` | DB password | `achelper_secret` | Set in `.env` (secret) | Yes |
| `SESSION_SECRET` | JWT signing key | `change-me-in-production` | Long random string (50+ chars) | Yes |
| `GEMINI_API_KEY` | Google Gemini API key (AI commands) | (empty) | Set in `.env` | Yes |
| `GEMINI_MODEL` | Gemini model identifier | `gemini-3-flash-preview` | `gemini-3-flash-preview` | No |
| `OPENAI_API_KEY` | OpenAI API key (brief extractor) | (empty) | Set in `.env` | Optional |
| `OPENAI_MODEL` | OpenAI model identifier | `gpt-4.1` | `gpt-4.1` | No |
| `ANTHROPIC_API_KEY` | Anthropic Claude API key (brief extractor) | (empty) | Set in `.env` | Optional |
| `ANTHROPIC_MODEL_SONNET` | Claude model identifier | `claude-sonnet-4-5-20250929` | `claude-sonnet-4-5-20250929` | No |
| `AZURE_TENANT_ID` | Azure AD tenant | (from .env.example) | `e519c2e6-bc6d-4fdf-8d9c-923c2f002385` | Yes (prod) |
| `AZURE_CLIENT_ID` | Azure AD app registration | (from .env.example) | `9079054c-9620-4757-a256-23413042f1ef` | Yes (prod) |
| `AZURE_REDIRECT_URI` | OAuth callback URL | `http://localhost:5173/` | `https://ai-sandbox.oliver.solutions/ac-helper/` | Yes (prod) |
| `ADMIN_EMAILS` | Comma-separated admin emails | (empty) | `daveporter@oliver.agency,vadymsamoilenko@oliver.agency` | No |
| `ADMIN_EMAIL` | Primary admin email | (empty) | `daveporter@oliver.agency` | No |
| `EMERGENCY_TOKEN` | Bypass SSO token (for outages) | (empty) | Set in `.env`; leave blank to disable | Optional |
| `MAX_CONCURRENT_JOBS` | Max parallel job queue size | `5` | `5` | No |
| `MAX_UPLOAD_SIZE_MB` |

## 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-tool/AC Tool]]
- [[gmal-scope-builder/GMAL Scope Builder]]