Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
Onyx utilizes a relational database system based on PostgreSQL to manage persistent application state, including user data, connector configurations, and task metadata. The project employs SQLAlchemy as the Object Relational Mapper (ORM) and Alembic for handling database schema migrations. This ensures that the database schema remains synchronized with the Python data models across different deployments, including Community and Enterprise editions.
The database architecture is designed to support both single-tenant and multi-tenant (Enterprise) environments, with dedicated workers for background processing that interact heavily with the relational state.
Sources: AGENTS.md:95-100, CLAUDE.md:95-100, CONTRIBUTING.md:120-125
Migration Management with Alembic
Alembic serves as the primary tool for evolving the database schema. Migrations are managed as "revisions" which define how to upgrade the schema to a newer version or downgrade it to a previous state.
Migration Lifecycle
The development workflow for schema changes involves creating a revision file, manually populating the logic, and applying it to the local or production database.
This diagram illustrates the standard workflow for applying schema changes in the Onyx backend.
Sources: backend/alembic/README.md:1-25, CONTRIBUTING.md:194-200
Key Alembic Operations
| Task | Command | Description |
|---|---|---|
| Create Migration | alembic revision -m "description" | Generates a new migration script in the versions directory. |
| Apply Migrations | alembic upgrade head | Runs all pending migrations to reach the latest schema version. |
| Undo Migrations | alembic downgrade -X | Reverts the last X number of applied migrations. |
| Multi-tenant Upgrade | alembic -n schema_private upgrade head | Specifically targets Enterprise Edition private schemas. |
Sources: backend/alembic/README.md:7-18, AGENTS.md:120-135, CLAUDE.md:120-135
Multi-tenant Migrations
For Enterprise Edition deployments, Onyx supports complex migration strategies across multiple tenants. These operations can be filtered by specific tenant IDs or alphabetical ranges.
- Upgrade All Tenants:
alembic -x upgrade_all_tenants=true upgrade head - Specific Range: Use
-x tenant_range_start=Xand-x tenant_range_end=Yto process a subset of tenants based on alphabetical position. - Error Handling: The
-x continue=trueflag allows batch operations to proceed even if a specific tenant migration fails.
Sources: backend/alembic/README.md:27-52
Data Modeling and Structures
Database models are defined using SQLAlchemy in models.py files, typically located within backend/onyx/db/ or backend/ee/onyx/db/.
Relational Schema Components
The system tracks several core entities, with a focus on connector configurations and knowledge graph structures.
Connector & Credential Models
Connectors define the source of data, while credentials store the access tokens or secrets required to fetch that data.
| Component | Description |
|---|---|
| Connector | Stores source type, input type, refresh frequency, and pruning schedules. |
| Credential | Stores sensitive JSON data (e.g., OAuth tokens) for external API access. |
| Access Type | Defines if the connector data is public or restricted to specific groups. |
Sources: web/src/lib/connectors/connectors.tsx:1037-1065, AGENTS.md:20-25
Knowledge Graph Entities
The system uses specific table structures to represent entities and their relationships, particularly for the Knowledge Graph (KG) features.
The ER diagram shows the schema structure used for the Knowledge Graph, where entities are linked via a dedicated relationship table.
Sources: backend/onyx/prompts/kg_prompts.py:530-575
Engineering Best Practices for DB Operations
Onyx enforces strict guidelines to ensure performance and prevent data corruption:
- Centralization: All database operations MUST be placed under
backend/onyx/dborbackend/ee/onyx/db. Ad-hoc queries outside these directories are prohibited. - Resource Management: Avoid holding database sessions for extended periods to prevent connection pool exhaustion.
- Lazy Loading: Developers are cautioned against lazy loading in SQLAlchemy, as it can lead to N+1 query problems and hidden dependencies. Explicit
joinedloadorselectinloadis preferred. - Async Constraints: Event loops and async code should be handled carefully; making existing async code synchronous is encouraged where it improves clarity without performance loss.
Sources: AGENTS.md:25-30, CONTRIBUTING.md:420-435, CLAUDE.md:25-30
Task State and Persistence
The database also tracks the state of background workers (Celery). Workers like docprocessing upsert documents to PostgreSQL before they are indexed into the Vespa vector database. This ensures that metadata and document status are always available in the relational store.
Sources: AGENTS.md:55-65, CLAUDE.md:55-65
Summary
The Database Schema & Migrations system in Onyx provides a robust framework for managing evolving data requirements. By utilizing SQLAlchemy and Alembic, the project maintains a clear separation between logical models and physical schema, while supporting advanced multi-tenant scaling for Enterprise environments. Accurate migration management is critical for the stability of background workers and the indexing pipeline.