verstak/docs/03_Data_Model_Storage.md

Верстак — модель данных и хранилище

1. Vault

Пример структуры:

~/VerstakVault/
  .verstak/
    index.db
    config.yml
    device.yml
    sync_state.db
    trash/
    history/
    originals/
    thumbnails/

  spaces/
    clients/
      romashka/
        case.yml
        overview.md
        notes/
          nginx.md
          mysql-cleanup.md
          access.secret.md.enc
        documents/
          dogovor-2026.docx
          sluzhebka.pdf
        screenshots/
          error-form.png
        scripts/
          backup-site.sh
          cleanup.sql
        actions/
          open-admin.yml
        worklog/
          2026-05.md

2. SQLite schema MVP

nodes

CREATE TABLE nodes (
    id TEXT PRIMARY KEY,
    parent_id TEXT NULL REFERENCES nodes(id),
    type TEXT NOT NULL,
    title TEXT NOT NULL,
    slug TEXT NOT NULL,
    path TEXT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    deleted_at TEXT NULL,
    revision INTEGER NOT NULL DEFAULT 1,
    device_id TEXT NULL
);

node_meta

CREATE TABLE node_meta (
    node_id TEXT NOT NULL REFERENCES nodes(id),
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (node_id, key)
);

files

CREATE TABLE files (
    id TEXT PRIMARY KEY,
    node_id TEXT NOT NULL REFERENCES nodes(id),
    filename TEXT NOT NULL,
    path TEXT NOT NULL,
    storage_mode TEXT NOT NULL, -- vault | external
    size INTEGER NOT NULL DEFAULT 0,
    sha256 TEXT NULL,
    mime TEXT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    last_seen_at TEXT NULL,
    missing INTEGER NOT NULL DEFAULT 0
);

notes

CREATE TABLE notes (
    node_id TEXT PRIMARY KEY REFERENCES nodes(id),
    file_id TEXT NOT NULL REFERENCES files(id),
    format TEXT NOT NULL DEFAULT 'markdown',
    original_format TEXT NULL,
    encrypted INTEGER NOT NULL DEFAULT 0
);

actions

CREATE TABLE actions (
    id TEXT PRIMARY KEY,
    node_id TEXT NOT NULL REFERENCES nodes(id),
    title TEXT NOT NULL,
    kind TEXT NOT NULL,
    command TEXT NULL,
    args_json TEXT NULL,
    working_dir TEXT NULL,
    url TEXT NULL,
    confirm_required INTEGER NOT NULL DEFAULT 0,
    capture_output INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

worklog_entries

CREATE TABLE worklog_entries (
    id TEXT PRIMARY KEY,
    node_id TEXT NOT NULL REFERENCES nodes(id),
    started_at TEXT NULL,
    ended_at TEXT NULL,
    date TEXT NOT NULL,
    minutes INTEGER NULL,
    approximate INTEGER NOT NULL DEFAULT 1,
    billable INTEGER NOT NULL DEFAULT 0,
    summary TEXT NOT NULL,
    details TEXT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

activity_events

CREATE TABLE activity_events (
    id TEXT PRIMARY KEY,
    node_id TEXT NULL REFERENCES nodes(id),
    source TEXT NOT NULL,
    event_type TEXT NOT NULL,
    title TEXT NULL,
    path TEXT NULL,
    url TEXT NULL,
    app_name TEXT NULL,
    process_name TEXT NULL,
    started_at TEXT NOT NULL,
    ended_at TEXT NULL,
    duration_seconds INTEGER NULL,
    metadata_json TEXT NULL,
    confidence INTEGER NOT NULL DEFAULT 100,
    created_at TEXT NOT NULL
);

worklog_suggestions

CREATE TABLE worklog_suggestions (
    id TEXT PRIMARY KEY,
    node_id TEXT NOT NULL REFERENCES nodes(id),
    date TEXT NOT NULL,
    started_at TEXT NULL,
    ended_at TEXT NULL,
    suggested_minutes INTEGER NULL,
    summary TEXT NOT NULL,
    evidence_json TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

search_index

CREATE VIRTUAL TABLE search_index USING fts5(
    node_id UNINDEXED,
    title,
    content,
    path,
    tags,
    type
);

sync_ops

CREATE TABLE sync_ops (
    id TEXT PRIMARY KEY,
    op_id TEXT NOT NULL UNIQUE,
    device_id TEXT NOT NULL,
    entity_type TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    op_type TEXT NOT NULL,
    payload_json TEXT NOT NULL,
    created_at TEXT NOT NULL,
    pushed_at TEXT NULL,
    applied_at TEXT NULL
);

3. Правила хранения

Заметки

• physical .md file;
• metadata in SQLite;
• backup old version before overwrite.

Документы

• physical file;
• open with system app;
• metadata in SQLite.

Secret notes

• encrypted file, e.g. access.secret.md.enc;
• no FTS indexing;
• no logs with plaintext;
• master password later.

Удаление

• soft delete node;
• file to .verstak/trash;
• sync tombstone;
• physical cleanup only after retention.

4. Sync operations

Каждое изменение должно создавать operation:

• node_create;
• node_update;
• node_move;
• node_delete;
• file_add;
• file_update;
• file_delete;
• note_update;
• action_update;
• worklog_create;
• worklog_update.

Даже если sync ещё не реализован, operation log лучше заложить рано.

5. Индексация

Индексировать:

• node titles;
• note content;
• filenames;
• paths;
• worklog summaries/details;
• action titles;
• links.

Не индексировать:

• secret notes;
• private keys;
• token-like values;
• binary content in MVP.

6. Расширяемость через плагины

Базовая схема фиксирована и поддерживает плагины:

• Новые типы нод регистрируются плагинами через Lua API (verstak.node.register_type()) — схема таблицы nodes не меняется, type принимает любое строковое значение.
• Мета-поля (node_meta) хранят произвольные key-value пары, зарегистрированные плагинами.
• Плагины могут создавать собственные таблицы через SQL-миграции в своей директории .verstak/plugins/<name>/migrations/.
• device_id на уровне nodes позволяет плагинам синхронизировать свои данные через sync_ops.