Database and sessions

This page explains the database layer of Cluster ERP: the Database class in core/database.py, the request session dependency in server/dependencies.py, the request context in core/request_context.py, and the session helper for EventBus handlers in core/handler_session.py. It also explains when a request's transaction commits. Tenant routing is documented at mechanism level in multi-tenancy.

In short¶

• Database wraps one async SQLAlchemy engine on asyncpg and one session factory with expire_on_commit=False (src/cluster_erp/core/database.py:53-93).
• get_db_session gives each request one AsyncSession, publishes it in context variables, and rolls it back on error (src/cluster_erp/server/dependencies.py:160-200).
• The commit runs before the first response byte is sent, through a pre-response hook (src/cluster_erp/server/dependencies.py:163-190, src/cluster_erp/server/middleware.py:25-57).
• The acting user, the correlation id, and the deactivation reason travel in context variables, not in function arguments (src/cluster_erp/core/request_context.py:16-61).
• An EventBus handler joins the request's session when there is one, else it opens its own (src/cluster_erp/core/handler_session.py:25-37).

How it works¶

The Database class¶

Database(settings, *, database_url=None, pool_size=None, max_overflow=None) creates the engine with these options (src/cluster_erp/core/database.py:69-80):

The tenancy layer builds one Database per tenant database with a smaller pool (src/cluster_erp/core/database.py:62-68). Database.session() is an async generator that commits on success and rolls back on error; start-up passes use it (src/cluster_erp/core/database.py:95-102).

The request session¶

get_db_session is the FastAPI dependency for every route that touches data. It does the following:

1. Resolves the request's Database: the process default in single-tenant mode, the tenant's database in multi-tenant mode (src/cluster_erp/server/dependencies.py:104-157).
2. Opens a session and publishes it, its task, and its database in three context variables (src/cluster_erp/server/dependencies.py:182-184, src/cluster_erp/core/database.py:21-37).
3. Opens the connection and injects the audit context (src/cluster_erp/server/dependencies.py:186-187). This sets five transaction-local PostgreSQL settings in one statement (app.actor_id, app.ip, app.user_agent, app.session_id, app.correlation_id), and sets the request context variables (src/cluster_erp/server/dependencies.py:34-72).
4. Registers _commit_once as a pre-response hook when the middleware has created the hook list (src/cluster_erp/server/dependencies.py:188-190).
5. Yields the session to the route, then calls _commit_once as a fallback for contexts without the middleware (src/cluster_erp/server/dependencies.py:191-192).
6. On any exception, marks the hook as done and rolls back (src/cluster_erp/server/dependencies.py:193-196).
7. Resets the context variables (src/cluster_erp/server/dependencies.py:197-200).

_commit_once commits and then emits the events queued for after the commit (src/cluster_erp/server/dependencies.py:171-176, src/cluster_erp/server/dependencies.py:203-222).

Commit timing¶

The sequence diagram shows when the commit happens.

sequenceDiagram
 participant C as Client
 participant M as CommitBeforeResponseMiddleware
 participant R as Route
 participant S as get_db_session
 participant DB as PostgreSQL
 C->>M: HTTP request
 M->>M: state.pre_response_hooks = []
 M->>R: call application
 R->>S: Depends(get_db_session)
 S->>S: register _commit_once
 S-->>R: session
 R->>DB: INSERT or UPDATE (flush)
 R-->>M: http.response.start
 M->>S: run hook _commit_once
 S->>DB: COMMIT
 M-->>C: response

In prose: the middleware creates an empty hook list on the request state. The session dependency adds _commit_once to it. When the route's response starts, the middleware runs the hooks before it sends the first message (src/cluster_erp/server/middleware.py:41-57). The commit is therefore complete before the client receives the status code. A commit failure becomes an error response.

Request context variables¶

Sessions in EventBus handlers¶

handler_session(database_for) yields the request session when current_request_session is set, flushes it, and does not commit, because the request commits. Outside a request it opens a session on database_for() and commits it when the block ends without an exception (src/cluster_erp/core/handler_session.py:25-37). A comment in core/database.py states that a session belongs to one task; code in another task must open its own session (src/cluster_erp/core/database.py:34-36).

Background work started by a request¶

get_session_factory returns the session factory of the request's tenant database, or the default one, for work a request starts in the background (src/cluster_erp/server/dependencies.py:233-244).

Why it is built this way¶

• The CommitBeforeResponseMiddleware docstring states the reason for the commit timing: FastAPI finalises yield dependencies after the response is sent, so a client could read a record before its insert committed (src/cluster_erp/server/middleware.py:26-36).
• The request_context docstring states why the actor travels in context variables: flush events run deep in the write path where the user is not available, and the context avoids module-to-module calls (src/cluster_erp/core/request_context.py:1-9).
• A comment states why plan_cache_mode is forced: generic plans turned trigram searches into sequential scans on some connections (src/cluster_erp/core/database.py:40-49).

Rules and boundaries¶

• Routes get their session from get_db_session, not from Database.session_factory directly. Whether every route follows this is not determined from code; it was not audited for this page.
• A failed request never commits from the hook: the exception path sets done = True before the rollback (src/cluster_erp/server/dependencies.py:193-196).
• The project's written engineering rules name PostgreSQL with asyncpg and Alembic migrations. The engine URL uses postgresql+asyncpg (src/cluster_erp/config/settings.py:838-843).

Interaction with other parts¶

• Change history reads the actor and deactivation context at flush time.
• Request pipeline and middleware places CommitBeforeResponseMiddleware in the stack.
• Event bus handlers use handler_session and request_database.
• Migrations are documented in database migrations.

Limits and open points¶

• get_language reads only the first two letters of Accept-Language and defaults to en (src/cluster_erp/server/dependencies.py:247-249). Which routes use it was not inventoried for this page.

Terms introduced¶

Related pages¶

• Platform core
• Change history
• Request pipeline and middleware
• Multi-tenancy
• Settings reference

Verified against commit 92f8051 on 2026-11-13.