Database Health Check CLI (db-check)

An internal CLI tool that verifies all required seed and manual data exists in the DaraMex database. Run it after deployments, environment setups, or whenever the platform behaves unexpectedly.

Quick Start

From the monorepo root:

# Run all checks (reads DB config from apps/api/.env)
pnpm --filter @repo/db-check start check

# Or with explicit DB credentials
pnpm --filter @repo/db-check start check \
  --host localhost \
  --port 5432 \
  --user postgres \
  --password postgres \
  --database daramex

# Point to a different .env file
pnpm --filter @repo/db-check start check --env /path/to/.env

What It Checks

1. System Policies (41 expected)

Verifies all system policies are seeded in identity.policies. These are required for RBAC authorization.

• Source of truth: apps/api/src/modules/identity/domain/constants/system-policies.ts
• Fix: Platform Owner runs POST /policies/seed
• Covers: Super Admin, Identity, AI, and Storage module policies

2. System Roles (2 expected)

Verifies the "Organization Owner" and "Client" system roles exist with correct policy assignments.

• Source of truth: apps/api/src/modules/identity/domain/constants/system-roles.ts
• Fix: Platform Owner runs POST /roles/seed
• Checks: Both role existence AND role-policy junction records

3. Default Agency Organization (1 expected)

Verifies exactly one organization has is_default_agency = true, is active, and is of type AGENCY.

• Table: identity.organizations
• Fix: Create via admin panel or direct DB insert

CLI Commands

Command	Description
check	Run all database health checks
info	Show detailed information about what checks are performed
--help	Show help with full check descriptions

CLI Options (for check command)

Option	Description	Default
--host <host>	Database host	DB_HOST from .env or localhost
--port <port>	Database port	DB_PORT from .env or 5432
--user <user>	Database username	DB_USERNAME from .env or postgres
--password <password>	Database password	DB_PASSWORD from .env or postgres
--database <database>	Database name	DB_NAME from .env or daramex
--env <path>	Path to .env file	apps/api/.env

How It Works

1. Loads configuration — reads DB credentials from CLI args, environment variables, or .env file (in that priority order)
2. Connects to PostgreSQL — uses pg (node-postgres) directly, no ORM
3. Runs checks sequentially — each check queries the identity schema and compares against expected data
4. Displays results — colored output with a summary table, pass/fail status, and fix instructions for failures

Architecture

apps/db-check/
├── src/
│   ├── index.ts              # CLI entry point (commander)
│   ├── runner.ts             # Check orchestrator
│   ├── types.ts              # ICheckResult, IDbConfig interfaces
│   └── checks/
│       ├── system-policies.check.ts    # Check #1
│       ├── system-roles.check.ts       # Check #2
│       └── default-organization.check.ts # Check #3
├── CHECKS.md                 # Full reference of all checked data
├── package.json
└── tsconfig.json

Adding New Checks

1. Create a new file in src/checks/ following the ICheckResult interface
2. Add the check function to the ALL_CHECKS array in src/runner.ts
3. Document the check in CHECKS.md

Exit Codes

Code	Meaning
0	All checks passed
1	One or more checks failed or connection error

Dependencies

Package	Purpose
pg	PostgreSQL client (direct queries, no ORM)
commander	CLI argument parsing
chalk	Terminal colors
ora	Loading spinners
cli-table3	ASCII table formatting
dotenv	.env file loading
tsx	TypeScript execution (dev)