Out of the Box web applications on top of PostgreSQL

https://github.com/Insubric/box

select current_user

> Andrea Minetti andrea.minetti@wsl.ch
Background mainly in web development, worked in database development in the last few years.

Developing Box framework since 2016

WSL is the Swiss Federal Institute for Forest Snow and Landscape Research, part of the ETH Domain

Problem

  • Good data is a prerequisite for good research
  • Its management needs considerable resources
  • Solutions for small research groups?

Problem

  • Good data is a prerequisite for good research
  • Its management needs considerable resources
  • Solutions for small research groups?

Solution: Box Framework

  • Automatically builds a working web application by reading the database structure

Box Framework @WSL

  • 12 instances running
  • Swissfire DB - first project build in boxframework released in 2019

Two use cases

  • Internal tool for fields campaigns and data storage (focus on effortless rapid development)
  • Fully fleadged open to public/stakeholder based on stable (focus on customizable possibilities with)

Principles

  • No application code needed
    • Box user == DB user
    • Auto-generated forms for db tables/views
    • Forms definition saved in the DB
    • Web Admin interface
  • Take advantage of PostgreSQL Features
    • Row Level Security
    • Triggers
    • Updatable views
    • PostGIS

UI

  • Prebuilt widgets
  • Nested forms (One-to-many relationship)
  • GIS support (with SwissTopo Integration)

Std HTML input

Tablechild widget

EditableTable widget

Excel-like table (Preview)

File widget

Map widget

Features

  • REST API
  • Data export: XLS, CSV, SHP, Geopackage
  • Customizable HTML and PDF reports
  • Multi-language support
  • SMTP support
  • Public forms

Implementation

Simple 3 tier architecture

Presentation Layer: scala.js
Application Layer: scala
Data Layer: PostgreSQL

Implementation

On installation, a new box schema is created in the db with all configuration required by the system

Requirements

  • PostrgreSQL 15+
  • Java 17 (i.e. https://adoptium.net/ )

DevOps/Deploy

  • Publish docker image
  • Gitlab CI workflow (trigger on commit tag)
  • Github Actions workflow (trigger on commit tag)

Bootstrap

Download starter package from:
https://github.com/Insubric/box-starter/releases
unzip and run 

						
							$ ./install.sh
							$ ./run.sh

DEMO

Demo database

https://github.com/Insubric/box-starter

Next step

  • User management in admin interface
  • Improve GIS support
  • Web Interface to simplify the deploy process (no terminal needed)
  • Offline-first web application
  • Integration with other reporting systems (i.e. R shiny)
  • JSONSchema/OpenAPI native support
  • SSO integration

Thank you!

www.boxframework.com
github.com/Insubric/box