Skip to content

powabase/supabase-fdw-ntp

BranchesTags

Repository files navigation

NTP Energy Market WASM FDW

WebAssembly Foreign Data Wrapper for PostgreSQL enabling SQL queries against German transmission system operator transparency data via the Netztransparenz.de (NTP) API.

Overview

This wrapper allows you to query German renewable energy production, electricity prices, grid redispatch, and grid status data using standard SQL:

SELECT DATE(timestamp_utc) as date,
       ROUND(AVG(tso_50hertz_mw + tso_amprion_mw +
                 tso_tennet_mw + tso_transnetbw_mw)::numeric, 2) as avg_solar_mw
FROM ntp.renewable_energy_timeseries
WHERE product_type = 'solar'
  AND data_category = 'extrapolation'
  AND timestamp_utc >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE(timestamp_utc);

A standalone WASM FDW that can be used with any Supabase project to access official German grid operator data for renewable energy analysis, price correlation studies, and grid operations monitoring.

Want to get started immediately? See QUICKSTART.md for a 5-minute setup guide.

Features

  • ✅ 11 Accessible Endpoints - 100% coverage of available German NTP API endpoints across 4 tables
  • ✅ OAuth2 Authentication - Secure access with 1-hour token caching using client credentials flow
  • ✅ CSV & JSON Parsing - German locale support (comma decimals, DD.MM.YYYY dates, "N.A." and "N.E." NULL indicators)
  • ✅ Consolidated Tables - Domain-driven design (4 tables covering 11 endpoints, not 1:1 API mapping)
  • ✅ JOIN Support - Full cross-table JOIN capability via re_scan() implementation
  • ✅ Performance - Sub-linear scaling: 2.1s for 365-day queries, 630ms for 7-day queries
  • ✅ 327 KB Optimized Binary - Fast download and secure WASM sandboxed execution
  • ✅ WHERE Clause Pushdown - Efficient API parameter translation (product_type, data_category, timestamp_utc)
  • ✅ WASM-Based - Works on hosted Supabase (no native PostgreSQL extensions needed)
  • ✅ Production Ready - 190 tests passing (100%), validated with real API data

Available Tables

Table Purpose Coverage
renewable_energy_timeseries Solar, wind onshore, wind offshore production (actuals, real-time) 5 endpoints
electricity_market_prices Spot market prices, market premiums, annual values, negative price flags 4 endpoints
redispatch_events Grid redispatch measures for congestion management 1 endpoint
grid_status_timeseries Real-time grid traffic light status (green/yellow/red) 1 endpoint

Quick Start

For Users: Just want to use the FDW? See QUICKSTART.md ⭐

For Developers: Building from source? See below.

Building from Source

Prerequisites:

  • Rust (stable 1.70+)
  • cargo-component 0.21.1
  • wasm32-unknown-unknown target
  • Supabase CLI (for local testing)
  • Docker Desktop (for local Supabase development)
  • PostgreSQL client (psql)

Installation:

# Install Rust toolchain
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh
rustup target add wasm32-unknown-unknown

# Install cargo-component
cargo install cargo-component --locked --version 0.21.1

Build:

git clone https://github.com/powabase/supabase-fdw-ntp.git
cd supabase-fdw-ntp
cargo component build --release --target wasm32-unknown-unknown
# Output: target/wasm32-unknown-unknown/release/supabase_fdw_ntp.wasm (327 KB)

Validate:

# Verify WASM structure
wasm-tools validate target/wasm32-unknown-unknown/release/supabase_fdw_ntp.wasm

# Ensure zero WASI CLI imports (CRITICAL for Supabase compatibility)
wasm-tools component wit target/wasm32-unknown-unknown/release/supabase_fdw_ntp.wasm | grep -i "wasi:cli"
# Expected: No output (zero matches)

# Check binary size
ls -lh target/wasm32-unknown-unknown/release/supabase_fdw_ntp.wasm

Deploy: See QUICKSTART.md for complete deployment instructions.

Security: Using Vault for OAuth2 Credentials (Recommended)

