> 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/45-database-agnostic-persistence.md).

# Database-Agnostic Persistence

## 1. Overview

The H2H platform is **database-agnostic**: the same application JARs run against **PostgreSQL**, **Oracle**, or **Microsoft SQL Server** without code forks. The bank selects the RDBMS via configuration and Flyway dialect paths — not by rebuilding the platform.

| Principle                         | Meaning                                                                                                          |
| --------------------------------- | ---------------------------------------------------------------------------------------------------------------- |
| **Database-agnostic application** | Route modules, admin APIs, and config runtime never embed vendor SQL or dialect-specific types                   |
| **Database-driven configuration** | Partner behavior lives in the **relational config store** (any supported RDBMS)                                  |
| **PostgreSQL as default**         | Local dev, CI, and reference deployments use PostgreSQL; production may use Oracle or SQL Server per bank policy |

**Module:** `h2h-persistence-spi` — vendor abstraction, `JsonColumnMapper`, pagination, `SqlTaskExecutor`.

See also: [14 Extensibility Framework](/docs/14-extensibility-framework.md) §6, [30 Database Schema Reference](/docs/30-database-schema-reference.md), [UC-12 Oracle deployment](/docs/22-use-cases-and-solutions.md#uc-12-oracle-database-deployment).

***

## 2. Architecture

```mermaid
flowchart TB
  subgraph app [Application - vendor neutral]
    Routes[h2h-*-routes]
    Admin[h2h-admin-api]
    ConfigRt[h2h-config-runtime]
    Repos[Repositories via JPA / jOOQ]
  end

  subgraph spi [h2h-persistence-spi]
    Vendor[PersistenceVendorSelector]
    Json[JsonColumnMapper SPI]
    Page[Pagination SPI]
    SqlTask[SqlTaskExecutor]
    Flyway[FlywayDialectLocator]
  end

  subgraph rdbms [Supported RDBMS - deploy-time choice]
    PG[(PostgreSQL)]
    ORA[(Oracle)]
    MSSQL[(SQL Server)]
  end

  Routes --> Repos
  Admin --> Repos
  ConfigRt --> Repos
  Repos --> spi
  spi --> PG
  spi --> ORA
  spi --> MSSQL
```

**Rule:** No module outside `h2h-persistence-spi` and `h2h-config-store` (migrations) imports `org.postgresql.*`, `oracle.jdbc.*`, or `com.microsoft.sqlserver.*`.

***

## 3. Supported Databases

| Vendor         | Config value | Hibernate dialect (example) | Flyway path                | Status                                   |
| -------------- | ------------ | --------------------------- | -------------------------- | ---------------------------------------- |
| **PostgreSQL** | `postgresql` | `PostgreSQLDialect`         | `db/migration/postgresql/` | Default — dev, CI, reference prod        |
| **Oracle**     | `oracle`     | `OracleDialect`             | `db/migration/oracle/`     | Supported — common in enterprise banking |
| **SQL Server** | `sqlserver`  | `SQLServerDialect`          | `db/migration/sqlserver/`  | Supported — bank infrastructure option   |

Adding a fourth vendor requires: dialect Flyway scripts, `JsonColumnMapper` implementation, Hibernate dialect profile, and CI Testcontainers job — **no changes to route or admin modules**.

***

## 4. Configuration

### 4.1 Runtime vendor selection

```yaml
h2h:
  persistence:
    vendor: postgresql    # postgresql | oracle | sqlserver
    schema: h2h_config

spring:
  datasource:
    url: ${H2H_DB_URL}    # jdbc URL — vendor-specific, from Vault/Helm
    username: ${H2H_DB_USER}
    password: ${H2H_DB_PASSWORD}
  jpa:
    database-platform: ${H2H_DB_PLATFORM:org.hibernate.dialect.PostgreSQLDialect}
    properties:
      hibernate:
        default_schema: ${h2h.persistence.schema}
```

### 4.2 Helm / environment examples

| Environment              | Typical vendor              |
| ------------------------ | --------------------------- |
| `docker-compose` (local) | PostgreSQL                  |
| CI pipeline              | PostgreSQL (Testcontainers) |
| Bank A production        | Oracle                      |
| Bank B production        | PostgreSQL (managed RDS)    |
| Bank C production        | SQL Server                  |

***

## 5. Persistence SPI Components

| SPI / component        | Responsibility                                                                      |
| ---------------------- | ----------------------------------------------------------------------------------- |
| `PersistenceVendor`    | Enum + bean wiring for active dialect                                               |
| `JsonColumnMapper`     | Read/write JSON columns across PG JSONB, Oracle JSON/CLOB, SQL Server NVARCHAR JSON |
| `PaginationAdapter`    | `LIMIT/OFFSET` vs `ROWNUM` vs `OFFSET/FETCH` — hidden from repositories             |
| `SqlTaskExecutor`      | Runs approved `sql_task_def` with dialect-specific SQL variants                     |
| `FlywayDialectLocator` | Runs `common/` then `{vendor}/` migration sets                                      |
| `SequenceStrategy`     | Identity vs sequence per vendor for PK generation                                   |

All repositories and domain services depend on **interfaces** in `h2h-persistence-spi` or JPA entities with portable column types.

***

## 6. Schema and Migration Strategy

### 6.1 Flyway layout

```
db/migration/
├── common/                    # ANSI-aligned DDL — all vendors
│   V1__partner_and_channel.sql
│   V2__integration_profile.sql
│   ...
├── postgresql/
│   V1.1__jsonb_indexes.sql
├── oracle/
│   V1.1__json_indexes.sql
└── sqlserver/
    V1.1__json_check.sql
```

### 6.2 Portability rules (mandatory)

| Rule                                                                 | Rationale                                                      |
| -------------------------------------------------------------------- | -------------------------------------------------------------- |
| Use `VARCHAR`, `NUMERIC`, `TIMESTAMP WITH TIME ZONE` in `common/`    | ANSI-aligned types                                             |
| No `JSONB`, `ROWNUM`, `TOP` in `common/` scripts                     | Vendor-specific → dialect folder                               |
| No vendor SQL in route or admin Java code                            | Single abstraction layer                                       |
| JSON access only via `JsonColumnMapper` or JPA `@JdbcTypeCode(JSON)` | Hides storage differences                                      |
| Pagination only via `PaginationAdapter`                              | No raw `LIMIT` in shared queries                               |
| Idempotency / locking via SPI or JPA                                 | Avoid `FOR UPDATE SKIP LOCKED` in routes without dialect guard |

### 6.3 JSON column mapping

| Database   | Physical type                  | Access                                                |
| ---------- | ------------------------------ | ----------------------------------------------------- |
| PostgreSQL | `JSONB`                        | Hibernate JSON + optional GIN index in dialect script |
| Oracle     | `JSON` (12c+) or `CLOB`        | `JsonColumnMapper` + dialect index                    |
| SQL Server | `NVARCHAR(MAX)` + `JSON_CHECK` | `JsonColumnMapper`                                    |

***

## 7. What Is Database-Agnostic vs Fixed

| Database-agnostic (config + tx RDBMS)   | Fixed technology (by design)                 |
| --------------------------------------- | -------------------------------------------- |
| Partner profiles, routes, rules, jobs   | **Kafka** — event backbone                   |
| Transaction index, file registry, recon | **Redis** (optional) — cache / idempotency   |
| Audit config versions                   | **OpenSearch** (optional) — full-text search |
| Custom attributes (EAV / JSON rows)     | **MinIO/S3** — object storage for files      |
| SQL tasks (approved, dialect variants)  | **Vault** — secrets (not in DB)              |

The platform is **RDBMS-agnostic for all application state**; ancillary stores (cache, events, files) use their own SPIs with the same extensibility pattern.

***

## 8. Module Boundaries

| Module                | Database coupling                                     |
| --------------------- | ----------------------------------------------------- |
| `h2h-persistence-spi` | **Owns** all vendor differences                       |
| `h2h-config-store`    | JPA entities + Flyway migrations (portable + dialect) |
| `h2h-config-runtime`  | Uses repositories only — no SQL                       |
| `h2h-*-routes`        | **No** direct DB access; config via `ConfigResolver`  |
| `h2h-admin-api`       | JPA repositories — portable entities                  |
| `h2h-reconciliation`  | Repositories + optional jOOQ via SPI                  |

***

## 9. Testing Strategy

| Test             | Approach                                                                                    |
| ---------------- | ------------------------------------------------------------------------------------------- |
| Unit             | H2 in-memory or mocked repositories                                                         |
| Integration (CI) | Testcontainers: PostgreSQL **required**; Oracle + SQL Server on nightly or release pipeline |
| Architecture     | ArchUnit: no vendor driver imports outside `h2h-persistence-spi` / `h2h-config-store`       |
| UC-12 validation | Full regression on Oracle profile before bank sign-off                                      |

***

## 10. Switching Database (Operations)

To deploy on Oracle instead of PostgreSQL:

1. Provision Oracle schema and JDBC URL in Vault.
2. Set `h2h.persistence.vendor: oracle` and `H2H_DB_PLATFORM` to Oracle dialect.
3. Run Flyway with `oracle` dialect path enabled.
4. Deploy **same** `h2h-runtime` image — no JAR changes.
5. Run UC-12 regression suite.

See [22 Use Cases — UC-12](/docs/22-use-cases-and-solutions.md#uc-12-oracle-database-deployment).

***

## 11. Related Documents

* [04 Database-Driven Configuration](/docs/04-database-driven-configuration.md)
* [14 Extensibility Framework](/docs/14-extensibility-framework.md) §6
* [30 Database Schema Reference](/docs/30-database-schema-reference.md)
* [02 Technology Stack](/docs/02-technology-stack.md)
* [19 Cloud-Agnostic Deployment](/docs/19-cloud-agnostic-deployment.md)
* [35 Testing Strategy](/docs/35-testing-strategy.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/45-database-agnostic-persistence.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.