JSONB Document Database

Executive Summary

HeliosDB Lite provides JSONB (binary JSON) document storage with PostgreSQL-compatible operators and GIN (Generalized Inverted Index) for O(1) containment queries, enabling teams to build schema-flexible applications without sacrificing relational database guarantees. With 7 JSONB operators (->, ->>, #>, #>>, @>, <@, ?), 8 specialized functions (jsonb_extract_path, jsonb_array_length, jsonb_typeof, etc.), and path-based indexing for nested document access, HeliosDB Lite combines document flexibility with ACID transactions, SQL joins, and type safety in a single embedded database.

Problem Being Solved

Core Problem Statement

Modern applications require schema flexibility for evolving product features, user-generated content, and third-party integrations, but traditional relational databases force rigid table schemas that require costly migrations, while pure document databases sacrifice ACID transactions, SQL expressiveness, and relational integrity.

HeliosDB Lite Solution

Architecture Overview

┌─────────────────────────────────────────────────────────────────────┐
│                 HeliosDB-Lite JSONB Architecture                     │
├─────────────────────────────────────────────────────────────────────┤
│  SQL Layer: JSONB Type, Operators (->,->>), Functions (jsonb_*)      │
├─────────────────────────────────────────────────────────────────────┤
│  GIN Index │ Path Extractor │ Containment Checker │ Type Validator  │
├─────────────────────────────────────────────────────────────────────┤
│  Binary JSON Storage │ Inverted Index (Key→RowIDs) │ Relational Tables│
├─────────────────────────────────────────────────────────────────────┤
│       Unified Storage Engine (RocksDB LSM) - No External DB          │
└─────────────────────────────────────────────────────────────────────┘

Concrete Examples with Code, Config & Architecture

Example 1: User Profile System with Custom Fields - SaaS Platform

Scenario: B2B SaaS platform (CRM, project management) serving 500 companies with 50K users, each company requires 10-50 custom profile fields (job title, department, manager, custom tags). Traditional approach: create 100 nullable columns or use EAV pattern. HeliosDB approach: store custom fields in JSONB column alongside core relational columns.

Architecture:

User Registration/Update
    ↓
Application Layer (validation)
    ↓
HeliosDB-Lite (embedded)
    ├─ Core Fields: users table (id, email, name, created_at)
    └─ Custom Fields: JSONB column (preferences, custom_attributes, tags)
    ↓
GIN Index on JSONB → Fast queries on custom fields
    ↓
Single Query: JOIN users + filter on JSONB attributes

Configuration (heliosdb.toml):

# HeliosDB-Lite configuration for hybrid user profile storage
[database]
path = "/var/lib/heliosdb/saas_platform.db"
memory_limit_mb = 512
enable_wal = true
page_size = 4096

[jsonb]
enabled = true
# Enable GIN indexing for fast containment queries
enable_gin_indexes = true
# Cache parsed JSON documents
json_cache_size_mb = 64

[monitoring]
metrics_enabled = true
verbose_logging = false

Example 2: E-commerce Product Catalog with Varying Attributes

Scenario: E-commerce platform with 100K products across 50 categories (electronics, clothing, books, furniture), each category has 20-100 unique attributes. Electronics need "voltage", "warranty_years", "battery_type"; Clothing needs "size", "color", "material"; Books need "ISBN", "author", "publisher". Unified product table with JSONB attributes column.

Architecture Pattern:

┌─────────────────────────────────────────┐
│   E-commerce Application Layer           │
├─────────────────────────────────────────┤
│   Product Search (category + attributes) │
├─────────────────────────────────────────┤
│   HeliosDB-Lite (Embedded)               │
│   ├─ products table                      │
│   │   ├─ Core: id, sku, name, price     │
│   │   └─ JSONB: attributes, metadata     │
│   ├─ GIN Index on attributes             │
│   └─ Path Indexes on common fields       │
├─────────────────────────────────────────┤
│   RocksDB LSM Storage                    │
└─────────────────────────────────────────┘

Example 3: Event Sourcing & API Webhook Storage

Service Architecture:

┌───────────────────────────────────────┐
│  Webhook Providers (Stripe, GitHub)   │
├───────────────────────────────────────┤
│  HTTP POST → /webhooks                │
├───────────────────────────────────────┤
│  Webhook Service (Axum/Actix)         │
│  ├─ Validation                        │
│  ├─ Store in HeliosDB (JSONB)         │
│  └─ Background processing queue       │
├───────────────────────────────────────┤
│  HeliosDB-Lite (Embedded)             │
│  ├─ webhook_events table              │
│  │   ├─ source, event_type (TEXT)     │
│  │   └─ payload (JSONB)                │
│  └─ GIN Index on payload              │
├───────────────────────────────────────┤
│  Query API: Search events by content  │
└───────────────────────────────────────┘

Example 4: Configuration Management System

Scenario: Multi-tenant SaaS platform where each customer can customize feature flags, UI themes, notification preferences, integration settings. Requirements: per-customer configuration, hierarchical defaults (global → tenant → user), schema-free updates.

Example 5: API Response Caching with JSONB

Scenario: Mobile app backend caching third-party API responses (weather, maps, social media) to reduce external API costs and improve offline capability. Store responses as JSONB for querying and partial updates.

Technical Advantages

JSONB Storage Efficiency

Additional Benefits:

• Type preservation (numbers stay numeric, not strings)
• Faster parsing (binary format vs string parsing)
• Efficient path extraction (pointer-based, not regex)

Adoption Strategy

Phase 1: Proof of Concept (Weeks 1-4)

Target: Validate JSONB performance and migration path for 1-2 tables

Tactics:

• Identify highest-pain table with frequent schema changes (e.g., user_profiles, product_attributes)
• Create parallel JSONB-based table for A/B comparison
• Run benchmark queries: INSERT, SELECT with nested paths, containment queries

Deliverables:

• Performance benchmark report (JSONB vs existing approach)
• Migration script for primary use case
• Developer documentation with JSONB query examples

Phase 2: Pilot Deployment (Weeks 5-12)

Target: Production deployment for 1-2 critical features

Tactics:

• Migrate identified high-pain table to JSONB schema
• Deploy to staging environment with production traffic replay
• Monitor query performance, storage growth, index utilization
• Train engineering team on JSONB operators and functions
• Establish backup/restore procedures for JSONB data
• Create monitoring dashboards for JSONB query performance

Deliverables:

• Production deployment runbook
• Team training materials and internal documentation
• Monitoring and alerting setup

Phase 3: Full Rollout

Target: Organization-wide adoption for appropriate use cases

Tactics:

• Establish JSONB design patterns and anti-patterns documentation
• Create migration playbook for legacy tables
• Optimize GIN index configurations based on query patterns
• Implement automated schema validation for JSONB columns
• Build internal tooling for JSONB data exploration/debugging

Deliverables:

• Comprehensive JSONB best practices guide
• Automated migration tooling
• Performance optimization playbook
• Case studies demonstrating business impact

Conclusion

The JSONB document database capability in HeliosDB Lite solves the fundamental tension between schema flexibility and relational integrity that has forced development teams to choose between rapid iteration (MongoDB) and data consistency (PostgreSQL).

For SaaS startups building multi-tenant platforms with customer-specific fields, e-commerce companies managing catalogs with varying product attributes, and API integration platforms storing unpredictable webhook payloads, HeliosDB Lite's JSONB support enables schema-less flexibility where needed while maintaining ACID transactions, SQL joins, and foreign key constraints for critical business data. The embedded architecture ensures zero network latency for JSONB queries, full offline capability for edge/mobile deployments, and integration with existing SQL tooling and analytics pipelines.