Version: v0.13

Database Routing

The EDK provides a flexible database routing system that directs database operations to the correct database, schema, or host based on the current scope (application, tenant, or user). This enables true multi-tenant isolation at the database level.

Overview

The database routing system consists of several components:

DatabaseRouter - Routes operations to the appropriate database based on scope
DatabaseScope - Defines the isolation level (APP, TENANT, USER)
IsolationStrategy - Determines how databases are isolated (shared, schema, database, host)
ConnectionPoolManager - Manages connection pools with HikariCP
DatabaseRegistry - Stores and retrieves database configurations

Database Scopes

The EDK supports three levels of database scoping:

enum class DatabaseScope {
    APP,     // Application-wide data shared across all tenants
    TENANT,  // Tenant-specific data (most common)
    USER     // User-specific data (e.g., wallet backends)
}

Scope	Use Case	Entity ID
`APP`	System config, feature flags, global lookups	`null` or `"default"`
`TENANT`	Organization data, party management, credentials	Tenant UUID
`USER`	Personal wallet data, user preferences	User/Principal UUID

Isolation Strategies

The isolation strategy determines how data is physically separated at the infrastructure level:

Shared

All entities use the same database and schema. Isolation is achieved via discriminator columns (tenant_id, user_id).

IsolationStrategy.Shared

Best for: Standard tenants with moderate data volumes where row-level isolation is sufficient.

Pool behavior: Single shared connection pool for the database.

Schema Per Entity

Each entity gets its own schema within a shared database. PostgreSQL supports this well.

IsolationStrategy.SchemaPerEntity(
    schemaNamePattern = "tenant_{id}"  // Results in: tenant_abc123
)

Best for: Premium tenants needing stronger isolation, or user-scoped databases in wallet scenarios.

Pool behavior: Shared pool at database level; schema is set per connection.

Database Per Entity

Each entity gets a dedicated database instance.

IsolationStrategy.DatabasePerEntity(
    databaseNamePattern = "vdx_{scope}_{id}"  // Results in: vdx_tenant_abc123
)

Best for: Large data volumes or stricter isolation requirements.

Pool behavior: Separate connection pool per database.

Host Per Entity

Each entity gets a dedicated database server/host. Used for enterprise tenants with regulatory requirements.

IsolationStrategy.HostPerEntity(
    requireDedicatedPool = true
)

Best for: Enterprise tenants with compliance requirements (data residency, dedicated resources).

Pool behavior: Dedicated pool per host.

Strategy Comparison

Strategy	Isolation	Resource Usage	Complexity	Use Case
Shared	Row-level	Low	Low	Most tenants
SchemaPerEntity	Schema-level	Medium	Medium	Premium tenants
DatabasePerEntity	Database-level	High	High	Large tenants
HostPerEntity	Server-level	Highest	Highest	Enterprise/Regulated

Using the DatabaseRouter

Basic Usage

import com.sphereon.data.store.db.routing.DatabaseRouter
import com.sphereon.data.store.db.routing.DatabaseScope
import jakarta.inject.Inject
import jakarta.inject.Singleton

@Singleton
class TenantDataService @Inject constructor(
    private val router: DatabaseRouter
) {
    suspend fun getParties(tenantId: String): List<Party> {
        // Get driver for tenant's database
        val driver = router.getDriver(DatabaseScope.TENANT, tenantId)

        // Use the driver with your database queries
        val database = PartyDatabase(driver)
        return database.partyQueries.findAll().executeAsList()
    }
}

Convenience Methods

The DatabaseRouter provides convenience methods for common scopes:

// Tenant-scoped database (most common)
val tenantDriver = router.getDriverForTenant(tenantUuid)

// User-scoped database (wallet data)
val userDriver = router.getDriverForUser(userUuid)

// App-scoped database (global data)
val appDriver = router.getDriverForApp("default")

Getting Database Metadata

// Get the dialect for a scoped entity
val dialect = router.getDialect(DatabaseScope.TENANT, tenantId)

// Get the isolation strategy
val strategy = router.getIsolationStrategy(DatabaseScope.TENANT, tenantId)

// Get the effective schema name (for SchemaPerEntity)
val schema = router.getSchema(DatabaseScope.TENANT, tenantId)

Health Checks

// Check if a database is healthy
val isHealthy = router.isHealthy(DatabaseScope.TENANT, tenantId)

Resource Cleanup

When a tenant is disabled or deleted:

// Release cached drivers and connection pools
router.releaseResources(DatabaseScope.TENANT, tenantId)

Database Configuration

ScopedDatabaseConfig

Each database is configured with ScopedDatabaseConfig:

import com.sphereon.data.store.db.routing.ScopedDatabaseConfig
import com.sphereon.data.store.db.routing.DatabaseDialect
import com.sphereon.data.store.db.routing.IsolationStrategy

// Tenant-scoped configuration
val tenantConfig = ScopedDatabaseConfig.forTenant(
    tenantId = "tenant-abc123",
    dialect = DatabaseDialect.POSTGRESQL,
    host = "db.example.com",
    port = 5432,
    database = "vdx",
    username = "vdx_app",
    password = secrets.getDatabasePassword(),
    isolationStrategy = IsolationStrategy.SchemaPerEntity(
        schemaNamePattern = "tenant_{id}"
    ),
    tier = ServiceTier.PREMIUM
)

// App-scoped configuration
val appConfig = ScopedDatabaseConfig.forApp(
    dialect = DatabaseDialect.POSTGRESQL,
    host = "db.example.com",
    port = 5432,
    database = "vdx_system",
    username = "vdx_system",
    password = secrets.getSystemPassword()
)

