Architecture Overview

Technical documentation of the SelfHostedDB licensing system architecture, data flows, and caching strategies.

System Architecture

High-Level Components

┌─────────────────────────────────────────────────────────────┐
│                    SelfHostedDB Stack                        │
├─────────────────────────────────────────────────────────────┤
│                                                               │
│  ┌──────────────┐      ┌──────────────┐      ┌───────────┐ │
│  │   Frontend   │◄────►│   Backend    │◄────►│PostgreSQL │ │
│  │  (React UI)  │      │  (Express)   │      │ Database  │ │
│  └──────────────┘      └──────┬───────┘      └───────────┘ │
│                               │                              │
│                               │ License                      │
│                               │ Validation                   │
│                               │                              │
└───────────────────────────────┼──────────────────────────────┘
                                │
                                ▼
                  ┌──────────────────────────┐
                  │   License Server         │
                  │   (Supabase Backend)     │
                  │                          │
                  │  • Trial Management      │
                  │  • License Validation    │
                  │  • Deployment Tracking   │
                  └──────────────────────────┘

Component Breakdown

Frontend (React)

User authentication (Basic Auth)
Database management UI
License status display
No direct license validation (offloaded to backend)

Backend (Node.js + Express)

PostgreSQL connection pooling
API endpoints for database operations
License validation middleware
In-memory and file-based license caching
CLI tool for license activation

License Server (Next.js + Supabase)

Separate deployment (hosted or self-hosted)
REST API for license operations
PostgreSQL database for licenses and trials
Machine ID tracking for deployment limits

PostgreSQL Database

User data storage
Schemas and tables
System schema (pgvista_system) for internal data:
- API keys
- License cache metadata

Licensing System

License Types

1. Trial License

Duration: 14 days
Features: Full access to all features
Deployment Limit: 5 instances per trial
Renewable: No (must purchase after expiration)
Grace Period: 3 days

2. Paid License

Duration: Lifetime (one-time purchase)
Features: Full access to all features
Deployment Limit: 5 instances per license
Updates: Free updates for life
Grace Period: 7 days

Deployment vs. Schema

Deployment = One Docker Container

Identified by unique machine ID
Generated on first startup
Stored in /app/license-data/machine-id.json
Each license allows up to 5 deployments

Schema = PostgreSQL Schema

Multiple schemas per deployment (unlimited)
No licensing restrictions on schema count
Example: public, dev, staging, prod

Example Scenario:

1 License = 5 Deployments
├── AWS EC2 Production (Deployment 1)
│   ├── Schema: public
│   ├── Schema: analytics
│   └── Schema: reporting
├── GCP Cloud Run Staging (Deployment 2)
│   └── Schema: public
├── Local Dev Machine (Deployment 3)
│   ├── Schema: dev_database_1
│   └── Schema: dev_database_2
├── Azure Container Instance (Deployment 4)
│   └── Schema: public
└── DigitalOcean Droplet (Deployment 5)
    └── Schema: public

License Activation Flow

Method 1: CLI Tool

┌─────────┐                ┌─────────────┐              ┌─────────────────┐
│  User   │                │  Container  │              │ License Server  │
└────┬────┘                └──────┬──────┘              └────────┬────────┘
     │                            │                              │
     │ activate-license           │                              │
     │ --key XXX --email ...      │                              │
     ├───────────────────────────►│                              │
     │                            │                              │
     │                            │ POST /api/license/validate   │
     │                            │ {licenseKey, machineId}      │
     │                            ├─────────────────────────────►│
     │                            │                              │
     │                            │        ◄────────────────────┤
     │                            │        {valid: true}         │
     │                            │                              │
     │                            │ POST /api/license/activate   │
     │                            │ {licenseKey, machineId, email}│
     │                            ├─────────────────────────────►│
     │                            │                              │
     │                            │                              │ Check email match
     │                            │                              │ Check deployment limit
     │                            │                              │ Add machine ID to list
     │                            │                              │
     │                            │        ◄────────────────────┤
     │                            │        {success: true}       │
     │                            │                              │
     │                            │ Save to /app/license-data/   │
     │                            │ - license.json (encrypted)   │
     │                            │ - machine-id.json            │
     │                            │                              │
     │      ◄────────────────────┤                              │
     │      Success Message       │                              │
     │                            │                              │

Method 2: Environment Variables

┌────────────────┐               ┌──────────────────────┐
│  Docker Run    │               │   Container Start    │
└────────┬───────┘               └──────────┬───────────┘
         │                                  │
         │ -e LICENSE_KEY=XXX              │
         │ -e LICENSE_EMAIL=...            │
         ├─────────────────────────────────►│
         │                                  │
         │                                  │ detectEnvVars()
         │                                  │ LICENSE_KEY exists?
         │                                  │      │
         │                                  │      └──► Yes
         │                                  │           │
         │                                  │           ├─► activateLicense()
         │                                  │           │
         │                                  │           ├─► Save to license.json
         │                                  │           │
         │                                  │           └─► Delete env vars (security)
         │                                  │
         │                                  │ Continue startup
         │      ◄──────────────────────────┤
         │      Container Running           │
         │                                  │

