Datový Model (Supabase / PostgreSQL)

Tento dokument popisuje databázové schéma aplikace Kosmo. Primární design dokument — SQL komentáře jsou zapsané i přímo v migračních skriptech.

1. Uživatelé a Role

ER Diagram: Evidence žáků a uživatelů

erDiagram
    classes ||--o{ students : "obsahuje"
    students ||--o{ student_guardians : "má zástupce"
    profiles ||--o{ student_guardians : "je zástupcem"

    profiles {
        uuid id PK "Odkazuje na Supabase Auth ID"
        text first_name
        text last_name
        text email
        text phone_number
        text avatar_url
        array roles "admin, teacher, parent, student"
        enum preferred_language "cs, en (default: cs)"
        enum summary_style "narrative, bullets"
        boolean is_identity_verified
        timestamp identity_verified_at
        uuid identity_verified_by FK
        timestamp created_at
    }

    students {
        uuid id PK
        text first_name
        text last_name
        date date_of_birth
        uuid class_id FK
        text health_notes "alergie, diety"
        text special_needs "podklady pro SVP"
        text variable_symbol "YYNNN — identifikátor pro platby"
        enum status "active, graduated, transferred..."
        date enrollment_date
        date end_date
        text notes "interní poznámky"
    }

    student_guardians {
        uuid id PK
        uuid student_id FK
        uuid guardian_id FK
        enum relationship "mother, father, grandparent, other"
        boolean is_primary_contact
        boolean can_pickup "oprávněn vyzvedávat"
        boolean is_legal_guardian "zákonný zástupce"
    }

    classes {
        uuid id PK
        text name "např. Hvězdáři"
        text grade_level "např. 1-3"
        uuid lead_teacher_id FK
    }

Popis entit

profiles: Rozšíření nativní Supabase auth.users tabulky. Profil uživatele v Kosmo aplikaci — jméno, e-mail, funkční kontakty, preference jazyka a AI souhrnu.
students: Informace o jednom konkrétním žákovi sdílené bez ohledu na zastupování (zdravotní kód, pojišťovna, RČ, zdravotní omezení, specifické vzdělávání atp.). Citlivá data — RLS: pouze Admin/Ředitel.
student_guardians: Vazební M:N tabulka. Jednomu guardian přiřadíme např. dvě děti. Udržujeme vztah (matka, otec, babička), oprávnění k vyzvedávání a kontaktní prioritu.
classes: Seznam tříd/triád. Žák do třídy nastoupí a posouvá se.

Školní roky

school_years
- id: UUID
- name: Text (např. "2025/2026")
- is_active: Boolean
students_classes (Vazba Žák ↔ Třída ↔ Školní rok)
- student_id: UUID (FK → profiles.id)
- class_id: UUID (FK → classes.id)
- school_year_id: UUID (FK → school_years.id)

2. Docházka a Omluvenky

attendance_records (Denní docházka)
- id: UUID
- student_id: UUID (FK)
- date: Date
- status: Enum ('present', 'absent', 'excused', 'late')
- arrival_time: Time
- departure_time: Time
- pickup_person_name: Text (kdo vyzvedl, pokud to nebyl defaultní rodič)
attendance_plans (Plán odchodů)
- id: UUID
- student_id: UUID (FK)
- day_of_week: Int (0-6)
- regular_departure_time: Time
- type: Enum ('regular', 'exception')
- valid_from: Date
- valid_to: Date
excuse_notes (Omluvenky)
- id: UUID
- student_id: UUID (FK)
- author_id: UUID (FK → rodič)
- content: Text
- from_date: Date
- to_date: Date
- reason: Text
- status: Enum ('pending', 'approved', 'rejected')

Poznámka: Tabulka pickup_persons byla odstraněna. V modelu Sofie Chat rodič sdělí vyzvedávající osobu v chatu, AI extrahuje údaje a uloží je do attendance_records.pickup_person_name. Historická data jsou dohledatelná z communication_threads.

Fáze 2: Tabulky activities (kroužky/akce), activity_enrollments (přihlášky na kroužky), consents (definice souhlasů) a student_consents (podpisy souhlasů) jsou odloženy do Fáze 2.

3. Evidence Práce

ER Diagram: Soubory a AI zpracování

erDiagram
    students ||--o{ student_files : "má soubory"
    students ||--o{ competency_records : "má záznamy kompetencí"
    students ||--o{ ai_processing_runs : "má AI zpracování"

    student_files {
        uuid id PK
        uuid student_id FK
        text file_name "čitelný název souboru"
        text storage_path "plná cesta v GCS"
        text folder "evidence | portfolio"
        text type "document | work | assessment"
        text document_type "smlouva, rodny_list... (jen pro evidence)"
        text subject "předmět (jen pro portfolio)"
        timestamptz uploaded_at
        timestamptz created_at
    }

    competency_records {
        uuid id PK
        uuid student_id FK
        text competency_code "RVP nebo vlastní kód školy"
        text competency_name "Sčítání zlomků"
        text subject_area "Matematika"
        text evidence_source "assessment | work | teacher_manual"
        text source_file "název souboru v bucketu"
        text level "zvládá samostatně | s podporou | rozvíjí se"
        date observed_at
        text school_year "2025/2026"
        boolean ai_generated
        boolean teacher_confirmed
        text processing_model "gemini-2.0-flash"
        timestamptz processing_date
        timestamptz created_at
    }

    ai_processing_runs {
        uuid id PK
        uuid student_id FK
        text run_type "single_file | full_portfolio"
        text model_used "gemini-2.0-flash"
        timestamptz started_at
        timestamptz completed_at
        int files_processed
        int records_generated
        text status "running | completed | failed"
        text error_message
    }

Popis entit — soubory a AI

student_files: Lightweight cache/index souborů uložených v GCP Cloud Storage. Slouží pro rychlý listing v UI bez nutnosti volat GCS API. Regenerovatelné z manifest.json v bucketu. Zdrojem pravdy je bucket (gs://sofie-media/), ne databáze.
competency_records: AI-extrahované záznamy kompetencí žáka. Plněné z Gemini Vision analýzy fotek prací a scanů, nebo ručně učitelem. Centrální tabulka pro budoucí AI metodické listy. Pole processing_model a processing_date umožňují tracking modelu pro případný re-processing.
ai_processing_runs: Audit log AI zpracování. Každý běh (upload jednoho souboru nebo re-processing celého portfolia) vytvoří záznam. Slouží pro debugging, monitoring nákladů a plánování re-processingu.

Předměty a kompetence

subjects (Předměty)
- id: UUID
- name: Text
- color: Text
competencies (Kompetence)
- id: UUID
- subject_id: UUID (FK → subjects.id)
- name: Text
- description: Text

Evidence prací (work_items)

work_items (Práce / důkazy)
- id: UUID
- student_id: UUID (FK)
- created_by: UUID (FK, nebo NULL pro AI agenta)
- title: Text
- description: Text (Markdown)
- content: JSONB (strukturovaný obsah: textové bloky, URL na fotky/soubory, audio poznámky)
- source: Enum ('upload', 'email', 'api_agent', 'manual')
- external_reference_id: Text (např. Message-ID emailu)
- status: Enum ('draft', 'pending_approval', 'published', 'archived')
- ai_metadata: JSONB (analýza od Gemini: navržené kompetence, sentiment, klíčová slova)
- teacher_feedback: Text
- created_at: Timestamp
- published_at: Timestamp
- visibility: Enum ('private', 'teacher', 'parent', 'public')
work_item_tags (Štítky na pracích — kompetence)
- work_item_id: UUID (FK)
- competence_id: UUID (FK)
- confidence_score: Float (0.0–1.0, pokud navrženo AI)
- is_approved: Boolean (potvrzeno učitelem)

4. Komunikace (Sofie Chat)

communication_threads (Konverzace / Tickety)
- id: UUID
- student_id: UUID (FK, nullable — obecné dotazy nemají žáka)
- category: Enum ('obecne', 'omluvenka', 'zdravi', 'vyuka', 'incident', 'survey')
- status: Enum ('open', 'ai_resolved', 'teacher_review', 'closed')
- assigned_to: UUID (FK, nullable — pokud řeší AI)
- created_at: Timestamp
- updated_at: Timestamp
messages (Zprávy ve vlákně)
- id: UUID
- thread_id: UUID (FK → communication_threads.id)
- sender_id: UUID (FK, nebo NULL pro AI bota)
- content: Text (Markdown)
- is_internal: Boolean (poznámky učitelů, rodič nevidí)
- ai_metadata: JSONB (analýza sentimentu, navržené odpovědi)
- created_at: Timestamp

5. Ankety a Sběr Dat

surveys (Ankety)
- id: UUID
- title: Text
- description: Text
- author_id: UUID (FK)
- is_active: Boolean
- deadline: Timestamp
- target_audience: Enum ('all_parents', 'class_group', 'specific_parents')
survey_questions (Otázky)
- id: UUID
- survey_id: UUID (FK)
- question_text: Text
- question_type: Enum ('yes_no', 'single_choice', 'multiple_choice', 'text')
- order: Int
survey_options (Možnosti odpovědí)
- id: UUID
- question_id: UUID (FK)
- option_text: Text
- order: Int
survey_responses (Odpovědi rodičů)
- id: UUID
- survey_id: UUID (FK)
- question_id: UUID (FK)
- option_id: UUID (FK, nullable)
- respondent_id: UUID (FK)
- text_answer: Text (nullable)
- created_at: Timestamp

6. Knowledge Base & FAQ

knowledge_documents (Indexované dokumenty z Google Drive)
- id: UUID
- drive_file_id: Text (Google Drive File ID)
- title: Text
- trust_level: Enum ('verified', 'current', 'draft')
- content_hash: Text (detekce změn při syncu)
- last_synced_at: Timestamp
- created_at: Timestamp
faq_entries (Ověřené FAQ — samoučící se z dotazů rodičů)
- id: UUID
- question: Text
- answer: Text
- source_thread_id: UUID (FK → communication_threads.id)
- approved_by: UUID (FK — ředitel/zástupce)
- approved_at: Timestamp
- is_published: Boolean
- category: Text (např. 'stravování', 'výlety', 'pravidla')
- usage_count: Integer (kolikrát Sofie tuto FAQ citovala)
- created_at: Timestamp

7. Projekty a Úkoly (Kosmo PM)

Jednoduchý interní PM modul — vlastní kanban. Učitelé potřebují projekt s popisem, odkazy na zdroje a seznam úkolů s deadlinem a zodpovědnou osobou.

projects (Projekty / akce)
- id: UUID
- name: Text (např. "Rekonstrukce šatny", "Zahradní slavnost 2026")
- description: Text (Markdown)
- status: Enum ('active', 'completed', 'archived')
- calendar_url: Text (odkaz na Google Calendar událost, volitelné)
- drive_url: Text (odkaz na Google Drive složku, volitelné)
- created_by: UUID (FK)
- created_at: Timestamp
- updated_at: Timestamp
project_tasks (Úkoly v projektu)
- id: UUID
- project_id: UUID (FK → projects.id)
- title: Text
- note: Text (volitelná poznámka)
- assignee_id: UUID (FK — zodpovědná osoba)
- due_date: Date (deadline)
- is_completed: Boolean (default: false)
- completed_at: Timestamp
- order: Integer (pořadí zobrazení)
- created_at: Timestamp

8. Pipeline zápisů / CRM (Leads)

Modul pro sledování zápisového procesu. Zájemce → kontaktován → prohlídka → přihláška → zapsán. Klíčová výhoda: přechod lead → student jedním kliknutím.

leads (Zájemci o zápis)
- id: UUID
- family_name: Text
- contact_email: Text
- contact_phone: Text
- stage: Enum ('new', 'contacted', 'tour_scheduled', 'tour_done', 'application', 'enrolled', 'rejected', 'withdrawn')
- source: Enum ('website', 'referral', 'open_day', 'social_media', 'other')
- source_detail: Text
- created_by: UUID (FK)
- created_at: Timestamp
- updated_at: Timestamp
lead_children (Děti zájemce)
- id: UUID
- lead_id: UUID (FK)
- first_name: Text
- age: Integer
- target_grade: Text
- notes: Text
lead_notes (Historie komunikace)
- id: UUID
- lead_id: UUID (FK)
- type: Enum ('email', 'phone', 'in_person', 'tour', 'note')
- content: Text
- author_id: UUID (FK)
- created_at: Timestamp

Přechod lead → student

Když lead dosáhne fáze enrolled: 1. Z lead_children se vytvoří záznamy v students 2. Z kontaktních údajů leadu se vytvoří profiles (rodiče) 3. Vytvoří se vazby ve student_guardians 4. Lead se označí jako enrolled a archivuje

9. Generování dokumentů (šablony)

document_templates (Šablony dokumentů)
- id: UUID
- name: Text (např. "Smlouva o vzdělávání")
- category: Enum ('contract', 'consent', 'confirmation', 'notice')
- content: Text (Markdown šablona s {{proměnnými}})
- version: Integer (default: 1)
- is_active: Boolean
- created_by: UUID (FK)
- created_at: Timestamp
- updated_at: Timestamp
generated_documents (Vygenerované dokumenty — archiv)
- id: UUID
- template_id: UUID (FK)
- template_version: Integer
- student_id: UUID (FK)
- pdf_url: Text
- generated_by: UUID (FK)
- generated_at: Timestamp

10. Audit Log

audit_logs (Auditní stopa — právní pojistka)
- id: UUID
- user_id: UUID (FK)
- action: Text (např. 'pickup_confirmed', 'excuse_created', 'consent_granted')
- entity_type: Text (např. 'attendance_records', 'excuse_notes')
- entity_id: UUID
- details: JSONB (snapshot dat v okamžiku akce)
- ip_address: Text
- user_agent: Text
- created_at: Timestamp

11. Nastavení Notifikací

notification_preferences (Preference uživatele)
- id: UUID
- user_id: UUID (FK)
- channel: Enum ('push', 'email')
- category: Text (např. 'attendance', 'evidence', 'surveys', 'daily_digest')
- enabled: Boolean (default: true)
- summary_frequency: Enum ('daily', 'weekly', 'monthly')

12. Bezpečnost (RLS Policies)

Row Level Security (RLS) policies zajistí, že data vidí jen oprávnění uživatelé.

Tabulka	Pravidlo
`profiles`	Každý čte své (nebo veřejné profily učitelů). Admin může vše.
`students`	Pouze admin/ředitel (citlivá data).
`work_items`	Učitel vidí svou třídu, rodič vidí své dítě.
`communication_threads`	Rodič vidí jen své konverzace. Učitel vidí přiřazené tickety.
`attendance_records`	Rodič vidí své dítě. Učitel vidí svou třídu.
`faq_entries`	Publikované (`is_published: true`) vidí všichni.

Poznámka: Detailní RLS policies budou implementovány v migraci 0000_init_schema.sql.

Odloženo do Fáze 2

Následující entity jsou navrženy, ale budou implementovány až ve Fáze 2:

activities — Kroužky a akce (šachy, keramika, plavání...)
activity_enrollments — Přihlášky na kroužky
consents — Definice souhlasů (GDPR, výlety, fotky)
student_consents — Podpisy souhlasů s audit logem
Služba (role dítěte) — Žák-služba digitalizuje práce třídy