// User-scoped configuration
val userConfig = ScopedDatabaseConfig.forUser(
    userId = "user-xyz789",
    dialect = DatabaseDialect.SQLITE,
    host = "",
    port = 0,
    database = "/data/wallets/user_xyz789.db",
    username = "",
    password = ""
)

Configuration Properties

Configure databases via properties files:

# Default tenant database configuration
db.routing.tenant.default.dialect=POSTGRESQL
db.routing.tenant.default.host=localhost
db.routing.tenant.default.port=5432
db.routing.tenant.default.database=vdx
db.routing.tenant.default.username=vdx_app
db.routing.tenant.default.password=${DB_PASSWORD}
db.routing.tenant.default.isolation=SHARED

# Premium tenant with schema isolation
db.routing.tenant.premium.isolation=SCHEMA_PER_ENTITY
db.routing.tenant.premium.schema-pattern=tenant_{id}

# Enterprise tenant with dedicated host
db.routing.tenant.enterprise-acme.dialect=POSTGRESQL
db.routing.tenant.enterprise-acme.host=acme.db.example.com
db.routing.tenant.enterprise-acme.isolation=HOST_PER_ENTITY

Connection Pooling

ConnectionPoolConfig

Configure connection pools for optimal performance:

import com.sphereon.data.store.db.routing.ConnectionPoolConfig

// Default configuration
val defaultPool = ConnectionPoolConfig.DEFAULT

// High-throughput configuration
val highThroughput = ConnectionPoolConfig.HIGH_THROUGHPUT

// Low-resource configuration
val lowResource = ConnectionPoolConfig.LOW_RESOURCE

// Custom configuration
val customPool = ConnectionPoolConfig(
    maximumPoolSize = 20,
    minimumIdle = 5,
    connectionTimeout = 15_000,    // 15 seconds
    idleTimeout = 300_000,         // 5 minutes
    maxLifetime = 1_200_000,       // 20 minutes
    keepaliveTime = 60_000,        // 1 minute
    validationTimeout = 5_000,     // 5 seconds
    dedicatedPool = false
)

Pool Configuration Presets

Preset	Max Pool	Min Idle	Timeout	Use Case
`DEFAULT`	10	2	30s	General purpose
`HIGH_THROUGHPUT`	25	10	10s	High-traffic services
`LOW_RESOURCE`	5	1	60s	Edge/embedded deployments

Pool sharing is determined by the isolation strategy:

Strategy	Pool Sharing
Shared	Single pool for entire database
SchemaPerEntity	Pool shared at database level
DatabasePerEntity	Pool per database instance
HostPerEntity	Dedicated pool per host

Force a dedicated pool for specific entities:

val config = ScopedDatabaseConfig.forTenant(
    tenantId = "high-priority-tenant",
    // ... other config
    poolConfig = ConnectionPoolConfig(
        dedicatedPool = true,
        maximumPoolSize = 20
    )
)

Database Registry

The EDK provides two registry implementations for storing database configurations:

Configuration-Based Registry

Reads configurations from property files. Best for static deployments.

import com.sphereon.data.store.db.routing.config.ConfigDatabaseRegistryImpl

// Automatically configured via DI
@Singleton
class MyService @Inject constructor(
    private val registry: DatabaseRegistry
) {
    suspend fun getTenantConfig(tenantId: String): ScopedDatabaseConfig? {
        return registry.getConfig(DatabaseScope.TENANT, tenantId)
    }
}

Database-Backed Registry

Stores configurations in a bootstrap database. Enables runtime configuration changes.

import com.sphereon.data.store.db.routing.database.DatabaseRegistryImpl
import com.sphereon.data.store.db.routing.database.BootstrapDatabaseProvider

// Bootstrap database stores routing configurations
val bootstrapProvider = BootstrapDatabaseProvider(
    jdbcUrl = "jdbc:postgresql://localhost:5432/vdx_bootstrap",
    username = "vdx_admin",
    password = secrets.getBootstrapPassword()
)

// Registry reads/writes to bootstrap database
val registry = DatabaseRegistryImpl(bootstrapProvider)

// Add a new tenant at runtime
registry.register(newTenantConfig)

Integration with Spring Boot

When using @EnableSphereonRestApi, the DatabaseRouter is automatically available:

@RestController
class PartyController @Inject constructor(
    private val router: DatabaseRouter,
    private val userContext: UserContext  // Contains tenantId
) {
    @GetMapping("/parties")
    suspend fun getParties(): List<Party> {
        val driver = router.getDriverForTenant(userContext.tenantId)
        // ... execute queries
    }
}

Supported Databases

Database	Dialect	Features
PostgreSQL	`POSTGRESQL`	Full support, schemas, JSONB
MySQL	`MYSQL`	Full support, JSON columns
SQLite	`SQLITE`	Single-file, edge deployments
H2	`H2`	Testing and development

Best Practices

Choose the right isolation strategy based on tenant requirements. Start with Shared and upgrade to stronger isolation as needed.

Size connection pools appropriately. Too few connections causes contention; too many wastes resources. Monitor pool metrics and adjust.

Use the database-backed registry for dynamic tenant onboarding where configurations change at runtime.

Implement health checks in your monitoring to detect database connectivity issues early.

Clean up resources when tenants are deactivated to release connections and memory.

Overview​

Database Scopes​

Isolation Strategies​

Shared​

Schema Per Entity​

Database Per Entity​

Host Per Entity​

Strategy Comparison​

Using the DatabaseRouter​

Basic Usage​

Convenience Methods​

Getting Database Metadata​

Health Checks​

Resource Cleanup​

Database Configuration​

ScopedDatabaseConfig​

Configuration Properties​

Connection Pooling​

ConnectionPoolConfig​

Pool Configuration Presets​

Pool Sharing​

Database Registry​

Configuration-Based Registry​

Database-Backed Registry​

Integration with Spring Boot​

Supported Databases​

Best Practices​