← back to Rentv
pr-intelligence: plan doc, isolated pg layer (rentv_pr), schema + seed-config migrations
459558da8431e77e98e8c26a12f04300b3b40a26 · 2026-07-30 10:53:03 -0700 · Steve Abrams
Files touched
M package-lock.jsonM package.jsonA src/pr/db.jsA src/pr/migrations/001_init.sqlA src/pr/migrations/002_seed_config.sql
Diff
commit 459558da8431e77e98e8c26a12f04300b3b40a26
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jul 30 10:53:03 2026 -0700
pr-intelligence: plan doc, isolated pg layer (rentv_pr), schema + seed-config migrations
---
package-lock.json | 153 ++++++++++-
package.json | 2 +-
src/pr/db.js | 125 +++++++++
src/pr/migrations/001_init.sql | 462 ++++++++++++++++++++++++++++++++++
src/pr/migrations/002_seed_config.sql | 108 ++++++++
5 files changed, 846 insertions(+), 4 deletions(-)
diff --git a/package-lock.json b/package-lock.json
index 71d2d7af..33d07b71 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -1,14 +1,15 @@
{
- "name": "rentv-v1",
+ "name": "rentv",
"version": "0.10.0",
"lockfileVersion": 3,
"requires": true,
"packages": {
"": {
- "name": "rentv-v1",
+ "name": "rentv",
"version": "0.10.0",
"dependencies": {
- "express": "^4.21.2"
+ "express": "^4.21.2",
+ "pg": "^8.22.0"
}
},
"node_modules/accepts": {
@@ -564,6 +565,134 @@
"integrity": "sha512-A/AGNMFN3c8bOlvV9RreMdrv7jsmF9XIfDeCd87+I8RNg6s78BhJxMu69NEMHBSJFxKidViTEdruRwEk/WIKqA==",
"license": "MIT"
},
+ "node_modules/pg": {
+ "version": "8.22.0",
+ "resolved": "https://registry.npmjs.org/pg/-/pg-8.22.0.tgz",
+ "integrity": "sha512-8wih1vVIBMxoUM2oB4soJsD9tDnDpLv4OXBJ+EJzFsvycD+lfyIreC2gGHq78f8jbLLt+bvlPTFdFZfJkOuzAA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-connection-string": "^2.14.0",
+ "pg-pool": "^3.14.0",
+ "pg-protocol": "^1.15.0",
+ "pg-types": "2.2.0",
+ "pgpass": "1.0.5"
+ },
+ "engines": {
+ "node": ">= 16.0.0"
+ },
+ "optionalDependencies": {
+ "pg-cloudflare": "^1.4.0"
+ },
+ "peerDependencies": {
+ "pg-native": ">=3.0.1"
+ },
+ "peerDependenciesMeta": {
+ "pg-native": {
+ "optional": true
+ }
+ }
+ },
+ "node_modules/pg-cloudflare": {
+ "version": "1.4.0",
+ "resolved": "https://registry.npmjs.org/pg-cloudflare/-/pg-cloudflare-1.4.0.tgz",
+ "integrity": "sha512-Vo7z/6rrQYxpNRylp4Tlob2elzbh+N/MOQbxFVWCxS7oEx6jF53GTJFxK2WWpKuBRkmiin4Mt+xofFDjx09R0A==",
+ "license": "MIT",
+ "optional": true
+ },
+ "node_modules/pg-connection-string": {
+ "version": "2.14.0",
+ "resolved": "https://registry.npmjs.org/pg-connection-string/-/pg-connection-string-2.14.0.tgz",
+ "integrity": "sha512-XwWDGcLRGCXAR8F/AM5bG7Q+A3Wm2s6QeEjlOKZLlH3UYcguiqCWKyWXVag5TLTIjR7oOJUY8kcADaZgWPyLeg==",
+ "license": "MIT"
+ },
+ "node_modules/pg-int8": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/pg-int8/-/pg-int8-1.0.1.tgz",
+ "integrity": "sha512-WCtabS6t3c8SkpDBUlb1kjOs7l66xsGdKpIPZsg4wR+B3+u9UAum2odSsF9tnvxg80h4ZxLWMy4pRjOsFIqQpw==",
+ "license": "ISC",
+ "engines": {
+ "node": ">=4.0.0"
+ }
+ },
+ "node_modules/pg-pool": {
+ "version": "3.14.0",
+ "resolved": "https://registry.npmjs.org/pg-pool/-/pg-pool-3.14.0.tgz",
+ "integrity": "sha512-gKtPkFdQPU3DksooVLi9LsjZxrsBUZIpa+7aVx+LV5pNh0KzP4Zleud2po+ConrxbuXGBJ6Hfer6hdgpIBpBaw==",
+ "license": "MIT",
+ "peerDependencies": {
+ "pg": ">=8.0"
+ }
+ },
+ "node_modules/pg-protocol": {
+ "version": "1.15.0",
+ "resolved": "https://registry.npmjs.org/pg-protocol/-/pg-protocol-1.15.0.tgz",
+ "integrity": "sha512-cq9sECI5s0+uPUXjbz8ioyPJni6RzsRib0US67i5IoTZKw8fNeYlVE7u8F4dG7vEJJtc5wdD1K189lCCUwqWTQ==",
+ "license": "MIT"
+ },
+ "node_modules/pg-types": {
+ "version": "2.2.0",
+ "resolved": "https://registry.npmjs.org/pg-types/-/pg-types-2.2.0.tgz",
+ "integrity": "sha512-qTAAlrEsl8s4OiEQY69wDvcMIdQN6wdz5ojQiOy6YRMuynxenON0O5oCpJI6lshc6scgAY8qvJ2On/p+CXY0GA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-int8": "1.0.1",
+ "postgres-array": "~2.0.0",
+ "postgres-bytea": "~1.0.0",
+ "postgres-date": "~1.0.4",
+ "postgres-interval": "^1.1.0"
+ },
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/pgpass": {
+ "version": "1.0.5",
+ "resolved": "https://registry.npmjs.org/pgpass/-/pgpass-1.0.5.tgz",
+ "integrity": "sha512-FdW9r/jQZhSeohs1Z3sI1yxFQNFvMcnmfuj4WBMUTxOrAyLMaTcE1aAMBiTlbMNaXvBCQuVi0R7hd8udDSP7ug==",
+ "license": "MIT",
+ "dependencies": {
+ "split2": "^4.1.0"
+ }
+ },
+ "node_modules/postgres-array": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/postgres-array/-/postgres-array-2.0.0.tgz",
+ "integrity": "sha512-VpZrUqU5A69eQyW2c5CA1jtLecCsN2U/bD6VilrFDWq5+5UIEVO7nazS3TEcHf1zuPYO/sqGvUvW62g86RXZuA==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/postgres-bytea": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/postgres-bytea/-/postgres-bytea-1.0.1.tgz",
+ "integrity": "sha512-5+5HqXnsZPE65IJZSMkZtURARZelel2oXUEO8rH83VS/hxH5vv1uHquPg5wZs8yMAfdv971IU+kcPUczi7NVBQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-date": {
+ "version": "1.0.7",
+ "resolved": "https://registry.npmjs.org/postgres-date/-/postgres-date-1.0.7.tgz",
+ "integrity": "sha512-suDmjLVQg78nMK2UZ454hAG+OAW+HQPZ6n++TNDUX+L0+uUlLywnoxJKDou51Zm+zTCjrCl0Nq6J9C5hP9vK/Q==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-interval": {
+ "version": "1.2.0",
+ "resolved": "https://registry.npmjs.org/postgres-interval/-/postgres-interval-1.2.0.tgz",
+ "integrity": "sha512-9ZhXKM/rw350N1ovuWHbGxnGh/SNJ4cnxHiM0rxE4VN41wsg8P8zWn9hv/buK00RP4WvlOyr/RBDiptyxVbkZQ==",
+ "license": "MIT",
+ "dependencies": {
+ "xtend": "^4.0.0"
+ },
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
"node_modules/proxy-addr": {
"version": "2.0.7",
"resolved": "https://registry.npmjs.org/proxy-addr/-/proxy-addr-2.0.7.tgz",
@@ -766,6 +895,15 @@
"url": "https://github.com/sponsors/ljharb"
}
},
+ "node_modules/split2": {
+ "version": "4.2.0",
+ "resolved": "https://registry.npmjs.org/split2/-/split2-4.2.0.tgz",
+ "integrity": "sha512-UcjcJOWknrNkF6PLX83qcHM6KHgVKNkV62Y8a5uYDVv9ydGQVwAHMKqHdJje1VTWpljG0WYpCDhrCdAOYH4TWg==",
+ "license": "ISC",
+ "engines": {
+ "node": ">= 10.x"
+ }
+ },
"node_modules/statuses": {
"version": "2.0.2",
"resolved": "https://registry.npmjs.org/statuses/-/statuses-2.0.2.tgz",
@@ -823,6 +961,15 @@
"engines": {
"node": ">= 0.8"
}
+ },
+ "node_modules/xtend": {
+ "version": "4.0.2",
+ "resolved": "https://registry.npmjs.org/xtend/-/xtend-4.0.2.tgz",
+ "integrity": "sha512-LKYU1iAXJXUgAXn9URjiu+MWhyUXHsvfp7mcuYm9dSUKK0/CjtrUwFAxD82/mCWbtLsGjFIad0wIsod4zrTAEQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.4"
+ }
}
}
}
diff --git a/package.json b/package.json
index 4547660a..9c890e7e 100644
--- a/package.json
+++ b/package.json
@@ -1 +1 @@
-{"name":"rentv","version":"0.10.0","private":true,"main":"server.js","scripts":{"start":"node server.js","pull":"node scripts/pull-news.mjs"},"dependencies":{"express":"^4.21.2"}}
\ No newline at end of file
+{"name":"rentv","version":"0.10.0","private":true,"main":"server.js","scripts":{"start":"node server.js","pull":"node scripts/pull-news.mjs"},"dependencies":{"express":"^4.21.2","pg":"^8.22.0"}}
\ No newline at end of file
diff --git a/src/pr/db.js b/src/pr/db.js
new file mode 100644
index 00000000..0f722142
--- /dev/null
+++ b/src/pr/db.js
@@ -0,0 +1,125 @@
+'use strict';
+// CRE PR Intelligence — Postgres access layer (isolated from the rest of rentv).
+//
+// Design notes:
+// - This module is the ONLY place that talks to Postgres for the PR feature. It uses the
+// raw `pg` driver (house standard across Steve's stack) — no ORM, no codegen.
+// - It NEVER touches dw_unified or any existing prod table. All PR objects live in their own
+// database (default `rentv_pr`) with the `pr_` table prefix.
+// - Graceful degradation: if Postgres is unreachable / not yet created, health() reports it and
+// the API layer returns a clean 503 instead of crashing the rentv site. The pool is created
+// lazily so simply requiring this file has no side effects.
+//
+// Configuration (env):
+// PR_DATABASE_URL full connection string (takes precedence)
+// otherwise assembled from PGHOST (default /tmp socket), PGPORT, PGUSER, PGPASSWORD,
+// and PR_DB_NAME (default `rentv_pr`).
+const path = require('path');
+const fs = require('fs');
+
+let Pool;
+try { ({ Pool } = require('pg')); } catch { Pool = null; } // pg may not be installed yet
+
+const DB_NAME = process.env.PR_DB_NAME || 'rentv_pr';
+
+function connConfig() {
+ if (process.env.PR_DATABASE_URL) return { connectionString: process.env.PR_DATABASE_URL };
+ // Default to the local unix socket used across this machine (dw_unified lives on the same server).
+ return {
+ host: process.env.PGHOST || '/tmp',
+ port: process.env.PGPORT ? Number(process.env.PGPORT) : 5432,
+ user: process.env.PGUSER || process.env.USER,
+ password: process.env.PGPASSWORD || undefined,
+ database: DB_NAME,
+ max: Number(process.env.PR_DB_POOL || 6),
+ idleTimeoutMillis: 30000,
+ connectionTimeoutMillis: 5000,
+ };
+}
+
+let _pool = null;
+let _initError = null;
+
+function pool() {
+ if (!Pool) { _initError = 'pg driver not installed (run: npm install)'; return null; }
+ if (!_pool) {
+ try {
+ _pool = new Pool(connConfig());
+ _pool.on('error', (e) => { _initError = e.message; }); // don't crash on idle client errors
+ } catch (e) { _initError = e.message; return null; }
+ }
+ return _pool;
+}
+
+/** Run a parameterized query. Returns the pg result. Throws on error (callers handle). */
+async function query(text, params) {
+ const p = pool();
+ if (!p) throw new Error(_initError || 'database unavailable');
+ return p.query(text, params);
+}
+
+/** Convenience: return rows array. */
+async function rows(text, params) { return (await query(text, params)).rows; }
+/** Convenience: return first row or null. */
+async function one(text, params) { return (await query(text, params)).rows[0] || null; }
+
+/** Run fn(client) inside a transaction. Rolls back on throw. */
+async function tx(fn) {
+ const p = pool();
+ if (!p) throw new Error(_initError || 'database unavailable');
+ const client = await p.connect();
+ try {
+ await client.query('BEGIN');
+ const out = await fn(client);
+ await client.query('COMMIT');
+ return out;
+ } catch (e) {
+ try { await client.query('ROLLBACK'); } catch { /* ignore */ }
+ throw e;
+ } finally {
+ client.release();
+ }
+}
+
+/** Lightweight health probe used by /api/pr/health and the mount guard. */
+async function health() {
+ if (!Pool) return { ok: false, db: 'unavailable', reason: 'pg driver not installed (run npm install)' };
+ try {
+ const r = await query('SELECT 1 AS ok', []);
+ // How many migrations applied? (0 if table missing.)
+ let migrations = 0;
+ try { migrations = Number((await one('SELECT count(*)::int AS n FROM pr_migrations', []))?.n || 0); } catch { migrations = 0; }
+ return { ok: r.rows[0].ok === 1, db: 'connected', database: DB_NAME, migrations };
+ } catch (e) {
+ return { ok: false, db: 'unavailable', reason: e.message, hint: `create the database once: createdb ${DB_NAME}` };
+ }
+}
+
+const MIGRATIONS_DIR = path.join(__dirname, 'migrations');
+
+/** Apply any migration files in migrations/ not yet recorded in pr_migrations, in filename order. */
+async function runMigrations({ log = () => {} } = {}) {
+ const p = pool();
+ if (!p) throw new Error(_initError || 'database unavailable (is Postgres running? run: createdb ' + DB_NAME + ')');
+ await query(`CREATE TABLE IF NOT EXISTS pr_migrations (
+ id serial PRIMARY KEY, name text UNIQUE NOT NULL, applied_at timestamptz NOT NULL DEFAULT now())`, []);
+ const applied = new Set((await rows('SELECT name FROM pr_migrations', [])).map((r) => r.name));
+ const files = fs.existsSync(MIGRATIONS_DIR)
+ ? fs.readdirSync(MIGRATIONS_DIR).filter((f) => f.endsWith('.sql')).sort()
+ : [];
+ const done = [];
+ for (const f of files) {
+ if (applied.has(f)) continue;
+ const sql = fs.readFileSync(path.join(MIGRATIONS_DIR, f), 'utf8');
+ log(`applying ${f} …`);
+ await tx(async (client) => {
+ await client.query(sql);
+ await client.query('INSERT INTO pr_migrations(name) VALUES ($1)', [f]);
+ });
+ done.push(f);
+ log(` ✓ ${f}`);
+ }
+ return { applied: done, alreadyApplied: [...applied] };
+}
+
+module.exports = { query, rows, one, tx, health, runMigrations, DB_NAME, connConfig };
diff --git a/src/pr/migrations/001_init.sql b/src/pr/migrations/001_init.sql
new file mode 100644
index 00000000..81cc1d65
--- /dev/null
+++ b/src/pr/migrations/001_init.sql
@@ -0,0 +1,462 @@
+-- CRE PR Intelligence — initial schema.
+-- Additive only. Lives in its own database (rentv_pr); never touches existing rentv data
+-- or dw_unified. All tables carry the pr_ prefix so a future merge into a shared DB
+-- cannot collide.
+
+-- ── Enums ────────────────────────────────────────────────────────────────────
+DO $$ BEGIN
+ CREATE TYPE pr_org_status AS ENUM
+ ('discovered','needs_review','verified','incomplete','duplicate','archived',
+ 'ready_for_outreach','contacted','active_relationship');
+EXCEPTION WHEN duplicate_object THEN NULL; END $$;
+
+DO $$ BEGIN
+ CREATE TYPE pr_person_status AS ENUM
+ ('discovered','needs_verification','verified','stale','wrong_person','left_company',
+ 'duplicate','suppressed','ready_for_outreach','contacted','replied');
+EXCEPTION WHEN duplicate_object THEN NULL; END $$;
+
+DO $$ BEGIN
+ CREATE TYPE pr_outreach_status AS ENUM
+ ('not_started','draft_generated','needs_review','approved','provider_draft_created',
+ 'sent','replied','follow_up_due','interested','referred','not_now','declined',
+ 'bounced','opted_out','closed');
+EXCEPTION WHEN duplicate_object THEN NULL; END $$;
+
+DO $$ BEGIN
+ CREATE TYPE pr_verification AS ENUM
+ ('unverified','inferred','search_indexed','corroborated','company_site_verified',
+ 'press_release_verified','registry_verified','manually_verified','stale','invalid');
+EXCEPTION WHEN duplicate_object THEN NULL; END $$;
+
+DO $$ BEGIN
+ CREATE TYPE pr_linkedin_status AS ENUM
+ ('none','found_uncorroborated','found_corroborated','manually_verified','stale','invalid');
+EXCEPTION WHEN duplicate_object THEN NULL; END $$;
+
+DO $$ BEGIN
+ CREATE TYPE pr_run_status AS ENUM ('queued','running','paused','completed','failed','cancelled');
+EXCEPTION WHEN duplicate_object THEN NULL; END $$;
+
+DO $$ BEGIN
+ CREATE TYPE pr_job_status AS ENUM ('queued','running','paused','completed','failed','cancelled');
+EXCEPTION WHEN duplicate_object THEN NULL; END $$;
+
+-- ── Settings (single-row-per-key config; AZ gate, sender identity, retention) ─
+CREATE TABLE IF NOT EXISTS pr_settings (
+ key text PRIMARY KEY,
+ value jsonb NOT NULL,
+ updated_at timestamptz NOT NULL DEFAULT now(),
+ updated_by text
+);
+
+-- ── Organizations ────────────────────────────────────────────────────────────
+CREATE TABLE IF NOT EXISTS pr_organizations (
+ id bigserial PRIMARY KEY,
+ legal_name text,
+ display_name text NOT NULL,
+ normalized_name text NOT NULL,
+ domain text, -- normalized apex domain (nullable; some firms have none)
+ website_url text,
+ linkedin_company_url text,
+ linkedin_status pr_linkedin_status NOT NULL DEFAULT 'none',
+ organization_type text NOT NULL, -- taxonomy key (lib/taxonomy.js)
+ organization_subtype text,
+ asset_classes text[] NOT NULL DEFAULT '{}',
+ headquarters text,
+ state_presence text[] NOT NULL DEFAULT '{}', -- e.g. {CA,AZ}
+ metros text[] NOT NULL DEFAULT '{}', -- metro keys (lib/geo.js)
+ counties text[] NOT NULL DEFAULT '{}',
+ office_addresses jsonb NOT NULL DEFAULT '[]',
+ parent_organization_id bigint REFERENCES pr_organizations(id) ON DELETE SET NULL,
+ internal_pr_status text NOT NULL DEFAULT 'unknown', -- unknown|has_internal_pr|no_internal_pr|external_agency|both
+ press_page_url text,
+ newsroom_url text,
+ general_press_email text,
+ general_press_phone text,
+ contact_form_url text,
+ priority_score int NOT NULL DEFAULT 0 CHECK (priority_score BETWEEN 0 AND 100),
+ confidence_score int NOT NULL DEFAULT 0 CHECK (confidence_score BETWEEN 0 AND 100),
+ score_components jsonb NOT NULL DEFAULT '{}', -- transparent breakdown shown in UI
+ verification_status pr_verification NOT NULL DEFAULT 'unverified',
+ last_verified_at timestamptz,
+ lifecycle_status pr_org_status NOT NULL DEFAULT 'discovered',
+ duplicate_of_id bigint REFERENCES pr_organizations(id) ON DELETE SET NULL,
+ notes text,
+ tags text[] NOT NULL DEFAULT '{}',
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS pr_org_normname_idx ON pr_organizations (normalized_name);
+CREATE INDEX IF NOT EXISTS pr_org_domain_idx ON pr_organizations (domain);
+CREATE INDEX IF NOT EXISTS pr_org_type_idx ON pr_organizations (organization_type);
+CREATE INDEX IF NOT EXISTS pr_org_status_idx ON pr_organizations (lifecycle_status);
+CREATE INDEX IF NOT EXISTS pr_org_metros_gin ON pr_organizations USING gin (metros);
+CREATE INDEX IF NOT EXISTS pr_org_states_gin ON pr_organizations USING gin (state_presence);
+CREATE INDEX IF NOT EXISTS pr_org_assets_gin ON pr_organizations USING gin (asset_classes);
+
+-- ── People ───────────────────────────────────────────────────────────────────
+CREATE TABLE IF NOT EXISTS pr_people (
+ id bigserial PRIMARY KEY,
+ organization_id bigint REFERENCES pr_organizations(id) ON DELETE SET NULL,
+ first_name text,
+ middle_name text,
+ last_name text,
+ full_name text NOT NULL,
+ normalized_name text NOT NULL,
+ exact_title text, -- exactly as published
+ normalized_role text, -- role key (lib/taxonomy.js CONTACT_ROLES)
+ department text, -- communications|marketing|leadership|lending|title_escrow|agency|bd|other
+ seniority text, -- c_suite|evp_svp|vp|director|manager|specialist|principal|other
+ office_location text,
+ state text,
+ metro text,
+ public_work_email text,
+ email_verification_status pr_verification NOT NULL DEFAULT 'unverified',
+ public_business_phone text,
+ linkedin_url text,
+ linkedin_status pr_linkedin_status NOT NULL DEFAULT 'none',
+ linkedin_indexed_title text, -- title as it appeared in the search index (never shown as "verified")
+ linkedin_indexed_snippet text,
+ biography_url text,
+ contact_priority_score int NOT NULL DEFAULT 0 CHECK (contact_priority_score BETWEEN 0 AND 100),
+ confidence_score int NOT NULL DEFAULT 0 CHECK (confidence_score BETWEEN 0 AND 100),
+ score_components jsonb NOT NULL DEFAULT '{}',
+ verification_status pr_verification NOT NULL DEFAULT 'unverified',
+ last_verified_at timestamptz,
+ lifecycle_status pr_person_status NOT NULL DEFAULT 'discovered',
+ outreach_eligible boolean NOT NULL DEFAULT false,
+ suppression_reason text,
+ duplicate_of_id bigint REFERENCES pr_people(id) ON DELETE SET NULL,
+ original_values jsonb NOT NULL DEFAULT '{}', -- manual corrections keep the extracted original here
+ role_history jsonb NOT NULL DEFAULT '[]', -- prior roles preserved, never silently overwritten
+ notes text,
+ tags text[] NOT NULL DEFAULT '{}',
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS pr_people_org_idx ON pr_people (organization_id);
+CREATE INDEX IF NOT EXISTS pr_people_normname_idx ON pr_people (normalized_name);
+CREATE INDEX IF NOT EXISTS pr_people_email_idx ON pr_people (public_work_email);
+CREATE INDEX IF NOT EXISTS pr_people_role_idx ON pr_people (normalized_role);
+CREATE INDEX IF NOT EXISTS pr_people_status_idx ON pr_people (lifecycle_status);
+CREATE INDEX IF NOT EXISTS pr_people_linkedin_idx ON pr_people (linkedin_url);
+
+-- ── Organization relationships (agency↔client, parent↔subsidiary, …) ─────────
+CREATE TABLE IF NOT EXISTS pr_org_relationships (
+ id bigserial PRIMARY KEY,
+ source_organization_id bigint NOT NULL REFERENCES pr_organizations(id) ON DELETE CASCADE,
+ target_organization_id bigint NOT NULL REFERENCES pr_organizations(id) ON DELETE CASCADE,
+ relationship_type text NOT NULL, -- agency_client|parent_subsidiary|affiliate|vendor
+ date_started date,
+ date_ended date,
+ current_status text NOT NULL DEFAULT 'unknown', -- current|historical|unknown (never imply old = current)
+ source_id bigint, -- FK to pr_sources added below (deferred: sources table follows)
+ confidence int NOT NULL DEFAULT 0 CHECK (confidence BETWEEN 0 AND 100),
+ notes text,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now(),
+ UNIQUE (source_organization_id, target_organization_id, relationship_type)
+);
+
+-- ── Sources & field-level evidence ───────────────────────────────────────────
+CREATE TABLE IF NOT EXISTS pr_sources (
+ id bigserial PRIMARY KEY,
+ source_type text NOT NULL, -- org_website|gov_registry|sec_edgar|association|press_release|news_article|search_api|user_import|licensed_provider|manual
+ source_name text NOT NULL,
+ url text,
+ page_title text,
+ publisher text,
+ published_at timestamptz,
+ retrieved_at timestamptz NOT NULL DEFAULT now(),
+ short_excerpt text, -- limited evidentiary excerpt only — never full page copies
+ content_hash text,
+ license_or_usage_note text,
+ is_primary_source boolean NOT NULL DEFAULT false,
+ status text NOT NULL DEFAULT 'active', -- active|gone|changed|superseded
+ last_checked_at timestamptz,
+ adapter text, -- which adapter produced it
+ created_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS pr_sources_url_idx ON pr_sources (url);
+CREATE INDEX IF NOT EXISTS pr_sources_type_idx ON pr_sources (source_type);
+
+-- Field evidence: which source supports which field of which entity, at what confidence.
+CREATE TABLE IF NOT EXISTS pr_field_evidence (
+ id bigserial PRIMARY KEY,
+ source_id bigint NOT NULL REFERENCES pr_sources(id) ON DELETE CASCADE,
+ entity_type text NOT NULL, -- organization|person|relationship
+ entity_id bigint NOT NULL,
+ field_name text NOT NULL, -- e.g. exact_title, public_work_email, website_url, organization
+ field_value text, -- the value as evidenced (so later edits keep the trail)
+ confidence int NOT NULL DEFAULT 0 CHECK (confidence BETWEEN 0 AND 100),
+ created_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS pr_evidence_entity_idx ON pr_field_evidence (entity_type, entity_id);
+CREATE INDEX IF NOT EXISTS pr_evidence_field_idx ON pr_field_evidence (entity_type, entity_id, field_name);
+
+-- now the deferred FK on relationships
+DO $$ BEGIN
+ ALTER TABLE pr_org_relationships
+ ADD CONSTRAINT pr_org_rel_source_fk FOREIGN KEY (source_id) REFERENCES pr_sources(id) ON DELETE SET NULL;
+EXCEPTION WHEN duplicate_object THEN NULL; END $$;
+
+-- ── Research runs (resumable) ────────────────────────────────────────────────
+CREATE TABLE IF NOT EXISTS pr_research_runs (
+ id bigserial PRIMARY KEY,
+ state text NOT NULL, -- CA|AZ
+ metro text,
+ category text, -- organization_type or 'all'
+ query text,
+ adapter text NOT NULL,
+ started_at timestamptz,
+ completed_at timestamptz,
+ status pr_run_status NOT NULL DEFAULT 'queued',
+ pages_checked int NOT NULL DEFAULT 0,
+ candidates_found int NOT NULL DEFAULT 0,
+ records_created int NOT NULL DEFAULT 0,
+ records_updated int NOT NULL DEFAULT 0,
+ duplicates_detected int NOT NULL DEFAULT 0,
+ errors jsonb NOT NULL DEFAULT '[]',
+ retry_count int NOT NULL DEFAULT 0,
+ checkpoint jsonb NOT NULL DEFAULT '{}', -- cursor — resume from here after interruption
+ rate_limit_state jsonb NOT NULL DEFAULT '{}',
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS pr_runs_status_idx ON pr_research_runs (status);
+CREATE INDEX IF NOT EXISTS pr_runs_geo_idx ON pr_research_runs (state, metro, category);
+
+-- ── Job queue (database-backed; idempotent, resumable, backoff) ──────────────
+CREATE TABLE IF NOT EXISTS pr_jobs (
+ id bigserial PRIMARY KEY,
+ job_type text NOT NULL, -- discover-organizations, verify-organization, …
+ payload jsonb NOT NULL DEFAULT '{}', -- filters: state/metro/category/org_id/…
+ dedupe_key text, -- prevents duplicate queued jobs for same unit of work
+ status pr_job_status NOT NULL DEFAULT 'queued',
+ priority int NOT NULL DEFAULT 5, -- 1 = highest
+ run_after timestamptz NOT NULL DEFAULT now(),
+ attempts int NOT NULL DEFAULT 0,
+ max_attempts int NOT NULL DEFAULT 5,
+ last_error text,
+ checkpoint jsonb NOT NULL DEFAULT '{}',
+ run_id bigint REFERENCES pr_research_runs(id) ON DELETE SET NULL,
+ started_at timestamptz,
+ completed_at timestamptz,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE UNIQUE INDEX IF NOT EXISTS pr_jobs_dedupe_idx ON pr_jobs (dedupe_key)
+ WHERE status IN ('queued','running','paused');
+CREATE INDEX IF NOT EXISTS pr_jobs_pick_idx ON pr_jobs (status, run_after, priority);
+
+-- ── Query matrix (editable without code changes) ─────────────────────────────
+CREATE TABLE IF NOT EXISTS pr_query_templates (
+ id bigserial PRIMARY KEY,
+ name text NOT NULL,
+ template text NOT NULL, -- with [metro] / [company name] / [state] merge slots
+ target text NOT NULL DEFAULT 'organization', -- organization|person|press_contact|linkedin
+ org_types text[] NOT NULL DEFAULT '{}', -- empty = applies to all
+ states text[] NOT NULL DEFAULT '{}', -- empty = all states
+ enabled boolean NOT NULL DEFAULT true,
+ notes text,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+
+-- ── Campaigns ────────────────────────────────────────────────────────────────
+CREATE TABLE IF NOT EXISTS pr_campaigns (
+ id bigserial PRIMARY KEY,
+ name text NOT NULL,
+ state text,
+ metros text[] NOT NULL DEFAULT '{}',
+ organization_types text[] NOT NULL DEFAULT '{}',
+ asset_classes text[] NOT NULL DEFAULT '{}',
+ status text NOT NULL DEFAULT 'draft', -- draft|active|paused|archived
+ base_template_id bigint,
+ owner_user text,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+
+CREATE TABLE IF NOT EXISTS pr_campaign_members (
+ campaign_id bigint NOT NULL REFERENCES pr_campaigns(id) ON DELETE CASCADE,
+ person_id bigint NOT NULL REFERENCES pr_people(id) ON DELETE CASCADE,
+ added_at timestamptz NOT NULL DEFAULT now(),
+ added_by text,
+ PRIMARY KEY (campaign_id, person_id)
+);
+
+-- ── Letter templates, versions, blocks, snippets ─────────────────────────────
+CREATE TABLE IF NOT EXISTS pr_letter_templates (
+ id bigserial PRIMARY KEY,
+ name text NOT NULL,
+ purpose text, -- configurable main purpose
+ is_base boolean NOT NULL DEFAULT false,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+
+-- immutable versions; current = highest version per template
+CREATE TABLE IF NOT EXISTS pr_letter_versions (
+ id bigserial PRIMARY KEY,
+ template_id bigint NOT NULL REFERENCES pr_letter_templates(id) ON DELETE CASCADE,
+ version int NOT NULL,
+ subject_template text,
+ blocks jsonb NOT NULL DEFAULT '[]', -- [{key,label,content,locked,order,internal_sources:[...]}]
+ merge_fields text[] NOT NULL DEFAULT '{}',
+ created_by text,
+ change_note text,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ UNIQUE (template_id, version)
+);
+
+-- reusable content blocks: vertical-specific paragraphs, org-specific snippets
+CREATE TABLE IF NOT EXISTS pr_letter_blocks (
+ id bigserial PRIMARY KEY,
+ key text NOT NULL,
+ label text NOT NULL,
+ scope text NOT NULL DEFAULT 'vertical', -- vertical|organization|generic|followup
+ org_type text, -- when scope=vertical
+ organization_id bigint REFERENCES pr_organizations(id) ON DELETE CASCADE,
+ content text NOT NULL,
+ internal_sources jsonb NOT NULL DEFAULT '[]', -- source ids grounding any factual sentence
+ enabled boolean NOT NULL DEFAULT true,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS pr_blocks_scope_idx ON pr_letter_blocks (scope, org_type);
+
+-- ── Outreach messages / drafts ───────────────────────────────────────────────
+CREATE TABLE IF NOT EXISTS pr_outreach_messages (
+ id bigserial PRIMARY KEY,
+ person_id bigint REFERENCES pr_people(id) ON DELETE SET NULL,
+ organization_id bigint REFERENCES pr_organizations(id) ON DELETE SET NULL,
+ campaign_id bigint REFERENCES pr_campaigns(id) ON DELETE SET NULL,
+ direction text NOT NULL DEFAULT 'outbound', -- outbound|inbound
+ provider text, -- gmail|manual|null
+ provider_message_id text,
+ provider_thread_id text,
+ subject text,
+ rendered_html text,
+ rendered_text text,
+ draft_blocks jsonb NOT NULL DEFAULT '[]', -- the block composition behind rendered output
+ draft_version_id bigint REFERENCES pr_letter_versions(id) ON DELETE SET NULL,
+ internal_citations jsonb NOT NULL DEFAULT '[]', -- kept in admin record; stripped from outgoing mail
+ status pr_outreach_status NOT NULL DEFAULT 'not_started',
+ drafted_at timestamptz,
+ approved_at timestamptz,
+ approved_by text,
+ sent_at timestamptz,
+ replied_at timestamptz,
+ bounced_at timestamptz,
+ opted_out_at timestamptz,
+ follow_up_at timestamptz,
+ owner_user text,
+ raw_headers jsonb NOT NULL DEFAULT '{}', -- preserve original message metadata
+ body_preserved text, -- original body, never rewritten
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS pr_msg_person_idx ON pr_outreach_messages (person_id);
+CREATE INDEX IF NOT EXISTS pr_msg_thread_idx ON pr_outreach_messages (provider_thread_id);
+CREATE INDEX IF NOT EXISTS pr_msg_status_idx ON pr_outreach_messages (status);
+CREATE INDEX IF NOT EXISTS pr_msg_campaign_idx ON pr_outreach_messages (campaign_id);
+
+-- ── Activities (audit-visible history) & tasks ───────────────────────────────
+CREATE TABLE IF NOT EXISTS pr_activities (
+ id bigserial PRIMARY KEY,
+ entity_type text NOT NULL, -- organization|person|message|campaign|run|import
+ entity_id bigint,
+ activity text NOT NULL, -- research|manual_verification|note|draft_created|approved|sent|reply_received|follow_up_due|reassigned|opted_out|bounced|wrong_person|duplicate_resolved
+ detail jsonb NOT NULL DEFAULT '{}',
+ actor text, -- admin username or 'system:<job>'
+ created_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS pr_activities_entity_idx ON pr_activities (entity_type, entity_id);
+
+CREATE TABLE IF NOT EXISTS pr_tasks (
+ id bigserial PRIMARY KEY,
+ title text NOT NULL,
+ detail text,
+ entity_type text,
+ entity_id bigint,
+ due_at timestamptz,
+ status text NOT NULL DEFAULT 'open', -- open|done|dismissed
+ assignee text,
+ created_by text,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ completed_at timestamptz
+);
+CREATE INDEX IF NOT EXISTS pr_tasks_status_idx ON pr_tasks (status, due_at);
+
+-- ── Suppression (opt-outs, bounces, do-not-contact) ──────────────────────────
+CREATE TABLE IF NOT EXISTS pr_suppression (
+ id bigserial PRIMARY KEY,
+ email text, -- normalized lowercase
+ domain text, -- when a whole domain opted out
+ person_id bigint REFERENCES pr_people(id) ON DELETE SET NULL,
+ organization_id bigint REFERENCES pr_organizations(id) ON DELETE SET NULL,
+ reason text NOT NULL, -- opt_out|bounce|manual|complaint|legal
+ source text, -- reply|admin|import
+ permanent boolean NOT NULL DEFAULT true,
+ expires_at timestamptz,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ created_by text
+);
+CREATE UNIQUE INDEX IF NOT EXISTS pr_suppression_email_idx ON pr_suppression (email) WHERE email IS NOT NULL;
+CREATE INDEX IF NOT EXISTS pr_suppression_domain_idx ON pr_suppression (domain) WHERE domain IS NOT NULL;
+
+-- ── Audit log (every send, edit, approval, status change, merge, import) ─────
+CREATE TABLE IF NOT EXISTS pr_audit_log (
+ id bigserial PRIMARY KEY,
+ actor text NOT NULL,
+ action text NOT NULL,
+ entity_type text,
+ entity_id bigint,
+ before jsonb,
+ after jsonb,
+ detail jsonb NOT NULL DEFAULT '{}',
+ at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS pr_audit_entity_idx ON pr_audit_log (entity_type, entity_id);
+CREATE INDEX IF NOT EXISTS pr_audit_at_idx ON pr_audit_log (at);
+
+-- ── Import batches (reversible) ──────────────────────────────────────────────
+CREATE TABLE IF NOT EXISTS pr_import_batches (
+ id bigserial PRIMARY KEY,
+ kind text NOT NULL, -- org_csv|people_csv|json|linkedin_urls|contact_list
+ filename text,
+ column_map jsonb NOT NULL DEFAULT '{}',
+ dry_run boolean NOT NULL DEFAULT false,
+ row_count int NOT NULL DEFAULT 0,
+ created_count int NOT NULL DEFAULT 0,
+ updated_count int NOT NULL DEFAULT 0,
+ skipped_count int NOT NULL DEFAULT 0,
+ duplicate_count int NOT NULL DEFAULT 0,
+ report jsonb NOT NULL DEFAULT '{}',
+ reversed_at timestamptz,
+ created_by text,
+ created_at timestamptz NOT NULL DEFAULT now()
+);
+
+-- tag every imported entity with its batch so a batch can be reversed
+DO $$ BEGIN
+ ALTER TABLE pr_organizations ADD COLUMN IF NOT EXISTS import_batch_id bigint REFERENCES pr_import_batches(id) ON DELETE SET NULL;
+ ALTER TABLE pr_people ADD COLUMN IF NOT EXISTS import_batch_id bigint REFERENCES pr_import_batches(id) ON DELETE SET NULL;
+END $$;
+
+-- ── updated_at maintenance ───────────────────────────────────────────────────
+CREATE OR REPLACE FUNCTION pr_touch_updated_at() RETURNS trigger AS $$
+BEGIN NEW.updated_at = now(); RETURN NEW; END $$ LANGUAGE plpgsql;
+
+DO $$
+DECLARE t text;
+BEGIN
+ FOREACH t IN ARRAY ARRAY['pr_organizations','pr_people','pr_org_relationships','pr_research_runs',
+ 'pr_jobs','pr_query_templates','pr_campaigns','pr_letter_templates',
+ 'pr_letter_blocks','pr_outreach_messages'] LOOP
+ EXECUTE format('DROP TRIGGER IF EXISTS %I_touch ON %I', t, t);
+ EXECUTE format('CREATE TRIGGER %I_touch BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION pr_touch_updated_at()', t, t);
+ END LOOP;
+END $$;
diff --git a/src/pr/migrations/002_seed_config.sql b/src/pr/migrations/002_seed_config.sql
new file mode 100644
index 00000000..33de636c
--- /dev/null
+++ b/src/pr/migrations/002_seed_config.sql
@@ -0,0 +1,108 @@
+-- CRE PR Intelligence — seed configuration.
+-- Settings (Arizona locked by default), the editable query matrix, and the base
+-- outreach letter. The letter here is the FALLBACK letter: a full repository audit
+-- (2026-07-30) found NO existing RentV outreach letter/template anywhere in the app,
+-- so per spec we seed the fallback — editable, and never sendable until the required
+-- sender settings are completed.
+
+-- ── Settings ─────────────────────────────────────────────────────────────────
+INSERT INTO pr_settings (key, value) VALUES
+ ('arizona_unlocked', 'false'),
+ ('arizona_unlock_note', '"Locked until the California quality gate passes (or an explicit, audit-logged admin override)."'),
+ ('california_gate', '{"passed": false, "checked_at": null, "detail": null}'),
+ ('refresh_days_high', '90'),
+ ('refresh_days_normal', '180'),
+ ('sender_name', '""'),
+ ('sender_title', '""'),
+ ('sender_contact_information', '""'),
+ ('sender_postal_address', '""'),
+ ('approved_submission_method', '""'),
+ ('unsubscribe_text', '"If you would prefer not to receive media-list requests from RENTV, reply with unsubscribe and we will suppress your address immediately."'),
+ ('letter_main_purpose', '"Add RentV to the organization''s commercial real estate press and media distribution list, and invite the organization to send relevant California and Arizona commercial real estate announcements."'),
+ ('approved_facts', '{"note": "Only facts listed here may appear in letters as claims about RENTV. Do not invent circulation numbers, audience statistics, guarantees, partnerships, or coverage claims.", "facts": []}'),
+ ('retention_days_sources', '0'),
+ ('retention_days_messages','0')
+ON CONFLICT (key) DO NOTHING;
+
+-- ── Query matrix (editable in the admin without code changes) ────────────────
+INSERT INTO pr_query_templates (name, template, target, org_types, states) VALUES
+ ('metro-cre-pr', '[metro] commercial real estate public relations', 'organization', '{}', '{}'),
+ ('metro-cre-comms-director', '[metro] commercial real estate communications director', 'person', '{}', '{}'),
+ ('metro-cre-marketing-firm', '[metro] commercial real estate marketing firm', 'organization', '{pr_agency,marketing_agency}', '{}'),
+ ('metro-re-pr-agency', '[metro] real estate public relations agency', 'organization', '{pr_agency}', '{}'),
+ ('company-press-contact', '[company name] press contact', 'press_contact','{}', '{}'),
+ ('company-media-relations', '[company name] media relations', 'press_contact','{}', '{}'),
+ ('company-comms-director', '[company name] communications director', 'person', '{}', '{}'),
+ ('company-marketing-director','[company name] marketing director', 'person', '{}', '{}'),
+ ('company-newsroom', '[company name] newsroom', 'press_contact','{}', '{}'),
+ ('company-press-release', '[company name] press release', 'press_contact','{}', '{}'),
+ ('company-media-contact', '[company name] media contact', 'press_contact','{}', '{}'),
+ ('linkedin-company-comms', 'site:linkedin.com/in ("public relations" OR communications OR marketing) "[company name]"', 'linkedin', '{}', '{}'),
+ ('linkedin-state-cre-comms', 'site:linkedin.com/in ("commercial real estate" AND communications) [state]', 'linkedin', '{}', '{}'),
+ ('company-loan-closing-media','[company name] loan closing [state] media contact', 'press_contact','{bank,commercial_mortgage_bank,debt_equity_broker,private_lender}', '{}'),
+ ('state-title-bd', 'commercial title company [state] business development', 'organization', '{title_insurer,title_agency}', '{}'),
+ ('state-escrow-marketing', 'commercial escrow [state] marketing director', 'person', '{escrow_company,commercial_escrow_dept}', '{}'),
+ ('state-lender-comms', 'commercial real estate lender [state] communications', 'person', '{bank,private_lender,bridge_lender,construction_lender}', '{}'),
+ ('state-cre-pr-agency', 'commercial real estate PR agency [state]', 'organization', '{pr_agency}', '{}'),
+ ('az-cre-pr', 'commercial real estate public relations Arizona', 'organization', '{pr_agency}', '{AZ}')
+ON CONFLICT DO NOTHING;
+
+-- ── Base letter template (fallback — no pre-existing RentV letter exists) ────
+INSERT INTO pr_letter_templates (name, purpose, is_base)
+SELECT 'RENTV media-list base letter',
+ 'Add RentV to the organization''s commercial real estate press and media distribution list, and invite the organization to send relevant California and Arizona commercial real estate announcements.',
+ true
+WHERE NOT EXISTS (SELECT 1 FROM pr_letter_templates WHERE is_base = true);
+
+INSERT INTO pr_letter_versions (template_id, version, subject_template, blocks, merge_fields, created_by, change_note)
+SELECT t.id, 1,
+ 'Add RentV to your commercial real estate media list',
+ '[
+ {"key":"greeting","label":"Greeting","order":1,"locked":false,
+ "content":"Hi {{first_name}},"},
+ {"key":"intro","label":"RentV introduction","order":2,"locked":false,
+ "content":"I’m reaching out from RentV regarding commercial real estate news from {{organization_name}} in {{market_name}}."},
+ {"key":"ask","label":"The ask","order":3,"locked":false,
+ "content":"Please add RentV to the appropriate press, communications, and media distribution lists for announcements involving transactions, leases, financing, development, company growth, research, events, and executive appointments."},
+ {"key":"org_specific","label":"Organization-specific paragraph","order":4,"locked":false,
+ "content":"{{organization_specific_block}}"},
+ {"key":"submission","label":"Submission method","order":5,"locked":false,
+ "content":"Releases and media information can be sent through {{approved_submission_method}}."},
+ {"key":"signoff","label":"Signature","order":6,"locked":false,
+ "content":"Thank you,\n\n{{sender_name}}\n{{sender_title}}\nRentV\n{{sender_contact_information}}"}
+ ]'::jsonb,
+ ARRAY['first_name','organization_name','market_name','organization_specific_block',
+ 'approved_submission_method','sender_name','sender_title','sender_contact_information'],
+ 'system',
+ 'Fallback base letter seeded 2026-07-30 — repository audit found no pre-existing RentV outreach letter. Not sendable until sender settings are completed.'
+FROM pr_letter_templates t
+WHERE t.is_base = true
+ AND NOT EXISTS (SELECT 1 FROM pr_letter_versions v WHERE v.template_id = t.id);
+
+-- ── Vertical-specific letter blocks (initial set; all editable) ──────────────
+-- These carry NO factual claims about RENTV audience/coverage — only descriptive
+-- relevance framing an admin can edit before use.
+INSERT INTO pr_letter_blocks (key, label, scope, org_type, content) VALUES
+ ('v-brokerage', 'Brokerage & advisory firms', 'vertical', 'brokerage',
+ 'RentV covers sale and lease transactions, tenant-rep wins, and market reports across {{market_name}} — announcements from your brokers and research team fit directly into our coverage.'),
+ ('v-owner-dev', 'Developers, owners, operators & REITs','vertical','developer_owner',
+ 'Groundbreakings, acquisitions, dispositions, and project milestones from {{organization_name}} are the kind of development and investment news RentV publishes for {{market_name}} readers.'),
+ ('v-bank-lender', 'Banks & lenders', 'vertical', 'bank',
+ 'Loan closings, financing programs, and CRE lending leadership announcements are a regular part of RentV''s financing coverage — we welcome releases from your commercial real estate group.'),
+ ('v-debt-equity', 'Debt & equity firms', 'vertical', 'debt_equity_broker',
+ 'Placement and capital-markets announcements — financings arranged, funds closed, notable structures — fit RentV''s financing section for {{market_name}}.'),
+ ('v-title-escrow','Title & escrow companies', 'vertical', 'title_insurer',
+ 'Commercial closings, new commercial-services offices, and team announcements from title and escrow organizations are welcome additions to RentV''s transaction coverage.'),
+ ('v-pr-agency', 'PR & marketing agencies', 'vertical', 'pr_agency',
+ 'If your agency represents commercial real estate clients in {{market_name}}, we''d welcome your client announcements — and are glad to be added to the distribution lists you manage.'),
+ ('v-professional','Law, accounting, valuation & consulting','vertical','law_firm',
+ 'Deal-team announcements, practice-group hires, and market studies from professional-services firms active in CRE are relevant to RentV''s readership.'),
+ ('v-aec', 'Architecture, engineering & construction','vertical','architecture_firm',
+ 'Design awards, project completions, toppings-out, and firm news from the built-environment side of CRE fit RentV''s development coverage for {{market_name}}.'),
+ ('v-assoc-edc', 'Associations & economic development','vertical','association',
+ 'Chapter events, market forecasts, and economic-development wins are useful signals for RentV''s {{market_name}} coverage — we''d be glad to receive your announcements calendar.'),
+ ('v-proptech', 'Proptech companies', 'vertical', 'proptech',
+ 'Product launches, market data releases, and CRE-technology partnerships relevant to owners and brokers in {{market_name}} are welcome in RentV''s coverage mix.'),
+ ('f-followup', 'Optional follow-up language', 'followup', NULL,
+ 'Following up briefly on my note below — if a colleague handles the media list for {{organization_name}}, I''d appreciate a pointer to the right contact.')
+ON CONFLICT DO NOTHING;
← fecb50bb auto-save: 2026-07-30T10:47:06 (1 files) — docs/
·
back to Rentv
·
pr-intelligence: lib (geo/taxonomy/normalize/dedupe/scoring/ 14c23d86 →