Database Schema Reference - ericfitz/tmi GitHub Wiki

Database Schema Reference

TMI uses two primary data stores: PostgreSQL for persistent data and Redis for real-time collaboration and caching.

Schema Overview

The TMI database schema supports a collaborative threat modeling platform with the following key features:

  • Multi-database support: PostgreSQL, Oracle ADB, MySQL, SQL Server, SQLite
  • OAuth-based authentication: Multi-provider OAuth support
  • Role-based access control (RBAC): Granular permissions for threat models (owner, writer, reader)
  • Hierarchical data model: Threat models contain threats, diagrams, documents, notes, and repositories
  • Real-time collaboration: WebSocket-based diagram collaboration with session management
  • Flexible metadata system: Key-value metadata for all entity types
  • Audit trail: Complete timestamps and user tracking

Schema Management

TMI uses GORM AutoMigrate for schema management. The schema is defined in Go structs in api/models/models.go, which serves as the single source of truth for all supported databases.

# Schema is automatically created/updated on server startup
make start-dev

# The server runs GORM AutoMigrate which creates all required tables

PostgreSQL Tables

The schema includes the following tables (defined in api/models/models.go):

Authentication Tables

Table Purpose
users User accounts with OAuth provider information
refresh_tokens JWT refresh token management
client_credentials OAuth 2.0 client credentials for machine-to-machine auth
groups Identity provider groups
group_members User memberships in groups

Teams and Projects Tables

Table Purpose
teams Team records with name, description, status, and reviewer tracking
team_members User memberships in teams with roles (unique per team+user)
team_responsible_parties Responsible parties for teams with roles (unique per team+user)
team_relationships Relationships between teams (unique per team pair)
projects Projects belonging to teams
project_responsible_parties Responsible parties for projects with roles (unique per project+user)
project_relationships Relationships between projects (unique per project pair)
team_notes Notes attached to teams (with Timmy AI and sharing flags)
project_notes Notes attached to projects (with Timmy AI and sharing flags)

Core Business Tables

Table Purpose
threat_models Central threat modeling projects (supports soft delete via deleted_at)
threat_model_access Role-based access control (owner, writer, reader)
threats Individual security threats with severity, CVSS scores, and CWE IDs
diagrams Visual diagram storage with JSON cells, color palette, and SVG image cache
assets Assets within threat models with criticality, classification, and sensitivity

Sub-resource Tables

Table Purpose
documents Document references (supports soft delete via deleted_at)
notes Notes attached to threat models (supports soft delete via deleted_at)
repositories Source code repository references with parameters JSON (supports soft delete via deleted_at)
metadata Flexible key-value metadata for all entity types

Collaboration Tables

Table Purpose
collaboration_sessions WebSocket collaboration session management
session_participants Active participant tracking

Webhook and Addon Tables

Table Purpose
webhook_subscriptions Webhook subscription configurations
webhook_quotas Per-user webhook quotas
webhook_url_deny_list Blocked URL patterns for webhooks
addons Addon configurations
addon_invocation_quotas Per-user addon invocation quotas

Survey Tables

Table Purpose
survey_templates Survey templates for security review intake (unique name+version)
survey_template_versions Versioned snapshots of survey template definitions
survey_responses User responses to survey templates with status workflow
survey_response_access Access control for survey responses (mirrors ThreatModelAccess pattern)
triage_notes Triage notes attached to survey responses (composite PK: response ID + sequential int)
survey_answers Extracted answers from survey responses, replaced on every save

Audit Tables

Table Purpose
audit_entries Audit trail tracking who changed what and when for entity mutations
version_snapshots Versioned data snapshots for rollback (checkpoints every 10th version, diffs otherwise)

Administration Tables

Table Purpose
user_api_quotas Per-user API rate limits
user_preferences User preferences stored as JSON (keyed by client app identifier)
system_settings Runtime-configurable system settings (key-value with type metadata)

Key Design Patterns

TMI implements several important database design patterns:

UUID-based Identifiers

All tables use UUIDs (varchar(36)) for primary keys, generated automatically via GORM's BeforeCreate hook using uuid.New().String().

Provider-based Identity

Users and groups are scoped by OAuth provider to support multi-provider authentication:

  • Provider field stores: "tmi", "google", "github", "microsoft", "azure"
  • Users are provider-scoped: alice@google and alice@github are different users
  • Groups support provider-specific or wildcard ("*") for cross-provider groups

Soft Delete Pattern

Several core entities support soft deletion via a nullable deleted_at timestamp field:

  • threat_models, diagrams, assets, threats, documents, notes, repositories

Soft-deleted records are retained in the database and can be filtered out or restored.

