Open-source AI Security Operations Center: alert fusion, LLM-agent triage, MITRE ATT&CK investigation, and a replayable decision ledger for every agent step. Self-hostable, runs with no API keys, MIT licensed. Ships an MCP server for Claude, Cursor and Continue.
Python agentic-aiai-securityalert-triageblue-teamcybersecuritydetection-engineeringincident-responselanggraphllm-agentsmcpmitre-attackmodel-context-protocolpurple-teamsecurity-operationsself-hostedsiemsoarsocthreat-detectionthreat-intelligence
"""Database connection management."""
from collections.abc import AsyncGenerator
from typing import Any
from urllib.parse import parse_qsl, urlencode, urlsplit, urlunsplit
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase
from app.core.config import settings
# libpq sslmode values -> what asyncpg's `ssl` kwarg expects.
# asyncpg accepts: "disable" | "allow" | "prefer" | "require" | "verify-ca" | "verify-full"
# but NOT under the libpq query-string name `sslmode=`. We pop it from the URL
# and forward it as a connect_arg so asyncpg sees it on the right hand.
_SSLMODE_PASSTHROUGH = {"disable", "allow", "prefer", "require", "verify-ca", "verify-full"}
def _normalize_async_pg_url(url: str) -> tuple[str, dict[str, Any]]:
"""Coerce common Postgres URL forms into the SQLAlchemy 2.x async dialect.
Fly.io's ``flyctl postgres attach`` (and Heroku, and most managed
hosts) writes ``DATABASE_URL=postgres://...``. SQLAlchemy 2.x dropped
the bare ``postgres`` scheme alias, so passing it straight to
``create_async_engine`` raises ``NoSuchModuleError: postgres``.
Likewise, ``postgresql://...`` without an explicit driver suffix
selects the synchronous ``psycopg2`` dialect, which the async engine
rejects ("the asyncio extension requires an async driver").
Managed Postgres providers also append ``?sslmode=require`` (libpq
syntax) which asyncpg's ``connect()`` rejects with
``TypeError: connect() got an unexpected keyword argument 'sslmode'``.
We strip the param and translate it into an explicit ``connect_args``
payload that asyncpg understands.
Returns the normalized URL plus a ``connect_args`` dict that callers
forward to ``create_async_engine``.
"""
if url.startswith("postgres://"):
url = "postgresql+asyncpg://" + url[len("postgres://") :]
elif url.startswith("postgresql://"):
# No driver specified -> default to asyncpg (the only async driver
# we ship with). Anything matching "postgresql+<driver>://" already
# has an explicit choice and we leave it alone.
url = "postgresql+asyncpg://" + url[len("postgresql://") :]
# Translate libpq-style query params that asyncpg can't parse.
parts = urlsplit(url)
query_pairs = parse_qsl(parts.query, keep_blank_values=True)
connect_args: dict[str, Any] = {}
remaining: list[tuple[str, str]] = []
for key, value in query_pairs:
if key == "sslmode":
mode = value.lower().strip()
if mode in _SSLMODE_PASSTHROUGH:
# asyncpg understands the libpq mode names via `ssl=`.
connect_args["ssl"] = mode if mode != "disable" else False
continue
if key == "channel_binding":
# libpq-only; asyncpg negotiates SCRAM channel binding itself.
continue
remaining.append((key, value))
new_url = urlunsplit((parts.scheme, parts.netloc, parts.path, urlencode(remaining), parts.fragment))
return new_url, connect_args
_normalized_url, _connect_args = _normalize_async_pg_url(str(settings.DATABASE_URL))
# Bound how long asyncpg waits for a single statement. Fly's proxy can hang
# a cold-start connect for a long time; failing fast lets the demo wake loop
# retry instead of blocking a worker for minutes.
_connect_args.setdefault("timeout", 15)
_connect_args.setdefault("command_timeout", 60)
# pool_pre_ping + pool_recycle are required for Fly Postgres (and any
# managed Postgres that autostops / idle-closes). Without pre_ping the
# pool hands out sockets that the server already closed, and asyncpg
# surfaces that as ``ConnectionDoesNotExistError`` on the next query —
# which is exactly what was 500ing every demo endpoint on tryaisoc.com
# (auth/login, metrics/*, alerts/*, …) while /health still looked fine.
engine = create_async_engine(
_normalized_url,
pool_size=settings.DATABASE_POOL_SIZE,
max_overflow=settings.DATABASE_MAX_OVERFLOW,
pool_pre_ping=True,
pool_recycle=settings.DATABASE_POOL_RECYCLE_SECONDS,
echo=settings.DEBUG,
future=True,
connect_args=_connect_args,
)
AsyncSessionLocal = async_sessionmaker(
engine,
class_=AsyncSession,
expire_on_commit=False,
)
class Base(DeclarativeBase):
pass
async def get_db() -> AsyncGenerator[AsyncSession, None]:
async with AsyncSessionLocal() as session:
try:
yield session
await session.commit()
except Exception:
await session.rollback()
raise