Database Tool Reference - ericfitz/tmi GitHub Wiki

Database Tool Reference

tmi-dbtool is the command-line tool for TMI database administration: schema migration, system data seeding, configuration import, and test data import.

Installation

# Standard build (PostgreSQL)
make build-dbtool

# Oracle Autonomous Database build
make build-dbtool-oci

The binary is written to bin/tmi-dbtool.

Startup Banner and Exit Summary

On startup, tmi-dbtool prints a banner to stderr with version, commit hash, build timestamp, and the number of schema models. On exit, it prints a JSON summary to stdout with the operation result:

{
  "tool": "tmi-dbtool",
  "version": "0.14.0",
  "commit": "abc1234",
  "built_at": "2026-04-10T00:00:00Z",
  "schema_models": 25,
  "arguments": { "schema": true, "dry_run": false },
  "status": "success",
  "error": ""
}

The exit summary enables scripted workflows to parse the result without scraping log output.

Modes

tmi-dbtool operates in one of four modes, depending on which operation flag is specified.

Health Check Mode (no operation flags)

When no operation flag is given, tmi-dbtool connects to the database and reports a health summary: database type and version, schema status (current or needs migration), and system data health (built-in groups, webhook deny list).

tmi-dbtool --config=config-development.yml

Use this to verify a database is correctly set up before deploying the server, or to diagnose schema issues after an upgrade.

Schema Mode (`--schema` / `-s`)

Runs GORM AutoMigrate to create or update all database tables, applies post-migration data fixups (e.g., normalizing enum values), and seeds built-in system data (groups, webhook deny list). This is the same schema and seed logic that runs automatically during server startup when the server has DDL permissions.

tmi-dbtool --schema --config=config-development.yml

Use --dry-run to preview what changes would be made without writing:

tmi-dbtool --schema --config=config-development.yml --dry-run

Use this when setting up a new database, after a database reset, or when upgrading the server in a Database-Security-Strategies where the server does not have DDL permissions.

Import Config Mode (`--import-config` / `-c`)

Migrates configuration file settings to the database. Infrastructure keys stay in the config file; all other settings are written to the system_settings table.

tmi-dbtool --import-config --input-file=config-production.yml \
           --output=config-production-migrated.yml

If --config is not specified, the --input-file value is also used for the database connection. See Config-Migration-Guide for the full workflow.

Import Test Data Mode (`--import-test-data` / `-t`)

Seeds entities from a declarative JSON seed file. Entities are created via direct database writes or the TMI REST API, depending on the entity kind.

tmi-dbtool --import-test-data --input-file=test/seeds/cats-seed-data.json \
           --config=config-development.yml \
           --server=http://localhost:8080 \
           --user=charlie --provider=tmi

Import test data mode requires:

A running TMI server (for API-created entities)
The OAuth callback stub running (make start-oauth-stub)
A valid config file for database access

CLI Flags

Flag	Short	Default	Description
`--schema`	`-s`	`false`	Create/migrate schema and seed system data
`--import-config`	`-c`	`false`	Import config file settings into database
`--import-test-data`	`-t`	`false`	Import test data from a seed file
`--input-file`	`-f`	(none)	Input file (config YAML for `-c`, seed JSON for `-t`). Required for `-c` and `-t`.
`--config`		(none)	Path to TMI configuration file (provides DB connection via `database.url`). For `-c`, defaults to `--input-file` if not set.
`--output`		(auto)	Path for migrated config YAML (`-c` only). If omitted, appends `-migrated` to the input filename.
`--server`		`http://localhost:8080`	TMI server URL for API calls (`-t` only)
`--user`		`charlie`	OAuth user ID for API authentication (`-t` only)
`--provider`		`tmi`	OAuth provider name (`-t` only)
`--overwrite`		`false`	Overwrite existing DB settings (`-c` only)
`--dry-run`		`false`	Show what would happen without writing
`--verbose`	`-v`	`false`	Enable debug logging

Only one operation flag (-s, -c, -t) can be specified at a time. If none is specified, tmi-dbtool runs in health check mode.

Seed Data File Format

Seed data files use a JSON envelope format. See test/seeds/cats-seed-data.json for a complete example.

