cleveragents-core/features/postgresql_backend.feature

@phase1 @database @postgresql @server_mode
Feature: PostgreSQL Storage Backend for Server Mode
  As a platform operator
  I want CleverAgents to support PostgreSQL as its storage backend
  So that server mode can handle multi-user collaborative access

  Background:
    Given I have imported the database infrastructure modules

  # ── Settings & URL Selection ──────────────────────────────────────

  @settings @database_url
  Scenario: Default database URL is SQLite in local mode
    Given I create settings with server_mode disabled
    Then the resolved database URL should start with "sqlite"

  @settings @database_url
  Scenario: Server mode with default URL raises ValueError
    Given I create settings with server_mode enabled
    And the database_url is the default SQLite value
    Then resolving the database URL should raise a ValueError

  @settings @database_url
  Scenario: Server mode with explicit PostgreSQL URL uses that URL
    Given I create settings with server_mode enabled
    And the database_url is "postgresql://myhost/mydb"
    Then the resolved database URL should be "postgresql://myhost/mydb"

  @settings @database_url
  Scenario: is_postgresql returns True for PostgreSQL URLs
    Given I create settings with server_mode enabled
    And the database_url is "postgresql://localhost/cleveragents"
    Then is_postgresql should return True

  @settings @database_url
  Scenario: is_postgresql returns False for SQLite URLs
    Given I create settings with server_mode disabled
    Then is_postgresql should return False

  @settings @database_url
  Scenario: is_postgresql returns False when server mode raises ValueError for SQLite URL
    Given I create settings with server_mode enabled
    And the database_url is the default SQLite value
    Then is_postgresql should return False

  # ── Pool Configuration ────────────────────────────────────────────

  @settings @pool
  Scenario: Default pool settings are appropriate for multi-user
    Given I create settings with server_mode enabled
    Then the db_pool_size should be 5
    And the db_max_overflow should be 10
    And the db_pool_recycle should be 1800

  @settings @pool
  Scenario: Pool settings can be overridden via environment
    Given I create settings with custom pool configuration
      | setting         | value |
      | db_pool_size    | 20    |
      | db_max_overflow | 30    |
      | db_pool_recycle | 900   |
    Then the db_pool_size should be 20
    And the db_max_overflow should be 30
    And the db_pool_recycle should be 900

  # ── UnitOfWork Engine Creation ────────────────────────────────────

  @engine @sqlite
  Scenario: UnitOfWork creates SQLite engine for local mode
    Given I create a UnitOfWork with URL "sqlite:///:memory:"
    When I access the engine
    Then the engine dialect should be "sqlite"

  @engine @pool
  Scenario: UnitOfWork passes pool parameters to non-SQLite engines
    Given I create a UnitOfWork with URL "sqlite:///:memory:" and pool_size 8
    Then the UnitOfWork pool_size should be 8
    And the UnitOfWork max_overflow should be 10
    And the UnitOfWork pool_recycle should be 1800

  # ── ORM Model Dialect Compatibility ───────────────────────────────

  @models @dialect
  Scenario: ORM models use only dialect-agnostic column types
    Given I inspect all ORM models from the Base metadata
    Then no column should use a SQLite-specific type
    And all columns should use standard SQLAlchemy types

  @models @dialect
  Scenario: ORM models define no raw SQL or sqlite3 imports
    Given I read the models module source code
    Then the source should not contain "sqlite3"
    And the source should not contain "PRAGMA"
    And the source should not contain "raw_sql"

  @models @dialect
  Scenario: All table names are valid PostgreSQL identifiers
    Given I inspect all ORM models from the Base metadata
    Then all table names should be valid SQL identifiers

  # ── Migration Runner ──────────────────────────────────────────────

  @migration
  Scenario: Migration runner handles SQLite URLs
    Given I create a MigrationRunner with URL "sqlite:///:memory:"
    Then the migration runner database_url should start with "sqlite"

  @migration
  Scenario: Migration runner handles PostgreSQL URLs
    Given I create a MigrationRunner with URL "postgresql://localhost/test"
    Then the migration runner database_url should start with "postgresql"