Data models and schema

Overview of persistence and API contracts in notice-reminders: Tortoise models, Pydantic schemas, and how they line up.

The data layer is implemented using Tortoise ORM within the notice-reminders application. Models are defined under app/models and registered with FastAPI via app/core/database.py. API schemas are defined under app/schemas and are used to validate and serialize requests and responses.

graph TB
subgraph "Core"
DB["Tortoise Config<br/>register_database()"]
end
subgraph "Models"
U["User"]
C["Course"]
A["Announcement"]
S["Subscription"]
N["Notification"]
NC["NotificationChannel"]
OTP["OtpCode"]
RT["RefreshToken"]
end
subgraph "Schemas"
SU["UserResponse"]
SC["CourseResponse"]
SA["AnnouncementResponse"]
SS["SubscriptionResponse"]
SN["NotificationResponse"]
end
DB --> U
DB --> C
DB --> A
DB --> S
DB --> N
DB --> NC
DB --> OTP
DB --> RT
U --> SU
C --> SC
A --> SA
S --> SS
N --> SN

This section documents each database entity, including fields, data types, primary keys, foreign keys, indexes, and constraints. It also explains how the models are registered and initialized.

• User

• Fields: id (IntField, pk), email (CharField, unique, indexed), name (CharField, nullable), telegram_id (CharField, unique, nullable), is_active (BooleanField), created_at (DatetimeField), updated_at (DatetimeField)

• Indexes: email, telegram_id

• Constraints: unique(email), unique(telegram_id)

• Table: users

• Course

• Fields: id (IntField, pk), code (CharField, unique, indexed), title (CharField), url (CharField), instructor (CharField), institute (CharField), nc_code (CharField), created_at (DatetimeField), updated_at (DatetimeField)

• Indexes: code

• Constraints: unique(code)

• Table: courses

• Announcement

• Fields: id (IntField, pk), course (ForeignKey to Course, related_name: announcements), title (CharField), date (CharField), content (TextField), fetched_at (DatetimeField)

• Table: announcements

• Subscription

• Fields: id (IntField, pk), user (ForeignKey to User, related_name: subscriptions), course (ForeignKey to Course, related_name: subscriptions), created_at (DatetimeField), is_active (BooleanField)

• Constraints: unique(user, course)

• Table: subscriptions

• NotificationChannel

• Fields: id (IntField, pk), user (ForeignKey to User, related_name: notification_channels), channel (CharField), address (CharField), is_active (BooleanField), created_at (DatetimeField)

• Constraints: unique(user, channel, address)

• Table: notification_channels

• Notification

• Fields: id (IntField, pk), user (ForeignKey to User, related_name: notifications), subscription (ForeignKey to Subscription, related_name: notifications), announcement (ForeignKey to Announcement, related_name: notifications), channel (ForeignKey to NotificationChannel, nullable, related_name: notifications), sent_at (DatetimeField), is_read (BooleanField)

• Table: notifications

• OtpCode

• Fields: id (IntField, pk), email (CharField, indexed), code (CharField), expires_at (DatetimeField), is_used (BooleanField), created_at (DatetimeField)

• Indexes: email

• Table: otp_codes

• RefreshToken

• Fields: id (IntField, pk), user (ForeignKey to User, related_name: refresh_tokens, on_delete=CASCADE), token (CharField, unique, indexed), expires_at (DatetimeField), is_revoked (BooleanField), created_at (DatetimeField)

• Indexes: token

• Constraints: unique(token)

• Table: refresh_tokens

The data model architecture follows a relational design with explicit foreign key relationships. Tortoise ORM is configured to auto-generate schemas when the SQLite file does not exist and registers all models with the FastAPI application.

