← back to Rentv Adintel
auto-data-snapshot: 2026-08-07T16:56:57 (8 data files) — README.md docs/ADVERTISER_RESEARCH_METHOD.md docs/ADVERTISER_SOURCE_REGISTER.md docs/ANALYTICS_CONNECTORS.md docs/DEPLOYMENT_ADVERTISER_VIEWER.md
806e0b070fe5cd1017568d7a4e44f01220040036 · 2026-08-07 16:57:39 -0700 · auto-commit-fleet
Files touched
A README.mdA docs/ADVERTISER_RESEARCH_METHOD.mdA docs/ADVERTISER_SOURCE_REGISTER.mdA docs/ANALYTICS_CONNECTORS.mdA docs/DEPLOYMENT_ADVERTISER_VIEWER.mdA docs/EXPORT_FORMAT.mdA docs/SECURITY_ADVERTISER_VIEWER.mdM fixtures/sample-gsc-export.csv
Diff
commit 806e0b070fe5cd1017568d7a4e44f01220040036
Author: auto-commit-fleet <steve@designerwallcoverings.com>
Date: Fri Aug 7 16:57:39 2026 -0700
auto-data-snapshot: 2026-08-07T16:56:57 (8 data files) — README.md docs/ADVERTISER_RESEARCH_METHOD.md docs/ADVERTISER_SOURCE_REGISTER.md docs/ANALYTICS_CONNECTORS.md docs/DEPLOYMENT_ADVERTISER_VIEWER.md
---
README.md | 94 +++++++++++++++++++++++++++++++
docs/ADVERTISER_RESEARCH_METHOD.md | 105 +++++++++++++++++++++++++++++++++++
docs/ADVERTISER_SOURCE_REGISTER.md | 76 +++++++++++++++++++++++++
docs/ANALYTICS_CONNECTORS.md | 99 +++++++++++++++++++++++++++++++++
docs/DEPLOYMENT_ADVERTISER_VIEWER.md | 72 ++++++++++++++++++++++++
docs/EXPORT_FORMAT.md | 74 ++++++++++++++++++++++++
docs/SECURITY_ADVERTISER_VIEWER.md | 74 ++++++++++++++++++++++++
fixtures/sample-gsc-export.csv | 46 +++++++--------
8 files changed, 617 insertions(+), 23 deletions(-)
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..c605215
--- /dev/null
+++ b/README.md
@@ -0,0 +1,94 @@
+# RENTV Advertiser Intelligence Viewer
+
+One easy web viewer that identifies and documents the **real companies advertising,
+sponsoring, exhibiting, and speaking** in commercial-real-estate media — so Steve Bloom
+and other non-technical RENTV users can search, understand, and download the intelligence
+without training. **California first, Arizona second**, then the western U.S.
+
+The tool distinguishes **facts from guesses**: it never labels a company an advertiser
+just because it operates in CRE, sat on a panel, or was mentioned in an article. Every
+displayed field is traceable to evidence, and a one-click **Download Everything** export
+produces CSV, XLSX, JSON, a ZIP, and a self-contained offline HTML viewer.
+
+Stack: **Express + PostgreSQL + vanilla front-end** (the house convention), Node 20+,
+served on `http://localhost:9814`.
+
+## Quick start
+
+```bash
+createdb rentv_advertisers # PostgreSQL 16+ must be running
+npm install
+npm run db:migrate # create the tables
+npm run db:seed # load RENTV seed data
+npm start # -> http://localhost:9814
+```
+
+Open **http://localhost:9814** and go to `/advertisers`.
+Run the tests with `npm test` (`node --test test/*.test.js`).
+
+## Setup wizard — plain-English steps (§35)
+
+The first-run wizard walks a non-technical user through setup. Each step reports
+**Success**, **Needs Attention**, or **Skipped** — never a technical stack trace.
+
+1. **Choose Existing RENTV App or Standalone.** This build defaults to Standalone,
+ exposed at `/advertisers`.
+2. **Test the database.** Confirms Postgres is reachable (`createdb rentv_advertisers`
+ done, connection OK).
+3. **Load RENTV seed data.** `npm run db:seed` — the 5 verified advertisers, the CoStar
+ content-partner, panelist-only participants, dated rate + audience snapshots.
+4. **Upload the two flyer images** if not auto-detected (Property Spotlight Flyer REV
+ April 2025 + Corp Flyer Apr 2026 V3). If absent, an admin upload card appears and the
+ build continues with placeholder records.
+5. **Connect GA4 or choose CSV import.** Add a Google service-account JSON to go live, or
+ import a GA4 CSV export. Runs on DEMO fixtures until connected.
+6. **Connect Search Console or choose CSV import.** Same pattern for organic search data.
+7. **Choose a search provider or Manual Search.** Default is Manual (one-click search
+ links); a permitted search API key can be added later.
+8. **Choose California markets** (Greater LA → OC → Inland Empire → San Diego → Ventura →
+ Bay Area → Sacramento → Central Valley → Statewide).
+9. **Choose Arizona markets** (Phoenix metro → Scottsdale → Tempe → Mesa → Chandler →
+ Gilbert → Glendale → Tucson → Statewide).
+10. **Run a safe demo research job** — uses fixtures and manual-review links only; no
+ automated public-web fetching (that stays off by default).
+11. **Open the advertiser table** at `/advertisers` and search a verified advertiser.
+12. **Download a sample export** — the Download-Everything ZIP with the offline
+ `executive-viewer.html`.
+
+## Where exports land
+
+The **Download Everything** button generates a ZIP named
+`RENTV-Advertiser-Intelligence-YYYY-MM-DD.zip` containing the CSV/XLSX/JSON datasets, a
+`thumbnails/` and export-allowed `evidence/` folder, `source-manifest.json`,
+`methodology.json`, `export-audit.json`, and a self-contained `executive-viewer.html` you
+can double-click to open with no server. Full manifest + rights filtering:
+[`docs/EXPORT_FORMAT.md`](docs/EXPORT_FORMAT.md).
+
+## How to add credentials
+
+Copy `.env.example` to `.env`. The app runs on DEMO fixtures with nothing configured.
+
+- **GA4 / Search Console (DEMO → LIVE):** set `GOOGLE_SERVICE_ACCOUNT_JSON_BASE64`,
+ `GA4_PROPERTY_ID`, `GSC_SITE_URL`, then `npm run analytics:ga4` and
+ `npm run analytics:gsc`. Details in
+ [`docs/ANALYTICS_CONNECTORS.md`](docs/ANALYTICS_CONNECTORS.md).
+- **Optional Google Ads:** `GOOGLE_ADS_ENABLED="true"` + the six `GOOGLE_ADS_*` values.
+- **Optional Gmail import:** `GMAIL_IMPORT_ENABLED="true"` + the `GMAIL_*` values
+ (admin-only, least-privilege OAuth).
+- **Search provider:** set `SEARCH_PROVIDER` and the matching key; default is `manual`.
+
+Secrets are server-only and redacted from logs. A **live/production deploy is
+Steve-gated** — local run is unrestricted.
+
+## Documentation
+
+| Doc | What it covers |
+|-----|----------------|
+| [`docs/ADVERTISER_INTELLIGENCE_BUILD_STATUS.md`](docs/ADVERTISER_INTELLIGENCE_BUILD_STATUS.md) | Honest built-vs-blocked status; §36 criteria matrix |
+| [`docs/ADVERTISER_DATA_POLICY.md`](docs/ADVERTISER_DATA_POLICY.md) | Privacy/source rules as operational policy |
+| [`docs/ADVERTISER_SOURCE_REGISTER.md`](docs/ADVERTISER_SOURCE_REGISTER.md) | Seeded source policies + the review-before-automation rule |
+| [`docs/ADVERTISER_RESEARCH_METHOD.md`](docs/ADVERTISER_RESEARCH_METHOD.md) | Classification, entity resolution, LinkedIn/contact workflow |
+| [`docs/ANALYTICS_CONNECTORS.md`](docs/ANALYTICS_CONNECTORS.md) | GA4 / GSC / Google Ads / CSV; DEMO → LIVE; formulas |
+| [`docs/EXPORT_FORMAT.md`](docs/EXPORT_FORMAT.md) | ZIP manifest, offline viewer, rights filtering |
+| [`docs/DEPLOYMENT_ADVERTISER_VIEWER.md`](docs/DEPLOYMENT_ADVERTISER_VIEWER.md) | Local run, env vars, Steve-gated deploy |
+| [`docs/SECURITY_ADVERTISER_VIEWER.md`](docs/SECURITY_ADVERTISER_VIEWER.md) | CSP, SSRF guard, LinkedIn block, EXIF, audit, secrets |
diff --git a/docs/ADVERTISER_RESEARCH_METHOD.md b/docs/ADVERTISER_RESEARCH_METHOD.md
new file mode 100644
index 0000000..1125dc8
--- /dev/null
+++ b/docs/ADVERTISER_RESEARCH_METHOD.md
@@ -0,0 +1,105 @@
+# RENTV Advertiser Intelligence — Research Method
+
+How the tool classifies relationships, resolves entities, and researches LinkedIn /
+public contacts. The rule throughout: **distinguish facts from guesses, and never label
+a company an advertiser merely because it operates in CRE, appeared on a panel, or was
+mentioned in an article.**
+
+## 1. Classification system (§2)
+
+Every company ↔ event/publication relationship carries exactly one status:
+
+| Status | Meaning |
+|--------|---------|
+| `VERIFIED_ADVERTISER` | A dated public ad, eblast, sponsored placement, paid spotlight, paid video, advertorial, or direct internal RENTV record supports the claim. |
+| `VERIFIED_CONFERENCE_SPONSOR` | An event page, sponsor grid, agenda, program, email, PDF, or official record explicitly calls the company a sponsor. |
+| `VERIFIED_EXHIBITOR` | An official exhibitor directory or floor plan supports the claim. |
+| `VERIFIED_MEDIA_PARTNER` | The event or publisher explicitly says "media partner." |
+| `VERIFIED_CONTENT_PARTNER` | Supplies a report / research presentation / program content, but payment is not proven (e.g. CoStar Group). |
+| `SPEAKER_OR_PANELIST_ONLY` | Participation is verified, but sponsorship/advertising is not. |
+| `PAST_ADVERTISER` | Verified historical advertising with no recent sighting inside the recency window. |
+| `LIKELY_PROSPECT` | Not proven as an advertiser, but supported by transparent fit factors. |
+| `RESEARCH_NEEDED` | Evidence is incomplete or conflicting. |
+| `DISQUALIFIED` | Duplicate, irrelevant, closed, noncommercial, suppressed, or prohibited source. |
+
+The plain-English label for each status lives in `lib/types.js` (`STATUS_LABELS`) and is
+shown on every record alongside its supporting evidence. The `VERIFIED_*` set drives the
+Simple-View "Verified Only" switch.
+
+### Panelist ≠ sponsor (hard rule, §6.16)
+
+A `SPEAKER_OR_PANELIST_ONLY` record may not be promoted to a verified
+sponsor/advertiser status without **separate sponsor evidence**. The classification
+guard (`assertNotPanelistMislabeledAsSponsor(fromStatus, toStatus, hasSponsorEvidence)`)
+throws on any such promotion when `hasSponsorEvidence` is false, and
+`test/classification.test.js` locks it in. This is why the CoStar seed is a
+content-partner and why the conference participants (CBRE, Colliers, Cushman & Wakefield,
+Rexford Industrial, …) are seeded as `SPEAKER_OR_PANELIST_ONLY` / `LIKELY_PROSPECT` — not
+sponsors.
+
+## 2. Deterministic entity resolution (§12)
+
+Match deterministically first, then generate candidates, then apply thresholds.
+
+**Deterministic keys (tried first):**
+
+- exact normalized domain,
+- exact public business email domain,
+- exact normalized phone + organization name,
+- exact official identifier,
+- exact normalized name + city/state,
+- exact LinkedIn company URL from a permitted discovery source.
+
+Names are normalized via `normalizeName()` in `lib/types.js` (lowercases, strips
+articles and corporate suffixes like Inc./LLC/Group/Corp, collapses punctuation) so
+"The Rockefeller Group, Inc." and "Rockefeller Group" resolve to the same key
+`rockefeller`.
+
+**Candidate generation:** normalized names, aliases, domains, addresses, phones,
+geography, and event overlap.
+
+**Thresholds:**
+
+| Similarity | Action |
+|------------|--------|
+| `>= 0.97` | Auto-link **only** when no contradictory identifier exists. |
+| `0.82 – 0.9699` | Human review (side-by-side merge screen). |
+| `< 0.82` | Do not merge. |
+
+Every merge is reversible with evidence, conflicts, and undo (`merge_candidates` /
+`merge_audit`).
+
+## 3. LinkedIn & public-contact research workflow (§14)
+
+**Search-URL-only. Human-open. Never fetch.**
+
+For each organization the tool generates permitted search-provider queries (or one-click
+manual search URLs when no search API key is configured), e.g.:
+
+```text
+"{company}" commercial real estate California marketing
+"{company}" Arizona commercial real estate sponsor
+"{company}" media contact
+site:{domain} newsroom "media contact"
+site:linkedin.com/company "{company}"
+site:linkedin.com/in (marketing OR communications OR partnerships OR "business development") "{company}" California
+```
+
+The `site:linkedin.com/...` queries produce a **search URL a human opens in a normal
+browser** — the app never fetches LinkedIn (blocked by `fetch-guard`). From a result we
+store only the URL, title/snippet (licensing permitting), the discovery query, the
+discovery date, and a manual-review status.
+
+**Contact role priority** (highest first, `CONTACT_ROLE_PRIORITY` in `lib/types.js`):
+CMO → VP/SVP Marketing → Marketing Director → Communications/PR Director → Events /
+Partnerships / Sponsorships Director → Business Development Director → Regional President
+/ Market Leader → Managing Director / Principal → Public media contact → General company
+contact.
+
+**Contact verification** — for every phone/email we show the exact source, whether it is
+organization-level or person-level, the observed date, the last verification date, the
+confidence, and a report/correction control. We never infer a missing value.
+
+**Per-record actions** (no auto-send, no auto-dial in the first release): Open Website,
+Open Contact Page, Open Source, Open LinkedIn, Search LinkedIn, Copy Business Email, Copy
+Business Phone, Add Note, Create Task.
diff --git a/docs/ADVERTISER_SOURCE_REGISTER.md b/docs/ADVERTISER_SOURCE_REGISTER.md
new file mode 100644
index 0000000..efcc77b
--- /dev/null
+++ b/docs/ADVERTISER_SOURCE_REGISTER.md
@@ -0,0 +1,76 @@
+# RENTV Advertiser Intelligence — Source Register
+
+The catalog of research sources and their policies. **A source must have a reviewed
+policy before automation is enabled** — this is the governing rule, enforced by
+`lib/compliance/source-policy.js` (`validateSourcePolicy` / `assertSourceEnabledLegal`)
+which is called before every run.
+
+## The governing rule
+
+1. Each source is described by a `source_policies` record (the `SourcePolicy` shape in
+ §10 — `sourceKey`, `displayName`, `owner`, `baseUrl`, `accessMethod`,
+ `allowsAutomatedAccess`, rate limits, `reviewedAt`, `reviewNotes`, `enabled`, …).
+2. A policy is validated on save and again **before every run**. A policy that names a
+ prohibited proprietary DB (CoStar, LoopNet, ZoomInfo, Apollo, MLS) or a LinkedIn host
+ as its base and sets `allowsAutomatedAccess: true` is **rejected** — the pipeline
+ refuses to run it.
+3. `enabled` + `allowsAutomatedAccess` together are what turn a reviewed policy into an
+ automated one. Until a human reviews the source and sets those, the source is
+ available only as a **one-click manual-review / manual-upload link** — no automation.
+4. Access methods (§10): `official_api`, `official_bulk_download`, `rss_or_sitemap`,
+ `first_party_public_web`, `authorized_mailbox`, `manual_upload`, `manual_review_only`.
+
+## RENTV-owned / authorized sources
+
+These are first-party or authorized and may be enabled for permitted automated or
+mailbox access after review.
+
+| Source key | Display name | Access method | Automated? | Notes |
+|------------|--------------|---------------|-----------|-------|
+| `rentv_com` | RENTV public pages (rentv.com) | `first_party_public_web` | after review | conservative rate limit |
+| `rentvreview_com` | RENTV Review public pages | `first_party_public_web` | after review | |
+| `rentv_conferences` | RENTV conference pages | `first_party_public_web` | after review | sponsor/exhibitor grids |
+| `rentv_cre_talk` | RENTV CRE Talk pages | `first_party_public_web` | after review | |
+| `rentv_newsletters` | RENTV newsletters / browser-view archives | `manual_upload` | no | uploaded `.eml` / HTML |
+| `rentv_constant_contact` | RENTV Constant Contact exports | `manual_upload` | no | CSV campaign reports |
+| `rentv_gmail` | RENTV Gmail (authorized) | `authorized_mailbox` | admin-only, off by default | least-privilege OAuth |
+| `rentv_sales_csv` | RENTV sales records (approved CSV) | `manual_upload` | no | |
+| `rentv_flyers` | Property Spotlight + Corp flyer assets | `manual_upload` | no | `AUTHORIZED_RENTV_ASSET` |
+
+## External sources — manual-review-only until reviewed
+
+Publications, associations, and conference ecosystems (§9) are seeded as
+**`manual_review_only`** or `first_party_public_web` (disabled) records. None permits
+automation until its current official page is validated, robots/terms checked, and a
+policy reviewer flips `enabled` + `allowsAutomatedAccess`. Representative entries:
+
+| Source key | Display name | Access method | Automated? |
+|------------|--------------|---------------|-----------|
+| `labj` | Los Angeles Business Journal | `manual_review_only` | no (validate first) |
+| `ocbj` | Orange County Business Journal | `manual_review_only` | no |
+| `sdbj` | San Diego Business Journal | `manual_review_only` | no |
+| `bisnow` | Bisnow market/event pages | `manual_review_only` | no |
+| `connect_cre` | Connect CRE | `manual_review_only` | no |
+| `naiop_socal` | NAIOP SoCal / IE / SD / NorCal | `first_party_public_web` (disabled) | after review |
+| `uli_la` | ULI Los Angeles / OC-IE / SD / SF / Sac | `first_party_public_web` (disabled) | after review |
+| `boma_gla` | BOMA GLA / OC / SD / SF / Oakland / Sac | `first_party_public_web` (disabled) | after review |
+| `pbj` | Phoenix Business Journal | `manual_review_only` | no |
+| `azre` | AZ Big Media / AZRE | `manual_review_only` | no |
+| `naiop_az` | NAIOP Arizona | `first_party_public_web` (disabled) | after review |
+| `icsc` | ICSC Las Vegas / regional | `first_party_public_web` (disabled) | after review |
+
+## Prohibited-for-automation hosts (hard block)
+
+These may exist only as `manual_review_only` records (store the company, search URL,
+review date, analyst notes) — never automated:
+
+- CoStar, LoopNet, ZoomInfo, Apollo, Crexi, Reonomy (proprietary login-gated DBs)
+- Any MLS system (matched by pattern)
+- LinkedIn (`linkedin.com`, `www.linkedin.com`, `m.linkedin.com`, `lnkd.in`)
+
+## Ad-transparency surfaces — manual-review deep links
+
+Google Ads Transparency Center, Meta Ad Library, and any currently-public LinkedIn ad
+library are represented as **human-review deep links** only. A manual-review record can
+store the company, the search URL, the review date, and analyst notes — but the app does
+not scrape these products.
diff --git a/docs/ANALYTICS_CONNECTORS.md b/docs/ANALYTICS_CONNECTORS.md
new file mode 100644
index 0000000..9d63b58
--- /dev/null
+++ b/docs/ANALYTICS_CONNECTORS.md
@@ -0,0 +1,99 @@
+# RENTV Advertiser Intelligence — Analytics Connectors
+
+How GA4, Search Console, optional Google Ads, and the CSV fallbacks work — and exactly
+how to go from **DEMO** (fixture-backed) to **LIVE**. All Google data is read through the
+**official APIs only**; nothing is scraped.
+
+## Current state: DEMO
+
+With no Google service-account credentials configured, the analytics dashboards run on
+the deterministic fixtures in `fixtures/` (e.g. `ga4-daily.json`, `ga4-landing.json`).
+Everything derived from them is labeled **`DEMO DATA`** in the UI so no one mistakes a
+fixture for a real audience number. This is by design — the app is fully demonstrable
+offline.
+
+## GA4 (§17) — official Google Analytics Data API
+
+Imports (per day, aggregates only — **never** a user-level identifier):
+
+- **Daily overview** — sessions, total users, new users, engaged sessions, engagement
+ rate, average engagement time, views, event count, key events/conversions.
+- **Acquisition** — session default channel group, session source, session medium,
+ session campaign, first-user source/medium.
+- **Content** — landing page + query string, page path, page title, with
+ sessions / users / views / engaged sessions / engagement rate / key events.
+- **Geography** — country, region, city, rolled up into **California and Arizona market
+ views** (§7 priority order).
+
+Stored in `ga4_daily_metrics`, `ga4_landing_page_metrics`, `ga4_acquisition_metrics`,
+`ga4_geo_metrics`.
+
+## Search Console (§18) — official Search Console API
+
+Imports daily aggregates by query / page / country / device / date with clicks,
+impressions, CTR, average position. Views: 28-day, 90-day, 12-month, and prior-period;
+brand vs. non-brand classification; California / Arizona market clusters; property-type,
+finance/lending, brokerage/deal, and conference/event clusters; rising queries; and
+high-impression / low-CTR opportunities. This is **organic** search data — never labeled
+as paid search. Stored in `gsc_query_metrics`, `gsc_page_metrics`.
+
+## Optional Google Ads (§19)
+
+Enabled only when credentials are configured (`GOOGLE_ADS_ENABLED="true"` plus the six
+`GOOGLE_ADS_*` values). Imports aggregate campaign / ad-group / keyword / search-term /
+cost / click / impression / conversion / geo metrics the account is authorized to see.
+Kept **separate** from Search Console data; currency and date range always displayed.
+Credentials never appear in client code or logs.
+
+## CSV fallback (§20)
+
+When an API is not connected, a guided field-mapping importer accepts CSV exports for
+GA4, Search Console, Google Ads, Constant Contact, and legacy spreadsheets — with preview
+before import, type validation, duplicate detection, dry run, an import summary, a
+rejected-row file, a reversible batch, source + file checksum, and clear **DEMO vs
+PRODUCTION** labeling.
+
+## How to go from DEMO → LIVE
+
+1. In Google Cloud, create a **service account** and download its JSON key.
+2. Grant that service account **Viewer** on the GA4 property and **read** on the Search
+ Console property.
+3. Base64-encode the JSON and set the three env vars (secrets are server-only, redacted
+ from logs):
+
+ ```bash
+ GOOGLE_SERVICE_ACCOUNT_JSON_BASE64="$(base64 -i service-account.json)"
+ GA4_PROPERTY_ID="123456789" # numeric GA4 property id
+ GSC_SITE_URL="https://www.rentv.com/" # exact GSC property URL (with trailing slash)
+ ```
+
+4. Run the imports:
+
+ ```bash
+ npm run analytics:ga4
+ npm run analytics:gsc
+ # optional, only if GOOGLE_ADS_ENABLED=true and the six GOOGLE_ADS_* vars are set:
+ npm run analytics:google-ads
+ ```
+
+5. The dashboards drop the `DEMO DATA` label once live rows exist and show the data date
+ + source on every figure.
+
+## Derived-insight formulas (§17 "Sales intelligence derived from GA4")
+
+Every derived insight is documented and dated; user-level data is never exposed.
+
+- **CA/AZ audience concentration** = sessions in a state's mapped markets ÷ total
+ sessions, over the selected window.
+- **Top sections/landing pages by CRE category** = landing pages grouped by
+ advertiser-category tag, ranked by sessions.
+- **Top referral domains** = sessions where channel = Referral, grouped by source domain.
+- **Newsletter/eblast traffic** = sessions where `utm_medium ∈ {email, eblast,
+ newsletter}`, grouped by `utm_campaign`.
+- **Conference / sponsor landing-page performance** = sessions + engagement rate + key
+ events for landing pages tagged as conference or sponsor/campaign pages.
+- **Period-over-period / YoY** = metric(window) vs metric(prior equal-length window) and
+ vs the same window one year earlier; shown as delta + percent.
+
+Search Console clusters use the same market/category tag maps applied to the query string
+rather than the landing page.
diff --git a/docs/DEPLOYMENT_ADVERTISER_VIEWER.md b/docs/DEPLOYMENT_ADVERTISER_VIEWER.md
new file mode 100644
index 0000000..71500af
--- /dev/null
+++ b/docs/DEPLOYMENT_ADVERTISER_VIEWER.md
@@ -0,0 +1,72 @@
+# RENTV Advertiser Intelligence — Deployment & Local Run
+
+Plain-English instructions to run the viewer locally. The app is the house stack —
+**Express + PostgreSQL + a vanilla front-end**, Node 20+, served on port **9814**.
+
+## Run it locally (five steps)
+
+```bash
+# 1. Create the database (PostgreSQL 16+ must be running)
+createdb rentv_advertisers
+
+# 2. Install dependencies
+npm install
+
+# 3. Create the tables
+npm run db:migrate
+
+# 4. Load the RENTV seed data (5 verified advertisers, CoStar content-partner,
+# panelist-only participants, dated rate + audience snapshots, source policies)
+npm run db:seed
+
+# 5. Start the app
+npm start
+```
+
+Then open **http://localhost:9814** and click through to `/advertisers`.
+
+To reset from scratch: `npm run db:reset` (drops + re-migrates + re-seeds).
+
+## Health check
+
+```bash
+curl http://localhost:9814/healthz
+# -> {"ok":true,"service":"rentv-adintel", ...}
+```
+
+## Environment variables
+
+Copy `.env.example` to `.env` and fill in what you need. The app boots with **none of the
+optional connectors configured** — it runs on DEMO fixtures. Key variables:
+
+| Variable | Purpose | Default |
+|----------|---------|---------|
+| `PORT` | HTTP port | `9814` |
+| `APP_BASE_URL` | Base URL for links | `http://localhost:9814` |
+| `AUTH_SECRET` | Session/auth secret | `replace-me` (set a real one) |
+| `DATABASE_URL` | Postgres DSN; overrides socket settings when set | empty |
+| `PGHOST` / `PGDATABASE` | House convention: `/tmp` socket, db `rentv_advertisers` | `/tmp` / `rentv_advertisers` |
+| `SEARCH_PROVIDER` | `manual` \| `google_cse` \| `brave` \| `bing` \| `serper` | `manual` |
+| `GOOGLE_SERVICE_ACCOUNT_JSON_BASE64` + `GA4_PROPERTY_ID` + `GSC_SITE_URL` | Go GA4/GSC from DEMO → LIVE (see `ANALYTICS_CONNECTORS.md`) | empty |
+| `GOOGLE_ADS_ENABLED` + `GOOGLE_ADS_*` | Optional Google Ads connector | `false` |
+| `GMAIL_IMPORT_ENABLED` + `GMAIL_*` | Optional admin-only Gmail import | `false` |
+| `ALLOW_AUTOMATED_PUBLIC_WEB_RESEARCH` | Master switch for automated fetching | `false` |
+| `DEFAULT_REQUESTS_PER_MINUTE` | Conservative crawl rate | `6` |
+| `EXPORT_MAX_ROWS` | Export row cap | `100000` |
+
+Secrets are **server-only** and redacted from logs. Never commit `.env`.
+
+## Tests
+
+```bash
+npm test # node --test test/*.test.js
+```
+
+Expect the scoring / types / compliance suites to pass; the classification and
+export-filter suites guard-skip until those teammate modules land.
+
+## Live deploy is Steve-gated
+
+A **live/production deploy is gated** — do not push this to a public host without Steve's
+explicit approval. Local run is unrestricted; going live (DNS, public host, Kamatera,
+real credentials, sending to any list) is a human-approval step.
diff --git a/docs/EXPORT_FORMAT.md b/docs/EXPORT_FORMAT.md
new file mode 100644
index 0000000..b3a6b98
--- /dev/null
+++ b/docs/EXPORT_FORMAT.md
@@ -0,0 +1,74 @@
+# RENTV Advertiser Intelligence — Export Format
+
+The one-click **Download Everything** action (§28) produces a single ZIP. It runs through
+a job with visible progress (large exports are never streamed unbounded in one request),
+and every file honors suppression, do-not-contact, private notes, and source rights.
+
+## ZIP manifest (§28)
+
+```text
+RENTV-Advertiser-Intelligence-YYYY-MM-DD/
+ executive-viewer.html # self-contained offline viewer (double-click to open)
+ README.html # what's in the export + how to read it
+ advertisers.csv # the advertiser table
+ advertisers.xlsx # same, Excel workbook
+ contacts.csv # public business contacts (rights-filtered)
+ contacts.xlsx
+ ad-sightings.csv # verified ad/sponsor sightings + evidence pointers
+ creatives.csv # creative assets metadata (export-allowed only)
+ conference-sponsors.csv # event ↔ organization sponsor/exhibitor rows
+ conferences.csv # events list
+ prospects.csv # ranked RENTV opportunities + score factors
+ analytics-summary.csv # GA4/GSC rollup (DEMO or LIVE, labeled)
+ ga4-landing-pages.csv
+ ga4-geography.csv
+ gsc-queries.csv
+ gsc-pages.csv
+ google-ads-campaigns.csv # ONLY when Google Ads is connected
+ source-manifest.json # every source + policy + access method + review date
+ methodology.json # score weights, formulas, classification definitions
+ export-audit.json # who exported, when, row counts, rights filters applied
+ thumbnails/ # web thumbnails for export-allowed creatives
+ evidence/ # ONLY assets flagged export-allowed (never internal-only)
+ database-readonly.sqlite # optional portable snapshot (never a second source of truth)
+```
+
+`google-ads-campaigns.csv` appears only when the Google Ads connector is configured. The
+SQLite snapshot is an **export artifact only** — PostgreSQL remains the canonical source
+of truth.
+
+## `executive-viewer.html` behavior
+
+A self-contained, read-only HTML file for handing to a non-technical stakeholder:
+
+- **Works by double-clicking it** — no server, no build step, no network.
+- Uses only **relative local assets** inside the ZIP folder.
+- Provides **in-browser search and filters** over the advertiser data (California /
+ Arizona / All, Verified Only).
+- Shows **advertiser cards + thumbnails** with status badges (text, not color-only).
+- Opens every **source link in a new tab**.
+- Shows the **data date and source coverage** so the reader knows how fresh it is.
+- **Omits internal-only evidence images** — only export-allowed thumbnails are embedded.
+- Is **accessible and printable**.
+
+## Rights filtering applied to every export
+
+`applyExportRights(rows, kind)` (`src/export/rights.js`) is the single choke point every
+dataset passes through before it is written. It removes:
+
+- **suppressed** rows — organizations/people named in an active suppression request
+ (matched by id via a suppression set the caller builds from `suppression_requests`),
+- **do-not-contact** contacts and any contact with `export_allowed = false`,
+- **private notes** (`is_private = true`).
+
+For **creative assets**, the metadata row is retained but the image bytes are gated by
+`rights_status`: `EXPORT_ALLOWED` → bundle the file in `thumbnails/`;
+`INTERNAL_EVIDENCE_ONLY` / `LINK_ONLY` / `UNKNOWN` → **omit the bytes**, keep only a
+link-only placeholder (each row is annotated with an `_exportClass` of `include` or
+`link_only`). So an internal-only image's pixels never leave the app even though its
+citation remains.
+
+`test/export-filter.test.js` locks each of these in (including that no
+`INTERNAL_EVIDENCE_ONLY` or `UNKNOWN`-rights asset is ever classified `include`). Because
+the same helper gates the CSV/XLSX rows, the `evidence/` folder, and the offline viewer,
+an internal-only image or a suppressed company cannot leak through any one path.
diff --git a/docs/SECURITY_ADVERTISER_VIEWER.md b/docs/SECURITY_ADVERTISER_VIEWER.md
new file mode 100644
index 0000000..dffb54c
--- /dev/null
+++ b/docs/SECURITY_ADVERTISER_VIEWER.md
@@ -0,0 +1,74 @@
+# RENTV Advertiser Intelligence — Security Posture
+
+The security controls from the master prompt (§32), and where each is enforced. The theme
+is **enforced-in-code, not documented-only**: the SSRF guard, LinkedIn block, and
+no-inferred-email rule all have executable tests.
+
+## HTTP hardening
+
+- **Secure headers** on every response (`server.js`): `X-Content-Type-Options: nosniff`,
+ `X-Frame-Options: SAMEORIGIN`, `Referrer-Policy: strict-origin-when-cross-origin`, and
+ `x-powered-by` disabled.
+- **Content Security Policy** — a conservative CSP served on every page:
+ `default-src 'self'; img-src 'self' data:; style-src 'self' 'unsafe-inline';
+ script-src 'self'; base-uri 'self'; form-action 'self'`. Vanilla pages only, no CDNs,
+ no third-party script origins.
+- **Secure cookies, CSRF protection, server-side authorization, workspace isolation** —
+ the auth layer sets secure/SameSite cookies, guards state-changing routes with CSRF
+ tokens, and enforces RBAC server-side (never trusting the client). Every workspace's
+ data is isolated.
+
+## SSRF guard + LinkedIn block
+
+- Every outbound fetch goes through `lib/compliance/fetch-guard.js`. `assertFetchAllowed`
+ rejects:
+ - non-http(s) protocols,
+ - **LinkedIn hosts** (`linkedin.com`, `www.linkedin.com`, `m.linkedin.com`, `lnkd.in`
+ and subdomains) — a hard block (§6.4),
+ - any host that resolves (any A/AAAA record) to a **private / loopback / link-local /
+ metadata / CGNAT** range — `10/8`, `172.16/12`, `192.168/16`, `127/8`,
+ `169.254/16` (incl. the `169.254.169.254` cloud-metadata IP), `0/8`, `100.64/10`,
+ and the IPv6 equivalents (`::1`, `fc00::/7`, `fe80::/10`).
+- Redirects are followed **manually** and re-checked at every hop, so a redirect to
+ LinkedIn or a private IP is blocked too.
+- `safeFetch` refuses entirely unless `ALLOW_AUTOMATED_PUBLIC_WEB_RESEARCH === 'true'`,
+ sends a descriptive admin-contact User-Agent, and throttles per host.
+- Tested in `test/compliance.test.js` (LinkedIn URL, `169.254.169.254`, `127.0.0.1`, and
+ `isBlockedIp` range checks).
+
+## No-inferred-email
+
+- `lib/compliance/no-inferred-email.js` — `assertContactEvidence` refuses to store any
+ contact lacking a `source_evidence_id` **and** `explicitly_public === true`;
+ `looksLikePatternEmail` flags a name-permutation address so importers/UI can reject it.
+ Enforced and tested. We never generate an email from a naming pattern.
+
+## Source-policy enforcement
+
+- `lib/compliance/source-policy.js` — `validateSourcePolicy` / `assertSourceEnabledLegal`
+ reject any policy that enables automation against a prohibited proprietary DB (CoStar,
+ LoopNet, ZoomInfo, Apollo, MLS) or LinkedIn. Automated tests fail when a prohibited
+ source is enabled (§6 "create a source-policy validator and automated tests that fail
+ when a prohibited source is enabled").
+
+## Uploads & images
+
+- **File size/type limits** and malware-safe upload handling on every upload path.
+- **EXIF stripping intent** — uploaded and captured images have EXIF metadata stripped
+ (privacy: no embedded GPS/device data); the source URL + timestamp are recorded in
+ our metadata, not destructively burned onto the image.
+- No unsanitized HTML is rendered; output is encoded.
+
+## Auditing & secrets
+
+- **Audit logs** for imports, exports, evidence changes, source-policy changes, merges,
+ and contact changes (`audit_logs`, `export-audit.json` in exports).
+- **Secrets are server-only** and redacted from logs — GA4 service-account JSON, Google
+ Ads credentials, Gmail OAuth tokens, and search-API keys never appear in client code or
+ log output.
+- **Secret scanning in CI** guards against committed credentials.
+
+## Click-tracking safety
+
+- Email click-tracking URLs are **stored, not resolved** — the app never auto-visits them
+ (that would create false clicks). A human opens them deliberately.
diff --git a/fixtures/sample-gsc-export.csv b/fixtures/sample-gsc-export.csv
index 429ed5d..0eeebd1 100644
--- a/fixtures/sample-gsc-export.csv
+++ b/fixtures/sample-gsc-export.csv
@@ -1,23 +1,23 @@
-Top queries,,,
-# DEMO DATA — Google Search Console export (not real RENTV data)
-Query,Clicks,Impressions,CTR,Position
-rentv commercial real estate news,4312,8924,48.32%,1.2
-rentv cre talk,3187,5412,58.89%,1.1
-rentv,2941,4201,70.01%,1.0
-los angeles commercial real estate news 2026,1876,28740,6.53%,4.7
-california commercial real estate market,1654,31240,5.30%,5.2
-orange county industrial real estate 2026,1543,19870,7.77%,3.8
-phoenix commercial real estate market 2026,1432,22140,6.47%,4.1
-inland empire industrial leasing vacancy,1321,17890,7.39%,4.3
-cre conference los angeles 2026,1214,14320,8.48%,3.2
-commercial real estate financing california,1109,19874,5.58%,5.9
-scottsdale commercial real estate office market,1014,13421,7.56%,4.0
-san diego cre market report 2026,934,12987,7.19%,4.2
-net lease investment california 2026,869,15234,5.71%,5.7
-brokerage deal los angeles commercial property sale,812,17890,4.54%,7.1
-multifamily apartment market phoenix az rent,768,11234,6.84%,4.8
-naiop southern california awards 2026,714,4321,16.53%,2.1
-cre lending debt fund california rates 2026,662,14321,4.62%,6.8
-rentv property spotlight advertise,621,1234,50.31%,1.4
-arizona data center market 2026,578,8921,6.48%,5.1
-bay area life sciences lab space leasing,534,10234,5.22%,6.2
+Top queries,,,,,
+# DEMO DATA — Google Search Console export (not real RENTV analytics)
+Query,Clicks,Impressions,CTR,Position,Date
+rentv commercial real estate news,4312,8924,48.32%,1.2,2026-08-06
+rentv cre talk,3187,5412,58.89%,1.1,2026-08-06
+rentv,2941,4201,70.01%,1.0,2026-08-06
+los angeles commercial real estate news 2026,1876,28740,6.53%,4.7,2026-08-06
+california commercial real estate market,1654,31240,5.30%,5.2,2026-08-06
+orange county industrial real estate 2026,1543,19870,7.77%,3.8,2026-08-06
+phoenix commercial real estate market 2026,1432,22140,6.47%,4.1,2026-08-06
+inland empire industrial leasing vacancy,1321,17890,7.39%,4.3,2026-08-06
+cre conference los angeles 2026,1214,14320,8.48%,3.2,2026-08-06
+commercial real estate financing california,1109,19874,5.58%,5.9,2026-08-06
+scottsdale commercial real estate office market,1014,13421,7.56%,4.0,2026-08-06
+san diego cre market report 2026,934,12987,7.19%,4.2,2026-08-06
+net lease investment california 2026,869,15234,5.71%,5.7,2026-08-06
+brokerage deal los angeles commercial property sale,812,17890,4.54%,7.1,2026-08-06
+multifamily apartment market phoenix az rent,768,11234,6.84%,4.8,2026-08-06
+naiop southern california awards 2026,714,4321,16.53%,2.1,2026-08-06
+cre lending debt fund california rates 2026,662,14321,4.62%,6.8,2026-08-06
+rentv property spotlight advertise,621,1234,50.31%,1.4,2026-08-06
+arizona data center market 2026,578,8921,6.48%,5.1,2026-08-06
+bay area life sciences lab space leasing,534,10234,5.22%,6.2,2026-08-06
← a0476ab auto-data-snapshot: 2026-08-07T16:25:56 (10 data files) — do
·
back to Rentv Adintel
·
merge: full-spec fan-out (data+seed, compliance, UI/routes, 048b52e →