Database Schema Reference

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

Core Business Tables

Table	Purpose
threat_models	Central threat modeling projects
threat_model_access	Role-based access control (owner, writer, reader)
threats	Individual security threats with severity levels
diagrams	Visual diagram storage with JSON cells
assets	Assets within threat models

Sub-resource Tables

Table	Purpose
documents	Document references
notes	Notes attached to threat models
repositories	Source code repository references
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_deliveries	Webhook delivery attempts
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

Administration Tables

Table	Purpose
administrators	Administrator users and groups
user_api_quotas	Per-user API rate limits
user_preferences	User preferences stored as JSON

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

Dual Foreign Key Pattern

Authorization tables (threat_model_access, administrators) 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

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 and users
• All sub-resources (threats, diagrams, documents, notes, repositories) reference threat_models
• threats optionally reference diagrams and assets
• collaboration_sessions reference threat_models and diagrams

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
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 - 25 GORM model definitions for all tables (single source of truth)
• 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

• Database-Operations - Operational procedures
• Database-Setup - Initial database deployment
• Performance-and-Scaling - Optimize schema for scale
• WebSocket-API-Reference - WebSocket message format details
• Configuration-Reference - Server and application configuration