Database

Engine & access

• PostgreSQL. Connection config via the API's DATABASE_* env vars (see api-backend.md). Default DB name bmekonza, port 5432.
• Application queries go through GORM (gorm.io/driver/postgres). The River job queue uses its own pgx/v5 pool and keeps its own tables (job/queue/leader state) in the same database — see api-backend.md.
• Hosting: [TODO: Coolify-managed Postgres / Docker container / external managed DB? host, version, data volume] — see the private operations docs (infrastructure).

Migrations

• goose SQL migrations in bme-konza-api/db/migrations/, embedded into the binary via db/embed.go. 50 files, numbered 001…039 (with several data backfills run as konza CLI scripts rather than migrations).
• Apply with konza migrate. Migrations run forward-only here (files are *.up.sql; note one stray double-extension 038_..._SEARCH.up.sql.sql — harmless but worth tidying).

Data model (high level)

Built up across the migrations; the core tables (from 001_Create_initial_tables onward). All tables use soft deletes (deleted_at) and created_at/updated_at.

Taxonomy & content - equipments, brands, models (a model = brand + equipment), schools - resources (manuals/videos/etc: type, link, cover, file_type) + resource_references (resource ↔ model), resource_stats, resource_events - tags, question_tags, user_tags_references

Q&A - questions (title/description, model_id, user_id, anonymously, attachments[], response_count), responses (with images, anonymous replies), upvote_records, question_stats

Users & gamification - users (email, role, profile_photo_path, device_token, Firebase UID), experiences, levels, user_reputation_points, user_bookmarks, user_feedback, user_activity_logs, device_id (FCM tokens)

Engagement & ops - notifications (typed, read status), announcements, events, requests (manual requests, with status), search_data, search_state_log

This is a summary — read the migration files for exact columns.

Full table list (33 tables)

announcements, banners, brands, categories, device_ids, equipments, events, models, notifications, question_stats, question_tags, questions, reports, reputation_tiers, requests, resource_category_references, resource_events, resource_references, resource_stats, resources, responses, schools, search_data, search_state_logs, tags, upvote_records, user_activity_logs, user_experiences, user_feedbacks, user_reputation_points, user_tag_references, users.

ER diagram (core relationships)

Derived from the FOREIGN KEY constraints across the migrations. Renders on GitHub (https://github.com/global-health-informatics-institute/BME-KONZA-HQ/blob/main/docs/database.md).

erDiagram
    equipments ||--o{ models : "has"
    brands     ||--o{ models : "has"
    models     ||--o{ questions : "about"
    models     ||--o{ resource_references : ""
    resources  ||--o{ resource_references : ""
    resources  ||--o{ resource_category_references : ""
    categories ||--o{ resource_category_references : ""
    resources  ||--o{ resource_events : ""
    resources  ||--o{ resource_stats : ""

    users      ||--o{ questions : "asks"
    users      ||--o{ responses : "writes"
    questions  ||--o{ responses : "has"
    responses  ||--o{ responses : "parent_id (threaded replies)"
    questions  ||--o{ question_stats : ""
    questions  ||--o{ question_tags : ""
    tags       ||--o{ question_tags : ""
    responses  ||--o{ upvote_records : ""

    users      ||--o{ notifications : ""
    questions  ||--o{ notifications : ""
    resources  ||--o{ notifications : ""
    users      ||--o{ events : ""
    questions  ||--o{ events : ""
    resources  ||--o{ events : ""

    users           ||--o{ user_tag_references : ""
    tags            ||--o{ user_tag_references : ""
    users           ||--o{ user_experiences : ""
    users           ||--o{ user_feedbacks : ""
    users           ||--o{ user_activity_logs : ""
    users           ||--o{ user_reputation_points : ""
    reputation_tiers ||--o{ user_reputation_points : "user_level"

Note — threaded replies: responses.parent_id is a self-reference (fk_parent_id → responses(id)), i.e. the schema already supports nested replies. This is the "replying to replies" feature flagged as unfinished — the data model exists but the behaviour/UI is incomplete. See 15-known-issues-roadmap.md.

A few tables have no FK (standalone): schools, device_ids, requests, banners, reports, search_data, search_state_logs, announcements — they reference users/ content by id without an enforced constraint, or stand alone.

Keep this current: the easiest way is to commit a pg_dump --schema-only (or an auto-generated diagram) into the repo and regenerate it when migrations change. [TODO: decide whether to add amake schema-dumptarget.]

Full-text search

038_CREATE_INDEXES_FOR_SEARCH adds: - pg_trgm extension. - Weighted tsvector GIN indexes on resources and questions (title weight A, description weight B). - Trigram GIN indexes for fuzzy matching, plus btree indexes on common filter/sort columns.

Search logic is in pkg/services/search_service.go; the searchLog worker records queries into search_state_log. Full-text search is listed as unfinished — see 15-known-issues-roadmap.md.

Backups & restore — [TODO] 🔑

This is the original sketch's "Backups (where stored, how to recover)" item. Document:

• [ ] What performs the backup (Coolify scheduled backup? a cron pg_dump?).
• [ ] Where backups are stored (off-server!) and retention.
• [ ] Exact restore command and the last time a restore was tested.

Restore runbook lives in 17-disaster-recovery (private handover docs).