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);
}

• ID: finserv-audit-trail.retention-compliance.searchable-logs

• Severity: medium

• What to look for: Count all database indexes on the audit log table. List all indexed columns and quote the actual CREATE INDEX statements found. Count all API endpoints or admin UI pages that support audit log filtering. Verify that at least 4 filter dimensions are supported: date range, user, amount, and status.

• Pass criteria: Audit log table has at least 3 indexes on frequently-searched columns (minimum: user_id, timestamp, status). At least 1 API endpoint or admin UI supports filtering by at least 4 dimensions (date, user, amount, status). Report the count even on pass (e.g., "5 indexes found, 1 search endpoint with 4 filter dimensions").

• Fail criteria: Fewer than 3 indexes on audit table, or no filtering API/UI exists, or filtering supports fewer than 4 dimensions.

• Skip (N/A) when: Never — searchability is essential for investigations and audits.

• Detail on fail: "Audit log table has 1 index (on id only) — 0 of 3 required search indexes present." or "No API endpoint supports audit log filtering — 0 of 4 required filter dimensions available.".

• Remediation: Add indexes and a query API (in db/migrations/ and src/app/api/admin/audit-logs/route.ts):

  CREATE INDEX idx_audit_user ON transaction_logs(user_id);
  CREATE INDEX idx_audit_timestamp ON transaction_logs(timestamp);
  CREATE INDEX idx_audit_status ON transaction_logs(status);
  CREATE INDEX idx_audit_amount ON transaction_logs(amount);
  
  -- Composite for common queries
  CREATE INDEX idx_audit_user_date ON transaction_logs(user_id, timestamp);
  

  Add a search API:

  app.get('/api/admin/audit-logs', authenticate, requireRole('compliance'), async (req, res) => {
    const { userId, startDate, endDate, status, minAmount, maxAmount } = req.query;
  
    let query = db.transactionLogs.find({});
    if (userId) query = query.where('user_id').equals(userId);
    if (startDate || endDate) {
      const range = {};
      if (startDate) range.$gte = new Date(startDate);
      if (endDate) range.$lte = new Date(endDate);
      query = query.where('timestamp').in(range);
    }
    if (status) query = query.where('status').equals(status);
    if (minAmount || maxAmount) {
      const amountRange = {};
      if (minAmount) amountRange.$gte = parseFloat(minAmount);
      if (maxAmount) amountRange.$lte = parseFloat(maxAmount);
      query = query.where('amount').in(amountRange);
    }
  
    const logs = await query.exec();
    res.json(logs);
  });