⚠️ Important: As of v0.3.1, this wrapper supports Supabase Vault for secure credential storage. Vault is strongly recommended for production deployments.

Why Vault?

Supabase Vault provides encrypted secret storage with several security benefits:

  • ✅ Encryption at rest using pgsodium
  • ✅ Secrets referenced by UUID only (no plain text in pg_catalog)
  • ✅ Audit trail and secret versioning
  • ✅ Industry-standard best practice for Supabase FDW wrappers

Step 1: Store Secrets in Vault

-- Create encrypted OAuth2 client ID in Vault
SELECT vault.create_secret(
  'your_actual_client_id_here',
  'ntp_oauth2_client_id',
  'NTP API OAuth2 client ID'
);
-- Returns: key_id (UUID) like '550e8400-e29b-41d4-a716-446655440000'

-- Create encrypted OAuth2 client secret in Vault
SELECT vault.create_secret(
  'your_actual_client_secret_here',
  'ntp_oauth2_client_secret',
  'NTP API OAuth2 client secret'
);
-- Returns: key_id (UUID) like '660f9511-f30c-52e5-b827-557766551111'

Step 2: Create Server with Vault References

CREATE SERVER ntp_server
  FOREIGN DATA WRAPPER wasm_wrapper
  OPTIONS (
    fdw_package_url 'https://github.com/powabase/supabase-fdw-ntp/releases/download/v0.3.1/supabase_fdw_ntp.wasm',
    fdw_package_name 'powabase:supabase-fdw-ntp',
    fdw_package_version '0.3.1',
    fdw_package_checksum '<sha256_checksum_here>',
    api_base_url 'https://api.netztransparenz.de/StatisticData/v2',
    oauth2_token_url 'https://api.netztransparenz.de/OAuth2/token',
    oauth2_client_id_vault '550e8400-e29b-41d4-a716-446655440000',      -- ✅ Vault UUID (secure)
    oauth2_client_secret_vault '660f9511-f30c-52e5-b827-557766551111',  -- ✅ Vault UUID (secure)
    oauth2_scope 'ntpStatistic.read_all_public'
  );

Secret Rotation

Rotate credentials without downtime:

-- 1. Create new secrets in Vault
SELECT vault.create_secret('<NEW_CLIENT_ID>', 'ntp_oauth2_client_id_v2', 'Rotated client ID');
SELECT vault.create_secret('<NEW_CLIENT_SECRET>', 'ntp_oauth2_client_secret_v2', 'Rotated client secret');

-- 2. Update server with new Vault UUIDs
ALTER SERVER ntp_server OPTIONS (SET oauth2_client_id_vault '<new_client_id_vault_uuid>');
ALTER SERVER ntp_server OPTIONS (SET oauth2_client_secret_vault '<new_client_secret_vault_uuid>');

-- 3. Verify it works, then delete old secrets
-- DELETE FROM vault.secrets WHERE id = '<old_vault_uuid>';

Legacy: Plain Text Credentials (Deprecated)

⚠️ Not recommended for production use

Plain text credentials remain supported for backward compatibility but will trigger deprecation warnings:

CREATE SERVER ntp_server_legacy
  FOREIGN DATA WRAPPER wasm_wrapper
  OPTIONS (
    -- ... other options ...
    oauth2_client_id 'plain_text_client_id',          -- ❌ Insecure (visible in pg_catalog)
    oauth2_client_secret 'plain_text_client_secret'   -- ❌ Insecure (visible in pg_catalog)
  );

Security Warnings:

  • Plain text credentials are visible in pg_catalog.pg_foreign_server
  • Stored unencrypted in database dumps
  • No audit trail for access or rotation
  • May be accidentally committed to version control

Migration Path: Replace oauth2_client_id / oauth2_client_secret with oauth2_client_id_vault / oauth2_client_secret_vault using Vault UUIDs.

Troubleshooting

Issue: "Failed to retrieve OAuth2 client ID from Vault"

  • Verify the UUID exists: SELECT id, name FROM vault.secrets WHERE id = '<uuid>';
  • Check permissions on vault.secrets table (RLS policies)
  • Ensure UUID format is valid (36 characters with hyphens)

