Add database connection health check to backend /api/health endpoint

[Calebux]

Description

The backend has an /api/admin/health endpoint but it does not verify that the database connection is actually working. The endpoint can return { status: 'ok' } even when the Supabase connection is broken, making health checks useless for deployment verification.

Current State

// Health endpoint likely returns static 'ok' without checking dependencies
app.get('/api/health', (req, res) => {
  res.json({ status: 'ok', timestamp: new Date() });
});

Fix: Comprehensive dependency health check

Checked Dependencies

1. Supabase DB — run a lightweight ping query
2. Email service — verify SMTP connection (not send)
3. Blockchain RPC (if configured) — verify reachability
4. Scheduler — verify cron jobs are running
5. EventListener — verify polling is active

Implementation

router.get('/health', async (req, res) => {
  const checks = await Promise.allSettled([
    checkDatabase(),
    checkEmailService(),
    checkBlockchainRpc(),
  ]);

  const [db, email, blockchain] = checks.map(c => 
    c.status === 'fulfilled' ? { status: 'healthy' } : { status: 'unhealthy', error: c.reason.message }
  );

  const isHealthy = db.status === 'healthy';  // DB is critical; others are degraded
  
  res.status(isHealthy ? 200 : 503).json({
    status: isHealthy ? 'healthy' : 'degraded',
    timestamp: new Date().toISOString(),
    version: process.env.npm_package_version,
    dependencies: { database: db, email, blockchain },
    uptime: process.uptime(),
  });
});

async function checkDatabase(): Promise<void> {
  const { error } = await supabase.from('profiles').select('id').limit(1);
  if (error) throw new Error(`Database check failed: ${error.message}`);
}

Response Example

{
  "status": "degraded",
  "timestamp": "2025-03-15T10:00:00Z",
  "version": "1.2.3",
  "dependencies": {
    "database": { "status": "healthy", "latencyMs": 23 },
    "email": { "status": "unhealthy", "error": "SMTP connection refused" },
    "blockchain": { "status": "healthy", "lastLedger": 12345678 }
  },
  "uptime": 86400
}

Public vs Admin Health

• GET /api/health — public, returns only { status, timestamp } (no internals)
• GET /api/admin/health — admin-only, returns full dependency breakdown

Acceptance Criteria

• /api/health pings database and returns 503 if DB is down
• Admin health endpoint shows all dependency statuses with latency
• Response includes version from package.json
• Vercel post-deploy check can use /api/health to verify deployment
• Latency of each dependency check included in response

[drips-wave]

This issue has been added to the Stellar Wave Program and is worth 150 Points.

🧑‍💻 Interested in contributing? Apply to work on this issue on Drips Wave, earn points, and receive rewards.

Warning

Only applications submitted via the apply link above will be considered. Please do not apply by commenting on the issue or opening a PR directly.

🤠 Repo maintainers: You can manage this issue's Points and Complexity on your Maintainer Dashboard here. Please keep an eye on applications and respond quickly 💙

ℹ️ Learn more about Drips Wave

[obed-smart]

@obed-smart has applied to work on this issue as part of the Stellar Wave Program's 3rd wave.

Hi, I'd like to work on this issue.

I have experience building backend features and working with APIs, databases, and middleware, and I'm confident I can implement this following the project guidelines.

ℹ️ Repo Maintainers: To accept this application, review their application or assign @obed-smart to this issue.

[drips-wave]

Congratulations, @obed-smart! 🎉 Your application was accepted by the repo's maintainers, and the issue is due on March 30, 2026.

🧑‍💻 @obed-smart: Please resolve the issue such that the repo's maintainers have enough time to review your contribution before the due date. You'll earn Points for completing the issue on-time, which will make you eligible for a share of the Stellar Wave Program's reward pool.

Warning

When opening a PR, please link it to this issue to ensure it gets tracked accurately. Points are awarded when this issue is marked as completed by the maintainer.

🤠 Repo maintainers: Please keep an eye on the contributor's progress and review their work before the due date. You can manage this issue, including adjusting its complexity and points, here.

🌊 Happy Wave 🌊

[obed-smart]

@Calebux im getting error of missing files here when i try running this server