Use DB

Database Investigation

Query PostgreSQL databases across environments for debugging, investigation, and data verification.

Arguments

/use-db <env> <database>

Environment Routing

Which environment?
├── local → Direct psql connection
│   ├── my_app_dev on localhost:5432
│   └── my_app_eventstore on localhost:5432
├── qa → AWS credentials required
│   ├── Profile: qa
│   └── Compose with /aws-env-discovery for RDS endpoint + secrets
├── uat → AWS credentials required
│   ├── Profile: uat
│   └── Compose with /aws-env-discovery for RDS endpoint + secrets
└── prod → AWS credentials required (READ-ONLY queries only)
    ├── Profile: production
    └── DB queries via psql still possible with credentials

Connection Workflow

1. Local Environment

# App read model
psql -h localhost -p 5432 -U postgres -d my_app_dev

# EventStore
psql -h localhost -p 5432 -U postgres -d my_app_eventstore

2. Remote Environments (QA/UAT/Prod)

Step 1: Check/refresh AWS SSO

aws sts get-caller-identity --profile <env>
# If expired:
aws sso login --profile <env>

Step 2: Check macOS Keychain for cached credentials

# Fast path — check cache first
security find-generic-password -a "<app>-<env>-<db>" -w 2>/dev/null

Step 3: If not cached, discover via AWS Compose with /aws-env-discovery to find:

• RDS endpoint address and port
• Credentials from Secrets Manager

Step 4: Connect

psql -h <rds-endpoint> -U <username> -d <database>

Step 5: Optionally cache credentials

# Ask user before caching
security add-generic-password -a "<app>-<env>-<db>" \
  -s "<app>-db-credentials" \
  -l "<App> <env> <db>" \
  -w "<connection-string>" \
  -U

3. Production Safety

• Production access is READ-ONLY queries only
• Always add SET statement_timeout = '30s'; before queries
• Never run UPDATE/DELETE/INSERT on production databases

Database Routing

What are you investigating?
├── Subscription lag → eventstore
├── Event inspection → eventstore
├── Stream analysis → eventstore
├── Dead subscriptions → eventstore
├── Oban jobs → app (read model)
├── Projection freshness → app (read model)
├── Entity lookups → app (read model)
├── Connection pool status → app (read model)
└── Cross-referencing events vs projections → both

Multi-Database Correlation (when both)

When using both, query both databases to cross-reference:

1. EventStore: Get latest events for an aggregate stream
2. Read Model: Get current projected state
3. Compare: Event stream version vs projection updated_at timestamp
4. Identify gaps: Missing projections = events processed but projection failed

Common Investigation Queries

For detailed query references:

• EventStore queries → references/eventstore-queries.md
• Read model queries → references/read-model-queries.md

Quick Reference (most common)

Subscription lag (EventStore):

SELECT subscription_name,
       last_seen_event_number,
       (SELECT max(event_number) FROM events) - last_seen_event_number AS lag
FROM subscriptions
ORDER BY lag DESC;

Oban job summary (Read Model):

SELECT queue, state, count(*) FROM oban_jobs GROUP BY queue, state ORDER BY queue, state;

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