Envelope Structure

{
  "format_version": "1.0",
  "description": "Human-readable description of this seed file",
  "created_at": "2026-04-10T00:00:00Z",
  "output": {
    "reference_file": "path/to/output.json",
    "reference_yaml": "path/to/output.yml"
  },
  "seeds": [
    { "kind": "...", "ref": "...", "data": { ... } }
  ]
}

Field	Required	Description
`format_version`	Yes	Schema version (currently `"1.0"`)
`description`	No	Human-readable description
`created_at`	No	Timestamp of file creation
`output`	No	Configures reference file generation after seeding
`output.reference_file`	No	Path for JSON reference output
`output.reference_yaml`	No	Path for YAML reference output (used by CATS `--refData`)
`seeds`	Yes	Ordered array of seed entries

Seed Entry Structure

Each entry in the seeds array has:

{
  "kind": "threat_model",
  "ref": "my-tm",
  "data": {
    "name": "Example Threat Model",
    "description": "..."
  }
}

Field	Required	Description
`kind`	Yes	Entity type (see Entity Kinds below)
`ref`	No	Reference name for cross-referencing by later entries
`data`	Yes	Entity-specific fields

Entity Kinds

Kind	Strategy	Required Fields	Description
`user`	DB	`user_id`, `provider`	Create user record. Optional: `admin` (bool), `api_quota`
`setting`	DB	`key`, `type`	Create system setting. Optional: `value`, `description`
`threat_model`	API	`name`	Create threat model
`threat`	API	`threat_model_ref`, `name`	Create threat under a threat model
`diagram`	API	`threat_model_ref`, `name`	Create diagram under a threat model
`document`	API	`threat_model_ref`, `name`	Create document under a threat model
`asset`	API	`threat_model_ref`, `name`	Create asset under a threat model
`note`	API	`threat_model_ref`	Create note under a threat model
`repository`	API	`threat_model_ref`, `name`	Create repository under a threat model
`metadata`	API	`target_ref`, `key`	Create metadata on any entity
`webhook`	API	(per schema)	Create webhook subscription
`webhook_test_delivery`	API	`webhook_ref`	Trigger a test delivery on a webhook
`addon`	API	(per schema)	Create addon
`client_credential`	API	(per schema)	Create client credential
`survey`	API	(per schema)	Create intake survey
`survey_response`	API	`survey_ref`	Create survey response

Strategy indicates how the entity is created:

DB -- Written directly to the database via GORM
API -- Created through the TMI REST API (requires a running server)

Reference Resolution

Seed entries are processed top-to-bottom. The ref field on an entry assigns a name that later entries can use to reference the created entity's ID.

Reference fields use the naming convention {kind}_ref. For example, a threat entry uses threat_model_ref to reference a previously created threat model:

{
  "kind": "threat_model",
  "ref": "my-tm",
  "data": { "name": "My Threat Model" }
},
{
  "kind": "threat",
  "ref": "my-threat",
  "data": {
    "threat_model_ref": "my-tm",
    "name": "My Threat"
  }
}

When the threat entry is processed, threat_model_ref: "my-tm" resolves to the UUID of the threat model created by the earlier entry. The threat_model_ref key is removed from the payload before the API call, and the resolved UUID is used to construct the correct API URL.

References that have not been defined yet (or are misspelled) cause a fatal error.

CATS Integration

CATS test data is defined declaratively in test/seeds/cats-seed-data.json instead of being hardcoded in Go source.

Quick Start

# Seed CATS test data (calls tmi-dbtool --import-test-data internally)
make cats-seed

# Seed + run CATS fuzzing
make cats-fuzz

The make cats-seed target builds tmi-dbtool, starts the OAuth stub, and runs the tool with the CATS seed file. After seeding, it generates:

test/outputs/cats/cats-test-data.json -- JSON reference file with created entity IDs
test/outputs/cats/cats-test-data.yml -- YAML reference data for CATS --refData parameter

Customizing CATS Test Data

To add new entity types for fuzzing, edit test/seeds/cats-seed-data.json and add entries to the seeds array. The reference file generation handles the new entities automatically.