Database Monitoring & Maintenance

Operational guide for monitoring database health, running maintenance tasks, and keeping the UberLotto v2 Supabase instance running smoothly.

Monitoring Queries

Row Counts

Get a quick overview of all table sizes:

SELECT
  'payment_transactions' as table_name, COUNT(*) as rows FROM payment_transactions
UNION ALL SELECT 'payment_transaction_events', COUNT(*) FROM payment_transaction_events
UNION ALL SELECT 'webhook_nonces', COUNT(*) FROM webhook_nonces
UNION ALL SELECT 'security_events', COUNT(*) FROM security_events
UNION ALL SELECT 'jackpots', COUNT(*) FROM jackpots
UNION ALL SELECT 'past_drawings', COUNT(*) FROM past_drawings;

Recent Security Events

Check for errors and critical events in the last 24 hours:

SELECT * FROM security_events
WHERE severity IN ('error', 'critical')
AND created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC;

Or use the built-in function with filtering:

-- All critical events in the last 6 hours
SELECT * FROM get_recent_security_events(NULL, 'critical', 100, 6);

-- HMAC failures in the last 24 hours
SELECT * FROM get_recent_security_events('hmac_failure', NULL, 100, 24);

-- Replay attack attempts
SELECT * FROM get_recent_security_events('replay_detected', NULL, 50, 168);

Payment Transaction Status

View the current distribution of payment transaction statuses by provider:

SELECT provider, status, COUNT(*), MAX(created_at) as latest
FROM payment_transactions
GROUP BY provider, status
ORDER BY provider, status;

Stale Pending Transactions

Find transactions that should have been expired by the cron job:

SELECT COUNT(*) as stale_count
FROM payment_transactions
WHERE expires_at < NOW()
AND status = 'pending';

Or use the built-in function to expire them:

SELECT expire_stale_transactions();

Expired Webhook Nonces

Check for nonces that haven't been cleaned up yet:

SELECT COUNT(*) as expired_count
FROM webhook_nonces
WHERE expires_at < NOW();

TIP

If this count is consistently high, the cleanup_expired_nonces() function may not be running on schedule. See Scheduled Maintenance below.

Security Event Summary

Get an aggregate view of event types over the last 7 days:

SELECT
  event_type,
  severity,
  COUNT(*) as event_count,
  MIN(created_at) as first_seen,
  MAX(created_at) as last_seen
FROM security_events
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY event_type, severity
ORDER BY event_count DESC;

Payment Transaction Events

View recent status changes across all transactions:

SELECT
  pte.created_at,
  pt.order_number,
  pt.provider,
  pte.previous_status,
  pte.new_status,
  pte.changed_by,
  pte.change_reason
FROM payment_transaction_events pte
JOIN payment_transactions pt ON pt.id = pte.transaction_id
ORDER BY pte.created_at DESC
LIMIT 50;

Transaction Summary by User

Get aggregated stats for a specific user using the built-in function:

SELECT * FROM get_transaction_summary('[email protected]');

Cancellation Rate Check

Check if a user is hitting the anti-abuse threshold (10+ cancellations per hour):

SELECT check_cancellation_rate('[email protected]');
-- Returns FALSE if abuse threshold exceeded

Active Transactions by Provider

Monitor currently active (non-terminal) transactions:

SELECT
  provider,
  status,
  COUNT(*) as count,
  MIN(created_at) as oldest,
  MAX(created_at) as newest
FROM payment_transactions
WHERE status IN ('pending', 'processing', 'awaiting_confirmation')
GROUP BY provider, status
ORDER BY provider, count DESC;

Jackpot Data Freshness

Check when jackpot data was last updated:

SELECT
  slug,
  game_name,
  jackpot_alpha_numeric,
  time_stamp_iso,
  created_at,
  NOW() - created_at as age
FROM jackpots
ORDER BY created_at DESC;

Scheduled Maintenance

The following maintenance tasks must run on a regular schedule:

