DEV Community

Richard Atodo
Richard Atodo

Posted on

🛠️ Building the Foundation of an AI DevOps Incident Copilot: FastAPI, PostgreSQL, and SQLAlchemy

#postgressql #fastapi #docker #sqlalchemy

📌 Introduction

When building an AI-powered DevOps incident investigation system, it’s tempting to start with the flashy features: log analysis, AI-generated root-cause explanations, and automated troubleshooting.

But those depend on something more fundamental: a reliable backend, a structured data model, and persistent storage.

As part of my IncidentCopilot project, Milestone 2 focused on establishing that foundation using FastAPI, PostgreSQL, SQLAlchemy, and Alembic.

🌍 Project Overview

IncidentCopilot is an open-source project designed to help developers and DevOps engineers investigate incidents more efficiently.

Planned features include:

  • Log ingestion
  • Incident correlation
  • Investigation timelines
  • Evidence retrieval
  • AI-assisted diagnosis

Principle: deterministic logic and reliable storage first, AI later.

1️⃣ Integrating PostgreSQL

  • Added PostgreSQL 16 to Docker Compose.
  • Configured persistent storage with a named volume.
  • Health checks via pg_isready.
  • Backend waits for DB readiness before starting.

⚠️ Key detail:

  • Host dev → localhost
  • Container dev → postgres (Compose service name)

2️⃣ Setting up SQLAlchemy

  • Introduced SQLAlchemy for DB access.
  • Shared engine + session factory.
  • get_db dependency ensures sessions close after requests.

3️⃣ Creating Domain Models

Six initial models:

  • Incident → title, severity, status, timestamps
  • Log → source, timestamp, severity, service, event type, message, metadata (JSONB)
  • IncidentLog → connects incidents ↔ logs, includes relevance scoring
  • Diagnosis → summary, root cause, confidence, category, raw response (JSONB)
  • Evidence → links diagnosis ↔ logs, with reasoning
  • InvestigationStep → description, status, position

4️⃣ Managing Schema with Alembic

  • Added Alembic for migrations.
  • Initial migration created tables + relationships.
  • Verified schema alignment with: 
alembic check
Enter fullscreen mode Exit fullscreen mode

Result:

No new upgrade operations detected.
Enter fullscreen mode Exit fullscreen mode

5️⃣ Adding API Endpoints

Endpoint Purpose
GET /health Confirms app health
GET /ready Checks DB connectivity
GET /api/v1/incidents Lists incidents
GET /api/v1/logs Lists logs

Example readiness response:

{
  "status": "ready"
}
Enter fullscreen mode Exit fullscreen mode

6️⃣ Testing the Foundation

  • Tests for health, readiness, DB connectivity, model registration, empty endpoints.
  • Result: 
6 passed, 1 warning in 2.33s
Enter fullscreen mode Exit fullscreen mode
  • Verified Docker Compose config + DB migration state.
  • Backend resolved postgres hostname correctly.

7️⃣ Lessons Learned

  • Clear domain model matters → incidents, logs, evidence, diagnoses, steps each have distinct roles.
  • Keep config outside code → easier portability.
  • Use migrations early → reproducible schema changes.
  • Health ≠ readiness → DB connectivity matters.
  • Build incrementally → don’t rush correlation or AI.

🔮 What’s Next?

Milestone 3 → multi-source log ingestion.

Future milestones:

  • Normalization
  • Incident correlation
  • Investigation timelines
  • Retrieval-augmented generation (RAG)
  • AI-assisted diagnosis

🏁 Conclusion

Milestone 2 wasn’t about flashy AI. It was about laying a dependable foundation.

By integrating PostgreSQL, SQLAlchemy, Alembic, structured domain models, versioned API endpoints, and automated tests, IncidentCopilot now has the backend infrastructure to grow.

Next challenge: make the foundation useful by ingesting operational logs.

🔗 GitHub: github.com/richardatodo/incidentcopilot

➡️ Next: Milestone 3 — Multi-source Log Ingestion

Top comments (0)