Instruction file imported from picasso/neuro-hub (.cursor/rules/db-scripts.mdc). Copyright stays with the author.

Database Scripts Development Guide

Reference-only guide for database and CLI scripts. Do not rely on this rule being auto-attached; use it when working in scripts/ or when a task explicitly involves database utilities.

Guidelines for creating consistent, maintainable database utility scripts. TypeScript entrypoints use scripts/utils/cli-utils.ts (chalk). Bash scripts in scripts/db/*.sh (and similar) source scripts/utils/shell-utils.sh so output matches the same scheme (ANSI colors, symbols, spacing) and honors NO_COLOR / TTY.

File Organization

scripts/
├── db/
│   ├── check-db.ts           # Database inspection
│   ├── delete-users.ts       # User deletion with cascade
│   ├── drop-all-tables.ts    # Table dropping utility
│   ├── backup-local.sh        # Local pg_dump (bash + shell-utils)
│   ├── restore-local.sh
│   └── ...
└── utils/
    ├── cli-utils.ts          # Centralized output for TypeScript CLIs
    └── shell-utils.sh         # Print helpers, env/safety, prompts for bash

TypeScript: cli-utils.ts

Always use centralized utilities from scripts/utils/cli-utils.ts for Node-based scripts. Do not call chalk directly; use the helpers below.

Output Functions

import {
    pluralize,
    printDataRow,
    printDimText,
    printEmpty,
    printError,
    printInfo,
    printListItem,
    printSection,
    printSuccess,
    printText,
    printUsage,
    printWarning,
    promptConfirmation,
} from '../utils/cli-utils'

Function Reference

Spacing:

• printEmpty() - Print empty line (instead of console.log(''))

Headers:

• printSection(title: string) - Section header with underline
  • Converts to UPPERCASE
  • Adds cyan line (━━━) below

Status Messages:

• printSuccess(message) - Green with ✓ symbol
• printWarning(message) - Yellow with ▲ symbol
• printError(message) - Red with ✖ symbol
• printInfo(message) - Blue with ◆ symbol

Dim text (secondary / descriptions):

• printDimText(message) - chalk blue + dim; use for one-line help blurbs under a section, hints, and secondary copy (same idea as a second line in print_info on the shell side)

Data Display:

• printDataRow(fields: [string, value][]) - Structured data
  • Labels in gray, values in blue.dim
  • Separators (|) in yellow.dim
  • Example: printDataRow([['Email', user.email], ['Role', user.role]])

Lists:

• printListItem(text: string, indent?: number) - Bullet lists
  • Default: • text in blue.dim
  • Custom indent: indent * 2 spaces

Text:

• printText(text: string) - Plain text output

Usage Help:

• printUsage(lines: string[]) - Command usage display
  • Renders "Usage:" header in cyan.dim
  • All lines in cyan.dim

User Interaction:

• promptConfirmation(message: string): Promise<boolean> - Yes/no prompt
  • Returns true for "yes" or "y"
  • Prompt text in yellow

Pluralization:

• pluralize(count: number, singular: string, withoutCount?: boolean): string
  • Auto-adds count: pluralize(5, 'user') → "5 users"
  • Without count: pluralize(1, 'user', true) → "user"

Bash: shell-utils.sh

Use for executable bash database scripts. Source once (after set options if you use set -u).

set -euo pipefail
ROOT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/../.." && pwd)"
# Adjust ../.. depth to the repo root from the script’s directory.
source "${ROOT_DIR}/scripts/utils/shell-utils.sh"

Print helpers (output)

• print_empty — blank line
• print_section 'Title' — uppercase title + cyan ━ line (32 chars), like printSection in TypeScript
• print_success 'message' — green, leading ✓
• print_info 'message' or print_info 'label' 'secondary' — ◆; first part blue; if a second argument is given, it prints in dim (like printDimText for a subtitle or path)
• print_error 'message' — red, leading ✖ on stderr (use for failures)
• print_text 'line' — plain (no color)
• print_dim_text 'line' — dim blue, for help text and sub-lines
• print_usage — pass each usage line as a separate argument (varargs), e.g. print_usage ' yarn db:foo' ' yarn db:foo --bar'; "Usage:" + lines in dim cyan, matching printUsage in TypeScript

Colors follow the same semantic mapping as cli-utils.ts (no raw ANSI in scripts; rely on these helpers). Styling is suppressed when NO_COLOR is set, or when stdout/stderr is not a TTY (errors check stderr).

set -u and optional arguments

With set -euo pipefail, never use bare $2 when a second parameter may be missing. Use ${2:-} or local x="${2:-}" in wrappers (see print_info in shell-utils.sh).

Environment and safety

• load_env_var_if_needed VAR_NAME /path/to/.env — if VAR_NAME is unset/empty and the file exists, sets and exports it from a KEY=value line (ignores comments; strips simple quotes)
• require_local_database_url "${0##*/}" — exits unless DATABASE_URL points at localhost or 127.0.0.1 (safety for local-only tooling)
• require_command psql 'Install PostgreSQL client tools (e.g. postgresql-client).' — exits if the binary is not on PATH (message + install hint)

