Add Database Models and Repositories for Notification Rules and Channels
- Dominant language
- Python
- Stars
- 670
- Forks
- 183
- Avg merge
- 17h 7m
- Merged PRs (30d)
- 358
Description
## User Story
As a Backend.AI administrator, I want to store notification rules and channel configurations in the database, so that I can dynamically manage notification routing without code deployment.
## Context
This story implements the database layer for Notification Center, providing persistent storage for notification rules, channel configurations (webhook, SMTP, Slack), and notification history.
## Database Schema
### 1. NotificationChannel
Stores channel configurations (where/how to send):
```python
class NotificationChannel(Base):
__tablename__ = "notification_channels"
id = sa.Column(UUID, primary_key=True, default=uuid4)
name = sa.Column(sa.String(255), nullable=False)
description = sa.Column(sa.Text)
# Channel type: webhook, smtp, slack, discord
channel_type = sa.Column(sa.String(50), nullable=False)
# Type-specific configuration (JSONB)
config = sa.Column(JSONB, nullable=False)
# Examples:
# webhook: {url, method, headers, auth, retry}
# smtp: {host, port, from, tls, auth}
# slack: {webhook_url, channel, username, icon}
# Status
enabled = sa.Column(sa.Boolean, default=True)
# Metadata
created_by = sa.Column(UUID, sa.ForeignKey("users.id"))
created_at = sa.Column(sa.DateTime(timezone=True), default=utcnow)
updated_at = sa.Column(sa.DateTime(timezone=True), onupdate=utcnow)
```
### 2. NotificationRule
Defines event → channel routing with template:
```python
class NotificationRule(Base):
__tablename__ = "notification_rules"
id = sa.Column(UUID, primary_key=True, default=uuid4)
name = sa.Column(sa.String(255), nullable=False)
description = sa.Column(sa.Text)
# Event matching
event_type = sa.Column(sa.String(255), nullable=False)
event_filter = sa.Column(JSONB) # Optional: filter conditions
# Routing
channel_id = sa.Column(UUID, sa.ForeignKey("notification_channels.id"))
# Message template (Jinja2)
message_template = sa.Column(sa.Text, nullable=False)
# Priority and control
priority = sa.Column(sa.Integer, default=0)
enabled = sa.Column(sa.Boolean, default=True)
# Metadata
created_by = sa.Column(UUID, sa.ForeignKey("users.id"))
created_at = sa.Column(sa.DateTime(timezone=True), default=utcnow)
updated_at = sa.Column(sa.DateTime(timezone=True), onupdate=utcnow)
# Relationship
channel = relationship("NotificationChannel")
```
### 3. NotificationLog (Optional, for history)
```python
class NotificationLog(Base):
__tablename__ = "notification_logs"
id = sa.Column(UUID, primary_key=True, default=uuid4)
event_id = sa.Column(sa.String(255), nullable=False, index=True)
event_type = sa.Column(sa.String(255), nullable=False)
rule_id = sa.Column(UUID, sa.ForeignKey("notification_rules.id"))
channel_id = sa.Column(UUID, sa.ForeignKey("notification_channels.id"))
# Delivery status
status = sa.Column(sa.String(50)) # pending, sent, failed
error_message = sa.Column(sa.Text)
retry_count = sa.Column(sa.Integer, default=0)
# Timestamps
created_at = sa.Column(sa.DateTime(timezone=True), default=utcnow)
sent_at = sa.Column(sa.DateTime(timezone=True))
```
## Migration Script
Create Alembic migration for all tables with proper indexes.
## Repository Layer
Implement repository pattern for data access:
```python
class NotificationChannelRepository:
async def create(self, channel: NotificationChannel) -> NotificationChannel
async def get_by_id(self, channel_id: UUID) -> NotificationChannel | None
async def list_enabled(self) -> list[NotificationChannel]
async def update(self, channel_id: UUID, updates: dict) -> NotificationChannel
async def delete(self, channel_id: UUID) -> None
class NotificationRuleRepository:
async def create(self, rule: NotificationRule) -> NotificationRule
async def get_by_id(self, rule_id: UUID) -> NotificationRule | None
async def find_by_event_type(self, event_type: str) -> list[NotificationRule]
async def list_enabled(self) -> list[NotificationRule]
async def update(self, rule_id: UUID, updates: dict) -> NotificationRule
async def delete(self, rule_id: UUID) -> None
```
## Acceptance Criteria
- [ ] Database models defined with proper types
- [ ] Alembic migration created and tested
- [ ] JSONB validation for channel config
- [ ] Repository layer with CRUD operations
- [ ] Indexes on event_type and enabled columns
- [ ] Foreign key constraints properly set
- [ ] Unit tests for repositories
- [ ] Sample data for testing
## Related Issues
Epic: BA-302
JIRA Issue: BA-2862
Contributor guide
Assessment
This issue has not been assessed yet.