Dual Foreign Key Pattern

Authorization tables (threat_model_access, survey_response_access) support both user and group subjects using XOR constraints:

  • Either user_internal_uuid OR group_internal_uuid is populated, never both
  • subject_type field discriminates between 'user' and 'group'

Authorization Inheritance

Child resources inherit authorization from their parent threat model:

  • Diagrams, threats, assets, documents, notes, repositories all inherit from threat_models
  • No direct access control on child resources - access is determined by threat model access

Threat Modeling Frameworks

The threat_model_framework field supports five methodologies:

  • CIA - Confidentiality, Integrity, Availability
  • STRIDE - Spoofing, Tampering, Repudiation, Information Disclosure, Denial of Service, Elevation of Privilege (default)
  • LINDDUN - Linkability, Identifiability, Non-repudiation, Detectability, Disclosure of information, Unawareness, Non-compliance
  • DIE - Distributed, Immutable, Ephemeral
  • PLOT4ai - AI-focused threat modeling

Asset Types

The assets.type field supports:

  • data, hardware, software, infrastructure, service, personnel

Audit Trail

TMI maintains a comprehensive audit trail for all entity mutations:

  • audit_entries records who changed what and when, with denormalized actor fields (not FKs) so entries persist after user deletion
  • version_snapshots stores rollback data as either full JSON checkpoints (every 10th version) or reverse JSON Patch diffs (RFC 6902)
  • Change types: created, updated, patched, deleted, restored, rolled_back
  • Object types: threat_model, diagram, threat, asset, document, note, repository

Key Schema Characteristics

Primary Keys

All tables use UUID (varchar(36)) as primary keys, generated automatically on record creation.

Timestamps

All tables include:

  • created_at - Record creation time (auto-generated)
  • modified_at - Last modification time (auto-updated)

Foreign Key Relationships

  • threat_model_access references threat_models, users, and groups
  • All sub-resources (threats, diagrams, documents, notes, repositories) reference threat_models
  • threats optionally reference diagrams, assets, and cells
  • collaboration_sessions reference threat_models and diagrams
  • teams are referenced by projects, team_members, team_responsible_parties, team_relationships, and team_notes
  • projects are referenced by project_responsible_parties, project_relationships, project_notes, threat_models, and survey_responses
  • survey_responses reference survey_templates and optionally link to threat_models (linked and created)
  • survey_response_access references survey_responses, users, and groups
  • audit_entries reference threat model IDs (not as FK, so entries persist after deletion)
  • version_snapshots reference audit_entries

Access Control Model

Authorization is stored in threat_model_access:

  • Owner - Full read/write/delete permissions, can manage access
  • Writer - Read and write permissions
  • Reader - Read-only permissions

Access can be granted to individual users or groups.

Redis Schema

Redis provides real-time collaboration features and comprehensive caching with structured key patterns.

Key Naming Convention

All Redis keys follow a hierarchical naming pattern:

{namespace}:{type}:{identifier}:{sub-identifier}

Authentication & Session Keys

Key Pattern Data Type TTL Description
session:{user_id}:{session_id} Hash 24 hours User session data
auth:token:{token_id} String Token expiry JWT token cache
auth:refresh:{refresh_token_id} Hash 30 days Refresh token data
auth:state:{state} Hash 10 minutes OAuth state data for PKCE flow
blacklist:token:{jti} String Token expiry Revoked JWT tokens

Rate Limiting Keys

Key Pattern Data Type TTL Description
rate_limit:global:{ip}:{endpoint} String 1 minute Global rate limiting per IP/endpoint
rate_limit:user:{user_id}:{action} String 1 minute User-specific rate limiting
rate_limit:api:{api_key}:{endpoint} String 1 hour API key rate limiting

Entity Cache Keys

Key Pattern Data Type TTL Description
cache:user:{user_id} JSON 15 minutes User profile cache (by internal UUID)
cache:user:email:{email} JSON 15 minutes User profile cache (by email)
cache:user:provider:{provider}:{provider_user_id} JSON 15 minutes User profile cache (by provider and provider user ID)
cache:threat_model:{model_id} JSON 10 minutes Threat model cache
cache:diagram:{diagram_id} JSON 2 minutes Diagram data cache
cache:threat:{threat_id} JSON 5 minutes Individual threat cache
cache:document:{document_id} JSON 5 minutes Document reference cache
cache:repository:{repository_id} JSON 5 minutes Repository cache
cache:note:{note_id} JSON 5 minutes Note cache
cache:asset:{asset_id} JSON 5 minutes Asset cache
cache:metadata:{entity_type}:{entity_id} JSON 7 minutes Entity metadata cache
cache:cells:{diagram_id} JSON 2 minutes Diagram cells cache
cache:auth:{threat_model_id} JSON 15 minutes Authorization data cache
cache:list:{entity_type}:{parent_id}:{offset}:{limit} JSON 5 minutes Paginated list cache

