Metadata-Version: 2.4
Name: exobrain-database
Version: 0.3.0b5
Summary: Exobrain database model and migration management
Keywords: 
Author: Laurent LAPORTE
Author-email: Laurent LAPORTE <laurent@dfya.io>
License-File: LICENSE.md
Classifier: Development Status :: 4 - Beta
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Software Development :: Libraries
Classifier: Topic :: Database
Classifier: Topic :: Database :: Database Engines/Servers
Requires-Dist: alembic>=1.18.4
Requires-Dist: psycopg[binary]>=3.3.4
Requires-Dist: sqlalchemy>=2.0.51
Requires-Python: >=3.12
Project-URL: Documentation, https://github.com/MyExobrain/exobrain-database#readme
Project-URL: Issues, https://github.com/MyExobrain/exobrain-database/issues
Project-URL: Source, https://github.com/MyExobrain/exobrain-database
Description-Content-Type: text/markdown

# exobrain-database

[![PyPI - Version](https://img.shields.io/pypi/v/exobrain-database.svg)](https://pypi.org/project/exobrain-database)
[![PyPI - Python Version](https://img.shields.io/pypi/pyversions/exobrain-database.svg)](https://pypi.org/project/exobrain-database)
[![CI/CD](https://github.com/MyExobrain/exobrain-database/actions/workflows/build.yml/badge.svg)](https://github.com/MyExobrain/exobrain-database/actions/workflows/build.yml)

---

`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](#installation)
- [Development](#development)
- [Project Structure](#project-structure)
- [Usage](#usage)
- [License](#license)

---

## Installation

```console
pip install exobrain-database
```

Or with [uv](https://docs.astral.sh/uv/):

```console
uv add exobrain-database
```

---

## Development

This project uses [uv](https://docs.astral.sh/uv/) for dependency management and building.

**Prerequisites:** Python 3.12+ and uv

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

Common tasks:
```console
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:
```console
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):

```sh
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:

```console
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:

```console
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:

```console
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:

```console
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

```python
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

```python
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](LICENSE.md) for details.