Skip to content

Installation & Setup

Prerequisites

PostgreSQL

The system requires PostgreSQL with the following extensions:

Extension Purpose
ltree Hierarchical permission paths
uuid-ossp UUID generation for users, tenants, etc.
unaccent Accent-insensitive text search
pg_trgm Trigram-based fuzzy search

All extensions are created automatically during migration (in the ext schema), so they just need to be available in your PostgreSQL installation.

debee

Database operations are managed through debee, a database migration and management tool available in three flavors:

  • debee.ps1 -- PowerShell (Windows/Linux/macOS)
  • debee.sh -- Bash (Linux/macOS)
  • debee.py -- Python (cross-platform)

All three support the same operations and parameters.

Environment Configuration

Primary Configuration

Create or edit debee.env in the project root with your PostgreSQL connection details:

PGHOST=localhost
PGPORT=5432
PGUSER=postgres
PGPASSWORD=your_password
DBDESTDB=your_database_name

Local Overrides

For developer-specific settings that should not be committed to version control, create .debee.env (note the leading dot). This file is gitignored and its values override debee.env.

Environment-Specific Configuration

For different deployment targets, use environment-specific files with the pattern debee.{environment}.env:

# debee.prod.env
PGHOST=prod-db.example.com
PGPORT=5432
PGUSER=app_user
PGPASSWORD=prod_password
DBDESTDB=production_db

Use the -Environment parameter to select which environment file to load:

./debee.ps1 -Environment prod -Operations updateDatabase

Key Environment Variables

Variable Description Default
PGHOST PostgreSQL host localhost
PGPORT PostgreSQL port 5432
PGUSER PostgreSQL user postgres
PGPASSWORD PostgreSQL password --
DBDESTDB Target database name postgresql_permissionmodel
DBUPDATESTARTNUMBER Start migration script number --
DBUPDATEENDNUMBER End migration script number --
DBBACKUPFILE Backup file path for restore --
DBBACKUPTYPE Backup type for restore --

Database Setup

The fullService operation performs a complete setup: recreate the database, restore from backup (if configured), and run all migration scripts:

./debee.ps1 -Operations fullService

./debee.sh -o fullService

python debee.py -o fullService

Individual Operations

You can run each step independently:

# Recreate database (drops and creates fresh)
./debee.ps1 -Operations recreateDatabase

# Restore from backup
./debee.ps1 -Operations restoreDatabase

# Run all migration scripts
./debee.ps1 -Operations updateDatabase

# Run pre-update scripts
./debee.ps1 -Operations preUpdateScripts

# Run post-update scripts
./debee.ps1 -Operations postUpdateScripts

./debee.sh -o recreateDatabase
./debee.sh -o restoreDatabase
./debee.sh -o updateDatabase
./debee.sh -o preUpdateScripts
./debee.sh -o postUpdateScripts

python debee.py -o recreateDatabase
python debee.py -o restoreDatabase
python debee.py -o updateDatabase
python debee.py -o preUpdateScripts
python debee.py -o postUpdateScripts

Running a Specific Migration Range

To run only a subset of migration scripts:

./debee.ps1 -Operations updateDatabase -UpdateStartNumber 001 -UpdateEndNumber 010

./debee.sh -o updateDatabase --update-start-number 001 --update-end-number 010

Running SQL Commands

Use the execSql operation for ad-hoc SQL execution:

# Run inline SQL
./debee.ps1 -Operations execSql -Sql "select * from auth.user_info limit 5;"

# Run SQL file
./debee.ps1 -Operations execSql -SqlFile 999-examples.sql

# Interactive psql session
./debee.ps1 -Operations execSql

./debee.sh -o execSql --sql "select * from auth.user_info limit 5;"
./debee.sh -o execSql --sql-file 999-examples.sql
./debee.sh -o execSql

python debee.py -o execSql --sql "select * from auth.user_info limit 5;"
python debee.py -o execSql --sql-file 999-examples.sql
python debee.py -o execSql

Execution priority

When multiple SQL sources are specified, priority is: file > inline command > interactive session.

Running Tests

Tests validate the entire permissions system. They are organized as suite directories with numbered SQL files and optional test.json manifests.

# Run all tests
./debee.ps1 -Operations runTests

# Run filtered tests (substring match on file/directory name)
./debee.ps1 -Operations runTests -TestFilter provider
./debee.ps1 -Operations runTests -TestFilter cache

./debee.sh -o runTests
./debee.sh -o runTests --test-filter provider

python debee.py -o runTests
python debee.py -o runTests --test-filter provider

make test
make test FILTER=resource

You can also run a single test file directly:

./debee.sh -o execSql --sql-file tests/test_provider_crud/001_create_provider.sql

Test suites

Tests are organized into suite directories (test_*/) with numbered SQL files. Files in the 000--899 range are the main phase (stops on first error); files in the 900--999 range are cleanup (always runs if always_cleanup: true). See the project's CLAUDE.md for the full test organization reference.

Version Management

The system tracks applied migrations using the public.__version table. Each migration script should declare its version:

-- start a version update (creates record with start timestamp)
select * from public.start_version_update('1.16', 'Description of update', 'Optional longer description', 'component_name');

-- migration content goes here

-- mark version complete (sets finish timestamp)
select * from public.stop_version_update('1.16', 'component_name');

To check whether a version has already been applied:

select public.check_version('1.16', 'component_name');

The version table tracks:

  • component -- supports multiple components (defaults to 'main')
  • version -- version identifier
  • title and description -- human-readable metadata
  • execution_started and execution_finished -- timestamps for tracking migration duration

Migration File Naming

Migration files follow the pattern XXX_description.sql where XXX is a 3-digit number:

File Purpose
000_create_database.sql Initial database creation
001_create_basic_structure.sql Core schemas and extensions
002_create_version_management.sql Version tracking (__version table)
004--009_create_helpers*.sql Helper functions and utilities
012_tables_const.sql Constant/configuration tables
013_tables_auth.sql Core auth tables (users, groups, permissions, tenants)
015--029 Functions (error, triggers, public, unsecure, auth), views, seed data
030--032 Language and translation system
033 Cache invalidation and notification triggers
034--035 Resource access (ACL) tables and functions
036--040 MFA tables, auto-lockout, MFA policy
041--042 Invitation system
043--044 Resource roles
045 Translation context functions
046 Seed translations for core data
047 Permission/provider/group seeding
099, 99_fix_permissions.sql Post-update permission fixes

Next Steps