Relational Persistence (PostgreSQL)

Relevant source files

The following files were used as context for generating this wiki page:

• AGENTS.md
• README.md
• pyproject.toml
• src/osu_server/repositories/sqlalchemy/unit_of_work.py
• src/osu_server/repositories/sqlalchemy/queries/user_stats.py
• tests/integration/test_sqlalchemy_score_repository.py
• tests/unit/repositories/sqlalchemy/test_beatmap_performance_best_command_repository.py

The Relational Persistence layer in Athena provides durable storage for domain entities and aggregates using PostgreSQL. It is architected as part of a layered modular monolith, utilizing hexagonal adapters to separate business logic from technical implementation details. Persistence is handled through SQLAlchemy 2.0 with asynchronous drivers to ensure high-performance I/O operations.

Sources: AGENTS.md:143-154, README.md:27-38

Architectural Overview

Athena employs a strict separation between command-side mutations and query-side reads, following a CQRS-inspired pattern. Persistence is structured into three primary components:

• Unit of Work (UoW): Manages transaction boundaries and ensures atomicity for state-changing operations.
• Command Repositories: Handle create, update, and delete operations within a UoW context.
• Query Repositories: Provide read-optimized, read-only access to data without requiring a Unit of Work.

Sources: AGENTS.md:143-154, README.md:33-38

Dependency Flow

The following diagram illustrates how the persistence layer fits into the overall system architecture. Note that services depend on repository interfaces, while concrete SQLAlchemy implementations reside in the infrastructure/repository layers.

The diagram shows the dependency flow from high-level services down to the physical PostgreSQL database.
Sources: AGENTS.md:113-120, README.md:33-38

Tech Stack & Configuration

The persistence layer is built on a modern Python asynchronous stack.

Component	Technology	Description
Database	PostgreSQL	Primary relational storage engine.
ORM	SQLAlchemy 2.0	Asynchronous ORM for Python.
Driver	asyncpg	High-performance async PostgreSQL driver.
Migrations	Alembic	Database schema versioning and migration tool.
Configuration	Pydantic Settings	Environment-based configuration management.

Sources: AGENTS.md:65-71, README.md:54-58, pyproject.toml:9-12

Environment Variables

Configuration is primarily managed via environment variables.

• DATABASE_URL: The connection string (e.g., postgresql+asyncpg://postgres:postgres@localhost:5432/athena).
• ENVIRONMENT: Set to development, test, or production.

Sources: README.md:79-82, tests/unit/transports/test_health.py:276-281

Command Persistence (Unit of Work)

Command-side persistence is governed by the SQLAlchemyUnitOfWork. This component ensures that multiple repository operations occur within a single ACID transaction. Command repositories do not commit or roll back themselves; instead, they receive a session managed by the Unit of Work.

Sources: AGENTS.md:143-154, src/osu_server/repositories/sqlalchemy/unit_of_work.py

Persistence Sequence

The following sequence diagram demonstrates a typical score submission flow utilizing the Unit of Work.

This diagram shows the transactional boundary managed by the Unit of Work during a score creation event.
Sources: AGENTS.md:143-154, tests/integration/test_sqlalchemy_score_repository.py:92-105

Query Persistence

Query repositories are optimized for read performance and do not use the Unit of Work. They utilize a SQLAlchemyQuerySessionFactory to generate short-lived, read-only sessions. These repositories often perform complex aggregations and joins to support compatibility views (e.g., legacy osu! getscores).

Sources: AGENTS.md:143-154, src/osu_server/repositories/sqlalchemy/queries/user_stats.py:38-42

User Statistics Queries

The SQLAlchemyUserStatsQueryRepository provides a concrete example of read-side persistence. It aggregates data from multiple tables including scores, users, and user_stats.

Key Query Features:

• Rank Calculation: Uses window-function style logic to determine global rank by comparing pp values across visible users.
• Aggregations: Calculates total score, ranked score, and play counts using func.sum and func.count.
• Permissions Filtering: Filters leaderboard visibility based on bitwise permission masks stored in the roles table.

Sources: src/osu_server/repositories/sqlalchemy/queries/user_stats.py:127-160, src/osu_server/repositories/sqlalchemy/queries/user_stats.py:202-243

Data Models and Schema

The project uses SQLAlchemy Declarative Models. Core models identified in the repository include:

Model	Table	Description
UserModel	users	Core user identity and account data.
ScoreModel	scores	Records of individual play performances.
CurrentUserStatsModel	current_user_stats	Cached/Projected statistics for users per ruleset.
BeatmapPerformanceBestModel	beatmap_performance_bests	Tracks the highest PP play for a user on a specific map.
RoleModel	roles	Defines authorization bundles and permissions.

Sources: src/osu_server/repositories/sqlalchemy/queries/user_stats.py:31-35, tests/unit/repositories/sqlalchemy/test_beatmap_performance_best_command_repository.py:270-291

Advanced Persistence Patterns

• Upsert with "Better" Guard: The performance best repository implements an upsert_if_better logic. It uses PostgreSQL ON CONFLICT with a WHERE clause to only update rows if the new PP, accuracy, or score is superior to the existing record.
• Advisory Locks: Athena uses PostgreSQL advisory locks (pg_advisory_xact_lock) to coordinate concurrent updates to specific performance scopes, preventing race conditions during heavy processing.

Sources: tests/unit/repositories/sqlalchemy/test_beatmap_performance_best_command_repository.py:68-88, tests/unit/repositories/sqlalchemy/test_beatmap_performance_best_command_repository.py:171-180

Database Migrations

Alembic is used to manage the PostgreSQL schema. Migrations are stored in the alembic/ directory and are generated automatically based on changes to SQLAlchemy models.

Common Commands:

# Apply migrations to latest version
alembic upgrade head

# Generate a new migration script
alembic revision --autogenerate -m "description"

Sources: AGENTS.md:73-77, README.md:112-117

Conclusion

Relational Persistence in Athena is designed for high consistency and scalability. By separating command and query concerns and leveraging asynchronous SQLAlchemy with PostgreSQL-specific features like advisory locks and conditional upserts, the system ensures data integrity for core osu! server features such as scoring, leaderboards, and user management.