File Structure

/app/license-data/
├── license.json              # Encrypted license data
│   ├── encryptedLicenseKey  # Encrypted with machine-specific key
│   ├── signature            # HMAC for integrity verification
│   ├── type                 # "trial" or "paid"
│   ├── expiresAt            # Expiration timestamp
│   ├── lastValidatedAt      # Last validation timestamp
│   └── lastSavedAt          # File save timestamp
│
└── machine-id.json           # Machine identification
    ├── machineId            # SHA-256 hash (hostname + platform + random)
    ├── createdAt            # Generation timestamp
    └── recovered            # true if recovered from database

Runtime License Validation

Middleware Flow

Every API request (except health/license endpoints) goes through license validation:

HTTP Request
    │
    ▼
┌─────────────────────────────┐
│ licenseValidationMiddleware │
└────────────┬────────────────┘
             │
             ├──► Check in-memory cache
             │    ├─► Valid & not expired (< 12 hours)?
             │    │   └──► Allow request ✓
             │    │
             │    └─► Expired or missing
             │        │
             │        ▼
             ├──► Call licenseClient.checkLicenseStatus()
             │    │
             │    ├──► Load license.json
             │    │
             │    ├──► Verify signature (HMAC)
             │    │
             │    ├──► Decrypt license key
             │    │
             │    ├──► Call license server API
             │    │    POST /api/license/validate
             │    │
             │    ├──► Success?
             │    │    ├─► Yes: Update cache, Allow request ✓
             │    │    │
             │    │    └─► No: Check grace period
             │    │        ├─► Within grace period?
             │    │        │   └──► Allow request ⚠️
             │    │        │
             │    │        └─► Expired
             │    │            └──► Allow request anyway (user can activate via CLI)
             │    │
             │    └──► Network Error?
             │         ├─► Check file cache (grace period)
             │         │   └──► Within grace period: Allow ⚠️
             │         │
             │         └─► Development mode: Allow ✓
             │
             ▼
        Next Middleware

Validation Frequency

Scenario	Frequency	Behavior
Normal operation	Every 12 hours	Cached in memory
Cache expired	On next API request	Revalidate with server
License server down	Use file cache	Allow if within grace period
Container restart	On startup	Read from file cache
First startup	Immediate	No license - allow access for activation

Caching Strategy

Three-Layer Cache

Layer 1: In-Memory Cache (Fast)

let licenseCache = {
  status: {
    valid: true,
    type: 'paid',
    expiresAt: '2026-01-01T00:00:00.000Z',
    daysRemaining: 365,
    lastValidatedAt: '2025-01-03T10:00:00.000Z'
  },
  lastChecked: 1704276000000,  // Unix timestamp
  ttl: 43200000  // 12 hours in milliseconds
};

Location: Backend server process memory
Lifetime: 12 hours or until process restart
Purpose: Avoid redundant license server calls
Cleared on: Container restart, process crash

Layer 2: File Cache (Persistent)

{
  "encryptedLicenseKey": {
    "encrypted": "abc123...",
    "iv": "def456..."
  },
  "type": "paid",
  "valid": true,
  "status": "activated",
  "expiresAt": "2026-01-01T00:00:00.000Z",
  "daysRemaining": 365,
  "lastValidatedAt": "2025-01-03T10:00:00.000Z",
  "signature": "789xyz...",
  "lastSavedAt": "2025-01-03T10:00:00.000Z"
}

Location: /app/license-data/license.json
Lifetime: Until container is destroyed (persists across restarts with volume)
Encryption: AES-256-CBC with machine-specific key
Integrity: HMAC-SHA256 signature
Purpose: Persist license across restarts, enable offline operation

Layer 3: Database Cache (Backup)