Issue: Deprecation warning not showing

  • Check PostgreSQL log level: SHOW client_min_messages; (should be notice or lower)
  • Warnings appear in PostgreSQL logs, not query results

Usage Examples

Example 1: Solar Production (Last 7 Days)

Retrieve average daily solar production across Germany for the past week.

SELECT DATE(timestamp_utc) as date,
       COUNT(*) as intervals,
       ROUND(AVG(tso_50hertz_mw + tso_amprion_mw +
                 tso_tennet_mw + tso_transnetbw_mw)::numeric, 2) as avg_total_mw
FROM ntp.renewable_energy_timeseries
WHERE product_type = 'solar'
  AND data_category = 'extrapolation'
  AND timestamp_utc >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE(timestamp_utc)
ORDER BY date DESC;

Expected Output:

date intervals avg_total_mw
2024-10-24 96 8,431.25
2024-10-23 96 9,127.50
... ... ...

Performance: ~630ms for 672 rows

Insights: Uses 'extrapolation' category for historical actual data. 96 intervals per day = 15-minute granularity. Total MW shows combined production across all four German TSO zones.

Example 2: Negative Electricity Prices

Find periods with negative electricity prices indicating renewable overproduction.

SELECT timestamp_utc, price_eur_mwh
FROM ntp.electricity_market_prices
WHERE price_type = 'spot_market'
  AND price_eur_mwh < 0
  AND timestamp_utc >= '2024-10-01'
ORDER BY price_eur_mwh ASC
LIMIT 10;

Expected Output:

timestamp_utc price_eur_mwh
2024-10-13 12:00:00 -2.01
2024-10-13 13:00:00 -1.85
... ...

Performance: <1 second

Insights: Negative prices occur during high solar/wind production with low demand. These periods indicate excess renewable energy on the grid, creating economic opportunities for flexible loads (e.g., battery charging, hydrogen production).

Example 3: Multi-Product Comparison

Compare solar vs wind onshore production for a specific date range.

SELECT product_type,
       COUNT(*) as intervals,
       ROUND(AVG(tso_50hertz_mw + tso_amprion_mw +
                 tso_tennet_mw + tso_transnetbw_mw)::numeric, 2) as avg_mw
FROM ntp.renewable_energy_timeseries
WHERE product_type IN ('solar', 'wind_onshore')
  AND data_category = 'extrapolation'
  AND timestamp_utc >= '2024-10-20'
  AND timestamp_utc < '2024-10-24'
GROUP BY product_type;

Expected Output:

product_type intervals avg_mw
solar 384 8,779.42
wind_onshore 384 6,145.83

Performance: ~680ms total (2 API calls)

Insights: Demonstrates consolidated table design - one query for multiple products. Single table query eliminates complex JOINs while providing rich comparative analysis.

Example 4: Cross-Table JOIN (Prices vs Production)

Correlate electricity prices with solar production to analyze price-production relationship.

SELECT DATE(p.timestamp_utc) as date,
       COUNT(*) as hours_with_data,
       ROUND(AVG(p.price_eur_mwh)::numeric, 2) as avg_price_eur_mwh,
       ROUND(AVG(COALESCE(r.tso_50hertz_mw,0) + COALESCE(r.tso_amprion_mw,0) +
                  COALESCE(r.tso_tennet_mw,0) + COALESCE(r.tso_transnetbw_mw,0))::numeric, 2) as avg_solar_mw
FROM ntp.electricity_market_prices p
LEFT JOIN ntp.renewable_energy_timeseries r
  ON DATE_TRUNC('hour', p.timestamp_utc) = DATE_TRUNC('hour', r.timestamp_utc)
  AND r.product_type = 'solar'
  AND r.data_category = 'extrapolation'
WHERE p.price_type = 'spot_market'
  AND p.timestamp_utc >= '2024-10-20'
  AND p.timestamp_utc < '2024-10-24'
GROUP BY DATE(p.timestamp_utc)
ORDER BY date;

Expected Output:

