Storage Providers Guide¶

Cesivi Server supports multiple storage backends to accommodate different deployment scenarios, from development and testing to enterprise production deployments.

Overview¶

The server uses an IStorageService interface abstraction that allows you to choose the best storage provider for your needs:

Provider	Use Case	Performance	Persistence	Multi-Server	Licensing
InMemory	Unit testing	Fastest	No (data lost on restart)	No	Free
FileSystem	Development	Slow	Yes (JSON files)	No (shared FS possible)	Free
LiteDb	Small production	Fast	Yes (embedded DB)	No	Free
SQL Server	Enterprise production	Very fast	Yes	Yes	Commercial
PostgreSQL	Open-source production	Very fast	Yes	Yes	Free (OSS)

Storage Providers¶

1. InMemory Storage¶

Best for: Unit testing, temporary data, CI/CD pipelines

{
  "Cesivi": {
    "StorageProvider": "InMemory"
  }
}

Features: - ✅ Fastest performance (no I/O) - ✅ Zero setup required - ✅ Transaction support (snapshot-based) - ✅ Bulk operations - ❌ Data lost on restart - ❌ No multi-server support

When to use: - Running unit tests - Temporary testing environments - CI/CD pipeline validation - Local development with throw-away data

2. FileSystem Storage¶

Best for: Development, debugging, data inspection

{
  "Cesivi": {
    "StorageProvider": "FileSystem",
    "DataRootPath": "./MockData"
  }
}

Features: - ✅ Human-readable JSON files - ✅ Easy debugging (inspect files directly) - ✅ Version control friendly - ✅ Simple backup (copy folder) - ❌ Slow performance (file I/O per operation) - ❌ No transaction support - ❌ No query optimization

File Structure:

MockData/
├── WebApplications/
│   └── Cesivi/
│       └── WebApplication.json
├── SiteCollections/
│   └── {site-guid}/
│       └── SiteCollection.json
├── Webs/
│   └── {web-guid}/
│       └── Web.json
├── Lists/
│   └── {list-guid}/
│       ├── List.json
│       └── Items/
│           └── {item-id}.json
└── Files/
    └── {file-guid}.bin

When to use: - Local development - Debugging data issues - Small datasets (<1000 items) - Need to inspect data manually

3. LiteDb Storage¶

Best for: Small production deployments, embedded scenarios

{
  "Cesivi": {
    "StorageProvider": "LiteDb",
    "LiteDbPath": "./Data/sharepoint.litedb"
  }
}

Features: - ✅ Fast performance (embedded database) - ✅ Single-file deployment - ✅ Full transaction support - ✅ Bulk operations - ✅ Query optimization (indexes) - ✅ Simple backup (copy .litedb file) - ❌ No multi-server support - ❌ Limited to ~50,000 items

When to use: - Small production deployments (1-10 users) - Embedded scenarios (ship with application) - Development with realistic data volumes - Quick prototyping

4. SQL Server Storage¶

Best for: Enterprise production, Windows/Azure environments

{
  "Cesivi": {
    "StorageProvider": "SqlServer",
    "SqlServer": {
      "ConnectionString": "Server=.;Database=Cesivi;Integrated Security=true;TrustServerCertificate=true",
      "EnableRetryOnFailure": true,
      "MaxRetryCount": 3,
      "CommandTimeout": 30,
      "EnableSensitiveDataLogging": false,
      "EnableFullTextSearch": true
    }
  }
}

Features: - ✅ Enterprise-grade reliability - ✅ Very fast performance - ✅ Full transaction support (ACID) - ✅ Bulk operations (optimized) - ✅ Query optimization (indexes, execution plans) - ✅ Multi-server support (load balancing) - ✅ Full-text search built-in - ✅ Advanced backup/restore - ✅ High availability (Always On, clustering) - ⚠️ Commercial licensing required - ⚠️ Windows/Azure focused (Linux support available)

Connection String Examples:

# Integrated Security (Windows)
Server=.;Database=Cesivi;Integrated Security=true;TrustServerCertificate=true

