Schema Management

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• src/meltano_state_backend_postgresql/backend.py
• tests/test_backwards_compat.py
• tests/test_integration.py
• tests/test_conninfo.py
• pyproject.toml

Schema Management in the meltano-state-backend-postgresql extension encompasses the definition, initialization, and configuration of the database structures required to store Meltano state. This system ensures that the necessary PostgreSQL tables exist and are correctly scoped within the database environment, supporting both default configurations and custom schema/table names.

The primary responsibility of the schema management logic is to translate Meltano settings into valid PostgreSQL identifiers and ensure the state table is provisioned upon initialization. It supports multitenancy through schema isolation and provides backward compatibility with Meltano's internal system database schema.

Sources: README.md:1-40, src/meltano_state_backend_postgresql/backend.py:1-25

Data Model Definition

The state backend utilizes a single table to manage all job states. This table is designed to store JSON-formatted state data and tracking metadata.

State Table Schema

The table structure is defined within the _ensure_tables method of the PostgreSQLStateStoreManager.

Field Name	Data Type	Constraints	Description
state_id	VARCHAR(900)	PRIMARY KEY	Unique identifier for the Meltano job or state entity.
partial_state	TEXT	-	JSON string representing the partial (uncommitted) state.
completed_state	TEXT	-	JSON string representing the successfully completed state.
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	The last time the state record was modified.

Sources: src/meltano_state_backend_postgresql/backend.py:228-237, tests/test_state.py:25-45

Identifier Resolution and Scoping

The system resolves the target location for state data using a hierarchy of configuration options. The resulting SQL identifier can be either unqualified (relying on the connection's search_path) or fully qualified (specifying both schema and table).

Hierarchy of Resolution

1. Explicit Table/Schema Settings: Settings provided via state_backend.postgresql.table and state_backend.postgresql.schema.
2. Connection URI Options: The search_path defined in the options query parameter of the PostgreSQL URI.
3. Defaults: The default table name state and the server's default search_path.

The following diagram illustrates how the PostgreSQLStateStoreManager determines the final table_identifier used in SQL queries:

Configuration Parameters

Schema management is controlled through specific Meltano settings. These settings can be defined in meltano.yml or via environment variables.

Setting Name	Default Value	Description
state_backend.postgresql.schema	None	The PostgreSQL schema where state tables will be created.
state_backend.postgresql.table	state	The table name used for state storage.
state_backend.uri	-	Standard PostgreSQL connection URI.

Sources: README.md:41-80, src/meltano_state_backend_postgresql/backend.py:82-97, pyproject.toml:29-37

Automatic Provisioning

The backend implements "lazy" provisioning. Upon the initialization of PostgreSQLStateStoreManager, the _ensure_tables method is called. This method executes a CREATE TABLE IF NOT EXISTS statement using the resolved table identifier.

# From src/meltano_state_backend_postgresql/backend.py:228-239
def _ensure_tables(self) -> None:
    """Ensure the state table exists."""
    with self.connection.cursor() as cursor:
        query = SQL(
            """\
            CREATE TABLE IF NOT EXISTS {table_identifier} (
                state_id VARCHAR(900) PRIMARY KEY,
                partial_state TEXT,
                completed_state TEXT,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """
        ).format(table_identifier=self.table_identifier)
        cursor.execute(query)

This ensures that the application is self-healing; if the table is missing in the target schema, it is created automatically before any GET or SET operations occur.

Sources: src/meltano_state_backend_postgresql/backend.py:214, src/meltano_state_backend_postgresql/backend.py:228-239

Backward Compatibility with System DB

The project is designed to be compatible with the existing Meltano system database schema. When Meltano's database_uri is set and migrations are run, the extension can interact with the same table structure used by the internal Meltano state management.

The extension validates compatibility by ensuring that state set via the internal systemdb manager can be retrieved by the PostgreSQLStateStoreManager when pointed at the same schema.

Sources: tests/test_backwards_compat.py:61-95, tests/test_backwards_compat.py:100-115

Conclusion

Schema management in meltano-state-backend-postgresql provides a flexible yet robust mechanism for state persistence. By automating table creation and supporting granular control over schemas and table names, it allows developers to isolate state data according to their organizational requirements while maintaining compatibility with standard Meltano workflows.

Sources: README.md:110-120, src/meltano_state_backend_postgresql/backend.py:170-180