Database Schema & ORM

Relevant source files

The following files were used as context for generating this wiki page:

• src/db/schema.ts
• src/db/db.ts
• src/db/functions/factFunctions.ts
• config.example.json
• CONTRIBUTING.md
• package.json

The Poixpixel Discord Bot utilizes a robust data persistence layer built on PostgreSQL and the Drizzle ORM. This architecture ensures type-safe database interactions, efficient connection pooling, and a clear separation between data definitions and business logic. The system is designed to handle high-frequency events such as message leveling, moderation actions, and server-wide achievements.

The database layer is integrated with a Redis caching tier to reduce load on the primary PostgreSQL instance for frequently accessed data, such as unused facts and user profile information. Configuration is handled through a centralized loader that manages connection strings for both direct access (migrations) and pooled access (production runtime).

Sources: src/db/db.ts:16-30, src/db/schema.ts:1-20, CONTRIBUTING.md:360-375

Core Architecture

The database architecture is centered around the Drizzle ORM, which provides a TypeScript-first approach to schema definition and querying. The bot employs a connection manager that handles initialization, automatic retries for transient errors, and failover between pooling and direct connection strings.

Connection Management

The system uses pg (node-postgres) to manage a Pool of connections. It prioritizes a poolingDbConnectionString for standard operations to benefit from connection multiplexing (often via PgBouncer), while keeping a directDbConnectionString as a fallback or for operations requiring direct DDL access.

The connection logic includes exponential backoff, attempting to reconnect up to a configurable maxRetryAttempts before triggering a fatal shutdown.
Sources: src/db/db.ts:153-230, config.example.json:20-35

Data Models (Schema)

The schema is defined in src/db/schema.ts using Drizzle's PostgreSQL-specific column types. It follows a relational structure where the members table serves as the primary entity for most foreign key relationships.

Entity Relationship Diagram

The following diagram illustrates the relationships between core bot features:

Table Definitions

Table	Purpose	Key Fields
members	Primary user registry	discord_id, currently_in_server, last_left_at
levels	XP and leveling data	xp, level, messages_sent, reaction_count
moderations	Log of sanctions	action, reason, duration, expires_at, active
facts	"Fact of the Day" pool	content, approved, added_by, used_on
giveaways	Giveaway metadata	prize, end_at, status, participants (array)
achievement_definitions	Achievement criteria	requirement_type, threshold, reward_type

Sources: src/db/schema.ts:15-185

ORM and Query Execution

Drizzle ORM is used for both Type-safe queries and schema migrations. The project exports a db instance that provides an interface to the underlying PostgreSQL pool.

Retrying Idempotent Queries

To improve reliability, the bot implements a wrapper for idempotent queries (like SELECT). If a transient network error occurs (e.g., ECONNRESET), the withDbRetryDrizzle function automatically retries the operation with exponential backoff.

export async function withDbRetryDrizzle<T>(
  queryFn: () => Promise<T>,
  options: { maxAttempts?: number; initialDelay?: number } = {}
): Promise<T> {
  // Logic to catch transient codes like '08006' or 'ETIMEDOUT'
  // and retry the queryFn up to maxAttempts.
}

Sources: src/db/db.ts:114-148, src/db/db.ts:60-90

Cache Integration (Redis)

The database layer is tightly coupled with a Redis caching mechanism defined in withCache. This is used to wrap database fetches for data that changes infrequently but is read often.

Example: Fact Retrieval Flow

The "Fact of the Day" system illustrates the interplay between PostgreSQL and Redis:

When a fact is added, approved, or deleted, the system calls invalidateCache('unused-facts') to ensure the next request fetches fresh data from PostgreSQL.
Sources: src/db/functions/factFunctions.ts:60-90, src/db/db.ts:366-395

Database Configuration

Configuration is managed via config.json (mapped from config.example.json).

Option	Type	Default	Description
poolingDbConnectionString	String	N/A	URI for pooled connections (PgBouncer)
directDbConnectionString	String	N/A	URI for direct database access
maxRetryAttempts	Number	5	Max attempts for initial connection
queryRetryAttempts	Number	3	Max retries for transient query errors
queryRetryInitialDelay	Number	250	Initial delay (ms) for query retries

Sources: config.example.json:20-35, src/db/db.ts:25-35

Summary

The Database Schema & ORM layer provides a resilient foundation for the bot's state management. By combining Drizzle's type safety with a custom retry logic and Redis caching, the system balances developer productivity with high availability. The modular design of factFunctions.ts, levelFunctions.ts, and other feature-specific files ensures that database logic remains maintainable and decoupled from the Discord interaction handlers.

Sources: src/db/db.ts:400-415, CONTRIBUTING.md:365-375