name: database-patterns
description: "PostgreSQL + Drizzle ORM patterns. Schema design, migrations, queries, indexing. Use when building database-backed services."
metadata:
openclaw:
emoji: "🗄️"

Database Patterns

PostgreSQL + Drizzle ORM patterns for building agent infrastructure backends (like AgentHQ).

Drizzle Setup

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(pool);

Schema Design

import { pgTable, text, timestamp, integer, uuid, boolean, jsonb } from 'drizzle-orm/pg-core';

export const agents = pgTable('agents', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull().unique(),
  address: text('address').notNull(),
  capabilities: jsonb('capabilities').$type<string[]>().default([]),
  trustScore: integer('trust_score').default(0),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const payments = pgTable('payments', {
  id: uuid('id').primaryKey().defaultRandom(),
  fromAgent: uuid('from_agent').references(() => agents.id),
  toAgent: uuid('to_agent').references(() => agents.id),
  amount: text('amount').notNull(),       // Store as text (bigint-safe)
  currency: text('currency').notNull(),
  txHash: text('tx_hash').unique(),
  attestationUid: text('attestation_uid'),
  status: text('status').notNull().default('pending'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

Common Queries

Insert

const [agent] = await db.insert(agents).values({
  name: 'BAiSED',
  address: '0x55ea...',
  capabilities: ['x402', 'eas', 'data-analysis']
}).returning();

Select with Filters

import { eq, and, gte, desc } from 'drizzle-orm';

// Single
const agent = await db.select().from(agents).where(eq(agents.name, 'BAiSED')).limit(1);

// Filtered list
const recentPayments = await db.select()
  .from(payments)
  .where(and(
    eq(payments.toAgent, agentId),
    gte(payments.createdAt, oneWeekAgo)
  ))
  .orderBy(desc(payments.createdAt))
  .limit(25);

Update

await db.update(agents)
  .set({ trustScore: 85, updatedAt: new Date() })
  .where(eq(agents.id, agentId));

Join

const paymentsWithAgents = await db.select({
  payment: payments,
  sender: agents
})
.from(payments)
.leftJoin(agents, eq(payments.fromAgent, agents.id))
.where(eq(payments.toAgent, myAgentId));

Migrations

# Generate migration from schema changes
npx drizzle-kit generate

# Push schema directly (dev only)
npx drizzle-kit push

# Run migrations (production)
npx drizzle-kit migrate

drizzle.config.ts

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './shared/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL! }
});

Indexing Strategy

-- Always index foreign keys
CREATE INDEX idx_payments_from_agent ON payments(from_agent);
CREATE INDEX idx_payments_to_agent ON payments(to_agent);

-- Index frequently filtered columns
CREATE INDEX idx_agents_active ON agents(is_active) WHERE is_active = true;
CREATE INDEX idx_payments_status ON payments(status);

-- Composite index for common query patterns
CREATE INDEX idx_payments_agent_date ON payments(to_agent, created_at DESC);

Storage Patterns

Money as Text

Store token amounts as text strings (not integers) — JavaScript can't represent uint256:

amount: text('amount').notNull()  // "1000000" = 1 USDC

JSONB for Flexible Data

metadata: jsonb('metadata').$type<Record<string, unknown>>()
// Query: WHERE metadata->>'capability' = 'x402'

Timestamps

Always use timestamp with time zone:

createdAt: timestamp('created_at', { withTimezone: true }).defaultNow()

Connection Management

// Pool for production (reuse connections)
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,              // Max connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

// Graceful shutdown
process.on('SIGTERM', async () => {
  await pool.end();
  process.exit(0);
});

Cross-References

• deployment-ops: Database in production (managed Postgres)
• api-design: Database-backed API routes
• monitoring-alerting: Monitor query performance