# SQL Authentication
Server=localhost;Database=Cesivi;User Id=sa;Password=YourPassword;TrustServerCertificate=true

# Azure SQL Database
Server=tcp:yourserver.database.windows.net,1433;Initial Catalog=Cesivi;User ID=youradmin;Password=YourPassword;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

When to use: - Enterprise production (50+ users) - Multi-server deployments (load balancing) - Windows/Azure infrastructure - Need advanced backup/HA features - Large datasets (1M+ items)

Setup Guide: See SQL_SERVER_SETUP.md

5. PostgreSQL Storage¶

Best for: Open-source production, Linux/cross-platform deployments

{
  "Cesivi": {
    "StorageProvider": "PostgreSql",
    "PostgreSql": {
      "ConnectionString": "Host=localhost;Database=Cesivi;Username=postgres;Password=password",
      "EnableRetryOnFailure": true,
      "MaxRetryCount": 3,
      "CommandTimeout": 30,
      "EnableFullTextSearch": true
    }
  }
}

Features: - ✅ Open-source (no licensing costs) - ✅ Very fast performance - ✅ Full transaction support (ACID) - ✅ Bulk operations (optimized) - ✅ Query optimization (indexes, EXPLAIN) - ✅ Multi-server support (load balancing) - ✅ Full-text search (tsvector/tsquery) - ✅ JSONB column support (flexible schema) - ✅ Cross-platform (Linux, Windows, macOS) - ✅ Advanced backup/restore - ✅ High availability (replication, clustering) - ✅ Community support

Connection String Examples:

# Local PostgreSQL
Host=localhost;Database=Cesivi;Username=postgres;Password=password

# Remote PostgreSQL
Host=192.168.1.100;Port=5432;Database=Cesivi;Username=spmuser;Password=SecurePassword

# SSL Connection
Host=postgres.example.com;Database=Cesivi;Username=spmuser;Password=SecurePassword;SSL Mode=Require;Trust Server Certificate=true

# Cloud PostgreSQL (Azure, AWS RDS, Google Cloud SQL)
Host=yourserver.postgres.database.azure.com;Database=Cesivi;Username=youradmin@yourserver;Password=YourPassword;SSL Mode=Require

When to use: - Open-source production (50+ users) - Multi-server deployments (load balancing) - Linux/cross-platform infrastructure - Cloud-native deployments (Kubernetes) - Cost-sensitive production deployments - Large datasets (1M+ items)

Setup Guide: See POSTGRESQL_SETUP.md

Performance Comparison¶

Based on benchmark tests with 10,000 list items:

Operation	InMemory	FileSystem	LiteDb	SQL Server	PostgreSQL
Single item retrieval	<1ms	10-50ms	2-5ms	1-3ms	1-4ms
Bulk insert (1000 items)	<10ms	2000-5000ms	50-100ms	20-50ms	25-60ms
Query (100 items, filtered)	<5ms	100-500ms	10-20ms	5-15ms	8-18ms
File upload (1MB)	<5ms	50-100ms	10-20ms	8-15ms	10-18ms
Transaction (10 operations)	<2ms	N/A	5-10ms	3-8ms	4-10ms

Note: FileSystem performance varies greatly based on disk speed (SSD vs HDD).

Migration Between Providers¶

Use the CesiviStorageConverter tool to migrate data between storage providers:

# LiteDb → SQL Server (development → production)
CesiviStorageConverter convert \
  -s litedb --source-path "./sharepoint.litedb" \
  -t sqlserver --target-connection "Server=.;Database=Cesivi;Integrated Security=true;TrustServerCertificate=true"

# FileSystem → PostgreSQL (development → production)
CesiviStorageConverter convert \
  -s filesystem --source-path "./MockData" \
  -t postgresql --target-connection "Host=localhost;Database=Cesivi;Username=postgres;Password=password"

# SQL Server → PostgreSQL (migrate between providers)
CesiviStorageConverter convert \
  -s sqlserver --source-connection "Server=.;Database=SPM;..." \
  -t postgresql --target-connection "Host=localhost;..."

