exobrain-database

exobrain-database is a Python library that provides the SQLAlchemy ORM models, Alembic migration management, and utility tools for the Exobrain platform database. It supports a multi-tenant architecture where each organization has its own isolated PostgreSQL schema.

The package is distributed as a namespace package under exobrain.database, making it composable with other exobrain.* packages.

Table of Contents

• Installation
• Development
• Project Structure
• Usage
• License

Installation

pip install exobrain-database

Or with uv:

uv add exobrain-database

Development

This project uses uv for dependency management and building.

Prerequisites: Python 3.12+ and uv

Setup:

git clone git@github.com:MyExobrain/exobrain-database.git
cd exobrain-database
uv sync --all-groups

Common tasks:

uv run pytest                                  # Run tests
uv run pytest --cov=exobrain.database          # Tests with coverage
uv run ruff check src tests                    # Lint
uv run ruff format src tests                   # Format
uv run mypy src                                # Type check
uv build                                       # Build wheel + sdist

Add dependencies:

uv add package-name                # Production
uv add --group dev package-name    # Development
uv sync --upgrade                  # Update all

Database migrations

Alembic reads the database connection from environment variables. Create a .env file at the project root (do not commit it):

export SQL_HOST="localhost"
export SQL_DATABASE="exobrain"
export SQL_USER="postgres"
export SQL_PORT="5432"
export SQL_PASSWORD="Dfya2023"

Source it before running any Alembic command:

source .env

The Alembic settings are declared in pyproject.toml under [tool.alembic], so you can run commands directly from the project root without the -c flag:

alembic current                     # Show applied revision
alembic history                     # List migration history
alembic upgrade head                # Apply all pending migrations
alembic downgrade -1                # Roll back one migration

For creating a new migration, pass -c explicitly so the file naming template from alembic.ini is applied:

alembic -c src/exobrain/database/migrations/alembic.ini revision --autogenerate -m "short description"

Running migrations from outside this repository

The alembic CLI above only works from this project's root, since it relies on the [tool.alembic] section of this repo's pyproject.toml. Consuming projects (e.g. exobrain-server) or containers that only have exobrain-database installed as a dependency don't have that file, and the packaged alembic.ini sits inside the installed package (e.g.: under .venv/), which is impractical to locate.

For that case, exobrain-database ships a console script, exobrain-db-migrate, that resolves its own alembic.ini and wraps the full Alembic CLI, so it works the same way from any environment where the package is installed:

exobrain-db-migrate current                       # Show applied revision(s)
exobrain-db-migrate history                       # List migration history
exobrain-db-migrate upgrade head                  # Apply all pending migrations
exobrain-db-migrate downgrade -1                  # Roll back one migration
exobrain-db-migrate -x schema=org_42 current      # Target a single organization schema

It requires the same SQL_* environment variables as above, and writes its logs to stdout so they are picked up by container log collectors. It is a thin wrapper: any other native Alembic command (stamp, heads, show, -c/ALEMBIC_CONFIG override, ...) works unchanged.

Project Structure

The source code lives under src/exobrain/database/ as a namespace package:

src/exobrain/database/
├── migrations/                   # Alembic migration engine
│   ├── alembic.ini               # Alembic configuration file
│   ├── cli.py                    # `exobrain-db-migrate` console script entry point
│   ├── env.py                    # Migration environment setup
│   ├── migration_manager.py      # Public API: upgrade / downgrade per org schema
│   └── versions/                 # Auto-generated migration scripts
├── model/                        # SQLAlchemy ORM models
│   ├── base.py                   # Declarative base classes
│   ├── associations/             # Many-to-many association tables
│   ├── enums/                    # Database enumeration types (config, currency, running states)
│   ├── general/                  # Cross-organization models (copilots, permissions, roles,
│   │                             #   predefined actions/executions/risks, reasons)
│   └── org/                      # Per-organization models (config, connections, dashboards,
│                                 #   running actions/executions/risks, scopes, users)
└── tools/                        # Reusable database utilities
    ├── jsonb_filter.py           # Helper for filtering on JSONB columns
    ├── record_count.py           # Efficient record counting queries
    ├── report_field_coercion.py  # Coerce raw context values into typed report slots
    ├── risk_report_projector.py  # Project declared reporting fields into risk_report_fact
    └── sql_query_logger.py       # SQLAlchemy query logging utility

Key design decisions

• Multi-tenant schemas: each organization's data is isolated in a dedicated PostgreSQL schema (e.g. org_<id>). Migrations are applied per-schema via migration_manager.upgrade(org_id).
• Namespace package: exobrain.database has no __init__.py at the exobrain level, allowing other exobrain.* packages to coexist in the same Python environment.
• py.typed marker: the package ships type information and is fully typed (compatible with mypy in strict mode).
• Build system: Uses uv as the modern Python build & package manager.

Usage

Apply migrations for an organization

from uuid import UUID
from exobrain.database.migrations import migration_manager

org_id = UUID("12345678-1234-5678-1234-567812345678")
migration_manager.upgrade(org_id)

Use ORM models

from exobrain.database.model.general.role import Role
from exobrain.database.model.org.user import User

License

exobrain-database is distributed under a proprietary license. See LICENSE.md for details.