Database Design¶
Referenced Files in This Document
Table of Contents¶
Introduction¶
This document describes the database design for Nexo, focusing on the SQLAlchemy ORM schema architecture and its integration with Alembic migrations. It details core entities (Decision, User, ActionLog, History, Status, PushSubscription), their relationships, constraints, indexes, and validation rules implemented at the model level. It also covers data access patterns, query optimization strategies, connection pooling, transaction management, and error handling practices used across the application.
Project Structure¶
The database layer is organized under apps/service/shared with:
-
models: SQLAlchemy declarative base classes for each entity
-
db.py: engine, session factory, and metadata configuration
-
alembic/versions: migration scripts that materialize the schema
graph TB
subgraph "Service Layer"
routes["API Routes"]
services["Business Logic"]
end
subgraph "Shared Data Layer"
models["SQLAlchemy Models"]
db_cfg["DB Config<br/>Engine & SessionFactory"]
end
subgraph "Database"
db[(Relational DB)]
end
routes --> services
services --> models
models --> db_cfg
db_cfg --> db
[No sources needed since this diagram shows conceptual workflow, not actual code structure]
Section sources
Core Components¶
This section summarizes the primary entities and their responsibilities:
-
Decision: Captures processing decisions and outcomes for media items. Includes fields to record type, confidence, source, and result flags.
-
User: Authentication and authorization records, including credentials and role attributes.
-
ActionLog: System activity tracking for auditability and diagnostics.
-
History: Audit trail of significant state changes or user actions.
-
Status: Application-level status and operational state.
-
PushSubscription: Web Push notification subscription endpoints and metadata.
Key implementation notes:
-
All models are defined using SQLAlchemy declarative base.
-
Primary keys are explicitly declared; foreign keys reference other tables where applicable.
-
Indexes are added to frequently queried columns to optimize lookups.
-
Validation is enforced via SQLAlchemy Column constraints (e.g., NOT NULL, unique) and Python-side validators where necessary.
Section sources
Architecture Overview¶
The ORM layer sits between business logic and the relational database. Alembic manages schema evolution through versioned migration files. The shared db module configures the engine and session factory consumed by all components.
classDiagram
class Base {
+metadata
+declarative_base()
}
class Engine {
+url
+pool_size
+max_overflow
+echo
}
class SessionFactory {
+bind(Engine)
+scoped_session()
}
class Decision {
+id
+type
+confidence
+source
+result
+created_at
}
class User {
+id
+username
+password_hash
+role
+is_active
+created_at
}
class ActionLog {
+id
+actor_id
+action
+details
+created_at
}
class History {
+id
+entity_type
+entity_id
+change_summary
+created_at
}
class Status {
+key
+value
+updated_at
}
class PushSubscription {
+id
+endpoint
+keys_p256dh
+keys_auth
+created_at
}
Base <|-- Decision
Base <|-- User
Base <|-- ActionLog
Base <|-- History
Base <|-- Status
Base <|-- PushSubscription
Engine --> SessionFactory : "creates"
SessionFactory --> Decision : "queries"
SessionFactory --> User : "authenticates"
SessionFactory --> ActionLog : "writes logs"
SessionFactory --> History : "records history"
SessionFactory --> Status : "reads/writes status"
SessionFactory --> PushSubscription : "manages subscriptions"
Diagram sources
Detailed Component Analysis¶
Decision Entity¶
Purpose: Records processing decisions for media items, including decision type, confidence score, source system, and outcome flags.
Schema highlights:
-
Primary key: id (auto-increment integer)
-
type: string field indicating decision category
-
confidence: numeric field representing confidence score
-
source: string field identifying the origin of the decision
-
result: boolean or enum-like field capturing the final outcome
-
created_at: timestamp for creation time
Indexes:
-
Index on type for filtering decisions by category
-
Optional index on created_at for time-based queries
Validation:
-
NOT NULL constraints on essential fields
-
Confidence range validation at the application layer if required
Relationships:
- Typically referenced by History entries when a decision triggers an audit event
Section sources
User Entity¶
Purpose: Stores authentication and authorization information for users.
Schema highlights:
-
Primary key: id (auto-increment integer)
-
username: unique string identifier
-
password_hash: hashed password string
-
role: string or enum defining permissions
-
is_active: boolean flag for account status
-
created_at: timestamp for account creation
Indexes:
-
Unique index on username
-
Optional index on role for admin filtering
Validation:
-
NOT NULL constraints on username and password_hash
-
Password hashing handled at the application layer before persistence
Relationships:
- Referenced by ActionLog.actor_id to attribute actions to users
Section sources
ActionLog Entity¶
Purpose: Tracks system activities for auditing and diagnostics.
Schema highlights:
-
Primary key: id (auto-increment integer)
-
actor_id: foreign key referencing User.id
-
action: string describing the performed action
-
details: text/json payload with contextual information
-
created_at: timestamp for when the log was recorded
Indexes:
-
Index on actor_id for user-specific logs
-
Index on created_at for chronological queries
Validation:
- NOT NULL constraints on action and created_at
Relationships:
- Foreign key to User for actor attribution
Section sources
History Entity¶
Purpose: Maintains an audit trail of significant state changes or user actions.
Schema highlights:
-
Primary key: id (auto-increment integer)
-
entity_type: string indicating the affected domain entity
-
entity_id: string or integer referencing the specific entity instance
-
change_summary: text describing the change
-
created_at: timestamp for when the change occurred
Indexes:
-
Composite index on (entity_type, entity_id) for efficient retrieval
-
Index on created_at for timeline views
Validation:
- NOT NULL constraints on entity_type, entity_id, and change_summary
Relationships:
- Logical references to various entities via entity_type and entity_id
Section sources
Status Entity¶
Purpose: Holds application-level status and operational state.
Schema highlights:
-
key: unique string identifying the status item
-
value: text/json payload containing current state
-
updated_at: timestamp for last update
Indexes:
- Unique index on key
Validation:
- NOT NULL constraints on key and value
Relationships:
- Standalone configuration-like table without foreign keys
Section sources
PushSubscription Entity¶
Purpose: Manages Web Push notification subscriptions.
Schema highlights:
-
Primary key: id (auto-increment integer)
-
endpoint: unique string URL for push notifications
-
keys_p256dh: string containing public encryption key
-
keys_auth: string containing auth secret
-
created_at: timestamp for subscription creation
Indexes:
- Unique index on endpoint
Validation:
- NOT NULL constraints on endpoint and keys
Relationships:
- Standalone table without foreign keys
Section sources
Entity Relationship Diagram¶
erDiagram
USER {
int id PK
string username UK
string password_hash
string role
boolean is_active
timestamp created_at
}
ACTION_LOG {
int id PK
int actor_id FK
string action
text details
timestamp created_at
}
HISTORY {
int id PK
string entity_type
string entity_id
text change_summary
timestamp created_at
}
DECISION {
int id PK
string type
float confidence
string source
boolean result
timestamp created_at
}
STATUS {
string key PK
text value
timestamp updated_at
}
PUSH_SUBSCRIPTION {
int id PK
string endpoint UK
string keys_p256dh
string keys_auth
timestamp created_at
}
USER ||--o{ ACTION_LOG : "has many"
Diagram sources
Dependency Analysis¶
The ORM models depend on the shared db configuration for engine and session management. Migrations define the concrete schema evolution.
graph LR
A["db.py<br/>Engine & SessionFactory"] --> B["Models<br/>Decision, User, ActionLog, History, Status, PushSubscription"]
C["Alembic<br/>001_initial_schema.py"] --> D["Schema Tables"]
E["Alembic<br/>002_add_decision_type.py"] --> D
B --> D
Diagram sources
Section sources
Performance Considerations¶
-
Indexing strategy:
-
Add indexes on high-cardinality filter columns such as Decision.type, ActionLog.actor_id, History.entity_type+entity_id, and timestamps for time-range queries.
-
Use composite indexes for common multi-column filters (e.g., entity_type and entity_id).
-
Query optimization:
-
Prefer selective WHERE clauses leveraging indexed columns.
-
Use pagination for large result sets (LIMIT/OFFSET or keyset pagination).
-
Avoid SELECT *; fetch only required columns.
-
Connection pooling:
-
Configure pool_size and max_overflow appropriately based on expected concurrency.
-
Enable echo selectively during development for SQL logging; disable in production.
-
Transaction management:
-
Wrap related writes in explicit transactions to ensure atomicity.
-
Commit early and reuse sessions per request to reduce overhead.
-
Error handling:
-
Catch SQLAlchemy IntegrityError for constraint violations and map to user-friendly messages.
-
Log exceptions with context (actor_id, entity_type, entity_id) for diagnostics.
[No sources needed since this section provides general guidance]
Troubleshooting Guide¶
Common issues and resolutions:
-
Integrity errors:
-
Symptom: Duplicate key or foreign key violation.
-
Resolution: Validate inputs before write; handle IntegrityError and return appropriate HTTP status codes.
-
Slow queries:
-
Symptom: High latency on list or filter endpoints.
-
Resolution: Inspect execution plans; add missing indexes; refine filters; paginate results.
-
Session leaks:
-
Symptom: Connection exhaustion under load.
-
Resolution: Ensure sessions are closed or scoped correctly per request lifecycle.
-
Migration conflicts:
-
Symptom: Alembic head mismatch or downgrade failures.
-
Resolution: Align local migrations with remote; review migration diffs; rebase branches carefully.
Section sources
Conclusion¶
Nexo’s database design centers on a clear set of core entities modeled with SQLAlchemy and evolved via Alembic. The schema emphasizes auditability (ActionLog, History), operational state (Status), and extensibility (PushSubscription). Proper indexing, connection pooling, transaction scoping, and robust error handling are critical to maintaining performance and reliability. Future enhancements should continue to refine indexes based on observed query patterns and expand validation rules at both the model and API layers.