VanillaDatabase

---

Overview

VanillaDatabase (VanillaDB) is a self-hosted, lightweight multi-tenant SQLite cloud platform engineered with Node.js 22+ and Fastify.

Rather than running monolithic database clusters for every client, project, or microservice, VanillaDatabase dynamically provisions and orchestrates isolated SQLite database instances on disk. Each tenant database functions as an independent storage unit with dedicated Write-Ahead Logging (WAL), scoped API tokens, encrypted backups, media storage, asynchronous webhooks, and live event streams.

[!NOTE] Isolation Guarantee: All tenant databases operate with strict filesystem isolation under data/databases/:id.sqlite. System metadata is partitioned separately under data/system/vanilladb.sqlite.

---

Architecture

                                  +-----------------------------------+
                                  |       HTTP / SSE Client Layer     |
                                  | (Browser Dashboard, SDKs, Scripts)|
                                  +-----------------+-----------------+
                                                    |
                                                    v
                                  +-----------------------------------+
                                  |    Fastify API Gateway (Port 3000)|
                                  |   - Strict CSP & Session HMAC     |
                                  |   - Cluster Spillover Controller  |
                                  |   - Inter-Node Transparent Proxy  |
                                  +--------+-----------------+--------+
                                           |                 |
                    +----------------------+                 +----------------------+
                    | (Local Storage)                                | (Proxy / Stream)
                    v                                                v
