# Technical Specification

## AOLL — Always-On Lead Library
### Engineering Implementation Specification

| Field | Value |
|-------|-------|
| **Document Version** | 1.0 |
| **Status** | Draft — For Engineering Review |
| **Author** | Engineering / Architecture |
| **Last Updated** | July 27, 2026 |
| **Companion Documents** | PRD-AOLL.md, ARCHITECTURE-AOLL.md |
| **Application Repository** | `aoll.com` (CodeIgniter 4 / PHP 8.2) |

---

## Table of Contents

1. [Purpose & Scope](#1-purpose--scope)
2. [System Context](#2-system-context)
3. [Technology Stack](#3-technology-stack)
4. [Data Model](#4-data-model)
5. [API Specification](#5-api-specification)
6. [Search & Indexing](#6-search--indexing)
7. [Data Pipeline Specification](#7-data-pipeline-specification)
8. [Module Implementation Specifications](#8-module-implementation-specifications)
9. [Integration Specifications](#9-integration-specifications)
10. [AI Services Specification](#10-ai-services-specification)
11. [Billing & Entitlements](#11-billing--entitlements)
12. [Security Specification](#12-security-specification)
13. [Observability & Operations](#13-observability--operations)
14. [Testing Strategy](#14-testing-strategy)
15. [Deployment & Environments](#15-deployment--environments)
16. [Feasibility, Limitations & Workarounds](#16-feasibility-limitations--workarounds)
17. [Assumptions](#17-assumptions)

---

## 1. Purpose & Scope

This document defines **how** the AOLL platform specified in the PRD will be implemented. It covers data models, APIs, pipelines, integrations, and non-functional engineering requirements.

**In scope:** Phases 2–5 engineering work building on CHGREL-1255 (auth shell, mock APIs).

**Out of scope:** Marketing CMS content management, internal Jira tooling, mobile native clients.

---

## 2. System Context

### 2.1 Existing Baseline (CHGREL-1255)

| Component | Implementation |
|-----------|----------------|
| Web framework | CodeIgniter 4, PHP 8.2 |
| User DB | MySQL (`cms`) — `aoll_users`, `aoll_contact_queries`, `aoll_demo_requests` |
| Auth | Session-based; Google OAuth; password hash via msacommon encryption |
| Email | Gmail SMTP |
| Dashboard | Server-rendered views; sidebar navigation |
| Product API | Read endpoints return mock JSON; writes return HTTP 501 |

### 2.2 Target State

The application evolves from a **UI shell with mock data** to a **multi-service architecture**:

- **Web App** (CodeIgniter 4): HTTP UI, session auth, orchestration
- **Product DB** (MySQL/PostgreSQL): Companies, contacts, signals, user artifacts
- **Search Index** (OpenSearch): Faceted search across entities
- **Job Queue** (Redis + worker): Alerts, workflows, exports, indexing
- **Object Storage** (S3-compatible): Export files, raw ingestion payloads
- **External Services:** Stripe, HubSpot, Salesforce, LLM provider, data vendors

Refer to **ARCHITECTURE-AOLL.md** for diagrams and service boundaries.

---

## 3. Technology Stack

### 3.1 Recommended Stack

| Layer | Technology | Rationale |
|-------|------------|-----------|
| Web application | CodeIgniter 4, PHP 8.2 | Existing codebase; team familiarity |
| Primary database | **PostgreSQL 15+** (new product DB) | JSONB for filters; better analytics. *MySQL retained for legacy user tables until migrated.* |
| Search | **OpenSearch 2.x** | Faceted filters; horizontal scale; industry standard |
| Cache / queue | **Redis 7** | Session cache, rate limits, job queue |
| Job workers | **PHP CLI workers** or **Python Celery** | Alerts, workflows, ETL triggers |
| Object storage | AWS S3 or compatible | Raw data, CSV exports |
| ETL / batch | Python 3.11 + Apache Airflow (or cron + scripts for v1) | Data pipeline orchestration |
| LLM | OpenAI GPT-4o / Anthropic Claude (configurable) | AI insights and outreach |
| Billing | Stripe Billing + Customer Portal | Self-serve subscriptions |
| Email | SendGrid or Postmark (migrate from Gmail SMTP at scale) | Alert digests, transactional |
| CRM | HubSpot API v3, Salesforce REST API v58+ | OAuth 2.0 integrations |
| Monitoring | Datadog or Grafana + Prometheus | APM, logs, alerts |

### 3.2 Stack Decision: Monolith vs. Microservices

**Decision (v1):** **Modular monolith** (CodeIgniter) + **extracted search service** + **async workers**.

| Approach | When |
|----------|------|
| Keep CodeIgniter as web + API gateway | Phase 2–3 |
| Extract `search-service` (OpenSearch queries) | Phase 2 |
| Extract `pipeline-workers` (Python) | Phase 2 |
| Extract `ai-service` (optional thin wrapper) | Phase 5 |
| Public REST API (Enterprise) | Phase 5 — versioned `/api/v1/` |

**Rationale:** Avoid premature microservice complexity; isolate only where scale boundaries are clear (search, batch).

---

## 4. Data Model

### 4.1 Entity Relationship Overview

```
Workspace (1) ──< (N) User
Workspace (1) ──< (N) SavedSearch
Workspace (1) ──< (N) Workflow
Workspace (1) ──< (N) Connector
Workspace (1) ──< (1) Subscription

Company (1) ──< (N) Contact
Company (1) ──< (N) Scoop
Company (1) ──< (N) IntentSignal
Company (1) ──< (N) CompanyTechnology
Brand (extends Company) ──< (N) BrandAgencyRelationship >── (N) Agency
Topic (1) ──< (N) IntentSignal
User (N) >──< (N) Topic (via user_topics, max 6)
```

All product entities use a **stable UUID** (`entity_id`) produced by the entity resolution layer. Internal surrogate keys (`id`) used for DB joins.

### 4.2 Core Tables

#### 4.2.1 `workspaces`

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| name | VARCHAR(255) | Display name |
| slug | VARCHAR(100) UNIQUE | URL-safe |
| owner_user_id | INT | FK → aoll_users.id |
| plan_tier | ENUM | starter, professional, intelligence, enterprise |
| stripe_customer_id | VARCHAR(255) | Nullable |
| stripe_subscription_id | VARCHAR(255) | Nullable |
| trial_ends_at | TIMESTAMPTZ | Nullable |
| created_at | TIMESTAMPTZ | |

#### 4.2.2 `companies`

| Column | Type | Notes |
|--------|------|-------|
| entity_id | UUID PK | Canonical ID from resolution |
| entity_type | ENUM | company, brand, agency |
| name | VARCHAR(500) | |
| domain | VARCHAR(255) | Indexed |
| website | VARCHAR(500) | |
| industry | VARCHAR(255) | |
| sub_industry | VARCHAR(255) | |
| employee_count | INT | Nullable |
| employee_range | VARCHAR(50) | e.g. "100-500" |
| revenue_range | VARCHAR(50) | |
| hq_city | VARCHAR(100) | |
| hq_state | VARCHAR(100) | |
| hq_country | CHAR(2) | ISO code |
| description | TEXT | |
| logo_url | VARCHAR(500) | |
| funding_stage | VARCHAR(50) | |
| funding_total_usd | BIGINT | Nullable |
| parent_entity_id | UUID | Nullable, FK self |
| media_spend_tier | ENUM | none, low, medium, high, verified — **estimated unless sourced** |
| media_spend_estimate_usd | BIGINT | Nullable; nullable if unverified |
| media_spend_source | VARCHAR(50) | e.g. `public_ad_library`, `vendor`, `model` |
| last_verified_at | TIMESTAMPTZ | |
| created_at | TIMESTAMPTZ | |
| updated_at | TIMESTAMPTZ | |

**Indexes:** `domain`, `entity_type`, `industry`, `hq_country`, `media_spend_tier`.

#### 4.2.3 `contacts`

| Column | Type | Notes |
|--------|------|-------|
| entity_id | UUID PK | |
| company_entity_id | UUID FK | |
| first_name | VARCHAR(100) | |
| last_name | VARCHAR(100) | |
| full_name | VARCHAR(255) | Generated/stored |
| job_title | VARCHAR(255) | |
| department | VARCHAR(100) | |
| seniority | ENUM | c_suite, vp, director, manager, individual |
| email | VARCHAR(255) | |
| phone | VARCHAR(50) | |
| linkedin_url | VARCHAR(500) | |
| location | VARCHAR(255) | |
| verification_status | ENUM | verified, likely, unverified |
| verification_checked_at | TIMESTAMPTZ | |
| last_verified_at | TIMESTAMPTZ | |

**Privacy note:** Contact views increment usage meter; email may be masked on Starter tier until "revealed."

#### 4.2.4 `scoops`

| Column | Type | Notes |
|--------|------|-------|
| id | BIGSERIAL PK | |
| company_entity_id | UUID FK | |
| scoop_type | VARCHAR(50) | funding, leadership, product, hiring, etc. |
| category | VARCHAR(50) | Maps to alert categories |
| title | VARCHAR(500) | |
| description | TEXT | |
| source_url | VARCHAR(1000) | |
| signal_date | DATE | |
| confidence_score | DECIMAL(3,2) | 0.00–1.00 |
| ai_explanation | TEXT | Nullable |
| topics | JSONB | Array of topic strings |
| last_verified_at | TIMESTAMPTZ | |
| created_at | TIMESTAMPTZ | |

#### 4.2.5 `topics`

| Column | Type | Notes |
|--------|------|-------|
| id | SERIAL PK | |
| slug | VARCHAR(100) UNIQUE | |
| name | VARCHAR(255) | |
| category | VARCHAR(100) | |
| is_active | BOOLEAN | |

#### 4.2.6 `intent_signals`

| Column | Type | Notes |
|--------|------|-------|
| id | BIGSERIAL PK | |
| company_entity_id | UUID FK | |
| topic_id | INT FK | |
| signal_date | DATE | |
| signal_score | INT | 0–100 |
| audience_strength | DECIMAL(3,2) | 0.00–1.00 |
| baseline_score | DECIMAL(10,4) | For spike detection |
| source | VARCHAR(50) | `bombora`, `proxy_composite`, `vendor` |
| raw_payload | JSONB | Nullable |
| created_at | TIMESTAMPTZ | |

**Unique constraint:** `(company_entity_id, topic_id, signal_date)`.

#### 4.2.7 `user_topics`

| Column | Type | Notes |
|--------|------|-------|
| workspace_id | UUID FK | |
| user_id | INT FK | |
| topic_id | INT FK | |
| assigned_at | TIMESTAMPTZ | |

**Constraint:** Max 6 topics per user enforced at application layer.

#### 4.2.8 `saved_searches`

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| workspace_id | UUID FK | |
| user_id | INT FK | |
| name | VARCHAR(255) | |
| description | VARCHAR(250) | |
| entity_type | ENUM | companies, brands, agencies |
| filters | JSONB | Serialized filter state |
| alert_enabled | BOOLEAN | |
| alert_notify_on | JSONB | `["contacts","companies","scoops"]` |
| alert_frequency | ENUM | daily, weekly |
| alert_day_of_week | SMALLINT | 0–6 for weekly |
| email_notifications | BOOLEAN | |
| is_favourite | BOOLEAN | |
| last_run_at | TIMESTAMPTZ | |
| created_at | TIMESTAMPTZ | |

#### 4.2.9 `alerts`

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| workspace_id | UUID FK | |
| saved_search_id | UUID FK | |
| company_entity_id | UUID FK | Nullable |
| category | VARCHAR(50) | |
| title | VARCHAR(500) | |
| body | TEXT | |
| is_read | BOOLEAN | Default false |
| alert_date | TIMESTAMPTZ | |
| created_at | TIMESTAMPTZ | |

#### 4.2.10 `workflows`

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| workspace_id | UUID FK | |
| name | VARCHAR(255) | |
| status | ENUM | active, inactive |
| trigger_type | ENUM | intent, saved_search, scoops |
| trigger_config | JSONB | Topic ID, search ID, or scoop category |
| schedule_frequency | ENUM | daily, weekly |
| schedule_day | SMALLINT | |
| schedule_time | TIME | |
| actions | JSONB | Array of `{type, config}` |
| last_run_at | TIMESTAMPTZ | |
| next_run_at | TIMESTAMPTZ | |
| created_at | TIMESTAMPTZ | |

#### 4.2.11 `connectors`

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| workspace_id | UUID FK | |
| provider | ENUM | hubspot, salesforce |
| status | ENUM | connected, disconnected, error |
| access_token_enc | TEXT | Encrypted |
| refresh_token_enc | TEXT | Encrypted |
| token_expires_at | TIMESTAMPTZ | |
| external_account_id | VARCHAR(255) | |
| field_mapping | JSONB | |
| last_sync_at | TIMESTAMPTZ | |
| last_error | TEXT | |

#### 4.2.12 `usage_events`

| Column | Type | Notes |
|--------|------|-------|
| id | BIGSERIAL PK | |
| workspace_id | UUID FK | |
| user_id | INT FK | |
| event_type | ENUM | search, contact_view, export, ai_generation |
| quantity | INT | Default 1 |
| metadata | JSONB | |
| created_at | TIMESTAMPTZ | |

**Usage aggregation:** Daily rollup table `usage_daily` for billing enforcement.

#### 4.2.13 `feed_preferences`

| Column | Type | Notes |
|--------|------|-------|
| user_id | INT PK | |
| industries | JSONB | |
| locations | JSONB | |
| company_sizes | JSONB | |
| media_spend_tiers | JSONB | |
| intent_topic_ids | JSONB | |
| technology_filters | JSONB | |

#### 4.2.14 `saved_signals`

| Column | Type | Notes |
|--------|------|-------|
| user_id | INT FK | |
| scoop_id | BIGINT FK | Nullable |
| intent_signal_id | BIGINT FK | Nullable |
| saved_at | TIMESTAMPTZ | |

### 4.3 OpenSearch Index Mappings (Summary)

**Index: `companies`**

```json
{
  "entity_id": "keyword",
  "entity_type": "keyword",
  "name": "text + keyword",
  "domain": "keyword",
  "industry": "keyword",
  "employee_count": "integer",
  "revenue_range": "keyword",
  "hq_country": "keyword",
  "hq_state": "keyword",
  "media_spend_tier": "keyword",
  "technologies": "keyword",
  "intent_topic_ids": "integer",
  "max_intent_score": "integer",
  "has_recent_scoop": "boolean",
  "last_signal_at": "date"
}
```

**Index: `contacts`**, **Index: `scoops`** — analogous mappings.

**Sync strategy:** CDC via application events post-write, or nightly full reindex for v1 (simpler, acceptable lag ≤24h for non-search-critical fields).

---

## 5. API Specification

### 5.1 Conventions

| Property | Value |
|----------|-------|
| Base path (authenticated) | `/api/v1/` |
| Auth | Session cookie (web) or `Authorization: Bearer` (Enterprise API) |
| Format | JSON |
| Errors | `{ "error": { "code": "...", "message": "..." } }` |
| Pagination | `?page=1&per_page=25` → `{ data, meta: { total, page, per_page } }` |

### 5.2 Endpoint Catalog

#### Search

| Method | Path | Description |
|--------|------|-------------|
| GET | `/api/v1/search/companies` | Faceted company search |
| GET | `/api/v1/search/brands` | Brand search (entity_type=brand) |
| GET | `/api/v1/search/agencies` | Agency search |
| GET | `/api/v1/search/contacts` | Contact search |
| GET | `/api/v1/search/scoops` | Scoop search |
| POST | `/api/v1/search/export` | Export selection (CSV job) |

**Query parameters (companies example):**

```
?q=acme
&industry=Software
&employee_min=100
&employee_max=500
&revenue_range=10M-50M
&media_spend_tier=high
&location_country=US
&location_state=CA
&technology=salesforce
&intent_topic_id=42
&page=1
&per_page=25
&sort=relevance|media_spend|intent_score
```

#### Entities

| Method | Path | Description |
|--------|------|-------------|
| GET | `/api/v1/companies/{entity_id}` | Company profile |
| GET | `/api/v1/companies/{entity_id}/contacts` | Decision makers |
| GET | `/api/v1/companies/{entity_id}/scoops` | Scoops |
| GET | `/api/v1/companies/{entity_id}/intent` | Intent signals |
| GET | `/api/v1/companies/{entity_id}/technologies` | Tech stack |
| GET | `/api/v1/companies/{entity_id}/ai-insights` | AI opportunity (Intelligence+) |

#### Home & Feed

| Method | Path | Description |
|--------|------|-------------|
| GET | `/api/v1/feed` | Personalized signal feed |
| PUT | `/api/v1/feed/preferences` | Edit feed config |
| GET | `/api/v1/feed/widgets/intent-summary` | Intent widget |
| GET | `/api/v1/feed/widgets/saved-searches` | Saved searches widget |
| GET | `/api/v1/feed/widgets/recently-viewed` | Recently viewed |
| GET | `/api/v1/feed/widgets/summary-counts` | Five count boxes |

#### Intent

| Method | Path | Description |
|--------|------|-------------|
| GET | `/api/v1/intent/signals` | Intent table |
| GET | `/api/v1/intent/topics/mine` | User's topics (≤6) |
| GET | `/api/v1/intent/topics/library` | All topics |
| POST | `/api/v1/intent/topics/assign` | Assign topics |
| DELETE | `/api/v1/intent/topics/{topic_id}` | Remove topic |

#### Saved & Alerts

| Method | Path | Description |
|--------|------|-------------|
| GET/POST | `/api/v1/saved-searches` | CRUD |
| PUT | `/api/v1/saved-searches/{id}` | Update/replace |
| POST | `/api/v1/signals/{id}/bookmark` | Save signal |
| GET | `/api/v1/alerts` | In-app alerts |
| GET | `/api/v1/alerts/email-log` | Email alert log |
| PATCH | `/api/v1/alerts/{id}/read` | Mark read |

#### Workflows

| Method | Path | Description |
|--------|------|-------------|
| GET/POST | `/api/v1/workflows` | CRUD |
| PATCH | `/api/v1/workflows/{id}/status` | Activate/deactivate |

#### Connectors

| Method | Path | Description |
|--------|------|-------------|
| GET | `/api/v1/connectors` | List connectors |
| POST | `/api/v1/connectors/{provider}/connect` | Initiate OAuth |
| GET | `/api/v1/connectors/{provider}/callback` | OAuth callback |
| DELETE | `/api/v1/connectors/{id}` | Disconnect |
| POST | `/api/v1/connectors/{id}/export` | Manual export |

#### AI

| Method | Path | Description |
|--------|------|-------------|
| POST | `/api/v1/ai/opportunity-score` | Generate/regenerate score |
| POST | `/api/v1/ai/outreach/email` | Generate email |
| POST | `/api/v1/ai/outreach/linkedin` | Generate LinkedIn message |
| POST | `/api/v1/ai/outreach/call-script` | Generate call script |

#### Billing

| Method | Path | Description |
|--------|------|-------------|
| GET | `/api/v1/billing/plan` | Current plan + usage |
| POST | `/api/v1/billing/checkout` | Stripe checkout session |
| POST | `/api/v1/billing/portal` | Stripe customer portal |
| POST | `/api/v1/billing/webhook` | Stripe webhook (no auth; signature verified) |

#### Admin

| Method | Path | Description |
|--------|------|-------------|
| GET/POST/DELETE | `/api/v1/admin/users` | Team management |

### 5.3 Entitlement Middleware

Every metered endpoint passes through `EntitlementMiddleware`:

```php
// Pseudocode
function check(string $action): void {
    $workspace = currentWorkspace();
    $plan = $workspace->plan_tier;
    $usage = UsageService::getMonthlyCount($workspace, $action);
    $limit = PlanLimits::get($plan, $action);
    if ($limit !== null && $usage >= $limit) {
        throw new PaymentRequiredException('Limit reached. Upgrade your plan.');
    }
    UsageService::increment($workspace, $action);
}
```

---

## 6. Search & Indexing

### 6.1 Query Flow

1. Client sends filter JSON to CodeIgniter API
2. API validates entitlements + sanitizes filters
3. API builds OpenSearch DSL query
4. OpenSearch returns hits + aggregations (facets)
5. API hydrates additional fields from PostgreSQL if needed
6. Response returned with pagination meta

### 6.2 Filter → Query Mapping

| UI Filter | OpenSearch Clause |
|-----------|-------------------|
| Industry | `term: { industry }` |
| Employee count | `range: { employee_count }` |
| Revenue | `term: { revenue_range }` |
| Media spend | `term: { media_spend_tier }` |
| Technology | `terms: { technologies }` |
| Intent topic | `term: { intent_topic_ids }` |
| Keyword q | `multi_match` on name, description, domain |

### 6.3 Performance Targets

| Operation | Target |
|-----------|--------|
| Simple keyword search | P95 < 500ms |
| Multi-filter search | P95 < 2000ms |
| Index refresh lag | ≤ 15 min (event-driven) or ≤ 24h (nightly batch for v1) |

---

## 7. Data Pipeline Specification

### 7.1 Pipeline Stages

```
┌─────────────┐   ┌──────────────┐   ┌─────────────────┐   ┌────────────┐
│  Ingestion  │──▶│  Normalization│──▶│ Entity Resolution│──▶│ Enrichment │
└─────────────┘   └──────────────┘   └─────────────────┘   └────────────┘
                                                                    │
                    ┌──────────────┐   ┌─────────────┐              │
                    │ OpenSearch   │◀──│  PostgreSQL │◀─────────────┘
                    │   Indexer    │   │   Loader    │
                    └──────────────┘   └─────────────┘
```

### 7.2 Ingestion Sources (Phase 2+)

| Source | Method | Entity Types | Status |
|--------|--------|--------------|--------|
| **Web scraper (existing)** | Scheduled crawler → staging DB | Companies, contacts, scoops | ✅ Primary — already in use |
| **CSV upload** | Admin UI → S3 → validation job | Companies, contacts, brands, agencies | ✅ Phase 2 |
| **Excel upload** | Admin UI → S3 → validation job (`.xlsx`) | Companies, contacts, brands, agencies | ✅ Phase 2 |
| Licensed vendor API (Apollo/Clearbit) | REST batch | Companies, contacts | Optional |
| Press release / news RSS | Crawler | Scoops | ✅ |
| Crunchbase / SEC filings API | REST | Funding scoops | ✅ |
| Career page feeds | Crawler | Hiring scoops | ⚠️ Fragile |
| Meta/Google Ad Libraries | API/scrape | Media spend estimates | ⚠️ Workaround — estimates only |
| Bombora intent feed | SFTP/API | Intent signals | ⚠️ Requires license |
| Public business registries | Batch | Firmographics | ✅ US-first |

**Unified pipeline rule:** Scraper output, uploaded files, and vendor batches all land in **raw/staging**, then share the same normalize → resolve → enrich → load path. Never write directly to production tables without entity resolution.

### 7.2.1 Web Scraper Integration (Existing)

The team already operates a scraper that extracts data from public sources and dumps records into the database. AOLL integrates it as follows:

```
Scraper (cron / worker)
  → staging table `raw_scraper_records` OR S3 prefix `raw/scraper/{run_id}/`
  → pipeline worker picks up new run_id
  → normalize → entity resolution → companies / contacts / scoops
  → mark run complete in `scraper_runs`
```

| Field | Spec |
|-------|------|
| Trigger | Cron schedule (e.g. daily) or manual re-run |
| Idempotency | Each run has `scraper_run_id`; re-processing same run is safe (upsert) |
| Monitoring | Log rows ingested, errors, duration; alert on failure |
| Source tag | All records get `data_source = 'scraper'` |

### 7.2.2 CSV / Excel Upload (New)

Admin-only bulk import for enriching or bootstrapping the database.

**Flow:**

```
Admin uploads file (UI)
  → POST /api/v1/admin/imports
  → File stored in S3: imports/{workspace_id}/{import_id}/original.{csv|xlsx}
  → import_jobs row created (status: pending)
  → Worker: parse → validate rows → map columns → entity resolution → upsert
  → import_jobs updated (status: completed | failed | partial)
  → Error rows written to imports/.../errors.csv
  → OpenSearch index sync triggered
  → Admin notified (in-app + optional email)
```

**File limits (v1):**

| Constraint | Value |
|------------|-------|
| Max file size | 50 MB |
| Max rows per file | 100,000 |
| Allowed types | `.csv`, `.xlsx`, `.xls` |
| Encoding | UTF-8 (CSV); BOM tolerated |

**Validation rules:**

| Rule | Action |
|------|--------|
| Missing required column | Reject entire file with clear message |
| Invalid email format | Reject row; include in error report |
| Duplicate domain in same file | Process last row wins; warn in summary |
| Domain matches existing entity | Merge/update via entity resolution |
| Unknown industry value | Accept; store as-is or map via lookup table |

### 7.2.3 Import Job Schema

#### `import_jobs`

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| workspace_id | UUID FK | Nullable for global admin imports |
| uploaded_by | INT FK | User ID |
| filename | VARCHAR(500) | Original filename |
| file_type | ENUM | csv, xlsx, xls |
| entity_type | ENUM | company, contact, brand, agency |
| s3_path | VARCHAR(1000) | Original file location |
| status | ENUM | pending, processing, completed, failed, partial |
| total_rows | INT | |
| accepted_rows | INT | |
| rejected_rows | INT | |
| merged_rows | INT | Updated existing entities |
| error_report_s3_path | VARCHAR(1000) | Nullable |
| column_mapping | JSONB | Header → field map used |
| started_at | TIMESTAMPTZ | |
| completed_at | TIMESTAMPTZ | |
| created_at | TIMESTAMPTZ | |

#### `scraper_runs`

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| scraper_name | VARCHAR(100) | e.g. `company_crawler`, `news_scoops` |
| status | ENUM | running, completed, failed |
| records_fetched | INT | |
| records_accepted | INT | |
| records_rejected | INT | |
| error_log | TEXT | Nullable |
| started_at | TIMESTAMPTZ | |
| completed_at | TIMESTAMPTZ | |

**Extend `companies` and `contacts` tables:**

| Column | Type | Notes |
|--------|------|-------|
| data_source | ENUM | scraper, csv_upload, excel_upload, vendor, manual |
| source_ref | VARCHAR(255) | `import_job_id` or `scraper_run_id` |
| source_file | VARCHAR(500) | Original filename (uploads only) |

### 7.2.4 Admin Import API

| Method | Path | Description |
|--------|------|-------------|
| GET | `/api/v1/admin/imports/templates/{entity_type}` | Download CSV template |
| POST | `/api/v1/admin/imports` | Upload file (multipart); returns `import_job_id` |
| GET | `/api/v1/admin/imports` | List import history |
| GET | `/api/v1/admin/imports/{id}` | Job status + summary |
| GET | `/api/v1/admin/imports/{id}/errors` | Download error report CSV |
| GET | `/api/v1/admin/scraper-runs` | List scraper run history |
| POST | `/api/v1/admin/scraper-runs/trigger` | Manual scraper trigger (optional) |

### 7.3 Entity Resolution Algorithm (v1)

**Input:** `{ domain?, company_name?, email?, linkedin_url? }`

**Steps:**

1. **Normalize:** Lowercase domain; strip `www.`; punycode; canonicalize company name (remove Inc, LLC)
2. **Tier 1 — Deterministic:** Exact domain match → existing `entity_id`
3. **Tier 2 — Fuzzy:** Levenshtein + trigram on name within same country; threshold ≥ 0.92
4. **Tier 3 — LLM adjudication:** For scores 0.85–0.92, LLM yes/no merge (batch only, cost-capped)
5. **No match:** Create new `entity_id` (UUID v4)

**Output:** `{ entity_id, confidence, match_method }`

### 7.4 Scoop Ingestion

```python
# Pseudocode pipeline job (daily)
for article in news_fetcher.fetch(since=yesterday):
    entities = ner.extract_companies(article.text)
    for entity_ref in entities:
        company_id = resolver.resolve(entity_ref)
        if company_id:
            scoop = classify_scoop(article)  # LLM or rules
            db.upsert_scoop(company_id, scoop, confidence=score)
```

### 7.5 Intent Signal Generation

**Option A — Licensed (preferred when budget allows):**
- Ingest Bombora company-topic scores daily
- Map to `intent_signals` with `source=bombora`

**Option B — Proxy Composite (v1 workaround):**

```
proxy_score = weighted_sum(
    recent_scoop_count * 0.25,
    hiring_signal * 0.20,
    funding_recency * 0.20,
    tech_stack_change * 0.15,
    ad_spend_tier_increase * 0.20
)
```

Normalize to 0–100; store with `source=proxy_composite`. **UI must label:** "Modeled intent based on activity signals."

### 7.6 Media Spend Estimation (Workaround)

**Not feasible v1:** Winmo-grade verified $100B+ ad spend database.

**Workaround pipeline:**

1. Query Meta Ad Library / Google Ads Transparency for brand domain
2. Count active ads, estimated impressions tier (if available)
3. Apply industry CPM benchmarks → spend **range estimate**
4. Store `media_spend_tier` + `media_spend_source=public_ad_library`
5. Display in UI: **"Estimated media spend"** with info tooltip

---

## 8. Module Implementation Specifications

### 8.1 Authentication (AA-38)

| Item | Spec |
|------|------|
| Password hashing | bcrypt, cost 12 (migrate from msacommon if needed) |
| Session | Redis-backed; 24h default; 30d with Remember me |
| Google OAuth | Existing; add `google_id` linking |
| Microsoft OAuth | Azure AD v2; scopes: `openid email profile` |
| Work email blocklist | Configurable JSON list of free domains |
| Reset token | 64-char random; SHA-256 stored; 30 min expiry; single use |

### 8.2 Home Feed (AA-39)

**Feed ranking algorithm (v1):**

```
score = (
    signal_recency_weight * recency_decay(days_since_signal) +
    icp_match_weight * icp_match(user_preferences, company) +
    intent_score_weight * normalized_intent_score +
    scoop_confidence_weight * scoop.confidence
)
ORDER BY score DESC LIMIT 50
```

**ICP match:** Boolean overlap on industry, location, company size, media spend tier from `feed_preferences`.

### 8.3 Advanced Search (AA-41)

- Tab switch clears `selected_ids` session state
- Filter state serialized to JSON for save search
- Export: async job → S3 presigned URL → email download link (large sets)

### 8.4 Saved Searches & Alerts (AA-44, AA-45)

**Alert diff job (daily cron):**

```sql
-- Pseudocode: new companies matching saved search since last_run_at
SELECT entity_id FROM search_index WHERE filters(saved_search.filters)
  AND created_at > saved_search.last_run_at
```

For each new match → insert `alerts` row → queue email if enabled.

**Email digest:** Render AA-60 template; attach CSV of all new signals.

### 8.5 Workflows (AA-46)

**Scheduler:** Redis sorted set keyed by `next_run_at`; worker polls every minute.

**Execution flow:**

1. Load workflow; verify `status=active`
2. Resolve trigger → list of company entity IDs
3. For each action: execute sequentially
4. Log run result; update `last_run_at`, compute `next_run_at`
5. On CRM failure → set connector `status=error`; queue admin email

**Discover Contacts action:**

```
SELECT * FROM contacts
WHERE company_entity_id IN (...)
  AND seniority IN (action.config.roles)
  AND (location filter)
LIMIT action.config.max_per_company
```

### 8.6 Connectors (AA-42)

#### HubSpot

| AOLL Field | HubSpot Property |
|------------|------------------|
| company.name | `name` |
| company.domain | `domain` |
| company.industry | `industry` |
| contact.email | `email` |
| contact.job_title | `jobtitle` |

- OAuth scopes: `crm.objects.contacts.write`, `crm.objects.companies.write`
- Rate limit: 100 requests/10 seconds — implement token bucket

#### Salesforce

| AOLL Field | Salesforce Field |
|------------|------------------|
| company.name | `Account.Name` |
| company.domain | `Account.Website` |
| contact.email | `Lead.Email` or `Contact.Email` |

- OAuth: Connected App with refresh token
- Upsert by email (contacts) and domain (accounts)

**Not in v1:** Bi-directional sync, duplicate merge in CRM.

---

## 9. Integration Specifications

### 9.1 Stripe

| Event | Handler |
|-------|---------|
| `checkout.session.completed` | Activate subscription; set plan_tier |
| `customer.subscription.updated` | Sync tier changes |
| `customer.subscription.deleted` | Downgrade to locked/free |
| `invoice.payment_failed` | Set grace period; send email; lock after 7 days |

**Products:** Create Stripe Products/Prices matching four tiers (monthly + annual).

### 9.2 Email (Transactional)

| Template | Trigger | Reference |
|----------|---------|-----------|
| Welcome | Signup | AA-60 |
| Forgot password | Reset request | AA-60 |
| Alert + CSV | Alert job | AA-60 |
| Plan upgrade | Stripe webhook | AA-60 |
| Trial ending | Cron (3d, 1d, 0d) | AA-60 |
| CRM sync failed | Connector error | AA-60 |

Use queue (Redis) for async send; retry 3x with exponential backoff.

### 9.3 Enterprise REST API (Phase 5)

- API keys per workspace (Enterprise tier)
- Rate limit: 1000 req/hour default
- Endpoints mirror `/api/v1/search/*` and `/api/v1/companies/*`
- OpenAPI 3.0 spec published

---

## 10. AI Services Specification

### 10.1 Architecture

```
Company Context Builder
  ├── Firmographics from DB
  ├── Recent scoops (last 90 days)
  ├── Intent signals
  └── Contact role (if outreach)
         │
         ▼
   Prompt Template (versioned)
         │
         ▼
   LLM Provider (GPT-4o / Claude)
         │
         ▼
   Response Validator (length, no fabricated facts)
         │
         ▼
   Store + return to client
```

### 10.2 Opportunity Score (v1 — Rule-Based)

```python
score = min(100, int(
    intent_component * 0.30 +
    scoop_recency_component * 0.25 +
    funding_component * 0.15 +
    hiring_component * 0.15 +
    media_spend_component * 0.15
))
```

Store breakdown in JSON for transparency. **v2:** Train classifier on conversion labels.

### 10.3 Outreach Generation

| Parameter | Value |
|-----------|-------|
| Max tokens | 800 |
| Temperature | 0.7 |
| System prompt | "You are a B2B sales assistant. Only use provided facts. Do not invent metrics." |
| Grounding | RAG context block ≤ 4000 tokens |
| Output | `{ subject, body }` for email; plain text for LinkedIn/scripts |

**Guardrails:**
- Reject if company context empty
- Strip email addresses not in source data from output
- Log prompt hash + model version for audit

### 10.4 Cost Controls

| Tier | AI generations/month |
|------|---------------------|
| Intelligence | 500 |
| Enterprise | Unlimited (fair use cap 5000) |

---

## 11. Billing & Entitlements

### 11.1 Plan Limits (Draft — Pending Product Sign-off)

| Limit | Starter | Professional | Intelligence | Enterprise |
|-------|---------|--------------|--------------|------------|
| Searches/month | 100 | Unlimited | Unlimited | Unlimited |
| Contact reveals/month | 50 | 500 | Unlimited | Unlimited |
| Exports/month | 25 | 250 | 1000 | Custom |
| Saved searches | 3 | 25 | Unlimited | Unlimited |
| Topics tracked | 0 | 6 | 6 | 12 |
| Workflows | 0 | 0 | 10 | Unlimited |
| CRM connections | 0 | 0 | 2 | Unlimited |
| Seats | 1 | 3 | 5 | Custom |

### 11.2 Feature Flags by Tier

Implement via `FeatureGate` service checking `workspace.plan_tier` against config map (see PRD §9.1).

---

## 12. Security Specification

| Area | Requirement |
|------|-------------|
| Transport | TLS 1.2+ everywhere |
| Secrets | AWS Secrets Manager or vault; never in repo |
| CRM tokens | AES-256-GCM encrypted at rest |
| Passwords | bcrypt |
| CSRF | CodeIgniter CSRF tokens on all forms |
| SQL injection | Parameterized queries / ORM only |
| XSS | Output escaping in views |
| Rate limiting | 100 req/min per user on search; 10 req/min on AI |
| Audit log | Admin actions → `audit_logs` table |
| Data retention | User deletion request → anonymize within 30 days (GDPR-ready) |
| PII | Contact emails are PII; access logged in `usage_events` |

---

## 13. Observability & Operations

| Signal | Tool |
|--------|------|
| APM | Datadog tracing on API endpoints |
| Logs | Structured JSON; correlation ID per request |
| Metrics | Search latency, export job duration, workflow success rate |
| Alerts | P95 search > 3s; workflow failure rate > 5%; CRM token refresh failures |
| Uptime | Pingdom on `/health` |

**Health endpoint:** `GET /health` → `{ "status": "ok", "db": "ok", "search": "ok", "redis": "ok" }`

---

## 14. Testing Strategy

| Layer | Approach |
|-------|----------|
| Unit | PHPUnit for services; pytest for pipeline |
| Integration | API tests against test DB + OpenSearch |
| E2E | Playwright for critical flows: search → export, save search → alert |
| Load | k6: 100 concurrent searches, P95 < 2s |
| Data quality | Weekly report: duplicate entity rate, email bounce sample |
| CRM | Sandbox HubSpot/Salesforce orgs for integration tests |

---

## 15. Deployment & Environments

| Environment | Purpose | Data |
|-------------|---------|------|
| `dev` | Developer local | Seed data (1K companies) |
| `staging` | QA + demos | Anonymized subset (10K) |
| `production` | Live | Full dataset |

**CI/CD:** Bitbucket Pipelines (existing) → build → test → deploy to staging → manual promote to prod.

**Database migrations:** Flyway or Phinx; versioned SQL files.

---

## 16. Feasibility, Limitations & Workarounds

| Requirement | Feasible? | Implementation | Workaround |
|-------------|-----------|----------------|------------|
| 100M company database | ❌ No (v1) | License + vertical focus | 100K–500K quality records |
| Verified media spend like Winmo | ❌ No (v1) | Public ad library signals | Estimated tiers; clear labeling |
| Bombora-grade intent | ⚠️ Partial | License or proxy | Proxy composite + daily refresh |
| Real-time alerts | ⚠️ Partial | Polling diff | Daily/weekly batch |
| Real-time workflow triggers | ⚠️ Partial | Scheduled runs | Event triggers in v2 |
| Org charts | ❌ No (v1) | — | Defer; show flat contact list |
| WebSights | ❌ No (v1) | — | Out of scope |
| Bi-directional CRM | ❌ No (v1) | One-way push | Manual CRM hygiene |
| 95% email accuracy | ❌ Unlikely | Verification sampling | Status badges; honest SLAs |
| LinkedIn scraping | ❌ No | — | Licensed contact data only |
| 6 intent topics/user | ✅ Yes | DB constraint | Enterprise upsell for more |
| AI outreach quality | ✅ Yes | LLM + RAG | Human review disclaimer |
| Self-serve Stripe billing | ✅ Yes | Stripe Billing | — |
| HubSpot/SF export | ✅ Yes | OAuth + REST | — |

---

## 17. Assumptions

| ID | Assumption |
|----|------------|
| T1 | PostgreSQL introduced as product DB; legacy MySQL tables migrated or synced by Phase 3 |
| T2 | OpenSearch cluster provisioned in same cloud region as app (≤10ms network) |
| T3 | Data vendor delivers company/contact data via REST API with bulk export |
| T4 | Pipeline workers run on schedule (Airflow or cron), not streaming Kafka (v1) |
| T5 | Single-region deployment (US-East) acceptable for v1 |
| T6 | CodeIgniter handles ≤500 req/s with horizontal scaling behind load balancer |
| T7 | Export jobs >10K rows run async; user receives email with download link |
| T8 | LLM prompts stored as versioned templates in repo (`resources/ai/prompts/`) |
| T9 | Stripe webhooks idempotent via `stripe_event_id` dedup table |
| T10 | CRM OAuth tokens refreshed by background job 24h before expiry |

---

## Document History

| Version | Date | Changes |
|---------|------|---------|
| 1.0 | 2026-07-27 | Initial technical specification |

---

*For system diagrams and service topology, see ARCHITECTURE-AOLL.md. For product requirements and acceptance criteria, see PRD-AOLL.md.*