Temporary Operation Keys

Key Pattern Data Type TTL Description
temp:export:{job_id} Hash 1 hour Export job status
temp:import:{job_id} Hash 1 hour Import job status
lock:{resource}:{id} String 30 seconds Distributed locks

Cache TTL Strategy

Cache Type TTL Justification
Threat Models 10 minutes Core entities, moderate update frequency
Diagrams 2 minutes High collaboration, real-time updates
Sub-resources 5 minutes Threats, documents, repositories - balanced consistency
Authorization 15 minutes Security-critical, infrequent changes
Metadata 7 minutes Flexible data, moderate update frequency
Lists 5 minutes Paginated results, balance between performance and freshness

Cache Invalidation

TMI implements proactive cache invalidation through the CacheService:

  • Entity Updates: Individual entity caches are invalidated on modification
  • Metadata Changes: Entity-specific metadata caches are cleared
  • Authorization Updates: Auth data cache is invalidated on role changes
  • Cascade Invalidation: Parent entity updates trigger related cache clearing

Redis Operations

# Connect to Redis
redis-cli

# View all keys by pattern
redis-cli --scan --pattern "cache:threat_model:*"

# Check TTL of a key
redis-cli TTL "cache:threat_model:uuid-here"

# Monitor real-time activity
redis-cli MONITOR

Schema Source Code

The definitive schema is defined in:

  • api/models/models.go, api/models/survey_models.go, api/models/team_project_models.go, api/models/team_project_note_models.go, api/models/audit.go, api/models/system_setting.go - 41 GORM model definitions for all tables (single source of truth, registered in AllModels() in models.go)
  • api/validation/validators.go - Business rules (replaces PostgreSQL CHECK constraints)
  • api/seed/seed.go - Required initial data (everyone group, webhook deny list)

To view the complete schema with all field types and constraints, refer to the source code.

Legacy SQL Migrations

TMI originally used golang-migrate with PostgreSQL-specific SQL migration files. When multi-database support was added (PostgreSQL, Oracle, MySQL, SQL Server, SQLite), the architecture was changed to use GORM AutoMigrate.

Legacy SQL migration files exist in docs/reference/legacy-migrations/ for historical reference. These are preserved for:

  1. Reference: Understanding the original PostgreSQL-specific schema design
  2. Documentation: Viewing what triggers, partial indexes, and CHECK constraints were used
  3. Troubleshooting: Comparing GORM-generated schema with the original design

PostgreSQL-Specific Features (No Longer Used):

  • Partial indexes (e.g., WHERE deleted_at IS NULL)
  • GIN indexes for JSONB columns
  • Covering indexes (INCLUDE clause)
  • PostgreSQL triggers for modified_at timestamps
  • CHECK constraints for enum validation
  • Native UUID type (GORM uses varchar(36) for Oracle compatibility)

Note: These legacy migration files are not executed by the current codebase. They are for reference only.

Viewing the Database Schema

# View schema in PostgreSQL
psql -U tmi_dev -d tmi_dev -c "\dt"

# View table structure
psql -U tmi_dev -d tmi_dev -c "\d+ threat_models"

# Export schema
pg_dump -U tmi_dev -d tmi_dev --schema-only > schema.sql

WebSocket Message Schemas

The TMI WebSocket API uses JSON messages for real-time collaboration. See WebSocket-API-Reference for complete documentation.

Common Message Fields

All WebSocket messages include:

  • message_type - Message type identifier
  • timestamp - ISO 8601 timestamp

Collaboration Message Examples

// Diagram operation message
{
  "message_type": "diagram_operation",
  "user_internal_uuid": "UUID",
  "operation_id": "UUID",
  "sequence_number": 1,
  "operation": {
    "type": "patch",
    "cells": [
      {
        "id": "cell-id",
        "operation": "add|update|remove",
        "data": {}
      }
    ]
  },
  "timestamp": "2025-01-24T12:00:00Z"
}

// Presenter mode messages
{
  "message_type": "current_presenter",
  "current_presenter": "UUID",
  "timestamp": "2025-01-24T12:00:00Z"
}

// Session management
{
  "event": "join|leave|session_ended",
  "user_internal_uuid": "UUID",
  "message": "optional reason",
  "timestamp": "2025-01-24T12:00:00Z"
}

Related Documentation