src/db/metaResearchRepository.js (lines 1–1339)
- Repository
- Demand-Radar
- Path
src/db/metaResearchRepository.js
const crypto = require('crypto');
const { classifyAdLifecycle } = require('../meta/lifecycleClassifier');
const {
buildOfferFamilies,
canonicalizeUrl,
FAMILY_CLASS_ORDER,
} = require('../meta/offerFamilyClassifier');
const RUN_STATUSES = new Set(['PENDING', 'RUNNING', 'COMPLETED', 'PARTIAL', 'FAILED']);
const QUERY_RUN_STATUSES = new Set(['PENDING', 'RUNNING', 'COMPLETED', 'FAILED']);
const LONGEVITY_ORDER = {
BALON_PROBNY: 1,
TEST_W_TOKU: 2,
ROKUJACA: 3,
MOCNA: 4,
EVERGREEN: 5,
};
const RELEVANCE_STATUSES = new Set(['UNREVIEWED', 'KEEP', 'POTENTIAL', 'REJECT']);
const MR04_DECISIONS = new Set(['KEEP', 'REVIEW', 'REJECT']);
const DETAILS_STATUSES = new Set(['NOT_FETCHED', 'FETCHING', 'FETCHED', 'FAILED']);
const LIFECYCLE_VALUES = ['BALON_PROBNY', 'TEST_W_TOKU', 'ROKUJACA', 'MOCNA', 'EVERGREEN'];
const LIFECYCLE_SET = new Set(LIFECYCLE_VALUES);
function safeJsonParse(value, fallback) {
if (typeof value !== 'string') return fallback;
try {
return JSON.parse(value);
} catch {
return fallback;
}
}
function mapLegacyLifecycle(longgevityCategory) {
switch (longgevityCategory) {
case 'BALON_PROBNY':
return 'TEST_BALLOON';
case 'TEST_W_TOKU':
return 'PROMISING';
case 'ROKUJACA':
return 'ESTABLISHED';
case 'MOCNA':
return 'ESTABLISHED';
case 'EVERGREEN':
return 'EVERGREEN';
default:
return 'UNCLASSIFIED';
}
}
function normalizeQueryTextForGrouping(value) {
return String(value || '')
.trim()
.replace(/\s+/g, ' ')
.toLowerCase();
}
function normalizeQueryTextPreserveCase(value) {
return String(value || '')
.trim()
.replace(/\s+/g, ' ');
}
function mapMr04DecisionToStatus(decision) {
if (decision === 'KEEP') return 'KEEP';
if (decision === 'REVIEW') return 'POTENTIAL';
if (decision === 'REJECT') return 'REJECT';
return 'UNREVIEWED';
}
function normalizeRelevanceStatus(value) {
const normalized = String(value || '').trim().toUpperCase();
if (!RELEVANCE_STATUSES.has(normalized)) {
return 'UNREVIEWED';
}
return normalized;
}
function normalizeDetailsStatus(value) {
const normalized = String(value || '').trim().toUpperCase();
if (!DETAILS_STATUSES.has(normalized)) {
return 'NOT_FETCHED';
}
return normalized;
}
function mapRunRow(row) {
if (!row) return null;
return {
id: row.id,
projectId: row.project_id,
queryPlanId: row.query_plan_id,
provider: row.provider || 'APIFY',
providerRunId: row.provider_run_id || null,
providerDatasetId: row.provider_dataset_id || null,
status: row.status,
progressStage: row.progress_stage || null,
startedAt: row.started_at,
finishedAt: row.finished_at,
createdAt: row.created_at || row.started_at,
country: row.country,
queriesTotal: row.queries_total,
queriesCompleted: row.queries_completed,
apiHitsTotal: row.api_hits_total,
fetchedCount: row.fetched_count || 0,
uniqueAdsTotal: row.unique_ads_total,
duplicatesCount: row.duplicates_count || 0,
errorsTotal: row.errors_total,
config: safeJsonParse(row.config_json, {}),
errorMessage: row.error_message,
};
}
function mapQueryRunRow(row) {
if (!row) return null;
return {
id: row.id,
researchRunId: row.research_run_id,
queryText: row.query_text,
queryTextNormalized: row.query_text_normalized,
queryTypeSnapshot: safeJsonParse(row.query_type_snapshot_json, []),
queryCategory: row.query_category || null,
sourceUrl: row.source_url || null,
prioritySnapshot: row.priority_snapshot,
startedAt: row.started_at,
finishedAt: row.finished_at,
status: row.status,
pagesFetched: row.pages_fetched,
hitsCount: row.hits_count,
uniqueAdsCount: row.unique_ads_count,
apiHitsCount: row.api_hits_count,
errorMessage: row.error_message,
};
}
function getResearchRunById(db, runId) {
const row = db.prepare(`
SELECT *
FROM meta_research_runs
WHERE id = ?
`).get(runId);
return mapRunRow(row);
}
function getLatestResearchRunForProject(db, projectId) {
const row = db.prepare(`
SELECT *
FROM meta_research_runs
WHERE project_id = ?
ORDER BY started_at DESC
LIMIT 1
`).get(projectId);
return mapRunRow(row);
}
function extractApprovedPlanAndGroups(db, projectId, queryPlanId = null) {
const queryPlanRow = queryPlanId
? db.prepare(`
SELECT *
FROM query_plans
WHERE id = ? AND project_id = ?
LIMIT 1
`).get(queryPlanId, projectId)
: db.prepare(`
SELECT *
FROM query_plans
WHERE project_id = ? AND status = 'APPROVED'
ORDER BY updated_at DESC, generated_at DESC
LIMIT 1
`).get(projectId);
if (!queryPlanRow) {
return { queryPlan: null, queryGroups: [] };
}
if (queryPlanRow.status !== 'APPROVED') {
return {
queryPlan: {
id: queryPlanRow.id,
status: queryPlanRow.status,
},
queryGroups: [],
notApproved: true,
};
}
const seedRows = db.prepare(`
SELECT *
FROM query_plan_seeds
WHERE query_plan_id = ?
ORDER BY sort_order ASC
`).all(queryPlanRow.id);
const variantRows = db.prepare(`
SELECT qv.*, qps.id AS query_plan_seed_id, qps.source_seed_id, qps.seed_text, qps.source_seed_origin
FROM query_variants qv
JOIN query_plan_seeds qps ON qps.id = qv.query_plan_seed_id
WHERE qps.query_plan_id = ?
ORDER BY qps.sort_order ASC, qv.sort_order ASC
`).all(queryPlanRow.id);
const groupByNormalized = new Map();
for (const variant of variantRows) {
if (!variant.enabled) continue;
const normalized = normalizeQueryTextForGrouping(variant.query_text);
const preserved = normalizeQueryTextPreserveCase(variant.query_text);
if (!normalized || !preserved) continue;
const existing = groupByNormalized.get(normalized) || {
normalizedText: normalized,
queryText: preserved,
queryTypes: new Set(),
maxPriority: variant.priority,
variants: [],
};
existing.queryTypes.add(variant.query_type);
existing.maxPriority = Math.max(existing.maxPriority, variant.priority);
existing.variants.push({
queryVariantId: variant.id,
queryPlanSeedId: variant.query_plan_seed_id,
sourceSeedId: variant.source_seed_id,
seedText: variant.seed_text,
sourceSeedOrigin: variant.source_seed_origin,
queryText: preserved,
queryType: variant.query_type,
priority: variant.priority,
sortOrder: variant.sort_order,
});
groupByNormalized.set(normalized, existing);
}
const queryGroups = [...groupByNormalized.values()].map(group => ({
normalizedText: group.normalizedText,
queryText: group.queryText,
queryTypeSnapshot: [...group.queryTypes],
queryCategory: [...group.queryTypes][0] || 'CORE',
prioritySnapshot: group.maxPriority,
variants: group.variants,
}));
return {
queryPlan: {
id: queryPlanRow.id,
projectId: queryPlanRow.project_id,
plannerVersion: queryPlanRow.planner_version,
country: queryPlanRow.country,
status: queryPlanRow.status,
updatedAt: queryPlanRow.updated_at,
},
queryGroups,
seedCount: seedRows.length,
enabledVariantCount: variantRows.filter(v => v.enabled).length,
};
}
function createResearchRun(db, {
projectId,
queryPlanId,
country,
queriesTotal,
config,
provider = 'APIFY',
}) {
const id = crypto.randomUUID();
const now = new Date().toISOString();
db.prepare(`
INSERT INTO meta_research_runs (
id,
project_id,
query_plan_id,
provider,
provider_run_id,
provider_dataset_id,
status,
progress_stage,
started_at,
finished_at,
created_at,
country,
queries_total,
queries_completed,
api_hits_total,
fetched_count,
unique_ads_total,
duplicates_count,
errors_total,
config_json,
error_message
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`).run(
id,
projectId,
queryPlanId,
provider,
null,
null,
'PENDING',
'Przygotowuje query...',
now,
null,
now,
country,
queriesTotal,
0,
0,
0,
0,
0,
0,
JSON.stringify(config || {}),
null,
);
return getResearchRunById(db, id);
}
function updateResearchRunProgress(db, runId, patch) {
const updates = [];
const values = [];
const fields = {
providerRunId: 'provider_run_id',
providerDatasetId: 'provider_dataset_id',
status: 'status',
progressStage: 'progress_stage',
queriesCompleted: 'queries_completed',
apiHitsTotal: 'api_hits_total',
fetchedCount: 'fetched_count',
uniqueAdsTotal: 'unique_ads_total',
duplicatesCount: 'duplicates_count',
errorsTotal: 'errors_total',
finishedAt: 'finished_at',
errorMessage: 'error_message',
};
for (const [key, column] of Object.entries(fields)) {
if (!Object.prototype.hasOwnProperty.call(patch, key)) continue;
if (key === 'status' && patch[key] != null && !RUN_STATUSES.has(patch[key])) {
throw new Error(`Invalid run status: ${patch[key]}`);
}
updates.push(`${column} = ?`);
values.push(patch[key]);
}
if (!updates.length) return;
values.push(runId);
db.prepare(`
UPDATE meta_research_runs
SET ${updates.join(', ')}
WHERE id = ?
`).run(...values);
}
function createQueryRun(db, {
researchRunId,
queryText,
queryTextNormalized,
queryTypeSnapshot,
queryCategory,
sourceUrl,
prioritySnapshot,
}) {
const id = crypto.randomUUID();
const now = new Date().toISOString();
db.prepare(`
INSERT INTO meta_query_runs (
id,
research_run_id,
query_text,
query_text_normalized,
query_type_snapshot_json,
query_category,
source_url,
priority_snapshot,
started_at,
finished_at,
status,
pages_fetched,
hits_count,
unique_ads_count,
api_hits_count,
error_message
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`).run(
id,
researchRunId,
queryText,
queryTextNormalized,
JSON.stringify(Array.isArray(queryTypeSnapshot) ? queryTypeSnapshot : []),
queryCategory || null,
sourceUrl || null,
Number.isFinite(prioritySnapshot) ? prioritySnapshot : 0,
now,
null,
'RUNNING',
0,
0,
0,
0,
null,
);
return getQueryRunById(db, id);
}
function updateQueryRun(db, queryRunId, patch) {
const updates = [];
const values = [];
const fields = {
finishedAt: 'finished_at',
status: 'status',
pagesFetched: 'pages_fetched',
hitsCount: 'hits_count',
uniqueAdsCount: 'unique_ads_count',
apiHitsCount: 'api_hits_count',
errorMessage: 'error_message',
};
for (const [key, column] of Object.entries(fields)) {
if (!Object.prototype.hasOwnProperty.call(patch, key)) continue;
if (key === 'status' && patch[key] != null && !QUERY_RUN_STATUSES.has(patch[key])) {
throw new Error(`Invalid query run status: ${patch[key]}`);
}
updates.push(`${column} = ?`);
values.push(patch[key]);
}
if (!updates.length) return;
values.push(queryRunId);
db.prepare(`
UPDATE meta_query_runs
SET ${updates.join(', ')}
WHERE id = ?
`).run(...values);
}
function getQueryRunById(db, queryRunId) {
const row = db.prepare(`
SELECT *
FROM meta_query_runs
WHERE id = ?
`).get(queryRunId);
return mapQueryRunRow(row);
}
function linkQueryRunVariants(db, queryRunId, variants) {
const insert = db.prepare(`
INSERT OR IGNORE INTO meta_query_run_variants (
id,
query_run_id,
query_variant_id,
query_plan_seed_id,
source_seed_id,
seed_text,
query_text_snapshot,
query_type_snapshot,
priority_snapshot
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`);
const tx = db.transaction(() => {
for (const variant of variants) {
insert.run(
crypto.randomUUID(),
queryRunId,
variant.queryVariantId,
variant.queryPlanSeedId,
variant.sourceSeedId,
variant.seedText,
variant.queryText,
variant.queryType,
variant.priority,
);
}
});
tx();
}
function toStringArray(value) {
if (Array.isArray(value)) {
return value.map(item => String(item ?? '').trim()).filter(Boolean);
}
if (typeof value === 'string' && value.trim()) {
return [value.trim()];
}
return [];
}
function parseInteger(value) {
if (value == null) return null;
const parsed = Number.parseInt(String(value), 10);
return Number.isFinite(parsed) ? parsed : null;
}
function extractBodyText(snapshot) {
if (!snapshot) return null;
if (typeof snapshot.body === 'string') {
const value = snapshot.body.trim();
return value || null;
}
if (snapshot.body && typeof snapshot.body.text === 'string') {
const value = snapshot.body.text.trim();
return value || null;
}
if (Array.isArray(snapshot.body)) {
const first = snapshot.body.find(item => typeof item === 'string' && item.trim());
return first ? first.trim() : null;
}
return null;
}
function extractTitle(snapshot) {
if (!snapshot) return null;
const candidates = [
snapshot.title,
snapshot.link_title,
snapshot.headline,
snapshot.link_description,
Array.isArray(snapshot.cards) && snapshot.cards.length ? snapshot.cards[0]?.title : null,
];
for (const candidate of candidates) {
if (typeof candidate === 'string' && candidate.trim()) return candidate.trim();
}
return null;
}
function extractDestinationUrl(snapshot) {
if (!snapshot) return null;
const candidates = [
snapshot.link_url,
Array.isArray(snapshot.cards) && snapshot.cards.length ? snapshot.cards[0]?.link_url : null,
];
for (const candidate of candidates) {
if (typeof candidate === 'string' && candidate.trim()) return candidate.trim();
}
return null;
}
function extractCollationId(rawAd, snapshot) {
const candidates = [
rawAd?.collation_id,
rawAd?.collationId,
snapshot?.collation_id,
snapshot?.collationId,
];
for (const candidate of candidates) {
if (candidate == null) continue;
const value = String(candidate).trim();
if (value) return value;
}
return null;
}
function getDomainAndPath(urlText) {
if (!urlText || typeof urlText !== 'string') {
return {
domain: null,
path: null,
};
}
try {
const parsed = new URL(urlText);
const path = parsed.pathname && parsed.pathname.length > 1 && parsed.pathname.endsWith('/')
? parsed.pathname.slice(0, -1)
: (parsed.pathname || '/');
return {
domain: parsed.hostname.toLowerCase(),
path,
};
} catch {
return {
domain: null,
path: null,
};
}
}
function extractAdFromApify(rawAd, observedAt) {
const adArchiveId = String(rawAd.ad_archive_id || rawAd.id || '').trim();
if (!adArchiveId) {
throw new Error('Record is missing ad_archive_id');
}
const snapshot = rawAd.snapshot && typeof rawAd.snapshot === 'object'
? rawAd.snapshot
: {};
const pageName = String(rawAd.page_name || snapshot.page_name || '').trim() || null;
const bodyText = extractBodyText(snapshot);
const title = extractTitle(snapshot);
const destinationUrl = extractDestinationUrl(snapshot);
const canonicalDestinationUrl = canonicalizeUrl(destinationUrl);
const urlParts = getDomainAndPath(canonicalDestinationUrl || destinationUrl);
const collationId = extractCollationId(rawAd, snapshot);
const isActive = typeof rawAd.is_active === 'boolean'
? rawAd.is_active
: String(rawAd.is_active || '').toLowerCase() === 'true';
const startDate = rawAd.start_date_formatted || rawAd.start_date || null;
const endDate = rawAd.end_date_formatted || rawAd.end_date || null;
const lifecycle = classifyAdLifecycle({
startDate,
endDate,
isActive,
now: new Date(observedAt),
});
const publisherPlatforms = toStringArray(rawAd.publisher_platform);
const pageCategories = toStringArray(snapshot.page_categories);
const imageUrls = Array.isArray(snapshot.images)
? snapshot.images.map(image => image?.original_image_url || image?.resized_image_url || image?.url || null).filter(Boolean)
: [];
const videoUrls = Array.isArray(snapshot.videos)
? snapshot.videos.map(video => video?.video_hd_url || video?.video_sd_url || video?.url || null).filter(Boolean)
: [];
const creativeBodies = bodyText ? [bodyText] : [];
const creativeTitles = title ? [title] : [];
const creativeDescriptions = toStringArray(snapshot.link_description);
return {
metaAdId: adArchiveId,
adArchiveId,
pageId: rawAd.page_id ? String(rawAd.page_id) : null,
pageName,
pageProfileUrl: snapshot.page_profile_uri || null,
bodyText,
title,
displayFormat: snapshot.display_format || null,
ctaType: snapshot.cta_type || null,
ctaText: snapshot.cta_text || null,
destinationUrl,
canonicalDestinationUrl,
destinationDomain: urlParts.domain,
destinationPath: urlParts.path,
collationId,
collationCount: parseInteger(rawAd.collation_count || snapshot.collation_count),
adLibraryUrl: rawAd.ad_library_url || rawAd.url || null,
isActive,
startDate,
endDate,
publisherPlatforms,
pageCategories,
pageLikeCount: parseInteger(snapshot.page_like_count),
adsCount: parseInteger(rawAd.ads_count),
runtimeDays: lifecycle.runtimeDays,
longevityCategory: lifecycle.longevityCategory,
lifecycleClass: mapLegacyLifecycle(lifecycle.longevityCategory),
lifecycleConfidence: lifecycle.lifecycleConfidence,
lifecycleReasons: lifecycle.lifecycleReasons,
createdAt: observedAt,
updatedAt: observedAt,
firstSeenAt: observedAt,
lastSeenAt: observedAt,
rawJson: JSON.stringify(rawAd),
creativeBodies,
creativeTitles,
creativeDescriptions,
imageUrls,
videoUrls,
};
}
function upsertMetaAd(db, model) {
const existing = db.prepare(`
SELECT meta_ad_id, first_seen_at
FROM meta_ads
WHERE ad_archive_id = ? OR meta_ad_id = ?
LIMIT 1
`).get(model.adArchiveId, model.metaAdId);
if (!existing) {
db.prepare(`
INSERT INTO meta_ads (
meta_ad_id,
ad_archive_id,
page_id,
page_name,
page_profile_url,
body_text,
title,
display_format,
cta_type,
cta_text,
destination_url,
canonical_destination_url,
destination_domain,
destination_path,
collation_id,
collation_count,
ad_library_url,
is_active,
start_date,
end_date,
publisher_platforms,
page_categories,
page_like_count,
ads_count,
runtime_days,
longevity_category,
relevance_score,
relevance_status,
rejection_reason,
raw_json,
created_at,
updated_at,
ad_delivery_start_time,
ad_delivery_stop_time,
ad_snapshot_url,
creative_bodies_json,
creative_link_titles_json,
creative_link_descriptions_json,
publisher_platforms_json,
languages_json,
eu_total_reach,
first_seen_at,
last_seen_at,
latest_raw_json,
lifecycle_class,
lifecycle_confidence,
lifecycle_reasons_json,
delivery_age_days
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`).run(
model.metaAdId,
model.adArchiveId,
model.pageId,
model.pageName,
model.pageProfileUrl,
model.bodyText,
model.title,
model.displayFormat,
model.ctaType,
model.ctaText,
model.destinationUrl,
model.canonicalDestinationUrl,
model.destinationDomain,
model.destinationPath,
model.collationId,
model.collationCount,
model.adLibraryUrl,
model.isActive ? 1 : 0,
model.startDate,
model.endDate,
JSON.stringify(model.publisherPlatforms),
JSON.stringify(model.pageCategories),
model.pageLikeCount,
model.adsCount,
model.runtimeDays,
model.longevityCategory,
null,
null,
null,
model.rawJson,
model.createdAt,
model.updatedAt,
model.startDate,
model.endDate,
model.adLibraryUrl,
JSON.stringify(model.creativeBodies),
JSON.stringify(model.creativeTitles),
JSON.stringify(model.creativeDescriptions),
JSON.stringify(model.publisherPlatforms),
'[]',
null,
model.firstSeenAt,
model.lastSeenAt,
model.rawJson,
model.lifecycleClass,
model.lifecycleConfidence,
JSON.stringify(model.lifecycleReasons),
model.runtimeDays,
);
return { metaAdId: model.metaAdId, inserted: true };
}
db.prepare(`
UPDATE meta_ads
SET ad_archive_id = ?,
page_id = ?,
page_name = ?,
page_profile_url = ?,
body_text = ?,
title = ?,
display_format = ?,
cta_type = ?,
cta_text = ?,
destination_url = ?,
canonical_destination_url = ?,
destination_domain = ?,
destination_path = ?,
collation_id = ?,
collation_count = ?,
ad_library_url = ?,
is_active = ?,
start_date = ?,
end_date = ?,
publisher_platforms = ?,
page_categories = ?,
page_like_count = ?,
ads_count = ?,
runtime_days = ?,
longevity_category = ?,
raw_json = ?,
updated_at = ?,
ad_delivery_start_time = ?,
ad_delivery_stop_time = ?,
ad_snapshot_url = ?,
creative_bodies_json = ?,
creative_link_titles_json = ?,
creative_link_descriptions_json = ?,
publisher_platforms_json = ?,
latest_raw_json = ?,
last_seen_at = ?,
lifecycle_class = ?,
lifecycle_confidence = ?,
lifecycle_reasons_json = ?,
delivery_age_days = ?
WHERE meta_ad_id = ?
`).run(
model.adArchiveId,
model.pageId,
model.pageName,
model.pageProfileUrl,
model.bodyText,
model.title,
model.displayFormat,
model.ctaType,
model.ctaText,
model.destinationUrl,
model.canonicalDestinationUrl,
model.destinationDomain,
model.destinationPath,
model.collationId,
model.collationCount,
model.adLibraryUrl,
model.isActive ? 1 : 0,
model.startDate,
model.endDate,
JSON.stringify(model.publisherPlatforms),
JSON.stringify(model.pageCategories),
model.pageLikeCount,
model.adsCount,
model.runtimeDays,
model.longevityCategory,
model.rawJson,
model.updatedAt,
model.startDate,
model.endDate,
model.adLibraryUrl,
JSON.stringify(model.creativeBodies),
JSON.stringify(model.creativeTitles),
JSON.stringify(model.creativeDescriptions),
JSON.stringify(model.publisherPlatforms),
model.rawJson,
model.lastSeenAt,
model.lifecycleClass,
model.lifecycleConfidence,
JSON.stringify(model.lifecycleReasons),
model.runtimeDays,
existing.meta_ad_id,
);
return { metaAdId: existing.meta_ad_id, inserted: false };
}
function ensureProjectAdRelevanceRows(db, {
projectId,
metaAdIds,
}) {
const uniqueMetaAdIds = [...new Set((metaAdIds || []).map(item => String(item || '').trim()).filter(Boolean))];
if (!uniqueMetaAdIds.length) {
return { ensuredCount: 0 };
}
const placeholders = uniqueMetaAdIds.map(() => '?').join(', ');
const rows = db.prepare(`
SELECT
meta_ad_id,
COALESCE(ad_archive_id, meta_ad_id) AS ad_archive_id
FROM meta_ads
WHERE meta_ad_id IN (${placeholders})
`).all(...uniqueMetaAdIds);
if (!rows.length) {
return { ensuredCount: 0 };
}
const now = new Date().toISOString();
const insert = db.prepare(`
INSERT OR IGNORE INTO project_ad_relevance (
id,
project_id,
meta_ad_id,
ad_archive_id,
relevance_status,
relevance_source,
details_status,
manual_override,
created_at,
updated_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`);
const tx = db.transaction(() => {
for (const row of rows) {
insert.run(
crypto.randomUUID(),
projectId,
row.meta_ad_id,
row.ad_archive_id,
'UNREVIEWED',
'MR04',
'NOT_FETCHED',
0,
now,
now,
);
}
});
tx();
return {
ensuredCount: rows.length,
};
}
function getProjectRelevancePendingCount(db, projectId) {
const row = db.prepare(`
SELECT COUNT(*) AS count_pending
FROM project_ad_relevance
WHERE project_id = ?
AND manual_override = 0
AND relevance_status = 'UNREVIEWED'
`).get(projectId);
return row?.count_pending || 0;
}
function getProjectAdsForRelevanceReview(db, projectId, {
includeResolved = false,
limit = 1500,
} = {}) {
const rows = db.prepare(`
SELECT
par.meta_ad_id,
par.ad_archive_id,
par.relevance_status,
par.relevance_source,
par.manual_override,
par.mr04_decision,
par.mr04_confidence,
par.mr04_reason_code,
par.mr04_reason,
par.mr04_filter_version,
par.mr04_checked_at,
par.details_status,
par.details_fetched_at,
par.details_provider,
par.details_error,
ma.page_id,
ma.page_name,
ma.page_profile_url,
ma.body_text,
ma.title,
ma.destination_url,
ma.ad_library_url,
ma.start_date,
ma.end_date,
ma.is_active,
ma.longevity_category,
ma.runtime_days,
ma.updated_at,
(
SELECT GROUP_CONCAT(DISTINCT maqm.query_text)
FROM meta_ad_query_matches maqm
JOIN meta_research_runs mrr ON mrr.id = maqm.research_run_id
WHERE maqm.meta_ad_id = ma.meta_ad_id
AND mrr.project_id = par.project_id
) AS matched_queries,
(
SELECT GROUP_CONCAT(DISTINCT mqrv.seed_text)
FROM meta_ad_query_matches maqm
JOIN meta_research_runs mrr ON mrr.id = maqm.research_run_id
LEFT JOIN meta_query_run_variants mqrv ON mqrv.query_run_id = maqm.query_run_id
WHERE maqm.meta_ad_id = ma.meta_ad_id
AND mrr.project_id = par.project_id
) AS matched_seeds
FROM project_ad_relevance par
JOIN meta_ads ma ON ma.meta_ad_id = par.meta_ad_id
WHERE par.project_id = ?
AND (
? = 1
OR (par.manual_override = 0 AND par.relevance_status = 'UNREVIEWED')
)
ORDER BY COALESCE(ma.updated_at, ma.last_seen_at, ma.first_seen_at) DESC
LIMIT ?
`).all(projectId, includeResolved ? 1 : 0, Math.max(1, Number(limit) || 1500));
return rows.map(row => ({
metaAdId: row.meta_ad_id,
adArchiveId: row.ad_archive_id,
relevanceStatus: normalizeRelevanceStatus(row.relevance_status),
relevanceSource: row.relevance_source || 'MR04',
manualOverride: row.manual_override === 1,
mr04Decision: row.mr04_decision || null,
mr04Confidence: Number.isFinite(Number(row.mr04_confidence)) ? Number(row.mr04_confidence) : null,
mr04ReasonCode: row.mr04_reason_code || null,
mr04Reason: row.mr04_reason || null,
mr04FilterVersion: row.mr04_filter_version || null,
mr04CheckedAt: row.mr04_checked_at || null,
detailsStatus: normalizeDetailsStatus(row.details_status),
detailsFetchedAt: row.details_fetched_at || null,
detailsProvider: row.details_provider || null,
detailsError: row.details_error || null,
pageId: row.page_id,
pageName: row.page_name,
pageProfileUrl: row.page_profile_url,
bodyText: row.body_text,
title: row.title,
destinationUrl: row.destination_url,
adLibraryUrl: row.ad_library_url,
startDate: row.start_date,
endDate: row.end_date,
isActive: row.is_active === 1,
longevityCategory: row.longevity_category,
runtimeDays: row.runtime_days,
matchedQueries: row.matched_queries
? row.matched_queries.split(',').map(item => item.trim()).filter(Boolean)
: [],
matchedSeeds: row.matched_seeds
? row.matched_seeds.split(',').map(item => item.trim()).filter(Boolean)
: [],
}));
}
function applyMr04Decisions(db, {
projectId,
decisions,
filterVersion = 'mr04',
}) {
const selectRow = db.prepare(`
SELECT *
FROM project_ad_relevance
WHERE project_id = ? AND ad_archive_id = ?
LIMIT 1
`);
const updateRow = db.prepare(`
UPDATE project_ad_relevance
SET relevance_status = ?,
relevance_source = 'MR04',
mr04_decision = ?,
mr04_confidence = ?,
mr04_reason_code = ?,
mr04_reason = ?,
mr04_filter_version = ?,
mr04_checked_at = ?,
updated_at = ?
WHERE id = ?
`);
const stats = {
processed: 0,
keep: 0,
potential: 0,
reject: 0,
skippedManual: 0,
missing: 0,
invalid: 0,
};
const tx = db.transaction(() => {
for (const decisionItem of decisions || []) {
const adArchiveId = String(decisionItem.adArchiveId || '').trim();
const decision = String(decisionItem.decision || '').trim().toUpperCase();
if (!adArchiveId || !MR04_DECISIONS.has(decision)) {
stats.invalid += 1;
continue;
}
const row = selectRow.get(projectId, adArchiveId);
if (!row) {
stats.missing += 1;
continue;
}
if (row.manual_override === 1) {
stats.skippedManual += 1;
continue;
}
const mappedStatus = mapMr04DecisionToStatus(decision);
const now = new Date().toISOString();
const confidenceRaw = Number(decisionItem.confidence);
const confidence = Number.isFinite(confidenceRaw)
? Math.max(0, Math.min(100, Math.round(confidenceRaw)))
: null;
updateRow.run(
mappedStatus,
decision,
confidence,
decisionItem.reasonCode ? String(decisionItem.reasonCode) : null,
decisionItem.reason ? String(decisionItem.reason) : null,
String(filterVersion || 'mr04'),
now,
now,
row.id,
);
stats.processed += 1;
if (mappedStatus === 'KEEP') stats.keep += 1;
if (mappedStatus === 'POTENTIAL') stats.potential += 1;
if (mappedStatus === 'REJECT') stats.reject += 1;
}
});
tx();
return stats;
}
function getProjectAdRelevanceRow(db, projectId, adArchiveId) {
return db.prepare(`
SELECT *
FROM project_ad_relevance
WHERE project_id = ? AND ad_archive_id = ?
LIMIT 1
`).get(projectId, adArchiveId);
}
function mapProjectAdRelevanceRow(row) {
if (!row) return null;
return {
id: row.id,
projectId: row.project_id,
metaAdId: row.meta_ad_id,
adArchiveId: row.ad_archive_id,
relevanceStatus: normalizeRelevanceStatus(row.relevance_status),
relevanceSource: row.relevance_source || 'MR04',
manualOverride: row.manual_override === 1,
manualOverrideStatus: row.manual_override_status || null,
manualOverrideAt: row.manual_override_at || null,
mr04Decision: row.mr04_decision || null,
mr04Confidence: row.mr04_confidence,
mr04ReasonCode: row.mr04_reason_code || null,
mr04Reason: row.mr04_reason || null,
mr04FilterVersion: row.mr04_filter_version || null,
mr04CheckedAt: row.mr04_checked_at || null,
detailsStatus: normalizeDetailsStatus(row.details_status),
detailsFetchedAt: row.details_fetched_at || null,
detailsProvider: row.details_provider || null,
detailsError: row.details_error || null,
createdAt: row.created_at,
updatedAt: row.updated_at,
};
}
function setProjectAdManualRelevance(db, {
projectId,
adArchiveId,
relevanceStatus,
}) {
const normalizedStatus = String(relevanceStatus || '').trim().toUpperCase();
if (!['KEEP', 'POTENTIAL', 'REJECT'].includes(normalizedStatus)) {
const error = new Error('Invalid relevance status. Allowed: KEEP, POTENTIAL, REJECT');
error.code = 'INVALID_RELEVANCE_STATUS';
throw error;
}
const now = new Date().toISOString();
const normalizedArchiveId = String(adArchiveId || '').trim();
if (!normalizedArchiveId) {
const error = new Error('adArchiveId is required');
error.code = 'INVALID_AD_ARCHIVE_ID';
throw error;
}
const tx = db.transaction(() => {
const existing = getProjectAdRelevanceRow(db, projectId, normalizedArchiveId);
if (!existing) {
const ad = db.prepare(`
SELECT meta_ad_id, COALESCE(ad_archive_id, meta_ad_id) AS ad_archive_id
FROM meta_ads
WHERE COALESCE(ad_archive_id, meta_ad_id) = ?
LIMIT 1
`).get(normalizedArchiveId);
if (!ad) {
const error = new Error('Ad not found in meta_ads');
error.code = 'AD_NOT_FOUND';
throw error;
}
db.prepare(`
INSERT INTO project_ad_relevance (
id,
project_id,
meta_ad_id,
ad_archive_id,
relevance_status,
relevance_source,
manual_override,
manual_override_status,
manual_override_at,
details_status,
created_at,
updated_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`).run(
crypto.randomUUID(),
projectId,
ad.meta_ad_id,
ad.ad_archive_id,
normalizedStatus,
'MANUAL',
1,
normalizedStatus,
now,
'NOT_FETCHED',
now,
now,
);
} else {
db.prepare(`
UPDATE project_ad_relevance
SET relevance_status = ?,
relevance_source = 'MANUAL',
manual_override = 1,
manual_override_status = ?,
manual_override_at = ?,
updated_at = ?
WHERE id = ?
`).run(
normalizedStatus,
normalizedStatus,
now,
now,
existing.id,
);
}
});
tx();
const updated = getProjectAdRelevanceRow(db, projectId, normalizedArchiveId);
return mapProjectAdRelevanceRow(updated);
}
function restoreProjectAdRelevanceToMr04(db, {
projectId,
adArchiveId,
}) {
const normalizedArchiveId = String(adArchiveId || '').trim();
const existing = getProjectAdRelevanceRow(db, projectId, normalizedArchiveId);
if (!existing) {
const error = new Error('Relevance row not found');
error.code = 'NOT_FOUND';
throw error;
}
const mappedStatus = mapMr04DecisionToStatus(existing.mr04_decision);
const now = new Date().toISOString();
db.prepare(`
UPDATE project_ad_relevance
SET relevance_status = ?,
relevance_source = 'MR04',
manual_override = 0,
manual_override_status = NULL,
manual_override_at = NULL,
updated_at = ?
WHERE id = ?
`).run(
mappedStatus,
now,
existing.id,
);
const updated = getProjectAdRelevanceRow(db, projectId, normalizedArchiveId);
return mapProjectAdRelevanceRow(updated);
}
function getProjectAdsEligibleForDetails(db, projectId, {
statuses = ['KEEP', 'POTENTIAL'],
includeFailed = true,
limit = 2000,
adArchiveIds = [],
} = {}) {
const normalizedStatuses = [...new Set((statuses || []).map(status => String(status || '').trim().toUpperCase()).filter(status => ['KEEP', 'POTENTIAL', 'REJECT', 'UNREVIEWED'].includes(status)))];