Store the history. Query it when you need it.

Storage persists; compute runs when needed

Parquet retains records independently of a running query engine. DuckDB is an embedded SQL engine that can read those files. A retrieval service can start work for a request rather than maintaining a second continuously indexed copy of all history.

On-demand compute still has costs: file listing, network requests, bytes read, CPU, memory and result storage. A gateway can itself remain running. Frequent concurrent queries or strict latency requirements may justify a dedicated serving system. The architecture should follow the access pattern, not a promise of universally cheap queries.

Find the archive before scanning it

Maintain dataset identifiers, schema versions, partition ranges, completed manifests and file locations. A catalog resolves “audit events from June” into an eligible file set and distinguishes complete exports from failed attempts. Version its relationship to stored files so retries do not accidentally expose duplicate batches.

Partition pruning and row-group statistics address different work. Choosing a month’s files can reduce the file set; statistics may then skip row groups inside those files. A filter on a field with scattered values might still scan much of the archive.

• Choose a Parquet layout for retrieval

Ask a focused SQL question

The example below is standard DuckDB SQL over existing local Parquet files, not a ColdCTL gateway API request; the browser uses validated filters. It selects specific columns and a time range. Define timestamp semantics in the export schema so the filter means the same thing to the writer and the reader.

DuckDB supports projection and filter pushdown when reading Parquet. Remote access depends on the relevant extension and storage credentials. S3-compatible services require suitable endpoint and authentication settings; an object-storage URI alone does not configure access.

SELECT id, created_at, event_type
FROM read_parquet('archive/audit_events/2024-06/*.parquet')
WHERE created_at >= TIMESTAMP '2024-06-01 00:00:00'
  AND created_at < TIMESTAMP '2024-06-02 00:00:00'
  AND event_type = 'login'
LIMIT 100;

The gateway needs a retrieval contract

Expose authorized datasets and parameterized query shapes rather than unrestricted SQL supplied by every caller. Set time, memory, concurrency and result limits. A SQL LIMIT restricts returned rows, not necessarily the work required to locate them. Track requested files, completion state and partial failures.

Protect both the source archive and any result exports. Apply tenant access boundaries before returning records; column projection is not field-level authorization. Keep credentials out of query text and logs. If a request fails, callers need to know whether no result was produced or whether only part of a requested export completed.

• Review the self-hosted access boundary

Querying and restoring are different operations

SQL over an archive can answer a historical question without repopulating production tables. Restoring records to a database additionally needs type fidelity, schema compatibility and a policy for conflicting identifiers. Test both paths if retention requirements demand them.

ColdCTL’s current agent can verify local archives and restore to a separate same-engine target; it does not reconstruct full application DDL. The separate gateway reads customer-controlled local, S3 and Azure archives without restoring production. Review its staging budgets, first-page preview limit and export row cap before deployment.

• Read the implemented archival lifecycle