date hours_with_data avg_price_eur_mwh avg_solar_mw
2024-10-20 24 68.45 7,231.50
2024-10-21 24 30.90 10,127.25
... ... ... ...

Performance: ~1.5 seconds

Insights: Enabled by re_scan() implementation in v0.2.0. Shows inverse correlation: high solar production = lower prices. Validates renewable economics: more clean energy reduces market prices.

Example 5: Wind Offshore with NULL Handling

Query wind offshore data with proper handling of 'N.E.' (Not Recorded) values.

SELECT product_type, data_category, COUNT(*) as row_count,
       ROUND(AVG(tso_50hertz_mw + tso_amprion_mw +
                 tso_tennet_mw + tso_transnetbw_mw)::numeric, 2) as avg_total_mw
FROM ntp.renewable_energy_timeseries
WHERE product_type = 'wind_offshore'
  AND data_category = 'online_actual'
  AND timestamp_utc >= '2024-10-23'
  AND timestamp_utc < '2024-10-24'
GROUP BY product_type, data_category;

Expected Output:

product_type data_category row_count avg_total_mw
wind_offshore online_actual 24 2,263.00

Performance: ~687ms

Insights: v0.2.0 fix handles both 'N.A.' (Not Available) and 'N.E.' (Nicht Erfasst = Not Recorded) NULL indicators. Average 2,263 MW offshore wind production is realistic for German offshore wind capacity.

Example 6: Grid Status Traffic Light

Monitor real-time grid congestion status across German TSO zones.

SELECT timestamp_utc,
       tso_50hertz_status,
       tso_amprion_status,
       tso_tennet_status,
       tso_transnetbw_status
FROM ntp.grid_status
WHERE timestamp_utc >= CURRENT_TIMESTAMP - INTERVAL '1 hour'
ORDER BY timestamp_utc DESC
LIMIT 5;

Expected Output:

timestamp_utc tso_50hertz_status tso_amprion_status tso_tennet_status tso_transnetbw_status
2024-10-25 14:30:00 Grün Grün Gelb Grün
2024-10-25 14:15:00 Grün Grün Grün Grün

Performance: <1 second

Insights: First JSON endpoint (TrafficLight API). Real-time grid congestion monitoring. Green = normal, Yellow = caution, Red = critical. Useful for demand response and grid stability analysis.

Example 7: TSO Zone Volatility Analysis

Analyze which German TSO zone has the most volatile solar production.

SELECT
  'tso_50hertz' as tso_zone,
  STDDEV(tso_50hertz_mw) as volatility_mw,
  AVG(tso_50hertz_mw) as avg_mw
FROM ntp.renewable_energy_timeseries
WHERE product_type = 'solar'
  AND data_category = 'extrapolation'
  AND timestamp_utc >= CURRENT_DATE - INTERVAL '7 days'
  AND tso_50hertz_mw IS NOT NULL
UNION ALL
SELECT 'tso_amprion', STDDEV(tso_amprion_mw), AVG(tso_amprion_mw)
FROM ntp.renewable_energy_timeseries
WHERE product_type = 'solar' AND data_category = 'extrapolation'
  AND timestamp_utc >= CURRENT_DATE - INTERVAL '7 days'
  AND tso_amprion_mw IS NOT NULL
UNION ALL
SELECT 'tso_tennet', STDDEV(tso_tennet_mw), AVG(tso_tennet_mw)
FROM ntp.renewable_energy_timeseries
WHERE product_type = 'solar' AND data_category = 'extrapolation'
  AND timestamp_utc >= CURRENT_DATE - INTERVAL '7 days'
  AND tso_tennet_mw IS NOT NULL
UNION ALL
SELECT 'tso_transnetbw', STDDEV(tso_transnetbw_mw), AVG(tso_transnetbw_mw)
FROM ntp.renewable_energy_timeseries
WHERE product_type = 'solar' AND data_category = 'extrapolation'
  AND timestamp_utc >= CURRENT_DATE - INTERVAL '7 days'
  AND tso_transnetbw_mw IS NOT NULL
ORDER BY volatility_mw DESC;

