Data-Driven Operational Dashboard (SQLPage)

Goal: Build a robust, SQL-driven dashboard where the schema is managed as a separate asset and executed via the surveilr engine.

Initialize and Configure

Set up your environment. We use ${env.SPRY_DB} to ensure flexibility between local development and production environments.

spry sp init

export SPRY_DB="sqlite://sales.db?mode=rwc"
export PORT=9227

Orchestrate Database with Surveilr

Instead of embedding long SQL strings in Markdown, we use surveilr to execute the external sales.sql file. This keeps our runbook clean.

#!/usr/bin/env -S bash
# Initialize the sqlite db
surveilr admin init -d sales.sqlite.db
# Execute the external schema file using the surveilr shell
surveilr shell sales.sql -d sales.sqlite.db

Why Surveilr? Unlike standard SQLite, surveilr provides a unified resource database environment, making it easier to scale from a simple table to a complex "Mesh" of system data.

Define the Global UI Shell

Inject a consistent navigation and layout into every page of the application automatically.

SELECT 'shell' AS component,
       'Sales Intelligence' AS title,
       'chart-bar' AS icon,
       'index.sql' AS link,
       '{"link":"/index.sql","title":"Home"}' AS menu_item,
       '{"link":"/report.sql","title":"Sales Report"}' AS menu_item;

The Dashboard (index.sql)

The dashboard consumes the data defined in sales.sql.

-- Card Metrics
SELECT 'card' AS component, 3 AS columns;
SELECT 'Total Revenue' AS title, '$' || SUM(amount) AS description, 'green' as color FROM sales;
SELECT 'Regional Markets' AS title, COUNT(DISTINCT region) AS description, 'blue' as color FROM sales;
SELECT 'Latest Order' AS title, product AS description FROM sales ORDER BY sale_date DESC LIMIT 1;

-- Visual Analytics
SELECT 'chart' AS component, 'Revenue by Region' AS title, 'bar' AS type;
SELECT region AS x, SUM(amount) AS y FROM sales GROUP BY region;

Execute and Deploy

Execute the database preparation task and start the live-reload development server.

# 1. Set the ENV defined in Step 1
spry rb task prepare-env

# 2. Run the DB task defined in Step 2
spry rb task prepare-db

# 3. Launch Development Server
spry sp spc --fs dev-src.auto --destroy-first --conf sqlpage/sqlpage.json --watch --with-sqlpage

Package the Application

Combine your UI components, business logic, and database schema into a single, portable binary artifact. We use surveilr here to ensure the internal sqlpage_files table is correctly populated.

# Package the UI and inject it into the production database
spry sp spc --package --conf sqlpage/sqlpage.json | surveilr shell sales.sqlite.db

View Schema

Access the raw SQL definition for the database.

Operational Runbook

Learn how to add health checks to this application using spry rb.

How is this guide?