memind/scripts/audit-conversation-packages.mjs

#!/usr/bin/env node
import mysql from 'mysql2/promise';
import {
  auditConversationPackages,
} from '../mindspace-conversation-package-audit.mjs';
import {
  loadMemindEnvFiles,
} from './memind-runtime-profile.mjs';

function usage() {
  return [
    'Usage: node scripts/audit-conversation-packages.mjs [--user-id <id>] [--session-id <id>] [--limit <n>] [--repair] [--json]',
    '',
    'Audits recent conversation packages for missing/invalid artifact references.',
    'Default mode is read-only. --repair only inserts missing public_html artifacts',
    'when a generated_file artifact already points at a public/*.html h5_asset.',
  ].join('\n');
}

function parseArgs(argv) {
  const args = {
    userId: '',
    sessionId: '',
    limit: 100,
    repair: false,
    json: false,
  };
  for (let i = 2; i < argv.length; i += 1) {
    const arg = argv[i];
    if (arg === '--user-id' && argv[i + 1]) {
      args.userId = argv[++i];
    } else if (arg === '--session-id' && argv[i + 1]) {
      args.sessionId = argv[++i];
    } else if (arg === '--limit' && argv[i + 1]) {
      args.limit = Math.max(1, Math.min(1000, Number(argv[++i]) || 100));
    } else if (arg === '--repair') {
      args.repair = true;
    } else if (arg === '--json') {
      args.json = true;
    } else if (arg === '--help' || arg === '-h') {
      console.log(usage());
      process.exit(0);
    } else {
      console.error(`Unknown argument: ${arg}`);
      console.error(usage());
      process.exit(2);
    }
  }
  return args;
}

function createPoolFromEnv() {
  loadMemindEnvFiles(process.cwd());
  const poolOptions = { connectionLimit: 3 };
  if (process.env.DATABASE_URL) {
    return mysql.createPool({
      uri: process.env.DATABASE_URL,
      ...poolOptions,
    });
  }
  if (!process.env.MYSQL_HOST && !process.env.MYSQL_DATABASE) {
    throw new Error(
      'conversation package audit requires DATABASE_URL or MYSQL_* configuration',
    );
  }
  return mysql.createPool({
    host: process.env.MYSQL_HOST ?? 'localhost',
    port: Number(process.env.MYSQL_PORT ?? 3306),
    user: process.env.MYSQL_USER ?? 'boot',
    password: process.env.MYSQL_PASSWORD ?? '',
    database: process.env.MYSQL_DATABASE ?? 'tkmind',
    ...poolOptions,
  });
}

function whereClause(args) {
  const clauses = ['p.status <> ?'];
  const values = ['deleted'];
  if (args.userId) {
    clauses.push('p.user_id = ?');
    values.push(args.userId);
  }
  if (args.sessionId) {
    clauses.push('p.session_id = ?');
    values.push(args.sessionId);
  }
  return {
    sql: clauses.length ? `WHERE ${clauses.join(' AND ')}` : '',
    values,
  };
}

async function loadAuditRows(pool, args) {
  const where = whereClause(args);
  const [packages] = await pool.query(
    `SELECT p.id, p.user_id, p.session_id, p.title, p.status,
            p.storage_prefix, p.manifest_asset_id, p.created_at, p.updated_at
     FROM h5_conversation_packages p
     ${where.sql}
     ORDER BY p.updated_at DESC
     LIMIT ?`,
    [...where.values, args.limit],
  );
  if (packages.length === 0) {
    return { packages: [], artifacts: [], assets: [] };
  }

  const packageIds = packages.map((item) => item.id);
  const placeholders = packageIds.map(() => '?').join(',');
  const [artifacts] = await pool.query(
    `SELECT ca.id, ca.package_id, ca.asset_id, ca.page_id,
            ca.publication_id, ca.agent_run_id, ca.message_id, ca.role,
            ca.artifact_kind, ca.display_name, ca.mime_type,
            ca.size_bytes, ca.storage_key, ca.canonical_url,
            ca.sort_order, ca.created_at
     FROM h5_conversation_artifacts ca
     WHERE ca.package_id IN (${placeholders})
     ORDER BY ca.package_id ASC, ca.sort_order ASC, ca.created_at ASC`,
    packageIds,
  );

  const assetIds = [
    ...new Set(
      artifacts
        .map((item) => item.asset_id)
        .filter(Boolean),
    ),
  ];
  let assets = [];
  if (assetIds.length > 0) {
    const assetPlaceholders = assetIds.map(() => '?').join(',');
    [assets] = await pool.query(
      `SELECT a.id, a.user_id, a.display_name, a.workspace_relative_path,
              a.asset_type, a.mime_type, a.size_bytes, a.status,
              a.visibility, a.updated_at
       FROM h5_assets a
       WHERE a.id IN (${assetPlaceholders})`,
      assetIds,
    );
  }
  return { packages, artifacts, assets };
}

async function applyRepairs(pool, repairs) {
  const applied = [];
  for (const repair of repairs) {
    await pool.query(
      `INSERT INTO h5_conversation_artifacts
        (id, package_id, artifact_kind, role, asset_id, page_id, publication_id,
         agent_run_id, message_id, display_name, mime_type, size_bytes,
         storage_key, canonical_url, sort_order, created_at)
       VALUES (?, ?, 'public_html', 'assistant', NULL, NULL, NULL,
         NULL, NULL, ?, ?, ?, NULL, ?, ?, ?)
       ON DUPLICATE KEY UPDATE
         display_name = VALUES(display_name),
         mime_type = VALUES(mime_type),
         size_bytes = VALUES(size_bytes),
         canonical_url = VALUES(canonical_url),
         sort_order = VALUES(sort_order)`,
      [
        repair.artifactId,
        repair.packageId,
        repair.displayName,
        repair.mimeType,
        repair.sizeBytes,
        repair.canonicalUrl,
        Math.round(Number(repair.sortOrder) || Date.now()),
        Date.now(),
      ],
    );
    applied.push(repair);
  }
  return applied;
}

function printTextReport(result, { applied = [] } = {}) {
  console.log(
    `conversation package audit: ${result.ok ? 'ok' : 'issues found'}`,
  );
  console.log(JSON.stringify(result.summary, null, 2));
  if (result.issues.length > 0) {
    for (const item of result.issues) {
      console.log(
        `- ${item.code}: ${item.message}${
          item.packageId ? ` [package=${item.packageId}]` : ''
        }`,
      );
    }
  }
  if (applied.length > 0) {
    console.log(`applied repairs: ${applied.length}`);
    for (const item of applied) {
      console.log(
        `- inserted ${item.artifactId} for ${item.packageId} ${item.relativePath}`,
      );
    }
  }
}

const args = parseArgs(process.argv);
const pool = createPoolFromEnv();
try {
  const rows = await loadAuditRows(pool, args);
  const result = auditConversationPackages({
    ...rows,
    publicBaseUrl:
      process.env.H5_PUBLIC_BASE_URL ?? 'http://127.0.0.1:5173',
  });
  const applied = args.repair
    ? await applyRepairs(pool, result.repairs)
    : [];
  if (args.json) {
    console.log(JSON.stringify({ ...result, applied }, null, 2));
  } else {
    printTextReport(result, { applied });
  }
  process.exit(result.ok || args.repair ? 0 : 1);
} finally {
  await pool.end();
}