---
title: 13.02 Database Schema Overview
description: SimpleRisk's database has dozens of tables across risks, mitigations, controls, audits, documents, frameworks, users, permissions, configuration, and…
---
[Skip to content](https://support.simplerisk.com/kb/13-02-database-schema-overview#main-content)
English
Show submenu for translations
[Customer portal](https://support.simplerisk.com/tickets?hsLang=en)
[](https://www.simplerisk.com/)
Open main navigation
Close main navigation
- English
Show submenu for translations
- [Customer portal](https://support.simplerisk.com/tickets)
- [Contact us](https://www.simplerisk.com/about-us/contact-us)
[Contact us](https://www.simplerisk.com/about-us/contact-us)
How can we help you?
- There are no suggestions because the search field is empty.
1. [SimpleRisk Knowledge Base](https://support.simplerisk.com/kb?hsLang=en)
2. [Administrator Guide](https://support.simplerisk.com/kb/administrator-guide?hsLang=en)
3. [13 Reference](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#13-reference)
# 13.02 Database Schema Overview
## SimpleRisk's database has dozens of tables across risks, mitigations, controls, audits, documents, frameworks, users, permissions, configuration, and per-Extra additions. This reference catalogs the major tables grouped by domain so operators querying or analyzing the database know where things live.
## 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 {table_name};
SHOW CREATE TABLE {table_name};
```
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](https://support.simplerisk.com/kb/13-01-config-settings-reference?hsLang=en).
- **`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](https://support.simplerisk.com/kb/05-04-managing-dropdown-values?hsLang=en) 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 "{table_name}" 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.
## Related
- [Settings Reference](https://support.simplerisk.com/kb/13-01-config-settings-reference?hsLang=en)
- [Cron Jobs Reference](https://support.simplerisk.com/kb/13-03-cron-jobs-reference?hsLang=en)
- [Default Roles and Permissions](https://support.simplerisk.com/kb/13-04-default-roles-and-permissions?hsLang=en)
- [Known Limitations](https://support.simplerisk.com/kb/13-05-known-limitations?hsLang=en)
- [Securing the Database](https://support.simplerisk.com/kb/09-04-securing-the-database?hsLang=en)
- [Database Backup and Restore](https://support.simplerisk.com/kb/02-05-database-backup-and-restore?hsLang=en)
- [The Encryption Extra Overview](https://support.simplerisk.com/kb/09-01-the-encryption-extra-overview?hsLang=en)
- [The Audit Trail](https://support.simplerisk.com/kb/11-01-the-audit-trail?hsLang=en)
- [FAQs](https://support.simplerisk.com/kb/faqs?hsLang=en)
- [SimpleRisk Extras](https://support.simplerisk.com/kb/simplerisk-extras?hsLang=en#main-content)
- [Vulnerability Management Extra](https://support.simplerisk.com/kb/simplerisk-extras?hsLang=en#vulnerability-management-extra)
- [Team Separation Extra](https://support.simplerisk.com/kb/simplerisk-extras?hsLang=en#team-separation-extra)
- [Import-Export Extra](https://support.simplerisk.com/kb/simplerisk-extras?hsLang=en#import-export-extra)
- [Administrator Guide](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#main-content)
- [00 About This Guide](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#00-about-this-guide)
- [01 Installation and Deployment](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#01-installation-and-deployment)
- [02 Upgrades and Maintenance](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#02-upgrades-and-maintenance)
- [03 Users and Permissions](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#03-users-and-permissions)
- [04 Authentication](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#04-authentication)
- [05 Customization](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#05-customization)
- [06 Configuring Risk and Compliance](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#06-configuring-risk-and-compliance)
- [07 Integrations](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#07-integrations)
- [08 The API](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#08-the-api)
- [09 Encryption and Data Security](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#09-encryption-and-data-security)
- [10 Workflows and Automation](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#10-workflows-and-automation)
- [11 Reporting and Auditing](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#11-reporting-and-auditing)
- [12 Operations](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#12-operations)
- [13 Reference](https://support.simplerisk.com/kb/administrator-guide?hsLang=en#13-reference)
- [User Guide](https://support.simplerisk.com/kb/user-guide?hsLang=en#main-content)
- [00 Foundations of GRC](https://support.simplerisk.com/kb/user-guide?hsLang=en#00-foundations-of-grc)
- [01 Risk Management](https://support.simplerisk.com/kb/user-guide?hsLang=en#01-risk-management)
- [02 Compliance Management](https://support.simplerisk.com/kb/user-guide?hsLang=en#02-compliance-management)
- [03 Governance](https://support.simplerisk.com/kb/user-guide?hsLang=en#03-governance)
- [04 Asset and Data Inventory](https://support.simplerisk.com/kb/user-guide?hsLang=en#04-asset-and-data-inventory)
- [05 Threat and Vulnerability Management](https://support.simplerisk.com/kb/user-guide?hsLang=en#05-threat-and-vulnerability-management)
- [06 Assessments](https://support.simplerisk.com/kb/user-guide?hsLang=en#06-assessments)
- [07 Incident Management](https://support.simplerisk.com/kb/user-guide?hsLang=en#07-incident-management)
- [08 Audit and Reporting](https://support.simplerisk.com/kb/user-guide?hsLang=en#08-audit-and-reporting)
- [09 Day-to-Day SimpleRisk](https://support.simplerisk.com/kb/user-guide?hsLang=en#09-day-to-day-simplerisk)
- [10 Continuous Improvement](https://support.simplerisk.com/kb/user-guide?hsLang=en#10-continuous-improvement)
- [SimpleRisk User Guides](https://support.simplerisk.com/kb/simplerisk-user-guides?hsLang=en)
- [Troubleshooting](https://support.simplerisk.com/kb/troubleshooting?hsLang=en)
- [SimpleRisk Hosted](https://support.simplerisk.com/kb/simplerisk-hosted?hsLang=en)
- [How To videos](https://support.simplerisk.com/kb/how-to-videos?hsLang=en)
[](https://www.simplerisk.com)
Copyright © 2026, SimpleRisk, Inc.