Database Overview

Vista używa SQLite w trybie WAL (Write-Ahead Logging) dla wydajnej pracy wielowątkowej.

Database Stats

Metric	Value
Tables	33
Indexes	106
Triggers	6
Foreign Keys	~50

Core Tables

erDiagram
    USERS ||--o{ VISITS : creates
    USERS ||--o{ APPOINTMENTS : schedules
    USERS ||--o{ TASKS : "assigned_to"

    PATIENTS ||--o{ VISITS : has
    PATIENTS ||--o{ APPOINTMENTS : "scheduled for"
    PATIENTS ||--o{ TASKS : "related to"

    VISITS ||--o{ RECORDINGS : has
    VISITS ||--o| APPOINTMENTS : "linked from"

    USERS {
        string user_id PK
        string email
        string name
        string role "admin|vet|assistant|viewer"
        string password_hash
        bool biometric_enabled
        string pin_hash
        datetime created_at
        datetime updated_at
    }

    PATIENTS {
        string patient_id PK
        string name
        string species
        string breed
        string owner_name
        string owner_phone
        string owner_email
        datetime created_at
        datetime updated_at
    }

    VISITS {
        string visit_id PK
        string patient_id FK
        string user_id FK
        string visit_status "draft|finalized|sent"
        string processing_status
        string transcript
        string soap_subjective
        string soap_objective
        string soap_assessment
        string soap_plan
        datetime created_at
        datetime updated_at
    }

    APPOINTMENTS {
        string appointment_id PK
        string patient_id FK
        string veterinarian_id FK
        string scheduled_date
        string scheduled_time
        string status "scheduled|completed|cancelled"
        string visit_id FK "nullable"
    }

    TASKS {
        string task_id PK
        string title
        string status "active|completed|cancelled"
        string source "user|system|ai"
        string patient_id FK "nullable"
        string visit_id FK "nullable"
        string assigned_to FK
        string created_by FK
    }

    RECORDINGS {
        string recording_id PK
        string visit_id FK "nullable"
        string path
        string transcript
        string status
        datetime created_at
    }

Database Location

# macOS
~/Library/Application Support/Vista/vista.db

# Windows
%APPDATA%/Vista/vista.db

# Linux
~/.local/share/Vista/vista.db

SQLite Configuration

-- WAL mode for concurrent reads
PRAGMA journal_mode = WAL;

-- Synchronous for durability
PRAGMA synchronous = NORMAL;

-- Foreign keys enabled
PRAGMA foreign_keys = ON;

-- Auto-vacuum for space reclamation
PRAGMA auto_vacuum = INCREMENTAL;

Index Strategy

Vista uses 106 indexes optimized for:

• Query patterns: Most common queries have covering indexes
• Foreign keys: All FK columns are indexed
• Text search: Full-text search indexes for patients, visits
• Date ranges: Composite indexes for date-based queries

Triggers

Trigger	Table	Purpose
updated_at_users	users	Auto-update timestamp
updated_at_patients	patients	Auto-update timestamp
updated_at_visits	visits	Auto-update timestamp
updated_at_appointments	appointments	Auto-update timestamp
updated_at_tasks	tasks	Auto-update timestamp
updated_at_recordings	recordings	Auto-update timestamp

Migrations

Database migrations are managed by SQLx and run automatically on app startup:

src-tauri/migrations/
├── 001_initial_schema.sql
├── 002_add_appointments.sql
├── 003_add_tasks.sql
├── 004_add_recordings.sql
└── ...