Database Setup

PostgreSQL database setup, schema, and configuration for BlueSentinel.

PostgreSQL Installation

Ubuntu/Debian

bash

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl enable postgresql
sudo systemctl start postgresql

macOS (Homebrew)

bash

brew install postgresql@16
brew services start postgresql@16

Create Database

bash

sudo -u postgres psql <<EOF
CREATE USER bluesentinel WITH PASSWORD 'your_secure_password';
CREATE DATABASE bluesentinel OWNER bluesentinel;
\c bluesentinel
CREATE EXTENSION "uuid-ossp";
EOF

The uuid-ossp extension is required for UUID generation (uuid_generate_v4()).

Run Schema Migration

bash

psql -U bluesentinel -d bluesentinel -f central_server/migrations/init_schema.sql

Schema Overview

The database contains 11 tables:

Table	PK Type	Purpose
`tenants`	UUID	Multi-tenant root organizations
`admin_users`	UUID	Dashboard users with RBAC roles
`device_groups`	UUID	Hierarchical device groups
`devices`	UUID	Enrolled endpoint devices
`policies`	UUID	Guard configuration policies
`user_exceptions`	UUID	Per-device/per-user policy overrides
`alerts`	BIGSERIAL	Security violation alerts
`commands`	UUID	Remote commands sent to devices
`usb_device_registry`	UUID	Whitelisted USB devices
`enrollment_tokens`	UUID	Device enrollment tokens
`recovery_keys`	UUID	Disk encryption recovery keys

Additional tables for audit:

recovery_key_access_log — Tracks who accessed recovery keys
admin_audit_log — All admin actions logged

Key Indexes

The schema includes several performance-critical indexes:

Partial index on unacknowledged alerts — WHERE is_acknowledged = FALSE
Partial index on default policies — WHERE is_default = TRUE
Partial index on pending commands — WHERE status = 'pending'
Device heartbeat index — last_heartbeat DESC NULLS LAST
Alert composite indexes — (tenant_id, created_at), (device_id, created_at)

Backup

bash

# Full backup
pg_dump -U bluesentinel -d bluesentinel -F c -f bluesentinel_backup.dump

# Restore
pg_restore -U bluesentinel -d bluesentinel bluesentinel_backup.dump

Connection String Format

postgresql://bluesentinel:password@hostname:5432/bluesentinel

Set via the DATABASE_URL environment variable.