Orchestrating Self-Contained Applications for Decentralized Data Hubs
Build portable, low-latency data applications that work offline. Transform raw data into fully deployable edge databases with embedded UIs.
What is an Edge Database?
An Edge Database is a database designed to operate at the network "edge"—meaning close to the user, the source of data generation (like a research lab, IoT device, or a local machine). Unlike traditional cloud databases, the primary focus is on low-latency access and offline capability. They prioritize portability and execution speed over centralized control.
Traditional Cloud Databases
- • High latency due to necessary network round trips
- • Requires persistent, reliable internet connection
- • Scales by provisioning larger remote data centers (vertical scaling)
Edge Databases
- • Extremely low latency (data is local to the application)
- • Works entirely offline or with intermittent connectivity
- • Scales by distributing self-contained, identical copies
- • Uses portable formats like SQLite
Case Study: Diabetes Research Hub (DRH) Edge Data Pipeline
In the Diabetes Research Hub (DRH) project, the final artifact, a single resource-surveillance.sqlite.db file, is the Edge Database. It contains both the processed research data and the entire web application UI (powered by SQLPage) necessary to view it, making it a fully portable, "database-as-an-app" solution suitable for clinic and research settings.
The Spry framework orchestrates the complete ETL (Extract, Transform, Load) and deployment pipeline, defined in the drh-simplera-spry.md manifest. It manages the execution of specialized tools to convert raw study files (CGM, meal, fitness data) into the final Edge Database.
Core Tools and Technologies
Spry
Task Orchestration & Dependency Management, the 'glue' of the pipeline.
surveilr
Initial data ingestion, format conversion (CSV to RSSD), and pre-processing.
DuckDB
In-memory analytical processing, ETL, and complex data transformation (e.g., calculating GRI).
SQLPage
Web UI generation directly from SQL queries stored in the SQLite file.
SQLite
The final, single-file, portable Edge Database format, containing both data and UI.
The Multi-task Pipeline Breakdown
Task: prepare-env
Role : Initializes the execution environment by checking for mandatory tools and ensuring all required environment variables are sourced (like SPRY_DB, STUDY_DATA_PATH, TENANT_ID).
```bash prepare-env -C ./.envrc --gitignore --descr "Generate .envrc file and add it to local .gitignore"
export SPRY_DB="sqlite://resource-surveillance.sqlite.db?mode=rwc"
export PORT=9227
export STUDY_DATA_PATH="raw-data/simplera-synthetic-cgm/"
export TENANT_ID="FLCG"
export TENANT_NAME="Florida Clinical Group"
direnv allow
```Task: prepare-db (ETL)
Role: The main ETL phase. It uses surveilr for raw data ingestion and conversion to RSSD, followed by DuckDB for complex analytical transformations. For DRH, this involves merging CGM, meal, and fitness data, and calculating derived clinical metrics (e.g., Time In Range, Glycemic Risk Index).
```bash prepare-db --dep prepare-env --descr "Performs pre-etl-validation, Extract data, Perform transformations"
#!/bin/bash
STUDY_DATA_PATH="${STUDY_DATA_PATH}"
TENANT_ID="${TENANT_ID}"
TENANT_NAME="${TENANT_NAME}"
TOOL_CMD="surveilr"
rm -f resource-surveillance.sqlite.db
rm -f *.sql
rm -rf dev-src.auto validation-reports
echo "Starting the pipeline......."
echo "Executing Data Pre-Validation Gate..."
deno run -A drh-pre-etl-validation.ts "${STUDY_DATA_PATH}" "${TENANT_ID}" "${TENANT_NAME}"
echo "Starting Ingestion and Initial Transformation........."
"${TOOL_CMD}" ingest files -r "${STUDY_DATA_PATH}" --tenant-id "${TENANT_ID}" --tenant-name "${TENANT_NAME}"
"${TOOL_CMD}" orchestrate transform-csv
echo "SUCCESS: Ingestion and CSV transformation complete........."
echo "Running Post-Ingestion SQL Data Quality Validation...."
"${TOOL_CMD}" shell common-sql/drh-data-validation.sql
echo "Running Complex ETL Pipelines (Anonymization, Tracing, Metrics, etc.)........."
"${TOOL_CMD}" shell common-sql/drh-anonymize-prepare.sql
cat duckdb-etl-sql/drh-master-etl.sql | duckdb ":memory:"
"${TOOL_CMD}" shell common-sql/drh-metrics-pipeline.sql
echo "ETL process complete. Database generated successfully........."
```Task: build-server (UI Compilation)
Role: Compiles the front-end application. The Spry compiler reads all SQLPage assets (SQL files, Handlebars templates like advanced_metrics.handlebars) and inserts them directly into the SQLite file's metadata tables (sqlpage_files). The entire web UI becomes a data payload within the database.
```bash build-server --descr "Generate sqlpage_files table upsert SQL and push them to SQLite"
#!/usr/bin/env -S bash
rm -rf dev-src.auto
echo "DRH EDGE UI Build is in progress............."
./spry.ts spc -m drh-simplera-spry.md --package --conf sqlpage/sqlpage.json | sqlite3 resource-surveillance.sqlite.db
echo "Data Pipeline and UI Build complete..."
echo "DRH EDGE UI will be available at http://localhost:9227/"..."
```Run the server
Role: The final deployment. This executes the pre-compiled SQLPage binary, pointing it directly to the fully compiled SQLite database. The server instantly reads the embedded UI and data, launching the complete, interactive DRH application.
Execution Command: sqlpage
```bash
sqlpage
```Why This Approach Works
- • Single-File Portability: The entire application—data, ETL logic, and UI—exists in one SQLite file that can be copied to any machine.
- • Zero-Latency Access: All queries execute locally with sub-millisecond response times, perfect for clinical environments with unreliable connectivity.
- • Reproducible Pipelines: The entire transformation process is codified in version-controlled Markdown, ensuring consistent results across research sites.
- • Embedded UI: SQLPage transforms the database into a web server, eliminating the need for separate deployment infrastructure.
Application Screenshots (DRH UI)
The following images illustrate the final interactive web application compiled and served by the SQLPage binary using the embedded Edge Database.
Note: The user interface is dynamically generated by SQLPage, querying the data tables created in the prepare-db stage.