Summary

The src/agentgit/database module currently uses SQLite-specific connection settings and assumptions (e.g., sqlite:// URL and connect_args={"check_same_thread": False} / SQLite SQL idioms). We need to support PostgreSQL (production-ready), keeping SQLite for local/dev convenience.

Why

SQLite is fine for local testing, but we need a production-grade DB (Postgres) for concurrency, durability, and better query/JSONB capabilities.

What to change (high-level)

Read DB URL from environment (e.g., DATABASE_URL) and build the SQLAlchemy engine from it. Support both sync (psycopg2) and/or async (asyncpg) patterns depending on project architecture.

Keep SQLite connect_args for local dev, but do not pass check_same_thread when using Postgres.

Add psycopg2-binary (or psycopg[binary] / psycopg3) to requirements and document how to run PostgreSQL for local testing (docker-compose).

Add Alembic (or similar) for DB migrations and a short migration script/instructions.

Add tests / CI job that runs the test suite against a PostgreSQL service (docker-compose in GitHub Actions).

Acceptance criteria

DATABASE_URL=postgresql://user:pass@localhost:5432/agentgit pytest runs tests against Postgres.

Existing SQLite flow still works by setting DATABASE_URL=sqlite:///./dev.db (or default fallback).

No SQLite-only kwargs (e.g., check_same_thread) are sent when the engine URL is PostgreSQL.

Notes / References

Example pattern for conditional connect_args in SQLAlchemy (sqlite vs others).

Recommend psycopg2-binary for the easiest install.