SQL Table Definitions

Na tej stronie

Core Tables
Audio & Transcription
Tasks & Reminders
AI & Chat
Calendar & Scheduling
System Tables

Core Tables

users

Główna tabela personelu kliniki - lekarze weterynarii, asystenci, administratorzy.

CREATE TABLE users (
    user_id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT,                        -- bcrypt hash
    license_no TEXT UNIQUE,                    -- Numer licencji weterynaryjnej
    roles TEXT NOT NULL,                       -- JSON: ["admin", "vet", "assistant"]
    active BOOLEAN DEFAULT TRUE,

    -- Profile Info
    phone TEXT,
    specialization TEXT,                       -- np. "Kardiologia", "Chirurgia"
    avatar_url TEXT,
    clinic_name TEXT,
    clinic_address TEXT,
    clinic_id TEXT,
    last_login TIMESTAMP,

    -- Onboarding Status
    onboarding_completed BOOLEAN DEFAULT FALSE,
    onboarding_origin TEXT,                    -- 'complete' | 'skip' | null
    is_initial_password INTEGER,

    -- Auth & Security
    biometric_enabled INTEGER DEFAULT 0,
    biometric_key_id TEXT,

    -- UI Preferences
    color_theme TEXT,
    notifications_enabled INTEGER,
    language_preference TEXT,
    font_size_preference TEXT,
    email_notifications BOOLEAN DEFAULT TRUE,

    -- Workflow Preferences (Onboarding)
    practice_mode TEXT CHECK(practice_mode IN ('solo', 'team', 'demo')),
    workspace_mode TEXT,
    workday_style TEXT CHECK(workday_style IN ('planned', 'dynamic', 'balanced')),
    docs_style TEXT CHECK(docs_style IN ('detailed', 'quick', 'keypoints')),
    tech_profile TEXT CHECK(tech_profile IN ('innovator', 'practical', 'conservative')),
    tone_of_voice TEXT CHECK(tone_of_voice IN ('neutral', 'warm', 'professional')),

    -- AI & Documentation Preferences
    note_style TEXT DEFAULT 'SOAP',
    ai_precision_level TEXT DEFAULT 'balanced',
    history_depth TEXT DEFAULT 'standard',
    format TEXT DEFAULT 'paragraph',
    audio_retention_days INTEGER DEFAULT 14,

    -- Legal
    tos_accepted INTEGER DEFAULT 0,

    -- Team (JSON)
    team_members TEXT,
    integrations TEXT,

    -- Soft Delete
    is_deleted INTEGER DEFAULT 0,
    deleted_at TIMESTAMP,

    -- Audit
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indeksy
CREATE INDEX idx_users_is_deleted ON users(is_deleted);
CREATE INDEX idx_users_deleted_at ON users(deleted_at);
CREATE INDEX idx_users_onboarding_completed ON users(onboarding_completed);
CREATE INDEX idx_users_practice_mode ON users(practice_mode);

patients

Pacjenci - zwierzęta i ich właściciele.

CREATE TABLE patients (
    patient_id TEXT PRIMARY KEY,
    name TEXT NOT NULL,                        -- Imię zwierzęcia
    species TEXT,                              -- Gatunek (dog, cat, rabbit, etc.)
    breed TEXT,                                -- Rasa
    age INTEGER,                               -- Wiek w latach (LEGACY)
    birth_date TEXT,                           -- Data urodzenia ISO: "2024-04-15"
    gender TEXT DEFAULT 'unknown' CHECK(gender IN ('male', 'female', 'unknown')),
    color TEXT,

    -- Owner Information
    owner_name TEXT,
    owner_phone TEXT,
    owner_email TEXT,
    owner_audio_consent INTEGER CHECK(owner_audio_consent IN (0, 1)),
    owner_audio_consent_updated_at TIMESTAMP,

    -- Identification
    chip_number TEXT,                          -- Numer mikrochipa (UNIQUE where not null)

    -- Medical Data
    current_weight_kg REAL,
    vaccination_date TEXT,
    vaccination_expiry TEXT,
    vaccination_type TEXT,
    vaccination_status TEXT DEFAULT 'unknown'
        CHECK(vaccination_status IN ('current', 'expired', 'unknown')),
    sterilization_status TEXT DEFAULT 'unknown'
        CHECK(sterilization_status IN ('neutered', 'intact', 'unknown')),
    sterilization_date TEXT,

    -- Clinical Notes (JSON arrays)
    medical_conditions TEXT,                   -- JSON array chorób przewlekłych
    allergies TEXT,                            -- JSON array alergii

    -- Insurance & Emergency
    insurance_number TEXT,
    emergency_contact TEXT,
    emergency_phone TEXT,
    special_notes TEXT,

    -- Identity Matching (deduplication)
    identity_key TEXT,
    identity_key_strict TEXT,
    identity_key_folded TEXT,

    -- Audit
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indeksy
CREATE INDEX idx_patients_chip_number ON patients(chip_number);
CREATE UNIQUE INDEX ux_patients_chip_no ON patients(chip_number) WHERE chip_number IS NOT NULL;
CREATE INDEX idx_patients_gender ON patients(gender);
CREATE INDEX idx_patients_identity_key ON patients(identity_key);
CREATE UNIQUE INDEX ux_patients_identity_strict ON patients(identity_key_strict)
    WHERE identity_key_strict IS NOT NULL;
CREATE INDEX ix_patients_identity_folded ON patients(identity_key_folded);
CREATE INDEX idx_patients_vaccination_status ON patients(vaccination_status);
CREATE INDEX idx_patients_vaccination_expiry ON patients(vaccination_expiry);
CREATE INDEX idx_patients_sterilization_status ON patients(sterilization_status);
CREATE INDEX idx_patients_current_weight ON patients(current_weight_kg);
CREATE INDEX idx_patients_owner_audio_consent ON patients(owner_audio_consent);

visits

Serce systemu medycznego - wizyty z notatkami SOAP.

CREATE TABLE visits (
    visit_id TEXT PRIMARY KEY,
    patient_id TEXT NOT NULL REFERENCES patients(patient_id),
    user_id TEXT NOT NULL REFERENCES users(user_id),
    date_time TIMESTAMP NOT NULL,

    -- Visit Classification
    visit_type TEXT NOT NULL CHECK(visit_type IN (
        'consultation', 'vaccination', 'emergency', 'post_operative',
        'dental', 'first_puppy_kitten_visit', 'procedure'
    )),
    visit_status TEXT NOT NULL DEFAULT 'draft' CHECK(visit_status IN (
        'draft', 'finalized', 'sent'
    )),

    -- Clinical Data
    weight_kg REAL,

    -- SOAP Note Structure
    soap_subjective TEXT,                     -- Historia, obserwacje właściciela
    soap_objective TEXT,                      -- Badanie fizykalne
    soap_assessment TEXT,                     -- Diagnoza, ocena kliniczna
    soap_plan TEXT,                           -- Plan leczenia

    -- AI Suggestions
    ai_sugestie TEXT,                         -- Sugestie AI

    -- Medical Metadata
    diagnosis_code TEXT,
    anesthesia_used TEXT,

    -- Audio & Transcription
    diarization_quality TEXT CHECK(diarization_quality IN ('low', 'medium', 'high')),
    attachments TEXT,                         -- JSON array ID załączników
    audio_source_url TEXT,
    audio_duration_sec REAL,
    audio_format TEXT,
    audio_source_type TEXT CHECK(audio_source_type IN ('live', 'upload', 'unknown')),

    -- Idempotency
    create_op_key TEXT UNIQUE,

    -- Transcription Pipeline
    transcript TEXT,
    raw_transcript TEXT,
    pending_transcript TEXT,
    pending_raw_transcript TEXT,
    transcript_updated_at TIMESTAMP,

    -- Internal Notes
    internal_notes TEXT,

    -- LLM Processing
    llm_response TEXT,
    processing_status TEXT CHECK(processing_status IN (
        'idle', 'processing_audio', 'processing_stt', 'processing_llm',
        'completed', 'awaiting_user_merge', 'error'
    )),

    -- Optimistic Locking
    version INTEGER NOT NULL DEFAULT 0,

    -- Audit
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indeksy
CREATE INDEX idx_visits_patient_id ON visits(patient_id);
CREATE INDEX idx_visits_user_id ON visits(user_id);
CREATE INDEX idx_visits_date_time ON visits(date_time);
CREATE INDEX idx_visits_patient_date ON visits(patient_id, date_time);
CREATE INDEX idx_visits_user_date ON visits(user_id, date_time);
CREATE INDEX idx_visits_visit_status ON visits(visit_status);
CREATE INDEX idx_visits_visit_type ON visits(visit_type);
CREATE INDEX idx_visits_processing_status ON visits(processing_status);
CREATE UNIQUE INDEX ux_visits_create_op_key ON visits(create_op_key)
    WHERE create_op_key IS NOT NULL;

Audio & Transcription

recordings

Metadane nagrań audio powiązanych z wizytami.

CREATE TABLE recordings (
    id TEXT PRIMARY KEY,
    visit_id TEXT REFERENCES visits(visit_id),
    patient_id TEXT REFERENCES patients(patient_id),
    created_by TEXT REFERENCES users(user_id) ON DELETE SET NULL,
    path TEXT NOT NULL,                        -- Ścieżka do pliku
    bytes INTEGER,
    duration_ms INTEGER,
    sha256 TEXT UNIQUE,                        -- Checksum SHA256 (deduplikacja)
    codec TEXT,                                -- pcm, aac, etc.
    status TEXT NOT NULL CHECK(status IN (
        'recording', 'saving', 'indexed', 'transcribing',
        'linked', 'done', 'failed', 'orphaned', 'retryable'
    )),
    error_code TEXT,
    error_summary TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indeksy
CREATE INDEX idx_recordings_path ON recordings(path);
CREATE INDEX idx_recordings_sha ON recordings(sha256);
CREATE INDEX idx_recordings_visit ON recordings(visit_id);
CREATE INDEX idx_recordings_patient ON recordings(patient_id);
CREATE INDEX idx_recordings_created_by ON recordings(created_by);
CREATE INDEX idx_recordings_status ON recordings(status);
CREATE INDEX ix_recordings_created_at ON recordings(created_at);

transcripts

Wyniki Speech-to-Text (relacja 1:1 z recordings).

CREATE TABLE transcripts (
    id TEXT PRIMARY KEY,
    recording_id TEXT NOT NULL UNIQUE REFERENCES recordings(id) ON DELETE CASCADE,
    engine TEXT,                               -- whisper, google, etc.
    lang TEXT,                                 -- pl, en, etc.
    status TEXT NOT NULL,
    text TEXT,
    segments_json TEXT,                        -- JSON array segmentów z timingiem
    error_code TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indeksy
CREATE INDEX ix_transcripts_status ON transcripts(status);
CREATE INDEX ix_transcripts_created_at ON transcripts(created_at);

Tasks & Reminders

tasks

Zadania kliniczne i administracyjne.

CREATE TABLE tasks (
    task_id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'completed')),
    task_type TEXT NOT NULL DEFAULT 'general',
    priority TEXT NOT NULL DEFAULT 'medium' CHECK(priority IN ('low', 'medium', 'high')),
    resolved_at TEXT,

    -- Powiązania
    patient_id TEXT REFERENCES patients(patient_id) ON DELETE SET NULL,
    visit_id TEXT REFERENCES visits(visit_id) ON DELETE SET NULL,
    assigned_to TEXT REFERENCES users(user_id) ON DELETE SET NULL,
    created_by TEXT REFERENCES users(user_id) ON DELETE SET NULL,

    -- Źródło zadania
    source TEXT NOT NULL DEFAULT 'manual',     -- 'manual' | 'ai'
    tags TEXT,                                 -- JSON array tagów
    metadata TEXT,
    confidence REAL,                           -- Confidence AI
    content_hash TEXT,                         -- Hash dla deduplikacji

    -- Audit
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indeksy
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_priority ON tasks(priority);
CREATE INDEX idx_tasks_assigned ON tasks(assigned_to);
CREATE INDEX idx_tasks_patient ON tasks(patient_id);
CREATE INDEX idx_tasks_visit ON tasks(visit_id);
CREATE INDEX idx_tasks_created_by ON tasks(created_by);
CREATE INDEX idx_tasks_source ON tasks(source);
CREATE INDEX idx_tasks_created_at ON tasks(created_at);
CREATE INDEX idx_tasks_updated_at ON tasks(updated_at);
CREATE INDEX idx_tasks_visit_hash ON tasks(visit_id, content_hash);

ai_task_suggestions

Sugestie zadań generowane przez AI z analizy wizyt.

CREATE TABLE ai_task_suggestions (
    suggestion_id TEXT PRIMARY KEY,
    visit_id TEXT NOT NULL REFERENCES visits(visit_id) ON DELETE CASCADE,
    patient_id TEXT NOT NULL REFERENCES patients(patient_id) ON DELETE CASCADE,

    -- Treść sugestii
    text TEXT NOT NULL,
    due_at TEXT,
    task_type TEXT NOT NULL,
    priority TEXT NOT NULL,
    tags TEXT,                                 -- JSON array
    confidence REAL,

    -- Deduplikacja
    content_hash TEXT NOT NULL,
    idempotency_key TEXT,
    duplicate BOOLEAN NOT NULL DEFAULT 0,

    -- Status
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK(status IN ('pending', 'accepted', 'dismissed')),
    accepted_at TEXT,
    dismissed_at TEXT,

    -- Metadane
    metadata TEXT,

    -- Audit
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

reminders

Przypomnienia użytkownika.

CREATE TABLE reminders (
    reminder_id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    details TEXT,
    category TEXT NOT NULL CHECK(category IN ('medical', 'logistics', 'relationship', 'automation')),
    origin_agent TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK(status IN ('pending', 'completed', 'snoozed', 'dismissed')),
    due_at TEXT,
    snooze_until TEXT,
    resolved_at TEXT,

    -- Powiązania
    created_by TEXT REFERENCES users(user_id) ON DELETE SET NULL,
    assigned_to TEXT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    context_patient_id TEXT REFERENCES patients(patient_id) ON DELETE SET NULL,
    context_visit_id TEXT REFERENCES visits(visit_id) ON DELETE SET NULL,

    -- Dodatkowe dane
    payload TEXT,

    -- Audit
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indeksy
CREATE INDEX idx_reminders_assigned_status_due ON reminders(assigned_to, status, due_at);
CREATE INDEX idx_reminders_category ON reminders(category);
CREATE INDEX idx_reminders_due_at ON reminders(due_at);

AI & Chat

chat_sessions

Sesje rozmów z AI.

CREATE TABLE chat_sessions (
    id TEXT PRIMARY KEY,
    user_id TEXT REFERENCES users(user_id) ON DELETE SET NULL,
    visit_id TEXT REFERENCES visits(visit_id) ON DELETE SET NULL,
    started_at INTEGER NOT NULL,               -- Unix timestamp
    ended_at INTEGER NOT NULL,
    message_count INTEGER NOT NULL,
    title TEXT NOT NULL,
    preview TEXT
);

-- Indeksy
CREATE INDEX idx_chat_sessions_user_started ON chat_sessions(user_id, started_at);
CREATE INDEX idx_chat_sessions_visit_started ON chat_sessions(visit_id, started_at);

chat_messages

Wiadomości w sesjach czatu.

CREATE TABLE chat_messages (
    id TEXT PRIMARY KEY,
    session_id TEXT NOT NULL REFERENCES chat_sessions(id) ON DELETE CASCADE,
    idx INTEGER NOT NULL,                      -- Indeks sekwencji
    role TEXT NOT NULL CHECK(role IN ('user', 'assistant', 'system')),
    content TEXT NOT NULL,
    timestamp INTEGER NOT NULL
);

-- Indeksy
CREATE INDEX idx_chat_messages_session_idx ON chat_messages(session_id, idx);

Calendar & Scheduling

appointments

Harmonogram wizyt.

CREATE TABLE appointments (
    appointment_id TEXT PRIMARY KEY,
    patient_id TEXT NOT NULL REFERENCES patients(patient_id) ON DELETE CASCADE,
    veterinarian_id TEXT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    visit_id TEXT REFERENCES visits(visit_id) ON DELETE SET NULL,

    scheduled_date TEXT NOT NULL,              -- "2025-09-04"
    scheduled_time TEXT NOT NULL,              -- "14:30"
    duration_minutes INTEGER NOT NULL DEFAULT 30,
    appointment_type TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'scheduled'
        CHECK(status IN ('scheduled', 'confirmed', 'completed', 'cancelled', 'no_show')),

    -- Recurrence
    is_recurring BOOLEAN NOT NULL DEFAULT false,
    recurrence_pattern TEXT,
    recurrence_end_date TEXT,

    -- Additional
    notes TEXT,
    reason TEXT,
    additional_staff TEXT,                     -- JSON array of user_ids

    -- Audit
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by TEXT REFERENCES users(user_id),
    updated_by TEXT REFERENCES users(user_id)
);

user_working_hours

Godziny pracy weterynarzy.

CREATE TABLE user_working_hours (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,

    day_of_week INTEGER NOT NULL,              -- 0=Sunday, 6=Saturday
    start_time TEXT NOT NULL,                  -- "HH:MM"
    end_time TEXT NOT NULL,
    is_working BOOLEAN NOT NULL DEFAULT true,

    -- Przerwa
    break_start_time TEXT,
    break_end_time TEXT,

    -- Audit
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by TEXT NOT NULL,
    updated_by TEXT NOT NULL,

    UNIQUE(user_id, day_of_week)
);

System Tables

jobs

Kolejka zadań background.

CREATE TABLE jobs (
    id TEXT PRIMARY KEY,
    type TEXT NOT NULL,                        -- Typ zadania
    key TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN (
        'queued', 'running', 'done', 'failed', 'retryable', 'scheduled'
    )),
    payload_json TEXT,
    attempts INTEGER NOT NULL DEFAULT 0,
    next_run_at TEXT,
    last_error TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(type, key)
);

-- Indeksy
CREATE INDEX ix_jobs_status ON jobs(status);
CREATE INDEX ix_jobs_next_run_at ON jobs(next_run_at);

audit_trail

Logi aktywności dla GDPR compliance.

CREATE TABLE audit_trail (
    audit_id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    user_name TEXT NOT NULL,                   -- Snapshot nazwy
    action TEXT NOT NULL CHECK(action IN (
        'create', 'update', 'delete', 'view',
        'export', 'print', 'share',
        'login', 'logout', 'permission_change'
    )),
    resource_type TEXT NOT NULL CHECK(resource_type IN (
        'patient', 'visit', 'user', 'clinical_note',
        'media_file', 'dictionary_term', 'settings'
    )),
    resource_id TEXT NOT NULL,
    changes TEXT,                              -- JSON diff (old → new)
    ip_address TEXT,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Konwencje Nazewnictwa

Element	Konwencja	Przykłady
Nazwa tabeli	`snake_case`, liczba mnoga	`users`, `visits`, `chat_messages`
Primary Key	`{singular}_id` lub `id`	`user_id`, `visit_id`, `id`
Foreign Key	`{referenced_table_singular}_id`	`patient_id`, `user_id`
Timestamps	`{action}_at`	`created_at`, `updated_at`, `deleted_at`
Booleans	`is_{adj}` / `{noun}_enabled`	`is_deleted`, `biometric_enabled`
JSON fields	bez suffixu lub `_json`	`roles`, `tags`, `payload_json`