Sapmapayı: Cold-Chain Compliance & Zero-Copy Reporting

1. Context

Sapmapayı is an enterprise compliance platform auditing cold-chain integrity across pharmaceutical and perishables storage facilities. It analyzes high-frequency temperature and humidity curves, flags threshold violations, and compiles officially enforceable e-signed audit PDFs.

2. Problem

The system encountered two architectural bottlenecks:

1. Browser Thread Lock on Large Time-Series: Inspecting a 30-day thermal history for a cold room yielded ~250,000 datapoints. Serializing this into conventional JSON produced a 25MB payload that froze browser JavaScript JSON.parse() threads for 800ms+.
2. Legal Enforceability: Proving that audit data had not been retroactively altered required cryptographic digital signatures (X.509) and certified RFC 3161 timestamps on all exported reports.

3. Architecture & Data Path

 [ TimescaleDB Hypertables ]
               │ Continuous Aggregate View
               ▼
 ┌────────────────────────────────────────────────────────┐
 │ .NET Backend: Arrow RecordBatch Streamer               │
 │ ┌───────────────────┐        ┌───────────────────────┐ │
 │ │ Npgsql Binary     │───────►│ Arrow RecordBatch     │ │
 │ │ Raw Buffer Reader │        │ IPC Streamer          │ │
 │ └───────────────────┘        └──────────┬────────────┘ │
 └─────────────────────────────────────────┼──────────────┘
                                           │ Zero-Copy IPC Stream (Binary)
                                           ▼
 ┌────────────────────────────────────────────────────────┐
 │ Frontend & Canvas / WebGL                              │
 │   Browser: Arrow Table.from() -> Float64Array (0ms)    │
 └────────────────────────────────────────────────────────┘
                                           │ Approved Report
                                           ▼
 ┌────────────────────────────────────────────────────────┐
 │ PDF Notarization Engine                                │
 │ ┌───────────────────┐        ┌───────────────────────┐ │
 │ │ QuestPDF Engine   │───────►│ PKCS#11 Worker Pool   │ │
 │ │ Raw PDF Stream    │        │ RFC 3161 Timestamping │ │
 │ └───────────────────┘        └───────────────────────┘ │
 └────────────────────────────────────────────────────────┘

4. Decisions & Rejected Alternatives

• Apache Arrow IPC chosen, JSON REST API rejected: String parsing overhead and duplicated JSON dictionary keys caused unacceptable latency. Arrow’s columnar memory layout enabled direct mapping from raw database bytes straight into browser Float64Array typed memory without intermediate serialization.
• Asynchronous HSM worker pool chosen, synchronous calls rejected: Hardware Security Modules (HSMs) operate synchronously with 300–700ms signing latency. Dedicated background worker queues decoupled report generation from interactive web threads.
• TimescaleDB Continuous Aggregates chosen, document stores rejected: Pre-computing hourly rollups inside partitioned PostgreSQL hypertables avoided repeated, resource-heavy disk scans on raw telemetry.

5. Production Code: Arrow RecordBatch Streaming

using Apache.Arrow;
using Apache.Arrow.Ipc;
using System.IO.Pipelines;

public async Task StreamTelemetryArrowAsync(
    long deviceId, 
    DateTimeOffset start, 
    DateTimeOffset end, 
    PipeWriter outputWriter,
    CancellationToken ct)
{
    var schema = new Schema.Builder()
        .Field(new Field("timestamp", new TimestampType(TimeUnit.Millisecond, "UTC"), false))
        .Field(new Field("temperature", new DoubleType(), false))
        .Field(new Field("humidity", new DoubleType(), false))
        .Build();

    using var arrowWriter = new ArrowStreamWriter(outputWriter.AsStream(), schema);

    await using var conn = await _dataSource.OpenConnectionAsync(ct);
    await using var cmd = conn.CreateCommand();
    cmd.CommandText = @"
        SELECT bucket AS timestamp, avg_temp, avg_hum 
        FROM chiller_hourly_summary 
        WHERE device_id = $1 AND bucket BETWEEN $2 AND $3 
        ORDER BY bucket ASC;";
    
    // Direct memory block transfer - zero JSON parsing allocations
    var batch = await ReadRecordBatchFromDbAsync(cmd, schema, ct);
    await arrowWriter.WriteRecordBatchAsync(batch, ct);
    await outputWriter.FlushAsync(ct);
}

6. Outcome & Measurable Results

• Network Payload: A 250,000-row telemetry dataset dropped from ~24.8MB (JSON) to ~3.1MB via Apache Arrow IPC (~87% reduction).
• Browser Thread Unblocking: UI freezes during JSON parsing (750–900ms) were reduced to <15ms using direct typed array buffers.
• Legal Compliance: Every generated PDF is cryptographically sealed with national root X.509 digital certificates and RFC 3161 timestamp tokens.
• Verified Scale: 500K+ daily processed telemetry records; <15ms browser IPC deserialization latency.

7. What I Would Do Differently

• Early prototypes used headless Chromium (Puppeteer) for HTML-to-PDF rendering, which consumed 400MB+ RAM per container and failed under concurrent bursts. Rewriting with a native C# layout engine (QuestPDF) dropped memory usage to 15MB and slashed render times by 10x.