Database Guide

Database Guide

Capuchin leverages PostgreSQL to safely handle multi-tenant data storage.

Connection Boundaries

Connections are generated in internal/database/db.go. We limit connection pooling to enforce robust stability:

• DB.SetMaxOpenConns(25)
• DB.SetMaxIdleConns(5)
• DB.SetConnMaxLifetime(5 * time.Minute)

Security & Tenant Isolation

Data is fundamentally partitioned using UUID mappings. Handlers securely resolve uuid.UUID parameters mapped from JWT decoding. Every Single DB Query must encapsulate the $2 (or equivalent) matching the identity context.

Example:

// CORRECT (Ensures user 1 can't read user 2's Todo structure if they guess the ID)
database.DB.Exec("DELETE FROM todos WHERE id=$1 AND user_id=$2", todoID, userID)

Migrations (The Boot Process)

We avoid messy initialization via CLI. We utilize a separate Docker container migrate that automatically sequences any migrations built with Goose v3 explicitly overriding standard shell access. Our SQL migration logic lives identically mirrored inside db/migrations/ guaranteeing synchronized builds on container spin-up.