Andres Resiri
350 lines
12 KiB
TypeScript
350 lines
12 KiB
TypeScript
/**
|
|
* Phase 1 — Metric Ground-Truth Audit
|
|
* ───────────────────────────────────────
|
|
*
|
|
* Run against a live DB to capture the *actual* counts per metric
|
|
* table for a single brand. Output is written to stdout AND to
|
|
* scripts/audit-results.md so the ground truth lives alongside the
|
|
* code-side audit.
|
|
*
|
|
* Usage:
|
|
* DATABASE_URL=postgres://... npx tsx scripts/audit-phase1.ts
|
|
* # or pick a different brand
|
|
* BRAND_NAME="Modern Heart" npx tsx scripts/audit-phase1.ts
|
|
*
|
|
* This script is READ-ONLY — it only runs aggregate() / groupBy()
|
|
* / count() queries. Safe to run in production.
|
|
*
|
|
* Scoping note: some metric tables are keyed by brandId directly
|
|
* (SiteConversion, SiteEvent, RealUserMetric, AiInteraction,
|
|
* AiUsageLog, PlatformEvent, MetricSnapshot, GscPage, TechnicalAudit)
|
|
* while TrackedEvent and TrackedSession are scoped via TrackedSite.
|
|
* The script resolves the brand's TrackedSite once and uses
|
|
* trackedSiteId for those two tables.
|
|
*/
|
|
|
|
import { PrismaClient } from "@prisma/client";
|
|
import { writeFileSync, mkdirSync } from "fs";
|
|
import { dirname } from "path";
|
|
|
|
const prisma = new PrismaClient();
|
|
|
|
// Accumulator for the markdown report. Every log() call appends to
|
|
// both stdout and this buffer; the buffer is flushed to
|
|
// scripts/audit-results.md at the end of the run.
|
|
const report: string[] = [];
|
|
function log(line: string) {
|
|
log(line);
|
|
report.push(line);
|
|
}
|
|
|
|
// JSON.stringify replacer that handles BigInt + Date cleanly.
|
|
function replacer(_key: string, value: unknown): unknown {
|
|
if (typeof value === "bigint") return value.toString();
|
|
if (value instanceof Date) return value.toISOString();
|
|
return value;
|
|
}
|
|
|
|
function j(v: unknown): string {
|
|
return JSON.stringify(v, replacer);
|
|
}
|
|
|
|
async function main() {
|
|
const brandNeedle = process.env.BRAND_NAME ?? "Modern Heart";
|
|
|
|
const brand = await prisma.brand.findFirst({
|
|
where: { name: { contains: brandNeedle, mode: "insensitive" } },
|
|
select: { id: true, name: true, domain: true, createdAt: true },
|
|
});
|
|
if (!brand) {
|
|
console.error(`No brand matching "${brandNeedle}" found.`);
|
|
process.exit(1);
|
|
}
|
|
|
|
const site = await prisma.trackedSite.findUnique({
|
|
where: { brandId: brand.id },
|
|
select: { id: true, status: true, firstEventAt: true, lastEventAt: true, createdAt: true },
|
|
});
|
|
|
|
log("=== Brand ===");
|
|
log(j({ brand }));
|
|
log("=== TrackedSite ===");
|
|
log(j({ site }));
|
|
|
|
// ─── Direct-brandId tables ──────────────────────────────────
|
|
//
|
|
// Each aggregate runs independently so a single failing query
|
|
// doesn't blackhole the rest of the audit. Empty / errored rows
|
|
// render as ERROR in the output.
|
|
const brandTables: Array<{ name: string; run: () => Promise<unknown> }> = [
|
|
{
|
|
name: "SiteConversion",
|
|
run: () => prisma.siteConversion.aggregate({
|
|
where: { brandId: brand.id },
|
|
_count: true,
|
|
_min: { timestamp: true },
|
|
_max: { timestamp: true },
|
|
}),
|
|
},
|
|
{
|
|
name: "SiteEvent",
|
|
run: () => prisma.siteEvent.aggregate({
|
|
where: { brandId: brand.id },
|
|
_count: true,
|
|
_min: { timestamp: true },
|
|
_max: { timestamp: true },
|
|
}),
|
|
},
|
|
{
|
|
name: "RealUserMetric",
|
|
run: () => prisma.realUserMetric.aggregate({
|
|
where: { brandId: brand.id },
|
|
_count: true,
|
|
_min: { timestamp: true },
|
|
_max: { timestamp: true },
|
|
}),
|
|
},
|
|
{
|
|
name: "AiInteraction",
|
|
run: () => prisma.aiInteraction.aggregate({
|
|
where: { brandId: brand.id },
|
|
_count: true,
|
|
_min: { timestamp: true },
|
|
_max: { timestamp: true },
|
|
}),
|
|
},
|
|
{
|
|
name: "AiUsageLog",
|
|
run: () => prisma.aiUsageLog.aggregate({
|
|
where: { brandId: brand.id },
|
|
_count: true,
|
|
_min: { timestamp: true },
|
|
_max: { timestamp: true },
|
|
}),
|
|
},
|
|
{
|
|
name: "PlatformEvent",
|
|
run: () => prisma.platformEvent.aggregate({
|
|
where: { brandId: brand.id },
|
|
_count: true,
|
|
_min: { timestamp: true },
|
|
_max: { timestamp: true },
|
|
}),
|
|
},
|
|
{
|
|
name: "MetricSnapshot",
|
|
run: () => prisma.metricSnapshot.aggregate({
|
|
where: { brandId: brand.id },
|
|
_count: true,
|
|
_min: { date: true },
|
|
_max: { date: true },
|
|
}),
|
|
},
|
|
{
|
|
name: "GscPage",
|
|
run: () => prisma.gscPage.aggregate({
|
|
where: { brandId: brand.id },
|
|
_count: true,
|
|
_min: { date: true },
|
|
_max: { date: true },
|
|
}),
|
|
},
|
|
{
|
|
name: "TechnicalAudit",
|
|
run: () => prisma.technicalAudit.aggregate({
|
|
where: { brandId: brand.id },
|
|
_count: true,
|
|
_min: { createdAt: true },
|
|
_max: { createdAt: true },
|
|
}),
|
|
},
|
|
];
|
|
|
|
log("\n=== Brand-scoped tables ===");
|
|
for (const t of brandTables) {
|
|
try {
|
|
const result = await t.run();
|
|
log(`${t.name}: ${j(result)}`);
|
|
} catch (err) {
|
|
log(`${t.name}: ERROR ${err instanceof Error ? err.message : String(err)}`);
|
|
}
|
|
}
|
|
|
|
// ─── TrackedSite-scoped tables ──────────────────────────────
|
|
log("\n=== TrackedSite-scoped tables ===");
|
|
if (!site) {
|
|
log("TrackedEvent / TrackedSession: SKIPPED (no TrackedSite for brand)");
|
|
} else {
|
|
try {
|
|
const te = await prisma.trackedEvent.aggregate({
|
|
where: { trackedSiteId: site.id },
|
|
_count: true,
|
|
_min: { timestamp: true },
|
|
_max: { timestamp: true },
|
|
});
|
|
log(`TrackedEvent: ${j(te)}`);
|
|
} catch (err) {
|
|
log(`TrackedEvent: ERROR ${err instanceof Error ? err.message : String(err)}`);
|
|
}
|
|
try {
|
|
const ts = await prisma.trackedSession.aggregate({
|
|
where: { trackedSiteId: site.id },
|
|
_count: true,
|
|
_min: { startedAt: true },
|
|
_max: { startedAt: true },
|
|
});
|
|
log(`TrackedSession: ${j(ts)}`);
|
|
} catch (err) {
|
|
log(`TrackedSession: ERROR ${err instanceof Error ? err.message : String(err)}`);
|
|
}
|
|
}
|
|
|
|
// ─── Type breakdowns (the numbers the UI actually renders) ──
|
|
log("\n=== Distinct event-type counts ===");
|
|
|
|
if (site) {
|
|
try {
|
|
const trackedTypes = await prisma.trackedEvent.groupBy({
|
|
by: ["eventType"],
|
|
where: { trackedSiteId: site.id },
|
|
_count: true,
|
|
});
|
|
trackedTypes.sort((a, b) => (b._count as unknown as number) - (a._count as unknown as number));
|
|
// Top 30 only — longer lists hide the signal.
|
|
log(`TrackedEvent types (top 30): ${j(trackedTypes.slice(0, 30))}`);
|
|
log(`TrackedEvent distinct type count: ${trackedTypes.length}`);
|
|
} catch (err) {
|
|
log(`TrackedEvent types: ERROR ${err instanceof Error ? err.message : String(err)}`);
|
|
}
|
|
}
|
|
|
|
try {
|
|
const siteConvTypes = await prisma.siteConversion.groupBy({
|
|
by: ["conversionType"],
|
|
where: { brandId: brand.id },
|
|
_count: true,
|
|
});
|
|
siteConvTypes.sort((a, b) => (b._count as unknown as number) - (a._count as unknown as number));
|
|
// All SiteConversion types — there are rarely more than ~20
|
|
// distinct values but capped at 30 for safety.
|
|
log(`SiteConversion types (top 30): ${j(siteConvTypes.slice(0, 30))}`);
|
|
log(`SiteConversion distinct type count: ${siteConvTypes.length}`);
|
|
} catch (err) {
|
|
log(`SiteConversion types: ERROR ${err instanceof Error ? err.message : String(err)}`);
|
|
}
|
|
|
|
try {
|
|
const siteEventTypes = await prisma.siteEvent.groupBy({
|
|
by: ["eventType"],
|
|
where: { brandId: brand.id },
|
|
_count: true,
|
|
});
|
|
siteEventTypes.sort((a, b) => (b._count as unknown as number) - (a._count as unknown as number));
|
|
log(`SiteEvent types (top 20): ${j(siteEventTypes.slice(0, 20))}`);
|
|
log(`SiteEvent distinct type count: ${siteEventTypes.length}`);
|
|
} catch (err) {
|
|
log(`SiteEvent types: ERROR ${err instanceof Error ? err.message : String(err)}`);
|
|
}
|
|
|
|
// ─── 30-day windowed ground truth ──────────────────────────
|
|
// The UI's default range is 30 days. Logging both all-time
|
|
// and 30-day counts side by side so "why does the dashboard
|
|
// show 528 conversions when the DB has 3000+?" is obvious.
|
|
log("\n=== 30-day windowed counts ===");
|
|
const since30d = new Date(Date.now() - 30 * 86_400_000);
|
|
|
|
try {
|
|
const conv30 = await prisma.siteConversion.count({
|
|
where: { brandId: brand.id, timestamp: { gte: since30d } },
|
|
});
|
|
log(`SiteConversion (30d): ${conv30}`);
|
|
} catch (err) {
|
|
log(`SiteConversion (30d): ERROR ${err instanceof Error ? err.message : String(err)}`);
|
|
}
|
|
|
|
if (site) {
|
|
try {
|
|
const te30 = await prisma.trackedEvent.count({
|
|
where: { trackedSiteId: site.id, timestamp: { gte: since30d } },
|
|
});
|
|
log(`TrackedEvent (30d): ${te30}`);
|
|
const sessStart30 = await prisma.trackedEvent.count({
|
|
where: { trackedSiteId: site.id, eventType: "session_start", timestamp: { gte: since30d } },
|
|
});
|
|
log(`TrackedEvent session_start (30d): ${sessStart30}`);
|
|
const pv30 = await prisma.trackedEvent.count({
|
|
where: { trackedSiteId: site.id, eventType: "page_view", timestamp: { gte: since30d } },
|
|
});
|
|
log(`TrackedEvent page_view (30d): ${pv30}`);
|
|
const ts30 = await prisma.trackedSession.count({
|
|
where: { trackedSiteId: site.id, startedAt: { gte: since30d } },
|
|
});
|
|
log(`TrackedSession (30d): ${ts30}`);
|
|
} catch (err) {
|
|
log(`TrackedEvent/TrackedSession (30d): ERROR ${err instanceof Error ? err.message : String(err)}`);
|
|
}
|
|
}
|
|
|
|
try {
|
|
const gsc30 = await prisma.gscPage.aggregate({
|
|
where: { brandId: brand.id, date: { gte: since30d } },
|
|
_sum: { clicks: true, impressions: true },
|
|
_avg: { position: true, ctr: true },
|
|
});
|
|
log(`GscPage (30d) aggregate: ${j(gsc30)}`);
|
|
} catch (err) {
|
|
log(`GscPage (30d): ERROR ${err instanceof Error ? err.message : String(err)}`);
|
|
}
|
|
|
|
try {
|
|
const ai30 = await prisma.aiUsageLog.aggregate({
|
|
where: { brandId: brand.id, timestamp: { gte: since30d } },
|
|
_count: true,
|
|
_sum: { estimatedCost: true, inputTokens: true, outputTokens: true },
|
|
});
|
|
log(`AiUsageLog (30d): ${j(ai30)}`);
|
|
} catch (err) {
|
|
log(`AiUsageLog (30d): ERROR ${err instanceof Error ? err.message : String(err)}`);
|
|
}
|
|
|
|
log("\n=== Audit complete ===");
|
|
|
|
// Flush the full run to scripts/audit-results.md so the result is
|
|
// checked in alongside the code-side audit. Wrapping in ``` keeps
|
|
// the markdown file renderable even for large JSON payloads.
|
|
const outPath = "scripts/audit-results.md";
|
|
const now = new Date().toISOString();
|
|
const body = [
|
|
"# Phase 1 — Metric Ground-Truth Audit",
|
|
"",
|
|
`**Generated:** ${now}`,
|
|
`**Brand:** ${brand.name} (${brand.id})`,
|
|
`**Domain:** ${brand.domain}`,
|
|
"",
|
|
"This file is produced by `scripts/audit-phase1.ts` and captures",
|
|
"the raw per-table counts + event-type breakdowns for a single",
|
|
"brand. These are the **ground-truth numbers** the UI surfaces",
|
|
"must agree with.",
|
|
"",
|
|
"Re-run with `npx tsx scripts/audit-phase1.ts` — the file is",
|
|
"overwritten in place.",
|
|
"",
|
|
"## Full output",
|
|
"",
|
|
"```text",
|
|
...report,
|
|
"```",
|
|
"",
|
|
].join("\n");
|
|
mkdirSync(dirname(outPath), { recursive: true });
|
|
writeFileSync(outPath, body, "utf8");
|
|
console.log(`\n[audit-phase1] wrote ${outPath} (${report.length} lines)`);
|
|
}
|
|
|
|
main()
|
|
.catch((err) => {
|
|
console.error("[audit-phase1] FATAL", err);
|
|
process.exit(1);
|
|
})
|
|
.finally(() => prisma.$disconnect());
|