DB

Database Interaction Skill

You are helping the user interact with the TMI PostgreSQL database.

Database Connection Information

IMPORTANT: All database connection information and credentials are stored in config-development.yml:

• Host: localhost (from host machine perspective)
• Port: 5432
• User: tmi_dev
• Password: dev123
• Database: tmi_dev
• Container Name: tmi-postgresql

Tool Requirements

PostgreSQL command-line tools (psql) are NOT installed on the host machine.

You MUST use docker exec to run psql commands inside the tmi-postgresql container.

Standard Database Operations

Interactive psql Session

To start an interactive psql session:

docker exec -it tmi-postgresql psql -U tmi_dev -d tmi_dev

Execute Single SQL Query

To run a single SQL query:

docker exec tmi-postgresql psql -U tmi_dev -d tmi_dev -c "YOUR SQL QUERY HERE"

Execute SQL from File

To execute SQL from a file:

docker exec -i tmi-postgresql psql -U tmi_dev -d tmi_dev < /path/to/file.sql

Or using heredoc:

docker exec -i tmi-postgresql psql -U tmi_dev -d tmi_dev <<'EOF'
YOUR SQL QUERY HERE
EOF

Common Database Tasks

List All Tables

docker exec tmi-postgresql psql -U tmi_dev -d tmi_dev -c "\dt"

Describe Table Schema

docker exec tmi-postgresql psql -U tmi_dev -d tmi_dev -c "\d table_name"

Count Records

docker exec tmi-postgresql psql -U tmi_dev -d tmi_dev -c "SELECT COUNT(*) FROM table_name;"

View Table Data

docker exec tmi-postgresql psql -U tmi_dev -d tmi_dev -c "SELECT * FROM table_name LIMIT 10;"

Run Migrations

Migrations are located in auth/migrations/ directory. See the migration commands in the Makefile or use:

make migrate

Database Reset

To reset the database (drop and recreate schema):

make heroku-reset-db  # Works for local database too

Clear generated test data from the dev database without

To clear automatically generated test data out of the development postgresql database without dropping it and recreating it:

make test-db-clean

TMI Database Schema

Key tables in the TMI database:

• users - User accounts and authentication
• threat_models - Top-level threat model entities
• diagrams - Threat model diagrams (DFD, etc.)
• cells - Diagram cells (nodes and edges)
• threats - Identified threats
• documents - Document attachments
• repositories - Code repository links
• notes - Text notes
• assets - Asset inventory items
• metadata - Flexible key-value metadata for entities

Best Practices

1. Always use parameterized queries when dealing with user input to prevent SQL injection
2. Use transactions for multi-statement operations
3. Check container status before executing commands:

   docker ps --filter "name=tmi-postgresql"
   

4. Quote SQL properly - use single quotes for SQL string literals, escape special characters
5. Use heredoc for multi-line SQL to avoid shell quoting issues:

   docker exec -i tmi-postgresql psql -U tmi_dev -d tmi_dev <<'EOF'
   SELECT * FROM table_name WHERE condition = 'value';
   EOF
   

Error Handling

If you encounter errors:

1. Container not running: Start the database with make start-database or make start-dev
2. Connection refused: Check if the container is healthy: docker ps
3. Authentication failed: Verify credentials in config-development.yml
4. Database does not exist: Run migrations with make migrate

Security Notes

• The credentials in config-development.yml are for local development only
• Never commit real production credentials to the repository
• The dev password (dev123) is intentionally simple for local development

Examples

Query threat models with their owners

docker exec tmi-postgresql psql -U tmi_dev -d tmi_dev -c "
SELECT id, name, owner, created_at
FROM threat_models
ORDER BY created_at DESC
LIMIT 5;
"

Find all assets of type 'software'

docker exec tmi-postgresql psql -U tmi_dev -d tmi_dev -c "
SELECT a.id, a.name, a.type, tm.name as threat_model
FROM assets a
JOIN threat_models tm ON a.threat_model_id = tm.id
WHERE a.type = 'software';
"

Check migration status

docker exec tmi-postgresql psql -U tmi_dev -d tmi_dev -c "
SELECT version, applied_at
FROM schema_migrations
ORDER BY version;
"

Integration with Claude Code

When the user asks to:

• "Show me..." - Use SELECT queries
• "Add/Create..." - Use INSERT queries (but ask for confirmation first)
• "Update/Modify..." - Use UPDATE queries (but ask for confirmation first)
• "Delete/Remove..." - Use DELETE queries (but ALWAYS ask for confirmation first)
• "Reset/Clear..." - Suggest using make heroku-reset-db or specific DELETE queries

Always show the user the SQL query you're about to execute before running it, especially for INSERT, UPDATE, or DELETE operations.

Converted and distributed by TomeVault — claim your Tome and manage your conversions.