Description

Create a DatabaseProviderPlugin (DPB) responsible for managing relational database access through configurable datasources.

This plugin must rely on jOOQ for SQL construction and execution.
At this stage, only PostgreSQL is supported.

The plugin must integrate with the global YAML configuration model and allow entities to be persisted dynamically based on external configuration, without static entity definitions.

Filtering and pagination logic must not be implemented yet. However, API responses must return structurally valid paginated responses.

Objectives

• Introduce a new provider type: database
• Support PostgreSQL exclusively (initial scope)
• Use jOOQ for all SQL interactions
• Allow multiple PostgreSQL providers to be declared
• Dynamically map entities to tables and attributes to columns
• Perform basic CRUD operations (initial focus: insert + basic read)
• Return valid page structures without implementing real pagination

⚠️ Critical Requirement

A connection pool must be implemented and properly configured.

The plugin must not use raw DriverManager connections.

Requirements:

• Use a production-grade connection pool (e.g., HikariCP)

• One pool per configured provider

• Proper configuration of:

  • maximum pool size
  • idle timeout
  • connection timeout
  • validation query (if needed)

• Ensure safe lifecycle management (startup / shutdown)

• Prevent connection leaks

Connection pooling is mandatory for performance, scalability, and resource safety.

Configuration Model

The plugin must support integration with a global YAML configuration structured as follows:

providers:
  - name: ExampleDatabase
    type: database
    url: jdbc:postgresql://DATABASE_HOST:DATABASE_PORT/DATABASE_NAME
    username: DATABASE_USER
    password: DATABASE_PASSWORD

entities:
  - name: account
    route: accounts
    provider: ExampleDatabase
    tasks:
      (...)
    access:
      table: ACCOUNT_TABLE_NAME
    attributes:
      - name: id
        type: String
        required: false
        input: Text
        access:
          column: ID_COLUMN_NAME

Technical Requirements

1. jOOQ Integration

• Use jOOQ DSLContext for all queries
• Use bind parameters for all values
• Do not concatenate SQL values directly
• Use dynamic table and field resolution via DSL.name(...)
• Validate table and column names against configuration before executing queries

2. PostgreSQL Support (Only)

• The plugin must support PostgreSQL exclusively
• Use PostgreSQL JDBC driver
• No abstraction layer for other databases yet
• Architecture should remain extensible for future RDBMS support

3. Provider Registration

• Detect providers with type: database
• Initialize a PostgreSQL datasource per provider
• Attach a dedicated connection pool per provider
• Support multiple configured PostgreSQL datasources simultaneously

4. Entity Binding

For each entity referencing a database provider:

• Resolve target table from entities[].access.table
• Resolve column mappings from entities[].attributes[].access.column
• Allow only declared attributes to be persisted
• Do not rely on static JPA entities
• All persistence logic must be dynamic and configuration-driven

5. Data Operations (Initial Scope)

The plugin must support:

• Insert operation using dynamic column mapping
• Basic read operation (select all rows from mapped table)

All operations must:

• Use jOOQ
• Use bind parameters
• Restrict columns to those declared in configuration
• Avoid runtime SQL injection risks

6. Pagination Contract (Without Real Pagination)

Filtering and pagination logic must not be implemented.

However, API responses must return a valid page structure, for example:

{
  "content": [...],
  "page": 0,
  "size": 20,
  "totalElements": X,
  "totalPages": 1
}

For now:

• page defaults to 0
• size defaults to a configured or safe default
• totalElements reflects actual row count
• totalPages may be set to 1

The response contract must remain forward-compatible with future pagination implementation.

Out of Scope

• Filtering
• Sorting
• Real pagination
• Joins and relationship handling
• Schema migration management
• Support for databases other than PostgreSQL