Backend Contract

This document defines the storage-backend contract for polars-hist-db. polars-hist-db exposes one backend contract and two shipping adapters: mariadb (default, complete) and xtdb (implemented and configurable). The public surface stays a typed Polars-first temporal table contract regardless of which adapter is active.

Goal

The backend layer must support repeatable ingestion of raw and derived data, typed dataframe reads and writes, and point-in-time views of historical state. It must also provide enough update visibility for downstream services to invalidate caches, publish events, and build user-facing projections.

The goal for both adapters is the same: equivalent or better correctness, performance, and operability for the typed temporal-table contract.

Public Behaviour

Backend implementations must preserve these behaviours:

  • Read a whole table into a strongly typed polars.DataFrame.
  • Read query results into a strongly typed polars.DataFrame.
  • Write dataframes into configured tables using the configured schema.
  • Reject implicit type conversion at the database boundary. A caller may opt into a strict conversion for one operation with force_type_coercion=True.
  • Create configured tables idempotently.
  • Add missing configured columns without silently dropping existing data.
  • Apply default values in the same places as the current dataframe insert path.
  • Upsert temporal data using configured primary keys and delta semantics.
  • Delete rows temporally when the source marks rows as final or removed.
  • Query latest state, all historical states, an as-of state, and a bounded historical span through TimeHint.
  • Track ingestion audit entries so repeated scrapes skip already-ingested source items.
  • Expose table-update signals that downstream services can poll or publish.
  • Preserve transaction boundaries for each file or message partition.

Current MariaDB Coupling

The existing implementation mixes the public behaviour above with several MariaDB-specific details:

  • Temporal history is implemented with hidden __valid_from and __valid_to columns created as a PERIOD FOR SYSTEM_TIME.
  • Historical reads are expressed with MariaDB FOR SYSTEM_TIME hints.
  • Upsert timestamps are controlled with SET @@timestamp through DbOps.set_system_versioning_time.
  • Table metadata is loaded with SQLAlchemy reflection and MariaDB SQL types.
  • Delta upserts are implemented with temporary SQL tables, SQLAlchemy UPDATE ... WHERE, and INSERT ... SELECT statements.
  • Some current query surfaces accept SQLAlchemy Selectable objects, so they implicitly assume a SQLAlchemy-compatible backend.
  • Audit tables live in the same relational database as the target tables.

These details are valid for the MariaDB adapter, but they should not leak into the storage-neutral contract.

Backend Interface Shape

A backend adapter should own the operations below. Naming can change during implementation, but the boundary should remain explicit.

Backends are selected through the parsed db.backend configuration field. mariadb is the default and complete backend. xtdb is implemented for database-agnostic parity and advanced temporal workloads. mssql is validated by name but not implemented in this codebase yet.

Connection and Transactions

  • Build and dispose backend connections from DbEngineConfig.
  • Provide a transaction context compatible with dataset ingestion.
  • Allow backend-specific tuning without changing dataset configuration shape.

Schema Management

  • Create schemas or namespaces.
  • Create configured tables from TableConfig.
  • Reflect an existing table into TableConfig.
  • Add missing configured columns.
  • Report primary keys, nullable columns, default values, and backend-native type information.

Type Mapping

  • Map configured SQL-like types to Polars dtypes.
  • Map Polars dtypes to backend-native storage types.
  • Round-trip dates, datetimes, decimals, booleans, categoricals, integers, floats, strings, and nulls without lossy conversion.
  • Preserve decimal precision needed by finance and commodity-volume data.
  • Treat TableConfig as authoritative. Unknown types and heterogeneous physical scalar unions fail closed rather than falling back to text.
  • Allow null-only columns to adopt their configured type without treating that as a value conversion.

Reads

  • Read table latest state.
  • Read table history with TimeHint(mode="all").
  • Read an as-of state with TimeHint(mode="asof").
  • Read a bounded history with TimeHint(mode="span").
  • Read filtered/query results without forcing callers to hand-write backend SQL where a dataframe-key lookup would be sufficient.

Writes

  • Insert dataframes.
  • Update dataframes by primary key.
  • Upsert temporal dataframes by primary key.
  • Delete rows temporally.
  • Return changed-row counts where they are meaningful.
  • Keep one source partition or message inside one atomic commit unit.

Delta and Finality Semantics