Migration Guide: See STORAGE_MIGRATION.md

Choosing the Right Provider¶

Decision Tree¶

┌─ Are you running unit tests?
│  └─ YES → InMemory
│  └─ NO ↓
│
├─ Are you developing locally?
│  └─ YES → FileSystem (debugging) or LiteDb (realistic data)
│  └─ NO ↓
│
├─ Is this a production deployment?
│  └─ YES ↓
│  │
│  ├─ Do you need multi-server (load balancing)?
│  │  └─ YES → SQL Server (Windows/Azure) or PostgreSQL (Linux/cross-platform)
│  │  └─ NO ↓
│  │
│  ├─ How many users?
│  │  ├─ <10 users → LiteDb
│  │  ├─ 10-50 users → LiteDb or SQL Server/PostgreSQL
│  │  └─ 50+ users → SQL Server or PostgreSQL
│  │
│  └─ How many items?
│     ├─ <10K items → LiteDb
│     ├─ 10K-100K items → SQL Server or PostgreSQL
│     └─ 100K+ items → SQL Server or PostgreSQL

Recommendations by Scenario¶

Scenario	Recommended Provider	Why
Unit Tests	InMemory	Fast, isolated, no cleanup needed
Local Development	FileSystem	Easy debugging, human-readable
Integration Tests	LiteDb	Realistic performance, easy cleanup
Small Production (1-10 users)	LiteDb	Simple deployment, good performance
Enterprise (Windows/Azure)	SQL Server	Best integration, HA/DR, familiar tooling
Enterprise (Linux/Cloud)	PostgreSQL	Open-source, cross-platform, cost-effective
Multi-Server	SQL Server or PostgreSQL	Shared storage, load balancing
Large Datasets (1M+ items)	SQL Server or PostgreSQL	Query optimization, scalability

Advanced Configuration¶

Connection Resilience¶

SQL Server and PostgreSQL support automatic retry on transient failures:

{
  "Cesivi": {
    "SqlServer": {
      "EnableRetryOnFailure": true,
      "MaxRetryCount": 3,
      "CommandTimeout": 30
    }
  }
}

Full-Text Search¶

SQL Server and PostgreSQL support full-text search for documents and metadata:

{
  "Cesivi": {
    "SqlServer": {
      "EnableFullTextSearch": true
    }
  }
}

Note: Requires manual full-text catalog setup (see setup guides).

Sensitive Data Logging¶

For debugging, enable sensitive data logging (NOT for production):

{
  "Cesivi": {
    "SqlServer": {
      "EnableSensitiveDataLogging": true  // ⚠️ Development only!
    }
  }
}

Troubleshooting¶

Common Issues¶

1. SQL Server Connection Failed - Check connection string format - Verify SQL Server is running: sqlcmd -S . -U sa -P YourPassword - Check firewall rules (TCP port 1433) - Verify database exists: CREATE DATABASE Cesivi;

2. PostgreSQL Connection Failed - Check connection string format - Verify PostgreSQL is running: psql -U postgres -d Cesivi - Check pg_hba.conf for authentication rules - Verify database exists: CREATE DATABASE Cesivi;

3. LiteDb File Locked - Close other processes accessing the .litedb file - Check file permissions - Use Connection=shared in connection string for multi-threaded access

4. FileSystem Permission Denied - Check folder permissions (read/write) - Verify DataRootPath exists - Run server with appropriate user permissions

Performance Optimization¶

SQL Server: - Add indexes on frequently queried columns - Use connection pooling (default in .NET) - Enable query execution plan analysis - Consider partitioning for large tables

PostgreSQL: - Add indexes (especially GIN indexes for JSONB columns) - Run VACUUM ANALYZE regularly - Use connection pooling (pgBouncer) - Configure shared_buffers and work_mem

LiteDb: - Keep database size under 2GB for best performance - Use bulk operations for large inserts - Compact database periodically