13.02 Database Schema Overview

Why this is a reference article

This article covers the major SimpleRisk database tables grouped by domain. For exact schema (column names, types, indexes, foreign keys), the canonical source is simplerisk/includes/upgrade.php (specifically the CREATE TABLE statements in the upgrade functions); for the live install's actual schema, query INFORMATION_SCHEMA:

SHOW TABLES;
DESCRIBE 
  
   ; SHOW CREATE TABLE 
   
    ;

The catalog below is for orientation: knowing where to look. The exact schema may evolve across versions; verify against the install.

Risk management

risks: The risk register. One row per risk with subject, status, owner, dates, etc.
risk_scoring: Per-risk scoring inputs and computed score. Joined to risks 1:1.
risk_to_team: Risks ↔ teams junction (which teams can see which risks).
mitigations: The per-risk mitigation row (1:1 with risk for the standard workflow).
mitigation_to_team: Mitigations ↔ teams junction.
mgmt_reviews: Per-risk review history (one row per review, with next_review date).
closures: Per-risk closure record.
comments: Risk and mitigation comments.
risk_to_additional_stakeholder: Additional-stakeholder assignments per risk.
risk_catalog: The risk catalog (predefined risk templates).
threat_catalog: The threat catalog.
risk_grouping: Risk grouping lookup (used by the catalog).
threat_grouping: Threat grouping lookup.

Compliance

frameworks: Compliance frameworks. Encrypted name and description when Encryption Extra is active.
framework_controls: Controls within frameworks.
framework_controls_mappings: Control-to-control cross-framework mappings (when manually maintained).
tests: Control test definitions.
test_results: Test execution records.
audits: Audit cycles.
audit_tests: Tests within an audit.
items_to_teams: Generic teams junction for tests, audits, and other items (type column distinguishes).

Governance

documents: Policy and procedure documents.
document_exceptions: Exception requests against documents.
exceptions: Approved exceptions.
document_to_team: Document ↔ teams junction.
projects: Projects (also used for "programs" — same entity).

Asset and data inventory

assets: Asset inventory (when the Asset Management Extra is active or for Core asset references).
asset_groups: Asset group lookup.
risk_to_asset_group: Risks ↔ asset groups.
risks_to_assets: Risks ↔ specific assets.

User management and permissions

user: User accounts. Includes password (bcrypt hash), multi_factor, lockout, change_password, language, selected_business_unit, admin flag.
user_mfa: Per-user MFA secrets (uid, secret, last_mfa_token, etc.).
pass_history: Prior password hashes for reuse-limit enforcement.
failed_login_attempts: Failed-login tracking for lockout.
permissions: Catalog of permission keys.
permission_to_user: Direct user-to-permission grants.
role: Role definitions.
role_responsibilities: Role-to-permission mappings.
permission_groups, permission_to_permission_group: UI organization for permissions.
team: Team catalog.
user_to_team: User ↔ team junction.
business_unit (Organizational Hierarchy Extra): Business units.
business_unit_to_team, business_unit_to_template_group: Business-unit junctions.
remote_team (Authentication Extra): LDAP/SAML remote-team mappings.
ldap_group_and_teams (Authentication Extra): LDAP-specific group ↔ team junction.

Configuration and operations

settings: The configuration key-value store. See Settings Reference.
audit_log: The audit trail.
debug_log: The debug log (when database destination is configured).
sessions: Database-backed session storage.
cron_history: Cron job execution records.
notification_sent_log (Notification Extra): Sent-notification dedup and audit.

Lookup tables (dropdowns)

These back the various dropdown fields throughout the UI:

category: Risk Category dropdown.
source: Risk Source dropdown.
location: Location dropdown.
technology: Technology / Asset Class dropdown.
status: Risk status values.
close_reason: Close-reason dropdown.
regulation: Regulation lookup.
family: Risk family classifications.
planning_strategy: Mitigation Planning Strategy.
mitigation_effort: Mitigation Effort.
mitigation_cost: Mitigation Cost.
mitigation_controls: Mitigation control mapping.
review: Review verdict values.
next_step: Next-step labels.
test_status, audit_status, audit_phase: Compliance lookups.
test_frequency: Test frequency dropdown.
risk_levels, review_levels: Level definitions and per-level review days.

See Managing Dropdown Values for management.

API

api_keys: Per-user API key hashes.

Customization Extra

custom_fields: Custom field definitions.
custom_data: Per-record custom field values.
custom_template: Per-form layout configuration.

Encryption Extra

encrypted_fields: Tracks which fields are encrypted.

(The master key isn't in the database; it's in extras/encryption/includes/init.php.)

Workflows Extra

workflow_definitions: Workflow definitions (JSON node graph).
workflow_executions: Per-execution instances and status.
workflow_email_templates: Email body templates referenced by send-email actions.
workflow_step_outputs: Per-step output for variable substitution.

Jira Extra

jira_issues: Risk ↔ Jira issue mappings.
jira_risk_pending_changes: Per-field change queue.

UCF Extra

ucf_ad_lists: UCF authority document lists.
ucf_authority_documents: UCF AD ↔ SimpleRisk framework mappings.
ucf_authority_document_controls: UCF control ↔ SimpleRisk control mappings.
ucf_audit_items: Audit-related UCF entries.

ComplianceForge SCF Extra

securecontrolsframework: The main SCF catalog.
securecontrolsframework_columns: Per-authority-document mapping columns.

Plus per-framework mapping tables created during framework-to-SCF mapping.

Import-Export Extra

import_export_github_frameworks: Tracks frameworks installed via the GitHub installer.

Schema migration source

The authoritative source for schema is simplerisk/includes/upgrade.php. Each upgrade function contains the CREATE TABLE, ALTER TABLE, and data-migration statements that produced the table state for that release. To trace when a column was added or modified:

grep -n "
  
   " simplerisk/includes/upgrade.php

For the current release's schema state, query the live database via SHOW CREATE TABLE rather than reconstructing from upgrade history.

Common pitfalls

A handful of patterns recur with database queries.

Querying without filters on large tables. audit_log and debug_log can be massive; full-table scans are slow.
Joining without indexes. Custom queries that join across tables without index support produce nested-loop scans. Check EXPLAIN.
Modifying tables directly without understanding the application's expectations. Some "obvious" changes (renaming columns, adding constraints) break the application.
Forgetting that some columns are encrypted. Direct queries return ciphertext for encrypted fields.
Dropping tables. Drops are permanent (unless you have a backup). Don't drop without certainty.
Querying production for ad-hoc analysis. Use a read replica or restored backup; don't introduce additional load on the production primary.
Treating the schema documentation as exhaustive. This article covers the major tables; there are many more (Extras-specific, helper tables, junctions). Use SHOW TABLES for the full list on your install.
Modifying lookup table rows by ID instead of by value. Some application code references lookups by ID; some by value. Verify before modifying.
Adding indexes without understanding write impact. Indexes speed reads but slow writes.