STATBUS

STATBUS STATistical BUSiness Registry is a registry for tracking business activity throughout history in a country. It offers a unique database approach, using temporal tables so that one can view a timeline of the information. This allows after the fact querying at any point in time.

STATBUS is developed by Statistics Norway(SSB).

Goals

Our motto is Simple to Use, Simple to Understand, Simply Useful

In 2023 we changed the technology stack with the following goals in mind:

• Make it easy to get started with statbus in a local installation. Have a wizard/guide to help set up required configuration.

• Make it easy and fast to enter data and get feedback. Either by simple web creation, or by fast batch processing.

• Make it easy to create custom reports with a simple graph, support excel/csv export for custom graphing/processing. The report system can used for a specific year, month and day, to see development over time.

• Simple and secure advanced database integration for custom setups and integration.

• Adaptation of the original data models with insights from SSB, and partner countries in asia and africa

Technology Stack

• Supabase with
  • PostgreSQL (Database)
    • With Row Level Security
  • PostgREST (Automatic API from Database)
  • GoTrue (Supabase JWT authentication integrated with PostgREST)
• Next.js (app with backend and frontend)
  • Using the TypeScript Language.
  • Shadcn Library for components with UI and behaviour.
  • TailwindCSS For styling.
  • Highcharts For graphing.
• Docker for managing application images
  • Docker Compose for orchestration for local development and production deployment.

Running Locally

Requirements

• Docker Container Managment.
• Git Source Code Control.
• Unix Shell - comes with macOS and any Linux, for windows use Git Bash.

Instructions

Clone the repository to your local machine using Git:

git clone https://github.com/statisticsnorway/statbus.git
cd statbus

Create initial users by copying .users.example to .users.yml and adding your admin access users.

Generate Configuration Files

./devops/manage-statbus.sh generate-config

This command will create the .env, .env.credentials, and .env.config files with the required environment variables.

Start the Docker Containers with all services.

./devops/manage-statbus.sh start

Setup the database:

# First time setup only
./devops/manage-statbus.sh activate_sql_saga
./devops/manage-statbus.sh create-db-structure
./devops/manage-statbus.sh create-users

# Apply any pending database migrations
./devops/manage-statbus.sh migrate up

Database Migrations

The system uses a versioned migration system to manage database schema changes:

• Migrations are stored in migrations/ directory
• Each migration has an up and down SQL file
• Migrations can be major (e.g., 001_add_users.up.sql) or minor within a major version (e.g., 001_initial/001_base_tables.up.sql)
• Migration files are automatically versioned and tracked in the database
• The system prevents applying duplicate migrations or modified versions of previously run migrations

Migration commands:

# Apply all pending migrations
./devops/manage-statbus.sh migrate up

# Apply just one migration
./devops/manage-statbus.sh migrate up one

# Roll back last migration
./devops/manage-statbus.sh migrate down

# Roll back all migrations 
./devops/manage-statbus.sh migrate down all

# Create new migration files
./devops/manage-statbus.sh migrate new -a "description"    # Major version
./devops/manage-statbus.sh migrate new -i "description"    # Minor version

# Fix migration numbering
./devops/manage-statbus.sh migrate renumber

Connect to your local statbus at http://localhost:3000 with the users you specified in .users.yml.

The Supabase admin panel is available at http://localhost:3001 See the file .env.credentials for generated secure login credentials.

From that point on you can manage with

./devops/manage-statbus.sh stop

and

./devops/manage-statbus.sh start

Teardown

To remove all data and start over again you can do

./devops/manage-statbus.sh stop
./devops/manage-statbus.sh delete-db

rm -f .env.credentials .env.config .env

Local Development

Requirements

• Windows
  • Git for Windows with Git Bash for running sh code blocks.
  • Scoop https://scoop.sh/ For installing packages from the command line.
  • NVM-Windows https://github.com/coreybutler/nvm-windows Install with scoop install nvm
• Linux (Ubuntu/Debian)
  • Node Version Manager https://github.com/nvm-sh/nvm Install with apt install nvm
• macOS
  • Homebrew https://brew.sh/ For installing packages from the command line.
  • Node Version Manager https://github.com/nvm-sh/nvm Install with brew install nvm

Git Line Ending Handling

This project uses the LF line ending. Git on windows will, depending on installation method, change from LF to CRLF, that breaks the running of scripts both from command line and when building with Docker (Compose).

Configure git on your system with:

git config --global core.autocrlf true

Ref. https://stackoverflow.com/a/13154031/1023558

Backend (Services)

Start the Docker Containers with all services except the app

./devops/manage-statbus.sh start not_app

Frontend

The project needs the Node version specified in the .nvmrc file, that is automatically handled by nvm as mentioned in the requirements.

The app for int

Linux/Ubuntu and macOs:

cd app
nvm use
npm run start

Windows:

When running Git Bash

nvm install $(cat .nvmrc | tr -d '[:space:]')
nvm use $(cat .nvmrc | tr -d '[:space:]')
npm install
npm run start

Notice that "nvm" is a tool to install the correct node version, with the corresponding npm version, and activate it. Notice that Windows has an nvm that behaves differently.

Database Migrations

The system uses a versioned migration system to manage database schema changes:

• Migrations are stored in migrations/ directory
• Each migration has an up and down SQL file
• Migrations can be major (e.g., 001_add_users.up.sql) or minor within a major version (e.g., 001_initial/001_base_tables.up.sql)
• Migration files are automatically versioned and tracked in the database
• The system prevents applying duplicate migrations or modified versions of previously run migrations

Migration commands:

# Apply all pending migrations
./devops/manage-statbus.sh migrate up

# Apply just one migration
./devops/manage-statbus.sh migrate up one

# Roll back last migration
./devops/manage-statbus.sh migrate down

# Roll back all migrations
./devops/manage-statbus.sh migrate down all

# Create new migration files
./devops/manage-statbus.sh migrate new -a "description"    # Major version
./devops/manage-statbus.sh migrate new -i "description"    # Minor version

# Fix migration numbering
./devops/manage-statbus.sh migrate renumber

Loading Data into Statbus

Statbus comes with sample data files for Norway using NACE activity categories. Use the command palette (Ctrl+Shift+K) to access data loading options.

Loading sequence:

1. Select NACE as your activity standard
2. Upload Regions (download sample CSV from "What is Region file?")
3. Upload Sectors (download sample CSV)
4. Upload Legal Forms (download sample CSV)
5. Upload Custom Activity Categories - Contains Norwegian translations for activity codes
6. Upload Legal Units - Sample uses Norwegian regions and NACE codes
7. Upload Establishments - Sample uses Norwegian regions and NACE codes
8. Refresh materialized views using command palette (Ctrl+Shift+K)

After loading data, the Dashboard will be visible on the front page. You can reset all data through the command palette.

Data Loading Tips

• Start with classifications before loading units
• Test with a small sample of units first
• Verify classifications match your unit data
• See detailed guides at: https://www.statbus.org/files/Statbus_faq_load.html

Cloud Testing Environment

Before local installation, we recommend testing Statbus in the cloud:

• Countries get dedicated domains for testing
• Test with country-specific classifications
• Start with limited test data
• Validate data model fits your needs
• Move to local installation once tested