Task	Schedule	Method	Description
Clean expired nonces	Every 5 minutes	Database function	Removes expired webhook nonces
Clean old security events	Monthly	Database function	Removes events older than 1 year
Expire stale transactions	Periodic (cron)	Database function	Marks pending transactions past expires_at as expired
Archive old events	Monthly	Database function	Removes transaction events older than 90 days

Nonce Cleanup (Every 5 Minutes)

Expired webhook nonces must be cleaned up regularly to prevent the webhook_nonces table from growing unbounded.

Method: Call the cleanup_expired_nonces() database function.

SELECT cleanup_expired_nonces();

Setup options:

1. Supabase pg_cron (recommended):

   SELECT cron.schedule(
     'cleanup-expired-nonces',
     '*/5 * * * *',
     $$SELECT cleanup_expired_nonces()$$
   );

2. External cron job calling the Supabase REST API or SQL Editor.

Security Event Cleanup (Monthly)

Security events are retained for 1 year. The cleanup function removes older records.

Method: Call the cleanup_old_security_events() database function.

SELECT cleanup_old_security_events();

Setup with pg_cron:

SELECT cron.schedule(
  'cleanup-old-security-events',
  '0 3 1 * *',  -- 3:00 AM on the 1st of each month
  $$SELECT cleanup_old_security_events()$$
);

Stale Transaction Expiry (Periodic)

Pending transactions that exceed their expires_at timestamp are marked as expired by the expire_stale_transactions() database function.

SELECT expire_stale_transactions();

Setup with pg_cron:

SELECT cron.schedule(
  'expire-stale-transactions',
  '*/15 * * * *',  -- Every 15 minutes
  $$SELECT expire_stale_transactions()$$
);

Old Event Archival (Monthly)

Transaction events older than 90 days are cleaned up by archive_old_events().

SELECT archive_old_events();              -- Default: 90 days
SELECT archive_old_events('180 days');    -- Custom retention

Setup with pg_cron:

SELECT cron.schedule(
  'archive-old-events',
  '0 4 1 * *',  -- 4:00 AM on the 1st of each month
  $$SELECT archive_old_events()$$
);

Alerts to Configure

Set up monitoring alerts for these conditions:

Condition	Query	Threshold
Critical security events	SELECT COUNT(*) FROM security_events WHERE severity = 'critical' AND created_at > NOW() - INTERVAL '1 hour'	> 0
High rate of HMAC failures	SELECT COUNT(*) FROM security_events WHERE event_type = 'hmac_failure' AND created_at > NOW() - INTERVAL '1 hour'	> 10
Replay attacks detected	SELECT COUNT(*) FROM security_events WHERE event_type = 'replay_detected' AND created_at > NOW() - INTERVAL '1 hour'	> 0
Stale nonces accumulating	SELECT COUNT(*) FROM webhook_nonces WHERE expires_at < NOW()	> 100
Stale pending payments	SELECT COUNT(*) FROM payment_transactions WHERE expires_at < NOW() AND status = 'pending'	> 10
Failed payments spike	SELECT COUNT(*) FROM payment_transactions WHERE status = 'failed' AND created_at > NOW() - INTERVAL '1 hour'	> 5
High cancellation rate	SELECT check_cancellation_rate('[email protected]')	Returns FALSE
Event log overflow	SELECT transaction_id, COUNT(*) FROM payment_transaction_events GROUP BY transaction_id HAVING COUNT(*) >= 45	Any rows

Best Practices

1. Run monitoring queries daily — Check security events and payment transaction status at minimum once per day.

2. Verify cleanup jobs are running — If expired nonces or old security events accumulate, the scheduled functions may have stopped.

3. Review security events after incidents — Use get_recent_security_events() with appropriate filters to investigate payment issues.

4. Monitor table sizes — The security_events table grows continuously. Ensure the monthly cleanup is running to keep it within the 1-year retention window.

5. Back up before maintenance — Before running any manual cleanup or schema changes, create a Supabase backup via the Dashboard.

6. Check jackpot freshness — If jackpot amounts appear stale on the website, check the time_stamp_iso and created_at columns in the jackpots table.

7. Use the Supabase Dashboard — The Table Editor and SQL Editor in the Supabase Dashboard are the primary tools for ad-hoc monitoring and maintenance.