Performance: <2 seconds

Insights: Wide table design enables per-TSO analysis without complex JOINs. Identifies which grid zones have most variable renewable production, useful for grid planning and storage deployment.

Architecture

┌─────────────────────────────────────────────────────────┐
│                    SQL Query                             │
│  SELECT * FROM ntp.renewable_energy_timeseries          │
│  WHERE product_type = 'solar'                           │
│    AND timestamp_utc >= '2024-10-01'                    │
└──────────────────────┬──────────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────────┐
│              PostgreSQL / Supabase                       │
│         (Identifies foreign table)                       │
└──────────────────────┬──────────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────────┐
│            WASM FDW Wrapper (This Project)               │
│  1. Extracts WHERE clause: product_type, dates         │
│  2. Routes to API: /prognose/Solar/2024-10-01/...      │
│  3. Authenticates: OAuth2 token (1-hour cache)         │
│  4. Executes HTTP GET to NTP API                        │
│  5. Parses CSV: German locale transformations           │
│      - Comma → decimal point                            │
│      - DD.MM.YYYY → TIMESTAMPTZ                         │
│      - "N.A.", "N.E." → SQL NULL                        │
│      - Semicolon-delimited parsing                      │
│  6. Flattens TSO zones: 4 columns (wide table)         │
│  7. Maps to PostgreSQL cells with bounds checking       │
└──────────────────────┬──────────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────────┐
│         German NTP Transparency API                      │
│  GET /api/v1/data/prognose/Solar/2024-10-01/...        │
│  Authorization: Bearer <OAuth2 token>                   │
│  Returns: CSV (semicolon-delimited, German locale)      │
└─────────────────────────────────────────────────────────┘

Key Design Decisions:

  • Consolidated Tables: 15 API endpoints → 4 tables (not 1:1 mapping)
  • English Schema, German Data: Column names in English (timestamp_utc), data values in German ("Strombedingter Redispatch")
  • Wide Tables: 4 TSO zones as columns (not normalized separate table)
  • OAuth2 Caching: 1-hour token lifetime with hybrid proactive + reactive refresh
  • NULL for Missing: SQL NULL (not zero) for unavailable data to preserve semantic distinction

Why WASM?

Hosted Supabase instances cannot install native PostgreSQL extensions. WASM FDW enables custom foreign data wrappers through:

  1. Dynamic loading from URL - Load from GitHub releases or file:// paths, no database restart
  2. Sandboxed execution - Security through WebAssembly isolation prevents malicious code
  3. No database restart required - Hot-load new FDW wrappers without downtime
  4. Near-native performance - Compiled WASM executes efficiently (~50-100ms overhead)
  5. Cross-platform compatibility - Same binary works on all PostgreSQL hosting environments

Documentation

Getting Started:

Reference:

Development:

Performance

Query Type Rows API Calls Time Scaling
1-day 96 1 0.54s Baseline
7-day 672 1 0.63s 7x data, 1.2x time
30-day 2,880 1 0.86s 30x data, 1.6x time
365-day 35,040 1 2.10s 365x data, 3.9x time
Simple JOIN 96 2 1.50s Cross-table correlation
Wind offshore 24 1 0.69s Hourly granularity
Spot prices 24 1 0.52s Hourly pricing data

Key Metrics:

  • Sub-linear scaling: 30x more data takes only 1.6x longer (excellent performance)
  • OAuth2 caching: 1-hour token lifetime, 100% success rate (8/8 calls in tests)
  • API latency: 200-700ms per API call
  • WASM overhead: ~50-100ms (parsing and row conversion)
  • Binary size: 301 KB (v0.2.0, target <150 KB for future optimization)

Known Limitations

1. Generated Columns Not Auto-Calculated

Issue: total_germany_mw and has_missing_data columns are defined in DDL but not computed by FDW.

Workaround: Manually compute in SELECT queries:

-- Instead of: SELECT total_germany_mw
-- Use:
SELECT (COALESCE(tso_50hertz_mw, 0) + COALESCE(tso_amprion_mw, 0) +
        COALESCE(tso_tennet_mw, 0) + COALESCE(tso_transnetbw_mw, 0)) as total_mw
