Logs searchable by date, user, amount, status

An audit log that exists but cannot be queried efficiently is useless during a regulatory examination or a fraud investigation. When a regulator asks for all transactions by a specific user between two dates above a given amount, your team needs to produce that result in minutes, not hours of full-table scans. NIST 800-53 AU-6 requires organizations to review and analyze audit records for indications of inappropriate activity; AU-3 specifies that records must contain sufficient detail to support investigation. PCI-DSS 4.0 Req-10.4 requires that audit logs are reviewed regularly. FINRA Rule 4511 requires that books and records be available for examination on demand. Without indexes on user_id, timestamp, status, and amount, even modest log tables become unusable for time-sensitive regulatory responses, and full-table scans can lock the database and disrupt live transaction processing.

Add composite and single-column indexes in a migration under db/migrations/ and expose a filtered query endpoint at src/app/api/admin/audit-logs/route.ts:

CREATE INDEX idx_txlog_user     ON transaction_logs(user_id);
CREATE INDEX idx_txlog_ts       ON transaction_logs(timestamp DESC);
CREATE INDEX idx_txlog_status   ON transaction_logs(status);
CREATE INDEX idx_txlog_user_ts  ON transaction_logs(user_id, timestamp DESC);
CREATE INDEX idx_txlog_optype   ON transaction_logs(operation_type);

Then in the compliance route, accept all four required filter dimensions:

export async function GET(req: Request) {
  const { userId, startDate, endDate, status, minAmount, maxAmount } =
    Object.fromEntries(new URL(req.url).searchParams);
  const rows = await db('transaction_logs')
    .where(q => {
      if (userId)    q.where('user_id', userId);
      if (startDate) q.where('timestamp', '>=', startDate);
      if (endDate)   q.where('timestamp', '<=', endDate);
      if (status)    q.where('status', status);
      if (minAmount) q.where('amount', '>=', parseFloat(minAmount));
      if (maxAmount) q.where('amount', '<=', parseFloat(maxAmount));
    }).limit(10_000);
  return Response.json(rows);
}