erDiagram
USERS {
int id PK
varchar email UK
varchar name
varchar telegram_id UK
boolean is_active
datetime created_at
datetime updated_at
}
COURSES {
int id PK
varchar code UK
varchar title
varchar url
varchar instructor
varchar institute
varchar nc_code
datetime created_at
datetime updated_at
}
ANNOUNCEMENTS {
int id PK
int course_id FK
varchar title
varchar date
text content
datetime fetched_at
}
SUBSCRIPTIONS {
int id PK
int user_id FK
int course_id FK
boolean is_active
datetime created_at
}
NOTIFICATION_CHANNELS {
int id PK
int user_id FK
varchar channel
varchar address
boolean is_active
datetime created_at
}
NOTIFICATIONS {
int id PK
int user_id FK
int subscription_id FK
int announcement_id FK
int channel_id FK
datetime sent_at
boolean is_read
}
OTP_CODES {
int id PK
varchar email
varchar code
datetime expires_at
boolean is_used
datetime created_at
}
REFRESH_TOKENS {
int id PK
int user_id FK
varchar token UK
datetime expires_at
boolean is_revoked
datetime created_at
}
USERS ||--o{ SUBSCRIPTIONS : "has"
COURSES ||--o{ SUBSCRIPTIONS : "has"
USERS ||--o{ NOTIFICATION_CHANNELS : "has"
USERS ||--o{ NOTIFICATIONS : "has"
SUBSCRIPTIONS ||--o{ NOTIFICATIONS : "has"
ANNOUNCEMENTS ||--o{ NOTIFICATIONS : "has"
NOTIFICATION_CHANNELS ||--o{ NOTIFICATIONS : "has"
USERS ||--o{ REFRESH_TOKENS : "has"

User model#

• Purpose: Stores user account information and profile metadata.
• Key constraints: unique(email), unique(telegram_id)
  • Indexes: email, telegram_id
• Related models: Subscriptions, NotificationChannels, Notifications, RefreshTokens

classDiagram
class User {
+int id
+string email
+string name
+string telegram_id
+boolean is_active
+datetime created_at
+datetime updated_at
}
class Subscription
class NotificationChannel
class Notification
class RefreshToken
User "1" o-- "*" Subscription : "has"
User "1" o-- "*" NotificationChannel : "has"
User "1" o-- "*" Notification : "has"
User "1" o-- "*" RefreshToken : "has"

Course model#

• Purpose: Represents MOOC courses with metadata.
• Key constraints: unique(code)
  • Indexes: code
• Related models: Announcements, Subscriptions

classDiagram
class Course {
+int id
+string code
+string title
+string url
+string instructor
+string institute
+string nc_code
+datetime created_at
+datetime updated_at
}
class Announcement
class Subscription
Course "1" o-- "*" Announcement : "has"
Course "1" o-- "*" Subscription : "has"

Announcement model#

• Purpose: Stores scraped course announcements with course linkage.
• Related model: Notifications
• Notes: fetched_at indicates when the record was inserted after scraping.

classDiagram
class Announcement {
+int id
+int course_id
+string title
+string date
+text content
+datetime fetched_at
}
class Notification
Announcement "1" o-- "*" Notification : "has"

Subscription model#

• Purpose: Links users to courses they follow.
• Unique constraint: (user, course)
• Related models: Notifications

classDiagram
class Subscription {
+int id
+int user_id
+int course_id
+boolean is_active
+datetime created_at
}
class Notification
Subscription "1" o-- "*" Notification : "has"

NotificationChannel model#

• Purpose: Defines per-user channels (e.g., Telegram) for delivery.
• Unique constraint: (user, channel, address)
• Related model: Notifications

classDiagram
class NotificationChannel {
+int id
+int user_id
+string channel
+string address
+boolean is_active
+datetime created_at
}
class Notification
NotificationChannel "1" o-- "*" Notification : "has"

Notification model#

• Purpose: Records sent notifications with optional channel association and read state.
• Foreign keys: user, subscription, announcement, channel (nullable)
• Related models: User, Subscription, Announcement, NotificationChannel

classDiagram
class Notification {
+int id
+int user_id
+int subscription_id
+int announcement_id
+int channel_id
+datetime sent_at
+boolean is_read
}
class User
class Subscription
class Announcement
class NotificationChannel
Notification --> User : "belongs to"
Notification --> Subscription : "belongs to"
Notification --> Announcement : "references"
Notification --> NotificationChannel : "optional"

OtpCode model#

• Purpose: Stores OTP codes for email-based authentication.
  • Indexes: email
• Lifecycle: expires_at determines validity; is_used marks consumption.

classDiagram
class OtpCode {
+int id
+string email
+string code
+datetime expires_at
+boolean is_used
+datetime created_at
}

RefreshToken model#

• Purpose: Manages refresh tokens for secure sessions.
  • Constraints: unique(token)
• Behavior: CASCADE delete on user removal; is_revoked flag supports revocation.

classDiagram
class RefreshToken {
+int id
+int user_id
+string token
+datetime expires_at
+boolean is_revoked
+datetime created_at
}
class User
RefreshToken --> User : "belongs to"

The models form a connected relational graph. The following diagram highlights foreign key dependencies and uniqueness constraints.

graph LR
U["User"] --> S["Subscription"]
C["Course"] --> S
U --> N["Notification"]
S --> N
A["Announcement"] --> N
NC["NotificationChannel"] --> N
U --> RT["RefreshToken"]
U --> OTP["OtpCode"]

• Indexes: email and telegram_id on User; code on Course; email on OtpCode; token on RefreshToken. These support frequent lookups and uniqueness checks.
• Unique constraints: reduce duplicate entries and simplify join conditions.
• Auto timestamps: created_at and updated_at enable efficient sorting and filtering.
• Recommendations:
• Add composite indexes for frequently filtered pairs (e.g., user+is_active on Subscription).
• Consider partitioning or soft-deleted views for Notification if volume grows large.
• Use pagination and limit clauses in queries to avoid large result sets.

• Registration and schema generation:
• The database is registered via a configuration that lists all models. If the SQLite file does not exist, schemas are generated automatically.
• Ensure the database URL points to a writable path for SQLite.
• Common issues:
• Integrity errors on unique fields (email, telegram_id, code, token): validate inputs before creation.
• Expiration handling: ensure background jobs or scheduled tasks invalidate expired OTPs and refresh tokens.
• Cascade deletes: removing a user will remove dependent refresh tokens; confirm intended behavior.

Models persist, schemas validate, services decide. Keep those roles from collapsing into the routers.

API schemas and validation rules#

• UserResponse mirrors User fields and enables ORM-to-Pydantic conversion.
• CourseResponse mirrors Course fields.
• AnnouncementResponse exposes course_id for clarity.
• SubscriptionCreate accepts course_code; SubscriptionResponse exposes user_id and course_id.
• NotificationResponse exposes channel_id as optional.

These schemas enforce type safety and validation for incoming/outgoing data.

Data access patterns and business rules#

• User CRUD: update profile fields; toggle is_active; link Telegram ID.
• Course lookup: filter by code for subscription creation.
• Subscription lifecycle: create unique (user, course); toggle is_active; cascade reads into notifications.
• Notification lifecycle: mark is_read; associate channel for delivery; track sent_at.
• OTP lifecycle: generate with expiry; mark is_used upon verification; reject expired codes.
• Refresh token lifecycle: generate with expiry; revoke by setting is_revoked; enforce CASCADE on user deletion.

Migration considerations#

• Initial schema generation occurs when the SQLite file is missing; subsequent runs reuse existing tables.
• For production databases (PostgreSQL/MySQL), use Tortoise migrations to evolve schema safely.
• When altering unique constraints or indexes, plan downtime or use online DDL where supported.
• Back up before major schema changes; test rollback procedures.