Documentation index

Database Schema Guide

Purpose

The on-premise database stores the customer's application control records and customer evidence inside the customer environment.

Schema Files

1. 001_onprem_control_schema.sql - local users, sessions, license state, audit, settings, and migrations.
2. 002_workbench_customer_schema.sql - people, accounts, functions, assets, evidence, SSPs, vendors, risk, and reports.
3. 003_seed_core_reference_data.sql - standard departments, workflow stages, framework references, and license tiers.
4. 004_reporting_views.sql - license usage, relationship maps, audit posture, and risk-register summaries.

Roles

Use a schema-setup role only during controlled migrations. Configure the application with a separate least-privilege runtime role. Human database administrators use named accounts with MFA through the customer access plane where supported.

Import

.\scripts\apply_schema.ps1 -DatabaseUrl "postgresql://setup_user:password@db.example.com:5432/projectxit_workbench"

./scripts/apply_schema.sh "postgresql://setup_user:password@db.example.com:5432/projectxit_workbench"

Required Controls

• Encrypt database storage and backups with customer-controlled keys where available.
• Require encrypted database transport and certificate validation.
• Restrict TCP 5432 to approved application, backup, monitoring, and DBA hosts.
• Store connection secrets outside source control.
• Enable database audit logging appropriate to the customer's data classification.
• Monitor capacity, failed authentication, privilege changes, schema changes, replication, and backup failures.
• Test point-in-time recovery and full restoration.

Validation

After import, confirm every migration is recorded, the runtime role cannot create roles or databases, reporting views return without elevated privileges, application health checks pass, and backup tooling can read the required objects.