The adapter must support the existing DeltaConfig behaviours:

  • Drop unchanged rows when configured.
  • Handle duplicate source keys with error, take_first, or take_last.
  • Apply row_finality="disabled" without deleting missing rows.
  • Apply row_finality="dropout" by temporally deleting rows missing from the incoming source snapshot.
  • Leave room for row_finality="manual" behaviour without blocking future implementation.

Audit and Update Visibility

  • Record source-item audit entries.
  • Filter already-ingested source items.
  • Query latest audit entry by table and source timestamp.
  • Support table-update callbacks used by API services for cache invalidation and event publication.

XTDB Adapter Capabilities

Official XTDB materials describe XTDB as an open-source immutable SQL database with comprehensive time-travel, bitemporal records, a columnar engine built on Apache Arrow, and object-storage-oriented architecture:

Those properties are directly relevant because the data platform is already Polars/Arrow-oriented and needs historical views. XTDB also enforces a strongly ordered write path, so batching and workload shape matter for throughput.

The current XtdbBackend exposes:

  • dataframes(connection) and adbc_dataframes(connection) for reads and dataframe writes.
  • table_configs(connection) for schema reflection and lifecycle operations.
  • temporal_upsert(...) for temporal writes.
  • staging(...) hooks for staged ingest.
  • Audit and override stores via the standard override integration points.

The first temporal parity slice shares backend-level system-time hint clause generation between MariaDB and XTDB for none, all, asof, and span. MariaDB still applies those hints through its existing SQLAlchemy/MariaDB TimeHint.apply path. XTDB applies the same clause string to table reads so both adapters honor the same historical-query contract.

XtdbBackend.table_configs(connection) exists as the schema-management seam. Table reflection reads information_schema.columns, maps XTDB type names into TableConfig, filters XTDB system-time columns, and treats _id as the reflected key. Configured table creation declares XTDB’s required _id plus the configured columns with XTDB’s bare-column CREATE TABLE schema.table (...) form. For a table config with exactly one primary key, that key is mapped to XTDB’s required _id document identifier. For composite primary keys, the adapter generates a deterministic text _id using the configured key order and the xtdb-pk-v1: encoding while preserving the original key columns as normal data columns. Table creation records the original primary-key list in an internal XTDB adapter metadata table so from_table(...) can recover composite keys when reflecting tables created by polars-hist-db. Legacy XTDB tables without that metadata still fall back to _id as the reflected key.

The same mapping is applied on experimental dataframe appends when callers pass the TableConfig to XtdbDataframeOps.table_insert(...): a configured key such as id is renamed to _id before Polars writes to XTDB, while composite-key tables receive a synthetic _id column and keep their original key columns. Existing MariaDB insert behaviour remains unchanged.

XTDB remains dynamically typed internally, so the adapter enforces the stable contract at its boundary. Configured writes require matching Polars dtypes by default, use native typed parameters or Arrow columns, and never convert bad values to null. Reflection compares XTDB’s inferred physical type family with the configured type and rejects heterogeneous scalar unions. Explicit forced conversion uses Polars strict casts and emits a warning, metric, and trace event; rejected contracts emit an error, metric, and trace event.

The first live pgwire round trip passes against ghcr.io/xtdb/xtdb:nightly:

POLARS_HIST_DB_XTDB_LIVE=1 uv run --extra xtdb \
  python -m pytest tests/backends/test_xtdb_live.py -m integration -q

The live test exposed important pgwire compatibility details:

  • The SQLAlchemy PostgreSQL dialect needs small XTDB-specific startup adjustments: connect to the xtdb database, disable native hstore probing, and skip the SHOW standard_conforming_strings dialect probe.
  • XTDB separates query and DML transactions more strictly than PostgreSQL. The adapter leaves reads in normal read-only transactions and wraps DML in explicit BEGIN READ WRITE blocks.
  • Psycopg parameter OIDs are not sufficient for this DML path, so the adapter emits typed SQL literals for inserts. This proves create/append/read viability, and bulk ingestion uses XTDB ADBC as the Arrow-native path.

The first live ADBC/FlightSQL round trip also passes against the same nightly image:

POLARS_HIST_DB_XTDB_LIVE=1 uv run --extra xtdb \
  python -m pytest tests/backends/test_xtdb_adbc_live.py -m integration -q

