← back to Rentv Adintel
db/seed/index.js
187 lines
'use strict';
/**
* RENTV Advertiser Intelligence — seed orchestrator.
*
* Usage: node db/seed/index.js
*
* Idempotent: safe to run multiple times. Each sub-module uses
* ON CONFLICT / existence checks to avoid duplicate rows.
*
* Execution order:
* 1. markets — geography lookup table (FK target)
* 2. sources — source_policies (FK target for evidence / rate snapshots)
* 3. organizations — orgs + evidence_records + ad_sightings + events + event_relationships
* 4. rentv-products — rentv_rate_snapshots + rentv_audience_snapshots
* 5. flyers — creative_assets for RENTV flyer files (placeholder if absent)
* 6. scores — opportunity_stages + opportunity_scores (per org)
*/
const { pool, tx } = require('../index');
const { seedMarkets } = require('./markets');
const { seedSources } = require('./sources');
const { seedOrganizations } = require('./organizations');
const { seedRentvProducts } = require('./rentv-products');
const { seedFlyers } = require('./flyers');
const { seedScores } = require('./scores');
async function run() {
console.log('=== RENTV Advertiser Intelligence — seed run ===');
console.log(`Started: ${new Date().toISOString()}`);
console.log('');
let marketCounts, sourceCounts, orgCounts, productCounts, flyerCounts, scoreCounts;
// Run all seed phases inside a single transaction so a failure rolls back cleanly.
// scores.js does a DELETE+reinsert per org — that's fine inside the same tx.
await tx(async (client) => {
console.log('[1/6] Seeding markets…');
marketCounts = await seedMarkets(client);
console.log(` markets: +${marketCounts.inserted} inserted, ${marketCounts.updated} updated`);
console.log('[2/6] Seeding source_policies…');
sourceCounts = await seedSources(client);
console.log(` source_policies: +${sourceCounts.inserted} inserted, ${sourceCounts.updated} updated`);
console.log('[3/6] Seeding organizations, evidence, ad_sightings, events…');
orgCounts = await seedOrganizations(client);
console.log(` organizations: +${orgCounts.orgs_inserted} inserted, ${orgCounts.orgs_updated} updated`);
console.log(` evidence_records: +${orgCounts.evidence_inserted} inserted`);
console.log(` ad_sightings: +${orgCounts.ad_sightings_inserted} inserted`);
console.log(` events: +${orgCounts.events_inserted} inserted`);
console.log(` event_relationships: +${orgCounts.event_relationships_inserted} inserted`);
console.log('[4/6] Seeding RENTV rate and audience snapshots…');
productCounts = await seedRentvProducts(client);
console.log(` rentv_rate_snapshots: +${productCounts.rate_inserted} inserted (${productCounts.rate_skipped} skipped/existing)`);
console.log(` rentv_audience_snapshots: +${productCounts.audience_inserted} inserted (${productCounts.audience_skipped} skipped/existing)`);
console.log('[5/6] Seeding creative_assets (RENTV flyers)…');
flyerCounts = await seedFlyers(client);
console.log(` creative_assets: +${flyerCounts.inserted} real, +${flyerCounts.placeholders} placeholder(s), ${flyerCounts.skipped} skipped/existing`);
console.log('[6/6] Seeding opportunity_stages and opportunity_scores…');
scoreCounts = await seedScores(client);
console.log(` opportunity_stages: +${scoreCounts.stages_inserted} inserted, ${scoreCounts.stages_updated} updated`);
console.log(` opportunity_scores: +${scoreCounts.scores_inserted} computed and inserted`);
});
// ---- Summary table ----------------------------------------------------------
console.log('');
console.log('=== Seed summary ===');
const fmt = (label, val) => console.log(` ${label.padEnd(32)} ${String(val).padStart(6)}`);
fmt('markets (CA + AZ)', marketCounts.inserted + marketCounts.updated);
fmt('source_policies', sourceCounts.inserted + sourceCounts.updated);
fmt('organizations', orgCounts.orgs_inserted + orgCounts.orgs_updated);
fmt('evidence_records', orgCounts.evidence_inserted);
fmt('ad_sightings', orgCounts.ad_sightings_inserted);
fmt('events', orgCounts.events_inserted);
fmt('event_relationships', orgCounts.event_relationships_inserted);
fmt('rentv_rate_snapshots', productCounts.rate_inserted + productCounts.rate_skipped);
fmt('rentv_audience_snapshots', productCounts.audience_inserted + productCounts.audience_skipped);
fmt('creative_assets', flyerCounts.inserted + flyerCounts.placeholders + flyerCounts.skipped);
fmt('opportunity_stages', scoreCounts.stages_inserted + scoreCounts.stages_updated);
fmt('opportunity_scores', scoreCounts.scores_inserted);
// ---- Verification queries ---------------------------------------------------
console.log('');
console.log('=== Verification ===');
const client = await pool.connect();
try {
// 5 verified advertisers present
const vaRes = await client.query(
`SELECT o.display_name, a.relationship_status, a.verification_status, a.confidence
FROM ad_sightings a
JOIN organizations o ON o.id=a.organization_id
WHERE a.relationship_status='VERIFIED_ADVERTISER'
ORDER BY o.display_name`
);
console.log(`\nVerified advertisers (ad_sightings): ${vaRes.rows.length}`);
for (const r of vaRes.rows) {
console.log(` [${r.relationship_status}] ${r.display_name} — verification_status: ${r.verification_status}, confidence: ${r.confidence}`);
}
// CoStar content partner
const costarRes = await client.query(
`SELECT o.display_name, er.relationship_status
FROM event_relationships er
JOIN organizations o ON o.id=er.organization_id
WHERE er.relationship_status='VERIFIED_CONTENT_PARTNER'`
);
console.log(`\nContent partners (event_relationships): ${costarRes.rows.length}`);
for (const r of costarRes.rows) {
console.log(` [${r.relationship_status}] ${r.display_name}`);
}
// Panelists NOT labeled as sponsors
const panelistRes = await client.query(
`SELECT o.display_name, er.relationship_status
FROM event_relationships er
JOIN organizations o ON o.id=er.organization_id
WHERE er.relationship_status IN ('SPEAKER_OR_PANELIST_ONLY','LIKELY_PROSPECT')
ORDER BY er.relationship_status, o.display_name`
);
console.log(`\nPanelists/prospects (event_relationships, NOT labeled as sponsors): ${panelistRes.rows.length}`);
for (const r of panelistRes.rows) {
console.log(` [${r.relationship_status}] ${r.display_name}`);
}
// Verify no panelist is mislabeled as sponsor in event_relationships
const mislabelRes = await client.query(
`SELECT count(*) AS cnt
FROM event_relationships
WHERE relationship_status IN ('VERIFIED_CONFERENCE_SPONSOR','VERIFIED_ADVERTISER','VERIFIED_EXHIBITOR','VERIFIED_MEDIA_PARTNER')`
);
const mislabelCount = parseInt(mislabelRes.rows[0].cnt, 10);
console.log(
`\nSafety check — event_relationships rows with sponsor/advertiser status: ${mislabelCount}` +
(mislabelCount === 0 ? ' (PASS — panelists correctly classified)' : ' (REVIEW NEEDED)')
);
// Audience snapshots
const audRes = await client.query(
`SELECT metric_key, value_numeric, observed_at, value_text
FROM rentv_audience_snapshots ORDER BY observed_at`
);
console.log(`\nAudience snapshots (must be 2 separate rows): ${audRes.rows.length}`);
for (const r of audRes.rows) {
console.log(` ${r.observed_at}: ${r.metric_key} = ${r.value_numeric}`);
}
// Admin confirm audit log
const adminConfirmRes = await client.query(
`SELECT count(*) AS cnt FROM audit_logs WHERE action='SEED_REQUIRES_ADMIN_CONFIRM'`
);
console.log(`\nAdmin confirm audit log entries (AUTHORIZED_INTERNAL_EMAIL): ${adminConfirmRes.rows[0].cnt}`);
// Missing flyer assets requiring upload
const flyerAuditRes = await client.query(
`SELECT detail->>'file_name' AS file_name, detail->>'note' AS note
FROM audit_logs WHERE action='SEED_ASSET_MISSING'`
);
if (flyerAuditRes.rows.length > 0) {
console.log(`\nAdmin upload required for ${flyerAuditRes.rows.length} flyer(s):`);
for (const r of flyerAuditRes.rows) {
console.log(` - ${r.file_name}`);
console.log(` ${r.note}`);
}
}
} finally {
client.release();
}
console.log('');
console.log(`=== Seed complete: ${new Date().toISOString()} ===`);
await pool.end();
}
run().catch((err) => {
console.error('Seed FAILED:', err.message);
console.error(err.stack);
process.exit(1);
});