Confirmation

• prompt_yes_confirmation '…' — legacy-style prompt; returns true only if the user types YES exactly (align UX with script copy; for yes/no, prefer documenting this contract in the prompt text). TypeScript’s promptConfirmation accepts yes/y; the bash helper is stricter by design

Conventions (structure, flags, spacing)

• Setup: set -euo pipefail, resolve ROOT_DIR (depth ../.. from db/ scripts), source shell-utils.sh, then call load_env_var_if_needed, require_local_database_url, and require_command as needed.
• Spacing: use print_empty around print_section and before exit 1 or final success (same intent as the TypeScript spacing list).
• --help: print_section, one print_dim_text line for what the script does, print_empty, print_usage with one argument per line, print_empty, exit 0 — see scripts/db/backup-local.sh.

Standard Patterns (TypeScript)

The snippets below are for Node/TypeScript scripts. For bash, keep the same spacing and messaging roles using print_empty, print_section, print_error, and print_usage from shell-utils.sh (see Examples).

Script Structure

// NO console.log - use utilities instead
import { kysely } from '../../src/lib/db'
import {
    printEmpty,
    printError,
    printSection,
    printSuccess,
    // ... other utilities
} from '../utils/cli-utils'

async function main() {
    printEmpty()
    printSection('Script Name')
    
    // ... script logic
    
    printEmpty()
    process.exit(0)
}

main().catch((error) => {
    printEmpty()
    printError('Error: ' + error)
    printEmpty()
    process.exit(1)
})

Sections

Always follow this pattern for sections:

printEmpty()                                    // space before
printSection('Section Title')                   // header
printSuccess('Found ' + pluralize(count, 'item'))  // status
items.forEach(item => {
    printDataRow([                              // data rows
        ['Field', item.field],
        ['Other', item.other],
    ])
})

User Confirmations

if (!force) {
    printEmpty()
    const confirmed = await promptConfirmation('Are you sure you want to proceed?')
    if (!confirmed) {
        printEmpty()
        printInfo('Operation cancelled.')
        printEmpty()
        return
    }
}

Error Handling

// Early returns with spacing
if (!data) {
    printEmpty()
    printError('Data not found.')
    printEmpty()
    return
}

// Try-catch blocks
try {
    await operation()
    printSuccess('Operation completed.')
} catch (error) {
    printError('Operation failed: ' + error)
}

Lists Display

// Simple lists
printSuccess('Found ' + pluralize(tables.length, 'table'))
tables.forEach(table => {
    printListItem(table)        // • table_name (blue.dim)
})

// Nested lists (with indent)
items.forEach(item => {
    printListItem('Item: ' + item.name)
    printText('  - Detail 1')   // manual indent for non-list text
    printText('  - Detail 2')
})

Structured Data

// Use printDataRow for key-value data
users.forEach(user => {
    printDataRow([
        ['ID', user.id],
        ['Email', user.email],
        ['Name', user.name],
        ['Role', user.role],
    ])
})
// Output: • ID: xxx | Email: yyy | Name: zzz | Role: admin

Usage Help

if (!validArgs) {
    printError('Invalid arguments provided.')
    printEmpty()
    printUsage([
        '  yarn script-name --option1          # Description',
        '  yarn script-name --option2 value    # Description',
        '  yarn script-name --option1 --force  # Description',
    ])
    printEmpty()
    process.exit(1)
}

Color Scheme

TypeScript: do not use chalk outside cli-utils.ts — use the table below. Bash: do not hand-pick ANSI codes — use shell-utils.sh print helpers (same roles).

Spacing Guidelines

Always add empty lines (TypeScript: printEmpty(); bash: print_empty):

• ✅ Before script starts: at beginning of main()
• ✅ Before each section: before printSection / print_section
• ✅ Before user prompts: before promptConfirmation / read / prompt_yes_confirmation
• ✅ After cancellation: after printInfo('Operation cancelled.') or equivalent
• ✅ Before exit: before process.exit() or exit
• ✅ In error handlers: before and after the error message

Never use:

