Data Model

The entities the bot reads and writes, their relationships, and the ownership rules. The schema is owned by the backend (abitly-api-v2); these models are a read-mostly mirror — see ADR 0006. Models: src/abitly_bot/db/models/.

Entity–relationship diagram

erDiagram
    telegram_users ||--o{ telegram_user_monitoring_offers : tracks
    telegram_users ||--o{ monitoring_open_days : attends
    telegram_users }o--o| users : "linked via web_user_id"
    telegram_users }o--o{ regions : "notif. filter (exists)"
    telegram_users }o--o{ specialities : "notif. filter (PENDING)"
    telegram_users }o--o{ universities : "notif. filter (PENDING)"

    offers ||--o{ telegram_user_monitoring_offers : "tracked by"
    offers ||--o{ offer_applicants : "applicant pool"
    offers }o--|| specialities : "for"
    offers }o--|| universities : "at"
    offers }o--o| faculties : "in"

    open_days ||--o{ monitoring_open_days : "attended by"
    open_days }o--o| universities : "hosted by"
    open_days }o--o| regions : "in"
    open_days }o--o| specialities : "about"

    specialities }o--o| specialty_coefficients : "scored with"
    universities }o--|| regions : "located in"
    faculties }o--|| universities : "part of"

    users ||--o{ users_grades : has
    subjects ||--o{ users_grades : "graded in"

    telegram_users {
        bigint telegram_chat_id PK "private chat id == user id"
        bigint telegram_user_id
        uuid web_user_id FK "→ users.id, nullable (link)"
        bool telegram_notifications_enabled
        datetime last_interacted_at
        string first_name
        string username
        string phone_number
    }
    offers {
        int id PK
        string name
        string type
        int university_id FK
        int faculty_id FK "nullable"
        int speciality_id FK
        int year
        int max_budget_licence "nullable"
        int max_contract_license "nullable"
    }
    offer_applicants {
        bigint id PK
        int offer_id FK
        float total_score
        int quota "nullable; excluded from rank"
        int appliement_priority "nullable"
        int year
        smallint explanation_id "0/2/3 excluded from rank"
    }
    open_days {
        int id PK
        datetime date "nullable"
        int region_id FK "nullable"
        int university_id FK "nullable"
        int speciality_id FK "nullable"
        string city
        bool is_registration_required
        datetime updated_at
    }
    users {
        uuid id PK
        string name
        string email
    }
    users_grades {
        uuid user_id PK,FK
        int subject_id PK,FK
        int grade
    }
    subjects {
        int id PK
        string name
        string name_en
        string subject_type "type: mandatory/optional/additional"
    }
    specialty_coefficients {
        int id PK
        numeric max_coeff
        numeric ukrainian
        numeric mathematics
        numeric creativeCompetition "nullable"
    }

Reference tables faculties, regions, universities, specialities, and table_metadata are shown only where they relate; see the model files for full columns.

Ownership: what the bot reads vs writes

The bot owns no DDL. It writes only the tables that represent a Telegram user’s own state; everything else is read-only.

Table	Bot access	Written by
telegram_users	read + write	bot (upsert, web_user_id, notif. toggle)
telegram_user_monitoring_offers	read + write	bot (track/untrack offers)
monitoring_open_days	read + write	bot (attend open days)
telegram_users_…_filter_* (3 M2M)	read + write	bot (notification filters)
offers, offer_applicants	read-only	backend
open_days	read-only	backend
users, users_grades, subjects	read-only	backend (web profile + grades)
specialities, specialty_coefficients	read-only	backend
universities, faculties, regions	read-only	backend
table_metadata	read-only	backend

Key modelling notes

• telegram_users.telegram_chat_id is the primary key (a BIGINT, not autoincremented). In private chats chat.id == user.id, so the chat id is the bot’s stable handle for a user. Most join tables FK to it.
• web_user_id links a Telegram chat to a web account (users.id, a UUID). It is nullable; a user without a linked account gets no personal score/rank. The bot is the writer for this link (see Runtime Flows).
• camelCase physical columns. The backend was generated by TypeORM, so some columns are camelCase in the DB (e.g. foreignLanguage, creativeCompetition) and some join-table FK columns too (telegramUsersTelegramChatId, regionsId). Models pin the real name with mapped_column("camelCase", ...). Do not “normalise” these — they must match the live schema.
• Loading strategy is deliberate. Large pools (offer_applicants) and the three notification-filter relationships are lazy="raise"; they load only via explicit selectinload in the repo method that needs them. See ADR 0003.

Pending tables (backend prerequisite)

Two of the three notification-filter join tables do not exist in the live database yet (an incomplete backend migration):

Join table	Status
telegram_users_open_days_notification_filter_regions_regions	✅ exists
telegram_users_open_days_notification_filter_specialities_specialities	⛔ pending
telegram_users_open_days_notification_filter_universities_universities	⛔ pending

Until they exist, the filter-dependent flows (/myprofile with filters, /opendayfilters, subscribe_uni_*, /notifyOpenDaysUpdate) will error only when invoked — core flows are unaffected because of the lazy="raise" guard. Definitions: src/abitly_bot/db/models/telegram_user.py:22-44. The TypeORM-generated names must be verified against the live DB before relying on them. Tracking: docs/MIGRATION_STATUS.md (Blocker 2).

Schema-drift safety net

There is no migration tooling in this repo. Instead, tests/integration/test_schema_reflection.py reflects the live schema and asserts every mapped column exists, so drift fails CI rather than production. It requires live-DB access and runs under the integration marker (skipped in the offline gate). See ADR 0006 and Operations.