+---------------------------------------+        +---------------------------------------+
| Primary Host Node (Local)             |        | Worker Storage Node 1 (Remote)        |
| - System Metadata (vanilladb.sqlite)  |        | - Isolated Tenant Databases           |
| - Local Tenant Databases              |        | - Local AES-256 Media Files           |
| - Realtime Hardware Telemetry (CPU/RAM)|       | - Internal Node API (/api/internal/*) |
| - Spillover Monitor (>85% / <5GB free)|        | - Zero-Downtime Migration Receiver    |
+---------------------------------------+        +---------------------------------------+

---

Key Capabilities

Multi-Node Cluster & Host Sharding

• Automated Storage Spillover: When the primary gateway disk exceeds 85% utilization (or has under 5GB free space), new tenant databases automatically allocate to healthy registered worker nodes with the highest available storage capacity.
• Transparent Reverse Proxying: The API Gateway intercepts requests (/v1/databases/:id/*, /query, /batch, /exec, /files) targeting databases hosted on remote worker nodes and transparently proxies traffic with zero client configuration changes.
• Zero-Downtime Database Migration: Moves databases between storage hosts using atomic SQLite VACUUM INTO snapshot streaming over secure authenticated endpoints (/api/admin/cluster/migrate) with SHA-256 integrity verification.
• Host Disk Quota Support: Supports containerized or VPS environments (VDB_HOST_DISK_GB) to enforce precise storage thresholds even on shared physical disk partitions.

Multi-Tenant SQLite Orchestration

• Dynamic creation of dedicated SQLite databases identified by nanoid (db_<nanoid>).
• Native Write-Ahead Logging (WAL mode), busy-timeout retry handling, foreign key enforcement, and memory-cached connection pooling.
• AI vector distance and similarity functions (vec_cosine_similarity(), vec_cosine_distance()) registered natively.
• Cryptographic SQL functions: encrypt_aes(), decrypt_aes(), hash_sha256(), hash_hmac().

OWASP Hardened & Cryptographic Defense

• Session Revocation (VDB-SEC-01): HMAC cookie signatures bind user token_version. Password changes and account disabling revoke active sessions immediately across all clients.
• TOTP Replay Protection (VDB-SEC-02): Monotonic time-step tracking enforcing RFC 6238 Section 5.2. OTP codes cannot be reused within the 90-second drift tolerance window.
• SSRF Network Blocker: Outbound webhooks block loopback (127.0.0.0/8), private subnets (RFC 1918), link-local addresses, and cloud metadata endpoints (169.254.169.254).
• Data-at-Rest Encryption: AES-256-GCM envelope encryption with authenticated headers (VENC signature, PBKDF2 salt, 128-bit authentication tag).
• Engine Sandboxing: ATTACH DATABASE, DETACH DATABASE, and binary extension loading (load_extension) are permanently disabled.

Multi-User Access Control & Quotas

• Three system roles: super_admin, admin, user.
• Database membership roles: owner, admin, editor, viewer.
• Granular database quantity quotas (max_databases) and per-user request throttling (rate_limit_per_minute).
• Self-service registration with automatic quota assignment.

Scoped Token System

• Generate tokens prefixed with vdb_live_* or vdb_test_*.
• Granular scopes: database:read, database:write, database:ddl, database:admin.
• Table-level allowlists and denylists.
• Raw tokens are never stored; only SHA-256 hashes are persisted in system metadata.

Server-Sent Events & Webhooks

• SSE streaming endpoint at /v1/databases/:id/realtime dispatching database mutations (insert, update, delete, schema).
• Webhook dispatcher with HMAC-SHA256 request signing (X-Vanilla-Signature), payload retries, and Discord/Slack formatting.

Media Assets & HTTP 206 Streaming

• Database-scoped file storage with transparent chunked encryption.
• HTTP 206 Partial Content range requests for audio and video scrubbing.

Schedules & Multi-Task Chaining Engine

• 5-Part Cron Scheduler: Industrial-grade cron parser (* * * * *) supporting step intervals, ranges, and comma lists.
• Ordered Task Pipeline: Execute sequential arrays of tasks: send_command (SQL), run_maintenance (wal_checkpoint, vacuum, optimize, reindex), create_backup, trigger_webhook, and export_data.
• Fault-Tolerant Automation: Configurable time_offset staging delays, continue_on_failure bypass, and only_when_online state validation.

Realtime Activity Timeline & Audit Stream

• Unified audit and operational event aggregation across all tenant databases (/api/admin/databases/:id/activity).
• Live event streaming and filtering by category, status, actor, and IP address with pause/resume controls.

RFC 6750 Ephemeral Single-Use Tickets (VDB-SEC-03)

• Complete prohibition of token exposure in query parameters across all write and query routes.
• 60-second single-use tickets (POST /v1/databases/:id/tickets) for secure media streaming and SSE connection handshakes.
• Memory-cached instant invalidation upon consumption.

Production Landing Page & Enterprise Dashboard

• Turso-Inspired Landing Page: Production-ready public landing page with 6 architectural pillars, tabbed code switcher (per-tenant, per-agent, per-user), real-time TypeScript/SQL syntax highlighter, feature grid, and smooth navigation exit transitions.
• Enterprise Management Console: Single-page dashboard built with React 19, Tailwind CSS v4, Lucide icons, and Monaco SQL Editor.
• Cross-Device Responsive Architecture: Fully adaptive UI tailored for mobile (<640px), tablet (640px–1024px), laptop (1024px–1280px), and wide desktop (>1280px).
• Cluster Management UI: Real-time multi-host telemetry dials (CPU, RAM, Disk, Network I/O throughput), worker node registration, and one-click database migration modal.
• Complete Bilingual Matrix: Seamless one-click English & Tiếng Việt switching across all dashboard screens, landing page, and notifications.
• Integrated Keyboard Shortcuts: Command palette (Ctrl+K), quick DB creation (Ctrl+B), Vim navigation chords (G+D, G+I), collapsible sidebar (Ctrl+\), and table browser hotkeys (Alt+I, Alt+R, [, ], /, Del).

Search Engine Optimization & AI-Agent Discovery (GEO/AEO)

• Crawler & Bot Discovery: Static robots.txt declaring crawler policies and Cloudflare Content-Signal (search=yes, ai-input=yes, ai-train=no), paired with standard sitemap.xml.
• AI Agent Readiness (llms.txt & index.md): Publishes curated LLM markdown summaries according to the community llmstxt.org specification, alongside native HTTP content negotiation (Accept: text/markdown) serving documentation with Vary: Accept.
• Pre-rendered Fallback Semantics: High-density semantic HTML embedded inside <div id="root"> ensures search bots and non-JS scrapers read complete product copy, H1/H2 hierarchies, and SDK examples.
• Schema.org JSON-LD Structured Data: Injected @graph schema combining WebSite, SoftwareApplication (DeveloperApplication with OS and pricing definitions), and Organization.
• Soft-404 Crawl Hardening: Backend server cleanly separates valid SPA client routes from non-existent endpoints, returning true HTTP 404 responses with custom 404.html to preserve search crawl budgets.
• Next-Gen Image Delivery: Packaged banner.webp delivering 80% payload size reduction compared to traditional PNG banners for optimal Core Web Vitals (LCP).

---

Quickstart

Prerequisites

• Node.js 22.0.0 or higher
• npm 10.0.0 or higher

1. Clone & Install

git clone https://github.com/Elaina2026/VanillaDB.git
cd VanillaDB
npm install

2. Configure Environment

cp .env.example .env

Review core variables in .env:

PORT=3000
HOST=0.0.0.0
NODE_ENV=production
VDB_MASTER_KEY=generate_a_secure_64_character_hex_key
VDB_SESSION_SECRET=generate_a_secure_64_character_hex_key
VDB_CORS_ORIGINS=http://localhost:3000

3. Build & Run

# Build frontend and server bundles
npm run build

# Start production server
npm start

For development with hot reloading:

npm run dev

The web dashboard is accessible at http://localhost:3000.

---

Configuration Reference

Variable	Type	Default	Description
PORT	number	3000	HTTP listening port
HOST	string	0.0.0.0	Network binding interface
NODE_ENV	string	development	Runtime environment (development, production, test)
VDB_NODE_ID	string	local	Host identifier for cluster sharding (local or worker node ID)
VDB_CLUSTER_SECRET	string	Auto-generated	Secret token for secure inter-node gateway-to-worker communication
VDB_HOST_DISK_GB	number	0 (Unset)	Host physical partition quota override (useful for 20GB VPS/containers)
VDB_MAX_DISK_GB	number	0 (Unset)	Alternative alias for host physical disk quota override
VDB_MASTER_KEY	string	Auto-generated	256-bit encryption key for database and media encryption
VDB_SESSION_SECRET	string	Auto-generated	HMAC secret for session cookies and temporary tokens
VDB_CORS_ORIGINS	string	*	Allowed CORS origins (comma-separated for multiple domains)

---

Security Policy & Model

VanillaDatabase follows a rigorous zero-trust defense-in-depth security model. For full vulnerability disclosure procedures, supported releases, SLA commitments, and architectural isolation guarantees, read our dedicated security documents:

• English Security Policy: SECURITY.md
• Chính sách Bảo mật Tiếng Việt: SECURITY.vi.md

---

API Reference

Data Plane (Tenant Database Operations)

All Data Plane endpoints require authentication via Bearer API token (Authorization: Bearer vdb_live_...) or active session cookie.

# Execute SQL Query (SELECT)
POST /v1/databases/:databaseId/query
Content-Type: application/json

{
  "sql": "SELECT id, username, email FROM users WHERE status = ? LIMIT 10;",
  "params": ["active"]
}

# Execute SQL Mutation (INSERT, UPDATE, DELETE)
POST /v1/databases/:databaseId/exec
Content-Type: application/json

{
  "sql": "UPDATE users SET status = ? WHERE id = ?;",
  "params": ["verified", "usr_123"]
}

# Transactional Batch Execution
POST /v1/databases/:databaseId/batch
Content-Type: application/json

{
  "transaction": true,
  "statements": [
    { "sql": "UPDATE accounts SET balance = balance - 100 WHERE id = ?;", "params": ["acc_a"] },
    { "sql": "UPDATE accounts SET balance = balance + 100 WHERE id = ?;", "params": ["acc_b"] }
  ]
}

# Connect to Live Realtime SSE Stream
GET /v1/databases/:databaseId/realtime
Accept: text/event-stream

# Stream Media File with Range Support
GET /v1/databases/:databaseId/storage/:fileId
Range: bytes=0-1048575

Control Plane (Administrative & Security Endpoints)

Method	Endpoint	Access	Purpose
GET, HEAD	/health or /api/health	Public (CORS *)	ProjectStatus-compliant health check for Gateway and Worker nodes
POST	/api/auth/register	Public	Self-service user account registration
POST	/api/auth/login	Public	User authentication and session cookie generation
POST	/api/auth/login/2fa	Public	Step-up 2FA verification with TOTP code or backup code
POST	/api/auth/change-password	Authenticated	Password update with automatic session revocation
GET	/api/admin/databases	Authenticated	List accessible tenant databases
POST	/api/admin/databases	Authenticated	Create a new tenant database
POST	/api/admin/databases/:id/clone	Admin / Owner	Clone database instance for branching
POST	/api/admin/databases/:id/backups	Admin / Owner	Trigger instant encrypted backup snapshot
POST	/api/admin/databases/:id/maintenance	Admin / Owner

# ProjectStatus Health Check API (Gateway & Worker compatible)
GET /health
Accept: application/json

# Response (200 OK for operational/degraded, 503 for outage):
{
  "status": "operational",
  "service": "vanilladb-gateway",
  "uptime": 12450,
  "timestamp": 1740000000000,
  "checks": {
    "database": "ok",
    "gateway": "connected"
  }
}

---

Keyboard Shortcuts

VanillaDatabase features a comprehensive shortcut matrix accessible from anywhere in the platform:

Context	Shortcut	Action (English)	Thao tác (Tiếng Việt)
Global	Ctrl + K	Open Command Palette	Mở thanh tìm kiếm lệnh nhanh
Global	Ctrl + B	Open Create Database modal	Mở cửa sổ tạo cơ sở dữ liệu mới
Global	Ctrl + \	Toggle desktop sidebar collapse	Thu gọn / Mở rộng Sidebar điều hướng
Global	G then D	Navigate to Databases (Vim chord)	Về danh sách Database (Vim chord)
Global	G then I	Navigate to Inbox (Vim chord)	Mở Hộp thư thông báo (Vim chord)
Global	Ctrl + Shift + L	Toggle language (EN / VI)	Chuyển đổi ngôn ngữ (EN / VI)
Global	Alt + T	Toggle theme (Light / Dark)	Chuyển đổi giao diện Sáng / Tối
Global	Shift + ?	Open Shortcuts reference page	Mở trang tra cứu phím tắt
DB Detail	1 .. 9	Switch database detail tabs	Chuyển nhanh qua lại các tab Database
SQL Console	Ctrl + Enter	Execute SQL statement	Thực thi câu lệnh SQL đang soạn

---

Testing & Quality Assurance

The test suite runs with Vitest and executes end-to-end assertions against the server instance:

# Run complete test suite (248 integration & unit tests across 16 test suites)
npm test

# Run security and pentesting suites (data leak, zero-day, auth & token tickets)
npm run test:security

# Run TypeScript typecheck
npm run typecheck

# Run performance benchmarks
npm run benchmark

All 248 tests cover:

• Multi-host cluster node registration, spillover triggers, and zero-loss snapshot migrations.
• Data leakage prevention and credential redaction (OWASP Top 10 API Security).
• Zero-day exploit resilience (DNS rebinding, decimal/hex IP SSRF, SQL prefix schema escapes).
• Multi-user RBAC, sub-account limits, and quota caps.
• 2FA TOTP activation, backup codes lifecycle, and step-up login challenges.
• Session revocation on credential update (VDB-SEC-01).
• Monotonic time-step TOTP replay rejection (VDB-SEC-02).
• Complete prohibition of token exposure in query parameters and ephemeral ticket lifecycle (VDB-SEC-03).
• Isolated binary avatar uploads, magic bytes validation, and path traversal security.
• Pterodactyl-inspired multi-task schedule execution pipelines and cron intervals.
• AES-256-GCM envelope encryption and PBKDF2 key derivation.
• SQL syntax translation from MySQL, PostgreSQL, CSV, and NDJSON.
• Transactional batch executions and rollback safety.
• HTTP 206 Partial Content range audio/video streaming.

---

Documentation Suite

For exhaustive technical reference and system designs:

• Comprehensive System Architecture: ARCHITECTURE.md

• Đặc tả Kiến trúc Hệ thống Toàn diện (VI): ARCHITECTURE.vi.md

• Documentation Hub: docs/README.md

• Documentation Hub: docs/README.md

• English Guides:

  • 01. Getting Started
  • 02. Architecture & Design
  • 03. Database Engine
  • 04. REST & SQL API
  • 05. Authentication & RBAC
  • 06. Realtime & Webhooks
  • 07. Storage & Streaming
  • 08. Backup & Maintenance
  • 09. Migration & Import

---

License

VanillaDatabase is open-source software licensed under the MIT License.