Audit logs

CareNova records every significant authentication event in the auth_audit_log table. This provides a traceable history of who logged in, when, from where, and what happened — giving admins the visibility needed to detect suspicious activity and investigate security incidents.

What Gets Logged

Every event in the table below is automatically recorded with no configuration required:

Events related to password resets and email confirmation are handled by Supabase Auth directly and are visible in Supabase → Authentication → Logs.

The auth_audit_log Table

Each recorded event captures the following:

user_id is null for login_failed events where the email address does not exist in the system. This prevents leaking whether an email is registered.

Accessing Audit Logs

Audit logs are not exposed in the CareNova dashboard UI. Admins access them directly in Supabase:

Supabase → Table Editor → auth_audit_log

Or via SQL Editor for filtered queries.

Useful Queries

All events in the last 24 hours

SELECT email, event, ip_address, 
       user_agent, created_at
FROM auth_audit_log
ORDER BY created_at DESC
LIMIT 100;

All failed login attempts

SELECT email, ip_address, user_agent, created_at
FROM auth_audit_log
WHERE event = 'login_failed'
ORDER BY created_at DESC;

All activity for a specific user

SELECT event, ip_address, user_agent, 
       metadata, created_at
FROM auth_audit_log
WHERE email = 'staff@yourdomain.com'
ORDER BY created_at DESC;

All rate-limited events

SELECT email, ip_address, created_at
FROM auth_audit_log
WHERE event = 'rate_limited'
ORDER BY created_at DESC;

IPs with the most failed attempts

in the last 24 hours

SELECT ip_address, COUNT(*) as attempts
FROM auth_audit_log
WHERE event = 'login_failed'
  AND created_at > now() - interval '24 hours'
GROUP BY ip_address
ORDER BY attempts DESC
LIMIT 20;

All successful logins in the last 7 days

SELECT email, ip_address, user_agent, created_at
FROM auth_audit_log
WHERE event = 'login_success'
  AND created_at > now() - interval '7 days'
ORDER BY created_at DESC;

Account approvals and rejections

SELECT email, event, ip_address, created_at
FROM auth_audit_log
WHERE event IN ('account_approved', 'account_rejected')
ORDER BY created_at DESC;

Identifying Suspicious Activity

Brute-Force Attack

Signs: Many login_failed events from the same IP address within a short window.

SELECT ip_address, COUNT(*) as failed_count,
       MIN(created_at) as first_attempt,
       MAX(created_at) as last_attempt
FROM auth_audit_log
WHERE event = 'login_failed'
  AND created_at > now() - interval '1 hour'
GROUP BY ip_address
HAVING COUNT(*) > 5
ORDER BY failed_count DESC;

Action: The rate limiter will have already blocked further attempts after 10 failures from the same IP within 15 minutes. Monitor for continued activity after the lockout window.

Credential Stuffing

Signs: Many login_failed events targeting different email addresses from the same IP.

SELECT ip_address, 
       COUNT(DISTINCT email) as emails_tried,
       COUNT(*) as total_attempts
FROM auth_audit_log
WHERE event = 'login_failed'
  AND created_at > now() - interval '1 hour'
GROUP BY ip_address
ORDER BY emails_tried DESC;

Unusual Login Location

Signs: A known user logging in from an IP address or country they have never used before.

SELECT email, ip_address, user_agent, created_at
FROM auth_audit_log
WHERE event = 'login_success'
  AND email = 'staff@yourdomain.com'
ORDER BY created_at DESC
LIMIT 20;

Compare IP addresses across recent successful logins to spot anomalies.

Repeated Rate-Limited Events

Signs: The same email appearing in rate_limited events repeatedly across multiple days — suggests a persistent attack targeting a specific account.

SELECT email, COUNT(*) as lockouts,
       MIN(created_at) as first_lockout,
       MAX(created_at) as last_lockout
FROM auth_audit_log
WHERE event = 'rate_limited'
GROUP BY email
ORDER BY lockouts DESC;

Audit Log Retention

Audit log entries older than 24 hours are automatically deleted by the cron job at /api/cron/cleanup-auth.

The 24-hour retention window is designed to keep the tables lean and rate limiting queries fast. If your clinic requires longer retention for compliance purposes, you can modify the cleanup query in lib/auth/audit.ts to use a longer interval — for example interval '90 days'.

Extending Log Retention

To keep audit logs for 90 days instead of 24 hours, update the cleanup function in lib/auth/audit.ts:

// Change this line in cleanupOldAttempts()
AND attempted_at < NOW() - INTERVAL '24 hours'

// To this
AND attempted_at < NOW() - INTERVAL '90 days'

Make the same change for auth_audit_log cleanup in the same file.

Extending retention will cause both tables to grow significantly over time. Add an index on created_at if you extend retention beyond a few days — the index is already included in INSTALL.sql.

Relationship With Login Attempts

The auth_audit_log and login_attempts tables serve different purposes and work together:

When a login fails, both tables are written to simultaneously. The rate limiter reads only from login_attempts (faster, simpler query). Admins investigate using auth_audit_log (richer, more context).

Security Recommendations

• Review audit logs weekly — look for rate_limited events and unusual IP patterns

• Set up alerting — consider using Supabase webhooks or a monitoring service to alert on high volumes of login_failed events

• Do not expose logs to non-admins — audit logs contain IP addresses and user agents; access should be restricted to Supabase admin accounts only

• Extend retention for compliance — if your jurisdiction requires audit trail retention longer than 24 hours, update the cleanup interval as described above

• Confirm cron is running — without daily cleanup, the tables grow unbounded and rate limiting degrades; see Cron Setup

Troubleshooting

Audit log is empty:

• Confirm the auth_audit_log table exists — run INSTALL.sql if not

• Confirm the cron job is not running too frequently and deleting records before you can view them

• Try logging out and back in — a logout and login_success event should appear immediately

Events missing for certain actions:

• login_failed is only recorded after Supabase Auth rejects the credentials — if Supabase is unavailable, the event may not be recorded

• Password reset and email confirmation events are in Supabase Auth logs, not auth_audit_log

Table growing too large:

• Confirm the cron job is running daily at /api/cron/cleanup-auth

• Run it manually once to clear the backlog:

curl -X GET \
  -H "Authorization: Bearer YOUR_CRON_SECRET" \
  https://yourdomain.com/api/cron/cleanup-auth

Next Step

The Authentication & Access Control collection is now complete. Continue to the Dashboard & Modules collection to learn how each dashboard module works in detail.