> For the complete documentation index, see [llms.txt](https://host2host.onibonje.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://host2host.onibonje.com/docs/04-database-driven-configuration.md).

# Database-Driven Configuration

## 1. Overview

All partner-specific, country-specific, and product-specific behavior is stored in the **relational configuration database** — not in `application.yml`, Java constants, or committed resource files. The platform is **database-agnostic**: PostgreSQL, Oracle, or SQL Server per bank policy via `h2h-persistence-spi`.

JAR modules provide **capabilities** (adapters, step types, transform engines); the database provides **behavior** (who, what, when, how).

See [45 Database-Agnostic Persistence](/docs/45-database-agnostic-persistence.md).

This is the foundation of the **low-code** approach: administrators change integration behavior through the admin portal without code changes or application redeployments.

## 2. Code vs Configuration Boundary

| Belongs in JARs (Code)                           | Belongs in Database (Config)                    |
| ------------------------------------------------ | ----------------------------------------------- |
| SFTP/Kafka/Finacle protocol handling             | Partner host paths, poll schedules, queue names |
| Transform engine implementations (JOLT, JSONata) | Mapping specifications per message type         |
| Route skeleton step types                        | Which steps run, in what order, per profile     |
| Idempotency and audit mechanisms                 | Retry counts, duplicate windows, cut-off times  |
| PGP decrypt/encrypt operations                   | Key alias references (not raw keys)             |
| Validation engine (schema validator)             | Field rules, limits, routing conditions         |
| Camel component registrations                    | Partner-to-template assignments                 |

## 3. Configuration Architecture

```mermaid
flowchart TB
  subgraph admin [Admin Portal]
    UI[Forms and Designers]
    Test[Config Test Harness]
    Approve[Approval Workflow]
  end

  subgraph db [Relational Config Store]
    Draft[(draft_config)]
    Published[(published_config)]
    Version[(config_version)]
    AuditLog[(config_audit)]
  end

  subgraph events [Event Bus]
    Kafka[CONFIG_PUBLISHED]
  end

  subgraph runtime [Integration Runtime]
    Resolver[ConfigResolver]
    Cache[ConfigCache]
    Camel[Camel Routes]
  end

  UI --> Draft
  Test --> Draft
  Approve --> Published
  Approve --> Version
  Approve --> AuditLog
  Published --> Kafka
  Kafka --> Cache
  Resolver --> Cache
  Cache -->|miss| Published
  Resolver --> Camel
```

## 4. Scope Hierarchy

Every configuration entity is scoped for multi-tenant, multi-country operations:

```
Organization
  └── Country (e.g. NG, GH, KE)
        └── Partner (e.g. ACME_CORP)
              └── Product (e.g. BULK_PAYMENT)
                    └── Channel (SFTP | API | MQ)
                          └── Environment (sandbox | production)
```

**Scope columns** on all config tables: `org_id`, `country_code`, `partner_id`, `product_code`, `channel_type`, `environment`.

RBAC filters queries by the authenticated user's scope.

## 5. Configuration Domains

### 5.1 Partner and Channel

**Tables:** `partner`, `channel_config`, `credential_ref`

| Entity           | Key Fields                                                                                                                       |
| ---------------- | -------------------------------------------------------------------------------------------------------------------------------- |
| `partner`        | id, name, country\_code, status, tier, contact                                                                                   |
| `channel_config` | partner\_id, channel\_type (`SFTP`/`HTTP`/`WEBSOCKET`/`MQ`/`SIGNED_URL`), protocol, connection\_params (JSON), poll\_cron, paths |
| `credential_ref` | partner\_id, ref\_type (SFTP\_KEY, API\_KEY, PGP), vault\_path, thumbprint                                                       |

**Example `channel_config.connection_params` (JSON):**

```json
{
  "sftpHostRef": "vault:secret/sftp/ng/acme/host",
  "inboxPath": "/inbound/payments",
  "outboxPath": "/outbound/ack",
  "filePattern": "PAY_*.csv.pgp",
  "pgpKeyRef": "vault:secret/pgp/acme/private"
}
```

> **Never store secrets in these JSON blobs** — only Vault path references.

**Example `channel_config` — HTTP (REST):**

```json
{
  "basePath": "/v1/partners/acme/payments",
  "methods": ["POST"],
  "contentTypes": ["application/json"],
  "idempotencyHeader": "X-Idempotency-Key",
  "authMode": "MTLS",
  "authRef": "vault:secret/partners/acme/api",
  "rateLimitTier": "PREMIUM",
  "syncTimeoutMs": 30000
}
```

**Example `channel_config` — WebSocket:**

```json
{
  "wsPath": "/v1/ws/partners/acme",
  "subprotocols": ["h2h.v1"],
  "authMode": "JWT_HANDSHAKE",
  "authRef": "vault:secret/partners/acme/ws-jwt",
  "heartbeatIntervalMs": 30000,
  "maxConnectionsPerPartner": 10
}
```

See [47 Gateway Entry Points](/docs/47-gateway-entrypoints.md).

### 5.2 Message Types and Integration Profiles

**Tables:** `message_type`, `integration_profile`

An **integration profile** binds a partner to a complete processing definition:

| Field                             | Description                            |
| --------------------------------- | -------------------------------------- |
| `profile_id`                      | Unique identifier                      |
| `partner_id`                      | FK to partner                          |
| `message_type`                    | BULK\_PAYMENT, COLLECTION, MT940, etc. |
| `route_template_id`               | FK to route template                   |
| `transform_inbound_id`            | FK to inbound transform spec           |
| `transform_outbound_id`           | FK to outbound/ACK transform           |
| `validation_ruleset_id`           | FK to validation rules                 |
| `routing_ruleset_id`              | FK to routing rules                    |
| `ack_template_id`                 | FK to ACK/NACK template                |
| `effective_from` / `effective_to` | Validity period                        |

### 5.3 Route Templates

**Tables:** `route_template`, `route_step`

Admins compose flows from registered step types — they do not write Camel XML.

**`route_template`:**

| Field           | Example                                           |
| --------------- | ------------------------------------------------- |
| `template_code` | BULK\_PAYMENT\_STP\_V1                            |
| `description`   | Standard bulk payment straight-through processing |
| `version`       | 1                                                 |

**`route_step`:**

| Field         | Example                     |
| ------------- | --------------------------- |
| `template_id` | FK                          |
| `step_order`  | 1, 2, 3...                  |
| `step_code`   | PGP\_DECRYPT                |
| `step_config` | JSON (step-specific params) |
| `on_error`    | RETRY / DLQ / MANUAL        |

**Example steps for BULK\_PAYMENT\_STP\_V1:**

| Order | Step Code          | Description                     |
| ----- | ------------------ | ------------------------------- |
| 1     | PGP\_DECRYPT       | Decrypt inbound file            |
| 2     | FILE\_VALIDATE     | Schema and structure validation |
| 3     | TRANSFORM\_INBOUND | Map to canonical payment batch  |
| 4     | BUSINESS\_VALIDATE | Apply business rules            |
| 5     | IDEMPOTENCY\_CHECK | Duplicate detection             |
| 6     | FINACLE\_POST      | Post payments to core banking   |
| 7     | EMIT\_AUDIT        | Publish audit event             |
| 8     | TRANSFORM\_ACK     | Generate acknowledgement file   |
| 9     | DELIVER\_ACK       | Place ACK on partner outbox     |

**`step_code` → Java bean mapping** is registered in code (JAR); the database selects which steps execute.

### 5.4 Transformation Specifications

**Table:** `transform_spec`

| Field             | Description                               |
| ----------------- | ----------------------------------------- |
| `spec_id`         | Unique ID                                 |
| `name`            | Human-readable name                       |
| `source_format`   | CSV, ISO20022, XML, JSON                  |
| `target_format`   | CANONICAL, FINACLE\_REQ, ACK\_CSV         |
| `engine`          | JOLT, JSONATA, XSLT                       |
| `spec_body`       | Engine-specific transformation definition |
| `sample_input`    | Test sample for admin harness             |
| `expected_output` | Expected result for validation            |

**Example JOLT spec (stored in `spec_body`):**

```json
[
  {
    "operation": "shift",
    "spec": {
      "payments": {
        "*": {
          "beneficiary_account": "items[&1].creditAccount",
          "amount": "items[&1].amount",
          "narration": "items[&1].narrative"
        }
      }
    }
  }
]
```

### 5.5 Validation Rules

**Table:** `validation_rule`

| Field        | Description                                   |
| ------------ | --------------------------------------------- |
| `ruleset_id` | Groups rules together                         |
| `priority`   | Evaluation order                              |
| `field_path` | JSONPath to field                             |
| `operator`   | REQUIRED, REGEX, MAX\_LENGTH, IN\_LIST, RANGE |
| `value`      | Operator parameter                            |
| `error_code` | Standardized error code for ACK/NACK          |
| `severity`   | ERROR (reject) / WARNING (log)                |

**Table:** `routing_rule`

| Field           | Description                              |
| --------------- | ---------------------------------------- |
| `ruleset_id`    | Groups rules                             |
| `priority`      | First match wins                         |
| `condition`     | Expression (SpEL, JSONata, or OPA)       |
| `action`        | ROUTE\_TO\_QUEUE, MANUAL\_REVIEW, REJECT |
| `action_params` | JSON (queue name, reason code)           |

### 5.6 Schedules and Calendars

**Table:** `cutoff_schedule`

| Field                 | Description                                                                                 |
| --------------------- | ------------------------------------------------------------------------------------------- |
| `partner_id`          | FK (nullable for country-wide)                                                              |
| `product_code`        | BULK\_PAYMENT                                                                               |
| `cron_expression`     | Schedule expression — defines cut-off window; evaluated at transaction time, not a cron job |
| `timezone`            | Africa/Lagos                                                                                |
| `holiday_calendar_id` | FK                                                                                          |
| `action_on_miss`      | REJECT, QUEUE\_NEXT\_DAY                                                                    |

**Table:** `holiday_calendar`

| Field          | Description         |
| -------------- | ------------------- |
| `calendar_id`  | Unique ID           |
| `country_code` | NG                  |
| `holidays`     | JSON array of dates |

### 5.7 ACK/NACK Templates

**Table:** `ack_template`

| Field                    | Description                          |
| ------------------------ | ------------------------------------ |
| `template_id`            | Unique ID                            |
| `partner_id`             | FK                                   |
| `message_type`           | BULK\_PAYMENT                        |
| `format`                 | CSV, XML, JSON                       |
| `filename_pattern`       | `ACK_{partner}_{date}_{batchId}.csv` |
| `transform_spec_id`      | FK to transform for ACK body         |
| `nack_transform_spec_id` | FK for rejection format              |

### 5.8 Reconciliation Configuration

**Table:** `recon_config`

| Field                    | Description                           |
| ------------------------ | ------------------------------------- |
| `config_id`              | Unique ID                             |
| `partner_id`             | FK                                    |
| `matching_keys`          | JSON array of field paths             |
| `tolerance_amount`       | Decimal tolerance for amount matching |
| `tolerance_days`         | Date window for matching              |
| `exception_bucket_rules` | JSON                                  |

## 6. Configuration Versioning and Lifecycle

### 6.1 States

| State              | Description               | Runtime Visible             |
| ------------------ | ------------------------- | --------------------------- |
| `DRAFT`            | Work in progress          | No (sandbox simulator only) |
| `PENDING_APPROVAL` | Submitted for review      | No                          |
| `PUBLISHED`        | Active configuration      | Yes                         |
| `SUPERSEDED`       | Replaced by newer version | No (retained for audit)     |
| `ARCHIVED`         | Deactivated               | No                          |

### 6.2 Publish Workflow

```mermaid
sequenceDiagram
  participant Analyst
  participant Admin as Admin Portal
  participant WF as h2h-workflow
  participant DB as Config Store
  participant EventBus
  participant Runtime

  Analyst->>Admin: Edit draft config
  Analyst->>Admin: Run test harness
  Admin->>Admin: Validate against schema
  Analyst->>Admin: Submit for approval
  Admin->>DB: status = PENDING_APPROVAL
  Admin->>WF: ApprovalService.submit(CONFIG_PUBLISH)
  Note over WF: Checker approves (≠ maker)
  WF->>DB: status = PUBLISHED, version++
  WF->>DB: Write config_audit row
  WF->>EventBus: CONFIG_PUBLISHED
  EventBus->>Runtime: Invalidate cache
  Runtime->>DB: Load new published config
```

### 6.3 Rollback

Rollback is **republishing a previous version** — no redeploy required:

1. Select historical version in admin portal
2. Supervisor approves rollback
3. New `PUBLISHED` row created (old row → `SUPERSEDED`)
4. Cache invalidation event fired

### 6.4 Audit Trail

**Table:** `config_audit`

| Field               | Description                                     |
| ------------------- | ----------------------------------------------- |
| `audit_id`          | UUID                                            |
| `entity_type`       | partner, transform\_spec, route\_template, etc. |
| `entity_id`         | FK to changed entity                            |
| `action`            | CREATE, UPDATE, PUBLISH, ROLLBACK, ARCHIVE      |
| `previous_snapshot` | JSON (before state)                             |
| `new_snapshot`      | JSON (after state)                              |
| `performed_by`      | User ID                                         |
| `performed_at`      | Timestamp                                       |
| `approval_ref`      | Camunda process instance ID                     |

## 7. Runtime Configuration Resolution

### 7.1 ConfigResolver API

```java
public interface ConfigResolver {
    IntegrationProfile resolve(ResolveKey key);
    TransformSpec getTransform(String specId);
    List<ValidationRule> getRules(String rulesetId);
    List<RouteStep> getRouteSteps(String templateId);
    AckTemplate getAckTemplate(String templateId);
}

public record ResolveKey(
    String partnerId,
    String messageType,
    String environment
) {}
```

### 7.2 Resolution Flow

```mermaid
sequenceDiagram
  participant Route as Camel Route
  participant Resolver as ConfigResolver
  participant Cache as ConfigCache
  participant DB as PostgreSQL

  Route->>Resolver: resolve(partnerId, messageType)
  Resolver->>Cache: get(cacheKey)
  alt cache hit
    Cache-->>Resolver: IntegrationProfile
  else cache miss
    Resolver->>DB: SELECT published config
    DB-->>Resolver: IntegrationProfile
    Resolver->>Cache: put(cacheKey, profile)
  end
  Resolver-->>Route: IntegrationProfile
  Route->>Route: Execute steps from profile
```

### 7.3 Caching Strategy

| Aspect                    | Policy                                                    |
| ------------------------- | --------------------------------------------------------- |
| Cache implementation      | Caffeine (in-process)                                     |
| Cache key                 | `partnerId:messageType:environment:configVersion`         |
| TTL                       | 5 minutes (safety net)                                    |
| Invalidation              | `CONFIG_PUBLISHED` event via `EventPublisher` (immediate) |
| DB reads per batch        | One profile resolution at batch start                     |
| DB reads per payment line | Zero (profile attached to exchange)                       |

### 7.4 Exchange Property Convention

After resolution, attach to Camel exchange:

```java
exchange.setProperty("h2h.profile", integrationProfile);
exchange.setProperty("h2h.partnerId", profile.getPartnerId());
exchange.setProperty("h2h.correlationId", correlationId);
```

All subsequent steps read from exchange properties — not global state.

## 8. Dynamic Pipeline Execution

Route modules execute DB-defined step sequences:

```java
from("direct:execute-profile")
    .process(exchange -> {
        IntegrationProfile profile = exchange.getProperty("h2h.profile", IntegrationProfile.class);
        List<RouteStep> steps = configResolver.getRouteSteps(profile.getRouteTemplateId());
        exchange.setProperty("h2h.steps", steps);
        exchange.setProperty("h2h.stepIndex", 0);
    })
    .loop(simple("${exchangeProperty.h2h.steps.size}"))
        .process(stepExecutor)  // looks up step_code → Spring bean
    .end();
```

**Step registry** (in JAR):

```java
@Component
public class StepRegistry {
    private final Map<String, Processor> steps;

    public Processor get(String stepCode) {
        return steps.get(stepCode);  // PGP_DECRYPT → pgpDecryptProcessor
    }
}
```

## 9. Schema Migrations

Database schema is managed with **Flyway** in `h2h-config-store`:

```
src/main/resources/db/migration/
├── V1__create_partner_tables.sql
├── V2__create_route_template_tables.sql
├── V3__create_transform_tables.sql
├── V4__create_validation_tables.sql
├── V5__create_config_version_audit.sql
└── ...
```

Migrations are **additive** — never destructive in production. Deprecate columns, don't drop.

## 10. Config Validation on Publish

Before publishing, the admin API validates:

| Check                 | Description                                                     |
| --------------------- | --------------------------------------------------------------- |
| Schema validation     | JSON fields match expected schemas                              |
| Step code registry    | All `step_code` values exist in `StepRegistry`                  |
| Transform test        | Run `sample_input` through engine, compare to `expected_output` |
| Referential integrity | All FKs resolve to published entities                           |
| Scope check           | User has permission for target scope                            |
| Effective dates       | No overlap conflicts for same partner/product                   |

## 11. What Never Goes in the Database

| Item                           | Storage                               |
| ------------------------------ | ------------------------------------- |
| Passwords, private keys        | HashiCorp Vault                       |
| Kafka broker URLs              | Environment variables / K8s ConfigMap |
| Database connection strings    | K8s secrets                           |
| Camel component class names    | JAR code (step registry)              |
| Finacle WSDL endpoints (infra) | Environment config per deployment     |
| Raw PGP key material           | Vault only                            |

## 12. GitOps Export (Optional)

For banks requiring Git-backed audit:

* Nightly export of all `PUBLISHED` config to Git repository
* Export is **read-only mirror** — database remains source of truth
* Format: JSON or YAML per partner profile

## 13. Anti-Patterns (Enforced in Code Review)

| Violation                                       | Detection                                      |
| ----------------------------------------------- | ---------------------------------------------- |
| `if (partnerId.equals("ACME"))` in route JARs   | ArchUnit / grep CI check                       |
| `@Value("${partner.acme.sftp.host}")`           | ArchUnit ban on partner-specific property keys |
| Mapping files in `src/main/resources/mappings/` | CI check — mappings must be in DB              |
| Secrets in `channel_config` JSON                | Schema validation rejects key patterns         |

## 14. Related Documents

* [Low-Code Admin Platform](/docs/05-low-code-admin-platform.md)
* [Modular JAR Architecture](/docs/03-modular-jar-architecture.md)
* [Camel Integration Patterns](/docs/08-camel-integration-patterns.md)


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://host2host.onibonje.com/docs/04-database-driven-configuration.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.