howto-develop-with-postgres

name	howto-develop-with-postgres
description	Use when writing database access code, creating schemas, or managing transactions with PostgreSQL - enforces transaction safety with TX_ naming, read-write separation, type safety for UUIDs/JSONB, and snake_case conventions to prevent data corruption and type errors
user-invocable	false

PostgreSQL Development Patterns

Overview

Enforce transaction safety, type safety, and naming conventions to prevent data corruption and runtime errors.

Core principles:

• Transactions prevent partial updates (data corruption)
• Type safety catches errors at compile time
• Naming conventions ensure consistency
• Read-write separation prevents accidental mutations

For TypeScript/Drizzle implementations: See typescript-drizzle.md for concrete patterns.

Transaction Management

TX_ Prefix Rule (STRICT ENFORCEMENT)

Methods that START transactions:

• Prefix method name with TX_
• Must NOT accept connection/executor parameter
• Call connection.transaction() or db.transaction() internally

Methods that PARTICIPATE in transactions:

• No TX_ prefix
• MUST accept connection/executor parameter with default value
• Execute queries using the provided executor

// GOOD: Starts transaction, has TX_ prefix, no executor parameter
async TX_createUserWithProfile(userData: UserData, profileData: ProfileData): Promise<User> {
  return this.db.transaction(async (tx) => {
    const user = await this.createUser(userData, tx);
    await this.createProfile(user.id, profileData, tx);
    return user;
  });
}

// GOOD: Participates in transaction, no TX_ prefix, takes executor
async createUser(userData: UserData, executor: Drizzle = this.db): Promise<User> {
  return executor.insert(USERS).values(userData).returning();
}

// BAD: Starts transaction but missing TX_ prefix
async createUserWithProfile(userData: UserData, profileData: ProfileData): Promise<User> {
  return this.db.transaction(async (tx) => { /* ... */ });
}

// BAD: Has TX_ prefix but takes executor parameter (allows nesting)
async TX_createUser(userData: UserData, executor: Drizzle = this.db): Promise<User> {
  return executor.transaction(async (tx) => { /* ... */ });
}

What DOES NOT count as "starting a transaction":

• Single INSERT/UPDATE/DELETE operations
• Atomic operations like onConflictDoUpdate
• SELECT queries

Type Safety

Primary Keys

Default: ULID stored as UUID

• When in doubt, use ULID: "Most things can leak in some way"
• Prevents ID enumeration attacks
• Time-sortable for indexing efficiency

Exceptions (context-dependent):

• Pure join tables (composite PK from both FKs)
• Small lookup tables (serial/identity acceptable)
• Internal-only tables with no user visibility (serial/identity acceptable)

Rule: If unsure whether data will be user-visible, use ULID.

Financial Data

Use exact decimal types (numeric/decimal) for monetary values:

• Never use float/double for money (causes rounding errors)
• Use numeric/decimal with appropriate precision and scale
• Example: numeric(19, 4) for general financial data

Why: Floating-point types accumulate rounding errors. Exact decimal types prevent financial discrepancies.

JSONB Columns

ALWAYS type JSONB columns in your ORM/schema:

• Use typed schema when structure is known
• Use Record<string, unknown> if truly schemaless
• Never leave JSONB untyped

Why: Prevents runtime errors from accessing undefined properties or wrong types.

Read-Write Separation

Maintain separate client types at compile time:

• Read-write client: Full mutation capabilities
• Read-only client: Mutation methods removed at type level
• Default to read-only for query methods
• Use read-write only when mutations needed

Why: Prevents accidental writes to replica, enforces deliberate mutation choices.

Naming Conventions

Database Identifiers

All database objects use snake_case:

• Tables: user_preferences, order_items
• Columns: created_at, user_id, is_active
• Indexes: idx_tablename_columns (e.g., idx_users_email)
• Foreign keys: fk_tablename_reftable (e.g., fk_orders_users)

Application code: Map to idiomatic case (camelCase in TypeScript, etc.)

Schema Patterns

Standard mixins:

• created_at, updated_at timestamps on all tables
• deleted_at for soft deletion when needed
• tenant_id for multi-tenant tables (project-dependent)

Proactive indexing:

• All foreign key columns
• Columns used in WHERE clauses
• Columns used in JOIN conditions
• Columns used in ORDER BY

Concurrency

Default isolation (Read Committed) for most operations.

Use stricter isolation when:

• Financial operations: Serializable isolation
• Inventory/count operations: Serializable isolation
• Critical sections: Pessimistic locking (SELECT ... FOR UPDATE)

Migrations

Always use generate + migrate workflow:

1. Change schema in code
2. Generate migration file
3. Review migration SQL
4. Apply migration to database

Never use auto-push workflow in production.

Common Mistakes

Mistake	Reality	Fix
"This is one operation, doesn't need transaction"	Multi-step operations without transactions cause partial updates and data corruption	Wrap in transaction with TX_ prefix
"Single atomic operation needs TX_ prefix"	TX_ is for explicit transaction blocks, not atomic operations	No TX_ for single INSERT/UPDATE/DELETE
"UUID is just a string"	Type confusion causes runtime errors (wrong ID formats, failed lookups)	Use strict UUID type in language
"I'll type JSONB later when schema stabilizes"	Untyped JSONB leads to undefined property access and type errors	Type immediately with known fields or Record<string, unknown>
"Read client vs write client doesn't matter"	Using wrong client bypasses separation, allows accidental mutations	Use read-only client by default, switch deliberately
"I'll add indexes when we see performance issues"	Missing indexes on foreign keys cause slow queries from day one	Add indexes proactively for FKs and common filters
"This table won't be user-visible, use serial"	Requirements change, IDs leak in logs/URLs/errors	Use ULID by default unless certain it's internal-only
"Float/double is fine for money, close enough"	Rounding errors accumulate, causing financial discrepancies (0.01 differences multiply)	Use numeric/decimal types for exact arithmetic

Red Flags - STOP and Refactor

Transaction management:

• Method calls .transaction() but no TX_ prefix
• Method has TX_ prefix but accepts executor parameter
• Multi-step operation without transaction wrapper

Type safety:

• JSONB column without type annotation
• UUID/ULID stored as plain string type
• No separation between read and write clients
• Float/double types for monetary values

Schema:

• Missing indexes on foreign keys
• No created_at/updated_at timestamps
• camelCase or PascalCase in database identifiers

All of these mean: Stop and fix immediately.

Reference

For TypeScript/Drizzle concrete implementations: typescript-drizzle.md