scripts/backfill-report-index.ts

Linked repository file from this build.

#!/usr/bin/env bun
import { reviewKey } from "@/features/reviews/review-key";
import "dotenv/config";
import { resolveDatabaseTarget } from "./db/target";
import { and, asc, eq, gt } from "drizzle-orm";
import { transmittalEligible } from "@/features/reviews/downstream";
import { reportRowFromReview } from "@/features/reports/report-input";
import { bindStoredRules } from "@/features/reviews/review-store";
import { getDb } from "@/lib/db";
import { reviewIndex, reviewSnapshots } from "@/lib/db/schema";

// Rebuilds every current report projection. Run it after a deliberate change to eligibility rules.
const target = resolveDatabaseTarget(process.env.QC_DB_TARGET);
process.env.DATABASE_URL = target.url;
console.log(`Database: ${target.target} — ${target.label} (${target.variable})`);
const db = getDb();
let after: string | undefined;
let processed = 0;
while (true) {
  const rows = await db
    .select({
      review: reviewSnapshots.review,
      version: reviewSnapshots.version,
      actor: reviewSnapshots.actor,
      requestId: reviewSnapshots.requestId,
      savedAt: reviewSnapshots.savedAt,
    })
    .from(reviewIndex)
    .innerJoin(
      reviewSnapshots,
      and(
        eq(reviewIndex.reviewKey, reviewSnapshots.reviewKey),
        eq(reviewIndex.version, reviewSnapshots.version),
      ),
    )
    .where(after ? gt(reviewIndex.reviewKey, after) : undefined)
    .orderBy(asc(reviewIndex.reviewKey))
    .limit(100);
  if (!rows.length) break;
  const updates = rows.map((stored) => {
    const review = bindStoredRules(stored)!.review;
    const { eligible, variances } = reportRowFromReview(review);
    return db
      .update(reviewIndex)
      .set({ report: { eligible, variances, transmittalEligible: transmittalEligible(review) } })
      .where(
        and(
          eq(reviewIndex.reviewKey, reviewKey(stored.review)),
          // Never overwrite a projection saved concurrently for a newer version.
          eq(reviewIndex.version, stored.version),
        ),
      );
  });
  await db.batch([updates[0]!, ...updates.slice(1)]);
  after = reviewKey(rows.at(-1)!.review);
  processed += rows.length;
}
console.log(`Processed ${processed} report projections.`);
process.exit(0);