JEFDAQ SQL-compatible Parquet export plan for #1462

sql-export-plan.md

SQL-Compatible Parquet Export Plan

Goal

Provide GIS and ETL consumers a read-only SQL interface to complete JEFDAQ tabular readings, including the 15-minute observations currently lost by hourly JSON scraping. The service must query materialized cache assets, not JEFDAQ's private ingest database.

Decision

Create a dedicated SqlExportRunner in the product layer. It opens an in-memory DuckDB catalog against the cache directory and exposes only curated, read-only views over tabular Parquet assets.

Do not add arbitrary SQL execution to the existing public Falcon application. That application has no authentication boundary and permissive CORS. DuckDB is an embedded engine, so Falcon POST /sql would be SQL-over-HTTP rather than a standard database endpoint.

Expose the catalog through a standards-compatible network protocol selected with the GIS team:

1. Preferred: PostgreSQL wire protocol, so ArcGIS, ODBC/JDBC, and ETL clients can use normal database connectors.
2. Alternative: Arrow Flight SQL, if the client supports it and columnar transfer is desirable.

The protocol server should be a small, separately configured adapter around the product catalog, bound internally by default and protected with TLS plus credentials or network allowlisting when remotely reachable.

Data Boundary and Contract

The cache is the correct source in the JENGA flow: ingest -> data -> cache -> product. CachePathWriter publishes Tabular DataFrames atomically as:

<cache>/tabular/<entity path>.parquet

DuckDB 1.5.2 can mount those assets directly through parquet_scan / read_parquet; no copy into a second SQL database is needed.

The initial catalog should export named views rather than physical tables. A generic view might be:

CREATE VIEW tabular_readings AS
SELECT
  regexp_replace(filename, '^.*/tabular/(.*)\\.parquet$', '\\1') AS entity_id,
  * EXCLUDE (filename)
FROM parquet_scan(
  '<cache>/tabular/**/*.parquet',
  filename = true,
  union_by_name = true
);

For the Flagstaff use case, add a specifically documented rain_gauge_readings view that selects only the approved rain-gauge entities and stable public columns. The precise entity allowlist and field mapping must be agreed with the GIS team. Document:

• entity_id: JEFDAQ entity path derived from the asset filename.
• ts: UTC TIMESTAMPTZ source observation timestamp.
• Measure columns such as precip, including unit and cumulative-vs-interval semantics.
• Data-quality policy: initial behavior should match the public tabular endpoint's good/known-measure filtering, implemented in the view or in a purpose-built export asset.
• Refresh semantics: data become queryable after the materializer atomically replaces the Parquet asset.

Example consumer query:

SELECT entity_id, ts, precip
FROM rain_gauge_readings
WHERE ts >= TIMESTAMPTZ '2026-08-26 00:00:00Z'
ORDER BY entity_id, ts;

Delivery Steps

1. Confirm consumer requirements: client protocol/driver, expected rain-gauge entity IDs, column names, units, quality semantics, retention, credential ownership, network route, and required update latency.
2. Add jefdaq.core.product.sql with a catalog builder accepting cache: Path. It creates only named views over cache/tabular/**/*.parquet; it must not access data/**/duckdb.
3. Define a data-driven allowlist and explicit projection for rain_gauge_readings, avoiding schema drift from arbitrary tabular assets.
4. Add SqlExportRunner and a dedicated binding/configuration section. Keep it independent from the public Falcon runner so its authentication and lifecycle are isolated.
5. Integrate a PostgreSQL-wire or Flight SQL adapter after validating the selected client. Enforce a read-only role and allow only SELECT / metadata operations. Reject DDL, DML, COPY, extension loading, filesystem access, and arbitrary DuckDB configuration changes.
6. Configure transport and access controls: loopback/private binding by default; TLS; service credentials stored in the existing deployment secret mechanism; source IP allowlisting where practical; connection/query timeouts and row/response limits.
7. Add operational metrics and structured logs for connection count, query duration, row counts, denied statements, and asset-refresh failures. Do not log SQL literals or credentials.
8. Publish a short integration guide with connection parameters, supported views, schema, sample queries, update cadence, and a migration note replacing the hourly JSON job.

Tests

• Catalog tests using the existing cache writer and tabular fixtures: multiple entities, nested entity paths, empty cache, mixed asset schemas, and atomic replacement.
• Contract tests for rain_gauge_readings: exact column set, types, UTC timestamps, known data-quality behavior, and no leakage from unapproved entities.
• Authorization tests: read-only accounts can query approved views; statements that write, change configuration, attach databases, read arbitrary files, or load extensions are denied.
• Network integration test against the chosen wire protocol using the intended GIS/ETL driver where possible.
• Concurrency test: queries remain successful while materialization replaces a Parquet file.

Rollout

1. Deploy internally with a single test client and compare results against the existing JSON feed over an agreed historical interval.
2. Give GIS a least-privilege read-only account and validate its existing scheduled job against the new view.
3. Run both feeds in parallel for one reporting cycle, monitoring row counts and latest timestamp.
4. Switch the GIS job to direct SQL access; retain the JSON endpoint as an independent public compatibility surface.

Open Decisions

• Which client protocol is confirmed by the GIS platform: PostgreSQL wire protocol or Flight SQL?
• Which entity IDs and field/unit names form the supported rain-gauge contract?
• Should the export include only good readings, or expose a documented quality column for GIS filtering?
• What retention, maximum query window, service account, TLS certificate, and network allowlist are required?