The current adapter keeps this as an explicit sidecar path:

  • XtdbBackend.create_engine(...) remains the pgwire/SQLAlchemy path for existing query and control-plane compatibility.
  • XtdbBackend.create_adbc_connection(...) opens grpc://host:adbc_port, defaulting XTDB adbc_port to 9832.
  • XtdbBackend.adbc_dataframes(connection) ingests Polars dataframes through Arrow/ADBC and reads query results back through fetch_arrow_table().
  • XTDB FlightSQL accepts schema-targeted ingest through ADBC’s db_schema_name option, so the ADBC path can preserve configured table schemas.
  • Polars emits Arrow large_string columns. The current XTDB nightly rejects large_string on ingest, so the adapter normalises large string and dictionary-large-string columns to Arrow string at the XTDB ingest boundary. This preserves values while avoiding the pgwire literal path.

The first temporal upsert parity slice now uses XTDB’s native insert-as-upsert behaviour:

  • XtdbBackend.temporal_upsert(...) delegates to the configured dataframe insert path. XTDB creates a new temporal version when an existing _id changes.
  • A live ADBC test proves latest state and FOR SYSTEM_TIME AS OF both work: after two upserts for the same configured primary key, latest reads the new value and an as-of checkpoint between commits reads the previous value.
  • MariaDB’s synthetic system-versioning time (SET @@timestamp) maps to XTDB’s transaction-level BEGIN READ WRITE WITH (SYSTEM_TIME = ...) option on the SQL/pgwire path. This is intended for initial backfills and must be monotonic: XTDB rejects a transaction system-time earlier than the current database transaction time.
  • A live pgwire test proves update_time is stored as XTDB system-time. The test imports two future system-time versions and queries with explicit FOR VALID_TIME AS OF ... FOR SYSTEM_TIME AS OF ... bases to account for XTDB’s independent valid-time and system-time axes.
  • ADBC bulk ingest does not yet support update_time in this adapter. It still works for commit-time system history, but imported system-time currently uses pgwire SQL transactions.
  • The current delta parity slice supports DeltaConfig(drop_unchanged_rows=True), on_duplicate_key, and row_finality values disabled and dropout for single-key XTDB tables. The adapter applies the source duplicate policy before writing: the default error mode rejects duplicate source keys, while take_first and take_last preserve the selected source row. For dropout finality it temporally deletes current rows whose _id is missing from the incoming snapshot. It then compares incoming rows against the current row at the same temporal basis and skips rows whose configured values are unchanged. The comparison fetches only uploaded document IDs and requested columns, so its transfer and client-memory cost scale with the upload rather than the complete target table.
  • A live test proves the unchanged update returns zero changed rows and does not create a transaction at the unchanged update_time; the subsequent changed update remains visible through as-of valid-time/system-time queries. Full FOR VALID_TIME ALL FOR SYSTEM_TIME ALL history can show more rows than the count of submitted changes because XTDB exposes the full bitemporal matrix. Tests should assert transaction/system-time and as-of state invariants, not assume one displayed row per logical source update.
  • A live test proves dropout finality removes a missing row from later valid-time/system-time as-of reads while preserving the earlier as-of snapshot.
  • Explicit XTDB valid-time columns are supported on the pgwire insert path: _valid_from / _valid_to pass through dataframe upserts, and unchanged-row filtering treats source valid-window changes as meaningful changes. A live test proves a row with an explicit valid-time window is only visible inside that window.
  • Dataset configs can declare per-table valid-time mappings: valid_time: [{table: records, from_column: msg_timestamp}]. The XTDB temporal upsert path copies the configured from_column to _valid_from and optional to_column to _valid_to, preserving the original source columns. This keeps feed semantics YAML-controlled: record snapshots can use message/as-of time, entity vectors can use source position time, and future rate feeds can use explicit validity windows.
  • Dataset ingestion keeps each XTDB partition in a process-local Polars dataframe until the pipeline finishes, then discards it. It does not persist a staging table in XTDB.
  • XTDB staging preserves pipeline source-to-target column mapping, duplicate removal, default filling, update_time system-time import, per-table valid-time mapping, and deduce_foreign_key normalization. Parent lookups fetch only uploaded natural keys. Missing textual rows get deterministic IDs of the form xtdb-fk-v1:<schema>.<table>:<natural-key-json>. Missing numeric rows use deterministic negative IDs, probe existing IDs, and resolve any collisions before insert.
  • MariaDB continues to treat __valid_from / __valid_to as system-version history. Dataset valid-time mappings are XTDB business valid-time inputs and are not equivalent to MariaDB’s generated system-time columns.