-- pgvista_system.license_cache table
CREATE TABLE pgvista_system.license_cache (
  machine_id VARCHAR(64) PRIMARY KEY,
  license_key_hash VARCHAR(255),
  license_type VARCHAR(20),
  expires_at TIMESTAMP,
  last_validated_at TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

Location: PostgreSQL pgvista_system schema
Purpose: Recover license if file is deleted
Access: Only metadata (hashed license key, type, expiration)
Usage: Rare (only when file cache is missing)

Cache Invalidation

Automatic Invalidation:

TTL expires (12 hours)
License expires
Server returns invalid status

Manual Invalidation:

User clicks "Refresh" in UI
CLI tool activation
Environment variable activation

Grace Period Behavior

Purpose

Allow continued operation when:

License server is temporarily unreachable
License has just expired but user is renewing
Network connectivity issues
Maintenance windows

Grace Period Duration

License Type	Grace Period	Behavior After Grace Period
Trial	3 days	Block access, prompt for activation
Paid	7 days	Block access, prompt for renewal

Calculation

function isWithinGracePeriod(licenseData, isTrial) {
  if (!licenseData || !licenseData.lastValidatedAt) {
    return false;
  }
 
  const gracePeriodDays = isTrial ? 3 : 7;
  const lastValidated = new Date(licenseData.lastValidatedAt);
  const now = new Date();
  const daysSinceValidation = (now - lastValidated) / (1000 * 60 * 60 * 24);
 
  return daysSinceValidation <= gracePeriodDays;
}

User Experience

During Grace Period:

Application remains fully functional
Warning shown in UI: "License expired - X days remaining in grace period"
Background revalidation attempts every 12 hours
User can renew license without downtime

After Grace Period:

Still allows access for CLI activation
Shows strong warnings
User must activate/renew to continue

Multi-Deployment Support

Machine ID Generation

function generateMachineId() {
  const hostname = os.hostname();
  const platform = os.platform();
  const randomBytes = crypto.randomBytes(16).toString('hex');
  
  return crypto
    .createHash('sha256')
    .update(`${hostname}-${platform}-${randomBytes}`)
    .digest('hex')
    .substring(0, 32);
}

Characteristics:

Unique per container instance
Persistent across container restarts (stored in volume)
Used to track deployment count
Submitted with every license validation

Deployment Tracking

License Server Database:

-- licenses table
CREATE TABLE licenses (
  id SERIAL PRIMARY KEY,
  license_key VARCHAR(255) UNIQUE,
  email VARCHAR(255),
  status VARCHAR(50),
  machine_ids TEXT[],  -- Array of activated machine IDs
  max_deployments INT DEFAULT 5,
  current_deployments INT DEFAULT 0,
  created_at TIMESTAMP,
  activated_at TIMESTAMP
);

Activation Logic:

// Pseudo-code from license server
async function activateLicense(licenseKey, machineId, email) {
  const license = await findLicense(licenseKey);
  
  if (!license) return { success: false, message: 'Invalid license' };
  if (license.email !== email) return { success: false, message: 'Email mismatch' };
  
  const machineIds = license.machine_ids || [];
  
  if (machineIds.includes(machineId)) {
    return { success: true, message: 'Already activated' };
  }
  
  if (machineIds.length >= license.max_deployments) {
    return { 
      success: false, 
      message: 'Deployment limit reached. Deactivate an existing deployment first.'
    };
  }
  
  machineIds.push(machineId);
  await updateLicense(license.id, {
    machine_ids: machineIds,
    current_deployments: machineIds.length,
    activated_at: new Date()
  });
  
  return { success: true, message: 'License activated' };
}

Deactivation

Users can deactivate deployments via:

License Portal: https://license.selfhosteddb.com/dashboard (opens in a new tab)
API: POST /api/license/deactivate

This frees up a deployment slot for reuse on a different machine.

Security Considerations

Encryption

License Key Storage:

Encrypted with AES-256-CBC
Key derived from hostname + environment variable
IV randomized per encryption
Stored encrypted in license.json

Machine ID:

SHA-256 hash of hostname + platform + random bytes
Not reversible to original hostname
Unique per container instance

Integrity Protection

HMAC Signature:

function signData(data) {
  const key = getEncryptionKey();
  const hmac = crypto.createHmac('sha256', key);
  hmac.update(JSON.stringify(data));
  return hmac.digest('hex');
}

Prevents tampering with license.json
Verified on every load
Invalid signature = cache invalidated

Network Security

HTTPS Only:

All license server communication over HTTPS
SSL certificate validation enforced
No fallback to HTTP

No Plain-Text Storage:

License keys never stored in plain text
Environment variables cleared after activation
Logs redact sensitive information

Environment Variable Security

// Auto-activation clears env vars after use
if (envLicenseKey && envLicenseEmail) {
  await activateLicense(envLicenseKey, envLicenseEmail);
  
  // Clear for security
  delete process.env.LICENSE_KEY;
  delete process.env.LICENSE_EMAIL;
}

Prevents:

Accidental exposure in logs
Container inspection revealing keys
Leaked credentials in orchestration configs

Performance Optimization

Reduced API Calls

Before Optimization:

License check on every API request
~1000 requests/hour = 1000 license server calls

After Optimization (Current):

License check every 12 hours
~1000 requests/hour = 2 license server calls/day
99.9% reduction in license server load

Fast Path for Cached Status

// Check in-memory cache first (microseconds)
if (licenseCache.status && 
    (now - licenseCache.lastChecked < licenseCache.ttl)) {
  return next();  // Skip network call
}
 
// Slow path: Revalidate (milliseconds to seconds)
const status = await checkLicenseStatus();

Graceful Degradation

Network issues don't block application
File cache used as fallback
Grace period prevents sudden access loss
User experience prioritized over strict enforcement