• ❌ console.log('') - use printEmpty() instead
• ❌ console.log() - use printText() or specific utilities
• ❌ Direct chalk calls - use provided utilities
• ❌ /* eslint-disable no-console */ - not needed with utilities
• ❌ Raw echo for user-facing status lines in bash when a print_* exists

Database Operations

Safety Checks

// Check environment
const databaseUrl = process.env.DATABASE_URL || ''
if (!databaseUrl) {
    printEmpty()
    printError('DATABASE_URL environment variable is not set')
    printEmpty()
    process.exit(1)
}

// Prevent localhost operations
if (databaseUrl.includes('localhost')) {
    printEmpty()
    printError('Safety check: Cannot perform on localhost database')
    printText('   Use this script only for remote databases')
    printEmpty()
    process.exit(1)
}

Force Flags

// Always support --force to skip confirmations
const force = process.argv.includes('--force')

if (!force) {
    const confirmed = await promptConfirmation('Destructive operation. Continue?')
    if (!confirmed) {
        printInfo('Operation cancelled.')
        return
    }
}

Transaction Patterns

// Prefer explicit transactions for multi-step operations
try {
    await kysely.transaction().execute(async (trx) => {
        // All operations in transaction
        await trx.deleteFrom('table1').where('id', '=', id).execute()
        await trx.deleteFrom('table2').where('ref_id', '=', id).execute()
    })
    printSuccess('Transaction completed.')
} catch (error) {
    printError('Transaction failed: ' + error)
}

Examples

Complete Script Template

import { kysely } from '../../src/lib/db'
import {
    pluralize,
    printEmpty,
    printError,
    printInfo,
    printSection,
    printSuccess,
    promptConfirmation,
} from '../utils/cli-utils'

type Args = {
    force: boolean
    // ... other args
}

function parseArgs(): Args {
    const args = process.argv.slice(2)
    return {
        force: args.includes('--force'),
    }
}

async function performOperation(force: boolean) {
    const items = await kysely.selectFrom('table').selectAll().execute()
    
    if (items.length === 0) {
        printEmpty()
        printInfo('No items found.')
        printEmpty()
        return
    }
    
    printEmpty()
    printSection('Operation Name')
    printSuccess('Found ' + pluralize(items.length, 'item'))
    
    if (!force) {
        printEmpty()
        const confirmed = await promptConfirmation('Continue with operation?')
        if (!confirmed) {
            printEmpty()
            printInfo('Operation cancelled.')
            printEmpty()
            return
        }
    }
    
    printEmpty()
    printInfo('Processing items...')
    
    // Perform operation
    
    printEmpty()
    printSuccess('Operation completed.')
}

async function main() {
    const args = parseArgs()
    await performOperation(args.force)
    
    printEmpty()
    process.exit(0)
}

main().catch((error) => {
    printEmpty()
    printError('Error: ' + error)
    printEmpty()
    process.exit(1)
})

Local shell script (bash)

#!/usr/bin/env bash
set -euo pipefail

ROOT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/../.." && pwd)"
source "${ROOT_DIR}/scripts/utils/shell-utils.sh"

load_env_var_if_needed DATABASE_URL "${ROOT_DIR}/.env"
require_local_database_url "${0##*/}"
require_command psql 'Install PostgreSQL client tools.'

main() {
    print_empty
    print_section 'Example'
    print_dim_text 'One-line description of what this script does.'
    print_empty
    print_success 'Done.'
    print_empty
}

main "$@"

Testing

Before committing:

1. Run npm run lint:ci - must pass with 0 errors
2. Test with invalid args (should show usage)
3. Test with --force flag (should skip confirmation)
4. Test cancellation (should show proper spacing)
5. Test actual operation on dev/test database
6. For bash scripts: run with --help and confirm section, description, and usage lines; run with NO_COLOR=1 if you need to verify plain output

Common Pitfalls

❌ Don't:

• Use console.log() directly
• Import and use chalk directly
• Forget spacing before/after messages
• Use hardcoded colors (TypeScript) or ad-hoc ANSI in bash
• Skip pluralization for countable items
• Forget --force flag support
• In bash, use bare $2 when a second arg may be absent while set -u is on

✅ Do:

• Use cli-utils.ts for TypeScript and shell-utils.sh for bash
• Use printEmpty() / print_empty for spacing
• Use pluralize() for counts in TypeScript
• Add confirmation prompts for destructive operations
• Provide clear usage help (printUsage / print_usage)
• Follow the color scheme table (no ad-hoc chalk or ANSI in scripts)
• In bash with set -u, use ${n:-} for optional positional parameters
• Spaghetti echo -e with hand-written escape codes in bash — use shell-utils.sh instead