FROM ntp.renewable_energy_timeseries;

Details: PostgreSQL FDW limitation - generated columns defined in DDL but not computed by FDW. Must calculate in query.

2. Use 2024 Dates for Testing

Issue: API hasn't published 2025 data yet (as of October 2025).

Workaround: Query historical data with explicit 2024 date ranges:

WHERE timestamp_utc >= '2024-10-01'
  AND timestamp_utc < '2024-11-01'

Details: Use data_category = 'extrapolation' for past data, 'forecast' for future predictions. Future dates beyond API data will return empty results or 404 errors.

3. Default Date Range Hardcoded

Issue: Fallback date range hardcoded to 2024-10-18 to 2024-10-25 due to WASM limitations (no SystemTime::now()).

Workaround: Always specify explicit timestamp_utc filters in WHERE clause:

WHERE timestamp_utc >= '2024-10-20'
  AND timestamp_utc < '2024-10-25'

Details: Queries without date filters use hardcoded default. Avoid relying on this - always provide explicit date ranges.

4. WASM Binary Size Above Target

Issue: Current binary is 301 KB, target is <150 KB (100% over target).

Workaround: No action needed for v0.2.0. Works correctly despite size. Optimization deferred to future release.

Details: Heavy dependencies (Chrono for timestamps, Serde_JSON for parsing) cause size increase. Functionality unaffected.

Use Cases

  • Renewable Energy Forecasting - Compare forecast vs actual production for accuracy analysis
  • Price Correlation Analysis - Understand how renewable production affects electricity prices
  • Grid Stability Monitoring - Track grid congestion with traffic light status (v0.2.0)
  • Renewable Overproduction Detection - Identify negative price periods caused by excess solar/wind
  • Market Premium Calculations - Analyze renewable energy market values and premiums
  • Regional Production Analysis - Compare renewable production across German TSO zones (50Hertz, Amprion, TenneT, TransnetBW)
  • Energy Trading Decisions - Historical price and production data for trading algorithms
  • Climate Impact Assessment - Track renewable energy penetration over time
  • Real-time Grid Operations - Monitor current renewable production and prices (via online_actual category)
  • Redispatch Cost Analysis - Track grid congestion management costs and volumes

Testing Notes

Critical: Use 2024 dates for testing (2025 data not yet published by API):

-- Historical actuals (extrapolation)
WHERE data_category = 'extrapolation'
  AND timestamp_utc >= '2024-10-01'
  AND timestamp_utc < '2024-10-31'

-- Real-time data (online_actual)
WHERE data_category = 'online_actual'
  AND timestamp_utc >= NOW() - INTERVAL '24 hours'

Test Coverage:

  • 190 tests passing (100% success rate)
  • Validated with real NTP API data
  • All security fixes applied and validated
  • Comprehensive E2E test suite (35 tests)

Local Testing: See E2E_TESTING_GUIDE.md for complete local testing setup with Supabase CLI.

Contributing

Contributions are welcome! Please:

  1. Read CLAUDE.md for development guidelines
  2. Test locally with Supabase CLI before creating PR
  3. Update endpoint documentation for schema changes
  4. Ensure WASM binary validates (zero WASI CLI imports)
  5. Run test suite (cargo test) before submitting
  6. Follow existing patterns for OAuth2, CSV parsing, and error handling
  7. Document performance impact of changes

License

Apache-2.0

Links

Version: v0.3.0 Last Updated: 2025-10-26 Status: Production Ready (190 tests passing, 11/11 accessible endpoints) Built with Rust, WebAssembly, and Supabase • Powered by German NTP Transparency API

About

WebAssembly Foreign Data Wrapper for PostgreSQL enabling SQL queries against German transmission system operator transparency data via the Netztransparenz.de (NTP) API.

Resources

Readme

License

View license
Activity
Custom properties

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases 7

v0.3.1 Latest
Oct 29, 2025
+ 6 releases

Packages

 
 
 

Contributors

Languages