[INVESTIGATION] Evaluate Persistent Storage Options for Configuration

[giubacc]

Summary

Investigate replacing the current single JSON file configuration storage with a persistent storage backend such as SQLite or Redis.
The goal is to improve concurrency, reliability, and scalability, and to produce an ADR documenting the decision.

---

Use Case

Currently, configuration is stored in a single JSON file on disk. This approach has several limitations:

• No transactional guarantees; concurrent writes risk data loss.
• Updates require rewriting the entire file.
• Difficult to support advanced features like config history, partial updates, or live reloads.

By using a persistent storage backend, we could enable:

• Atomic updates and transactions.
• Concurrent safe reads/writes.
• Enable advanced scenarios.

---

Proposed Solution

• Investigate SQLite as a local, file-based transactional store.
• Investigate Redis as a distributed, in-memory option with persistence.
• Compare each option using criteria such as:
  • Ease of integration with the current dataclass-based config model.
  • Operational complexity and required infrastructure.
  • Migration complexity from JSON file.
• Produce an ADR containing:
  • Problem statement.
  • Requirements.
  • Options considered and their pros/cons.
  • Decision and justification.

---

Alternatives Considered

• Do nothing: Keep using the JSON file.
• Custom binary format: More efficient but harder to maintain.
• Other databases: PostgreSQL or MySQL for larger-scale needs (likely overkill).

---

Additional Context

• The current config system is dynamic and supports placeholder resolution via Resolvable dataclasses.
• The backend storage should preserve existing behavior (e.g., placeholder resolution, nested configs).
• Storage abstraction is recommended to allow backend swaps without changing application logic.

[giubacc]

Regarding this, I have conducted an initial research using SQLModel:

from contextlib import contextmanager
from typing import Any, Generator

from sqlmodel import (
    JSON,
    Column,
    Field,
    Session,
    SQLModel,
    create_engine,
)


class SvcTemplate(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)

    tag: str
    factory: str
    image: str
    hostname: str | None = None
    container_name: str | None = None
    labels: dict[str, Any] | None = Field(
        default_factory=dict, sa_column=Column(JSON)
    )
    workdir: str | None = None
    volumes: dict[str, Any] | None = Field(
        default_factory=dict, sa_column=Column(JSON)
    )
    ingress: bool | None = None
    empty_env: str | None = None
    environment: dict[str, Any] | None = Field(
        default_factory=dict, sa_column=Column(JSON)
    )
    ports: dict[str, Any] | None = Field(
        default_factory=dict, sa_column=Column(JSON)
    )
    properties: dict[str, Any] | None = Field(
        default_factory=dict, sa_column=Column(JSON)
    )
    networks: dict[str, Any] | None = Field(
        default_factory=dict, sa_column=Column(JSON)
    )
    extra_hosts: dict[str, Any] | None = Field(
        default_factory=dict, sa_column=Column(JSON)
    )
    subject_alternative_name: str | None = None


class DatabaseMng:
    def __init__(self, db_url: str, echo: bool = True):
        """
        Initialize the database manager.

        :param db_url: The database URL (SQLite file or memory DB).
        :param echo: If True, SQL queries will be logged.
        """
        db_url = (
            "sqlite:///" + db_url
            if db_url != ":memory:"
            else "sqlite:///:memory:"
        )
        self.engine = create_engine(db_url, echo=echo)

    def init_db(self) -> None:
        """
        Create all tables defined in the SQLModel metadata.
        """
        SQLModel.metadata.create_all(self.engine)

    @contextmanager
    def session(self) -> Generator[Session, None, None]:
        """
        Context manager for getting a session.
        Closes automatically when leaving the block.
        """
        with Session(self.engine) as session:
            yield session

    def dispose(self) -> None:
        """
        Dispose the engine (close all connections).
        """
        self.engine.dispose()

I would discard the idea of using REDIS, because that is an external service (overcomplicated & overkill).
We will have to evaluate if we really need to switch to a dbms.

[giubacc]

@AndreaTrasacco I was reevaluating this issue, it is perhaps too much early to address this. See consideration on: #122.
Probably, it is better in the near future to address other topics; in case, just contact me and we will find something to work on!