# UTM Cleaner & Validator · utm-cleaner-validator

Messy marketing URLs waste spend and pollute analytics. This one-page PHP app cleans URLs, normalizes/append UTM parameters, validates domains, and (optionally) generates tracked redirect links with click logging so you ship consistent, trustworthy links.

## Features
- Input methods: bulk paste (textarea), single URL input, and CSV upload (.csv).
- UTM handling:
  - Modes: overwrite, preserve, merge
  - Lowercase option for values
  - Force HTTPS
  - Strip common trackers (e.g., fbclid, gclid, msclkid, igshid, etc.)
  - UTM template fields: source, medium, campaign, term, content
- Validation:
  - Domain whitelist (CSV), optional enforcement
  - Per-URL warnings reported in results
- Redirect links:
  - Optional creation of short redirect URLs on your domain
  - GET /r/{id} logs click (IP, UA, referrer, timestamp) then 302 to cleaned URL
- History:
  - Stores “runs,” links, and clicks for review
  - Simple table view of previous runs
- Exports:
  - Per-run CSV and JSON exports of cleaned and redirect URLs
- Safety and performance:
  - CSP and other secure headers
  - CSRF protection for POST
  - Prepared statements + output escaping
  - Rate limiting (default 60/min)
- Operational:
  - Optional daily purge of old runs/clicks
  - Freemium quotas (default free: 100 URLs/run, pro: 1000 URLs/run)
- Optional AI assist:
  - If configured, suggests consistent UTM values and flags anomalies
  - Fully functional offline when AI is disabled

## Requirements
- PHP 8.2+ (extensions: pdo_mysql, json, mbstring, filter, fileinfo, openssl)
- MySQL 8.0+
- Web server that can route /r/* to index.php (Apache, Nginx, or PHP built-in server)

## Quick start
1. Database
   - Create a database and user in MySQL 8.
   - Create tables: runs, links, clicks, rate_limits (see schema in repo or your deployment notes).
2. Configure environment
   - Create a .env file in the app root and set variables from the table below (DB, APP_SECRET, etc.).
3. Run locally
   - Start the PHP built-in server: php -S 127.0.0.1:8000
   - Open http://127.0.0.1:8000
4. Production deploy
   - Point your virtual host’s document root at the app directory.
   - Ensure HTTPS is enabled.
   - Add a rewrite for /r/{id} to route through index.php (so clicks can be logged before redirect).
   - Set secure headers at the server or allow the app to emit them.
5. Optional cron
   - If PURGE_DAYS > 0, schedule a daily task (e.g., curl to an internal maintenance endpoint or invoke a small CLI helper) to delete runs/clicks older than PURGE_DAYS.

## Configuration (.env)
| Variable | Required | Default | Purpose |
|---|---|---|---|
| APP_ENV | No | production | Environment name (production/development) for error verbosity. |
| APP_URL | Yes | — | Base URL for generating absolute links. |
| APP_SECRET | Yes | — | Long random string used for CSRF HMAC and signing. |
| APP_DB_HOST | Yes | localhost | MySQL host. |
| APP_DB_PORT | No | 3306 | MySQL port. |
| APP_DB_NAME | Yes | — | MySQL database name. |
| APP_DB_USER | Yes | — | MySQL user. |
| APP_DB_PASS | Yes | — | MySQL password. |
| RATE_LIMIT_PER_MINUTE | No | 60 | Requests per IP per minute (stored in rate_limits). |
| FREE_QUOTA | No | 100 | Max URLs per run for free plan. |
| PRO_QUOTA | No | 1000 | Max URLs per run for pro plan. |
| PURGE_DAYS | No | 0 | If > 0, old runs/clicks older than N days are purged. |
| REDIRECT_DOMAIN_DEFAULT | No | — | Default domain used when creating redirect links. |
| OPENAI_API_KEY | No | — | Enables AI assist when present. |
| OPENAI_MODEL | No | — | Model ID to use (any text-capable model supported by your endpoint). |
| OPENAI_TEMPERATURE | No | 0.0 | Sampling temperature for suggestions. |
| OPENAI_BASE_URL | No | https://api.openai.com/v1 | Override for custom/enterprise gateways. |

Notes:
- AI is entirely optional; no data leaves your server unless OPENAI_API_KEY is set.
- If REDIRECT_DOMAIN_DEFAULT is not set, users must provide redirect_domain in the form to emit redirects.

## Usage overview
- Paste URLs (one per line) or upload a CSV.
- Fill in UTM template fields (source, medium, campaign, optional term/content).
- Choose UTM Mode:
  - Overwrite: replace any existing utm_* values
  - Preserve: keep existing utm_* values; only append missing ones
  - Merge: keep existing keys, overwrite only blanks or non-UTM keys as needed
- Options:
  - Lowercase values
  - Force HTTPS
  - Strip common trackers
  - Domain whitelist and optional enforcement
  - Emit redirect links (uses redirect_domain input or REDIRECT_DOMAIN_DEFAULT)
- Output per run:
  - Cleaned URLs
  - Redirect URLs (if enabled)
  - Warnings for invalid/blocked URLs
- Redirect endpoint:
  - GET /r/{id} logs the click then 302 to the cleaned destination.

## Exports
- CSV: GET /export.csv?run_id={id}
- JSON: GET /export.json?run_id={id}
- Exports include: original_url, cleaned_url, redirect_url (if created), and warnings.

## Security measures
- Content Security Policy (CSP)
  - default-src 'self'; base-uri 'none'; frame-ancestors 'none'; form-action 'self'
  - connect-src 'self' plus OpenAI endpoint if AI is enabled
  - img-src 'self' data:; script-src 'self'; style-src 'self'
  - Refine as needed for your deployment
- CSRF protection
  - Session-bound token signed with APP_SECRET; validated on each POST
- Prepared statements and output escaping
  - All DB queries via PDO prepared statements
  - HTML output is escaped; URLs are validated/encoded
- Rate limiting
  - Per-IP per-minute limits stored in rate_limits; default 60/min
- File upload hardening
  - Accepts .csv only; MIME/size checks; sanitized filenames
- Cookies/headers
  - HttpOnly/Secure/SameSite=Lax cookies; X-Content-Type-Options: nosniff; Referrer-Policy: strict-origin-when-cross-origin

## Optional AI behavior and fallback
- When OPENAI_API_KEY is set:
  - Suggests standardized UTM naming (e.g., consistent medium/source), surfaces anomalies, and can recommend merge vs. overwrite for mixed inputs.
  - All prompts are minimal and avoid sending full PII; you control the endpoint via OPENAI_BASE_URL.
- When not set:
  - The app runs entirely offline with deterministic parsing and validation.
  - No features are blocked; only suggestions are omitted.

## Data model (at a glance)
- runs: one per submission; stores settings and counts
- links: each input URL with original, cleaned, redirect token, warnings
- clicks: one row per GET /r/{id} event
- rate_limits: rolling counters by IP/key

## Acceptance checklist
- App name and slug match: “UTM Cleaner & Validator” · utm-cleaner-validator
- One-page PHP app; PHP 8.2+ and MySQL 8 required
- Cleans URLs; normalizes UTM params with overwrite/preserve/merge modes
- Lowercase, force HTTPS, strip common trackers
- Domain whitelist with optional enforcement
- Optional redirect link creation; GET /r/{id} logs and 302
- Stores runs, links, clicks; shows history
- CSV and JSON export per run
- Rate limiting implemented (default 60/min)
- Security: CSP, CSRF, prepared statements, output escaping
- Optional daily purge of aged data
- Optional AI assist with full offline fallback

## License
MIT License (see LICENSE)