Skip to content

DATABASE_SCHEMA.md

Latest commit

 

History

History
415 lines (328 loc) · 11.7 KB

DATABASE_SCHEMA.md

File metadata and controls

415 lines (328 loc) · 11.7 KB

Database Schema Documentation

Overview

Vectorizer Sync uses SQLite as its local database to store all application data. The database is located in the user's home directory to ensure data persistence across application updates and to avoid permission issues.

Database Location

The database file location is platform-specific:

  • Windows: C:\Users\<username>\vectorizer-sync\database.db
  • macOS: ~/vectorizer-sync/database.db
  • Linux: ~/vectorizer-sync/database.db

Database Initialization

The database is automatically created on first application launch. The application SHALL:

  1. Create the database directory if it doesn't exist
  2. Initialize the database with the schema
  3. Run any pending migrations
  4. Verify database integrity

Schema Version

The database schema includes a version number for migration purposes:

CREATE TABLE IF NOT EXISTS schema_version (
  version INTEGER PRIMARY KEY,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Tables

projects

Stores project configurations and settings.

CREATE TABLE projects (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  path TEXT NOT NULL UNIQUE,
  workspace_type TEXT NOT NULL CHECK(workspace_type IN ('local', 'remote')),
  workspace_path TEXT,
  cloud_workspace_id TEXT,
  sync_enabled INTEGER NOT NULL DEFAULT 0,
  last_sync_at DATETIME,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_projects_path ON projects(path);
CREATE INDEX idx_projects_sync_enabled ON projects(sync_enabled);
CREATE INDEX idx_projects_workspace_type ON projects(workspace_type);

Fields:

  • id: Unique identifier (UUID)
  • name: Project name
  • path: Absolute path to project directory
  • workspace_type: 'local' or 'remote'
  • workspace_path: Path to workspace.yml file (for local workspaces)
  • cloud_workspace_id: HiveHub Cloud workspace ID (for remote workspaces)
  • sync_enabled: Whether cloud sync is enabled (0 or 1)
  • last_sync_at: Timestamp of last successful sync
  • created_at: Creation timestamp
  • updated_at: Last update timestamp

workspace_configs

Stores exported workspace.yml configurations.

CREATE TABLE workspace_configs (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL,
  workspace_yml TEXT NOT NULL,
  exported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  version INTEGER NOT NULL DEFAULT 1,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX idx_workspace_configs_project_id ON workspace_configs(project_id);
CREATE INDEX idx_workspace_configs_exported_at ON workspace_configs(exported_at);

Fields:

  • id: Unique identifier (UUID)
  • project_id: Reference to projects table
  • workspace_yml: YAML content of workspace.yml
  • exported_at: When the configuration was exported
  • version: Workspace format version

sync_history

Stores history of synchronization operations.

CREATE TABLE sync_history (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL,
  type TEXT NOT NULL CHECK(type IN ('export', 'upload', 'update', 'delete')),
  status TEXT NOT NULL CHECK(status IN ('success', 'failed', 'partial')),
  files_count INTEGER NOT NULL DEFAULT 0,
  files_processed INTEGER NOT NULL DEFAULT 0,
  errors TEXT, -- JSON array of error messages
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX idx_sync_history_project_id ON sync_history(project_id);
CREATE INDEX idx_sync_history_started_at ON sync_history(started_at);
CREATE INDEX idx_sync_history_status ON sync_history(status);
CREATE INDEX idx_sync_history_type ON sync_history(type);

Fields:

  • id: Unique identifier (UUID)
  • project_id: Reference to projects table
  • type: Type of sync operation ('export', 'upload', 'update', 'delete')
  • status: Operation status ('success', 'failed', 'partial')
  • files_count: Total number of files to process
  • files_processed: Number of files successfully processed
  • errors: JSON array of error messages (if any)
  • started_at: When the sync started
  • completed_at: When the sync completed (NULL if still in progress)

file_metadata

Stores metadata for tracked files.

CREATE TABLE file_metadata (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL,
  relative_path TEXT NOT NULL,
  absolute_path TEXT NOT NULL,
  size INTEGER NOT NULL,
  hash TEXT NOT NULL, -- SHA-256 hash
  last_modified DATETIME NOT NULL,
  synced_at DATETIME,
  sync_status TEXT NOT NULL DEFAULT 'pending' CHECK(sync_status IN ('pending', 'synced', 'failed', 'excluded')),
  exclusion_reason TEXT,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  UNIQUE(project_id, relative_path)
);

CREATE INDEX idx_file_metadata_project_id ON file_metadata(project_id);
CREATE INDEX idx_file_metadata_sync_status ON file_metadata(sync_status);
CREATE INDEX idx_file_metadata_hash ON file_metadata(hash);
CREATE INDEX idx_file_metadata_last_modified ON file_metadata(last_modified);

Fields:

  • id: Unique identifier (UUID)
  • project_id: Reference to projects table
  • relative_path: File path relative to project root
  • absolute_path: Absolute file path
  • size: File size in bytes
  • hash: SHA-256 hash of file content (for change detection)
  • last_modified: Last modification timestamp
  • synced_at: When file was last synced to cloud
  • sync_status: Current sync status
  • exclusion_reason: Reason for exclusion (if excluded)

notifications

Stores application notifications.

CREATE TABLE notifications (
  id TEXT PRIMARY KEY,
  type TEXT NOT NULL CHECK(type IN ('internal', 'hivehub')),
  severity TEXT NOT NULL CHECK(severity IN ('info', 'warning', 'error')),
  title TEXT NOT NULL,
  message TEXT NOT NULL,
  read INTEGER NOT NULL DEFAULT 0,
  action_url TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_notifications_read ON notifications(read);
CREATE INDEX idx_notifications_created_at ON notifications(created_at);
CREATE INDEX idx_notifications_type ON notifications(type);
CREATE INDEX idx_notifications_severity ON notifications(severity);

Fields:

  • id: Unique identifier (UUID)
  • type: Notification type ('internal' or 'hivehub')
  • severity: Severity level ('info', 'warning', 'error')
  • title: Notification title
  • message: Notification message
  • read: Whether notification has been read (0 or 1)
  • action_url: Optional URL for action (e.g., upgrade plan link)
  • created_at: When notification was created

user_settings

Stores user preferences and settings.

CREATE TABLE user_settings (
  id TEXT PRIMARY KEY DEFAULT 'default',
  sync_enabled INTEGER NOT NULL DEFAULT 1,
  auto_sync_enabled INTEGER NOT NULL DEFAULT 0,
  sync_interval INTEGER, -- in minutes
  max_file_size INTEGER NOT NULL DEFAULT 102400, -- 100KB in bytes
  excluded_patterns TEXT, -- JSON array
  notification_preferences TEXT, -- JSON object
  hivehub_account TEXT, -- JSON object
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Fields:

  • id: Always 'default' (single row table)
  • sync_enabled: Global sync enabled flag
  • auto_sync_enabled: Auto-sync on file changes
  • sync_interval: Sync interval in minutes (NULL for manual sync)
  • max_file_size: Maximum file size in bytes (default: 100KB)
  • excluded_patterns: JSON array of exclusion patterns
  • notification_preferences: JSON object with notification settings
  • hivehub_account: JSON object with HiveHub account information
  • updated_at: Last update timestamp

Notification Preferences JSON Structure:

{
  "syncStatus": true,
  "errors": true,
  "quotaWarnings": true,
  "hivehubAlerts": true
}

HiveHub Account JSON Structure:

{
  "email": "user@example.com",
  "planType": "free",
  "quotaLimit": 1000000000,
  "quotaUsed": 500000000
}

Database Configuration

Pragmas

The database is configured with the following SQLite pragmas:

PRAGMA journal_mode = WAL;        -- Write-Ahead Logging for better concurrency
PRAGMA foreign_keys = ON;          -- Enable foreign key constraints
PRAGMA synchronous = NORMAL;       -- Balance between safety and performance
PRAGMA cache_size = 10000;         -- 10MB cache
PRAGMA temp_store = MEMORY;        -- Use memory for temporary storage

Transactions

All write operations SHOULD use transactions:

db.transaction(() => {
  // Multiple operations
  insertProject(...);
  insertFileMetadata(...);
  updateSyncHistory(...);
})();

Data Integrity

Foreign Keys

All foreign key relationships are enforced:

  • workspace_configs.project_id → projects.id (CASCADE DELETE)
  • sync_history.project_id → projects.id (CASCADE DELETE)
  • file_metadata.project_id → projects.id (CASCADE DELETE)

Constraints

  • Unique constraints on:

    • projects.path (one project per path)
    • (project_id, relative_path) in file_metadata (one entry per file per project)

  • Check constraints on:

    • projects.workspace_type (must be 'local' or 'remote')
    • sync_history.type (must be valid operation type)
    • sync_history.status (must be valid status)
    • file_metadata.sync_status (must be valid status)
    • notifications.type (must be 'internal' or 'hivehub')
    • notifications.severity (must be valid severity level)

Migrations

Database schema changes are handled through migrations:

  1. Each migration has a version number
  2. Migrations are applied in order
  3. Migration history is stored in schema_version table
  4. Failed migrations are rolled back

Migration Example

const migrations = [
  {
    version: 1,
    up: `
      CREATE TABLE projects (...);
      CREATE TABLE workspace_configs (...);
    `,
    down: `
      DROP TABLE workspace_configs;
      DROP TABLE projects;
    `
  },
  {
    version: 2,
    up: `
      ALTER TABLE projects ADD COLUMN description TEXT;
    `,
    down: `
      -- SQLite doesn't support DROP COLUMN, would need to recreate table
    `
  }
];

Backup and Recovery

Backup Strategy

  1. Automatic Backups:

    • Database is backed up before schema migrations
    • Backups stored in ~/vectorizer-sync/backups/

  2. Manual Backups:

    • Users can export database via UI
    • Backup includes all tables and data

  3. Backup Format:

    • SQL dump format (.sql)
    • Compressed SQLite database (.db.backup)

Recovery

  1. Automatic Recovery:

    • Database integrity checks on startup
    • Automatic repair if corruption detected

  2. Manual Recovery:

    • Restore from backup file
    • Import from SQL dump

Performance Considerations

Indexes

All foreign keys and frequently queried columns are indexed:

  • Project lookups by path
  • File metadata by project and status
  • Sync history by project and date
  • Notifications by read status and date

Query Optimization

  1. Use prepared statements for repeated queries
  2. Batch operations in transactions
  3. Limit result sets with appropriate WHERE clauses
  4. Use appropriate indexes for queries

Maintenance

  1. VACUUM: Run periodically to reclaim space
  2. ANALYZE: Update query planner statistics
  3. REINDEX: Rebuild indexes if needed

Security

  1. File Permissions: Database file has restricted permissions (user-only)
  2. SQL Injection: All queries use parameterized statements
  3. Data Validation: All data is validated before insertion
  4. Encryption: Optional database encryption (future feature)

Testing

Test Database

Use in-memory database for tests:

const testDb = new Database(':memory:');
// Initialize schema
// Run tests
testDb.close();

Test Data

Create test fixtures for:

  • Sample projects
  • File metadata
  • Sync history
  • Notifications